mysql索引以及優化_ZenDei技術網路在線

今天看到別人寫的一些關於mysql索引的文章，有一些小收穫，就以此開啟我的隨筆記錄簡單摘了一些重點轉載文章：http://www.cnblogs.com/tgycoder/p/5410057.html mysql索引實現原理 1. MyISAM引擎使用B+Tree作為索引結構，葉結點的data域存 ...

今天看到別人寫的一些關於mysql索引的文章，有一些小收穫，就以此開啟我的隨筆記錄簡單摘了一些重點

轉載文章：http://www.cnblogs.com/tgycoder/p/5410057.html

mysql索引實現原理

1. MyISAM引擎使用B+Tree作為索引結構，葉結點的data域存放的是數據記錄的地址，MyISAM的索引方式也叫做“非聚集”的，之所以這麼稱呼是為了與InnoDB的聚集索引區分。

2. InnoDB也使用B+Tree作為索引結構，第一個重大區別是InnoDB的數據文件本身就是索引文件，第一個重大區別是InnoDB的數據文件本身就是索引文件。從上文知道，MyISAM索引文件和數據文件是分離的，索引文件僅保存數據記錄的地址。而在InnoDB中，表數據文件本身就是按B+Tree組織的一個索引結構，這棵樹的葉結點data域保存了完整的數據記錄。這個索引的key是數據表的主鍵，因此InnoDB表數據文件本身就是主索引

InnoDB主索引（同時也是數據文件）的示意圖，可以看到葉結點包含了完整的數據記錄。這種索引叫做聚集索引。因為InnoDB的數據文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒有），如果沒有顯式指定，則MySQL系統會自動選擇一個可以唯一標識數據記錄的列作為主鍵，如果不存在這種列，則MySQL自動為InnoDB表生成一個隱含欄位作為主鍵，這個欄位長度為6個位元組，類型為長整形。

3. 最左首碼與相關優化

之前我理解的最左首碼以為索引的順序是跟where條件查詢的一致不一致就使用不到索引，這是錯誤的

　　Ps：最左首碼原則中where字句有or出現還是會遍歷全表

（1）其實where條件的順序不影響使用索引，比如三個欄位添加聯合索引t_user表聯合索引（name, mobile, create_date）

　　 select * from t_user where mobile = '13256767876' and create_date= '2017-07-31' and name = 'corner';

　　理論上索引對順序是敏感的，但是由於MySQL的查詢優化器會自動調整where子句的條件順序以使用適合的索引，所以這樣也是可以用到索引的

（2）查詢條件沒有指定索引第一列

　　　如果where條件中沒有name條件，只有另外兩個無論順序是什麼都是無法用到索引的，如果where條件只有name,status而沒有mobile這時候只能用到一列索引，status這一列的索引是用不到的

（3）範圍查詢

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

表t_title聯合索引(emp_no，title,from_date）

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

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 4       | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

  只能用到第一列索引,這裡特別要說明MySQL一個有意思的地方，那就是僅用explain可能無法區分範圍索引和多值匹配，因為在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';

+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 59      | NULL |   16 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

（4）查詢條件中含有函數或表達式
如果查詢條件中含有函數或表達式，則MySQL不會為這列使用索引
like如果通配符%不出現在開頭，則可以用到索引，但根據具體情況不同可能只會用其中一個首碼

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%';
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | titles | range | PRIMARY       | PRIMARY | 56      | NULL |    1 | Using where |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------------+

4.索引選擇性與首碼索引
  　（1）什麼情況下判斷欄位是否應該建立索引，今天剛看到這個"選擇性"的概念，除了表數據很少的情況不用建索引因為索引文件本身要消耗存儲空間會加重資料庫操作的負擔，另外一種情況就是索引的選擇性比較低：
  所謂索引的選擇性（Selectivity），是指不重覆的索引值（也叫基數，Cardinality）與表記錄數（#T）的比值：Index Selectivity = Cardinality / #T
  顯然選擇性的取值範圍為(0, 1]，選擇性越高的索引價值越大，這是由B+Tree的性質決定的。

這個問題就像是面試時提問我的一個問題：性別列適不適合建立索引？（答案是否定的）

例如，上文用到的employees.titles表，如果title欄位經常被單獨查詢，是否需要建索引，我們看一下它的選擇性：

SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

title的選擇性不足0.0001（精確值為0.00001579），所以實在沒有什麼必要為其單獨建索引。

（2）有一種與索引選擇性有關的索引優化策略叫做首碼索引，就是用列的首碼代替整個列作為索引key，當首碼長度合適時，可以做到既使得首碼索引的選擇性接近全列索引，同時因為索引key變短而減少了索引文件的大小和維護開銷。下麵以employees.employees表為例介紹首碼索引的選擇和使用。

從圖12可以看到employees表只有一個索引<emp_no>，那麼如果我們想按名字搜索一個人，就只能全表掃描了：

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+

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

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+

<first_name>顯然選擇性太低，<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;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+

選擇性還不錯，但離0.9313還是有點距離，那麼把last_name首碼加到4：

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

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

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

此時再執行一遍按名字查詢，比較分析一下與建索引前的結果：

SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+