MySQL Optimization experience

Problem: CPU load is too high to reach 36.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7E/4B/wKioL1b73Wezby7zAAAxc4hkXaw606.png "title=" 2.png " Width= "height=" border= "0" hspace= "0" vspace= "0" style= "width:800px;height:110px;" alt= " Wkiol1b73wezby7zaaaxc4hkxaw606.png "/>

Phenomenon: Through Mysqladmin-uroot-p processlist See a large number of information as follows:

Sending data select * from ' rep_corp_vehicle_online_count ' where corp_id = vehicle_id = 10017543

According to the above may be the table rep_corp_vehicle_online_count problem to make the following test:

To view the table structure:

mysql> desc rep_corp_vehicle_online_count;+-------------+-------------+------+-----+---------+--- -------------+| field       | type         | Null | Key | Default | Extra           |+-------------+-------------+------+-----+---------+----------------+|  id          | int (one)       | no   | pri | null    | auto_increment  | |  corp_id     | int (one)      | NO    |     | NULL    |                 | |  vehicle_id  | int (one)      | no   |     | null    |                 | |  online_day  | varchar ( | NO   |     ) | null    |                 | |  loc_total   | int (one)      | no   |      | NULL    |                 | |  create_time | datetime    | NO   |      | NULL    |                 | |  update_time | datetime    | NO   |     | NULL     |                | +-------------+-------------+------+-----+---------+----------------+

7 Rows in Set (0.00 sec)

To 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 Execution Status:

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, a lot of data duplication:

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)

Last processed, index created:

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: 0mysql> 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)

The load was reduced to 1.73 after the index was added:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7E/4F/wKiom1b73ECQa31vAAAUpZl1w7A437.png "title=" 1 "alt= "Wkiom1b73ecqa31vaaaupzl1w7a437.png"/>

This article is from the "ROC" blog, please be sure to keep this source http://jupeng.blog.51cto.com/7730888/1758600

MySQL Optimization experience