概述

前面我們介紹了 MySQL 中怎么樣通過索引來優(yōu)化查詢。日常開發(fā)中，除了使用查詢外，我們還會(huì)使用一些其他的常用 SQL，比如 INSERT、GROUP BY 等。對(duì)于這些 SQL 語句，我們?cè)撛趺礃舆M(jìn)行優(yōu)化呢？本節(jié)將針對(duì)這些 SQL 語句介紹一些優(yōu)化的方法。

優(yōu)化 INSERT 語句

當(dāng)進(jìn)行數(shù)據(jù) INSERT 的時(shí)候，可以考慮采用以下幾種優(yōu)化方式：

如果同時(shí)從同一客戶插入很多行，盡量使用多個(gè)值表的 INSERT 語句，這種方式將大大縮減客戶端與數(shù)據(jù)庫(kù)之間的連接、關(guān)閉等消耗，使得效率比分開執(zhí)行的單個(gè) INSERT 語句快（在一些情況中幾倍）。下面是一次插入多值的一個(gè)例子：

insert into test values(1,2),(1,3),(1,4)…
? ?

• 如果從不同客戶插入很多行，能通過使用 INSERT DELAYED 語句得到更高的速度。DELAYED 的含義是讓 INSERT 語句馬上執(zhí)行，其實(shí)數(shù)據(jù)都被放在內(nèi)存的隊(duì)列中，并沒有真正寫入磁盤，這比每條語句分別插入要快的多；LOW_PRIORITY 剛好相反，在所有其他用戶對(duì)表的讀寫完后才進(jìn)行插入；

• 將索引文件和數(shù)據(jù)文件分在不同的磁盤上存放（利用建表中的選項(xiàng)）；

• 如果進(jìn)行批量插入，可以增加 bulk_insert_buffer_size 變量值的方法來提高速度。但是，這只能對(duì) MyISAM 表使用；

• 當(dāng)從一個(gè)文本文件裝載一個(gè)表時(shí)，使用 LOAD DATA INFILE。這通常比使用很多 INSERT 語句快 20 倍。

優(yōu)化 GROUP BY 語句

默認(rèn)情況下，MySQL 對(duì)所有 GROUP BY col1，col2....的字段進(jìn)行排序。這與在查詢中指定 ORDER BY col1，col2...類似。因此，如果顯式包括一個(gè)包含相同的列的 ORDER BY 子句，則對(duì) MySQL 的實(shí)際執(zhí)行性能沒有什么影響。如果查詢包括 GROUP BY，但用戶想要避免排序結(jié)果的消耗，則可以指定 ORDER BY NULL 禁止排序，如下面的例子：

EXPLAIN SELECT StockType,SUM(StockQty) FROM goods_stock GROUP BY StockType;
? ?

EXPLAIN SELECT StockType,SUM(StockQty) FROM goods_stock GROUP BY StockType ORDER BY NULL;
? ?

從上面的例子可以看出第一個(gè) SQL 語句需要進(jìn)行“filesort”，而第二個(gè) SQL 由于 ORDER BY NULL 不需要進(jìn)行“filesort”，而 filesort 往往非常耗費(fèi)時(shí)間。

優(yōu)化 ORDER BY 語句

MySQL 可以使用一個(gè)索引來滿足 ORDER BY 子句，而不需要額外的排序。WHERE 條件和 ORDER BY 使用相同的索引，并且 ORDER BY 的順序和索引順序相同，并且 ORDER BY 的字段都是升序或者都是降序。

例如下列 SQL 可以使用索引：

EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358' ORDER BY Model,LotNO;
? ?

但是在以下幾種情況下則不使用索引：

1.order by 后面跟的排序方式不一致

EXPLAIN SELECT * FROM goods_stock ORDER BY Model DESC,LotNO ASC;
? ?

2.where 條件后面跟著的查詢條件和 order by 排序的條件不一致

EXPLAIN SELECT * FROM goods_stock WHERE LotNO=2020 ORDER BY Model;
? ?

3.單純加入 order by 不加入 where 條件做過濾

EXPLAIN SELECT * FROM goods_stock ORDER BY Model,LotNO;
? ?

優(yōu)化嵌套查詢

MySQL 支持 SQL 子查詢?？梢允褂?SELECT 語句來創(chuàng)建單列的查詢結(jié)果，然后把這個(gè)結(jié)果作為過濾條件用在另一個(gè)查詢中。

使用子查詢可以一次性地完成很多邏輯上需要多個(gè)步驟才能完成的 SQL 操作，同時(shí)也可以避免事務(wù)或者表鎖死，并且寫起來也很容易。但是，有些情況下，子查詢可以被更有效率的連接（JOIN）替代。在下面的例子中，要從 goods_stock 表中找到那些在 goods_stock_price 表中不存在階梯價(jià)格的庫(kù)存：

EXPLAIN SELECT * FROM goods_stock WHERE StockGUID NOT IN (SELECT StockGUID FROM goods_stock_price);
? ?

從上面執(zhí)行計(jì)劃可以看到 goods_stock 表是走了全表掃描的，goods_stock、goods_stock_price 表查詢結(jié)果是在內(nèi)存上創(chuàng)建臨時(shí)表存儲(chǔ)的，如果使用連接（JOIN）來完成這個(gè)查詢工作，速度將會(huì)快很多。尤其是當(dāng) goods_stock_price 表中對(duì) goods_stock.StockGUID 建有索引的話，性能將會(huì)更好，具體查詢?nèi)缦拢?/p>

