欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

mysql最左前綴法則導(dǎo)致索引失效的解決

 更新時(shí)間:2024年07月24日 10:31:19   作者:飛哥不鴿  
最左前綴是在使用innodb存儲(chǔ)引擎索引時(shí),需要遵守的法則,本文主要介紹了mysql最左前綴法則導(dǎo)致索引失效的解決,具有一定的參考價(jià)值,感興趣的可以了解一下

1. 什么是最左前綴法則

最左前綴是在使用innodb存儲(chǔ)引擎索引時(shí),需要遵守的法則。在一個(gè)聯(lián)合索引如idx(a, b, c),執(zhí)行查詢SQL時(shí),如果查詢條件包含索引的最左前綴,那么可以使用聯(lián)合索引加速查詢。對(duì)于上述例子,最左前綴包括idx(a)idx(a, b)idx(a, b, c)

2. 為什么存在最左前綴

最左前綴涉及到聯(lián)合索引如何構(gòu)建這一問(wèn)題。

我們構(gòu)建如下圖所示的數(shù)據(jù)庫(kù)表。

在這里插入圖片描述

我們構(gòu)建a, b, c字段的聯(lián)合索引,idx(a, b, c)。

索引構(gòu)建的規(guī)則很簡(jiǎn)單,先按照a排序,如果a一致,則按照b排序。如果b一致,按照c排序。如果所有索引字段都一致,那么按照主鍵排序。

有上述規(guī)則,我們可以得到如下索引圖

在這里插入圖片描述

通過(guò)索引圖,我們觀察發(fā)現(xiàn)如下結(jié)論

  • 在全局范圍內(nèi),a字段(紅框標(biāo)記)全局有序
  • 在全局范圍內(nèi),b字段(橘色框標(biāo)記)全局無(wú)序,但局部有序(從左往右數(shù)的第一個(gè)數(shù)據(jù)頁(yè))
  • 在全局范圍內(nèi),c字段(綠框標(biāo)記)全局無(wú)序

由索引的構(gòu)建規(guī)則,我們可以提煉非常關(guān)鍵的一條信息:右側(cè)字段對(duì)應(yīng)數(shù)據(jù)有序的前提是,左側(cè)字段數(shù)據(jù)確定。

我們?nèi)稳灰运饕龍D為例。b字段在全局的視角來(lái)看是沒(méi)有順序的。只有a字段確定下來(lái),b字段才能有順序。當(dāng)a = Bill時(shí),b字段對(duì)應(yīng)的數(shù)據(jù)則呈現(xiàn)升序狀態(tài)。同理,c字段要想有序,b字段必須確定下來(lái)

這就是為什么要遵守索引前綴法則。其核心原因就是聯(lián)合索引創(chuàng)建時(shí),需要優(yōu)先滿足左側(cè)字段的有序性,然后才會(huì)考慮右側(cè)字段

3. 索引失效情況

知道了為什么存在最左前綴法則,我們來(lái)分析一下什么時(shí)候聯(lián)合索引會(huì)失效。

3.1 查詢條件未添加最左側(cè)列,索引失效

我們以dish_flavor數(shù)據(jù)表為例進(jìn)行分析

在這里插入圖片描述

tip:數(shù)據(jù)庫(kù)的表最好不要太簡(jiǎn)單,數(shù)據(jù)不要太少。否則優(yōu)化器可能并不會(huì)走索引,因?yàn)樵跀?shù)據(jù)量小的情況下,可能全表掃描效率更高

我們以dish_id,name,value為字段,創(chuàng)建聯(lián)合索引

CREATE INDEX idx_dishid_name_value ON dish_flavor(dish_id, name, value);

我們分別執(zhí)行以下SQL,看看索引情況

EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

在這里插入圖片描述

走了索引

EXPLAIN SELECT * FROM dish_flavor WHERE name = '中餐' AND value = '[]';

在這里插入圖片描述

沒(méi)走索引

EXPLAIN SELECT * FROM dish_flavor WHERE value = '["不辣"]';

在這里插入圖片描述

沒(méi)走索引

EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';

在這里插入圖片描述

走了索引

以上四種情況只有第一條、第四條SQL走了索引。而這兩條SQL的共同點(diǎn)就是:都包含了dish_id這個(gè)最左側(cè)的字段。因此,想要索引生效,必須包含最左側(cè)的字段

當(dāng)然了,第一句SQL和第四句SQL也是存在區(qū)別的。我們建立的索引是idx(dish_id, name, value)dish_id緊鄰的是name。因此第一句SQL索引全部生效EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND name = '中餐';

但第二句SQL,EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 AND value = '["不辣"]';缺少了name這個(gè)字段,而value字段必須要name字段確定才能夠有序,因此當(dāng)前SQL會(huì)走idx_dishid_name_value索引,但僅僅到dish_id這個(gè)字段為止。

我們可以觀察explain輸出表的ref列

在這里插入圖片描述

