国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

MySQL的索引是數(shù)據(jù)庫(kù)非常重要的知識(shí)點(diǎn)，這些知識(shí)點(diǎn)你都掌握了嗎？如果有幫到你可以點(diǎn)贊收藏呦。

推薦閱讀：這些必會(huì)的計(jì)算機(jī)網(wǎng)絡(luò)知識(shí)點(diǎn)你都掌握了嗎

什么是索引？

百度百科的解釋?zhuān)核饕菍?duì)數(shù)據(jù)庫(kù)表的一列或者多列的值進(jìn)行排序一種結(jié)構(gòu)，使用索引可以快速訪(fǎng)問(wèn)數(shù)據(jù)表中的特定信息。

索引的優(yōu)缺點(diǎn)？

優(yōu)點(diǎn)：

• 大大加快數(shù)據(jù)檢索的速度。
• 將隨機(jī)I/O變成順序I/O(因?yàn)锽+樹(shù)的葉子節(jié)點(diǎn)是連接在一起的)
• 加速表與表之間的連接

缺點(diǎn)：

• 從空間角度考慮，建立索引需要占用物理空間
• 從時(shí)間角度 考慮，創(chuàng)建和維護(hù)索引都需要花費(fèi)時(shí)間，例如對(duì)數(shù)據(jù)進(jìn)行增刪改的時(shí)候都需要維護(hù)索引。

索引的數(shù)據(jù)結(jié)構(gòu)？

索引的數(shù)據(jù)結(jié)構(gòu)主要有B+樹(shù)和哈希表，對(duì)應(yīng)的索引分別為B+樹(shù)索引和哈希索引。InnoDB引擎的索引類(lèi)型有B+樹(shù)索引和哈希索引，默認(rèn)的索引類(lèi)型為B+樹(shù)索引。

• B+樹(shù)索引

  熟悉數(shù)據(jù)結(jié)構(gòu)的同學(xué)都知道，B+樹(shù)、平衡二叉樹(shù)、紅黑樹(shù)都是經(jīng)典的數(shù)據(jù)結(jié)構(gòu)。在B+樹(shù)中，所有的記錄節(jié)點(diǎn)都是按照鍵值大小的順序放在葉子節(jié)點(diǎn)上，如下圖。

從上圖可以看出 ，因?yàn)锽+樹(shù)具有有序性，并且所有的數(shù)據(jù)都存放在葉子節(jié)點(diǎn)，所以查找的效率非常高，并且支持排序和范圍查找。

B+樹(shù)的索引又可以分為主索引和輔助索引。其中主索引為聚簇索引，輔助索引為非聚簇索引。聚簇索引是以主鍵作為B+ 樹(shù)索引的鍵值所構(gòu)成的B+樹(shù)索引，聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)著完整的數(shù)據(jù)記錄；非聚簇索引是以非主鍵的列作為B+樹(shù)索引的鍵值所構(gòu)成的B+樹(shù)索引，非聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)著主鍵值。所以使用非聚簇索引進(jìn)行查詢(xún)時(shí)，會(huì)先找到主鍵值，然后到根據(jù)聚簇索引找到主鍵對(duì)應(yīng)的數(shù)據(jù)域。上圖中葉子節(jié)點(diǎn)存儲(chǔ)的是數(shù)據(jù)記錄，為聚簇索引的結(jié)構(gòu)圖，非聚簇索引的結(jié)構(gòu)圖如下：

上圖中的字母為數(shù)據(jù)的非主鍵的列值，假設(shè)要查詢(xún)?cè)摿兄禐锽的信息，則需先找到主鍵7，在到聚簇索引中查詢(xún)主鍵7所對(duì)應(yīng)的數(shù)據(jù)域。

• 哈希索引

  哈希索引是基于哈希表實(shí)現(xiàn)的，對(duì)于每一行數(shù)據(jù)，存儲(chǔ)引擎會(huì)對(duì)索引列通過(guò)哈希算法進(jìn)行哈希計(jì)算得到哈希碼，并且哈希算法要盡量保證不同的列值計(jì)算出的哈希碼值是不同的，將哈希碼的值作為哈希表的key值，將指向數(shù)據(jù)行的指針作為哈希表的value值。這樣查找一個(gè)數(shù)據(jù)的時(shí)間復(fù)雜度就是o(1)，一般多用于精確查找。

Hash索引和B+樹(shù)的區(qū)別？

