如何定位不合理的SQL

引言
在應用的開發程序中，由于初期資料量小，開發人員寫 SQL 陳述句時更重視功能上的實作，但是當應用系統正式上線后，隨著生產資料量的急劇增長，很多SQL陳述句開始逐漸顯露出性能問題，對生產的影響也越來越大，此時這些有問題的SQL陳述句就成為整個系統性能的瓶頸，因此我們必須要對它們進行優化，本章將詳細介紹在MySQL中優化SQL陳述句的方法，

當面對一個有SQL性能問題的資料庫時，我們應該從何處入手來進行系統的分析，使得能夠盡快定位問題SQL并盡快解決問題，

4.1 如何查看SQL執行頻率

MySQL 客戶端連接成功后，通過

-- 服務器狀態資訊
show [session|global] status;

命令可以提供服務器狀態資訊，show [session|global] status 可以根據需要加上引數“session”或者“global”來顯示 session 級（當前連接）的統計結果和 global 級（自資料庫上次啟動至今）的統計結果，

如果不寫，默認使用引數是“session”，

下面的命令顯示了當前 session 中所有統計引數的值：

show status like 'Com_______';

show status like 'Innodb_rows_%';

Com_xxx 表示每個 xxx 陳述句執行的次數，我們通常比較關心的是以下幾個統計引數，

Com_*** : 這些引數對于所有存盤引擎的表操作都會進行累計，

Innodb_*** : 這幾個引數只是針對InnoDB 存盤引擎的，累加的演算法也略有不同，

4.2 如何定位低效率SQL

以下兩種方式：

• 慢查詢日志（重要） : 通過慢查詢日志定位那些執行效率較低的 SQL 陳述句，用--log-slow-queries[=file_name]選項啟動時，mysqld 寫一個包含所有執行時間超過 long_query_time 秒的 SQL 陳述句的日志檔案，

  tips：

  關于慢查詢SQL如何獲取

  參看上個章節

• show processlist （重要） :

  慢查詢日志在查詢結束以后才記錄，所以在應用反映執行效率出現問題的時候查詢慢查詢日志并不能定位問題，

  可以使用show processlist命令查看當前MySQL在進行的執行緒，包括執行緒的狀態、是否鎖表等，可以實時地查看 SQL 的執行情況，同時對一些鎖表操作進行優化，

屬性欄位解釋
1） id列，用戶登錄mysql時，系統分配的"connection_id"，可以使用函式connection_id()查看
2） user列，顯示當前用戶，如果不是root，這個命令就只顯示用戶權限范圍的sql陳述句
3） host列，顯示這個陳述句是從哪個ip的哪個埠上發的，可以用來跟蹤出現問題陳述句的用戶
4） db列，顯示這個行程目前連接的是哪個資料庫
5） command列，顯示當前連接的執行的命令，一般取值為休眠（sleep），查詢（query），連接（connect）等
6） time列，顯示這個狀態持續的時間，單位是秒
7） state列，顯示使用當前連接的sql陳述句的狀態，很重要的列，
	state描述的是陳述句執行中的某一個狀態，一個sql陳述句，以查詢為例，可能需要經過copying to tmp table、sorting result、sending data等狀態才可以完成
8） info列，顯示這個sql陳述句，是判斷問題陳述句的一個重要依據

4.3 使用explain分析執行計劃

-- explain 分析執行計劃
explain SELECT * FROM product_list WHERE store_name = '聯想北達興科專賣店';

4.3.1 環境準備

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_role_user` (`role_id`,`user_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','超級管理員');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','系統管理員');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','itcast','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','stu1','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','學生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學生2');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','t1','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','老師1');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','學生','student','學生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老師','teacher','老師');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教學管理員','teachmanager','教學管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理員','admin','管理員');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超級管理員','super','超級管理員');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;

4.3.2 explain 之 id

id 欄位是 select查詢的序列號，是一組數字，表示的是查詢中執行select子句或者是操作表的順序，

id 情況有三種 ：

1） id 相同表示加載表的順序是從上到下，

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id ;

2） id 不同id值越大，優先級越高，越先被執行，

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'))

3） id 有相同，也有不同，同時存在，id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先級越高，越先執行，

EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = (select role.id from t_user, user_role role where role.id = 10) ;

4.3.3 explain 之 select_type

表示 SELECT 的型別，常見的取值，如下表所示：

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

4.3.4 explain 之 table

展示這一行的資料是關于哪一張表的

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

4.3.5 explain 之 type

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));

type 顯示的是訪問型別，是較為重要的一個指標，可取值為：

結果值從最好到最壞依次是：

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL

一般來說， 我們需要保證查詢至少達到 range 級別， 最好達到ref ，

4.3.6 explain 之 key

possible_keys : 顯示可能應用在這張表的索引， 一個或多個， 
key ： 實際使用的索引， 如果為NULL， 則沒有使用索引，
key_len : 表示索引中使用的位元組數，len=3*n+2（n為索引欄位的長度）

EXPLAIN select * from t_role where role_name = '超級管理員'; 
select 255 * 3 + 2; -- role_name VARCHAR(255)

4.3.7 explain 之 rows

掃描行的數量，

4.3.8 explain 之 extra

其他的額外的執行計劃資訊，在該列展示 ，

EXPLAIN select u.username from t_user u order by u.username desc;

本文由傳智教育博學谷 - 狂野架構師教研團隊發布
如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力
轉載請注明出處！

標籤：其他

上一篇：python面向物件編程

下一篇：Java 大后端各種架構圖匯總（建議收藏！！）