mysql索引优化

原创文章，转载请注明。时间：2016-03-31 

问题：cpu负载过高，达到36。

现象：通过mysqladmin -uroot -p processlist 查看到大量如下信息：

Sending data  select * from `rep_corp_vehicle_online_count` where corp_id = 48 and vehicle_id = 10017543

根据以上的可能是表rep_corp_vehicle_online_count的问题 做出如下信息收集：

查看表结构：

mysql> desc rep_corp_vehicle_online_count;+-------------+-------------+------+-----+---------+----------------+| Field    | Type    | Null | Key | Default | Extra     |+-------------+-------------+------+-----+---------+----------------+| id     | int(11)   | NO  | PRI | NULL  | auto_increment || corp_id   | int(11)   | NO  |   | NULL  |        || vehicle_id | int(11)   | NO  |   | NULL  |        || online_day | varchar(20) | NO  |   | NULL  |        || loc_total  | int(11)   | NO  |   | NULL  |        || create_time | datetime  | NO  |   | NULL  |        || update_time | datetime  | NO  |   | NULL  |        |+-------------+-------------+------+-----+---------+----------------+7 rows in set (0.00 sec)

mysql> show index from rep_corp_vehicle_online_count;+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| rep_corp_vehicle_online_count | 0 | PRIMARY | 1 | id | A | 1490176 | NULL | NULL | | BTREE | | || rep_corp_vehicle_online_count | 1 | r_c_v | 1 | corp_id | A | 18 | NULL | NULL | | BTREE | | || rep_corp_vehicle_online_count | 1 | r_c_v | 2 | vehicle_id | A | 2596 | NULL | NULL | | BTREE | | |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)

 

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：mysql索引优化

关键词：MYSQL