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

詳解MySQL單列索引和聯(lián)合索引

 更新時間:2022年09月22日 14:36:29   作者:哪 吒  
聯(lián)合索引的結構與電話簿類似,人名由姓和名構成,電話簿首先按姓氏進行排序,然后按名字對有相同姓氏的人進行排序,這篇文章主要介紹了MySQL單列索引和聯(lián)合索引,需要的朋友可以參考下

一、簡介

利用索引中的附加列,可以縮小搜索的范圍,但使用一個具有兩列的索引不同于使用兩個單獨的索引。

聯(lián)合索引的結構與電話簿類似,人名由姓和名構成,電話簿首先按姓氏進行排序,然后按名字對有相同姓氏的人進行排序。如果您知道姓,電話簿將非常有用,如果您知道姓和名,電話簿則更為有用,但如果您只知道名不知道姓,電話簿將沒有用處。

所以說創(chuàng)建聯(lián)合索引時,應該仔細考慮列的順序。對索引中的所有列執(zhí)行搜索或僅對前幾列執(zhí)行搜索時,聯(lián)合索引非常有用;僅對后面的任意列執(zhí)行搜索時,聯(lián)合索引則沒有用處。

二、單列索引

多個單列索引在多條件查詢時優(yōu)化器會優(yōu)先選擇最優(yōu)索引策略,可能只用一個索引,也可能將多個索引全用上。但多個單列索引底層會創(chuàng)建多個B+索引樹,比較占用空間,也會浪費一定搜索效率,故如果只有多條件聯(lián)合查詢時最好建聯(lián)合索引。

三、最左前綴原則

顧名思義是最左優(yōu)先,以最左邊的為起點任何連續(xù)的索引都能匹配上,如果第一個字段是范圍查詢需要單獨建一個索引,在創(chuàng)建聯(lián)合索引時,要根據(jù)業(yè)務需求,where子句中使用最頻繁的一列放在最左邊。這樣的話擴展性比較好,比如username經(jīng)常需要作為查詢條件,而age不常使用,則需要把username放在聯(lián)合索引的第一位置,即最左邊。

1、創(chuàng)建復合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、滿足復合索引的最左特性,哪怕只是部分,復合索引生效

SELECT * FROM employee WHERE NAME='哪吒編程'

3、沒有出現(xiàn)左邊的字段,則不滿足最左特性,索引失效

SELECT * FROM employee WHERE salary=5000

4、復合索引全使用,按左側(cè)順序出現(xiàn) name,salary,索引生效

SELECT * FROM employee WHERE NAME='哪吒編程' AND salary=5000

5、雖然違背了最左特性,但MySQL執(zhí)行SQL時會進行優(yōu)化,底層進行顛倒優(yōu)化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒編程'

6、理由

復合索引也稱為聯(lián)合索引,當我們創(chuàng)建一個聯(lián)合索引的時候,如(k1,k2,k3),相當于創(chuàng)建了(k1)、(k1,k2)和(k1,k2,k3)三個索引,這就是最左匹配原則。

聯(lián)合索引不滿足最左原則,索引一般會失效。

四、同時存在聯(lián)合索引和單列索引(字段有重復)

這個時候查詢mysql會怎么用索引呢?

這個涉及到MySQL本身的查詢優(yōu)化器策略,當一個表有多條索引可走時,mysql根據(jù)查詢語句的成本來選擇走哪條索引;

有人說where查詢是按照從左到右的順序,所以篩選力度大的條件盡量放在前面。網(wǎng)上百度過,確實有這種說法,但我親自測試過,MySQL執(zhí)行優(yōu)化器會對其進行優(yōu)化,當不考慮索引時,where條件順序?qū)π蕸]有影響,真正有影響的是是否用到了索引!

五、聯(lián)合索引本質(zhì)

當創(chuàng)建**(a, b, c)聯(lián)合索引時,相當于創(chuàng)建了(a)單列索引,(a, b)聯(lián)合索引以及(a, b, c)聯(lián)合索引,想要索引生效的話,只能使用者三種組合;當然,我們上面測試過,a, c組合也可以,但實際上只用到了a的索引,c并沒有用到。

六、索引失效

1、like子查詢,%放前面;