因?yàn)閮烧邤?shù)據(jù)結(jié)構(gòu)上的差異導(dǎo)致它們的使用場(chǎng)景也不同，哈希索引一般多用于精確的等值查找，B+索引則多用于除了精確的等值查找外的其他查找。在大多數(shù)情況下，會(huì)選擇使用B+樹(shù)索引。

• 哈希索引不支持排序，因?yàn)楣１硎菬o(wú)序的。
• 哈希索引不支持范圍查找。
• 哈希索引不支持模糊查詢(xún)及多列索引的最左前綴匹配。
• 因?yàn)楣１碇袝?huì)存在哈希沖突，所以哈希索引的性能是不穩(wěn)定的，而B(niǎo)+樹(shù)索引的性能是相對(duì)穩(wěn)定的，每次查詢(xún)都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)

索引的類(lèi)型有哪些?

MySQL主要的索引類(lèi)型主要有FULLTEXT，HASH，BTREE，RTREE。

• FULLTEXT

  FULLTEXT即全文索引，MyISAM存儲(chǔ)引擎和InnoDB存儲(chǔ)引擎在MySQL5.6.4以上版本支持全文索引，一般用于查找文本中的關(guān)鍵字，而不是直接比較是否相等，多在CHAR，VARCHAR，TAXT等數(shù)據(jù)類(lèi)型上創(chuàng)建全文索引。全文索引主要是用來(lái)解決WHERE name LIKE "%zhang%"等針對(duì)文本的模糊查詢(xún)效率低的問(wèn)題。

• HASH

  HASH即哈希索引，哈希索引多用于等值查詢(xún)，時(shí)間復(fù)雜夫?yàn)閛(1)，效率非常高，但不支持排序、范圍查詢(xún)及模糊查詢(xún)等。

• BTREE

  BTREE即B+樹(shù)索引，INnoDB存儲(chǔ)引擎默認(rèn)的索引，支持排序、分組、范圍查詢(xún)、模糊查詢(xún)等，并且性能穩(wěn)定。

• RTREE

  RTREE即空間數(shù)據(jù)索引，多用于地理數(shù)據(jù)的存儲(chǔ)，相比于其他索引，空間數(shù)據(jù)索引的優(yōu)勢(shì)在于范圍查找

索引的種類(lèi)有哪些？

• 主鍵索引：數(shù)據(jù)列不允許重復(fù)，不能為NULL，一個(gè)表只能有一個(gè)主鍵索引
• 組合索引：由多個(gè)列值組成的索引。
• 唯一索引：數(shù)據(jù)列不允許重復(fù)，可以為NULL，索引列的值必須唯一的，如果是組合索引，則列值的組合必須唯一。
• 全文索引：對(duì)文本的內(nèi)容進(jìn)行搜索。
• 普通索引：基本的索引類(lèi)型，可以為NULL

B樹(shù)和B+樹(shù)的區(qū)別？

B樹(shù)和B+樹(shù)最主要的區(qū)別主要有兩點(diǎn)：

• B樹(shù)中的內(nèi)部節(jié)點(diǎn)和葉子節(jié)點(diǎn)均存放鍵和值，而B(niǎo)+樹(shù)的內(nèi)部節(jié)點(diǎn)只有鍵沒(méi)有值，葉子節(jié)點(diǎn)存放所有的鍵和值。

• B＋樹(shù)的葉子節(jié)點(diǎn)是通過(guò)相連在一起的，方便順序檢索。

  兩者的結(jié)構(gòu)圖如下。

  
  

數(shù)據(jù)庫(kù)為什么使用B+樹(shù)而不是B樹(shù)？

• B樹(shù)適用于隨機(jī)檢索，而B(niǎo)+樹(shù)適用于隨機(jī)檢索和順序檢索
• B+樹(shù)的空間利用率更高，因?yàn)锽樹(shù)每個(gè)節(jié)點(diǎn)要存儲(chǔ)鍵和值，而B(niǎo)+樹(shù)的內(nèi)部節(jié)點(diǎn)只存儲(chǔ)鍵，這樣B+樹(shù)的一個(gè)節(jié)點(diǎn)就可以存儲(chǔ)更多的索引，從而使樹(shù)的高度變低，減少了I/O次數(shù)，使得數(shù)據(jù)檢索速度更快。
• B+樹(shù)的葉子節(jié)點(diǎn)都是連接在一起的，所以范圍查找，順序查找更加方便
• B+樹(shù)的性能更加穩(wěn)定，因?yàn)樵贐+樹(shù)中，每次查詢(xún)都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn)，而在B樹(shù)中，要查詢(xún)的值可能不在葉子節(jié)點(diǎn)，在內(nèi)部節(jié)點(diǎn)就已經(jīng)找到。