EXPLAIN SELECT s.* FROM goods_stock AS s LEFT JOIN goods_stock_price AS spON s.StockGUID=sp.StockGUIDWHERE sp.StockGUID IS NOT NULL;
? ?

（此部分可以走內(nèi)連接，在這不做重復(fù)說明了！）

從執(zhí)行計(jì)劃中可以明顯看出查詢掃描的記錄范圍和使用索引的情況都有了很大的改善。連接（JOIN）之所以更有效率一些，是因?yàn)?MySQL 不需要在內(nèi)存中創(chuàng)建臨時(shí)表來完成這個(gè)邏輯上的需要兩個(gè)步驟的查詢工作。

MySQL 如何優(yōu)化 OR 條件

對(duì)于含有 OR 的查詢子句，如果要利用索引，則 OR 之間的每個(gè)條件列都必須用到索引；如果沒有索引，則應(yīng)該考慮增加索引。例如，首先使用 show index 命令查看 goods_stock 表的索引，可知它有 3 個(gè)非聚集索引，在 StockGUID、LotNO 兩個(gè)字段上分別有 1 個(gè)獨(dú)立的索引，在 Model 和 Brand 字段上有 1 個(gè)復(fù)合索引。

SHOW INDEX FROM goods_stock;
? ?

然后在兩個(gè)獨(dú)立索引上面做 OR 操作，具體如下：

EXPLAIN SELECT * FROM goods_stock WHERE LotNO='2020' OR StockGUID='werer-1weq-hdf1-qgqq';
? ?

使用 SQL 提示

SQL 提示（SQL HINT）是優(yōu)化數(shù)據(jù)庫(kù)的一個(gè)重要手段，簡(jiǎn)單來說就是在 SQL 語句中加入一些人為的提示來達(dá)到優(yōu)化操作的目的。下面是一個(gè)使用 SQL 提示的例子：

SELECT SQL_BUFFER_RESULTS * FROM...
? ?

這個(gè)語句將強(qiáng)制 MySQL 生成一個(gè)臨時(shí)結(jié)果集。只要臨時(shí)結(jié)果集生成后，所有表上的鎖定均被釋放。這能在遇到表鎖定問題時(shí)或要花很長(zhǎng)時(shí)間將結(jié)果傳給客戶端時(shí)有所幫助，因?yàn)榭梢员M快釋放鎖資源。下面是一些在 MySQL 中常用的 SQL 提示。

USE INDEX

在查詢語句中表名的后面，添加 USE INDEX 來提供希望 MySQL 去參考的索引列表，就可以讓 MySQL 不再考慮其他可用的索引：

EXPLAIN SELECT * FROM goods_stock USE INDEX (idx_stock_3) WHERE LotNO='2020';
? ?

IGNORE INDEX

如果用戶只是單純地想讓 MySQL 忽略一個(gè)或者多個(gè)索引，則可以使用 IGNORE INDEX 作為 HINT。同樣是上面的例子，這次來看一下查詢過程忽略索引 idx_stock_3 的情況：

EXPLAIN SELECT * FROM goods_stock IGNORE INDEX (idx_stock_3) WHERE LotNO='2020';
? ?

從執(zhí)行計(jì)劃可以看出，系統(tǒng)忽略了指定的索引，而使用了全表掃描。

FORCE INDEX

為強(qiáng)制 MySQL 使用一個(gè)特定的索引，可在查詢中使用 FORCE INDEX 作為 HINT。例如，當(dāng)不強(qiáng)制使用索引的時(shí)候，因?yàn)?goods_stock_price.GoodsStockID（已加索引）的值都是大于 0 的，因此 MySQL 會(huì)默認(rèn)進(jìn)行全表掃描，而不使用索引，如下所示：

EXPLAIN SELECT * FROM goods_stock_price WHERE GoodsStockID>0;
? ?

但是，當(dāng)使用 FORCE INDEX 進(jìn)行提示時(shí)，即便使用索引的效率不是最高，MySQL 還是選擇使用了索引，這是 MySQL 留給用戶的一個(gè)自行選擇執(zhí)行計(jì)劃的權(quán)力。加入 FORCE INDEX 提示后再次執(zhí)行上面的 SQL：

EXPLAIN SELECT * FROM goods_stock_price FORCE INDEX(idx_stock_price_1) WHERE GoodsStockID>0;
? ?

果然，執(zhí)行計(jì)劃中使用了 FORCE INDEX 后的索引。

總結(jié)

SQL 優(yōu)化問題是數(shù)據(jù)庫(kù)性能優(yōu)化最基礎(chǔ)也是最重要的一個(gè)問題，實(shí)踐表明很多數(shù)據(jù)庫(kù)性能問題都是由不合適的 SQL 語句造成。本章通過實(shí)例描述了 SQL 優(yōu)化的一般過程，從定位一個(gè)有性能問題的 SQL 語句到分析產(chǎn)生性能問題的原因，最后到采取什么措施優(yōu)化 SQL 語句的性能。