Mysql慢SQL分析及優化

youmen發表於2021-06-22

為何對慢SQL進行治理

從資料庫角度看：每個SQL執行都需要消耗一定I/O資源，SQL執行的快慢，決定資源被佔用時間的長短。假設總資源是100，有一條慢SQL佔用了30的資源共計1分鐘。那麼在這1分鐘時間內，其他SQL能夠分配的資源總量就是70，如此迴圈，當資源分配完的時候，所有新的SQL執行將會排隊等待。
從應用的角度看：SQL執行時間長意味著等待，在OLTP應用當中，使用者的體驗較差

治理的優先順序上

master資料庫->slave資料庫
- 目前資料庫基本上都是讀寫分離架構，讀在從庫（slave）上執行，寫在主庫（master）上執行。
- 由於從庫的資料都是從主庫上覆制過去的，主庫等待較多的，會加大與從庫的複製時延。
執行次數多的SQL優先治理
如果有一類SQL高併發集中訪問某一張表，應當優先治理。

Mysql執行原理

綠色部分為SQL實際執行部分，可以發現SQL執行2大步驟：解析，執行。

以com_query為例，dispatch_command會先呼叫alloc_query為query buffer分配記憶體，之後呼叫解析

解析：詞法解析->語法解析->邏輯計劃->查詢優化->物理執行計劃

檢查是否存在可用查詢快取結果，如果沒有或者快取失效，則呼叫mysql_execute_command執行
執行：檢查使用者、表許可權->表上加共享讀鎖->取資料到query cache->取消共享讀鎖

影響因素

如不考慮MySQL資料庫的引數以及硬體I/O的影響，則影響SQL執行效率的因素主要是I/O和CPU的消耗量
總結：

資料量：資料量越大需要的I/O次數越多
取資料的方式
- 資料在快取中還是在磁碟上
- 是否可以通過索引快速定址
資料加工的方式
- 排序、子查詢等，需要先把資料取到臨時表中，再對資料進行加工
- 增加了I/O，且消耗大量CPU資源

解決思路

將資料存放在更快的地方。
- 如果資料量不大，變化頻率不高，但訪問頻率很高，此時應該考慮將資料放在應用端的快取當中或者Redis這樣的快取當中，以提高存取速度。如果資料不做過濾、關聯、排序等操作，僅按照key進行存取，且不考慮強一致性需求，也可考慮選用NoSQL資料庫。
適當合併I/O
- 分別執行select c1 from t1與select c2 from t1，與執行select c1,c2 from t1相比，後者開銷更小。
- 合併時也需要考慮執行時間的增加。
利用分散式架構
- 在面對海量的資料時，通常的做法是將資料和I/O分散到多臺主機上去執行。

案例 (mysql資料高CPU問題定位和優化)

開啟慢查詢

## 開關
slow_query_log=1 
## 檔案位置及名字 
slow_query_log_file=/data/mysql/slow.log
## 設定慢查詢時間
long_query_time=0.4
## 沒走索引的語句也記錄
log_queries_not_using_indexes

vim /etc/my.cnf
slow_query_log=1 
slow_query_log_file=/data/mysql/slow.log
long_query_time=0.1
log_queries_not_using_indexes

mysql> select @@long_query_time;	# 預設十秒才記錄慢日誌
  
mysql> show variables like 'slow_query_log%';
mysql>  show variables like 'long%';
mysql>  show variables like '%using_indexes%';

查詢一張沒有索引的100w資料的表

五十個併發查詢十t100w表,

mysqlslap --defaults-file=/etc/my.cnf \
--concurrency=50 --iterations=1 --create-schema='oldboy' \
--query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb \
--number-of-queries=10 -uroot -pZHOUjian.22 -verbose

mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 26.447 seconds
	Minimum number of seconds to run all queries: 26.447 seconds
	Maximum number of seconds to run all queries: 26.447 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

檢視系統資源消耗

mysql檢視連線執行緒

1 . 通過 show processlist; 或 show full processlist; 命令檢視當前執行的查詢，如下圖所示：

“Sending data”官網解釋：
The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

狀態的含義，原來這個狀態的名稱很具有誤導性，所謂的“Sending data”並不是單純的傳送資料，而是包括“收集 + 傳送資料”。

體現在:

1.沒有使用索引
2.mysql索引表結構，要是沒有使用主鍵查詢的話，需要進行回表操作，在返回客戶端。
3.返回的行數太多，需要頻繁io互動

Copying to tmp table，Copying to tmp table on disk：官網解釋：
Copying to tmp table The server is copying to a temporary table in memory. Copying to tmp table on disk The server is copying to a temporary table on disk. The temporary result set has become too large

整體來說生成臨時表記憶體空間，落磁碟臨時表，臨時表使用太
體現在多表join，buffer_size設定不合理，alter algrithem copy等方式

Sorting result：
For a SELECT statement, this is similar to Creating sort index, but for nontemporary tables.
結果集使用大的排序，基本上SQL語句上order by 欄位上沒有索引
上述的情況大量堆積，就會發現CPU飆升的情況，當然也有併發量太高的情況。
優化方向:

1.新增索引，組合索引，堅持2張表以內的join方式這樣查詢執行成本就會大幅減少。
2.隱私轉換避免，系統時間函式的呼叫避免
3.相關快取大小設定：join_buffer_size，sort_buffer_size，read_buffer_size ,read_rnd_buffer_size ，tmp_table_size。
在緊急情況下，無法改動下，通過引數控制併發度，執行時間 innodb_thread_concurrency ，max_execution_time都是有效的臨時控制手段。

檢視慢日誌

mysql> show variables like 'slow_query_log%';
+---------------------+----------------------+
| Variable_name       | Value                |
+---------------------+----------------------+
| slow_query_log      | ON                   |
| slow_query_log_file | /data/mysql/slow.log |
+---------------------+----------------------+
2 rows in set (0.00 sec)

分析慢日誌

[root@master1 ~]# mysqldumpslow -s c -t 10 /data/mysql/slow.log

Reading mysql slow query log from /data/mysql/slow.log
Count: 50  Time=27.10s (1354s)  Lock=0.42s (20s)  Rows=270.0 (13500), root[root]@localhost
  select * from oldboy.t_100w where k2='S'

Count: 3  Time=0.68s (2s)  Lock=0.00s (0s)  Rows=262.0 (786), root[root]@localhost
  select * from t_100w where k2='S'

Died at /usr/bin/mysqldumpslow line 167, <> chunk 53.

加索引

alter table t_100w add index idx(k2);

