一、聯(lián)合索引

對主鍵建立的索引叫做聚簇索引, 對普通字段建立的索引叫做二級索引 多個普通字段組合在一起創(chuàng)建的索引叫做聯(lián)合索引, 也被稱之為組合索引 在創(chuàng)建聯(lián)合索引時, 需要著重注意多個字段的順序問題, 因為(a,b,c)和(b,a,c)在使用時會有不同 聯(lián)合索引的使用需要遵循最左前綴匹配原則, 也就是按照最左優(yōu)先的方式進(jìn)行索引的匹配

聯(lián)合索引執(zhí)行示例

創(chuàng)建一個(a,b,c)的聯(lián)合索引, 接下來將會舉例可能會遇到的所有情況, 并寫出是否會執(zhí)行索引

因為有查詢優(yōu)化器, 所以字段 a在 where子句中的順序不重要

二、索引的 order by優(yōu)化

MySQL中的排序方式

在 MySQL中有兩種排序方式:

• Using filesort: 通過表的索引或全表掃描, 讀取滿足條件的數(shù)據(jù)行, 然后在排序緩沖區(qū)sort buffer中完成排序操作, 所有不是通過索引直接返回排序結(jié)果的排序都叫Using filesort
• Using index: 通過有序索引順序掃描直接返回有序數(shù)據(jù), 這種情況下使用的是Using index, 不需要額外的排序, 操作效率高

很明顯, Using index 使用到了索引, 肯定是性能高的, 所以我們在實際使用中盡量將 SQL優(yōu)化到Using index 接下來我們就測試一下 order by的索引使用

數(shù)據(jù)準(zhǔn)備

測試數(shù)據(jù)嘛, 肯定是越多越好.準(zhǔn)備了一張表, 數(shù)據(jù)量 2w 角色表:

• id: 自增長
• role_name: 隨機(jī)字符串, 不允許重復(fù)
• orders: 1-1000任意數(shù)字

無索引

這里我們要使用到explain命令, 也是大家很熟悉的了

explain命令主要用于查看 SQL的執(zhí)行計劃, 該命令可以模擬優(yōu)化器執(zhí)行 SQL查詢語句

當(dāng)前我們的role表是沒有索引的

接下來我們會執(zhí)行以下 SQL語句分別查看沒有索引和有索引的情況

explain select * from role order by orders

此時可以看到, 因為排序所用到的條件orders沒有用到索引, 索引會用到排序緩沖區(qū), 也就是把數(shù)據(jù)讀出來, 然后在排序緩沖區(qū)進(jìn)行排序后展示出來

有索引

這個時候我們給role表新增索引

-- 給tb_user中的age和phone創(chuàng)建索引 
-- CREATE INDEZ 索引名 ON 表名(字段名...);
CREATE INDEX or_role ON role(orders,role_name);

現(xiàn)在我們就創(chuàng)建好需要的索引了, 重新執(zhí)行一下之前的 SQL語句

explain select * from role order by orders, role_name

這次我們可以看到Extra出現(xiàn)了Using index, 也就代表著我們使用到了索引, 同時需要注意的是, 這次我們使用到了兩個排序字段orders和role_name, 也就是我們之前創(chuàng)建的索引, 眾所周知, MySQL有自己的執(zhí)行優(yōu)化器, where子句索引字段所處的位置無關(guān)緊要, 只要使用到了就可以, 那么order by是不是也是這樣呢

where子句索引字段順序不一致

explain select * from role where orders = 500 and role_name like 'a%'

咱就說, 不知道沒關(guān)系, 有圖有真相

order by索引字段順序不一致

explain select * from role order by role_name,orders

接下來我們看一下 order by子句字段順序與索引順序不一致的情況

可以看到, 最后還是出現(xiàn)了Using filesort的情況

索引字段升降序不一致

explain select * from role order by orders asc, role_name desc

我們在使用 order by的時候如果沒有指定順序, 默認(rèn)都是按照升序排列的, 索引也是這樣, 字段默認(rèn)是升序排列的, 但是當(dāng)我們查詢的時候一個升序, 一個降序, 此時就會出現(xiàn)Using filesort如果想解決這個問題, 我們可以使用下面的 SQL語句在生成索引的時候指定索引的排列順序

CREATE INDEX or_role ON role(orders asc,role_name desc);

三、總結(jié)

當(dāng)我們使用聯(lián)合索引的時候, 在where子句中要考慮最左前綴索引是否使用到了, 合理的去創(chuàng)建索引, 因為 MySQL有優(yōu)化器的存在, 所以在where子句中不用考慮字段的順序問題但是在order by使用聯(lián)合索引的時候, 要考慮order by字段和索引順序是否一致, 排序規(guī)則和索引是否一致。

到此這篇關(guān)于關(guān)于MySQL的索引之最左前綴優(yōu)化詳解的文章就介紹到這了,更多相關(guān)MySQL索引最左前綴優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論