久久伊人啪啪啪,中文字幕日韩免费首页,伊人久久97

(同時(shí)再次強(qiáng)調(diào)，這幾篇關(guān)于MySQL的探究都是基于5.7版本，相關(guān)總結(jié)與結(jié)論不一定適用于其他版本)

MySQL官方文檔中(https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html)有這樣一段描述：

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. But unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

就是說(shuō)提高查詢(xún)性能最直接有效的方法就是建立索引，但是不必要的索引會(huì)浪費(fèi)空間，同時(shí)也增加了額外的時(shí)間成本去判斷應(yīng)該走哪個(gè)索引，此外，索引還會(huì)增加插入、更新、刪除數(shù)據(jù)的成本，因?yàn)樽鲞@些操作的同時(shí)還要去維護(hù)(更新)索引樹(shù)。因此，應(yīng)該學(xué)會(huì)使用最佳索引集來(lái)優(yōu)化查詢(xún)。

什么是索引#

在MySQL中，索引(Index)是幫助高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。這種數(shù)據(jù)結(jié)構(gòu)MySQL中最常用的就是B+樹(shù)(B+Tree)。

Indexes are used to find rows with specific column values quickly. Without an index, MySQL must begin with the first row and then read through the entire table to find the relevant rows.

就好比給你一本書(shū)和一篇文章標(biāo)題，如果沒(méi)有目錄，讓你找此標(biāo)題對(duì)應(yīng)的文章，可能需要從第一頁(yè)翻到最后一頁(yè)；如果有目錄大綱，你可能只需要在目錄頁(yè)尋找此標(biāo)題，然后迅速定位文章。

這里我們可以把書(shū)(book)看成是MySQL中的table，把文章(article)看成是table中的一行記錄，即row，文章標(biāo)題(title)看成row中的一列column，目錄自然就是對(duì)title列建立的索引index了，這樣根據(jù)文章標(biāo)題從書(shū)中檢索文章就對(duì)應(yīng)sql語(yǔ)句select * from book where title = ?，相應(yīng)的，書(shū)中每增加一篇文章(即insert into book (title, ...) values ('華山論劍', ...))，都需要維護(hù)一下目錄，這樣才能從目錄中找到新增的文章華山論劍，這一操作對(duì)應(yīng)的是MySQL中每插入(insert)一條記錄需要維護(hù)title列的索引樹(shù)(B+Tree)。

為什么使用B+Tree#

