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

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

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

1. 什么是最左前綴法則

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

2. 為什么存在最左前綴

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

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

在這里插入圖片描述

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

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

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

在這里插入圖片描述

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

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

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

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

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

3. 索引失效情況

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

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

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

在這里插入圖片描述

tip:數(shù)據(jù)庫的表最好不要太簡單,數(shù)據(jù)不要太少。否則優(yōu)化器可能并不會走索引,因為在數(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 = '[]';

在這里插入圖片描述

沒走索引

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

在這里插入圖片描述

沒走索引

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

在這里插入圖片描述

走了索引

以上四種情況只有第一條、第四條SQL走了索引。而這兩條SQL的共同點就是:都包含了dish_id這個最左側(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這個字段,而value字段必須要name字段確定才能夠有序,因此當(dāng)前SQL會走idx_dishid_name_value索引,但僅僅到dish_id這個字段為止。

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

在這里插入圖片描述

第一句SQL兩個篩選條件都用于和索引進行比較

在這里插入圖片描述

第二句SQL只有第一個篩選條件用于和索引進行比較

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

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)鍵字為何會導(dǎo)致失效,其實很簡單。

OR在結(jié)果上可以等價于當(dāng)個SQL得到集合的并集,具體來說

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

=>

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

對于拆解成的兩句SQL,前者可以走聯(lián)合索引,后者不能走(因為最左前綴法則)。事實上,MySQL可不會真這么拆解SQL,那對于MySQL來說,OR的兩個篩選條件一個能走idx,一個不能。這到底是能還是不能,干脆就不走聯(lián)合索引。

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

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

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

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

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

現(xiàn)在我們在執(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這個條件,idx_name索引解決name = '中餐'這個條件,非常完美

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

為了下文實驗不被多余索引干擾,我們刪除idx_name索引

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

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


![

索引失效

模糊匹配

在這里插入圖片描述

索引未失效

模糊匹配

在這里插入圖片描述

索引未失效

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

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

首模糊

對于首模糊情況,他的起始端點無法走索引確定,因為起始端點可以是任何數(shù)據(jù)。

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

中間模糊

對于中間模糊情況,他的起始端點可以走索引確定。但他的尾端點無法確定,但這足夠了。因為有一部分內(nèi)容可以走索引,剩下內(nèi)容掃描整個索引即可

尾模糊

起始端點可以走索引確定,尾端點不需要確定,因為它可以無限匹配后續(xù)內(nèi)容

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

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

相關(guān)文章

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

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

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

    MySQL xtrabackup 物理備份原理解析

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

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

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

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

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

    Innodb表select查詢順序

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

    mysql8.0.23 msi安裝超詳細教程

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

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

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

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

    最近使用mysql數(shù)據(jù)庫的時候遇到了多種數(shù)字的類型,主要有int,bigint,smallint和tinyint;接下來將詳細介紹以上三種類型的應(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)知識,本文給大家介紹的非常詳細,對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值 ,需要的朋友可以參考下
    2020-03-03
  • MySQL數(shù)據(jù)庫數(shù)據(jù)刪除操作詳解

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

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

最新評論