學習筆記之 Effective MySQL之SQL語句最優化--索引

1 兩個索引取并集組合

執行添加索引指令：

ALTER TABLE album ADD INDEX name_release (name,first_released);
EXPLAIN SELECT a.name, ar.name,
a.first_released
FROM album a
INNER JOIN artist ar USING (artist_id)
WHERE a.name = 'Greatest Hits'
ORDER BY a.first_released;

執行過程如下：

mysql> EXPLAIN SELECT a.name, ar.name,
-> a.first_released
-> FROM album a
-> INNER JOIN artist ar USING (artist_id)
-> WHERE a.name = 'Greatest Hits'
-> ORDER BY a.first_released;
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| 1 | SIMPLE | a | ref | name_release,name_2,name_part2 | name_release | 257 | const | 659 | Using where |
| 1 | SIMPLE | ar | eq_ref | PRIMARY | PRIMARY | 4 | union.a.artist_id | 1 | |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ALTER TABLE album ADD INDEX name_release (name,first_released);

MySQL 可以在WHERE、ORDER BY 以及GROUP BY 列中使用索引；然而，一般來說MySQL 在一個表上隻選擇一個索引。

從MySQL 5.0 開始，在個别例外情況中優化器可能會使用一個以上的索引，但是在早期的版本中這樣做會導緻查詢運作更加緩慢。

2 兩個索引取并集

2.1 第一種: 最常見的索引合并的操作是兩個索引取并集，當使用者對兩個有很

高基數的索引執行OR 操作時會出現這種這種索引合并操作。請

看下面的示例：

# 執行sql指令
SET @@session.optimizer_switch='index_merge_intersection=on';
EXPLAIN SELECT artist_id, name
FROM artist
WHERE name = 'Queen'
OR founded = 1942\G
# 檢視explain結果
mysql> EXPLAIN SELECT artist_id, name
-> FROM artist
-> WHERE name = 'Queen'
-> OR founded = 1942;
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.01 sec)

Extra: Using union(name,founded); 采用了union的聯合索引模式,取合集.

注意：

在MySQL 5.1 中首次引入了optimizer_switch 系統變量，可以通過啟用或禁用這個變量來控制這些附加選項。想了解更多資訊可

以參考以下連結：http://dev.mysql.com/doc/refman/5.1/en/switchableoptimizations.html。

2.2 第二種類型的索引合并是對兩個有少量唯一值的索引取交集，如下所示：

SET @@session.optimizer_switch='index_merge_intersection=on';
EXPLAIN SELECT artist_id, name
FROM artist
WHERE type = 'Band'
AND founded = 1942;
# 執行過程顯示：
mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql> EXPLAIN SELECT artist_id, name
-> FROM artist
-> WHERE type = 'Band'
-> AND founded = 1942;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | artist | ref | founded | founded | 2 | const | 498 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra: Using intersect(founded,type); Using where 這裡由于是AND,是以隻需要取2個索引中最高效的那個索引來進行周遊取值.

2.3 第三種類型的索引合并操作和對兩個索引取并集比較類似，但它需要先經過排序：

EXPLAIN SELECT artist_id, name
FROM artist
WHERE name = 'Queen'
OR (founded BETWEEN 1942 AND 1950);
mysql> EXPLAIN SELECT artist_id, name
-> FROM artist
-> WHERE name = 'Queen'
-> OR (founded BETWEEN 1942 AND 1950);
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 5900 | Using sort_union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
1 row in set (0.00 sec)

可以通過以下連結了解更多關于索引合并的資訊：http://dev.mysql.com/doc/refman/5.5/en/index-merge-optimization.html。

4 數個索引合并的情況

在建立這些示例的過程中，還發現一種以前在任何用戶端的查詢中未曾出現過的新情況。以下是三個索引合并的示例：

mysql> EXPLAIN SELECT artist_id, name
FROM artist
WHERE name = 'Queen'
OR (type = 'Band' AND founded = '1942');
.....

mysql> EXPLAIN SELECT artist_id, name
-> FROM artist
-> WHERE name = 'Queen'
-> OR (type = 'Band' AND founded = '1942');
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| 1 | SIMPLE | artist | index_merge | name,founded | name,founded | 257,2 | NULL | 499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)

技巧

應該經常評估多列索引是否比讓優化器合并索列效率更高。多個單列索引和多個多列索引到底哪個更有優勢？這個問題

隻有結合特定應用程式的查詢類型和查詢容量才能給出答案。在各種不同的查詢條件下，将一些高基數列上的那些單列索引進行

索引合并能夠帶來很高的靈活性。資料庫寫操作的性能參考因素也同樣會影響到擷取資料的最優的資料通路路徑。

5 建立更好的MySQL 索引

主要用的比較多的2個特殊的索引

通過使用索引，查詢的執行時間可以從秒的數量級減少到毫秒數量級，這樣的性能改進能夠為你的應用程式的性能帶來飛躍。

合理的調整你的索引對優化來說是非常重要的，尤其是對于高吞吐量的應用程式。即使對執行時間的改進僅僅是數毫秒，但對于

