Mysql index optimization and mysql Index

Mysql index optimization and mysql Index

Original article. For more information, see. Time: 2016-03-31

Problem: the cpu load is too high, reaching 36.

Symptom: a large amount of information is displayed through mysqladmin-uroot-p processlist:

Sending data select * from 'rep _ corp_vehicle_online_count 'where corp_id = 48 and vehicle_id = 10017543

Collect the following information based on the above problem that may be caused by the rep_corp_vehicle_online_count table:

View the table structure:

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)

 

 

View the index, only the primary key index:

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)

 

Code efficiency execution:

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)

 

Table data analysis, many duplicate data:

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)

 

Finally, create an index:

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) 

 

After the index is added, the load is reduced to 1.73: