作為一名工程師，其專(zhuān)長(zhǎng)是應(yīng)用程序而不是 MySQL 性能，這可能是壓倒性的。您應(yīng)該從分析查詢(xún)響應(yīng)時(shí)間開(kāi)始，因?yàn)樗怯幸饬x且可操作的。

有意義——我們看到的唯一指標(biāo)是查詢(xún)響應(yīng)時(shí)間。當(dāng)查詢(xún)需要 n 秒執(zhí)行時(shí)，我們會(huì)變得不耐煩。在同一個(gè)查詢(xún)中，可能會(huì)檢查一百萬(wàn)行，但我們看不到檢查一百萬(wàn)行。我們的時(shí)間很寶貴。

可操作——因?yàn)槟鷵碛写a，所以可以更改查詢(xún)響應(yīng)時(shí)間。即使您無(wú)權(quán)訪(fǎng)問(wèn)它，您仍然可以間接優(yōu)化查詢(xún)響應(yīng)時(shí)間。

提高數(shù)據(jù)庫(kù)性能的最有效方法是正確使用索引。在數(shù)據(jù)庫(kù)中，索引是一種數(shù)據(jù)結(jié)構(gòu)，它將值與這些值出現(xiàn)在給定列中的行相關(guān)聯(lián)。與使用蠻力從上到下搜索整個(gè)表相比，數(shù)據(jù)庫(kù)可以使用索引更快地找到值。

大多數(shù)軟件開(kāi)發(fā)人員不知道如何或何時(shí)創(chuàng)建索引。在有關(guān)數(shù)據(jù)庫(kù)的書(shū)籍和文檔中很少或從不解釋何時(shí)使用索引。開(kāi)發(fā)人員只能猜測(cè)如何有效地使用它們。

SELECT 查詢(xún)用于檢索數(shù)據(jù)，通常使用 WHERE 子句過(guò)濾數(shù)據(jù)。在大多數(shù)情況下，會(huì)在查詢(xún)的 WHERE 子句中指定的列上創(chuàng)建索引。在沒(méi)有索引的情況下，數(shù)據(jù)庫(kù)會(huì)掃描所有的行，過(guò)濾掉匹配的行，然后返回結(jié)果。如果有數(shù)百萬(wàn)條記錄，此掃描操作可能需要很長(zhǎng)時(shí)間，導(dǎo)致 API 和應(yīng)用程序響應(yīng)時(shí)間變慢。

讓我們創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)以更好地理解它。InnoDB 是 MySQL 的默認(rèn)數(shù)據(jù)庫(kù)引擎。

CREATE TABLE sample_table (
first_name VARCHAR(32) NOT NULL,
last_name VARCHAR(32) NOT NULL,
age INT,
mobile_no VARCHAR(10) );

向該表中插入 1000 條隨機(jī)記錄。使用一個(gè)程序，我就能生成這些記錄。

DELIMITER $$
DROP PROCEDURE generate_data;
CREATE PROCEDURE generate_data()
BEGIN
DECLARE i INT DEFAULT 0;
當(dāng) i < 1000 DO
INSERT INTO `sample_table` (`first_name`,`last_name`,`age`,`mobile_no`) 值 (
CONV(FLOOR(RAND() * 99999999999999), 10, 36),
CONV(FLOOR(RAND ) () * 99999999999999), 10, 36),
ROUND(RAND()*100,2),
LPAD(FLOOR(RAND() * 10000000000), 10, '0')
);
設(shè)置 i = i + 1;
結(jié)束；
END$$分隔符；

命令 SHOW INDEX 可用于驗(yàn)證沒(méi)有在該表上創(chuàng)建索引。

到目前為止，如果我們運(yùn)行一個(gè)簡(jiǎn)單的 SELECT 查詢(xún)，該查詢(xún)將掃描整個(gè)表，因?yàn)樵?mobile_no 上沒(méi)有定義索引。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

EXPLAIN 指示查詢(xún)引擎打算如何執(zhí)行查詢(xún)。正如您在上面的快照中看到的，rows 列返回 1000，而 possible_keys 返回 null。possible_keys 列表示可以在此查詢(xún)中使用的所有索引。在所有可能的索引中，鍵列指示將使用哪個(gè)索引。

CREATE INDEX 語(yǔ)句創(chuàng)建一個(gè)索引，它是數(shù)據(jù)庫(kù)中的唯一結(jié)構(gòu)。它需要自己的磁盤(pán)空間并存儲(chǔ)索引表數(shù)據(jù)的副本。因此，索引是純粹的冗余。表索引不會(huì)改變表中的數(shù)據(jù)；它只是創(chuàng)建一個(gè)引用表的新數(shù)據(jù)結(jié)構(gòu)。畢竟數(shù)據(jù)庫(kù)索引與書(shū)籍索引非常相似。它們占據(jù)自己的空間，高度冗余，并引用存儲(chǔ)在另一個(gè)位置的信息。為了創(chuàng)建主索引，必須使用 ALTER TABLE，而不是 CREATE INDEX。

