關(guān)鍵要點(diǎn)

• 使用MySQL的EXPLAIN命令分析和優(yōu)化查詢執(zhí)行計(jì)劃，通過(guò)揭示連接類型和索引使用等關(guān)鍵信息，確保更高效的數(shù)據(jù)庫(kù)操作。
• 實(shí)施查詢分析來(lái)衡量查詢的實(shí)際運(yùn)行時(shí)間，從而進(jìn)行有針對(duì)性的優(yōu)化，以減少執(zhí)行時(shí)間並提高整體性能。
• 根據(jù)EXPLAIN命令的反饋添加合適的索引，重點(diǎn)關(guān)注WHERE子句中使用的列，以加快數(shù)據(jù)檢索速度並提高查詢性能。
• 對(duì)於搜索操作中涉及的列，考慮使用全文索引來(lái)優(yōu)化性能，尤其是在查詢中使用LIKE運(yùn)算符時(shí)。
• 注意ORDER BY與LIMIT組合使用的情況，因?yàn)樗赡軙?huì)抵消限制結(jié)果的性能優(yōu)勢(shì)，尤其是在沒(méi)有有效使用索引的情況下。

數(shù)據(jù)庫(kù)優(yōu)化通常是提升應(yīng)用性能的首要關(guān)注點(diǎn)，也是最常見(jiàn)的瓶頸。如何衡量和理解需要改進(jìn)的地方？

一個(gè)簡(jiǎn)單而有效的工具是查詢分析。啟用分析可以更準(zhǔn)確地估計(jì)查詢的運(yùn)行時(shí)間。這是一個(gè)兩步過(guò)程：首先，啟用分析；然後，調(diào)用show profiles來(lái)獲取查詢運(yùn)行時(shí)間。

假設(shè)數(shù)據(jù)庫(kù)中存在以下插入操作（並假設(shè)用戶1和圖庫(kù)1已創(chuàng)建）：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

少量數(shù)據(jù)不會(huì)造成問(wèn)題，但我們可以用它來(lái)進(jìn)行簡(jiǎn)單的分析?？紤]以下查詢：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

如果照片條目很多，此查詢將來(lái)可能會(huì)成為問(wèn)題。

要獲取此查詢的準(zhǔn)確運(yùn)行時(shí)間，可以使用以下SQL：

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

結(jié)果如下所示：

show profiles;命令不僅顯示原始查詢的時(shí)間，還顯示所有其他查詢的時(shí)間，從而可以準(zhǔn)確地分析查詢。

如何改進(jìn)查詢？

可以依靠SQL知識(shí)進(jìn)行改進(jìn)，或者依靠MySQL的EXPLAIN命令並根據(jù)實(shí)際信息來(lái)提高查詢性能。

EXPLAIN用於獲取查詢執(zhí)行計(jì)劃，即MySQL如何執(zhí)行查詢。它適用於SELECT、DELETE、INSERT、REPLACE和UPDATE語(yǔ)句，並顯示優(yōu)化器關(guān)於語(yǔ)句執(zhí)行計(jì)劃的信息。官方文檔很好地描述了EXPLAIN如何幫助我們：

通過(guò)EXPLAIN，您可以看到應(yīng)該在哪些表中添加索引，以便語(yǔ)句通過(guò)使用索引查找行來(lái)更快地執(zhí)行。您還可以使用EXPLAIN來(lái)檢查優(yōu)化器是否以最佳順序連接表。

為了舉例說(shuō)明EXPLAIN的使用，我們將使用UserManager.php中查找用戶電子郵件的查詢：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

要使用EXPLAIN命令，只需在SELECT類型查詢之前添加它：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

結(jié)果如下（向右滾動(dòng)查看所有內(nèi)容）：

這些結(jié)果一開(kāi)始不容易理解，讓我們仔細(xì)看看每一個(gè)：

• id：這是SELECT中每個(gè)查詢的順序標(biāo)識(shí)符。
• select_type：SELECT查詢的類型。此字段可以取多個(gè)不同的值，因此我們將關(guān)注最重要的幾個(gè)：
  • SIMPLE：沒(méi)有子查詢或聯(lián)合的簡(jiǎn)單查詢
  • PRIMARY：select位於連接中最外層的查詢中
  • DERIVED：select是from中子查詢的一部分
  • SUBQUERY：子查詢中的第一個(gè)select
  • UNION：select是聯(lián)合的第二個(gè)或後續(xù)語(yǔ)句。 完整的select_type字段值列表可以在此處找到。