首先需要澄清的一點(diǎn)是，MySQL跟B+樹(shù)沒(méi)有直接的關(guān)系，真正與B+樹(shù)有關(guān)系的是MySQL的默認(rèn)存儲(chǔ)引擎InnoDB，MySQL中存儲(chǔ)引擎的主要作用是負(fù)責(zé)數(shù)據(jù)的存儲(chǔ)和提取，除了InnoDB之外，MySQL中也支持比如MyISAM等其他存儲(chǔ)引擎(詳情見(jiàn)https://dev.mysql.com/doc/refman/5.7/en/storage-engine-setting.html)作為表的底層存儲(chǔ)引擎。

Copy

mysql> show engines;+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine? ? ? ? ? ? | Support | Comment? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | Transactions | XA? | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MRG_MYISAM? ? ? ? | YES? ? | Collection of identical MyISAM tables? ? ? ? ? ? ? ? ? ? ? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || CSV? ? ? ? ? ? ? ? | YES? ? | CSV storage engine? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || PERFORMANCE_SCHEMA | YES? ? | Performance Schema? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || BLACKHOLE? ? ? ? ? | YES? ? | /dev/null storage engine (anything you write to it disappears) | NO? ? ? ? ? | NO? | NO? ? ? ? || InnoDB? ? ? ? ? ? | DEFAULT | Supports transactions, row-level locking, and foreign keys? ? | YES? ? ? ? ? | YES? | YES? ? ? ? || MyISAM? ? ? ? ? ? | YES? ? | MyISAM storage engine? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || ARCHIVE? ? ? ? ? ? | YES? ? | Archive storage engine? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || MEMORY? ? ? ? ? ? | YES? ? | Hash based, storedinmemory, usefulfortemporary tables? ? ? | NO? ? ? ? ? | NO? | NO? ? ? ? || FEDERATED? ? ? ? ? | NO? ? ? | Federated MySQL storage engine? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | NULL? ? ? ? | NULL | NULL? ? ? |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

提到索引，我們可能會(huì)立馬想到下面幾種數(shù)據(jù)結(jié)構(gòu)來(lái)實(shí)現(xiàn)。

(1) 哈希表

哈希雖然能夠提供O(1)的單數(shù)據(jù)行的查詢(xún)性能，但是對(duì)于范圍查詢(xún)和排序卻無(wú)法很好支持，需全表掃描。

(2) 紅黑樹(shù)

紅黑樹(shù)(Red Black Tree)是一種自平衡二叉查找樹(shù)，在進(jìn)行插入和刪除操作時(shí)通過(guò)特定操作保持二叉查找樹(shù)的平衡，從而獲得較高的查找性能。

一般來(lái)說(shuō)，索引本身也很大，往往不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤(pán)上。這樣的話(huà)，索引查找過(guò)程中就要產(chǎn)生磁盤(pán)I/O消耗，相對(duì)于內(nèi)存存取，I/O存取的消耗遠(yuǎn)遠(yuǎn)高于內(nèi)存，所以評(píng)價(jià)一個(gè)數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是查找過(guò)程中磁盤(pán)I/O次數(shù)。換句話(huà)說(shuō)，索引的結(jié)構(gòu)組織要盡量減少查找過(guò)程中磁盤(pán)I/O的次數(shù)。

在這里，磁盤(pán)I/O的次數(shù)取決于樹(shù)的高度，所以，在數(shù)據(jù)量較大時(shí)，紅黑樹(shù)會(huì)因樹(shù)的高度較大而造成磁盤(pán)IO較多，從而影響查詢(xún)效率。

(3) B-Tree

B樹(shù)中的B代表平衡(Balance)，而不是二叉(Binary)，B樹(shù)是從平衡二叉樹(shù)演化而來(lái)的。

為了降低樹(shù)的高度(也就是減少磁盤(pán)I/O次數(shù))，把原來(lái)瘦高的樹(shù)結(jié)構(gòu)變得矮胖，B樹(shù)會(huì)在每個(gè)節(jié)點(diǎn)存儲(chǔ)多個(gè)元素(紅黑樹(shù)每個(gè)節(jié)點(diǎn)只會(huì)存儲(chǔ)一個(gè)元素)，并且節(jié)點(diǎn)中的元素從左到右遞增排列。如下圖所示：

B-Tree在查詢(xún)的時(shí)候比較次數(shù)其實(shí)不比二叉查找樹(shù)少，但在內(nèi)存中的大小比較、二分查找的耗時(shí)相比磁盤(pán)IO耗時(shí)幾乎可以忽略。?B-Tree大大降低了樹(shù)的高度，所以也就極大地提升了查找性能。

(4) B+Tree

B+Tree是在B-Tree基礎(chǔ)上進(jìn)一步優(yōu)化，使其更適合實(shí)現(xiàn)存儲(chǔ)索引結(jié)構(gòu)。InnoDB存儲(chǔ)引擎就是用B+Tree實(shí)現(xiàn)其索引結(jié)構(gòu)。

B-Tree結(jié)構(gòu)圖中可以看到每個(gè)節(jié)點(diǎn)中不僅包含數(shù)據(jù)的key值，還有data值。而每一個(gè)節(jié)點(diǎn)的存儲(chǔ)空間是有限的，如果data值較大時(shí)將會(huì)導(dǎo)致每個(gè)節(jié)點(diǎn)能存儲(chǔ)的key的數(shù)量很小，這樣會(huì)導(dǎo)致B-Tree的高度變大，增加了查詢(xún)時(shí)的磁盤(pán)I/O次數(shù)，進(jìn)而影響查詢(xún)性能。在B+Tree中，所有data值都是按照鍵值大小順序存放在同一層的葉子節(jié)點(diǎn)上，而非葉子節(jié)點(diǎn)上只存儲(chǔ)key值信息，這樣可以增大每個(gè)非葉子節(jié)點(diǎn)存儲(chǔ)的key值數(shù)量，降低B+Tree的高度，提高效率。

這里補(bǔ)充一點(diǎn)相關(guān)知識(shí)?在計(jì)算機(jī)中，磁盤(pán)往往不是嚴(yán)格按需讀取，而是每次都會(huì)預(yù)讀，即使只需要一個(gè)字節(jié)，磁盤(pán)也會(huì)從這個(gè)位置開(kāi)始，順序向后讀取一定長(zhǎng)度的數(shù)據(jù)放入內(nèi)存。這樣做的理論依據(jù)是計(jì)算機(jī)科學(xué)中著名的局部性原理：

當(dāng)一個(gè)數(shù)據(jù)被用到時(shí)，其附近的數(shù)據(jù)也通常會(huì)馬上被使用。

由于磁盤(pán)順序讀取的效率很高（不需要尋道時(shí)間，只需很少的旋轉(zhuǎn)時(shí)間），因此對(duì)于具有局部性的程序來(lái)說(shuō)，預(yù)讀可以提高I/O效率。預(yù)讀的長(zhǎng)度一般為頁(yè)(page)的整數(shù)倍。

頁(yè)是計(jì)算機(jī)管理存儲(chǔ)器的邏輯塊，硬件及操作系統(tǒng)往往將主存和磁盤(pán)存儲(chǔ)區(qū)分割為連續(xù)的大小相等的塊，每個(gè)存儲(chǔ)塊稱(chēng)為一頁(yè)(許多操作系統(tǒng)的頁(yè)默認(rèn)大小為4KB)，主存和磁盤(pán)以頁(yè)為單位交換數(shù)據(jù)。當(dāng)程序要讀取的數(shù)據(jù)不在主存中時(shí)，會(huì)觸發(fā)一個(gè)缺頁(yè)異常，此時(shí)操作系統(tǒng)會(huì)向磁盤(pán)發(fā)出讀盤(pán)信號(hào)，磁盤(pán)會(huì)找到數(shù)據(jù)的起始位置并向后連續(xù)讀取一頁(yè)或幾頁(yè)載入內(nèi)存中，然后異常返回，程序繼續(xù)運(yùn)行。(如下命令可以查看操作系統(tǒng)的默認(rèn)頁(yè)大小)

Copy

$ getconf PAGE_SIZE

4096

數(shù)據(jù)庫(kù)系統(tǒng)的設(shè)計(jì)者巧妙利用了磁盤(pán)預(yù)讀原理，將一個(gè)節(jié)點(diǎn)的大小設(shè)為操作系統(tǒng)的頁(yè)大小的整數(shù)倍，這樣每個(gè)節(jié)點(diǎn)只需要一次I/O就可以完全載入。

InnoDB存儲(chǔ)引擎中也有頁(yè)(Page)的概念，頁(yè)是其磁盤(pán)管理的最小單位。InnoDB存儲(chǔ)引擎中默認(rèn)每個(gè)頁(yè)的大小為16KB。

Copy

一般表的主鍵類(lèi)型為INT（占4個(gè)字節(jié)）或BIGINT（占8個(gè)字節(jié)），指針類(lèi)型也一般為4或8個(gè)字節(jié)，也就是說(shuō)一個(gè)頁(yè)（B+Tree中的一個(gè)節(jié)點(diǎn)）中大概存儲(chǔ)16KB/(8B+8B)=1K個(gè)鍵值（因?yàn)槭枪乐担瑸榉奖阌?jì)算，這里的K取值為10^3）。也就是說(shuō)一個(gè)深度為3的B+Tree索引可以維護(hù)10^3 * 10^3 * 10^3 = 10億條記錄。

