索引基礎(chǔ)
SELECT first_name FROM sakila.actor WHERE actor_id = 5;
如果 actor_id 列上有索引,這 MySQL 將使用該索引找到 actor_id 為5的列,也就是說 MySQL 會(huì)在索引上按值進(jìn)行查找,然后返回所有包含改值得數(shù)據(jù)行.
索引可以包含一個(gè)或者多個(gè)列的值.如果索引包含多個(gè)列,列的順序也很重要,因?yàn)?MySQL 只能高效的使用索引的最左前綴列.創(chuàng)建一個(gè)包含兩個(gè)列的索引和創(chuàng)建兩個(gè)只包含一列的索引是大不相同的
如果使用的是 ORM, 是否還需要關(guān)心索引?
仍然需要
索引的類型
索引有很多類型, 現(xiàn)在MySQL中,索引是在存儲(chǔ)引擎層而不是服務(wù)器層實(shí)現(xiàn)的.所以不同的存儲(chǔ)引擎有不同的索引類型
B-Tree 索引
存儲(chǔ)引擎以不同的方式使用 B-Tree 索引,性能也各有不同,各有優(yōu)劣.
MyISAM 使用前綴壓縮技術(shù)使得索引更小,但是 InnoDB 則按照原數(shù)據(jù)格式進(jìn)行存儲(chǔ).在 InnoDB 中使用 B+Tree.MyISAM索引通過數(shù)據(jù)的物理位置引用被索引的行,而InnoDB 則根據(jù)主鍵引用被索引的行.
B-Tree意味著所有的值都是按照順序存儲(chǔ)的,并且每一個(gè)葉子頁到根的距離相同.下圖表示了B-Tree索引的抽象表示,大致反映了 InnoDB 索引是如何工作的.
B-Tree索引能加快訪問數(shù)據(jù)的速度,因?yàn)榇鎯?chǔ)引擎不再需要進(jìn)行全表掃描來獲取需要的數(shù)據(jù),取而代之是從索引的根節(jié)點(diǎn)進(jìn)行搜索.關(guān)于 B+Tree 的數(shù)據(jù)結(jié)構(gòu)介紹,可以參見我的數(shù)據(jù)結(jié)構(gòu)相關(guān)的文章.
請(qǐng)注意,索引對(duì)多個(gè)值進(jìn)行排序的依據(jù)是 CREATE TABLE 語句中定義索引時(shí)列的順序.
可以使用 B-Tree 索引的查詢類型. B-Tree 索引適合全鍵值,鍵值范圍或鍵前綴查找.其中鍵前綴查找只適合于根據(jù)最左前綴的查找. B-Tree 對(duì)如下類型的查詢有效:
全值匹配
匹配最左前列
匹配列前綴
匹配范圍值
精確匹配某一列并范圍匹配另外一列
只訪問索引的查詢
因?yàn)樗饕龝械墓?jié)點(diǎn)是有序的,所以除了按值查找之外,索引還可以用于查詢中的 ORDER BY 操作.一般來說如果 B-Tree 可以按照某種方式查找到值,那么也可以按照這種方式用于排序.
下面是一些關(guān)于 B-Tree 索引的限制:
- 如果不是按照索引的最左列開始查找,則無法使用索引.
- 不能跳過索引中的列
- 如果查詢中有某個(gè)列的范圍查詢,則其右邊所有的列都無法使用索引優(yōu)化查找,比如使用 LIKE. 如果范圍查詢劣質(zhì)的數(shù)量有限,那么可以通過使用多個(gè)等于條件來替代范圍條件.
綜上所述,索引列的順序是多的重要.索引再優(yōu)化性能的時(shí)候,需要根據(jù)不用的也無需求來設(shè)計(jì)索引列的順序
Cardinality
- 什么是Cardinality? 通過 SHOW INDEX 來查看數(shù)據(jù)表的 Cardinality項(xiàng),表示索引中以為只記錄數(shù)量的預(yù)估值.當(dāng) Cardinality/n_rows_in_table 應(yīng)盡可能的等于1,否則就要考慮是否需要索引當(dāng)前列.
- Cardinality的統(tǒng)計(jì) 采用采樣的方法: 1.表中1/16的數(shù)據(jù)發(fā)生了變化;2.stat_modified_counter > 2000000000
B+ 樹索引的使用
在不同應(yīng)用中使用
- OLTP 應(yīng)用中查詢操作中只從數(shù)據(jù)庫取一小部分?jǐn)?shù)據(jù),這是建立 B+樹索引很有意義
- OLAP 應(yīng)用中需要訪問大量的數(shù)據(jù),如果 OLAP 中的復(fù)雜查詢涉及多張表之間的聯(lián)結(jié)操作,這是添加索引有意義,如果聯(lián)結(jié)操作使用 HASH Join, 那么索引可能又變得沒有必要.
聯(lián)合索引
??聯(lián)合索引需要注意的事項(xiàng)在上面 B-Tree 都說介紹的很詳細(xì)了.
覆蓋索引
優(yōu)化器選擇不使用索引的情況
??在某些情況下發(fā)現(xiàn)優(yōu)化器并沒有使用索引去查找數(shù)據(jù),而是進(jìn)行了全表掃描.這種情況多發(fā)生于范圍查找,Join 等操作.例如:
mysql> explain SELECT * FROM tb_cinema WHERE cinema_id > 100 and cinema_id < 120;
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_cinema | NULL | range | cinema_id | cinema_id | 4 | NULL | 5 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain SELECT * FROM tb_cinema WHERE cinema_id > 100 and cinema_id < 46349;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_cinema | NULL | ALL | cinema_id | NULL | NULL | NULL | 9014 | 50.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
通過上面的例子可以看出,如果要求訪問的數(shù)據(jù)量很小那么優(yōu)化器會(huì)選擇使用輔助索引,但是當(dāng)訪問的數(shù)據(jù)量占整個(gè)表中數(shù)據(jù)的很大一部分時(shí)(超過20%),優(yōu)化器會(huì)選擇通過聚集索引來查數(shù)據(jù).如果用戶使用的是固態(tài)硬盤同時(shí)確保使用輔助索引能帶來更好的性能,那么使用關(guān)鍵字 FORCE INDEX 來強(qiáng)制使用某個(gè)索引:
mysql> explain SELECT * FROM tb_cinema FORCE INDEX(cinema_id) WHERE cinema_id > 100 and cinema_id < 46349;
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_cinema | NULL | range | cinema_id | cinema_id | 4 | NULL | 4507 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)
INDEX HINT (索引提示)
一下兩種情況使用到索引提示:
- MySQL 錯(cuò)誤的選擇了某個(gè)索引,這種情況非常少.
- 某個(gè) SQL 語句可以選擇的索引非常多,這時(shí)優(yōu)化器選擇執(zhí)計(jì)劃時(shí)間的開銷可能會(huì)大于 SQL 語句本身.這時(shí)需要通過INDEX HINT來強(qiáng)制使用優(yōu)化器不進(jìn)行各個(gè)執(zhí)行路徑的成本分析,直接執(zhí)行選擇指定的索引來完成查詢
哈希索引
MySQL 中只有 Memory 引擎顯式支持哈希索引.這里不多介紹
空間索引
MyISAM 表支持空間索引,可以用作地理數(shù)據(jù)存儲(chǔ).和 B-Tree 不一樣,這類索引無序前綴查詢.MySQL 對(duì) GIS 支持并不完善, PostgreSQL支持的比較好.
全文索引
全文索引是一種特殊類型的索引,它查找的事文本中的關(guān)鍵詞,而不是直接比較索引中的值.全文索引和其他幾類索引的匹配方式完全不一樣.全文索引更類似于搜索引擎做的事情,而不是簡單的 WHERE 條件匹配.以后的文章會(huì)有詳細(xì)介紹更多關(guān)于全文索引的細(xì)節(jié).
索引的優(yōu)點(diǎn)及使用Tips
索引可以讓服務(wù)器更快速的定位到表的指定位置.但這不是索引的唯一作用.
最常見的 B-Tree 索引,按照順序存儲(chǔ)數(shù)據(jù),所以 MySQL 可以用來做 ORDER BY 和 GROUP BY 操作.因?yàn)閿?shù)據(jù)是有序的,所以 B-Tree 也就會(huì)將相關(guān)的列值都存儲(chǔ)到一起.以下總結(jié)索引的三個(gè)優(yōu)點(diǎn):
- 索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量
- 索引可以幫助服務(wù)器避免排序如臨時(shí)表.
- 索引可以將隨機(jī) I/O 變?yōu)轫樞?I/O.
同時(shí),對(duì)于非常小的表,大部分情況下簡單的全表掃描更高效.對(duì)于中到大型的表,索引就非常的有效.但是對(duì)于特別大的表,建立和使用索引的代價(jià)將隨之增長.這種情況下需要一種技術(shù)可以直接區(qū)分出查詢需要的一組數(shù)據(jù),而不是一條一條的記錄的匹配.例如可以使用分區(qū)技術(shù).如果表的數(shù)量特別多,可以建立一個(gè)元數(shù)據(jù)信息表,用來查詢需要用到的某些特性.例如執(zhí)行那些需要聚合多個(gè)表的數(shù)據(jù)的查詢,則需要記錄"哪個(gè)用戶的信息存儲(chǔ)在哪個(gè)表中"的元數(shù)據(jù),這樣在查詢時(shí)就可以直接忽略那些不包含指定和用戶信息.對(duì)于大型系統(tǒng)(TB 級(jí)別的數(shù)據(jù)),只是一個(gè)常用的技巧,使用塊級(jí)別元數(shù)據(jù)技術(shù)來替代索引.
