mysql优化经验_mysql优化经验

问题：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: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1248495

Extra: Using where

1 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：