B+Tree的高度一般都在2到4層。mysql的InnoDB存儲(chǔ)引擎在設(shè)計(jì)時(shí)是將根節(jié)點(diǎn)常駐內(nèi)存的，也就是說(shuō)查找某一鍵值的行記錄時(shí)最多只需要1到3次磁盤(pán)I/O操作。

隨機(jī)I/O對(duì)于MySQL的查詢(xún)性能影響會(huì)非常大，而順序讀取磁盤(pán)中的數(shù)據(jù)會(huì)很快，由此我們也應(yīng)該盡量減少隨機(jī)I/O的次數(shù)，這樣才能提高性能。在B-Tree中由于所有的節(jié)點(diǎn)都可能包含目標(biāo)數(shù)據(jù)，我們總是要從根節(jié)點(diǎn)向下遍歷子樹(shù)查找滿(mǎn)足條件的數(shù)據(jù)行，這會(huì)帶來(lái)大量的隨機(jī)I/O，而B(niǎo)+Tree所有的數(shù)據(jù)行都存儲(chǔ)在葉子節(jié)點(diǎn)中，而這些葉子節(jié)點(diǎn)通過(guò)雙向鏈表依次按順序連接，當(dāng)我們?cè)贐+樹(shù)遍歷數(shù)據(jù)(比如說(shuō)范圍查詢(xún))時(shí)可以直接在多個(gè)葉子節(jié)點(diǎn)之間進(jìn)行跳轉(zhuǎn)，保證順序、倒序遍歷的性能。

另外，對(duì)以上提到的數(shù)據(jù)結(jié)構(gòu)不熟悉的朋友，這里推薦一個(gè)在線(xiàn)數(shù)據(jù)結(jié)構(gòu)可視化演示工具，有助于快速理解這些數(shù)據(jù)結(jié)構(gòu)的機(jī)制：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

主鍵索引#

上面也有提及，在MySQL中，索引屬于存儲(chǔ)引擎級(jí)別的概念。不同存儲(chǔ)引擎對(duì)索引的實(shí)現(xiàn)方式是不同的，這里主要看下MyISAM和InnoDB兩種存儲(chǔ)引擎的索引實(shí)現(xiàn)方式。

MyISAM索引實(shí)現(xiàn)#

MyISAM引擎使用B+Tree作為索引結(jié)構(gòu)時(shí)葉子節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址。如下圖所示：

由上圖可以看出：MyISAM索引文件和數(shù)據(jù)文件是分離的，索引文件僅保存數(shù)據(jù)記錄的地址，因此MyISAM的索引方式也叫做非聚集的，之所以這么稱(chēng)呼是為了與InnoDB的聚集索引區(qū)分。

InnoDB索引實(shí)現(xiàn)#

InnoDB的主鍵索引也使用B+Tree作為索引結(jié)構(gòu)時(shí)的實(shí)現(xiàn)方式卻與MyISAM截然不同。InnoDB的數(shù)據(jù)文件本身就是索引文件。在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)，這棵樹(shù)的葉子節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄，這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引。

InnoDB存儲(chǔ)引擎中的主鍵索引(primary key)又叫做聚集索引(clustered index)。因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集，所以InnoDB要求表必須有主鍵（MyISAM可以沒(méi)有），如果沒(méi)有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵，如果不存在這種列，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵，這個(gè)字段長(zhǎng)度為6個(gè)字節(jié)，類(lèi)型為長(zhǎng)整形。(詳情見(jiàn)官方文檔：https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html)

聚集索引這種實(shí)現(xiàn)方式使得按主鍵搜索十分高效，直接能查出整行數(shù)據(jù)。