第一句SQL兩個(gè)篩選條件都用于和索引進(jìn)行比較

在這里插入圖片描述

第二句SQL只有第一個(gè)篩選條件用于和索引進(jìn)行比較

tip: explain輸出的表格,ref列表示的意思是,篩選條件是否和索引進(jìn)行比較。下方是筆者從官方文檔中摘錄的信息

The ref column shows which columns or constants are compared to the index named in the key column to select rows from the table

3.2 使用OR,索引失效

執(zhí)行這條SQL:EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';,索引失效

在這里插入圖片描述

想要解釋OR關(guān)鍵字為何會(huì)導(dǎo)致失效,其實(shí)很簡(jiǎn)單。

OR在結(jié)果上可以等價(jià)于當(dāng)個(gè)SQL得到集合的并集,具體來(lái)說(shuō)

SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐'

=>

SELECT * FROM dish_flavor WHERE dish_id = 1 并上 SELECT * FROM dish_flavor WHERE name = '中餐'

對(duì)于拆解成的兩句SQL,前者可以走聯(lián)合索引,后者不能走(因?yàn)樽钭笄熬Y法則)。事實(shí)上,MySQL可不會(huì)真這么拆解SQL,那對(duì)于MySQL來(lái)說(shuō),OR的兩個(gè)篩選條件一個(gè)能走idx,一個(gè)不能。這到底是能還是不能,干脆就不走聯(lián)合索引。

即使走了聯(lián)合索引,也只能對(duì)dish_id做篩選,而存在的name字段必須要全表掃描,因?yàn)閐ish_id沒(méi)法被確定,因此他全局無(wú)序。既然如此,那為什么不直接全表掃描呢?

因此,OR關(guān)鍵字會(huì)導(dǎo)致索引失效??偨Y(jié)一下:OR關(guān)聯(lián)的兩個(gè)查詢條件,必然存在一個(gè)條件無(wú)法滿足最左前綴法則,走不了索引。對(duì)于那個(gè)走不了索引的查詢條件,在不存在其它索引的前提下,必然需要全表掃描。因此OR關(guān)鍵字會(huì)破壞聯(lián)合索引,導(dǎo)致索引失效

tip: 當(dāng)前數(shù)據(jù)表中,只存在idx(dish_id, name, value)

現(xiàn)在,我們做些額外操作,在OR的查詢條件下,讓MySQL依然走索引。

我們?yōu)?code>name字段單獨(dú)創(chuàng)建索引CREATE INDEX idx_name ON dish_flavor(name);

現(xiàn)在我們?cè)趫?zhí)行含有OR的SQL,EXPLAIN SELECT * FROM dish_flavor WHERE dish_id = 1 OR name = '中餐';

在這里插入圖片描述

我們發(fā)現(xiàn),MySQL不僅走了聯(lián)合索引,還走了idx_name索引。聯(lián)合索引解決dish_id = 1這個(gè)條件,idx_name索引解決name = '中餐'這個(gè)條件,非常完美

tip: explain輸出的type字段,內(nèi)容為index_merge。這表明SQL走了多個(gè)索引

為了下文實(shí)驗(yàn)不被多余索引干擾,我們刪除idx_name索引

3.3 函數(shù)運(yùn)算,索引失效

我們執(zhí)行SQLEXPLAIN SELECT * FROM dish_flavor WHERE dish_id + 1 = 1;,會(huì)發(fā)現(xiàn)索引失效


![

索引失效

模糊匹配

在這里插入圖片描述

索引未失效

模糊匹配

在這里插入圖片描述

索引未失效

筆者嘗試解釋上述現(xiàn)象

首先,模糊匹配本質(zhì)就是范圍查找。既然是范圍,那必然存在兩端。我們查找范圍的思路可以大致框定,對(duì)于有序的數(shù)據(jù),我們可以通過(guò)索引確定端點(diǎn),端點(diǎn)間的數(shù)據(jù)就是我們模糊匹配的內(nèi)容。

首模糊

對(duì)于首模糊情況,他的起始端點(diǎn)無(wú)法走索引確定,因?yàn)槠鹗级它c(diǎn)可以是任何數(shù)據(jù)。

同樣的,他的結(jié)束端點(diǎn)也無(wú)法走索引確定,因?yàn)樽址侄螛?gòu)建索引,依然遵循另一個(gè)層面的最左前綴法則,字符串比較就是從左到右一次比較。如果左側(cè)字符串無(wú)法確定,右側(cè)字符串就無(wú)法保證有序性,這種情況也可以歸類為全局無(wú)序,局部有序

中間模糊

對(duì)于中間模糊情況,他的起始端點(diǎn)可以走索引確定。但他的尾端點(diǎn)無(wú)法確定,但這足夠了。因?yàn)橛幸徊糠謨?nèi)容可以走索引,剩下內(nèi)容掃描整個(gè)索引即可

尾模糊

