MySQL憑借著出色的性能、低廉的成本、豐富的資源，已經(jīng)成為絕大多數(shù)互聯(lián)網(wǎng)公司的首選關(guān)系型數(shù)據(jù)庫(kù)。雖然性能出色，但所謂“好馬配好鞍”，如何能夠更好的使用它，已經(jīng)成為開(kāi)發(fā)工程師的必修課，我們經(jīng)常會(huì)從職位描述上看到諸如“精通MySQL”、“SQL語(yǔ)句優(yōu)化”、“了解數(shù)據(jù)庫(kù)原理”等要求。我們知道一般的應(yīng)用系統(tǒng)，讀寫(xiě)比例在10:1左右，而且插入操作和一般的更新操作很少出現(xiàn)性能問(wèn)題，遇到最多的，也是最容易出問(wèn)題的，還是一些復(fù)雜的查詢(xún)操作，所以查詢(xún)語(yǔ)句的優(yōu)化顯然是重中之重。
問(wèn)題：cpu負(fù)載過(guò)高，達(dá)到36。
現(xiàn)象：通過(guò)mysqladmin -uroot -p processlist 查看到大量如下信息：
|
1
|
Sending data select * from `rep_corp_vehicle_online_count` where corp_id = 48 and vehicle_id = 10017543 |
根據(jù)以上的可能是表rep_corp_vehicle_online_count的問(wèn)題 做出如下測(cè)試：
查看表結(jié)構(gòu)：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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) |
查看索引，只有主鍵索引：
|
1
2
3
4
5
6
7
|
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) |
代碼執(zhí)行情況：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
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: 1select_type: SIMPLEtable: rep_corp_vehicle_online_counttype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 1248495Extra: Using where1 row in set (0.00 sec) |
表數(shù)據(jù)分析情況，重復(fù)數(shù)據(jù)很多：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
|
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) |
最后處理，創(chuàng)建索引：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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) |
添加索引過(guò)后負(fù)載降低到了1.73：
以上內(nèi)容是小編給大家介紹的Mysql數(shù)據(jù)庫(kù)之索引優(yōu)化 ，希望對(duì)大家學(xué)習(xí)有所幫助！