在InnoDB中，用非單調(diào)遞增的字段作為主鍵不是個(gè)好主意，因?yàn)镮nnoDB數(shù)據(jù)文件本身是一棵B+Tree，非單增的主鍵會(huì)造成在插入新記錄時(shí)數(shù)據(jù)文件為了維持B+Tree的特性而頻繁的分裂調(diào)整，十分低效，因而使用遞增字段作為主鍵則是一個(gè)很好的選擇。

非主鍵索引#

MyISAM索引實(shí)現(xiàn)#

MyISAM中，主鍵索引和非主鍵索引（Secondary key，也有人叫做輔助索引）在結(jié)構(gòu)上沒(méi)有任何區(qū)別，只是主鍵索引要求key是唯一的，而輔助索引的key可以重復(fù)。這里不再多加敘述。

InnoDB索引實(shí)現(xiàn)#

InnoDB的非主鍵索引data域存儲(chǔ)相應(yīng)記錄主鍵的值。換句話(huà)說(shuō)，InnoDB的所有非主鍵索引都引用主鍵的值作為data域。如下圖所示：

由上圖可知：使用非主鍵索引搜索時(shí)需要檢索兩遍索引，首先檢索非主鍵索引獲得主鍵(primary key)，然后用主鍵到主鍵索引樹(shù)中檢索獲得完整記錄。

那么為什么非主鍵索引結(jié)構(gòu)葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值，而不像主鍵索引那樣直接存儲(chǔ)完整的一行數(shù)據(jù)，這樣就能避免回表二次檢索？顯然，這樣做一方面節(jié)省了大量的存儲(chǔ)空間，另一方面多份冗余數(shù)據(jù)，更新數(shù)據(jù)的效率肯定低下，另外保證數(shù)據(jù)的一致性是個(gè)麻煩事。

到了這里，也很容易明白為什么不建議使用過(guò)長(zhǎng)的字段作為主鍵，因?yàn)樗械姆侵麈I索引都引用主鍵值，過(guò)長(zhǎng)的主鍵值會(huì)讓非主鍵索引變得過(guò)大。

聯(lián)合索引#

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

比如INDEX idx_book_id_hero_name (book_id, hero_name) USING BTREE，即對(duì)book_id, hero_name兩列建立了一個(gè)聯(lián)合索引。

A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.

聯(lián)合索引是多列按照次序一列一列比較大小，拿idx_book_id_hero_name這個(gè)聯(lián)合索引來(lái)說(shuō)，先比較book_id，book_id小的排在左邊，book_id大的排在右邊，book_id相同時(shí)再比較hero_name。如下圖所示：

了解了聯(lián)合索引的結(jié)構(gòu)，就能引入最左前綴法則：

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

就是說(shuō)聯(lián)合索引中的多列是按照列的次序排列的，如果查詢(xún)的時(shí)候不能滿(mǎn)足列的次序，比如說(shuō)where條件中缺少col1 = ?，直接就是col2 = ? and col3 = ?，那么就走不了聯(lián)合索引，從上面聯(lián)合索引的結(jié)構(gòu)圖應(yīng)該能明顯看出，只有col2列無(wú)法通過(guò)索引樹(shù)檢索符合條件的數(shù)據(jù)。

根據(jù)最左前綴法則，我們知道對(duì)INDEX idx_book_id_hero_name (book_id, hero_name)來(lái)說(shuō)，where book_id = ? and hero_name = ?的查詢(xún)來(lái)說(shuō)，肯定可以走索引，但是如果是where hero_name = ? and book_id = ?呢，表面上看起來(lái)不符合最左前綴法則啊，但MySQL優(yōu)化器會(huì)根據(jù)已有的索引，調(diào)整查詢(xún)條件中這兩列的順序，讓它符合最左前綴法則，走索引，這里也就回答了上篇《一文學(xué)會(huì)MySQL的explain工具》中為什么用show warnings命令查看時(shí)，where中的兩個(gè)過(guò)濾條件hero_name、book_id先后順序被調(diào)換了。

至于對(duì)聯(lián)合索引中的列進(jìn)行范圍查詢(xún)等各種情況，都可以先想聯(lián)合索引的結(jié)構(gòu)是如何創(chuàng)建出來(lái)的，然后看過(guò)濾條件是否滿(mǎn)足最左前綴法則。比如說(shuō)范圍查詢(xún)時(shí)，范圍列可以用到索引（必須是最左前綴），但是范圍列后面的列無(wú)法用到索引。同時(shí)，索引最多用于一個(gè)范圍列，因此如果查詢(xún)條件中有兩個(gè)范圍列則無(wú)法全用到索引。

優(yōu)化建議#

主鍵的選擇#

在使用InnoDB存儲(chǔ)引擎時(shí)，如果沒(méi)有特別的需要，盡量使用一個(gè)與業(yè)務(wù)無(wú)關(guān)的遞增字段作為主鍵，主鍵字段不宜過(guò)長(zhǎng)。原因上面在講索引結(jié)構(gòu)時(shí)已提過(guò)。比如說(shuō)常用雪花算法生成64bit大小的整數(shù)(占8個(gè)字節(jié)，用BIGINT類(lèi)型)作為主鍵就是一個(gè)不錯(cuò)的選擇。