• table：行引用的表。
• type：此字段表示MySQL連接所用表的類型。這可能是EXPLAIN輸出中最重要的字段。它可以指示缺少索引，也可以顯示如何重寫查詢。此字段的可能值如下（從最佳類型到最差類型排序）：
  • system：表具有零行或一行。
  • const：表只有一行匹配行，該行已編入索引。這是最快的連接類型。
  • eq_ref：索引的所有部分都由連接使用，並且索引是PRIMARY_KEY或UNIQUE NOT NULL。
  • ref：對(duì)於來(lái)自前一張表的每一行組合，都會(huì)讀取索引列的所有匹配行。這種類型的連接通常出現(xiàn)在使用=或運(yùn)算符比較的已編入索引的列中。
  • fulltext：連接使用表的全文索引。
  • ref_or_null：與ref相同，但也包含來(lái)自列的NULL值的行。
  • index_merge：連接使用索引列表來(lái)生成結(jié)果集。 EXPLAIN的KEY列將包含所使用的鍵。
  • unique_subquery：IN子查詢僅從表中返回一個(gè)結(jié)果，並使用主鍵。
  • range：使用索引在特定範(fàn)圍內(nèi)查找匹配的行。
  • index：掃描整個(gè)索引樹(shù)以查找匹配的行。
  • ALL：掃描整個(gè)表以查找連接的匹配行。這是最差的連接類型，通常表示表上缺少合適的索引。
• possible_keys：顯示MySQL可用於從表中查找行的鍵。這些鍵在實(shí)踐中可能被使用，也可能不被使用。
• keys：指示MySQL實(shí)際使用的索引。 MySQL始終查找可用於查詢的最佳鍵。連接多個(gè)表時(shí)，它可能會(huì)找到一些不在possible_keys中列出但更優(yōu)的鍵。
• key_len：指示查詢優(yōu)化器選擇使用的索引的長(zhǎng)度。
• ref：顯示與key列中命名的索引進(jìn)行比較的列或常量。
• rows：列出為生成輸出而檢查的記錄數(shù)。這是一個(gè)非常重要的指標(biāo)；檢查的記錄越少越好。
• Extra：包含其他信息。此列中的Using filesort或Using temporary等值可能表示有問(wèn)題的查詢。

EXPLAIN輸出格式的完整文檔可以在MySQL官方頁(yè)面上找到。

回到我們的簡(jiǎn)單查詢：它是一種SIMPLE類型的select，具有const類型的連接。這是我們可能擁有的最佳查詢案例。但是，當(dāng)我們需要更大更複雜的查詢時(shí)會(huì)發(fā)生什麼？

回到我們的應(yīng)用程序模式，我們可能想要獲取所有圖庫(kù)圖像。我們可能還只想包含描述中包含單詞“cat”的照片。這絕對(duì)是我們可以在項(xiàng)目需求中找到的情況。讓我們看看查詢：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

在這個(gè)更複雜的情況下，我們應(yīng)該在EXPLAIN中獲得更多信息來(lái)分析：

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

這將給出以下結(jié)果（向右滾動(dòng)查看所有單元格）：

讓我們仔細(xì)看看，看看我們可以在查詢中改進(jìn)什麼。

如前所述，首先應(yīng)該查看的主要列是type列和rows列。目標(biāo)應(yīng)該是獲得type列中更好的值，並儘可能減少rows列的值。

第一個(gè)查詢的結(jié)果是index，這根本不是一個(gè)好結(jié)果。這意味著我們可能可以改進(jìn)它。

查看我們的查詢，有兩種方法可以解決它。首先，Users表沒(méi)有被使用。我們要么擴(kuò)展查詢以確保我們正在定位用戶，要么應(yīng)該完全刪除查詢的用戶部分。它只會(huì)增加我們整體性能的複雜性和時(shí)間。

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

所以現(xiàn)在我們得到了完全相同的結(jié)果。讓我們看看EXPLAIN：