[root@master1 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=50 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=10 -uroot -pZHOUjian.22 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 0.075 seconds
	Minimum number of seconds to run all queries: 0.075 seconds
	Maximum number of seconds to run all queries: 0.075 seconds
	Number of clients running queries: 50
	Average number of queries per client: 0

五千個併發查詢一百t100w表,

[root@master1 ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=5000 --iterations=1 --create-schema='oldboy' --query="select * from oldboy.t_100w where k2='FGCD'" engine=innodb --number-of-queries=100 -uroot -pZHOUjian.22 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
	Running for engine rbose
	Average number of seconds to run all queries: 6.285 seconds
	Minimum number of seconds to run all queries: 6.285 seconds
	Maximum number of seconds to run all queries: 6.285 seconds
	Number of clients running queries: 5000
	Average number of queries per client: 0

優化方向和注意點

cpu優化方向

對於MySQL硬體環境資源，建議CPU起步8核開始，SSD硬碟；
索引，合理設計表結構，優化SQL。
讀寫分離，將對資料一致性不敏感的查詢轉移到只讀例項上，分擔主庫壓力。
對於由應用負載高導致的 CPU 使用率高的狀況，從應用架構、例項規格等方面來解決。
使用 Memcache 或者 Redis快取技術，儘量從快取中獲取常用的查詢結果，減輕資料庫的壓力。

mysql效能測試優化方向

系統引數：磁碟排程算，SHELL資源限制,numa架構，檔案系統ext4，exfs
重新整理mysql log相關重新整理引數：
臨近頁（innodb_flush_neighbors）
死鎖檢查機制（innodb_deadlock_detect），
雙1重新整理：sync_binlog，innodb_flush_log_at_trx_commit
併發引數: innodb_buffer_pool_instances, innodb_thread_concurrency 等
因為一些伺服器的特性，導致cpu通道和記憶體協調存在一些問題，導致cpu效能上去得案例也存在

不走索引的情況(開發規範)

1 . 沒有查詢條件，或者查詢條件沒有建立索引

select * from tab;       全表掃描。
select  * from tab where 1=1;
在業務資料庫中，特別是資料量比較大的表。
是沒有全表掃描這種需求。
1、對使用者檢視是非常痛苦的。
2、對伺服器來講毀滅性的。
（1）
select * from tab;
SQL改寫成以下語句：
select  * from  tab  order by  price  limit 10 ;    需要在price列上建立索引
（2）
select  * from  tab where name='zhangsan'          name列沒有索引
改：
1、換成有索引的列作為查詢條件
2、將name列建立索引

2 . 查詢結果集是原表中的大部分資料，應該是25％以上

查詢的結果集，超過了總數行數25%，優化器覺得就沒有必要走索引了。

假如：tab表 id，name    id:1-100w  ，id列有(輔助)索引
select * from tab  where id>500000;
如果業務允許，可以使用limit控制。
怎麼改寫 ？
結合業務判斷，有沒有更好的方式。如果沒有更好的改寫方案
儘量不要在mysql存放這個資料了。放到redis裡面。

3 . 索引本身失效，統計資料不真實

索引有自我維護的能力。
對於表內容變化比較頻繁的情況下，有可能會出現索引失效。
一般是刪除重建

現象:
有一條select語句平常查詢時很快,突然有一天很慢,會是什麼原因
select?  --->索引失效,，統計資料不真實
DML ?   --->鎖衝突

4 . 查詢條件使用函式在索引列上，或者對索引列進行運算，運算包括(+，-，*，/，! 等)

例子：
錯誤的例子：select * from test where id-1=9;
正確的例子：select * from test where id=10;
算術運算
函式運算
子查詢

5 . 隱式轉換導致索引失效.這一點應當引起重視.也是開發中經常會犯的錯誤.

這樣會導致索引失效. 錯誤的例子：
mysql> alter table tab add index inx_tel(telnum);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> desc tab;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id    | int(11)    | YES  |    | NULL    |      |
| name  | varchar(20) | YES  |    | NULL    |      |
| telnum | varchar(20) | YES  | MUL | NULL    |      |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> select * from tab where telnum='1333333';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> select * from tab where telnum=1333333;
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+

|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum=1555555;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tab  | ALL  | inx_tel      | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain  select * from tab where telnum='1555555';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key    | key_len | ref  | rows | Extra                |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | tab  | ref  | inx_tel      | inx_tel | 63      | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
mysql>

6 . <> ，not in 不走索引（輔助索引）

EXPLAIN  SELECT * FROM teltab WHERE telnum  <> '110';
EXPLAIN  SELECT * FROM teltab WHERE telnum  NOT IN ('110','119');

mysql> select * from tab where telnum <> '1555555';
+------+------+---------+
| id  | name | telnum  |
+------+------+---------+
|    1 | a    | 1333333 |
+------+------+---------+
1 row in set (0.00 sec)
mysql> explain select * from tab where telnum <> '1555555';

單獨的>,<,in 有可能走，也有可能不走，和結果集有關，儘量結合業務新增limit
or或in  儘量改成union
EXPLAIN  SELECT * FROM teltab WHERE telnum  IN ('110','119');
改寫成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119'

7 . like "%_" 百分號在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'  走range索引掃描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'  不走索引
%linux%類的搜尋需求，可以使用elasticsearch+mongodb 專門做搜尋服務的資料庫產品

建立外來鍵的規則

父子表中建立外來鍵的欄位資料型別需要一致
關聯父表時，父表的欄位需要為父表
如果父表為聯合主鍵需要從第一個欄位開始關聯
書寫問題
儲存引擎只有innodb才支援外來鍵，其他不行，否則外來鍵建立不成功
建立有外來鍵的父子表中不允許使用truncate table 只能使用delete進行刪除資料

父子表寫入資料時，如果想給子表中的外來鍵寫入資料，需要保證寫入的資料在父表的主鍵列擁有該資料才能進行新增是否新增失敗，用來保證資料的一致性

外來鍵在進行建立的過程中需要重新寫一行進行新增，不能跟在資料型別的後面進行建立

自增

# 自增，如果為某列設定自增列，插入資料時無需設定此列的值，預設將自增（表中只能有一個自增列）
create table tb1(
    id int auto_increment primary key,
    age int not null
)

show variables like '%auto_increment_%';
auto_increment_increment | 1    # 每次按照指定的數量自增
auto_increment_offset    | 1    # 自增量的初始量
set auto_increment_increment=2;

建立表定義一對多關係

create table student(
    id1 int  auto_increment primary key,
    name varchar(12) not null,
    age int not null,
    phone char(11)
);

create table student2(
    id int auto_increment primary key,
    class_id int,
    foreign key(class_id) REFERENCES student(id1)
);

新增主鍵

alter table 表名 add primary key(列名);
alter table students add id int not null auto_increment, add primary key (id);

刪除主鍵

alter table 表名 drop primary key;
# 刪除主鍵屬性，保留原值和列

alter table 表名  modify  列名 int, drop primary key;

資料庫注意事項

1、重要的sql必須被索引，例如：
1）select、update、delete語句的where條件列；
2）order by、group by、distinct欄位
2、mysql索引的限制：
1）mysql目前不支援函式索引
2）使用不等於（！=或者<>）的時候，mysql無法使用索引,單獨的>,<,in 有可能走，也有可能不走，和結果集有關，儘量結合業務新增limitor或in 儘量改成union
3）過濾欄位使用單行函式 (如 abs (column)) 後, MYSQL無法使用索引。
4） join語句中join條件欄位型別不一致的時候MYSQL 無法使用索引
5）使用 LIKE 操作的時候如果條件以萬用字元開始 (如 ‘%abc…’)時, MYSQL無法使用索引。
6）使用非等值查詢的時候, MYSQL 無法使用 Hash 索引。
7）BLOB 和 TEXT 型別的列只能建立字首索引
3、mysql常見sql規範：
1）SQL語句儘可能簡單大SQL語句儘可能拆成小SQL語句，MySQL對複雜SQL支援不好。
2）事務要簡單，整個事務的時間長度不要太長，SQL結束後及時提交。
3）限制單個事務所操作的資料集大小，不能超過 10000 條記錄
4）禁止使用觸發器、函式、儲存過程。
5）降低業務耦合度，為scale out、sharding留有餘地
6）避免在資料庫中進行數學運算（資料庫不擅長數學運算和邏輯判斷）
*7）避免使用select ，需要查詢哪幾個欄位就select這幾個欄位，避免buffer pool被無用資料填充。
8）條件中使用到OR的SQL語句必須改寫成用IN()（OR的效率比IN低很多）
9）IN()裡面的資料個數建議控制在 500 以內，可以用exist代替in，exist在某些場景比in效率高，儘量不使
用not in。
10）limit分頁注意效率。 limit越大，效率越低。可以改寫limit，例如：
select id from test limit 10000,10 可以改寫為 select id from test where id > 10000 limit 10
11）當只要一行資料時使用LIMIT 1 。
12）獲取大量資料時，建議分批次獲取資料，每次獲取資料少於 10000 條，結果集應小於 1M
13）避免使用大表做 JOIN，使用group by分組、自動排序
14）SQL語句禁止出現隱式轉換，例如：select id from test where id=’1’，其中 id 列為 int 等數字
型別。
15）在SQL中，儘量不使用like，且禁止使用字首是%的like匹配。
16）合理選擇union all與union
17）禁止在OLTP型別系統中使用沒有where條件的查詢。
18）使用 prepared statement 語句，只傳引數，比傳遞 SQL 語句更高效；一次解析，多次使用；降低SQL
注入概率。
19）禁止使用 order by rand().
20）禁止單條 SQL 語句同時更新多個表。
21）不在業務高峰期批量更新或查詢資料庫，避免在業務高峰期alter表。
22）禁止在主庫上執行 sum,count 等複雜的統計分析語句，可以使用從庫來執行。

《MySQL慢查詢優化》之SQL語句及索引優化
2020-12-06
MySql優化索引
MySQL慢日誌功能分析及優化增強
2018-10-19
MySql優化
慢Sql優化思路
2020-12-18
SQL優化
MySQL 慢查詢優化
2019-06-23
MySql優化
MySQL慢日誌優化
2021-12-16
MySql優化
慢SQL優化實戰筆記
2020-07-26
SQL優化筆記
MySQL-SQL優化
2021-02-06
MySql優化
Mysql 慢查詢優化實踐
2019-01-29
MySql優化
MYSQL SQL語句優化
2019-02-22
MySql優化
MySQL SQL優化案例(一)
2021-11-30
MySql優化
MySQL之SQL優化技巧
2021-08-27
MySql優化
Sql語句本身的優化-定位慢查詢
2018-10-11
SQL優化
MySQL之SQL語句優化
2022-05-25
MySql優化
MYSQL資料庫------SQL優化
2020-12-05
MySql資料庫優化
MySQL資料庫的索引原理、與慢SQL優化的5大原則
2019-04-03
MySql資料庫索引優化
MySQL：慢SQL（slow_log）
2023-05-17
MySql
MySQL索引原理及慢查詢最佳化
2020-09-20
MySql索引
MySQL 規範及優化
2018-03-07
MySql優化
mysql索引原理及優化
2021-06-20
MySql索引優化
MySQL 資料庫與 SQL 優化
2019-01-06
MySql資料庫優化
十七、Mysql之SQL優化查詢
2018-12-17
MySql優化
MySQL之SQL優化詳解（二）
2019-06-30
MySql優化
MySQL之SQL優化詳解（三）
2019-07-01
MySql優化
MySQL之SQL優化詳解（一）
2019-06-20
MySql優化
MySQL的SQL效能優化總結
2019-02-16
MySql優化
MYSQL sql執行過程的一些跟蹤分析(二.mysql優化器追蹤分析)
2019-01-28
MySql優化
Hive使用Calcite CBO優化流程及SQL優化實戰
2020-09-22
Hive優化SQL
MySQL 索引使用策略及優化
2019-08-07
MySql索引優化
MySQL函式索引及優化
2020-07-04
MySql函式索引優化
mysql 表資料量大量查詢慢如何優化
2021-03-08
MySql優化
MySQL 52個SQL效能優化策略SQL語句彙總
2022-01-17
MySql優化
mysql優化 | 儲存引擎，建表，索引，sql的優化建議
2019-02-01
MySql優化儲存引擎索引
sql語句執行緩慢分析
2018-11-19
SQL
TiDB 查詢優化及調優系列（三）慢查詢診斷監控及排查
2022-05-06
TiDB優化
MySQL效能優化之簡單sql改寫
2019-11-12
MySql優化
mysql查詢效率慢的SQL語句
2018-12-12
MySql
【MySQL】MySQL的執行計劃及索引優化
2019-02-25
MySql索引優化
Mysql 慢日誌分析工具Mysqldumpslow
2020-09-21
MySql