表数据分析情况，重复数据很多：
| 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：
以上内容是小编给大家介绍的Mysql数据库之索引优化 ，希望对大家学习有所帮助！