我們剩下的是ALL類型。雖然ALL可能是最差的連接類型，但也有一些情況下它是唯一的選擇。根據(jù)我們的要求，我們想要所有圖庫(kù)圖像，因此我們需要搜索整個(gè)galleries表。當(dāng)我們需要表中的所有信息時(shí)，索引在嘗試查找表中的特定信息時(shí)非常好，但它們幫不了我們。當(dāng)我們遇到這種情況時(shí)，我們必須求助於其他方法，例如緩存。

由於我們正在處理LIKE，我們可以做的最後一個(gè)改進(jìn)是向我們的description字段添加全文索引。這樣，我們可以將LIKE更改為match()並提高性能。更多關(guān)於全文索引的信息可以在此處找到。

我們還必須查看兩個(gè)非常有趣的情況：應(yīng)用程序中最新的和相關(guān)的功能。這些適用於圖庫(kù)，並涉及我們應(yīng)該注意的一些極端情況：

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

以上是相關(guān)圖庫(kù)。

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

以上是最新的圖庫(kù)。

乍一看，這些查詢應(yīng)該非?？欤?yàn)樗鼈兪褂玫氖?code>LIMIT。在大多數(shù)使用LIMIT的查詢中都是這種情況。不幸的是，對(duì)於我們和我們的應(yīng)用程序來(lái)說(shuō)，這些查詢也使用了ORDER BY。因?yàn)槲覀冃枰谙拗撇樵冎皩?duì)所有結(jié)果進(jìn)行排序，所以我們失去了使用LIMIT的優(yōu)勢(shì)。

由於我們知道ORDER BY可能很棘手，讓我們應(yīng)用我們可靠的EXPLAIN。

以及，

我們可以看到，對(duì)於我們的兩個(gè)查詢，我們都有最糟糕的連接類型：ALL。

歷史上，MySQL的ORDER BY實(shí)現(xiàn)，特別是與LIMIT一起使用時(shí)，通常是MySQL性能問(wèn)題的根源。這種組合也用於大多數(shù)具有大型數(shù)據(jù)集的交互式應(yīng)用程序。像新註冊(cè)用戶和熱門標(biāo)籤這樣的功能通常使用這種組合。

因?yàn)檫@是一個(gè)常見(jiàn)問(wèn)題，所以我們還應(yīng)該應(yīng)用一些常見(jiàn)的解決方案來(lái)解決性能問(wèn)題。

• 確保我們正在使用索引。在我們的例子中，created_at是一個(gè)很好的候選者，因?yàn)樗俏覀冋谂判虻淖侄?。這樣，我們就可以在不掃描和排序完整結(jié)果集的情況下執(zhí)行ORDER BY和LIMIT。
• 按前導(dǎo)表中的列排序。通常，如果ORDER BY按不是連接順序中的第一個(gè)表的字段進(jìn)行排序，則無(wú)法使用索引。
• 不要按表達(dá)式排序。表達(dá)式和函數(shù)不允許ORDER BY使用索引。
• 注意大的LIMIT值。大的LIMIT值將強(qiáng)制ORDER BY對(duì)更多行進(jìn)行排序。這會(huì)影響性能。

當(dāng)我們同時(shí)使用LIMIT和ORDER BY時(shí)，這些是我們應(yīng)該採(cǎi)取的一些措施，以最大限度地減少性能問(wèn)題。

結(jié)論

正如我們所看到的，EXPLAIN對(duì)於儘早發(fā)現(xiàn)查詢中的問(wèn)題非常有用。有很多問(wèn)題只有當(dāng)我們的應(yīng)用程序處?kù)渡a(chǎn)環(huán)境中並且有大量數(shù)據(jù)或大量訪問(wèn)者訪問(wèn)數(shù)據(jù)庫(kù)時(shí)才會(huì)注意到。如果可以使用EXPLAIN儘早發(fā)現(xiàn)這些問(wèn)題，那麼將來(lái)出現(xiàn)性能問(wèn)題的可能性就小得多。

我們的應(yīng)用程序擁有所有需要的索引，並且速度很快，但我們現(xiàn)在知道，每當(dāng)我們需要檢查性能提升時(shí)，我們總是可以求助於EXPLAIN和索引。

關(guān)於MySQL性能索引的常見(jiàn)問(wèn)題解答(FAQ)

