[toc]
為什么使用聯(lián)合索引
以聯(lián)合索引(a,b,c)為例
- 建立這樣的索引相當(dāng)于建立了索引a、ab媚赖、abc三個(gè)索引狈孔。一個(gè)索引頂三個(gè)索引當(dāng)然是好事，畢竟每多一個(gè)索引奴迅，都會(huì)增加寫操作的開銷和磁盤空間的開銷锁保。
- 覆蓋(動(dòng)詞)索引。同樣的有聯(lián)合索引（a,b,c）半沽，如果有如下的sql: select a,b,c from table where a=xxx and b = xxx。那么MySQL可以直接通過遍歷索引取得數(shù)據(jù)吴菠，而無需讀表者填，這減少了很多的隨機(jī)io操作。減少io操作做葵，特別的隨機(jī)io其實(shí)是dba主要的優(yōu)化策略占哟。所以，在真正的實(shí)際應(yīng)用中酿矢，覆蓋索引是主要的提升性能的優(yōu)化手段之一
- 索引列越多榨乎，通過索引篩選出的數(shù)據(jù)越少。有1000W條數(shù)據(jù)的表瘫筐，有如下sql:select * from table where a = 1 and b =2 and c = 3,假設(shè)每個(gè)條件可以篩選出10%的數(shù)據(jù)蜜暑，如果只有單值索引，那么通過該索引能篩選出1000W*10%=100w 條數(shù)據(jù)策肝，然后再回表從100w條數(shù)據(jù)中找到符合b=2 and c= 3的數(shù)據(jù)肛捍，然后再排序，再分頁之众；如果是復(fù)合索引拙毫，通過索引篩選出1000w *10% *10% *10%=1w，然后再排序棺禾、分頁缀蹄，哪個(gè)更高效，一眼便知
1. 示例表結(jié)構(gòu)
CREATE TABLE `test_user` (
`name` varchar(20) DEFAULT NULL,
`province` int(11) DEFAULT NULL,
`sex` varchar(20) DEFAULT NULL,
`birthday` int(11) DEFAULT NULL,
`phone` double DEFAULT NULL
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE INDEX test_user_name_phone_province_index
ON test_user (name, phone, province);
2. 執(zhí)行計(jì)劃說明
以下全部詳細(xì)解析explain各個(gè)屬性含義：
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
各屬性含義：
id: 查詢的序列號(hào)
-
select_type: 查詢的類型膘婶，主要是區(qū)別普通查詢和聯(lián)合查詢缺前、子查詢之類的復(fù)雜查詢
- SIMPLE：查詢中不包含子查詢或者UNION
- 查詢中若包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為：PRIMARY
- 在SELECT或WHERE列表中包含了子查詢悬襟，該子查詢被標(biāo)記為：SUBQUERY
table: 輸出的行所引用的表
-
type: 訪問類型
- ALL: 掃描全表
- index: 掃描全部索引樹
- range: 掃描部分索引诡延，索引范圍掃描，對(duì)索引的掃描開始于某一點(diǎn)古胆，返回匹配值域的行肆良，常見于between筛璧、<、>等的查詢
- ref: 使用非唯一索引或非唯一索引前綴進(jìn)行的查找
- （eq_ref和const的區(qū)別：）
- eq_ref：唯一性索引掃描惹恃，對(duì)于每個(gè)索引鍵夭谤，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描
- const, system: 單表中最多有一個(gè)匹配行巫糙，查詢起來非常迅速朗儒，例如根據(jù)主鍵或唯一索引查詢。system是const類型的特例参淹，當(dāng)查詢- 的表只有一行的情況下醉锄， 使用system。
- NULL: 不用訪問表或者索引浙值，直接就能得到結(jié)果恳不，如select 1 from test where 1
key: 顯示MySQL實(shí)際決定使用的索引。如果沒有索引被選擇开呐，是NULL
key_len: 使用到索引字段的長(zhǎng)度
注：key_len顯示的值為索引字段的最大可能長(zhǎng)度烟勋，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得筐付，不是通過表內(nèi)檢索出的卵惦。ref: 顯示哪個(gè)字段或常數(shù)與key一起被使用
rows: 這個(gè)數(shù)表示mysql要遍歷多少數(shù)據(jù)才能找到，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況瓦戚，估算的找到所需的記錄所需要讀取的行數(shù)沮尿，在innodb上可能是不準(zhǔn)確的
Extra: 執(zhí)行情況的說明和描述。包含不適合在其他列中顯示但十分重要的額外信息较解。
Using index：表示使用索引蛹找，如果只有 Using index，說明他沒有查詢到數(shù)據(jù)表哨坪，只用索引表就完成了這個(gè)查詢庸疾，這個(gè)叫覆蓋索引。
Using where：表示條件查詢当编，如果不讀取表的所有數(shù)據(jù)届慈，或不是僅僅通過索引就可以獲取所有需要的數(shù)據(jù)，則會(huì)出現(xiàn) Using where忿偷。
3. 執(zhí)行計(jì)劃分析
mysql> explain select *
-> from test_user
-> where name = '張三';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | test_user | NULL | ref | test_user_name_phone_province_index | test_user_name_phone_province_index | 63 | const | 11 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
使用了一個(gè)const
mysql> explain select *
-> from test_user
-> where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where name = '張三' and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | test_user | NULL | ref | test_user_name_phone_province_index | test_user_name_phone_province_index | 63 | const | 11 | 10.00 | Using index condition |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
使用了一個(gè)const
mysql> explain select *
-> from test_user
-> where name = '張三' and phone = '13546294373';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | test_user | NULL | ref | test_user_name_phone_province_index | test_user_name_phone_province_index | 72 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
使用了兩個(gè)const
mysql> explain select *
-> from test_user
-> where phone = '13546294373' and province = '532130';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where province = '532130' and phone = '13546294373';
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | test_user | NULL | ALL | NULL | NULL | NULL | NULL | 4753980 | 1.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
全表掃描
mysql> explain select *
-> from test_user
-> where name = '張三' and phone = '13546294373' and province = '532130';
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | test_user | NULL | ref | test_user_name_phone_province_index | test_user_name_phone_province_index | 77 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-------------------------------------+-------------------------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
使用了到了 三個(gè)const
4. 現(xiàn)象總結(jié)
對(duì)于聯(lián)合索引
CREATE INDEX test_user_name_phone_province_index
ON test_user (name, phone, province);
在查詢中我們能用到的索引的是
- name
- name phone
- name phone province
對(duì)于其他順序,或者其他字段我們不能使用該聯(lián)合索引,這個(gè)就是mysql聯(lián)合索引原則