那在什么情況適合使用B樹(shù)呢，因?yàn)锽樹(shù)的內(nèi)部節(jié)點(diǎn)也可以存儲(chǔ)值，所以可以把一些頻繁訪(fǎng)問(wèn)的值放在距離根節(jié)點(diǎn)比較近的地方，這樣就可以提高查詢(xún)效率。綜上所述，B+樹(shù)的性能更加適合作為數(shù)據(jù)庫(kù)的索引。

什么是聚簇索引，什么是非聚簇索引？

聚簇索引和非聚簇索引最主要的區(qū)別是數(shù)據(jù)和索引是否分開(kāi)存儲(chǔ)。

• 聚簇索引：將數(shù)據(jù)和索引放到一起存儲(chǔ)，索引結(jié)構(gòu)的葉子節(jié)點(diǎn)保留了數(shù)據(jù)行。
• 非聚簇索引：將數(shù)據(jù)進(jìn)和索引分開(kāi)存儲(chǔ)，索引葉子節(jié)點(diǎn)存儲(chǔ)的是指向數(shù)據(jù)行的地址。

在InnoDB存儲(chǔ)引擎中，默認(rèn)的索引為B+樹(shù)索引，利用主鍵創(chuàng)建的索引為主索引，也是聚簇索引，在主索引之上創(chuàng)建的索引為輔助索引，也是非聚簇索引。為什么說(shuō)輔助索引是在主索引之上創(chuàng)建的呢，因?yàn)檩o助索引中的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵。

在MyISAM存儲(chǔ)引擎中，默認(rèn)的索引也是B+樹(shù)索引，但主索引和輔助索引都是非聚簇索引，也就是說(shuō)索引結(jié)構(gòu)的葉子節(jié)點(diǎn)存儲(chǔ)的都是一個(gè)指向數(shù)據(jù)行的地址。并且使用輔助索引檢索無(wú)需訪(fǎng)問(wèn)主鍵的索引。

可以從非常經(jīng)典的兩張圖看看它們的區(qū)別(圖片來(lái)源于網(wǎng)絡(luò))：

非聚簇索引一定會(huì)進(jìn)行回表查詢(xún)嗎？

上面是說(shuō)了非聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵，也就是說(shuō)要先通過(guò)非聚簇索引找到主鍵，再通過(guò)聚簇索引找到主鍵所對(duì)應(yīng)的數(shù)據(jù)，后面這個(gè)再通過(guò)聚簇索引找到主鍵對(duì)應(yīng)的數(shù)據(jù)的過(guò)程就是回表查詢(xún)，那么非聚簇索引就一定會(huì)進(jìn)行回表查詢(xún)嗎？

答案是不一定的，這里涉及到一個(gè)索引覆蓋的問(wèn)題，如果查詢(xún)的數(shù)據(jù)再輔助索引上完全能獲取到便不需要回表查詢(xún)。例如有一張表存儲(chǔ)著個(gè)人信息包括id、name、age等字段。假設(shè)聚簇索引是以ID為鍵值構(gòu)建的索引，非聚簇索引是以name為鍵值構(gòu)建的索引，select id,name from user where name = 'zhangsan';這個(gè)查詢(xún)便不需要進(jìn)行回表查詢(xún)因?yàn)椋ㄟ^(guò)非聚簇索引已經(jīng)能全部檢索出數(shù)據(jù)，這就是索引覆蓋的情況。如果查詢(xún)語(yǔ)句是這樣，select id,name,age from user where name = 'zhangsan';則需要進(jìn)行回表查詢(xún)，因?yàn)橥ㄟ^(guò)非聚簇索引不能檢索出age的值。那應(yīng)該如何解決那呢？只需要將索引覆蓋即可，建立age和name的聯(lián)合索引再使用select id,name,age from user where name = 'zhangsan';進(jìn)行查詢(xún)即可。

所以通過(guò)索引覆蓋能解決非聚簇索引回表查詢(xún)的問(wèn)題。

索引的使用場(chǎng)景有哪些？

