MySQL優(yōu)化之如何查找SQL效率低的原因

查詢到效率低的 SQL 語(yǔ)句 后，可以通過(guò) EXPLAIN 或者 DESC 命令獲取 MySQL 如何執(zhí)行 SELECT 語(yǔ)句的信息，包括在 SELECT 語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序，比如我們想計(jì)算 2006 年所有公司的銷售額，需要關(guān)聯(lián) sales 表和 company 表，并且對(duì) profit 字段做求和（ sum ）操作，相應(yīng) SQL 的執(zhí)行計(jì)劃如下：
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
2 rows in set (0.00 sec)

每個(gè)列的解釋如下：

•select_type ：表示 SELECT 的 類型，常見(jiàn)的取值有 SIMPLE （簡(jiǎn)單表，即不使用表連接或者子查詢）、 PRIMARY （主查詢，即外層的查詢）、 UNION （ UNION 中的第二個(gè)或者后面的查詢語(yǔ)句）、 SUBQUERY （子查詢中的第一個(gè) SELECT ）等。

•table ：輸出結(jié)果集的表。

•type ：表示表的連接類型，性能由好到差的連接類型為 system （表中僅有一行，即常量表）、 const （單表中最多有一個(gè)匹配行，例如 primary key 或者 unique index ）、 eq_ref （對(duì)于前面的每一行，在此表中只查詢一條記錄，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用 primary key 或者 unique index ）、 ref （與 eq_ref 類似，區(qū)別在于不是使用 primary key 或者 unique index ，而是使用普通的索引）、 ref_or_null （ 與 ref 類似，區(qū)別在于條件中包含對(duì) NULL 的查詢 ） 、 index_merge ( 索引合并優(yōu)化 ) 、 unique_subquery （ in 的后面是一個(gè)查詢主鍵字段的子查詢）、 index_subquery （ 與 unique_subquery 類似，區(qū)別在于 in 的后面是查詢非唯一索引字段的子查詢）、 range （單表中的范圍查詢）、 index （對(duì)于前面的每一行，都通過(guò)查詢索引來(lái)得到數(shù)據(jù)）、 all （對(duì)于前面的每一行，都通過(guò)全表掃描來(lái)得到數(shù)據(jù)）。

•possible_keys ：表示查詢時(shí)，可能使用的索引。
•key ：表示實(shí)際使用的索引。
•key_len ：索引字段的長(zhǎng)度。
•rows ：掃描行的數(shù)量。
•Extra ：執(zhí)行情況的說(shuō)明和描述。

在上面的例子中，已經(jīng)可以確認(rèn)是 對(duì) a 表的全表掃描導(dǎo)致效率的不理想，那么 對(duì) a 表的 year 字段創(chuàng)建索引，具體如下：

mysql> create index idx_sales_year on sales(year);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
創(chuàng)建索引后，這條語(yǔ)句的執(zhí)行計(jì)劃如下：
mysql> explain select sum(profit) from sales a,company b where a.company_id = b.id and a.year = 2006\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: idx_sales_year
key: idx_sales_year
key_len: 4
ref: const
rows: 3
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 12
Extra: Using where
2 rows in set (0.00 sec)

    可以發(fā)現(xiàn)建立索引后對(duì) a 表需要掃描的行數(shù)明顯減少（從全表掃描減少到 3 行），可見(jiàn)索引的使用可以大大提高數(shù)據(jù)庫(kù)的訪問(wèn)速度，尤其在表很龐大的時(shí)候這種優(yōu)勢(shì)更為明顯，使用索引優(yōu)化 sql 是優(yōu)化問(wèn)題 sql 的一種常用基本方法，在后面的章節(jié)中我們會(huì)具體介紹如何使索引來(lái)優(yōu)化 sql 。

您可能感興趣的文章:

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com