2、非空判斷 is not null;or語句前后沒有同時使用索引。當or左右查詢字段只有一個是索引,該索引失效,只有當or左右查詢字段均為索引時,才會生效;

3、or語句(前后都有索引才行,SQL優(yōu)化要避免寫or語句);

4、數(shù)據(jù)類型出現(xiàn)隱式轉(zhuǎn)化。如varchar不加單引號的話可能會自動轉(zhuǎn)換為int型,使索引無效,產(chǎn)生全表掃描。

七、其它知識點

1、需要加索引的字段,要在where條件中

2、數(shù)據(jù)量少的字段不需要加索引,因為建索引有一定開銷,如果數(shù)據(jù)量小則沒有必要建索引,速度范圍慢。

3、聯(lián)合索引比每個列建索引更有優(yōu)勢,因為索引建立得越多就越占磁盤空間,在更新數(shù)據(jù)的時候速度會越慢、另外建立多列索引時,順序也是需要注意的,應該講嚴格的索引放在前面,這樣篩選的力度會更大,效率更高。

八、MySQL存儲引擎簡介

1、InnoDB

支持事務處理,支持外鍵,支持崩潰修復能力和并發(fā)控制。如果需要對事務的完整性要求比較高(比如銀行),要求實現(xiàn)并發(fā)控制(比如售票),那選擇InnoDB有很大的優(yōu)勢。如果需要頻繁的更新、刪除操作的數(shù)據(jù)庫,也可以選擇InnoDB,因為支持事務的提交和回滾。

2、MyISAM

插入速度快,空間和內(nèi)存使用比較低。如果表主要是用于插入新紀錄和讀取記錄,那么選擇MyISAM能實現(xiàn)處理高效率。如果應用的完整性、并發(fā)要求比較低,也可以使用。

注意,同一個數(shù)據(jù)庫也可以使用多種存儲引擎的表。如果一個表要求比較高的事務處理,可以選擇InnoDB。這個數(shù)據(jù)庫中可以將查詢要求比較高的表選擇MyISAM存儲。如果該數(shù)據(jù)庫需要一個用于查詢的臨時表,可以選擇MEMORY存儲引擎。

九、索引結構(方法、算法)

在mysql中常用兩種索引結構(算法)BTree和Hash,兩種算法檢索方式不一樣,對查詢的作用也不一樣。

1、Hash

Hash索引的底層實現(xiàn)是由Hash表來實現(xiàn)的,非常適合以 key-value 的形式查詢,也就是單個key 查詢,或者說是等值查詢。

Hash 索引可以比較方便的提供等值查詢的場景,由于是一次定位數(shù)據(jù),不像BTree索引需 要從根節(jié)點到枝節(jié)點,最后才能訪問到頁節(jié)點這樣多次IO訪問,所以檢索效率遠高于BTree索引。但是對于范圍查詢的話,就需要進行全表掃描了。

但為什么我們使用BTree比使用Hash多呢?主要Hash本身由于其特殊性,也帶來了很多限制和弊端:

  • Hash索引僅僅能滿足“=”,“IN”,“<=>”查詢,不能使用范圍查詢。
  • 聯(lián)合索引中,Hash索引不能利用部分索引鍵查詢。 對于聯(lián)合索引中的多個列,Hash是要么全部使用,要么全部不使用,并不支持BTree支持的聯(lián)合索引的最優(yōu)前綴,也就是聯(lián)合索引的前面一個或幾個索引鍵進行查詢時,Hash索引無法被利用。
  • Hash索引無法避免數(shù)據(jù)的排序操作 由于Hash索引中存放的是經(jīng)過Hash計算之后的Hash值,而且Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣,所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算。
  • Hash索引任何時候都不能避免表掃描 Hash索引是將索引鍵通過Hash運算之后,將Hash運算結果的Hash值和所對應的行指針信息存放于一個Hash表中,由于不同索引鍵存在相同Hash值,所以即使?jié)M足某個Hash鍵值的數(shù)據(jù)的記錄條數(shù),也無法從Hash索引中直接完成查詢,還是要通過訪問表中的實際數(shù)據(jù)進行比較,并得到相應的結果。
  • Hash索引遇到大量Hash值相等的情況后性能并不一定會比BTree高 對于選擇性比較低的索引鍵,如果創(chuàng)建Hash索引,那么將會存在大量記錄指針信息存于同一個Hash值相關聯(lián)。這樣要定位某一條記錄時就會非常麻煩,會浪費多次表數(shù)據(jù)訪問,而造成整體性能底下。