索引的選擇#

(1) 表記錄比較少的時(shí)候，比如說(shuō)只有幾百條記錄的表，對(duì)一些列建立索引的意義可能并不大，所以表記錄不大時(shí)酌情考慮索引。但是業(yè)務(wù)上具有唯一特性的字段，即使是多個(gè)字段的組合，也建議使用唯一索引(UNIQUE KEY)。

(2) 當(dāng)索引的選擇性非常低時(shí)，索引的意義可能也不大。所謂索引的選擇性(Selectivity)，是指不重復(fù)的索引值(也叫基數(shù)Cardinality)與表記錄數(shù)的比值，即count(distinct 列名)/count(*)，常見(jiàn)的場(chǎng)景就是有一列status標(biāo)識(shí)數(shù)據(jù)行的狀態(tài)，可能status非0即1，總數(shù)據(jù)100萬(wàn)行有50萬(wàn)行status為0，50萬(wàn)行status為1，那么是否有必要對(duì)這一列單獨(dú)建立索引呢？

An index is best used when you need to select a small number of rows in comparison to the total rows.

這句話(huà)我摘自stackoverflow上《MySQL: low selectivity columns = how to index?》下面一個(gè)人的回答。(詳情見(jiàn)：https://stackoverflow.com/questions/2386852/mysql-low-cardinality-selectivity-columns-how-to-index)

對(duì)于上面說(shuō)的status非0即1，而且這兩種情況分布比較均勻的情況，索引可能并沒(méi)有實(shí)際意義，實(shí)際查詢(xún)時(shí)，MySQL優(yōu)化器在計(jì)算全表掃描和索引樹(shù)掃描代價(jià)后，可能會(huì)放棄走索引，因?yàn)橄葟膕tatus索引樹(shù)中遍歷出來(lái)主鍵值，再去主鍵索引樹(shù)中查最終數(shù)據(jù)，代價(jià)可能比全表掃描還高。

但是如果對(duì)于status為1的數(shù)據(jù)只有1萬(wàn)行，其他99萬(wàn)行數(shù)據(jù)status為0的情況呢，你怎么看？歡迎有興趣的朋友在文章下面留言討論！

補(bǔ)充: 關(guān)于MySQL如何選擇走不走索引或者選擇走哪個(gè)最佳索引，可以使用MySQL自帶的trace工具一探究竟。具體使用見(jiàn)下面的官方文檔。

https://dev.mysql.com/doc/internals/en/optimizer-tracing.html

https://dev.mysql.com/doc/refman/5.7/en/information-schema-optimizer-trace-table.html

使用方法：

Copy

mysql>setsession optimizer_trace="enabled=on",end_markers_in_json=on;mysql> select * from tb_herowherehero_id = 1;mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;

注意：開(kāi)啟trace工具會(huì)影響MySQL性能，所以只能臨時(shí)分析sql使用，用完之后應(yīng)當(dāng)立即關(guān)閉

Copy

mysql>setsession optimizer_trace="enabled=off";

(3) 在varchar類(lèi)型字段上建立索引時(shí)，建議指定索引長(zhǎng)度，有些時(shí)候可能沒(méi)必要對(duì)全字段建立索引，根據(jù)實(shí)際文本區(qū)分度決定索引長(zhǎng)度即可【說(shuō)明：索引的長(zhǎng)度與區(qū)分度是一對(duì)矛盾體，一般對(duì)字符串類(lèi)型數(shù)據(jù)，長(zhǎng)度為20的索引，區(qū)分度會(huì)高達(dá)90%以上，可以使用count(distinct left(列名, 索引長(zhǎng)度))/count(*)來(lái)確定區(qū)分度】。

這種指定索引長(zhǎng)度的索引叫做前綴索引(詳情見(jiàn)https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-prefix)。

With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column. Indexing only a prefix of column values in this way can make the index file much smaller. When you index a BLOB or TEXT column, you must specify a prefix length for the index.

前綴索引語(yǔ)法如下：

Copy

mysql> alter table tb_hero add index idx_hero_name_skill2 (hero_name, skill(2));

前綴索引兼顧索引大小和查詢(xún)速度，但是其缺點(diǎn)是不能用于group by和order by操作，也不能用于covering index（即當(dāng)索引本身包含查詢(xún)所需全部數(shù)據(jù)時(shí)，不再訪(fǎng)問(wèn)數(shù)據(jù)文件本身）。

(4) 當(dāng)查詢(xún)語(yǔ)句的where條件或group by、order by含多列時(shí)，可根據(jù)實(shí)際情況優(yōu)先考慮聯(lián)合索引(multiple-column index)，這樣可以減少單列索引(single-column index)的個(gè)數(shù)，有助于高效查詢(xún)。

If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

建立聯(lián)合索引時(shí)要特別注意column的次序，應(yīng)結(jié)合上面提到的最左前綴法則以及實(shí)際的過(guò)濾、分組、排序需求。區(qū)分度最高的建議放最左邊。

說(shuō)明：

