你的位置:首页 > 数据库

[数据库]mysql索引优化


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

问题:cpu负载过高,达到36。

wKioL1b73Wezby7zAAAxc4hkXaw606.png

现象:通过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:

wKiom1b73ECQa31vAAAUpZl1w7A437.png