• 對(duì)于中大型表建立索引非常有效，對(duì)于非常小的表，一般全部表掃描速度更快些。
• 對(duì)于超大型的表，建立和維護(hù)索引的代價(jià)也會(huì)變高，這時(shí)可以考慮分區(qū)技術(shù)。
• 如何表的增刪改非常多，而查詢(xún)需求非常少的話(huà)，那就沒(méi)有必要建立索引了，因?yàn)榫S護(hù)索引也是需要代價(jià)的。
• 一般不會(huì)出現(xiàn)再where條件中的字段就沒(méi)有必要建立索引了。
• 多個(gè)字段經(jīng)常被查詢(xún)的話(huà)可以考慮聯(lián)合索引。
• 字段多且字段值沒(méi)有重復(fù)的時(shí)候考慮唯一索引。
• 字段多且有重復(fù)的時(shí)候考慮普通索引。

索引的設(shè)計(jì)原則？

• 最適合索引的列是在where后面出現(xiàn)的列或者連接句子中指定的列，而不是出現(xiàn)在SELECT關(guān)鍵字后面的選擇列表中的列。
• 索引列的基數(shù)越大，索引的效果越好，換句話(huà)說(shuō)就是索引列的區(qū)分度越高，索引的效果越好。比如使用性別這種區(qū)分度很低的列作為索引，效果就會(huì)很差，因?yàn)榱械幕鶖?shù)最多也就是三種，大多不是男性就是女性。
• 盡量使用短索引，對(duì)于較長(zhǎng)的字符串進(jìn)行索引時(shí)應(yīng)該指定一個(gè)較短的前綴長(zhǎng)度，因?yàn)檩^小的索引涉及到的磁盤(pán)I/O較少，并且索引高速緩存中的塊可以容納更多的鍵值，會(huì)使得查詢(xún)速度更快。
• 盡量利用最左前綴。
• 不要過(guò)度索引，每個(gè)索引都需要額外的物理空間，維護(hù)也需要花費(fèi)時(shí)間，所以索引不是越多越好。

如何對(duì)索引進(jìn)行優(yōu)化？

對(duì)索引的優(yōu)化其實(shí)最關(guān)鍵的就是要符合索引的設(shè)計(jì)原則和應(yīng)用場(chǎng)景，將不符合要求的索引優(yōu)化成符合索引設(shè)計(jì)原則和應(yīng)用場(chǎng)景的索引。

除了索引的設(shè)計(jì)原則和應(yīng)用場(chǎng)景那幾點(diǎn)外，還可以從以下兩方面考慮。

• 在進(jìn)行查詢(xún)時(shí)，索引列不能是表達(dá)式的一部分，也不能是函數(shù)的參數(shù)，因?yàn)檫@樣無(wú)法使用索引。例如select * from table_name where a + 1 = 2
• 將區(qū)分度最高的索引放在前面
• 盡量少使用select*

索引的使用場(chǎng)景、索引的設(shè)計(jì)原則和如何對(duì)索引進(jìn)行優(yōu)化可以看成一個(gè)問(wèn)題。

如何創(chuàng)建/刪除索引？

創(chuàng)建索引：

• 使用CREATE INDEX 語(yǔ)句

  CREATE INDEX index_name ON table_name (column_list);

• 在CREATE TABLE時(shí)創(chuàng)建

  	CREATE TABLE user(
  	id INT PRIMARY KEY,
  	information text,
  	FULLTEXT KEY (information)
  );
  

• 使用ALTER TABLE創(chuàng)建索引

  ALTER TABLE table_name ADD INDEX index_name (column_list);

刪除索引：

• 刪除主鍵索引

  alter table 表名 drop primary key

• 刪除其他索引

  alter table 表名 drop key 索引名

使用索引查詢(xún)時(shí)性能一定會(huì)提升嗎？

不一定，前面在索引的使用場(chǎng)景和索引的設(shè)計(jì)原則中已經(jīng)提到了如何合理地使用索引，因?yàn)閯?chuàng)建和維護(hù)索引需要花費(fèi)空間和時(shí)間上的代價(jià)，如果不合理地使用索引反而會(huì)使查詢(xún)性能下降。

什么是前綴索引？

前綴索引是指對(duì)文本或者字符串的前幾個(gè)字符建立索引，這樣索引的長(zhǎng)度更短，查詢(xún)速度更快。

使用場(chǎng)景：前綴的區(qū)分度比較高的情況下。

建立前綴索引的方式

