Mysql数据库之索引优化

mysql凭借着出色的性能、低廉的成本、丰富的资源，已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色，但所谓“好马配好鞍”，如何能够更好的使用它，已经成为开发工程师的必修课，我们经常会从职位描述上看到诸如“精通mysql”、“sql语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。

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

以上内容是小编给大家介绍的mysql数据库之索引优化 ，希望对大家学习有所帮助！