快捷導(dǎo)航

關(guān)于MySQL的索引之最左前綴優(yōu)化詳解

更新時(shí)間：2023年05月16日 10:37:13 作者：逆流°只是風(fēng)景-bjhxcc

這篇文章主要介紹了關(guān)于MySQL的索引之最左前綴優(yōu)化詳解,多個(gè)普通字段組合在一起創(chuàng)建的索引叫做聯(lián)合索引, 也被稱之為組合索引,需要的朋友可以參考下

一、聯(lián)合索引

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

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

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

Where語(yǔ)句	索引是否被使用
where a = 1Y,	使用到a
where a = 1 and b = 2	Y,使用到a，b
where a = 1 and b = 2 and c = 3	Y,使用到a,b,c
where a = 1 and b like ‘kk%’ and c = 3	Y,使用到a,b,c
where a = 1 and b like ‘%kk’ and c = 3	Y,只用到a
where a = 1 and b like ‘%kk%’ and c = 3	Y,只用到a
where a = 1 and b like ‘k%kk%’ and c = 3	Y,使用到a,b,c
where a = 1 and c = 3	使用到a，但是c不可以，b中間斷了
where a =13 and b > 2 and c = 3	使用到a和b， c不能用在范圍之后，b斷了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不一定能用上索引（8.0）
where b = 2 或者 where b = 3 and c = 4 或者 where c = 4	N
where a <> 1不能使用索引where abs(a) =1	不能使用索引
where b = 2不能使用索引where c = 3	不能使用索引
where b = 2 and c = 3	不能使用索引

因?yàn)橛胁樵儍?yōu)化器, 所以字段 a在 where子句中的順序不重要

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

MySQL中的排序方式

在 MySQL中有兩種排序方式:

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

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

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

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

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

在這里插入圖片描述

無(wú)索引

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

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

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

在這里插入圖片描述

接下來(lái)我們會(huì)執(zhí)行以下 SQL語(yǔ)句分別查看沒(méi)有索引和有索引的情況

explain select * from role order by orders

在這里插入圖片描述

此時(shí)可以看到, 因?yàn)榕判蛩玫降臈l件orders沒(méi)有用到索引, 索引會(huì)用到排序緩沖區(qū), 也就是把數(shù)據(jù)讀出來(lái), 然后在排序緩沖區(qū)進(jìn)行排序后展示出來(lái)

有索引

這個(gè)時(shí)候我們給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語(yǔ)句

explain select * from role order by orders, role_name

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

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

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

在這里插入圖片描述

咱就說(shuō), 不知道沒(méi)關(guān)系, 有圖有真相

order by索引字段順序不一致

explain select * from role order by role_name,orders

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

在這里插入圖片描述

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

索引字段升降序不一致

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

在這里插入圖片描述

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

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

三、總結(jié)

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

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

您可能感興趣的文章: