在應(yīng)用的的開發(fā)過程中衙傀,由于初期數(shù)據(jù)量小,開發(fā)人員寫 SQL 語句時更重視功能上的實(shí)現(xiàn)萨咕,但是當(dāng)應(yīng)用系統(tǒng)正式上線后稼钩,隨著生產(chǎn)數(shù)據(jù)量的急劇增長肢扯,很多 SQL 語句開始逐漸顯露出性能問題,對生產(chǎn)的影響也越來越大,此時這些有問題的 SQL 語句就成為整個系統(tǒng)性能的瓶頸造烁,因此我們必須要對它們進(jìn)行優(yōu)化,本章將詳細(xì)介紹在 MySQL 中優(yōu)化 SQL 語句的方法樊零。
當(dāng)面對一個有 SQL 性能問題的數(shù)據(jù)庫時刨肃,我們應(yīng)該從何處入手來進(jìn)行系統(tǒng)的分析,使得能夠盡快定位問題 SQL 并盡快解決問題盅弛。
3.1 查看SQL執(zhí)行頻率
MySQL 客戶端連接成功后钱骂,通過 show [session|global] status 命令可以提供服務(wù)器狀態(tài)信息。show [session|global] status 可以根據(jù)需要加上參數(shù)“session”或者“global”來顯示 session 級(當(dāng)前連接)的計結(jié)果和 global 級(自數(shù)據(jù)庫上次啟動至今)的統(tǒng)計結(jié)果挪鹏。如果不寫见秽,默認(rèn)使用參數(shù)是“session”。
下面的命令顯示了當(dāng)前 session 中所有統(tǒng)計參數(shù)的值:
show status like 'Com_______';
show global status like 'Com_______';
show status like 'Innodb_rows_%';
Com_xxx 表示每個 xxx 語句執(zhí)行的次數(shù)讨盒,我們通常比較關(guān)心的是以下幾個統(tǒng)計參數(shù)解取。
Com_*** : 這些參數(shù)對于所有存儲引擎的表操作都會進(jìn)行累計。
Innodb_*** : 這幾個參數(shù)只是針對InnoDB 存儲引擎的返顺,累加的算法也略有不同禀苦。
3.2 定位低效率執(zhí)行SQL
可以通過以下兩種方式定位執(zhí)行效率較低的 SQL 語句。
- 慢查詢?nèi)罩?: 通過慢查詢?nèi)罩径ㄎ荒切﹫?zhí)行效率較低的 SQL 語句遂鹊,用--log-slow-queries[=file_name]選項(xiàng)啟動時振乏,mysqld 寫一個包含所有執(zhí)行時間超過 long_query_time 秒的 SQL 語句的日志文件。具體可以查看本書第 26 章中日志管理的相關(guān)部分秉扑。
-
show processlist : 慢查詢?nèi)罩驹诓樵兘Y(jié)束以后才紀(jì)錄慧邮,所以在應(yīng)用反映執(zhí)行效率出現(xiàn)問題的時候查詢慢查詢?nèi)罩静⒉荒芏ㄎ粏栴},可以使用show processlist命令查看當(dāng)前MySQL在進(jìn)行的線程,包括線程的狀態(tài)误澳、是否鎖表等耻矮,可以實(shí)時地查看 SQL 的執(zhí)行情況,同時對一些鎖表操作進(jìn)行優(yōu)化脓匿。
1) id列淘钟,用戶登錄mysql時,系統(tǒng)分配的"connection_id"陪毡,可以使用函數(shù)connection_id()查看
2) user列米母,顯示當(dāng)前用戶。如果不是root毡琉,這個命令就只顯示用戶權(quán)限范圍的sql語句
3) host列铁瞒,顯示這個語句是從哪個ip的哪個端口上發(fā)的,可以用來跟蹤出現(xiàn)問題語句的用戶
4) db列桅滋,顯示這個進(jìn)程目前連接的是哪個數(shù)據(jù)庫
5) command列慧耍,顯示當(dāng)前連接的執(zhí)行的命令,一般取值為休眠(sleep)丐谋,查詢(query)芍碧,連接(connect)等
6) time列,顯示這個狀態(tài)持續(xù)的時間号俐,單位是秒
7) state列泌豆,顯示使用當(dāng)前連接的sql語句的狀態(tài),很重要的列吏饿。state描述的是語句執(zhí)行中的某一個狀態(tài)踪危。一個sql語句,以查詢?yōu)槔砺洌赡苄枰?jīng)過copying to tmp table贞远、sorting result、sending data等狀態(tài)才可以完成
8) info列笨忌,顯示這個sql語句蓝仲,是判斷問題語句的一個重要依據(jù)
3.3 explain分析執(zhí)行計劃
通過以上步驟查詢到效率低的 SQL 語句后,可以通過 EXPLAIN或者 DESC命令獲取 MySQL如何執(zhí)行 SELECT 語句的信息官疲,包括在 SELECT 語句執(zhí)行過程中表如何連接和連接的順序杂曲。
查詢SQL語句的執(zhí)行計劃 :
explain select * from order_1991 where order_id = 10;
explain select * from tb_item where title = '阿爾卡特 (OT-979) 冰川白 聯(lián)通3G手機(jī)3';
3.3.1 環(huán)境準(zhǔn)備
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`),
UNIQUE KEY `unique_user_username` (`username`)
) 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 `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) 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','系統(tǒng)管理員');
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','學(xué)生1');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','stu2','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','學(xué)生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','學(xué)生','student','學(xué)生');
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','教學(xué)管理員','teachmanager','教學(xué)管理員');
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') ;
3.3.2 explain 之 id
id 字段是 select查詢的序列號,是一組數(shù)字袁余,表示的是查詢中執(zhí)行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值越大颖榜,優(yōu)先級越高,越先被執(zhí)行。
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相同的可以認(rèn)為是一組且蓬,從上往下順序執(zhí)行欣硼;在所有的組中,id的值越大恶阴,優(yōu)先級越高诈胜,越先執(zhí)行。
EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id ;
3.3.3 explain 之 select_type
表示 SELECT 的類型冯事,常見的取值焦匈,如下表所示:執(zhí)行效率逐級遞減
UBQUERY/DEPENDENT SUBQUERY
SUBQUERY:子查詢中首個SELECT(如果有多個子查詢存在):
DEPENDENT SUBQUERY:子查詢中首個SELECT,但依賴于外層的表(如果有多個子查詢存在)
特別關(guān)注 DEPENDENT SUBQUERY
1 會嚴(yán)重消耗性能
2 不會進(jìn)行子查詢昵仅,會先進(jìn)行外部查詢,生成結(jié)果集,再在內(nèi)部進(jìn)行關(guān)聯(lián)查詢
3 子查詢的執(zhí)行效率受制于外層查詢的記錄數(shù)
4 可以嘗試改成join查詢
3.3.4 explain 之 table
展示這一行的數(shù)據(jù)是關(guān)于哪一張表的
3.3.5 explain 之 type
type 顯示的是訪問類型缓熟,是較為重要的一個指標(biāo),可取值為:
結(jié)果值從最好到最壞以此是:
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
==一般來說摔笤, 我們需要保證查詢至少達(dá)到 range 級別够滑, 最好達(dá)到ref 。==
3.3.6 explain 之 key
possible_keys : 顯示可能應(yīng)用在這張表的索引吕世, 一個或多個彰触。
key : 實(shí)際使用的索引, 如果為NULL寞冯, 則沒有使用索引渴析。
key_len : 表示索引中使用的字節(jié)數(shù), 該值為索引字段最大可能長度吮龄,并非實(shí)際使用長度俭茧,在不損失精確性的前提下, 長度越短越好 漓帚。
3.3.7 explain 之 rows
掃描行的數(shù)量母债。
3.3.8 explain 之 extra
其他的額外的執(zhí)行計劃信息,在該列展示 尝抖。
3.4 show profile分析SQL
Mysql從5.0.37版本開始增加了對 show profiles 和 show profile 語句的支持毡们。show profiles 能夠在做SQL優(yōu)化時幫助我們了解時間都耗費(fèi)到哪里去了。
通過 have_profiling 參數(shù)昧辽,能夠看到當(dāng)前MySQL是否支持profile:
默認(rèn)profiling是關(guān)閉的衙熔,可以通過set語句在Session級別開啟profiling:
set profiling=1; //開啟profiling 開關(guān);
通過profile搅荞,我們能夠更清楚地了解SQL執(zhí)行的過程红氯。
首先框咙,我們可以執(zhí)行一系列的操作,如下圖所示:
show databases;
use db01;
show tables;
select * from tb_item where id < 5;
select count(*) from tb_item;
執(zhí)行完上述命令之后痢甘,再執(zhí)行show profiles 指令喇嘱, 來查看SQL語句執(zhí)行的耗時:
通過show profile for query query_id 語句可以查看到該SQL執(zhí)行過程中每個線程的狀態(tài)和消耗的時間:
TIP :
Sending data 狀態(tài)表示MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端,而不僅僅是返回個客戶端塞栅。由于在Sending data狀態(tài)下者铜,MySQL線程往往需要做大量的磁盤讀取操作,所以經(jīng)常是整各查詢中耗時最長的狀態(tài)放椰。
在獲取到最消耗時間的線程狀態(tài)后作烟,MySQL支持進(jìn)一步選擇all、cpu庄敛、block io 俗壹、context switch、page faults等明細(xì)類型類查看MySQL在使用什么資源上耗費(fèi)了過高的時間藻烤。例如绷雏,選擇查看CPU的耗費(fèi)時間 :
3.5 trace分析優(yōu)化器執(zhí)行計劃
MySQL5.6提供了對SQL的跟蹤trace, 通過trace文件能夠進(jìn)一步了解為什么優(yōu)化器選擇A計劃, 而不是選擇B計劃。
打開trace 怖亭, 設(shè)置格式為 JSON涎显,并設(shè)置trace最大能夠使用的內(nèi)存大小,避免解析過程中因?yàn)槟J(rèn)內(nèi)存過小而不能夠完整展示兴猩。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
執(zhí)行SQL語句 :
select * from tb_item where id < 4;
最后期吓, 檢查information_schema.optimizer_trace就可以知道MySQL是如何執(zhí)行SQL的 :
select * from information_schema.optimizer_trace\G;
*************************** 1. row ***************************
QUERY: select * from tb_item where id < 4
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `tb_item`.`id` AS `id`,`tb_item`.`title` AS `title`,`tb_item`.`price` AS `price`,`tb_item`.`num` AS `num`,`tb_item`.`categoryid` AS `categoryid`,`tb_item`.`status` AS `status`,`tb_item`.`sellerid` AS `sellerid`,`tb_item`.`createtime` AS `createtime`,`tb_item`.`updatetime` AS `updatetime` from `tb_item` where (`tb_item`.`id` < 4)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`tb_item`.`id` < 4)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`tb_item`.`id` < 4)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`tb_item`.`id` < 4)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`tb_item`.`id` < 4)"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`tb_item`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`tb_item`",
"range_analysis": {
"table_scan": {
"rows": 9816098,
"cost": 2.04e6
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"id < 4"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 3,
"cost": 1.6154,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 3,
"ranges": [
"id < 4"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 3,
"cost_for_plan": 1.6154,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`tb_item`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "range",
"rows": 3,
"cost": 2.2154,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 2.2154,
"rows_for_plan": 3,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`tb_item`.`id` < 4)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`tb_item`",
"attached": "(`tb_item`.`id` < 4)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"refine_plan": [
{
"table": "`tb_item`",
"access_type": "range"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}