order by的字段可以作為聯(lián)合索引的一部分，并且放在最后，避免出現(xiàn)file_sort的情況，影響查詢(xún)性能。正例：where a=? and b=? order by c會(huì)走索引idx_a_b_c，但是WHERE a>10 order by b卻無(wú)法完全使用上索引idx_a_b，只會(huì)使用上聯(lián)合索引的第一列a

存在非等號(hào)和等號(hào)混合時(shí)，在建聯(lián)合索引時(shí)，應(yīng)該把等號(hào)條件的列前置。如：where c>? and d=?那么即使c的區(qū)分度更高，也應(yīng)該把d放在索引的最前列，即索引idx_d_c

如果where a=? and b=?，如果a列的幾乎接近于唯一值，那么只需要建立單列索引idx_a即可

order by與group by#

盡量在索引列上完成分組、排序，遵循索引最左前綴法則，如果order by的條件不在索引列上，就會(huì)產(chǎn)生Using filesort，降低查詢(xún)性能。

分頁(yè)查詢(xún)#

MySQL分頁(yè)查詢(xún)大多數(shù)寫(xiě)法可能如下：

Copy

mysql> select * from tb_herolimitoffset,N;

MySQL并不是跳過(guò)offset行，而是取offset+N行，然后返回放棄前offset行，返回N行，那當(dāng)offset特別大的時(shí)候，效率就非常的低下。

可以對(duì)超過(guò)特定閾值的頁(yè)數(shù)進(jìn)行SQL改寫(xiě)如下：

先快速定位需要獲取的id段，然后再關(guān)聯(lián)

Copy

mysql> select a.* from tb_hero a, (select hero_id from tb_herowhere條件limit100000,20 ) bwherea.hero_id = b.hero_id;

或者這種寫(xiě)法

Copy

mysql> select a.* from tb_hero a inner join (select hero_id from tb_herowhere條件limit100000,20) b on a.hero_id = b.hero_id;

多表join#

(1) 需要join的字段，數(shù)據(jù)類(lèi)型必須絕對(duì)一致；

(2) 多表join時(shí)，保證被關(guān)聯(lián)的字段有索引

覆蓋索引#

利用覆蓋索引(covering index)來(lái)進(jìn)行查詢(xún)操作，避免回表，從而增加磁盤(pán)I/O。換句話(huà)說(shuō)就是，盡可能避免select *語(yǔ)句，只選擇必要的列，去除無(wú)用的列。

An index that includes all the columns retrieved by a query. Instead of using the index values as pointers to find the full table rows, the query returns values from the index structure, saving disk I/O. InnoDB can apply this optimization technique to more indexes than MyISAM can, because InnoDB secondary indexes also include the primary key columns. InnoDB cannot apply this technique for queries against tables modified by a transaction, until that transaction ends.

Any column index or composite index could act as a covering index, given the right query. Design your indexes and queries to take advantage of this optimization technique wherever possible.

當(dāng)索引本身包含查詢(xún)所需全部列時(shí)，無(wú)需回表查詢(xún)完整的行記錄。對(duì)于InnoDB來(lái)說(shuō)，非主鍵索引中包含了所有的索引列以及主鍵值，查詢(xún)的時(shí)候盡量用這種特性避免回表操作，數(shù)據(jù)量很大時(shí)，查詢(xún)性能提升很明顯。

in和exsits#

原則：小表驅(qū)動(dòng)大表，即小的數(shù)據(jù)集驅(qū)動(dòng)大的數(shù)據(jù)集

(1) 當(dāng)A表的數(shù)據(jù)集大于B表的數(shù)據(jù)集時(shí)，in優(yōu)于exists

Copy

mysql> select * from Awhereidin(select id from B)

(2) 當(dāng)A表的數(shù)據(jù)集小于B表的數(shù)據(jù)集時(shí)，exists優(yōu)于in

Copy

mysql> select * from Awhereexists (select 1 from BwhereB.id = A.id)

like#

索引文件具有B+Tree最左前綴匹配特性，如果左邊的值未確定，那么無(wú)法使用索引，所以應(yīng)盡量避免左模糊(即%xxx)或者全模糊(即%xxx%)。

Copy

mysql> select * from tb_herowherehero_name like'%無(wú)%';+---------+-----------+--------------+---------+| hero_id | hero_name | skill? ? ? ? | book_id |+---------+-----------+--------------+---------+|? ? ? 3 | 張無(wú)忌? ? | 九陽(yáng)神功? ? |? ? ? 3 ||? ? ? 5 | 花無(wú)缺? ? | 移花接玉? ? |? ? ? 5 |+---------+-----------+--------------+---------+2 rowsinset(0.00 sec)mysql> explain select * from tb_herowherehero_name like'%無(wú)%';+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table? | partitions |type| possible_keys | key? | key_len | ref? | rows | filtered | Extra? ? ? |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+|? 1 | SIMPLE? ? ? | tb_hero | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL |? ? 6 |? ? 16.67 | Usingwhere|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+1 rowinset, 1 warning (0.00 sec)

可以看出全模糊查詢(xún)時(shí)全表掃了，這個(gè)時(shí)候使用覆蓋索引的特性，只選擇索引字段可以有所優(yōu)化。如下：

Copy

count(*)#

