Q1:數(shù)據(jù)庫有哪些索引??jī)?yōu)缺點(diǎn)是什么?
1.B樹索引:大多數(shù)數(shù)據(jù)庫采用的索引(innoDB采用的是b+樹)。能夠加快訪問數(shù)據(jù)的速度,尤其是范圍數(shù)據(jù)的查找非???。缺點(diǎn)是只能從索引的最左列開始查找,也不能跳過索引中的列,如果查詢中有某個(gè)列用到了范圍查詢,則右邊所有列都無法使用索引優(yōu)化查找。
2.哈希索引:基于哈希表實(shí)現(xiàn)。在MySQL中,只有Memory引擎顯式的支持哈希搜索。哈希查找的速度非常快,但哈希索引只包含哈希值和行指針,不存儲(chǔ)字段值,所以不能用索引中的值來避免讀取行,也不能進(jìn)行排序。由于哈希索引使用的是索引列的全部?jī)?nèi)容來計(jì)算哈希值的,所以不支持部分所有列匹配查找。哈希只支持等值比較,不支持任何范圍查詢。一旦哈希沖突很多的話,維護(hù)成本非常高。innoDB支持“自適應(yīng)哈希索引”(adaptive hash index)。
3.全文索引:全文索引是一種特殊類型的索引,它查找的是文本中的關(guān)鍵字,而不是比較索引的值。最初只能在MyISAM上使用,5.6.24以后innoDB也支持了全文索引。全文索引的查詢要使用Match....against,在相同的列上同時(shí)創(chuàng)建全文搜索和基于值的B-Tree索引不會(huì)有沖突。
4.空間數(shù)據(jù)索引(R-tree索引),MyISAM支持R樹索引,好處是無需前綴查詢,會(huì)從所有緯度來索引數(shù)據(jù),可以用作地理數(shù)據(jù)的存儲(chǔ);缺點(diǎn)是必須使用MySQL的GIS相關(guān)函數(shù)如MBRCONTAINS( )等來維護(hù)數(shù)據(jù),但由于MySQL中的GIS并不完善,因此大多數(shù)人不會(huì)使用這個(gè)特性。
Q2:為什么不實(shí)用二叉查找樹或者紅黑樹作為數(shù)據(jù)庫索引。
二叉樹在處理海量數(shù)據(jù)時(shí),樹的高度太高,雖然索引效率很高,達(dá)到logN,但會(huì)進(jìn)行大量磁盤io,得不償失。而且刪除或者插入數(shù)據(jù)可能導(dǎo)致數(shù)據(jù)結(jié)構(gòu)改變變成鏈表,需要增進(jìn)平衡算法。而紅黑樹,插入刪除元素的時(shí)候會(huì)進(jìn)行頻繁的變色和旋轉(zhuǎn)(左旋,右旋),很浪費(fèi)時(shí)間。但是當(dāng)數(shù)據(jù)量很小的時(shí)候,完全可以放入紅黑樹中,此時(shí)紅黑樹的時(shí)間復(fù)雜性比b樹低。因此,綜上考慮,數(shù)據(jù)庫最后選擇了b樹作為索引。
Q3:B tree和B+ tree應(yīng)用場(chǎng)景:
1.B樹常用于文件系統(tǒng),和少部分?jǐn)?shù)據(jù)庫索引,比如mongoDB。
2.B+樹主要用于mysql數(shù)據(jù)庫索引。
Q4:B+ tree對(duì)比B tree的優(yōu)點(diǎn)
B樹的每個(gè)節(jié)點(diǎn)除了存儲(chǔ)指向 子節(jié)點(diǎn)的索引外,還要存儲(chǔ)data域,因此單一節(jié)點(diǎn)指向子節(jié)點(diǎn)的索引并不是很多,樹的高度較高,磁盤io次數(shù)較多。B+樹的高度更低,且所有data都存儲(chǔ)在葉子節(jié)點(diǎn),葉子節(jié)點(diǎn)都處于同一層,因此查詢性能穩(wěn)定,便于范圍查找。
Q5:多列排序時(shí)使用索引的坑
A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.
根據(jù)Mysql文檔的說明,創(chuàng)建索引的時(shí)候,可以加上asc或者desc,例如:add index idx(a asc,b desc)
.但是實(shí)際Mysql是會(huì)忽略的(好坑。。。)好像8.0版本之后支持desc了。
這會(huì)有什么影響呢?
假如有列test1和test2,都是int類型。
我們創(chuàng)建索引``idx1(test1,test2),
假如我們要按test1和test2排序,例如SQL
explain select * from table order by test1 ,test2 limit 1;
可以使用索引的排序:
- order by test1
- order by test1 desc
- order by test1,test2
- order by test1 desc,test2 desc
不可以使用索引的排序:
- order by test1,test2,desc
- order by test1 desc,test2
因?yàn)樗饕恢С謉esc,所以多列的索引是按全部列的升序存儲(chǔ)的。所以只排序一列,全部列升序,全部列降序,都能用索引。但是第一列用升序,第二列用降序,或者第一列降序,第二列用升級(jí),都不能使用索引。
以上就是Mysql索引常見問題匯總的詳細(xì)內(nèi)容,更多關(guān)于MySQL 索引的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章!
您可能感興趣的文章:- Mysql 索引結(jié)構(gòu)直觀圖解介紹
- 為什么MySQL數(shù)據(jù)庫索引選擇使用B+樹?
- 獲取 MySQL innodb B+tree 的高度的方法
- Mysql中索引和約束的示例語句
- MySql范圍查找時(shí)索引不生效問題的原因分析
- MySql如何查看索引并實(shí)現(xiàn)優(yōu)化
- MySQL 8.0 之索引跳躍掃描(Index Skip Scan)
- MySQL如何基于Explain關(guān)鍵字優(yōu)化索引功能
- MySQL用B+樹作為索引結(jié)構(gòu)有什么好處