MySQL8.0 SQL语句执行计划
* SQL语句只是告诉了数据库要做什么，并没有告诉数据库如何做，查看SQL语句的执行计划可以使SQL的执行过程从黑盒变成白盒。
* 在SQL语句前面加上EXPLAIN即可查看SQL语句的执行计划，但不会实际执行这个SQL语句。
* 显示SQL语句的执行计划的格式有三种，分别是：传统（TRADITIONAL）、JSON和树形（TREE）格式。可以使用FORMAT=TRADITIONAL|JSON|TREE来指定格式，默认是传统格式。
* 显示SQL语句的执行计划不光支持select，还支持delete、insert、replace和update，但 explain analyze是例外。
1.实验准备
1.1创建需要的表和数据
省份表的创建与插入
//省份表
create table Provincial(pid int,Provincial varchar(50),primary key (pid));
insert into Provincial values(1,'北京市');
insert into Provincial values(2,'天津市');
insert into Provincial values(3,'上海市');
insert into Provincial values(4,'重庆市');
insert into Provincial values(5,'河北省');
insert into Provincial values(6,'山西省');
insert into Provincial values(7,'台湾省');
insert into Provincial values(8,'辽宁省');
insert into Provincial values(9,'吉林省');
insert into Provincial values(10,'黑龙江省');
insert into Provincial values(11,'江苏省');
insert into Provincial values(12,'浙江省');
insert into Provincial values(13,'安徽省');
insert into Provincial values(14,'福建省');
insert into Provincial values(15,'江西省');
insert into Provincial values(16,'山东省');
insert into Provincial values(17,'河南省');
insert into Provincial values(18,'湖北省');
insert into Provincial values(19,'湖南省');
insert into Provincial values(20,'广东省');
insert into Provincial values(21,'甘肃省');
insert into Provincial values(22,'四川省');
insert into Provincial values(23,'贵州省');
insert into Provincial values(24,'海南省');
insert into Provincial values(25,'云南省');
insert into Provincial values(26,'青海省');
insert into Provincial values(27,'陕西省');
insert into Provincial values(28,'广西壮族自治区');
insert into Provincial values(29,'西藏自治区');
insert into Provincial values(30,'宁夏回族自治区');
insert into Provincial values(31,'新疆维吾尔自治区');
insert into Provincial values(32,'内蒙古自治区');
insert into Provincial values(33,'澳门特别行政区');
insert into Provincial values(34,'香港特别行政区');
城市表的创建与插入
//城市表
create table City(
cid int not null,
city varchar(50) primary key,
pid int,
CONSTRAINT fk_city_pid
FOREIGN KEY(pid) REFERENCES Provincial(pid)
);```
------------------------------------------------------------------------
```sql
insert into City values(1,'北京市',1);
insert into City values(1,'天津市',2);
insert into City values(1,'上海市',3);
insert into City values(1,'重庆市',4);
insert into City values(1,'石家庄市',5);
insert into City values(2,'唐山市',5);
insert into City values(3,'秦皇岛市',5);
insert into City values(4,'邯郸市',5);
insert into City values(5,'邢台市',5);
insert into City values(6,'保定市',5);
insert into City values(7,'张家口市',5);
insert into City values(8,'承德市',5);
insert into City values(9,'沧州市',5);
insert into City values(10,'廊坊市',5);
insert into City values(11,'衡水市',5);
insert into City values(1,'太原市',6);
insert into City values(2,'大同市',6);
insert into City values(3,'阳泉市',6);
insert into City values(4,'长治市',6);
insert into City values(5,'晋城市',6);
insert into City values(6,'朔州市',6);
insert into City values(7,'晋中市',6);
insert into City values(8,'运城市',6);
insert into City values(9,'忻州市',6);
insert into City values(10,'临汾市',6);
insert into City values(11,'吕梁市',6);
insert into City values(1,'台北市',7);
insert into City values(2,'高雄市',7);
insert into City values(3,'基隆市',7);
insert into City values(4,'台中市',7);
insert into City values(5,'台南市',7);
insert into City values(6,'新竹市',7);
insert into City values(7,'嘉义市',7);
insert into City values(8,'台北县',7);
insert into City values(9,'宜兰县',7);
insert into City values(10,'桃园县',7);
insert into City values(11,'新竹县',7);
insert into City values(12,'苗栗县',7);
insert into City values(13,'台中县',7);
insert into City values(14,'彰化县',7);
insert into City values(15,'南投县',7);
insert into City values(16,'云林县',7);
insert into City values(17,'嘉义县',7);
insert into City values(18,'台南县',7);
insert into City values(19,'高雄县',7);
insert into City values(20,'屏东县',7);
insert into City values(21,'澎湖县',7);
insert into City values(22,'台东县',7);
insert into City values(23,'花莲县',7);
insert into City values(1,'沈阳市',8);
insert into City values(2,'大连市',8);
insert into City values(3,'鞍山市',8);
insert into City values(4,'抚顺市',8);
insert into City values(5,'本溪市',8);
insert into City values(6,'丹东市',8);
insert into City values(7,'锦州市',8);
insert into City values(8,'营口市',8);
insert into City values(9,'阜新市',8);
insert into City values(10,'辽阳市',8);
insert into City values(11,'盘锦市',8);
insert into City values(12,'铁岭市',8);
insert into City values(13,'朝阳市',8);
insert into City values(14,'葫芦岛市',8);
insert into City values(1,'长春市',9);
insert into City values(2,'吉林市',9);
insert into City values(3,'四平市',9);
insert into City values(4,'辽源市',9);
insert into City values(5,'通化市',9);
insert into City values(6,'白山市',9);
insert into City values(7,'松原市',9);
insert into City values(8,'白城市',9);
insert into City values(9,'延边朝鲜族自治州',9);
insert into City values(1,'哈尔滨市',10);
insert into City values(2,'齐齐哈尔市',10);
insert into City values(3,'鹤 岗 市',10);
insert into City values(4,'双鸭山市',10);
insert into City values(5,'鸡 西 市',10);
insert into City values(6,'大 庆 市',10);
insert into City values(7,'伊 春 市',10);
insert into City values(8,'牡丹江市',10);
insert into City values(9,'佳木斯市',10);
insert into City values(10,'七台河市',10);
insert into City values(11,'黑 河 市',10);
insert into City values(12,'绥 化 市',10);
insert into City values(13,'大兴安岭地区',10);
insert into City values(1,'南京市',11);
insert into City values(2,'无锡市',11);
insert into City values(3,'徐州市',11);
insert into City values(4,'常州市',11);
insert into City values(5,'苏州市',11);
insert into City values(6,'南通市',11);
insert into City values(7,'连云港市',11);
insert into City values(8,'淮安市',11);
insert into City values(9,'盐城市',11);
insert into City values(10,'扬州市',11);
insert into City values(11,'镇江市',11);
insert into City values(12,'泰州市',11);
insert into City values(13,'宿迁市',11);
insert into City values(1,'杭州市',12);
insert into City values(2,'宁波市',12);
insert into City values(3,'温州市',12);
insert into City values(4,'嘉兴市',12);
insert into City values(5,'湖州市',12);
insert into City values(6,'绍兴市',12);
insert into City values(7,'金华市',12);
insert into City values(8,'衢州市',12);
insert into City values(9,'舟山市',12);
insert into City values(10,'台州市',12);
insert into City values(11,'丽水市',12);
insert into City values(1,'合肥市',13);
insert into City values(2,'芜湖市',13);
insert into City values(3,'蚌埠市',13);
insert into City values(4,'淮南市',13);
insert into City values(5,'马鞍山市',13);
insert into City values(6,'淮北市',13);
insert into City values(7,'铜陵市',13);
insert into City values(8,'安庆市',13);
insert into City values(9,'黄山市',13);
insert into City values(10,'滁州市',13);
insert into City values(11,'阜阳市',13);
insert into City values(12,'宿州市',13);
insert into City values(13,'巢湖市',13);
insert into City values(14,'六安市',13);
insert into City values(15,'亳州市',13);
insert into City values(16,'池州市',13);
insert into City values(17,'宣城市',13);
insert into City values(1,'福州市',14);
insert into City values(2,'厦门市',14);
insert into City values(3,'莆田市',14);
insert into City values(4,'三明市',14);
insert into City values(5,'泉州市',14);
insert into City values(6,'漳州市',14);
insert into City values(7,'南平市',14);
insert into City values(8,'龙岩市',14);
insert into City values(9,'宁德市',14);
insert into City values(1,'南昌市',15);
insert into City values(2,'景德镇市',15);
insert into City values(3,'萍乡市',15);
insert into City values(4,'九江市',15);
insert into City values(5,'新余市',15);
insert into City values(6,'鹰潭市',15);
insert into City values(7,'赣州市',15);
insert into City values(8,'吉安市',15);
insert into City values(9,'宜春市',15);
insert into City values(10,'抚州市',15);
insert into City values(11,'上饶市',15);
insert into City values(1,'济南市',16);
insert into City values(2,'青岛市',16);
insert into City values(3,'淄博市',16);
insert into City values(4,'枣庄市',16);
insert into City values(5,'东营市',16);
insert into City values(6,'烟台市',16);
insert into City values(7,'潍坊市',16);
insert into City values(8,'济宁市',16);
insert into City values(9,'泰安市',16);
insert into City values(10,'威海市',16);
insert into City values(11,'日照市',16);
insert into City values(12,'莱芜市',16);
insert into City values(13,'临沂市',16);
insert into City values(14,'德州市',16);
insert into City values(15,'聊城市',16);
insert into City values(16,'滨州市',16);
insert into City values(17,'菏泽市',16);
insert into City values(1,'郑州市',17);
insert into City values(2,'开封市',17);
insert into City values(3,'洛阳市',17);
insert into City values(4,'平顶山市',17);
insert into City values(5,'安阳市',17);
insert into City values(6,'鹤壁市',17);
insert into City values(7,'新乡市',17);
insert into City values(8,'焦作市',17);
insert into City values(9,'濮阳市',17);
insert into City values(10,'许昌市',17);
insert into City values(11,'漯河市',17);
insert into City values(12,'三门峡市',17);
insert into City values(13,'南阳市',17);
insert into City values(14,'商丘市',17);
insert into City values(15,'信阳市',17);
insert into City values(16,'周口市',17);
insert into City values(17,'驻马店市',17);
insert into City values(18,'济源市',17);
insert into City values(1,'武汉市',18);
insert into City values(2,'黄石市',18);
insert into City values(3,'十堰市',18);
insert into City values(4,'荆州市',18);
insert into City values(5,'宜昌市',18);
insert into City values(6,'襄樊市',18);
insert into City values(7,'鄂州市',18);
insert into City values(8,'荆门市',18);
insert into City values(9,'孝感市',18);
insert into City values(10,'黄冈市',18);
insert into City values(11,'咸宁市',18);
insert into City values(12,'随州市',18);
insert into City values(13,'仙桃市',18);
insert into City values(14,'天门市',18);
insert into City values(15,'潜江市',18);
insert into City values(16,'神农架林区',18);
insert into City values(17,'恩施土家族苗族自治州',18);
insert into City values(1,'长沙市',19);
insert into City values(2,'株洲市',19);
insert into City values(3,'湘潭市',19);
insert into City values(4,'衡阳市',19);
insert into City values(5,'邵阳市',19);
insert into City values(6,'岳阳市',19);
insert into City values(7,'常德市',19);
insert into City values(8,'张家界市',19);
insert into City values(9,'益阳市',19);
insert into City values(10,'郴州市',19);
insert into City values(11,'永州市',19);
insert into City values(12,'怀化市',19);
insert into City values(13,'娄底市',19);
insert into City values(14,'湘西土家族苗族自治州',19);
insert into City values(1,'广州市',20);
insert into City values(2,'深圳市',20);
insert into City values(3,'珠海市',20);
insert into City values(4,'汕头市',20);
insert into City values(5,'韶关市',20);
insert into City values(6,'佛山市',20);
insert into City values(7,'江门市',20);
insert into City values(8,'湛江市',20);
insert into City values(9,'茂名市',20);
insert into City values(10,'肇庆市',20);
insert into City values(11,'惠州市',20);
insert into City values(12,'梅州市',20);
insert into City values(13,'汕尾市',20);
insert into City values(14,'河源市',20);
insert into City values(15,'阳江市',20);
insert into City values(16,'清远市',20);
insert into City values(17,'东莞市',20);
insert into City values(18,'中山市',20);
insert into City values(19,'潮州市',20);
insert into City values(20,'揭阳市',20);
insert into City values(21,'云浮市',20);
insert into City values(1,'兰州市',21);
insert into City values(2,'金昌市',21);
insert into City values(3,'白银市',21);
insert into City values(4,'天水市',21);
insert into City values(5,'嘉峪关市',21);
insert into City values(6,'武威市',21);
insert into City values(7,'张掖市',21);
insert into City values(8,'平凉市',21);
insert into City values(9,'酒泉市',21);
insert into City values(10,'庆阳市',21);
insert into City values(11,'定西市',21);
insert into City values(12,'陇南市',21);
insert into City values(13,'临夏回族自治州',21);
insert into City values(14,'甘南藏族自治州',21);
insert into City values(1,'成都市',22);
insert into City values(2,'自贡市',22);
insert into City values(3,'攀枝花市',22);
insert into City values(4,'泸州市',22);
insert into City values(5,'德阳市',22);
insert into City values(6,'绵阳市',22);
insert into City values(7,'广元市',22);
insert into City values(8,'遂宁市',22);
insert into City values(9,'内江市',22);
insert into City values(10,'乐山市',22);
insert into City values(11,'南充市',22);
insert into City values(12,'眉山市',22);
insert into City values(13,'宜宾市',22);
insert into City values(14,'广安市',22);
insert into City values(15,'达州市',22);
insert into City values(16,'雅安市',22);
insert into City values(17,'巴中市',22);
insert into City values(18,'资阳市',22);
insert into City values(19,'阿坝藏族羌族自治州',22);
insert into City values(20,'甘孜藏族自治州',22);
insert into City values(21,'凉山彝族自治州',22);
insert into City values(1,'贵阳市',23);
insert into City values(2,'六盘水市',23);
insert into City values(3,'遵义市',23);
insert into City values(4,'安顺市',23);
insert into City values(5,'铜仁地区',23);
insert into City values(6,'毕节地区',23);
insert into City values(7,'黔西南布依族苗族自治州',23);
insert into City values(8,'黔东南苗族侗族自治州',23);
insert into City values(9,'黔南布依族苗族自治州',23);
insert into City values(1,'海口市',24);
insert into City values(2,'三亚市',24);
insert into City values(3,'五指山市',24);
insert into City values(4,'琼海市',24);
insert into City values(5,'儋州市',24);
insert into City values(6,'文昌市',24);
insert into City values(7,'万宁市',24);
insert into City values(8,'东方市',24);
insert into City values(9,'澄迈县',24);
insert into City values(10,'定安县',24);
insert into City values(11,'屯昌县',24);
insert into City values(12,'临高县',24);
insert into City values(13,'白沙黎族自治县',24);
insert into City values(14,'昌江黎族自治县',24);
insert into City values(15,'乐东黎族自治县',24);
insert into City values(16,'陵水黎族自治县',24);
insert into City values(17,'保亭黎族苗族自治县',24);
insert into City values(18,'琼中黎族苗族自治县',24);
insert into City values(1,'昆明市',25);
insert into City values(2,'曲靖市',25);
insert into City values(3,'玉溪市',25);
insert into City values(4,'保山市',25);
insert into City values(5,'昭通市',25);
insert into City values(6,'丽江市',25);
insert into City values(7,'思茅市',25);
insert into City values(8,'临沧市',25);
insert into City values(9,'文山壮族苗族自治州',25);
insert into City values(10,'红河哈尼族彝族自治州',25);
insert into City values(11,'西双版纳傣族自治州',25);
insert into City values(12,'楚雄彝族自治州',25);
insert into City values(13,'大理白族自治州',25);
insert into City values(14,'德宏傣族景颇族自治州',25);
insert into City values(15,'怒江傈傈族自治州',25);
insert into City values(16,'迪庆藏族自治州',25);
insert into City values(1,'西宁市',26);
insert into City values(2,'海东地区',26);
insert into City values(3,'海北藏族自治州',26);
insert into City values(4,'黄南藏族自治州',26);
insert into City values(5,'海南藏族自治州',26);
insert into City values(6,'果洛藏族自治州',26);
insert into City values(7,'玉树藏族自治州',26);
insert into City values(8,'海西蒙古族藏族自治州',26);
insert into City values(1,'西安市',27);
insert into City values(2,'铜川市',27);
insert into City values(3,'宝鸡市',27);
insert into City values(4,'咸阳市',27);
insert into City values(5,'渭南市',27);
insert into City values(6,'延安市',27);
insert into City values(7,'汉中市',27);
insert into City values(8,'榆林市',27);
insert into City values(9,'安康市',27);
insert into City values(10,'商洛市',27);
insert into City values(1,'南宁市',28);
insert into City values(2,'柳州市',28);
insert into City values(3,'桂林市',28);
insert into City values(4,'梧州市',28);
insert into City values(5,'北海市',28);
insert into City values(6,'防城港市',28);
insert into City values(7,'钦州市',28);
insert into City values(8,'贵港市',28);
insert into City values(9,'玉林市',28);
insert into City values(10,'百色市',28);
insert into City values(11,'贺州市',28);
insert into City values(12,'河池市',28);
insert into City values(13,'来宾市',28);
insert into City values(14,'崇左市',28);
insert into City values(1,'拉萨市',29);
insert into City values(2,'那曲地区',29);
insert into City values(3,'昌都地区',29);
insert into City values(4,'山南地区',29);
insert into City values(5,'日喀则地区',29);
insert into City values(6,'阿里地区',29);
insert into City values(7,'林芝地区',29);
insert into City values(1,'银川市',30);
insert into City values(2,'石嘴山市',30);
insert into City values(3,'吴忠市',30);
insert into City values(4,'固原市',30);
insert into City values(5,'中卫市',30);
insert into City values(1,'乌鲁木齐市',31);
insert into City values(2,'克拉玛依市',31);
insert into City values(3,'石河子市 ',31);
insert into City values(4,'阿拉尔市',31);
insert into City values(5,'图木舒克市',31);
insert into City values(6,'五家渠市',31);
insert into City values(7,'吐鲁番市',31);
insert into City values(8,'阿克苏市',31);
insert into City values(9,'喀什市',31);
insert into City values(10,'哈密市',31);
insert into City values(11,'和田市',31);
insert into City values(12,'阿图什市',31);
insert into City values(13,'库尔勒市',31);
insert into City values(14,'昌吉市 ',31);
insert into City values(15,'阜康市',31);
insert into City values(16,'米泉市',31);
insert into City values(17,'博乐市',31);
insert into City values(18,'伊宁市',31);
insert into City values(19,'奎屯市',31);
insert into City values(20,'塔城市',31);
insert into City values(21,'乌苏市',31);
insert into City values(22,'阿勒泰市',31);
insert into City values(1,'呼和浩特市',32);
insert into City values(2,'包头市',32);
insert into City values(3,'乌海市',32);
insert into City values(4,'赤峰市',32);
insert into City values(5,'通辽市',32);
insert into City values(6,'鄂尔多斯市',32);
insert into City values(7,'呼伦贝尔市',32);
insert into City values(8,'巴彦淖尔市',32);
insert into City values(9,'乌兰察布市',32);
insert into City values(10,'锡林郭勒盟',32);
insert into City values(11,'兴安盟',32);
insert into City values(12,'阿拉善盟',32);
insert into City values(1,'澳门特别行政区',33);
insert into City values(1,'香港特别行政区',34);
2.解读SQL的执行计划
在MySQL官方文档中的第8章Optimization中有关于explain的详细介绍。
https://dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html
2.1传统格式
传统格式提供了执行计划的概况、索引的使用等基本信息，下面是一个查询SQL语句的执行计划的例子：
mysql> explain select city from City where pid=(select pid from Provincial where Provincial='安徽省');
root@db 14:06: [testdb]> explain select city from City where pid=(select pid from Provincial where Provincial='安徽省');
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | City | NULL | ref | fk_city_pid | fk_city_pid | 5 | const | 17 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | Provincial | NULL | ALL | NULL | NULL | NULL | NULL | 34 | 10.00 | Using where |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.11 sec)
执行计划中有两个ref：
type：连接类型，ALL是全表扫描，这是成本最高的访问方式，ref是使用非唯一索引访问，const 是使用主键或唯一索引访问。 ref：索引过滤的字段，const代表常量。
警告信息
EXPLAIN语句执行完成后提示有一个警告信息，警告信息里包括的是优化器重写的伪SQL，这个SQL不一定是能执行的，使用\W打开\w关闭警告信息。
下面的命令显示前面的SQL执行计划的警告信息：
mysql> show warnings\G
root@db 14:08: [testdb]> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `testdb`.`City`.`city` AS `city` from `testdb`.`City` where (`testdb`.`City`.`pid` = (/* select#2 */ select `testdb`.`Provincial`.`pid` from `testdb`.`Provincial` where (`testdb`.`Provincial`.`Provincial` = '安徽省')))
1 row in set (0.00 sec)
2.2JSON格式
JSON格式提供了以JSON格式显示的详细执行计划，这个格式适合于被程序调用，例如图形工具workbench显示的图形化的执行计划就是调用了JSON格式的接口。下面是前面SQL语句的JSON 格式的执行计划：
mysql> explain format=json select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
root@db 14:20: [testdb]> explain format=json select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
EXPLAIN
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2.02"
},
"table": {
"table_name": "City",
"access_type": "ref",
"possible_keys": [
"fk_city_pid"
],
"key": "fk_city_pid",
"used_key_parts": [
"pid"
],
"key_length": "5",
"ref": [
"const"
],
"rows_examined_per_scan": 17,
"rows_produced_per_join": 17,
"filtered": "100.00",
"using_index": true,
"cost_info": {
"read_cost": "0.33",
"eval_cost": "1.70",
"prefix_cost": "2.03",
"data_read_per_join": "2K"
},
"used_columns": [
"city",
"pid"
],
"attached_condition": "(`testdb`.`City`.`pid` = (/* select#2 */ select `testdb`.`Provincial`.`pid` from `testdb`.`Provincial` where (`testdb`.`Provincial`.`Provincial` = '安徽省')))",
"attached_subqueries": [
{
"dependent": false,
"cacheable": true,
"query_block": {
"select_id": 2,
"cost_info": {
"query_cost": "3.65"
},
"table": {
"table_name": "Provincial",
"access_type": "ALL",
"rows_examined_per_scan": 34,
"rows_produced_per_join": 3,
"filtered": "10.00",
"cost_info": {
"read_cost": "3.31",
"eval_cost": "0.34",
"prefix_cost": "3.65",
"data_read_per_join": "544"
},
"used_columns": [
"pid",
"Provincial"
],
"attached_condition": "(`testdb`.`Provincial`.`Provincial` = '安徽省')"
}
}
}
]
}
}
}
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
在JSON格式中cost_info元素提供了估算的执行成本。
2.3图形方式
MySQL的图形工具MySQL Workbench可以以图形方式显示JSON格式的执行计划。在MySQL Workbench里显示执行计划有两种方法：
（1）在SQL语句没有执行时，在SQL语句的输入框的上方有个闪电上面带放大镜的图标，点击这个图标就会生产这个SQL语句的执行计划。这种方式适合于SQL语句执行时间长，或者SQL语句将修改数据的情况下使用。
（2）执行SQL语句后，在输出结果的右边有个“Execution Plan”的图标，点击这个图标也会以图形方式显示SQL语句的执行计划。
在这个图形里，每个方框代表一个执行步骤，方框的上面左边数字是估算的执行成本，右边数据是估算的输出行数，下边是表名和索引名，其中索引名用加粗的方式显示。方框的颜色和相对执行成本相关，从低到高依次是：蓝、绿、黄、橘、红。把鼠标停留在方框上还可以显示更加详细的信息，例如这个图里鼠标就停留在最下面的一步。
MySQL Workbench下载链接：
https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-workbench-community-8.0.36-winx64.msi
MySQL Workbench安装步骤(略)
2.4树形格式
树形格式是从MySQL 8.0.18开始引入格式，它提供的执行计划比传统的执行计划更详细，输出格式是树形的，例如：
explain format=tree select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
root@db 14:42: [testdb]> explain format=tree select city from City where pid=(select pid from Provincial where Provincial='安徽省');
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (City.pid = (select #2)) (cost=2.03 rows=17)
-> Covering index lookup on City using fk_city_pid (pid=(select #2)) (cost=2.03 rows=17)
-> Select #2 (subquery in condition; run only once)
-> Filter: (Provincial.Provincial = '安徽省') (cost=3.65 rows=3.4)
-> Table scan on Provincial (cost=3.65 rows=34)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
EXPLAIN ANALYZE
EXPLAIN ANALYZE实际上是树形的执行计划的扩展，它不但提供了执行计划，还检测并执行了SQL语句，提供了执行过程中的实际度量，例如：
root@db 14:43: [testdb]> EXPLAIN ANALYZE select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
EXPLAIN
| -> Filter: (City.pid = (select #2)) (cost=2.03 rows=17) (actual time=0.0186..0.0237 rows=17 loops=1)
-> Covering index lookup on City using fk_city_pid (pid=(select #2)) (cost=2.03 rows=17) (actual time=0.0176..0.0208 rows=17 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Filter: (Provincial.Provincial = '安徽省') (cost=3.65 rows=3.4) (actual time=0.0452..0.0522 rows=1 loops=1)
-> Table scan on Provincial (cost=3.65 rows=34) (actual time=0.0374..0.0448 rows=34 loops=1)
|
1 row in set (0.00 sec)
ERROR:
No query specified
explain for connection
在实际工作中，如果发现一个正在执行的SQL语句耗时很长，这时想查询它的执行计划，通常的做法是使用EXPLAIN生成这个SQL语句的执行计划，但因为统计信息等原因，生成的执行计划和正在执行的执行计划可能不完全相同，更好的做法是使用explain for connection查询当前正在使用的执行计划。
下面的SQL查询出当前的会话号：
root@db 14:49: [testdb]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 49 |
+-----------------+
1 row in set (0.00 sec)
或者使用show processlist查询会话号。
root@db 14:49: [testdb]> show processlist \G;
*************************** 1. row ***************************
Id: 5
User: event_scheduler
Host: localhost
db: NULL
Command: Daemon
Time: 424042
State: Waiting on empty queue
Info: NULL
*************************** 2. row ***************************
Id: 49
User: root
Host: localhost
db: testdb
Command: Query
Time: 0
State: init
Info: show processlist
2 rows in set, 1 warning (0.01 sec)
ERROR:
No query specified
在当前的会话中执行一个慢SQL语句：
session 1：
root@db 16:12: [(none)]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 53 |
+-----------------+
1 row in set (0.00 sec)
root@db 16:13: [(none)]> use testdb;
Database changed
root@db 16:13: [testdb]> select sleep(60),city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
根据会话号在其他会话里查询正在执行的SQL语句的执行计划：
session 2：
root@db 16:13: [(none)]> explain for connection 53\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: City
partitions: NULL
type: ref
possible_keys: fk_city_pid
key: fk_city_pid
key_len: 5
ref: const
rows: 17
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: Provincial
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 34
filtered: 10.00
Extra: Using where
2 rows in set (0.00 sec)
对于非select语句的支持抑制输出，并不真正执行SQL语句。
mysql> explain analyze update City set cid=1000000 where city='七台河市';\G
root@db 16:18: [testdb]> explain analyze update City set cid=1000000 where city='七台河市';\G
+----------------------------------------+
| EXPLAIN |
+----------------------------------------+
|
|
+----------------------------------------+
1 row in set (0.00 sec)
ERROR:
No query specified
3.SQL执行性能的评估方法查看SQL执行性能的最简单方法是看SQL执行完成的时间，除此之外还有：
1). 性能视图
2). 状态变量
3). Explain analyze
4). 操作系统层监控
执行时间对比
root@db 16:21: [testdb]> select * from City where city='七台河市';
+-----+--------------+------+
| cid | city | pid |
+-----+--------------+------+
| 10 | 七台河市 | 10 |
+-----+--------------+------+
1 row in set (0.00 sec)
root@db 16:22: [testdb]> select * from City where cid=10;
+-----+--------------------------------+------+
| cid | city | pid |
+-----+--------------------------------+------+
| 10 | 七台河市 | 10 |
| 10 | 临汾市 | 6 |
| 10 | 乐山市 | 22 |
| 10 | 台州市 | 12 |
| 10 | 威海市 | 16 |
| 10 | 定安县 | 24 |
| 10 | 廊坊市 | 5 |
| 10 | 扬州市 | 11 |
| 10 | 抚州市 | 15 |
| 10 | 桃园县 | 7 |
| 10 | 滁州市 | 13 |
| 10 | 红河哈尼族彝族自治州 | 25 |
| 10 | 许昌市 | 17 |
| 10 | 辽阳市 | 8 |
| 10 | 锡林郭勒盟 | 32 |
| 10 | 黄冈市 | 18 |
+-----+--------------------------------+------+
16 rows in set (0.00 sec)
3.1性能视图
MySQL自带的performance_schema和sys数据库的很多性能视图记录了SQL语句的执行性能:
$ mysqlshow performance_schema|grep events_statements_
| events_statements_current |
| events_statements_histogram_by_digest |
| events_statements_histogram_global |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
等待时间最长的3个SQL语句
下面语句找出等待时间最长的3个SQL语句,注意观察其中与性能相关的字段：
root@db 16:31: [information_schema]> select * from performance_schema.events_statements_summary_by_digest where schema_name!='performance_schema' order by sum_timer_wait desc limit 3\G
*************************** 1. row ***************************
SCHEMA_NAME: testdb
DIGEST: 6a32a3e132e7b15efde27d53fad0a7bb999b96b9af7d54eaae3efacc399dbc48
DIGEST_TEXT: CALL `insert_t1` ( )
COUNT_STAR: 4
SUM_TIMER_WAIT: 40848925289729000
MIN_TIMER_WAIT: 10119106493937000
AVG_TIMER_WAIT: 10212231322432000
MAX_TIMER_WAIT: 10302688002287000
SUM_LOCK_TIME: 13000000
SUM_ERRORS: 4
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 0
SUM_ROWS_EXAMINED: 0
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 0
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 0
SUM_NO_GOOD_INDEX_USED: 0
SUM_CPU_TIME: 0
MAX_CONTROLLED_MEMORY: 1077608
MAX_TOTAL_MEMORY: 1435118
COUNT_SECONDARY: 0
FIRST_SEEN: 2024-02-21 19:52:51.208172
LAST_SEEN: 2024-02-23 18:16:48.285364
QUANTILE_95: 18446744073709551615
QUANTILE_99: 18446744073709551615
QUANTILE_999: 18446744073709551615
QUERY_SAMPLE_TEXT: call insert_t1()
QUERY_SAMPLE_SEEN: 2024-02-23 18:16:48.285364
QUERY_SAMPLE_TIMER_WAIT: 10302688002287000
*************************** 2. row ***************************
SCHEMA_NAME: testdb
DIGEST: ea48c5dfb2e3702c8f956462f340f1d57507ab677020f7c09aabe812874dba10
DIGEST_TEXT: SELECT `sleep` (?) , `city` FROM `City` WHERE `pid` = ( SELECT `pid` FROM `Provincial` WHERE `Provincial` = ? )
COUNT_STAR: 2
SUM_TIMER_WAIT: 972709175684000
MIN_TIMER_WAIT: 162706368783000
AVG_TIMER_WAIT: 486354587842000
MAX_TIMER_WAIT: 810002806901000
SUM_LOCK_TIME: 8000000
SUM_ERRORS: 2
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 20
SUM_ROWS_EXAMINED: 89
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 2
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 2
SUM_NO_GOOD_INDEX_USED: 0
SUM_CPU_TIME: 0
MAX_CONTROLLED_MEMORY: 79136
MAX_TOTAL_MEMORY: 118454
COUNT_SECONDARY: 0
FIRST_SEEN: 2024-02-26 15:12:27.017699
LAST_SEEN: 2024-02-26 16:16:04.948317
QUANTILE_95: 831763771102695
QUANTILE_99: 831763771102695
QUANTILE_999: 831763771102695
QUERY_SAMPLE_TEXT: select sleep(60),city from City where pid=(select pid from Provincial where Provincial='安徽省')
QUERY_SAMPLE_SEEN: 2024-02-26 15:12:27.017699
QUERY_SAMPLE_TIMER_WAIT: 810002806901000
*************************** 3. row ***************************
SCHEMA_NAME: testdb
DIGEST: 533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a
DIGEST_TEXT: SELECT * FROM `t1`
COUNT_STAR: 1
SUM_TIMER_WAIT: 2885147375000
MIN_TIMER_WAIT: 2885147375000
AVG_TIMER_WAIT: 2885147375000
MAX_TIMER_WAIT: 2885147375000
SUM_LOCK_TIME: 4000000
SUM_ERRORS: 0
SUM_WARNINGS: 0
SUM_ROWS_AFFECTED: 0
SUM_ROWS_SENT: 8000000
SUM_ROWS_EXAMINED: 8000000
SUM_CREATED_TMP_DISK_TABLES: 0
SUM_CREATED_TMP_TABLES: 0
SUM_SELECT_FULL_JOIN: 0
SUM_SELECT_FULL_RANGE_JOIN: 0
SUM_SELECT_RANGE: 0
SUM_SELECT_RANGE_CHECK: 0
SUM_SELECT_SCAN: 1
SUM_SORT_MERGE_PASSES: 0
SUM_SORT_RANGE: 0
SUM_SORT_ROWS: 0
SUM_SORT_SCAN: 0
SUM_NO_INDEX_USED: 1
SUM_NO_GOOD_INDEX_USED: 0
SUM_CPU_TIME: 0
MAX_CONTROLLED_MEMORY: 20576
MAX_TOTAL_MEMORY: 117496
COUNT_SECONDARY: 0
FIRST_SEEN: 2024-02-26 16:16:19.752100
LAST_SEEN: 2024-02-26 16:16:19.752100
QUANTILE_95: 3019951720402
QUANTILE_99: 3019951720402
QUANTILE_999: 3019951720402
QUERY_SAMPLE_TEXT: select * from t1
QUERY_SAMPLE_SEEN: 2024-02-26 16:16:19.752100
QUERY_SAMPLE_TIMER_WAIT: 2885147375000
3 rows in set (0.00 sec)
I/O性能
首先重置performance_schema.file_summary_by_event_name视图：
mysql> truncate table performance_schema.file_summary_by_event_name;
然后执行一个全表扫描的语句：
root@db 16:33: [information_schema]> select count(*) from testdb.City where cid=10;
+----------+
| count(*) |
+----------+
| 16 |
+----------+
1 row in set (0.00 sec)
最后查询等待事件wait/io/file/innodb/innodb_data_file的性能信息，这些信息反映了全表扫描的性能：
select event_name, count_read, avg_timer_read/1000000000.0 "Avg Read Time
(ms)", sum_number_of_bytes_read/1024/1024 "MB Read" from performance_schema.file_summary_by_event_name where event_name='wait/io/file/innodb/innodb_data_file'\G
root@db 16:34: [information_schema]> select event_name, count_read, avg_timer_read/1000000000.0 "Avg Read Time
"> (ms)", sum_number_of_bytes_read/1024/1024 "MB Read" from performance_schema.file_summary_by_event_name where event_name='wait/io/file/innodb/innodb_data_file'\G
*************************** 1. row ***************************
event_name: wait/io/file/innodb/innodb_data_file
count_read: 0
Avg Read Time
(ms): 0.0000
MB Read: 0.00000000
1 row in set (0.00 sec)
3.2状态变量
MySQL的自带了479个状态变量（MySQL 8.0.22）用以反映MySQL的运行状态，在mysql客户端里可以使用下面的命令查询会话和全局的状态变量：
mysql> show session status;
mysql> show global status;
1).使用mysqladmin监控性能
也可以使用mysqladmin extended-status查询全局的状态变量。如果要查看一段时间状态变量的变化情况，可以使用下面的命令：
$ mysqladmin -uroot -pP@ssw0rd -h172.17.0.35 -P3306 extended-status -ri60 -c3|tee my_status
其中：-i60表示每60秒重复执行一次，-r表示显示相邻两次查询的差值，-c3表示重复查询3次，tee 命令表示把输出结果同时保存到文件。
2).SQL语句的计数器
Com_XXX是SQL语句的计数器，其中Com是command的缩写，XXX是指的SQL语句类型，这些计数器包括：
Com_begin
Com_commit
Com_delete
Com_insert
Com_select
Com_update
3).查询这些SQL语句的计数器可以了解当前实例执行SQL语句的情况，可以使用下面的命令查询这些计数器：
mysqladmin -uroot -pP@ssw0rd -h172.17.0.35 -P3306 extended-status | grep Com_ |grep -E 'begin|commit|delete|insert|select|update'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
|Com_begin|0|
|Com_commit|0|
|Com_delete|6
|Com_delete_multi|0|
|Com_insert|8000724
4).查询这些计数器在10秒间隔的变化值：
mysqladmin -uroot -pP@ssw0rd -h172.17.0.35 -P3306 extended-status -ri10 -c9| grep Com_ |grep -E 'begin|commit|delete|insert|select|update'
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
| Com_begin| 0 |
| Com_commit | 0 |
| Com_delete | 6 |
| Com_delete_multi | 0 |
| Com_insert | 8000724 |
| Com_insert_select| 2325 |
| Com_replace_select | 0 |
| Com_select | 2784 |
| Com_update | 162 |
| Com_update_multi | 3 |
| Com_xa_commit | 0
5).处理InnoDB表的行数的计数器
Innodb_rows_XXX是对应SQL语句处理InnoDB表的行数的计数器，XXX是指的SQL语句类型
Innodb_rows_deleted
Innodb_rows_inserted Innodb_rows_read
Innodb_rows_updated
6).查询这些行数的计数器可以了解当前实例处理行数的情况，可以使用下面的命令查询这些计数器：
$ mysqladmin -uroot -pP@ssw0rd -h172.17.0.35 -P3306 | grep Innodb_rows
7).查询这些计数器在10秒间隔的变化值：
$ mysqladmin -uroot -pP@ssw0rd -h172.17.0.35 -P3306 extended-status -ri10 -c9 | grep Innodb_rows
8).查询单个SQL的状态参数
Handler_*计数器统计了句柄操作，句柄API是MySQL和存储引擎之间的接口，其中
Handler_read_*对调试SQL语句的性能很有用，在执行SQL语句之前，可以先使用flush status将当前会话的状态变量重置为零：
mysql> flush status;
然后执行一条SQL语句：
root@db 11:50: [testdb]> select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
+--------------+
| city |
+--------------+
| 亳州市 |
| 六安市 |
| 合肥市 |
| 安庆市 |
| 宣城市 |
| 宿州市 |
| 巢湖市 |
| 池州市 |
| 淮北市 |
| 淮南市 |
| 滁州市 |
| 芜湖市 |
| 蚌埠市 |
| 铜陵市 |
| 阜阳市 |
| 马鞍山市 |
| 黄山市 |
+--------------+
17 rows in set (0.00 sec)
ERROR:
No query specified
root@db 11:50: [testdb]> show session status like 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 1 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 17 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 35 |
+-----------------------+-------+
7 rows in set (0.00 sec)
对比另外一个SQL语句：
select * from City where city='亳州市';
last_query_cost状态变量
查询最后执行的SQL语句的估算成本（注意不是实际执行成本，MySQL里没有实际执行成本）：
mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
观察状态变量值，可以看到最后一个SQL语句执行了一次索引访问，估计执行成本是1。
如果要查询其他会话的状态变量值
可以查询视图performance_schema.status_by_thread，例如下面的SQL查询所有会话中的状态变量Handler_write：
root@db 11:53: [testdb]> select * from performance_schema.status_by_thread where variable_name='Handler_write';
+-----------+---------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+---------------+----------------+
| 121 | Handler_write | 0 |
| 122 | Handler_write | 2 |-- 插入记录最多的线程
| 123 | Handler_write | 1 |
+-----------+---------------+----------------+
3 rows in set (0.01 sec)
慢查询日志中的状态变量
打开查询慢查询日志后，如果同时将系统参数log_slow_extra设置为true，也会记录和慢SQL语句相关的状态变量。
3.3Explain analyze
使用explain analyze既可以看到估计成本，也能看到实际执行用时和访问的行数，而且每一步都可以看到这些计量信息，这样对精确定位SQL语句执行瓶颈很有帮助。
root@db 11:54: [testdb]> explain analyze select city from City where pid=(select pid from Provincial where Provincial='安徽省');\G
+---------------------------+
| EXPLAIN |
+---------------------------+
| -> Filter: (City.pid = (select #2)) (cost=2.03 rows=17) (actual time=0.0138..0.019 rows=17 loops=1)
-> Covering index lookup on City using fk_city_pid (pid=(select #2)) (cost=2.03 rows=17) (actual time=0.0128..0.0161 rows=17 loops=1)
-> Select #2 (subquery in condition; run only once)
-> Filter: (Provincial.Provincial = '安徽省') (cost=3.65 rows=3.4) (actual time=0.0498..0.0565 rows=1 loops=1)
-> Table scan on Provincial (cost=3.65 rows=34) (actual time=0.0423..0.0495 rows=34 loops=1)
|
+---------------------------+
1 row in set (0.00 sec)
ERROR:
No query specified
3.4操作系统层监控
从操作系统层也可以监控SQL语句的执行性能，MySQL需要消耗操作系统的4种资源：CPU、磁盘、内存和网络，在Linux系统上可以采用监控工具包括：top、free、vmstat、iostat、mpstat、sar 和netstat等。
