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