2、B+ Tree

B+Tree索引是最常用的mysql數(shù)據(jù)庫索引算法,因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上,而且還可以用于like操作符,只要它的查詢條件是一個不以通配符開頭的常量,

例如:

select * from user where name like 'jack%'; select * from user where name like 'jac%k%';

如果一通配符開頭,或者沒有使用常量,則不會使用索引,

例如:

select * from user where name like '%jack'; select * from user where name like simply_name;

3、 B+/-Tree原理

在數(shù)據(jù)庫中,數(shù)據(jù)量相對較大,多路查找樹顯然更加適合數(shù)據(jù)庫的應用場景,接下來我們就介紹這兩類多路查找樹,畢竟作為程序員,心里沒點B樹怎么能行呢?

B樹:B樹就是B-樹,他有著如下的特性:

  • B樹不同于二叉樹,他們的一個節(jié)點可以存儲多個關鍵字和多個子樹指針,這就是B+樹的特點;
  • 一個m階的B樹要求除了根節(jié)點以外,所有的非葉子子節(jié)點必須要有[m/2,m]個子樹;
  • 根節(jié)點必須只能有兩個子樹,當然,如果只有根節(jié)點一個節(jié)點的情況存在;
  • B樹是一個查找二叉樹,這點和二叉查找樹很像,他都是越靠前的子樹越小,并且,同一個節(jié)點內(nèi),關鍵字按照大小排序;
  • B樹的一個節(jié)點要求子樹的個數(shù)等于關鍵字的個數(shù)+1;

B+樹就是B樹的plus版

  • B+樹將所有的查找結果放在葉子節(jié)點中,這也就意味著查找B+樹,就必須到葉子節(jié)點才能返回結果;
  • B+樹每一個節(jié)點的關鍵字個數(shù)和子樹指針個數(shù)相同;
  • B+樹的非葉子節(jié)點的每一個關鍵字對應一個指針,而關鍵字則是子樹的最大,或者最小值;

將上一節(jié)中的B-Tree優(yōu)化,由于B+Tree的非葉子節(jié)點只存儲鍵值信息,假設每個磁盤塊能存儲4個鍵值及指針信息,則變成B+Tree后其結構如下圖所示:

通常在B+Tree上有兩個頭指針,一個指向根節(jié)點,另一個指向關鍵字最小的葉子節(jié)點,而且所有葉子節(jié)點(即數(shù)據(jù)節(jié)點)之間是一種鏈式環(huán)結構。因此可以對B+Tree進行兩種查找運算:一種是對于主鍵的范圍查找和分頁查找,另一種是從根節(jié)點開始,進行隨機查找。

可能上面例子中只有22條數(shù)據(jù)記錄,看不出B+Tree的優(yōu)點,下面做一個推算:

InnoDB存儲引擎中頁的大小為16KB,一般表的主鍵類型為INT(占用4個字節(jié))或BIGINT(占用8個字節(jié)),指針類型也一般為4或8個字節(jié),也就是說一個頁(B+Tree中的一個節(jié)點)中大概存儲16KB/(8B+8B)=1K個鍵值(因為是估值,為方便計算,這里的K取值為〖10〗^3)。
也就是說一個深度為3的B+Tree索引可以維護10^3 * 10^3 * 10^3 = 10億條記錄。

實際情況中每個節(jié)點可能不能填充滿,因此在數(shù)據(jù)庫中,B+Tree的高度一般都在2-4層。MySQL的InnoDB存儲引擎在設計時是將根節(jié)點常駐內(nèi)存的,也就是說查找某一鍵值的行記錄時最多只需要1~3次磁盤I/O操作。

