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     |   1247259 |   NULL | NULL  |   | BTREE   |     |        |+-------------------------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)

代码效率执行情况：

mysql>explain select * from rep_corp_vehicle_online_count where corp_id = 79 and vehicle_id = 10016911 and online_day = '2016-03-29'\G*************************** 1. row ***************************      id: 1 select_type: SIMPLE    table: rep_corp_vehicle_online_count     type: ALLpossible_keys: NULL     key: NULL   key_len: NULL     ref: NULL     rows: 1248495    Extra: Using where1 row in set (0.00 sec)

表数据分析情况，重复数据很多：

mysql> select count(distinct corp_id) from rep_corp_vehicle_online_count;+-------------------------+| count(distinct corp_id) |+-------------------------+|           18 |+-------------------------+1 row in set (0.63 sec)mysql> select count(corp_id) from rep_corp_vehicle_online_count;      +----------------+| count(corp_id) |+----------------+|    1239573 |+----------------+1 row in set (0.00 sec)mysql> select count(distinct vehicle_id) from rep_corp_vehicle_online_count;    +----------------------------+| count(distinct vehicle_id) |+----------------------------+|            2580 |+----------------------------+1 row in set (1.03 sec)mysql>explain select count(vehicle_id) from rep_corp_vehicle_online_count;     +-------------------+| count(vehicle_id) |+-------------------+|      1239911 |+-------------------+1 row in set (0.00 sec)

最后处理，创建索引：

mysql> create index r_c_v on rep_corp_vehicle_online_count(corp_id,vehicle_id);    Query OK, 1487993 rows affected (6.09 sec)Records: 1487993 Duplicates: 0 Warnings: 0

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)

添加索引过后负载降低到了1.73：