在MySQL中如何最佳化SQL語句
- WBOY轉載
- 2023-05-26 14:07:431759瀏覽
1.概述
在應用系統開發過程中，由於初期資料量小，開發人員寫SQL語句時更重視功能上的實現，但當應用系統正式上線後，隨著生產數據量的急遽成長，許多SQL語句開始逐漸顯露出效能問題，對生產環境的影響也越來越大，此時這些有問題的SQL語句就成為整個系統效能的瓶頸，因此我們必須要對它們進行最佳化。
2.透過show status指令了解各種SQL的執行頻率
MySQL客戶端連線成功後，透過show [session|global]status指令可以提供伺服器狀態訊息，也可以在作業系統上使用mysqladmin extended-status指令來取得這些訊息。 show [session|global] status可以根據需要加上參數「session」或「global」來顯示session級（目前連接）的統計結果和global級（自資料庫上次啟動至今）的統計結果。如果不寫，預設使用參數是“session”。
下面的指令顯示了目前session中所有統計參數的值：
-- 查看会话所有统计的值 SHOW STATUS LIKE 'Com_%'; Or SHOW SESSION STATUS LIKE 'Com_%';
#下面的指令顯示了目前global中所有統計參數的值：
-- 查看全域所有統計的值
SHOW GLOBAL STATUS LIKE 'Com_%';
#Com_xxx表示每個xxx語句執行的次數，我們通常比較關心的是以下幾個統計參數：
Com_select：執行SELECT操作的次數，一次查詢只累積1。
Com_insert：執行INSERT操作的次數，對於批次插入的INSERT操作，只累積一次。
Com_update：執行UPDATE運算的次數。
Com_delete：執行DELETE操作的次數。
上面這些參數對於所有儲存引擎的表格操作都會進行累計。這些參數僅適用於InnoDB儲存引擎，其累加演算法也稍有差異。
Innodb_rows_read：SELECT查詢傳回的行數。
Innodb_rows_inserted：執行INSERT操作插入的行數。
Innodb_rows_updated：執行UPDATE作業更新的行數。
Innodb_rows_deleted：執行DELETE操作刪除的行數。
透過以上幾個參數，可以輕鬆了解目前資料庫的應用系統是以插入更新為主還是以查詢操作為主，以及各種類型的SQL大致的執行比例是多少。無論是提交或回滾，更新操作的計數都會進行累加，其計數物件為執行次數。
對於交易型的應用，透過Com_commit和Com_rollback可以了解交易提交和回溯的情況，對於回溯操作非常頻繁的資料庫，可能表示應用程式編寫有問題。此外，以下幾個參數便於使用者了解資料庫的基本情況。
Connections：試圖連接MySQL伺服器的次數。
Uptime：伺服器工作時間。
Slow_queries：慢查詢的次數。
3.定位執行效率較低的SQL語句
可以透過以下兩種方式定位執行效率較低的SQL語句。
透過慢查詢日誌定位那些執行效率較低的SQL語句，用--log-slow-queries[=file_name]選項啟動時，mysqld寫一個包含所有執行時間超過long_query_time秒的SQL語句的日誌檔。
慢查詢日誌在查詢結束以後才紀錄，所以在應用系統反映執行效率出現問題的時候查詢慢查詢日誌並不能定位問題，可以使用show processlist指令查看當前MySQL在進行的線程，包括線程的狀態、是否鎖定表等，可以即時查看SQL的執行情況，同時對一些鎖定表操作進行最佳化。
4.透過EXPLAIN分析低效率SQL的執行計畫
透過定位執行效率較低的SQL語句後，可以透過EXPLAIN或DESC指令取得MySQL如何執行SELECT語句的信息，包括在SELECT語句執行過程中表如何連接和連接的順序，例如想統計所有庫存階梯數量，需要關聯goods_stock表和goods_stock_price表，並且對goods_stock_price.Qty字段做求和（sum）操作，對應SQL 的執行計劃如下：
EXPLAIN SELECT SUM(sp.Qty) FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp ON s.ID=sp.GoodsStockID;
如上圖所示每個列的簡單解釋如下：
- ##select_type：表示SELECT 的類型，常見的取值有：
- SIMPLE（簡單表，即不使用表連接或子查詢）。
- PRIMARY（主查詢，即外層的查詢）、UNION（UNION 中的第二個或後面的查詢語句）、◎SUBQUERY（子查詢中的第一個SELECT ）等。
- table：輸出結果集的表格。
type：表示表的连接类型，性能由好到差的连接类型为：
system（表中仅有一行，即常量表）。
const（单表中最多有一个匹配行，例如primary key或者unique index）。
eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）。
ref（与eq_ref类似，区别在于不是使用primary key或者unique index，而是使用普通的索引）。
ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）。
index_merge（索引合并优化）。
unique_subquery（in的后面是一个查询主键字段的子查询）。
index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）。
range（单表中的范围查询）。
index（对于前面的每一行，都通过查询索引来得到数据）。
all（对于前面的每一行，都通过全表扫描来得到数据）。
possible_keys：表示查询时，可能使用的索引。
key：表示实际使用的索引。
key_len：索引字段的长度。
rows：扫描行的数量。
filtered：返回结果的行占需要读到的行(rows列的值)的百分比。
Extra：执行情况的说明和描述。
Using index（此值表示mysql将使用覆盖索引，以避免访问表）。
Using where（mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。“Using where”有时提示了一种可能性：查询可以从不同的索引中受益。
Using temporary（mysql 对查询结果排序时会使用临时表）。
MySQL will apply an external index sorting on the results instead of reading rows from the table in index order.。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成）。
Range checked for each record(index map: N) （没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的）。
5.确定问题并采取相应的优化措施
经过以上定位步骤，我们基本就可以分析到问题出现的原因。此时我们可以根据情况采取相应的改进措施，进行优化提高语句执行效率。
在上面的例子中，已经可以确认是goods_stock是走主键索引的，但是对goods_stock_price子表的进行了全表扫描导致效率的不理想，那么应该对goods_stock_price表的GoodsStockID字段创建索引，具体命令如下：
-- 创建索引 CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID); -- 附加删除跟查询索引语句 ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1; SHOW INDEX FROM goods_stock_price;
创建索引后，我们再看一下这条语句的执行计划，具体如下：
EXPLAIN SELECT SUM(sp.Qty) FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp ON s.ID=sp.GoodsStockID;
可以发现建立索引后对goods_stock_price子表需要扫描的行数明显减少（从 3 行减少到1行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。
以上是在MySQL中如何最佳化SQL語句的詳細內容。更多資訊請關注PHP中文網其他相關文章！