1.概述
前面我們介紹了MySQL中怎么樣通過索引來優化查詢，日常開發中，除了使用查詢外，我們還會使用一些其他的常用SQL，比如 INSERT、GROUP BY等，對于這些SQL陳述句，我們該怎么樣進行優化呢？本節將針對這些SQL陳述句介紹一些優化的方法，
2.優化INSERT陳述句
當進行資料INSERT的時候，可以考慮采用以下幾種優化方式：
●如果同時從同一客戶插入很多行，盡量使用多個值表的INSERT陳述句，這種方式將大大縮減客戶端與資料庫之間的連接、關閉等消耗，使得效率比分開執行的單個INSERT陳述句快（在一些情況中幾倍），下面是一次插入多值的一個例子：
insert into test values(1,2),(1,3),(1,4)…
●如果從不同客戶插入很多行，能通過使用INSERT DELAYED陳述句得到更高的速度，DELAYED的含義是讓INSERT陳述句馬上執行，其實資料都被放在記憶體的佇列中，并沒有真正寫入磁盤，這比每條陳述句分別插入要快的多；LOW_PRIORITY剛好相反，在所有其他用戶對表的讀寫完后才進行插入；
●將索引檔案和資料檔案分在不同的磁盤上存放（利用建表中的選項）；
●如果進行批量插入，可以增加bulk_insert_buffer_size變數值的方法來提高速度，但是，這只能對MyISAM表使用；
●當從一個文本檔案裝載一個表時，使用LOAD DATA INFILE，這通常比使用很多INSERT陳述句快20倍，
3.優化GROUP BY陳述句
默認情況下，MySQL對所有GROUP BY col1，col2....的欄位進行排序，這與在查詢中指定ORDER BY col1，col2...類似，因此，如果顯式包括一個包含相同的列的ORDER BY子句，則對MySQL的實際執行性能沒有什么影響，如果查詢包括GROUP BY，但用戶想要避免排序結果的消耗，則可以指定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;
從上面的例子可以看出第一個SQL陳述句需要進行“filesort”，而第二個SQL由于ORDER BY NULL不需要進行“filesort”，而filesort往往非常耗費時間，
4.優化ORDER BY陳述句
在某些情況中，MySQL可以使用一個索引來滿足ORDER BY子句，而不需要額外的排序，WHERE條件和ORDER BY使用相同的索引，并且ORDER BY的順序和索引順序相同，并且ORDER BY的欄位都是升序或者都是降序，例如下列SQL可以使用索引：
EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358' ORDER BY Model,LotNO;
但是在以下幾種情況下則不使用索引：
EXPLAIN SELECT * FROM goods_stock ORDER BY Model DESC,LotNO ASC;
EXPLAIN SELECT * FROM goods_stock WHERE LotNO=2020 ORDER BY Model;
EXPLAIN SELECT * FROM goods_stock ORDER BY Model,LotNO;
5.優化嵌套查詢
MySQL支持SQL的子查詢，可以使用SELECT陳述句來創建一個單列的查詢結果，然后把這個結果作為過濾條件用在另一個查詢中，使用子查詢可以一次性地完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務或者表鎖死，并且寫起來也很容易，但是，有些情況下，子查詢可以被更有效率的連接（JOIN）替代，在下面的例子中，要從goods_stock表中找到那些在goods_stock_price表中不存在階梯價格的庫存：
EXPLAIN SELECT * FROM goods_stock WHERE StockGUID NOT IN (SELECT StockGUID FROM goods_stock_price);
從上面執行計劃可以看到goods_stock表是走了全表掃描的，goods_stock、goods_stock_price表查詢結果是在記憶體上創建臨時表存盤的，如果使用連接（JOIN）來完成這個查詢作業，速度將會快很多，尤其是當goods_stock_price表中對 goods_stock.StockGUID建有索引的話，性能將會更好，具體查詢如下：
EXPLAIN SELECT s.* FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp ON s.StockGUID=sp.StockGUID WHERE sp.StockGUID IS NOT NULL;
從執行計劃中可以明顯看出查詢掃描的記錄范圍和使用索引的情況都有了很大的改善，連接（JOIN）之所以更有效率一些，是因為MySQL不需要在記憶體中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢作業，
6.MySQL如何優化OR條件
對于含有OR的查詢子句，如果要利用索引，則OR之間的每個條件列都必須用到索引；如果沒有索引，則應該考慮增加索引，例如，首先使用show index命令查看goods_stock表的索引，可知它有3個非聚集索引，在StockGUID、LotNO兩個欄位上分別有1個獨立的索引，在Model和Brand欄位上有1個復合索引，
SHOW INDEX FROM goods_stock;
然后在兩個獨立索引上面做OR操作，具體如下：
EXPLAIN SELECT * FROM goods_stock WHERE LotNO='2020' OR StockGUID='werer-1weq-hdf1-qgqq';
可以發現查詢正確的用到了索引，并且從執行計劃的描述中可以發現MySQL在處理含有OR字句的查詢時，實際是對OR的各個欄位分別查詢后的結果進行了UNION，但是當在建有復合索引的列Model和Brand上面做OR操作的時候，卻不能用到索引，具體結果如下：
EXPLAIN SELECT * FROM goods_stock WHERE Model='LM358' OR Brand='2020';
7.使用SQL提示
SQL提示（SQL HINT）是優化資料庫的一個重要手段，簡單來說就是在SQL陳述句中加入一些人為的提示來達到優化操作的目的，下面是一個使用SQL提示的例子：
SELECT SQL_BUFFER_RESULTS * FROM...
這個陳述句將強制MySQL生成一個臨時結果集，只要臨時結果集生成后，所有表上的鎖定均被釋放，這能在遇到表鎖定問題時或要花很長時間將結果傳給客戶端時有所幫助，因為可以盡快釋放鎖資源，下面是一些在MySQL中常用的SQL提示，
7.1USE INDEX
在查詢陳述句中表名的后面，添加USE INDEX來提供希望MySQL去參考的索引串列，就可以讓MySQL不再考慮其他可用的索引：
EXPLAIN SELECT * FROM goods_stock USE INDEX (idx_stock_3) WHERE LotNO='2020';
7.2IGNORE INDEX
如果用戶只是單純地想讓MySQL忽略一個或者多個索引，則可以使用IGNORE INDEX作為HINT，同樣是上面的例子，這次來看一下查詢程序忽略索引idx_stock_3的情況：
EXPLAIN SELECT * FROM goods_stock IGNORE INDEX (idx_stock_3) WHERE LotNO='2020';
從執行計劃可以看出，系統忽略了指定的索引，而使用了全表掃描，
7.3FORCE INDEX
為強制MySQL使用一個特定的索引，可在查詢中使用FORCE INDEX作為HINT，例如，當不強制使用索引的時候，因為goods_stock_price.GoodsStockID（已加索引）的值都是大于0的，因此MySQL會默認進行全表掃描，而不使用索引，如下所示：
EXPLAIN SELECT * FROM goods_stock_price WHERE GoodsStockID>0;
但是，當使用FORCE INDEX進行提示時，即便使用索引的效率不是最高，MySQL還是選擇使用了索引，這是MySQL留給用戶的一個自行選擇執行計劃的權力，加入FORCE INDEX提示后再次執行上面的SQL：
EXPLAIN SELECT * FROM goods_stock_price FORCE INDEX(idx_stock_price_1) WHERE GoodsStockID>0;
果然，執行計劃中使用了FORCE INDEX后的索引，
8.總結
SQL優化問題是資料庫性能優化最基礎也是最重要的一個問題，實踐表明很多資料庫性能問題都是由不合適的SQL陳述句造成，本章通過實體描述了SQL優化的一般程序，從定位一個有性能問題的SQL陳述句到分析產生性能問題的原因，最后到采取什么措施優化SQL陳述句的性能，另外還介紹了優化SQL陳述句經常需要考慮的幾個方面，比如索引、表分析、排序等，
參考文獻：
深入淺出MySQL大全
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/270998.html
標籤：MySQL