MySQL性能索引的重要性是什麼？

MySQL性能索引對(duì)於優(yōu)化數(shù)據(jù)庫(kù)性能至關(guān)重要。它們通過(guò)根據(jù)已編入索引的列中的值快速訪問(wèn)數(shù)據(jù)表中的行，顯著加快數(shù)據(jù)檢索操作。如果沒(méi)有索引，MySQL將不得不遍歷表中的每一行才能找到相關(guān)的行，這可能會(huì)非常耗時(shí)，尤其對(duì)於大型數(shù)據(jù)庫(kù)而言。

EXPLAIN命令如何幫助提高M(jìn)ySQL性能？

MySQL中的EXPLAIN命令是一個(gè)強(qiáng)大的工具，它提供有關(guān)MySQL如何執(zhí)行查詢的信息。它顯示讀取表的順序、執(zhí)行的讀取操作類型、可選擇的索引以及要檢查的估計(jì)行數(shù)。這些信息可以幫助開(kāi)發(fā)人員優(yōu)化查詢並提高數(shù)據(jù)庫(kù)性能。

為什麼MySQL不使用任何可能的鍵？

MySQL不使用任何可能的鍵可能有幾個(gè)原因。一個(gè)原因可能是優(yōu)化器估計(jì)使用索引需要掃描表的大部分，並決定表掃描會(huì)更快。另一個(gè)原因可能是WHERE子句中的列與索引中的列不匹配。

如何優(yōu)化我的MySQL查詢？

有幾種方法可以優(yōu)化MySQL查詢。一種方法是有效地使用索引。索引可以顯著加快數(shù)據(jù)檢索速度。但是，它們會(huì)減慢數(shù)據(jù)修改操作（如INSERT、UPDATE和DELETE）的速度。因此，找到平衡點(diǎn)非常重要。另一種方法是使用EXPLAIN命令來(lái)了解MySQL如何執(zhí)行查詢並查找潛在的瓶頸。

MySQL中主鍵和索引的區(qū)別是什麼？

MySQL中的主鍵是一種索引。主鍵是表中行的唯一標(biāo)識(shí)符。它強(qiáng)制執(zhí)行列或列組合的唯一性，並確保列或列組合不包含NULL值。另一方面，索引是一種數(shù)據(jù)結(jié)構(gòu)，它可以提高數(shù)據(jù)檢索操作的速度。它可以應(yīng)用於任何列或列的組合。

如何在MySQL中創(chuàng)建索引？

可以使用CREATE INDEX語(yǔ)句在MySQL中創(chuàng)建索引。語(yǔ)法如下：CREATE INDEX index_name ON table_name (column1, column2, …);。這將在指定表的指定列上創(chuàng)建一個(gè)索引。

MySQL中的複合索引是什麼？

複合索引，也稱為多列索引，是一個(gè)包含多列的索引。在MySQL中，複合索引最多可以包含16列，但已編入索引的列的總大小不能超過(guò)767字節(jié)。

如何在MySQL中刪除索引？

可以使用DROP INDEX語(yǔ)句在MySQL中刪除索引。語(yǔ)法如下：DROP INDEX index_name ON table_name;。這將從指定的表中刪除指定的索引。

MySQL中聚集索引和非聚集索引的區(qū)別是什麼？

聚集索引決定表中數(shù)據(jù)的物理順序。每個(gè)表只能有一個(gè)聚集索引。另一方面，非聚集索引不會(huì)更改表中數(shù)據(jù)的物理順序。相反，它維護(hù)一個(gè)指向數(shù)據(jù)行的單獨(dú)數(shù)據(jù)結(jié)構(gòu)（索引），從而允許更快地檢索數(shù)據(jù)。

MySQL如何選擇要使用的索引？

MySQL使用基於成本的優(yōu)化器來(lái)選擇要使用的索引。優(yōu)化器會(huì)估計(jì)不同查詢執(zhí)行計(jì)劃的成本，並選擇成本最低的計(jì)劃。成本是根據(jù)要讀取的行數(shù)、磁盤查找次數(shù)、CPU成本和內(nèi)存使用情況等因素來(lái)估計(jì)的。

以上是用索引提高M(jìn)ySQL性能並解釋的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！