個人中心
退出
《MySQL 性能優(yōu)化》之覆蓋索引不是索引
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
覆蓋索引
優(yōu)化示例
總結(jié)
大家好，我是只談技術(shù)不剪發(fā)的 Tony 老師。
今天我們來討論一個特殊的性能優(yōu)化技術(shù)：覆蓋索引。
如果覺得文章有用，歡迎評論??、點贊??、推薦??
覆蓋索引
在某些情況下，查詢語句通過索引訪問就可以返回所需的結(jié)果，不需要訪問表中的數(shù)據(jù)，此時我們把這個索引稱為覆蓋索引（Covering Index）。某些數(shù)據(jù)庫中稱之為 Index Only Scan。
準(zhǔn)確來說，覆蓋索引是優(yōu)化器選擇的一種執(zhí)行計劃；或者也可以說，任何索引在某種情況下都可能稱為覆蓋索引。
顯然，覆蓋索引減少了表的訪問（磁盤 IO 訪問），在某些情況下可以明顯提高查詢的性能。InnoDB 存儲引擎比 MyISAM 存儲引擎可以獲得更多的優(yōu)化提升，因為它的輔助索引存儲的是主鍵字段，覆蓋索引避免了回表操作（通過主鍵訪問表中的數(shù)據(jù)）。不過，InnoDB 無法針對正在被其他事務(wù)修改數(shù)據(jù)的表應(yīng)用這一優(yōu)化技術(shù)，必須等待其他事務(wù)的結(jié)束。
優(yōu)化示例
我們首先創(chuàng)建一個測試表 test 和一些數(shù)據(jù)：
CREATE TABLE test(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
col1 INT,
col2 INT,
col3 INT
) ENGINE = InnoDB;
SET SESSION cte_max_recursion_depth = 999999;
INSERT INTO test(col1, col2, col3)
WITH RECURSIVE d AS (
SELECT 1 n, 1000*rand() c1, 1000*rand() c2, 1000*rand() c3
UNION ALL
SELECT n+1, 1000*rand(), 1000*rand(), 1000*rand()
FROM d
WHERE n<10000
)
SELECT c1, c2, c3 FROM d;
除了主鍵之外，test 表上沒有其他索引。
現(xiàn)在我們通過 col1 字段查詢數(shù)據(jù)：
EXPLAIN
SELECT col1, col3
FROM test
WHERE col1 = 100;
id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra |
--|-----------|-----|----------|----|-------------|---|-------|---|----|--------|-----------|
1|SIMPLE |test | |ALL | | | | |9991| 10.0|Using where|
由于缺少索引，MySQL 只能選擇全表掃描。為了優(yōu)化以上查詢，我們可以在 col1 字段上創(chuàng)建一個索引：
CREATE INDEX idx_test_col1 USING BTREE ON test(col1);
然后再次執(zhí)行以上查詢：
EXPLAIN format=json
SELECT col1, col3
FROM test
WHERE col1 = 100;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.50"
},
"table": {
"table_name": "test",
"access_type": "ref",
"possible_keys": [
"idx_test_col1"
],
"key": "idx_test_col1",
"used_key_parts": [
"col1"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 10,
"filtered": "100.00",
"cost_info": {
"read_cost": "2.50",
"eval_cost": "1.00",
"prefix_cost": "3.50",
"data_read_per_join": "240"
},
"used_columns": [
"col1",
"col3"
]
}
}
}
此時，MySQL 選擇了通過索引 idx_test_col1 查找數(shù)據(jù)。
如果我們想要使用覆蓋索引進(jìn)一步優(yōu)化以上查詢，可以基于 col1 和 col3 創(chuàng)建一個復(fù)合索引：
CREATE INDEX idx_test_col1col3 USING BTREE ON test(col1, col3);
再次查看執(zhí)行計劃：
EXPLAIN format=json
SELECT col1, col3
FROM test
WHERE col1 = 100;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "1.95"
},
"table": {
"table_name": "test",
"access_type": "ref",
"possible_keys": [
"idx_test_col1",
"idx_test_col1col3"
],
"key": "idx_test_col1col3",
"used_key_parts": [
"col1"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 10,
"rows_produced_per_join": 10,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.95",
"eval_cost": "1.00",
"prefix_cost": "1.95",
"data_read_per_join": "240"
},
"used_columns": [
"col1",
"col3"
]
}
}
}
索引 idx_test_col1col3 中存儲了查詢所需的全部數(shù)據(jù)，因此可以實現(xiàn)覆蓋索引，或者索引覆蓋查詢。執(zhí)行計劃輸出可以看出，query_cost 從之前的 3.50 下降到了 1.95（減少了 44%），“using_index”: true 表示只需要訪問索引就可以返回查詢結(jié)果。
我們再來看一個查詢示例：
EXPLAIN -- format=json
SELECT col2, sum(col3)
FROM test
WHERE col1 = 100
GROUP BY col2
ORDER BY col2;
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-------------------------------|-------------|-------|-----|----|--------|-------------------------------|
1|SIMPLE |test | |ref |idx_test_col1,idx_test_col1col3|idx_test_col1|5 |const| 10| 100.0|Using temporary; Using filesort|
以上查詢使用 col1 字段作為條件，同時按照 col2 字段進(jìn)行分組，統(tǒng)計 col3 字段的和值，最后基于 col2 字段進(jìn)行排序。雖然可以通過索引 idx_test_col1 查找數(shù)據(jù)，但是仍然需要回表以及額外的排序。
為了優(yōu)化以上查詢語句，我們可以將 col1、col2 以及 col3 一起創(chuàng)建一個復(fù)合索引：
CREATE INDEX idx_test_col1col2col3 USING BTREE ON test(col1, col2, col3);
注意，創(chuàng)建復(fù)合索引時字段的順序一般是 WHERE 子句中的字段、GROUP BY 以及 ORDER BY 子句中的字段，最后才是 SELECT 列表中的其他字段。
此時，查詢語句的執(zhí)行計劃如下：
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-----------------------------------------------------|---------------------|-------|-----|----|--------|-----------|
1|SIMPLE |test | |ref |idx_test_col1,idx_test_col1col3,idx_test_col1col2col3|idx_test_col1col2col3|5 |const| 10| 100.0|Using index|
此時查詢同樣可以利用覆蓋索引進(jìn)行優(yōu)化。
通過以上示例可以看出，為了實現(xiàn)覆蓋索引，我們必須將查詢涉及的字段全部添加到索引中。這樣一來可能會導(dǎo)致另一個問題，那就是索引需要占用更多的磁盤空間和維護(hù)成本，當(dāng)表的數(shù)據(jù)量很大時尤其如此。另外，覆蓋索引通常只能用于優(yōu)化特定的查詢語句，大多數(shù)情況下查詢可能不能滿足覆蓋條件。
最后，任何索引都有可能成為覆蓋索引，例如：
EXPLAIN -- format=json
SELECT id
FROM test
WHERE col1 = 100;
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-----------------------------------------------------|---------------------|-------|-----|----|--------|-----------|
1|SIMPLE |test | |ref |idx_test_col1,idx_test_col1col3,idx_test_col1col2col3|idx_test_col1col2col3|5 |const| 10| 100.0|Using index|
EXPLAIN -- format=json
SELECT count(*)
FROM test
WHERE col1 = 100;
id|select_type|table|partitions|type|possible_keys |key |key_len|ref |rows|filtered|Extra |
--|-----------|-----|----------|----|-----------------------------------------------------|---------------------|-------|-----|----|--------|-----------|
1|SIMPLE |test | |ref |idx_test_col1,idx_test_col1col3,idx_test_col1col2col3|idx_test_col1col2col3|5 |const| 10| 100.0|Using index|
MySQL 二級索引中包含了主鍵 id 的數(shù)值，因此第一個查詢可以使用覆蓋索引。第二個查詢只需要統(tǒng)計字段 col1 的數(shù)量，也可以使用覆蓋索引。
總結(jié)
利用覆蓋索引技術(shù)，MySQL 只需要訪問索引就可以得到查詢所需要的數(shù)據(jù)，不需要訪問表中的數(shù)據(jù)。覆蓋索引可以用于優(yōu)化某些情況下的查詢語句。
分類導(dǎo)航