阿里巴巴Java開(kāi)發(fā)手冊(cè)中有這樣的規(guī)約：

不要使用count(列名)或count(常量)來(lái)替代count(*)，count(*)是SQL92定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法，跟數(shù)據(jù)庫(kù)無(wú)關(guān)，跟NULL和非NULL無(wú)關(guān)【說(shuō)明：count(*)會(huì)統(tǒng)計(jì)值為NULL的行，而count(列名)不會(huì)統(tǒng)計(jì)此列為NULL值的行】。

count(distinct col)計(jì)算該列除NULL之外的不重復(fù)行數(shù)，注意count(distinct col1, col2)如果其中一列全為NULL，那么即使另一列有不同的值，也返回為0

截取一段官方文檔對(duì)count的描述(具體見(jiàn)：https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_count)

COUNT(expr): Returns a count of the number of non-NULL values of expr in the rows.The result is a BIGINT value.If there are no matching rows, COUNT(expr) returns 0.

COUNT(*) is somewhat different in that it returns a count of the number of rows, whether or not they contain NULL values.

Prior to MySQL 5.7.18, InnoDB processes SELECT?COUNT(*)?statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.

可見(jiàn)5.7.18之前，MySQL處理count(*)會(huì)掃描主鍵索引，5.7.18之后從非主鍵索引中選擇較小的合適的索引掃描。可以用explain看下執(zhí)行計(jì)劃。

Copy

有人糾結(jié)count(*)、count(1)到底哪種寫(xiě)法更高效，從上面的執(zhí)行計(jì)劃來(lái)看都一樣，如果你還不放心的話(huà)，官方文檔中也明確指明了InnoDB對(duì)count(*)、count(1)的處理完全一致。

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

其他#

索引列上做任何操作(表達(dá)式、函數(shù)計(jì)算、類(lèi)型轉(zhuǎn)換等)時(shí)無(wú)法使用索引會(huì)導(dǎo)致全表掃描

實(shí)戰(zhàn)#

前幾周測(cè)試同事對(duì)公司的某產(chǎn)品進(jìn)行壓測(cè)，某單表寫(xiě)入了近2億條數(shù)據(jù)，過(guò)程中發(fā)現(xiàn)配的報(bào)表有幾個(gè)數(shù)據(jù)查詢(xún)時(shí)間太長(zhǎng)，所以重點(diǎn)看了幾個(gè)慢查詢(xún)SQL。避免敏感信息，這里對(duì)其提取簡(jiǎn)化做個(gè)記錄。

Copy

mysql> select count(*) from tb_alert;

+-----------+

| count(*)? |

+-----------+

| 198101877 |

+-----------+

表join慢#

表join后，取前10條數(shù)據(jù)就花了15秒，看了下SQL執(zhí)行計(jì)劃，如下：

Copy

mysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_idlimit10;10 rowsinset(15.46 sec)mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_idlimit10;+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+| id | select_type | table? ? ? ? ? ? ? | partitions |type| possible_keys | key? | key_len | ref? | rows? ? ? | filtered | Extra? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+|? 1 | SIMPLE? ? ? | tb_alert? ? ? ? ? | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL | 190097118 |? 100.00 | NULL? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ||? 1 | SIMPLE? ? ? | tb_situation_alert | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL | NULL? ? | NULL |? 8026988 |? 100.00 | Usingwhere; Using join buffer (Block Nested Loop) |+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+2 rowsinset, 1 warning (0.00 sec)

可以看出join的時(shí)候沒(méi)有用上索引，tb_situation_alert表上聯(lián)合主鍵是這樣的PRIMARY KEY (situation_id, alert_id)，參與表join字段是alert_id，原來(lái)是不符合聯(lián)合索引的最左前綴法則，僅從這條sql看，解決方案有兩種，一種是對(duì)tb_situation_alert表上的alert_id單獨(dú)建立索引，另外一種是調(diào)換聯(lián)合主鍵的列的次序，改為PRIMARY KEY (alert_id, situation_id)。當(dāng)然不能因?yàn)槎嗯湟粡垐?bào)表，就改其他產(chǎn)線(xiàn)的表的主鍵索引，這并不合理。在這里，應(yīng)該對(duì)alert_id列單獨(dú)建立索引。

Copy

mysql> create index idx_alert_id on tb_situation_alert (alert_id);mysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_idlimit100;100 rowsinset(0.01 sec)mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_idlimit100;+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+| id | select_type | table? ? ? ? ? ? ? | partitions |type| possible_keys | key? ? ? ? ? | key_len | ref? ? ? ? ? ? ? ? ? ? ? ? ? ? | rows? ? ? | filtered | Extra |+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+|? 1 | SIMPLE? ? ? | tb_alert? ? ? ? ? | NULL? ? ? | ALL? | NULL? ? ? ? ? | NULL? ? ? ? | NULL? ? | NULL? ? ? ? ? ? ? ? ? ? ? ? ? ? | 190097118 |? 100.00 | NULL? ||? 1 | SIMPLE? ? ? | tb_situation_alert | NULL? ? ? | ref? | idx_alert_id? | idx_alert_id | 8? ? ? | tb_alert.alert_id |? ? ? ? 2 |? 100.00 | NULL? |+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+2 rowsinset, 1 warning (0.00 sec)