一個每秒執行1000 次的查詢來說這也是非常有意義的性能提升。例如，把一個原本需要20 毫秒執行的每秒運作1 000 次的查詢的

執行之間縮短4 毫秒，這對于優化SQL 語句來說是至關重要的。我們将使用第4 章介紹的方法建立多列索引，并在這一基礎

上建立更好的覆寫索引。

● 建立覆寫索引

ALTER TABLE artist

DROP INDEX founded,

ADD INDEX founded_name (founded,name);

在InnoDB 中，主碼的值會被附加在非主碼索引的每個對應記錄後面，是以沒有必要在非主碼索引中指定主碼。

這一重要特性意味着InnoDB 引擎中所有非主碼索引都隐含主碼列了。并且對于那些從MyISAM 存儲引擎轉換過來的表，通常會

在它們InnoDB 表索引中将主碼添加為最後一個元素。當QEP 在Extra 列中顯示Using index 時，這并不意味着在訪

問底層表資料時使用到了索引，這表示隻有這個索引才是滿足查詢所有要求的。這種索引可以為大型查詢或者頻繁執行的查詢帶

來顯著的性能提升，它被稱為覆寫索引。覆寫索引得名于它滿足了查詢中給定表用到的所有的列。想

要建立一個覆寫索引，這個索引必須包含指定表上包括WHERE語句、ORDER BY 語句、GROUP BY 語句(如果有的話)以及

SELECT 語句中的所有列。

會對系統整體性能有影響。覆寫索引對于那些使用了很多較小長度的主碼和外鍵限制的大型規範化模式來說是理想的優化方式。

● 建立局部列的索引

ALTER TABLE artist DROP INDEX name, ADD INDEX name_part(name(20));

這裡主要考慮的是如何減小索引占用的空間。一個更小的索引意味着更少的磁盤I/O 開銷，而這又意味着能更快地通路到需

要通路的行，尤其是當磁盤上的索引和資料列遠大于可用的系統記憶體時。這樣獲得的性能改進将會超過一個非唯一的并且擁有低

基數的索引帶來的影響。局部索引是否适用取決于資料是如何通路的。之前介紹覆寫索引時，你可以看到記錄一個短小版本的name 列不會對執行過

的SQL 語句有任何好處。最大的益處隻有當你在被索引的列上添加限制條件時才能展現出來。

# 執行指令：
EXPLAIN SELECT artist_id,name,founded
FROM artist
WHERE name LIKE 'Queen%';
# 執行結果顯示：
mysql> EXPLAIN SELECT artist_id,name,founded
-> FROM artist
-> WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------
| 1 | SIMPLE | artist | range | name | name | 257 | NULL | 93 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在這個示例中，Extra後面沒有出現Using Index，是以在索引中記錄全名并沒有帶來額外的益處。

而所提供的局部列索引滿足了WHERE 條件。如何選擇合适的長度取決于資料的分布以及通路路徑。目前沒有準确的方法計算索

引的恰當長度。是以對給定範圍的列長度内的唯一值數目的比較

是必不可少的。

count了下SELECT count() FROM artist WHERE name LIKE ‘Queen%’; 才93條記錄，而SELECT count() FROM artist;有577983條記錄，按照普遍的情況，可以走索引，難道是name(20)的20定義的太長了？

ALTER TABLE artist
DROP INDEX name_part,
ADD INDEX name_part2(name(10));



mysql> ALTER TABLE artist
-> DROP INDEX name_part,
-> ADD INDEX name_part2(name(10));
Query OK, 0 rows affected (3.41 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
-> FROM artist
-> WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | artist | range | name_part2 | name_part2 | 12 | NULL | 93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看結果，再用name(5) 試試看。

mysql> ALTER TABLE artist
-> DROP INDEX name_part2,
-> ADD INDEX name_part3(name(5));
Query OK, 0 rows affected (3.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
-> FROM artist
-> WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | artist | range | name_part3 | name_part3 | 7 | NULL | 93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看來局部索引對like的效果不是很明顯的，可能跟資料分布範圍有關，也許這93條資料全部打散在各個資料庫塊中，

是以導緻解析器認為不能簡單地通過數次index就能周遊出資料，故而Extra欄裡面就沒有出現Using Index的提示。

總結：在索引中正确的定義列(包括定義列的順序和位置)能夠改變索引的實際使用效果。好的索引能夠為一個執行緩慢的查詢帶來

巨大的性能提升。索引也可能使原來執行很快的查詢的執行時間減少若幹毫秒。在高并發系統中，将1 000 000 條查詢減少幾毫秒

将會顯著改善性能，并且獲得更大的容量和擴充性。為SQL 查詢建立最優索引可以認為是一項藝術。

Refenerce : http://EffectiveMySQL.com

看完 Effective MySQL之SQL語句最優化一書的索引章節記錄.

學習筆記之 Effective MySQL之SQL語句最優化--索引

1 兩個索引取并集組合

2 兩個索引取并集

4 數個索引合并的情況

5 建立更好的MySQL 索引

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案