數(shù)據(jù)庫中的B+Tree索引可以分為聚集索引(clustered index)和輔助索引(secondary index)。上面的B+Tree示例圖在數(shù)據(jù)庫中的實現(xiàn)即為聚集索引,聚集索引的B+Tree中的葉子節(jié)點存放的是整張表的行記錄數(shù)據(jù)。輔助索引與聚集索引的區(qū)別在于輔助索引的葉子節(jié)點并不包含行記錄的全部數(shù)據(jù),而是存儲相應行數(shù)據(jù)的聚集索引鍵,即主鍵。當通過輔助索引來查詢數(shù)據(jù)時,InnoDB存儲引擎會遍歷輔助索引找到主鍵,然后再通過主鍵在聚集索引中找到完整的行記錄數(shù)據(jù)。

到此這篇關于MySQL單列索引和聯(lián)合索引的文章就介紹到這了,更多相關MySQL單列索引和聯(lián)合索引內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!

相關文章

  • 利用MySQL統(tǒng)計一列中不同值的數(shù)量方法示例

    利用MySQL統(tǒng)計一列中不同值的數(shù)量方法示例

    這篇文章主要給大家介紹了利用MySQL統(tǒng)計一列中不同值的數(shù)量的幾種解決方法,每種方法都給了詳細的示例代碼供大家參考學習,相信對大家具有一定的參考價值,需要的朋友們下面跟隨小編一起來看看吧。
    2017-04-04
  • 如何解決mysql重裝失敗方法介紹

    如何解決mysql重裝失敗方法介紹

    相信大家使用MySQL都有過重裝的經(jīng)歷,要是重裝MySQL基本都是在最后一步通不過,除非重裝操作系統(tǒng),究其原因就是系統(tǒng)里的注冊表沒有刪除干凈
    2012-11-11
  • mysql中的7種日志小結

    mysql中的7種日志小結

    這篇文章主要介紹了mysql中的7種日志小結,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2021-03-03
  • 一文搞懂MySQL元數(shù)據(jù)鎖(MDL)

    一文搞懂MySQL元數(shù)據(jù)鎖(MDL)

    這篇文章主要為大家詳細介紹了MySQL中元數(shù)據(jù)鎖(MDL)的相關資料,文中的示例代碼講解詳細,對我們學習有一定的借鑒價值,需要的可以參考一下
    2022-09-09
  • 21條MySQL優(yōu)化建議(經(jīng)驗總結)

    21條MySQL優(yōu)化建議(經(jīng)驗總結)

    這篇文章主要介紹了21條MySQL優(yōu)化建議,均來自個人的實戰(zhàn)經(jīng)驗總結,需要的朋友可以參考下
    2014-07-07
  • MySQL數(shù)據(jù)庫學習之排序與單行處理函數(shù)詳解

    MySQL數(shù)據(jù)庫學習之排序與單行處理函數(shù)詳解

    這篇文章主要為大家詳細介紹一下MySQL數(shù)據(jù)庫中排序與單行處理函數(shù)的使用,文中的示例代碼講解詳細,對我們學習MySQL有一定幫助,需要的可以參考一下
    2022-07-07
  • win10安裝zip版MySQL8.0.19的教程詳解

    win10安裝zip版MySQL8.0.19的教程詳解

    這篇文章主要介紹了win10安裝zip版MySQL8.0.19的教程詳細,本文分步驟給大家介紹的非常詳細,具有一定的參考借鑒價值,需要的朋友可以參考下
    2020-02-02
  • MySQL內(nèi)存及虛擬內(nèi)存優(yōu)化設置參數(shù)

    MySQL內(nèi)存及虛擬內(nèi)存優(yōu)化設置參數(shù)

    這篇文章主要介紹了MySQL內(nèi)存及虛擬內(nèi)存優(yōu)化設置參數(shù),需要的朋友可以參考下
    2016-05-05
  • MySql設置指定用戶數(shù)據(jù)庫查看查詢權限

    MySql設置指定用戶數(shù)據(jù)庫查看查詢權限

    這篇文章主要介紹了MySql設置指定用戶數(shù)據(jù)庫查看查詢權限,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
    2020-10-10
  • mysql無法啟動服務及其他問題總結

    mysql無法啟動服務及其他問題總結

    MySQL無法啟動,可能有多種原因?qū)е?本文主要介紹了mysql無法啟動服務及其他問題總結,具有一定的參考價值,感興趣的可以了解一下
    2024-01-01

最新評論