不必創(chuàng)建自己的主鍵。由于 InnoDB 在設(shè)計(jì)上必須在每個(gè)表中都有一個(gè)主鍵，因此如果您沒(méi)有定義主鍵，它會(huì)自動(dòng)為您創(chuàng)建一個(gè)。當(dāng)您稍后為該表創(chuàng)建主鍵時(shí)，InnoDB 會(huì)刪除先前自動(dòng)生成的主鍵。

現(xiàn)在我們沒(méi)有定義主鍵，讓我們看看 InnoDB 默認(rèn)為我們創(chuàng)建了什么：

EXTENDED 顯示用戶(hù)無(wú)法訪(fǎng)問(wèn)但完全由 MySQL 管理的所有索引。MySQL 已在 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR 以及表中的所有列上定義了復(fù)合索引。在沒(méi)有用戶(hù)定義的主鍵的情況下，該索引用于唯一地查找記錄。

ALTER TABLE sample_table 添加主鍵（mobile_no）；

由于我隨機(jī)生成了表行，因此主鍵的標(biāo)準(zhǔn)之一是列值在表中應(yīng)該是唯一的。就我而言，我有重復(fù)的生成值，所以我不能將它們用作主鍵。

但是，我可以在 mobile_no 列上創(chuàng)建一個(gè)索引，我將在這里向您展示。

在 sample_table(mobile_no) 上創(chuàng)建索引 mobile_no_idx；

現(xiàn)在讓我們看看這個(gè)索引是否減少了需要在查詢(xún)的 WHERE 子句中搜索給定 mobile_no 的行數(shù)。

EXPLAIN SELECT * from sample_table where mobile_no='5554688121';

這些行只返回 1， possible_keys 和 key 都返回 mobile_no_idx。使用索引，查詢(xún)優(yōu)化器直接進(jìn)入記錄并檢索它。這是非常有效的。索引的目的是以增加空間為代價(jià)來(lái)縮小搜索范圍。

1. B-Tree（平衡樹(shù)）索引——最常用和默認(rèn)的索引類(lèi)型。該索引可以與使用 =、>、>=、<、<= 和 BETWEEN & LIKE 關(guān)鍵字的搜索查詢(xún)一起使用。

2. R-Tree（空間樹(shù)）索引——利用 MySQL 的幾何數(shù)據(jù)類(lèi)型，它可以用來(lái)索引地理對(duì)象。

3. 哈希索引——通常與 = 或 <=> 搜索運(yùn)算符一起使用。非?？?，但只能在使用內(nèi)存存儲(chǔ)引擎時(shí)使用。

4. 覆蓋索引— 覆蓋完成查詢(xún)所需的所有列的索引。

5. 聚集索引——行數(shù)據(jù)存儲(chǔ)在此類(lèi)索引中。PRIMARY KEY 或者，如果不存在，則通常使用 UNIQUE 索引

6. 復(fù)合索引——多列用于創(chuàng)建索引。

7. 前綴索引— 這種類(lèi)型的索引允許您索引列的前綴。這些索引不索引列的完整值，因此它們經(jīng)常用于節(jié)省空間。

B-tree 索引是 MySQL 中最常用的索引。只要有這樣的索引，MySQL 就可以利用使用關(guān)鍵字 =、>、>=、<、<= 和 BETWEEN & LIKE 的搜索查詢(xún)。這是可以將此類(lèi)索引添加到列以加快搜索查詢(xún)的主要原因之一。當(dāng)使用 B 樹(shù)索引時(shí)，存儲(chǔ)引擎不必掃描整個(gè)表來(lái)查找相關(guān)行。

CREATE INDEX index_name ON table_name(column_name)
或CREATE INDEX index_name ON table_name(column_name) USING BTREE；

SELECT * from table_name where column_name <= 'value';
SELECT * from table_name where column_name BETWEEN 1 AND 100; SELECT * from table_name where column_name LIKE '%value%';

為了使用 R-Tree 或空間索引，您必須使用 MySQL 提供的地理信息系統(tǒng) (GIS) 功能。MBRContains、MBRCovers 和 MBREquals 是其中的一些函數(shù)。它們可以指示一個(gè)參數(shù)的最小邊界矩形是否包含、覆蓋或等于另一個(gè)參數(shù)的最小邊界矩形。

要將此類(lèi)索引添加到表中，請(qǐng)使用 SPATIAL 關(guān)鍵字。

創(chuàng)建空間索引 index_name ON table_name(column_name);

MySQL 中哈希索引的主要優(yōu)點(diǎn)是速度非?？?，但主要缺點(diǎn)是只能用于相等比較，并且只能在 MEMORY 存儲(chǔ)引擎上工作。也不支持范圍搜索。

要在表上創(chuàng)建這樣的索引，請(qǐng)?jiān)谧詈笫褂?USING HASH 選項(xiàng)。

CREATE INDEX index_name ON table_name(column_name) USING HASH;

