MySQL中的索引探究

ENMOTECHMySQL中的索引探究fei.gong@鞏飛2020ENMOTECHMySQL的索引概述01MySQL何時使用索引探究02InnoDB引擎對索引的擴展03基于GeneratedColumn的索引04目錄鞏飛(Morinsonei.gong@2002年工作至今，搞過開發(fā)、架構(gòu)、運維等，如今專注產(chǎn)品，都是圍繞著數(shù)據(jù)庫這個領(lǐng)域。經(jīng)歷了兩層架構(gòu)時代關(guān)系型數(shù)據(jù)庫技術(shù)的蓬勃發(fā)展、三層架構(gòu)時代關(guān)系型數(shù)據(jù)庫技術(shù)的砥礪前行、互聯(lián)網(wǎng)+時代數(shù)據(jù)庫技術(shù)面臨的諸多挑戰(zhàn)、一直到現(xiàn)在百家爭鳴的數(shù)據(jù)庫新時代。作為數(shù)據(jù)領(lǐng)域的老兵，很高興能繼續(xù)奮戰(zhàn)在一線，和大家一起學習成長，樂在其中。擅長場景化的SQL質(zhì)控解決方案、Oracle、MySQL、內(nèi)存數(shù)據(jù)庫、GoldenGate等云和恩墨應用架構(gòu)產(chǎn)品部總經(jīng)理，SQM產(chǎn)品經(jīng)理關(guān)于我－數(shù)據(jù)庫行業(yè)的一枚老兵01MySQL的索引概述

索引概念如果沒有索引，必須從第一行開始，讀取整個表查找，表越大，成本就越高。如果表中有相關(guān)列的索引，就可以快速確定要在數(shù)據(jù)文件中間查找的位置，而不必查看所有數(shù)據(jù)，比按順序讀取每一行快得多。和其它數(shù)據(jù)庫一樣，MySQL索引對表中指定列進行排序后另外保存，用于快速查找具有特定值的行。MySQL對索引的支持FeatureMyISAMMemoryInnoDBArchiveNDBB-treeindexesYesYesYesNoNoClusteredindexesNoNoYesNoNoFull-textsearchindexesYesNoYes(note1)NoNoGeospatialindexingsupportYesNoYes(note2)NoNoHashindexesNoYesNo(note3)NoYesT-treeindexesNoNoNoNoYesIndexcachesYesN/AYesNoYesMySQL中不是所有表上都可以建索引，要根據(jù)表使用的存儲引擎來看，有的存儲引擎支持建索引，有的不支持。MySQL5.7中主要存儲引擎對索引的支持程度如下：MySQL索引的存儲MySQL的PRIMARYKEY索引、UNIQUE索引、普通索引、FULLTEXT索引都使用B-trees存儲，Spatial索引使用R-trees存儲。MyISAM引擎中的B+Tree索引結(jié)構(gòu)Innodb主鍵索引結(jié)構(gòu)Innodb非主鍵索引結(jié)構(gòu)MySQL中創(chuàng)建索引MySQL中可以create

index語句創(chuàng)建索引，也可以使用altertable語句來創(chuàng)建索引。CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_name

[index_type]

ONtbl_name(key_part,…)

[index_option]

[algorithm_option|lock_option]…key_part:

col_name[(length)][ASC|DESC]index_option:

KEY_BLOCK_SIZE[=]value

|index_type

|WITHPARSERparser_name

|COMMENT‘string’index_type:

USING{BTREE|HASH}algorithm_option:

ALGORITHM[=]{DEFAULT|INPLACE|COPY}lock_option:

LOCK[=]{DEFAULT|NONE|SHARED|EXCLUSIVE}MySQL中索引示例對經(jīng)常在where、連接條件中出現(xiàn)的列考慮建索引。對選擇性比較好的列必要時建索引。比如用戶表中，身份證的列具有不同值，選擇性很好，索引被使用時特別高效；姓名列，選擇性較好，索引被使用時也比較高效；性別列，只含有男和女，選擇性很差，對此列建索引就沒有多大用處。不要過度創(chuàng)建索引。索引不是越多越好，每個索引都要占用磁盤空間，并會降低DML操作的性能。另外MySQL在生成執(zhí)行計劃時，過多的索引也會加重優(yōu)化器的工作，甚至可能干擾優(yōu)化器選擇不到最好的索引。MySQL索引創(chuàng)建原則02MySQL何時使用索引探究查找與WHERE條件匹配的行時emp_copy表有2萬多行數(shù)據(jù)，eno、ename列上有索引，當查找“劉杰”的信息時，MySQL就通過ename列上的索引去快速匹配。排除某些行時如果可以使用的索引有多個，MySQL通常使用查找行數(shù)最小的索引。針對多列索引，即組合索引，使用最左邊的前綴列查詢時均會使用索引例如，在表上有基于（col1、col2、col3）三列的索引，則以(col1)、(col1,col2)和(col1,col2,col3)作為條件搜索時都能使用上索引。針對多列索引，即組合索引，使用最左邊的前綴列查詢時均會使用索引如果where條件中不包含多列索引最左邊的列，則無法使用索引。關(guān)聯(lián)其它表時groupby或orderby時可以看到groupby或orderby時，MySQL并不是列上有索引就必定使用，還跟select要返回的列有關(guān)系。當select要返回的列都包含在索引中時，MySQL會優(yōu)先使用索引，這樣可以不必回表，獲得更好的效能。索引對小表價值不大，因為直接讀取整個表可能比通過索引檢索更快。索引對要讀取表中大部分甚至全部數(shù)據(jù)的SQL價值不大，因為當SQL要訪問表中大部分行時，按順序讀取比通過索引讀取更快，順序讀取可以使磁盤查找最小化。特別注意03InnoDB引擎對索引的擴展Innodb主鍵索引Innodb主鍵索引：葉子結(jié)點存放的是key值和數(shù)據(jù)，葉子結(jié)點載入內(nèi)存時，數(shù)據(jù)一起載入，找到葉子結(jié)點的key，就找到了數(shù)據(jù)。Innodb非主鍵索引Innodb非主鍵索引：葉子結(jié)點存放的是key值和對應的記錄的主鍵值，使用這些索引查詢，首先檢索其獲取主鍵，然后用主鍵在主索引中檢索獲取記錄。InnoDB引擎對索引的擴展創(chuàng)建了t1表，其主鍵為(i1,i2)，同時創(chuàng)建了基于d列的索引k_d，但其實在底層，InnoDB引擎將索引k_d擴展成（d，i1，i2）。why？InnoDB引擎這么做，是用空間換性能，優(yōu)化器在判斷是否使用索引及使用哪個索引時會有更多列參考，這樣可能生成更高效的執(zhí)行計劃，獲得更好的性能。優(yōu)化器在ref、range和index_merge類型的訪問，LooseIndexScan訪問，連接和排序優(yōu)化，MIN()/MAX()優(yōu)化時使都會使用擴展列。InnoDB對索引擴展示例在普通索引中追加擴展主鍵是InnoDB在底層做的，showindex等語句不顯示追加列，但我們可以通過其它方式來驗證。InnoDB對索引擴展示例看這個SQL： SELECTCOUNT(*)FROMt1WHEREi1=3ANDd=‘2000-01-01’如果InnoDB沒有擴展索引，索引k_d為（d），生成的執(zhí)行計劃應該類似這樣，使用k_d索引找到d為’2000-01-01’的5行數(shù)據(jù)，再回表過濾出i1為3的，最后計算count。或者使用主鍵索引找到i1為3的5行數(shù)據(jù)，再回表過濾出d為’2000-01-01’的，最后計算count。如果InnoDB擴展了索引，索引k_d為（d，i1，i2），這時，優(yōu)化器可以使用最左邊的索引前綴（d，i1），生成的執(zhí)行計劃應該類似這樣，使用k_d索引找到d為’2000-01-01’及i1為3的數(shù)據(jù)，然后計算count實際的執(zhí)行計劃特別說明默認情況下，優(yōu)化器分析InnoDB表的索引時會考慮擴展列，但如果因為特殊原因讓優(yōu)化器不考慮擴展列，可以使用SEToptimizer_switch='use_index_extensions=off’設置。沒有充分的理由，不要關(guān)閉此能力04基于GeneratedColumn的索引什么是GeneratedColumn（生成列）？我們先看看什么是GeneratedColumn（生成列）？就是表中這列的值是從其它列計算的，這是MySQL5.7的新特性。比如，知道直角三角形兩條直角邊的長度，斜邊的長度可以通過兩條直角邊計算而得。這時候就可以在數(shù)據(jù)庫中只存放直角邊，斜邊使用GeneratedColumn。什么是GeneratedColumn（生成列）？GeneratedColumn的語法為col_namedata_type[GENERATEDALWAYS]AS(expr)

[VIRTUAL|STORED][NOTNULL|NULL]

[UNIQUE[KEY]][[PRIMARY]KEY]

[COMMENT‘string’]AS（expr）表示生成列并定義用于計算列值的表達式，前面可以加上GENERATEDALWAYS，使列的生成性質(zhì)更加明確。VIRTUAL或STORED關(guān)鍵字指示如何存儲列值：

VIRTUAL：列值不存儲，讀取行時立即在BEFORE觸發(fā)器之后計算。這種列不占用存儲空間。

STORED：在插入或更新行時計算并存儲列值。需要占用存儲空間，并且可以基于其創(chuàng)建索引。如果未指定關(guān)鍵字，則默認值為VIRTUAL。MySQL允許在表中混合使用VIRTUAL列和STORED列?；贕eneratedColumn的索引MySQL支持基于GeneratedColumn創(chuàng)建索引?？梢钥吹剑傻牧術(shù)c被定義為f1+1，并創(chuàng)建了索引，優(yōu)化器在生成執(zhí)行計劃時也使用了該索引。如果WHERE、ORDERBY或GROUPBY引用的表達式與某個生成列的定義匹配，優(yōu)化器生成執(zhí)行計劃時就會使用基于生成列的索引。注意事項優(yōu)化器使用生成列的索引時有下面這些限制：查詢表達式與生成列定義匹配，即表達式必須相同，并且必須具有相同的結(jié)果類型。例如，如果生成列的表達式是f1+1，而查詢使用