前言：Mysql索引的底層實現(xiàn)原理包括數(shù)據(jù)結(jié)構(gòu)和不同的mysql引擎下索引的實現(xiàn)方式會在另一篇文章中詳細描寫，這里主要討論的是具體優(yōu)化策略體現(xiàn)。

1. 導(dǎo)入

這里的數(shù)據(jù)庫示例用mysql的官方數(shù)據(jù)庫employees做演示，現(xiàn)在git上把項目clone到本地，按照mysql的官方文檔進行導(dǎo)入（這里不做具體解釋）。導(dǎo)入完后的數(shù)據(jù)庫ER圖具體如下：

image.png

2. 最左前綴原理和相關(guān)優(yōu)化

• 首先可以查看employees數(shù)據(jù)庫titles都有哪些索引

SHOW INDEX FROM employees.titles;

image.png

• 進行全列匹配的索引優(yōu)化

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

image.png

很明顯，當按照索引中所有列進行精確匹配（這里精確匹配指“=”或“IN”匹配）時，索引可以被用到。這里有一點需要注意，理論上索引對順序是敏感的，但是由于MySQL的查詢優(yōu)化器會自動調(diào)整where子句的條件順序以使用適合的索引，例如我們將where中的條件順序顛倒：

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26' AND emp_no='10001' AND title='Senior Engineer';

image.png

結(jié)果是一樣的。

• 最左前綴匹配

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001';

image.png

當查詢條件精確匹配索引的左邊連續(xù)一個或幾個列時，如<emp_no>或<emp_no, title>，所以可以被用到，但是只能用到一部分，即條件所組成的最左前綴。上面的查詢從分析結(jié)果看用到了PRIMARY索引，但是key_len為4，說明只用到了索引的第一列前綴。

• 查詢條件用到了索引中列的精確匹配，但是中間某個條件未提供。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26';

image.png

此時索引使用情況和情況二相同，因為title未提供，所以查詢只用到了索引的第一列，而后面的from_date雖然也在索引中，但是由于title不存在而無法和左前綴連接，因此需要對結(jié)果進行掃描過濾from_date（這里由于emp_no唯一，所以不存在掃描）。如果想讓from_date也使用索引而不是where過濾，可以增加一個輔助索引<emp_no, from_date>，此時上面的查詢會使用這個索引。除此之外，還可以使用一種稱之為“隔離列”的優(yōu)化方法，將emp_no與from_date之間的“坑”填上。

查看下title一共有幾種不同的值：

SELECT DISTINCT(title) FROM employees.titles;

image.png

在這種成為“坑”的列值比較少的情況下，可以考慮用“IN”來填補這個“坑”從而形成最左前綴：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no='10001'
AND title IN ('Senior Engineer', 'Staff', 'Engineer', 'Senior Staff', 'Assistant Engineer', 'Technique Leader', 'Manager')
AND from_date='1986-06-26';

image.png

這次key_len為59，說明索引被用全了，但是從type和rows看出IN實際上執(zhí)行了一個range查詢，這里檢查了7個key?？聪聝煞N查詢的性能比較：

SHOW PROFILES(這句話的開啟具體見這里);

image.png

“填坑”后性能提升了一點。如果經(jīng)過emp_no篩選后余下很多數(shù)據(jù)，則后者性能優(yōu)勢會更加明顯。當然，如果title的值很多，用填坑就不合適了，必須建立輔助索引。

• 查詢條件沒有指定索引第一列。

EXPLAIN SELECT * FROM employees.titles WHERE from_date='1986-06-26';

image.png

由于不是最左前綴，索引這樣的查詢顯然用不到索引。

• 匹配某列的前綴字符串。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';

image.png

此時可以用到索引，如果通配符%不出現(xiàn)在開頭，則可以用到索引，但根據(jù)具體情況不同可能只會用其中一個前綴。

• 范圍查詢。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer';

image.png

范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無法用到索引。同時，索引最多用于一個范圍列，因此如果查詢條件中有兩個范圍列則無法全用到索引。

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no < '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

image.png

可以看到索引對第二個范圍索引無能為力。這里特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區(qū)分范圍索引和多值匹配，因為在type中這兩者都顯示為range。同時，用了“between”并不意味著就是范圍查詢，例如下面的查詢：

EXPLAIN SELECT * FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31';

image.png

看起來是用了兩個范圍查詢，但作用于emp_no上的“BETWEEN”實際上相當于“IN”，也就是說emp_no實際是多值精確匹配。可以看到這個查詢用到了索引全部三個列。因此在MySQL中要謹慎地區(qū)分多值匹配和范圍匹配，否則會對MySQL的行為產(chǎn)生困惑。

• 查詢條件中含有函數(shù)或表達式。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior';

image.png

雖然這個查詢和情況五中功能相同，但是由于使用了函數(shù)left，則無法為title列應(yīng)用索引，而情況五中用LIKE則可以。再如：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no - 1='10000';

image.png

顯然這個查詢等價于查詢emp_no為10001的函數(shù)，但是由于查詢條件是一個表達式，MySQL無法為其使用索引?？磥鞰ySQL還沒有智能到自動優(yōu)化常量表達式的程度，因此在寫查詢語句時盡量避免表達式出現(xiàn)在查詢中，而是先手工私下代數(shù)運算，轉(zhuǎn)換為無表達式的查詢語句。

3.如何選擇索引和使用前綴索引

????如何建立索引可以根據(jù)一條公式?jīng)Q定：

Index Selectivity = Cardinality / #T

其中Selectivity是索引的選擇性指數(shù)，顯然范圍在（0,1]之間，Cardinality是指不重復(fù)的數(shù)據(jù)，也叫基數(shù)，#T是表的記錄數(shù)，選擇性指數(shù)越高建立索引的價值就越高（由B+Tree的性質(zhì)決定的）；可以驗證上面employees.titles表的title字段選擇性指數(shù)數(shù)值：

image.png

title的選擇性不足0.0001（精確值為0.00001579），所以實在沒有什么必要為其單獨建索引。
可以多試幾個數(shù)據(jù)，比如employees.salaries表的salary字段：

image.png

????????有一種與索引選擇性有關(guān)的索引優(yōu)化策略叫做前綴索引，就是用列的前綴代替整個列作為索引key，當前綴長度合適時，可以做到既使得前綴索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。下面以employees.employees表為例介紹前綴索引的選擇和使用。

查詢employees表的索引可得：

SHOW INDEX FROM employees.employees;

image.png

只有主鍵emp_no一個索引，搜索人名比較麻煩,做全表掃描:

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';

image.png

如果頻繁按名字搜索員工，這樣顯然效率很低，因此我們可以考慮建索引。有兩種選擇，建<first_name>或<first_name, last_name>，看下兩個索引的選擇性：

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;

image.png

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;

image.png

<first_name, last_name>選擇性很好，但是first_name和last_name加起來長度為30,可以考慮用first_name和last_name的前幾個字符建立索引，例如<first_name, left(last_name, 3)>，看看其選擇性：

SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;

image.png

可以嘗試把last_name前綴加到4：

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;

image.png

這時選擇性已經(jīng)很理想了，而這個索引的長度只有18，比<first_name, last_name>短了接近一半，我們把這個前綴索引 建上：

ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));

image.png

SHOW PROFILES;

image.png

????????前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即當索引本身包含查詢所需全部數(shù)據(jù)時，不再訪問數(shù)據(jù)文件本身）。