ALTER TABLE table_name ADD KEY(column_name(prefix_length));

這里面有個(gè)prefix_length參數(shù)很難確定，這個(gè)參數(shù)就是前綴長(zhǎng)度的意思。通?？梢允褂靡韵路椒ㄟM(jìn)行確定，先計(jì)算全列的區(qū)分度

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

然后在計(jì)算前綴長(zhǎng)度為多少時(shí)和全列的區(qū)分度最相似。

SELECT COUNT(DISTINCT LEFT(column_name, prefix_length)) / COUNT(*) FROM table_name;

不斷地調(diào)整prefix_length的值，直到和全列計(jì)算出區(qū)分度相近。

什么是最左匹配原則？

最左匹配原則：從最左邊為起點(diǎn)開(kāi)始連續(xù)匹配，遇到范圍查詢(xún)（<、>、between、like）會(huì)停止匹配。

例如建立索引(a,b,c)，大家可以猜測(cè)以下幾種情況是否用到了索引。

• 第一種

  select * from table_name where a = 1 and b = 2 and c = 3 
  select * from table_name where b = 2 and a = 1 and c = 3
  

  上面兩次查詢(xún)過(guò)程中所有值都用到了索引，where后面字段調(diào)換不會(huì)影響查詢(xún)結(jié)果，因?yàn)镸ySQL中的優(yōu)化器會(huì)自動(dòng)優(yōu)化查詢(xún)順序。

• 第二種

  select * from table_name where a = 1
  select * from table_name where a = 1 and b = 2  
  select * from table_name where a = 1 and b = 2 and c = 3
  

  答案是三個(gè)查詢(xún)語(yǔ)句都用到了索引，因?yàn)槿齻€(gè)語(yǔ)句都是從最左開(kāi)始匹配的。

• 第三種

  select * from table_name where  b = 1 
  select * from table_name where  b = 1 and c = 2 
  

  答案是這兩個(gè)查詢(xún)語(yǔ)句都沒(méi)有用到索引，因?yàn)椴皇菑淖钭筮呴_(kāi)始匹配的

• 第四種

  select * from table_name where a = 1 and c = 2 
  

  這個(gè)查詢(xún)語(yǔ)句只有a列用到了索引，c列沒(méi)有用到索引，因?yàn)橹虚g跳過(guò)了b列，不是從最左開(kāi)始連續(xù)匹配的。

• 第五種

  select * from table_name where  a = 1 and b < 3 and c < 1
  

  這個(gè)查詢(xún)中只有a列和b列使用到了索引，而c列沒(méi)有使用索引，因?yàn)楦鶕?jù)最左匹配查詢(xún)?cè)瓌t，遇到范圍查詢(xún)會(huì)停止。

• 第六種

  select * from table_name where a like 'ab%'; 
  select * from table_name where  a like '%ab'
  select * from table_name where  a like '%ab%'
  

  對(duì)于列為字符串的情況，只有前綴匹配可以使用索引，中綴匹配和后綴匹配只能進(jìn)行全表掃描。

索引在什么情況下會(huì)失效？

在上面介紹了幾種不符合最左匹配原則的情況會(huì)導(dǎo)致索引失效，除此之外，以下這幾種情況也會(huì)導(dǎo)致索引失效。

• 條件中有or，例如select * from table_name where a = 1 or b = 3
• 在索引上進(jìn)行計(jì)算會(huì)導(dǎo)致索引失效，例如select * from table_name where a + 1 = 2
• 在索引的類(lèi)型上進(jìn)行數(shù)據(jù)類(lèi)型的隱形轉(zhuǎn)換，會(huì)導(dǎo)致索引失效，例如字符串一定要加引號(hào)，假設(shè) select * from table_name where a = '1' 會(huì)使用到索引，如果寫(xiě)成select * from table_name where a = 1 則會(huì)導(dǎo)致索引失效。
• 在索引中使用函數(shù)會(huì)導(dǎo)致索引失效，例如select * from table_name where abs(a) = 1
• 在使用like查詢(xún)時(shí)以%開(kāi)頭會(huì)導(dǎo)致索引失效
• 索引上使用！、=、<>進(jìn)行判斷時(shí)會(huì)導(dǎo)致索引失效，例如select * from table_name where a != 1
• 索引字段上使用 is null/is not null判斷時(shí)會(huì)導(dǎo)致索引失效，例如select * from table_name where a is null