SQL mysql優化

慢查詢

如何通過慢查日志發現有問題的SQL？

• 查詢次數多且每次查詢占用時間長的SQL
  • pt-query-digest分析前幾個查詢
• IO大的SQL
  • pt-query-diges分析中的Rows examine項
• 未命中索引的SQL
  • pt-query-digest分析中Rows examine 和Rows Send的對比

如何分析SQL查詢

• 使用explain查詢SQL的執行計劃
  ? explain select custome_id,first_name,last_name from customers;
• explain返回列的含義
  table:顯示這一行的數據是哪張表的
  type: 這是重要的列，顯示連接用了何種類型，從最好到最差的類型為
  const,eq_reg、ref、range、index、ALL
  possible_keys: 顯示可能應用在這張表中的索引，如果未空，沒有可能的索引。
  key:實際使用的索引。如果為NULL，則沒有使用索引。
  key_len:使用的索引長度。在不損失精確性的情況下，長度越短越好
  ref:顯示索引被哪一列使用了，如果可能的話是一個常數
  rows:MYSQL認為必須檢查的用來返回請求數據的行數。
• 需要注意的返回列
  • Using filesort:看到這個的時候，查詢就需要優化了，MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行。
  • Using temporary 看到這個的時候就需要優化了。這里，MSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列進行Order By上，而不是Group By上。

max()和Count()的優化

查詢最后支付的時間-優化max()函數

select max(payment_date) from payment

explain select max(payment_date) from payment

建索引：

create index idx_paydate on payment(pay_date);

在一條SQL中同事查出2006年和2007年的電影的數量--優化count()函數

錯誤的方式
select count(release_year='2006' or release_year='2006') from film;
分開計算2006和2007年的電影數量
select count(*) from film where release_year='2006' AND
release_year='2007';

優化：

select count(release_year='2006' or NULL)

select count(release_year='2076' or NULL)

備注：count(*)包含空值，count(某一列) 不包含某一列

子查詢優化：

通常情況下，需要把子查詢優化為join查詢，但在優化時需要注意關聯鍵是否有一對多的關系。需要注意數據重復

groupby優化：

通過關聯子查詢優化

limit查詢的優化：

limit常用于分頁處理，時常會伴隨order by 從句使用，因此大多時候會使用Filesorts這樣會造成大量的IO。

記錄上次返回的主鍵，在下次查詢時使用主鍵過濾。

索引優化

pt-duplicate-key-checker \
-h 127.0.0.1 \
-uroot \
-p root \
--databases hand_sql \
--tables HAND_COURSE

索引使用情況分析

pt-index-usage \
-u root \ 
-p root \
mysql-slow.log

 pt-query-digest --report  /tmp/mysql-slow.log