起始端點(diǎn)可以走索引確定,尾端點(diǎn)不需要確定,因?yàn)樗梢詿o(wú)限匹配后續(xù)內(nèi)容

一句話總結(jié),模糊查詢依然遵循另一個(gè)維度的最左前綴法則,它依賴于字符串索引創(chuàng)建的規(guī)則。優(yōu)先匹配左側(cè)字符串,右側(cè)字符串確定順序的前提是左側(cè)字符串已確定。

到此這篇關(guān)于mysql最左前綴法則導(dǎo)致索引失效的解決的文章就介紹到這了,更多相關(guān)mysql最左前綴法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • 安全快速修改Mysql數(shù)據(jù)庫(kù)名的5種方法

    安全快速修改Mysql數(shù)據(jù)庫(kù)名的5種方法

    mysql中如何重命名數(shù)據(jù)庫(kù)?這篇文章主要介紹了安全快速修改Mysql數(shù)據(jù)庫(kù)名的5種方法,需要的朋友可以參考下
    2014-04-04
  • MySQL xtrabackup 物理備份原理解析

    MySQL xtrabackup 物理備份原理解析

    xtrabackup 是percona公司開(kāi)源的MySQL innodb物理備份工具,支持在線熱備(備份時(shí)不影響數(shù)據(jù)讀寫(xiě)),在工具在業(yè)內(nèi)生產(chǎn)上被大量使用,本次使用xtrabackup 備份的日志和數(shù)據(jù)庫(kù)general 日志來(lái)對(duì)備份的流程和原理進(jìn)行解讀,需要的朋友可以參考下
    2022-12-12
  • 一文帶你了解MySQL之InnoDB統(tǒng)計(jì)數(shù)據(jù)是如何收集的

    一文帶你了解MySQL之InnoDB統(tǒng)計(jì)數(shù)據(jù)是如何收集的

    通過(guò)show index可以看到關(guān)于索引的統(tǒng)計(jì)數(shù)據(jù),那么這些統(tǒng)計(jì)數(shù)據(jù)是怎么來(lái)的呢,它們是以什么方式收集的呢,本章將聚焦于InnoDB存儲(chǔ)引擎的統(tǒng)計(jì)數(shù)據(jù)收集策略,需要的朋友可以參考下
    2023-05-05
  • MySQL和Redis的數(shù)據(jù)一致性問(wèn)題

    MySQL和Redis的數(shù)據(jù)一致性問(wèn)題

    這篇文章主要介紹了MySQL和Redis的數(shù)據(jù)一致性問(wèn)題,下面文章圍繞Redis大的相關(guān)資料展開(kāi)詳情,需要的小伙伴可以參考一下
    2022-04-04
  • Innodb表select查詢順序

    Innodb表select查詢順序

    這篇文章主要介紹了Innodb表select查詢順序的相關(guān)資料,需要的朋友可以參考下
    2017-01-01
  • mysql8.0.23 msi安裝超詳細(xì)教程

    mysql8.0.23 msi安裝超詳細(xì)教程

    這篇文章主要介紹了mysql8.0.23 msi安裝超詳細(xì)教程,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2021-03-03
  • MySQL?表分區(qū)步驟示例詳解

    MySQL?表分區(qū)步驟示例詳解

    MySQL表分區(qū)是一種數(shù)據(jù)庫(kù)管理技術(shù),用于將大型表拆分成更小、更可管理的分區(qū)(子表,這篇文章主要介紹了MySQL?表分區(qū)簡(jiǎn)介,需要的朋友可以參考下
    2023-09-09
  • mysql中int、bigint、smallint 和 tinyint的區(qū)別詳細(xì)介紹

    mysql中int、bigint、smallint 和 tinyint的區(qū)別詳細(xì)介紹

    最近使用mysql數(shù)據(jù)庫(kù)的時(shí)候遇到了多種數(shù)字的類型,主要有int,bigint,smallint和tinyint;接下來(lái)將詳細(xì)介紹以上三種類型的應(yīng)用
    2012-11-11
  • mysql8.0.19基礎(chǔ)數(shù)據(jù)類型詳解

    mysql8.0.19基礎(chǔ)數(shù)據(jù)類型詳解

    這篇文章主要介紹了mysql8.0.19基礎(chǔ)數(shù)據(jù)類型的相關(guān)知識(shí),本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值 ,需要的朋友可以參考下
    2020-03-03
  • MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)刪除操作詳解

    MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)刪除操作詳解

    本文我們將要學(xué)習(xí)的是作為刪除數(shù)據(jù)使用的?“DELETE”?語(yǔ)句,“DELETE”?語(yǔ)句是用來(lái)刪除數(shù)據(jù)的,它不能用來(lái)刪除數(shù)據(jù)表本身。刪除數(shù)據(jù)表使用的是?“DROP”?語(yǔ)句,而?“DELETE”?的作用只是用來(lái)刪除記錄而已
    2022-08-08

最新評(píng)論