優(yōu)化后，執(zhí)行計(jì)劃可以看出join的時(shí)候走了索引，查詢(xún)前100條0.01秒，和之前的取前10條數(shù)據(jù)就花了15秒天壤之別。

分頁(yè)查詢(xún)慢#

從第10000000條數(shù)據(jù)往后翻頁(yè)時(shí)，25秒才能出結(jié)果，這里就能使用上面的分頁(yè)查詢(xún)優(yōu)化技巧了。上面講優(yōu)化建議時(shí)，沒(méi)看執(zhí)行計(jì)劃，這里正好看一下。

Copy

再看下使用上分頁(yè)查詢(xún)優(yōu)化技巧的sql的執(zhí)行計(jì)劃

Copy

mysql> select * from tb_alert a inner join (select alert_id from tb_alertlimit10000000, 10) b on a.alert_id = b.alert_id;10 rowsinset(2.29 sec)mysql> explain select * from tb_alert a inner join (select alert_id from tb_alert a2limit10000000, 10) b on a.alert_id = b.alert_id;+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+| id | select_type | table? ? ? | partitions |type| possible_keys | key? ? ? ? ? | key_len | ref? ? ? | rows? ? ? | filtered | Extra? ? ? |+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+|? 1 | PRIMARY? ? | | NULL? ? ? | ALL? ? | NULL? ? ? ? ? | NULL? ? ? ? ? | NULL? ? | NULL? ? ? |? 10000010 |? 100.00 | NULL? ? ? ? ||? 1 | PRIMARY? ? | a? ? ? ? ? | NULL? ? ? | eq_ref | PRIMARY? ? ? | PRIMARY? ? ? | 8? ? ? | b.alert_id |? ? ? ? 1 |? 100.00 | NULL? ? ? ? ||? 2 | DERIVED? ? | a2? ? ? ? | NULL? ? ? | index? | NULL? ? ? ? ? | idx_processed | 5? ? ? | NULL? ? ? | 190097118 |? 100.00 | Using index |+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+3 rowsinset, 1 warning (0.00 sec)

分組聚合慢#

分析SQL后，發(fā)現(xiàn)根本上并非分組聚合慢，而是掃描聯(lián)合索引后，回表導(dǎo)致性能低下，去除不必要的字段，使用覆蓋索引。

這里避免敏感信息，只演示分組聚合前的簡(jiǎn)化SQL，主要問(wèn)題也是在這。

表上有聯(lián)合索引KEY idx_alert_start_host_template_id ( alert_start, alert_host, template_id)，優(yōu)化前的sql為

Copy

mysql> select alert_start, alert_host, template_id, alert_service from tb_alertwherealert_start > {ts'2019-06-05 00:00:10.0'}limit10000;10000 rowsinset(1 min 5.22 sec)

使用覆蓋索引，去掉template_id列，就能避免回表，查詢(xún)時(shí)間從1min多變?yōu)?.03秒，如下：

Copy

mysql> select alert_start, alert_host, template_id from tb_alertwherealert_start > {ts'2019-06-05 00:00:10.0'}limit10000;10000 rowsinset(0.03 sec)mysql> explain select alert_start, alert_host, template_id from tb_alertwherealert_start > {ts'2019-06-05 00:00:10.0'}limit10000;+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+| id | select_type | table? ? | partitions |type| possible_keys? ? ? ? ? ? ? ? ? ? ? | key? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? | key_len | ref? | rows? ? | filtered | Extra? ? ? ? ? ? ? ? ? ? |+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+|? 1 | SIMPLE? ? ? | tb_alert | NULL? ? ? | range | idx_alert_start_host_template_id? | idx_alert_start_host_template_id? | 9? ? ? | NULL | 95048559 |? 100.00 | Usingwhere; Using index |+----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+1 rowinset, 1 warning (0.01 sec)

總結(jié)#

任何不考慮應(yīng)用場(chǎng)景的設(shè)計(jì)都不是最好的設(shè)計(jì)，就比如說(shuō)表結(jié)構(gòu)的設(shè)計(jì)、索引的創(chuàng)建，都應(yīng)該權(quán)衡數(shù)據(jù)量大小、查詢(xún)需求、數(shù)據(jù)更新頻率等。

1）寧濫勿缺。認(rèn)為一個(gè)查詢(xún)就需要建一個(gè)索引

2）寧缺勿濫。認(rèn)為索引會(huì)消耗空間、嚴(yán)重拖慢記錄的更新以及行的新增速度

最后，小編想說(shuō)：我是一名python開(kāi)發(fā)工程師，整理了一套最新的python系統(tǒng)學(xué)習(xí)教程，想要這些資料的可以關(guān)注私信小編“01”即可（免費(fèi)分享哦）希望能對(duì)你有所幫助.

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

一文讀懂MySQL的索引結(jié)構(gòu)及查詢(xún)優(yōu)化

一文讀懂MySQL的索引結(jié)構(gòu)及查詢(xún)優(yōu)化

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

一文讀懂MySQL的索引結(jié)構(gòu)及查詢(xún)優(yōu)化

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av