幹掉mysql慢查詢

實時分析(

show full processlist;

)結合延後分析(

mysql.slow_log

),對SQL語句進行優化

設定慢查詢參數

slow_query_log 1
log_queries_not_using_indexes OFF
long_query_time 5
slow_query_log 1             

實時分析

檢視有哪些線程正在執行

show processlist;
show full processlist;
           

相比

show processlist;

我比較喜歡用.因為這個查詢可以用where條件

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST where state !='' order by state,time desc,command ;
-- 按照用戶端IP對目前連接配接使用者進行分組
SELECT substring_index(Host,':',1) as h,count(Host)  as c,user FROM INFORMATION_SCHEMA.PROCESSLIST  group by h  order by c desc,user;
-- 按使用者名對目前連接配接使用者進行分組
SELECT substring_index(Host,':',1) as h,count(Host)  as c,user FROM INFORMATION_SCHEMA.PROCESSLIST  group by user  order by c desc,user;           

各種耗時SQL對應的特征

• 改表

1. Copying to tmp table

• 記憶體不夠用,轉成磁盤

1. Copying to tmp table on disk

• 傳輸資料量大

1. Reading from net
2. Sending data

• 沒有索引

2. Sorting result
3. Creating sort index

重點關注這些狀态,參考

processlist中哪些狀态要引起關注

進行優化

延後分析

# 建資料庫
CREATE TABLE `slow_log_2019-05-30` (
  `start_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `user_host` mediumtext NOT NULL,
  `query_time` time(6) NOT NULL,
  `lock_time` time(6) NOT NULL,
  `rows_sent` int(11) NOT NULL,
  `rows_examined` int(11) NOT NULL,
  `db` varchar(512) NOT NULL,
  `last_insert_id` int(11) NOT NULL,
  `insert_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `sql_text` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  KEY `idx_start_time` (`start_time`),
  KEY `idx_query_time` (`query_time`),
  KEY `idx_lock_time` (`lock_time`),
  KEY `idx_rows_examined` (`rows_examined`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- insert into slow_log.slow_log_2019-05-30 select * from mysql.slow_log;
-- truncate table mysql.slow_log ;
select * FROM slow_log.`slow_log_2019-05-30`
where sql_text not like 'xxx`%'
order by  query_time desc,query_time desc;           

按優先級排列,需要關注的列是

lock_time

,

query_time

rows_examined

.分析的時候應用二八法則,先找出最坑爹的那部分SQL,率先優化掉,然後不斷not like或者删除掉排除掉已經優化好的低效SQL.

低效SQL的優化思路

對于每一個查詢,先用

explain SQL

分析一遍,是比較明智的做法.

一般而言,rows越少越好,提防Extra:

Using where

這種情況,這種情況一般是掃全表,在資料量大(>10萬)的時候考慮增加索引.

慎用子查詢

盡力避免嵌套子查詢，使用索引來優化它們

EXPLAIN SELECT *
FROM (
    SELECT *
    FROM `s`.`t`
    WHERE status IN (-15, -11)
    LIMIT 0, 10
) a
ORDER BY a.modified DESC           

比如說這種的,根本毫無必要.表面上看,比去掉子查詢更快一點,實際上是因為mysql 5.7對子查詢進行了優化,生成了

Derived table

,把結果集做了一層緩存.

按照實際的場景分析發現,

status

這個字段沒有做索引,導緻查詢變成了全表掃描(using where),加了索引後,問題解決.

json類型

json資料類型,如果存入的JSON很長,讀取出來自然越慢.在實際場景中,首先要确定是否有使用這一類型的必要,其次,盡量隻取所需字段.

見過這樣寫的

WHERE j_a like '%"sid":514572%'           

這種行為明顯是對mysql不熟悉,MYSQL是有JSON提取函數的.

WHERE JSON_EXTRACT(j_a, "$[0].sid")=514572;           

雖然也是全表掃描,但怎麼說也比like全模糊查詢好吧?

更好的做法,是通過虛拟字段建索引

MySQL · 最佳實踐 · 如何索引JSON字段

但是現階段MYSQL對json的索引做的是不夠的,如果json資料列過大,建議還是存

MongoDB

(見過把12萬json存mysql的,那讀取速度簡直無語).

字元串類型

WHERE a=1           

用數字給字元串類型的字段指派會導緻該字段上的索引失效.

WHERE a='1'           

分組查詢

group by

count(x)

sum(x)

,慎用.非常消耗CPU

group by

select col_1 from table_a where (col_2 > 7 or mtsp_col_2 > 0) and col_3 = 1 group by col_1           

這種不涉及聚合查詢(

count(x)

sum(x)

)的

group by

明顯就是不合理的,去重複查詢效果更高點

select distinct(col_1) from table_a where (col_2 > 7 or mtsp_col_2 > 0) and col_3 = 1 limit xxx;           

count(x)

sum(x)

x 這個字段最好帶索引,不然就算篩選條件有索引也會很慢

order by x

x這字段最好帶上索引,不然

show processlist;

裡面可能會出現大量

Creating sort index

的結果

組合索引失效

組合索引有個最左比對原則

KEY 'idx_a' (a,b,c)           

WHERE b='' and c =''           

這時組合索引是無效的.

其他

EXPLAIN SQL
DESC SQL           

# INNODB_TRX表主要是包含了正在InnoDB引擎中執行的所有事務的資訊，包括waiting for a lock和running的事務
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.innodb_locks;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;           

參考連結

1. MySQL慢查詢日志總結
2. MySQL CPU 使用率高的原因和解決方法
3. mysql優化，導緻查詢不走索引的原因總結
4. information_schema中Innodb相關表用于分析sql查詢鎖的使用情況介紹