在使用 MySQL 時(shí)，可能會(huì)遇到一些可能需要以不同方式使用索引的極端情況。覆蓋索引是那些覆蓋查詢(xún)成功所必需的所有字段的索引。當(dāng)存在覆蓋索引時(shí)，查詢(xún)可以直接從索引中檢索結(jié)果，而不必訪(fǎng)問(wèn)磁盤(pán)。如果您使用的索引已經(jīng)包含您要搜索的列的值，MySQL 將不會(huì)訪(fǎng)問(wèn)磁盤(pán)，而是直接從索引中檢索結(jié)果。

SELECT col1, col2 FROM table_name WHERE col3 = 'value';

聚集索引不是單獨(dú)的 MySQL 索引類(lèi)型，它們是存儲(chǔ)數(shù)據(jù)的不同方法。如果您的表有 PRIMARY KEY，則聚集索引就是 PRIMARY KEY。如果您的表沒(méi)有 PRIMARY KEY，它是第一個(gè)將所有鍵列定義為 NOT NULL 的唯一索引。

使用這樣的索引，表中的所有行都根據(jù)其鍵值進(jìn)行存儲(chǔ)和排序。表中不能有多個(gè)聚集索引，因?yàn)槊恳恍兄荒苡幸粋€(gè)排序順序。

復(fù)合索引跨越多個(gè)列。如果我們?cè)诙嗔猩嫌幸粋€(gè)索引，我們就有一個(gè)復(fù)合索引。

CREATE INDEX index_name ON demo_table(col1,col2,col3);

在使用索引字段進(jìn)行選擇、連接或過(guò)濾的查詢(xún)中，復(fù)合索引是有益的。

SELECT * FROM table_name WHERE col1 = 'value1' AND col2 = 'val2';

在 MySQL 中，復(fù)合索引最多可以包含 16 列，但請(qǐng)記住 MySQL 從左到右使用索引，反之則不然。

在某些情況下，您可能需要索引冗長(zhǎng)的列，從而使索引變得非常大。作為索引整個(gè)值的替代方法，前綴索引還可以索引列的前幾個(gè)字符。

CREATE INDEX index_name ON table_name (column_name(length));

使用前綴索引，以提供良好選擇性同時(shí)節(jié)省空間的方式對(duì)列進(jìn)行索引很重要。

通過(guò)猜測(cè)來(lái)選擇我們的索引不可避免地會(huì)導(dǎo)致一些錯(cuò)誤。

1. 沒(méi)有定義索引或沒(méi)有足夠的索引

2. 定義過(guò)多的索引或無(wú)用的索引

3. 運(yùn)行沒(méi)有索引的查詢(xún)可以提供幫助

通常認(rèn)為數(shù)據(jù)庫(kù)在維護(hù)索引時(shí)會(huì)產(chǎn)生開(kāi)銷(xiāo)。每當(dāng)我們插入、更新或刪除表時(shí)，數(shù)據(jù)庫(kù)必須更新其索引數(shù)據(jù)結(jié)構(gòu)，以確保后續(xù)搜索能夠可靠地找到正確的行集。開(kāi)銷(xiāo)通常與浪費(fèi)有關(guān)。因此，當(dāng)我們讀到維護(hù)索引會(huì)產(chǎn)生開(kāi)銷(xiāo)時(shí)，我們希望消除它。在某些情況下，開(kāi)發(fā)人員得出結(jié)論，刪除索引是最好的解決方案。這是常見(jiàn)的建議，但它忽略了索引具有證明其成本合理的好處這一事實(shí)。

僅當(dāng)您運(yùn)行使用它的查詢(xún)時(shí)，索引才有用。創(chuàng)建你不使用的索引是沒(méi)有意義的。因?yàn)樗麄儾恢滥男┧饕龝?huì)對(duì)他們的查詢(xún)有益，所以有些人會(huì)在每列和每列組合上創(chuàng)建索引。一個(gè)被索引覆蓋的數(shù)據(jù)庫(kù)表會(huì)產(chǎn)生大量開(kāi)銷(xiāo)，并且不能保證返回。

第三種錯(cuò)誤是運(yùn)行不能使用索引的查詢(xún)。為了讓查詢(xún)運(yùn)行得更快，開(kāi)發(fā)人員構(gòu)建了越來(lái)越多的索引，試圖找到一些列或索引選項(xiàng)的神奇組合。

在 sample_table(last_name, first_name) 上創(chuàng)建索引電話(huà) IDX；

SELECT * from sample_table ORDER BY first_name, last_name; SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

EXPLAIN SELECT * from sample_table ORDER BY first_name, last_name;

possible_keys 和 key 在這里都是空的，并且所有的行都被掃描了。

EXPLAIN SELECT * FROM sample_table WHERE last_name = 'Charles' OR first_name = 'Charles' ;

這里， possible_key 是telephone_idx，但是key 仍然是空的，因?yàn)檫@個(gè)索引沒(méi)有被使用。

索引最重要的規(guī)則之一是您不應(yīng)該盲目猜測(cè)。了解您的數(shù)據(jù)、了解您的查詢(xún)并指導(dǎo)您的索引。