如何定位不合理的 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_select | 执行 select 操作的次数，一次查问只累加 1。 |
| Com_insert | 执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。 |
| Com_update | 执行 UPDATE 操作的次数。 |
| Com_delete | 执行 DELETE 操作的次数。 |
| Innodb_rows_read | select 查问返回的行数。 |
| Innodb_rows_inserted | 执行 INSERT 操作插入的行数。 |
| Innodb_rows_updated | 执行 UPDATE 操作更新的行数。 |
| Innodb_rows_deleted | 执行 DELETE 操作删除的行数。 |
| Connections | 试图连贯 MySQL 服务器的次数。 |
| Uptime | 服务器工作工夫。 |
| Slow_queries | 慢查问的次数。 |
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 = '联想北达兴科专卖店';| 字段 | 含意 |
|---|---|
| id | select 查问的序列号，是一组数字，示意的是查问中执行 select 子句或者是操作表的程序。 |
| select_type | 示意 SELECT 的类型，常见的取值有 SIMPLE（简略表，即不应用表连贯或者子查问）、PRIMARY（主查问，即外层的查问）、UNION（UNION 中的第二个或者前面的查问语句）、SUBQUERY（子查问中的第一个 SELECT）等 |
| table | 输入后果集的表 |
| partitions | 匹配的分区 |
| type | 示意表的连贯类型，性能由好到差的连贯类型为 (system —> const —–> eq_ref ——> ref ——-> ref_or_null—-> index_merge —> index_subquery —–> range —–> index ——> all) |
| possible_keys | 示意查问时，可能应用的索引 |
| key | 示意理论应用的索引 |
| key_len | 索引字段的长度 |
| rows | 扫描行的数量 |
| filtered | 按表条件过滤的行百分比 |
| extra | 执行状况的阐明和形容 |
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'));| select_type | 含意 |
|---|---|
| SIMPLE | 简略的 select 查问，查问中不蕴含子查问或者 UNION |
| PRIMARY | 查问中若蕴含任何简单的子查问，最外层查问标记为该标识 |
| SUBQUERY | 在 SELECT 或 WHERE 列表中蕴含了子查问 |
| DERIVED | 在 FROM 列表中蕴含的子查问，被标记为 DERIVED（衍生）MYSQL 会递归执行这些子查问，把后果放在长期表中 |
| UNION | 若第二个 SELECT 呈现在 UNION 之后，则标记为 UNION；若 UNION 蕴含在 FROM 子句的子查问中，外层 SELECT 将被标记为：DERIVED |
| UNION RESULT | 从 UNION 表获取后果的 SELECT |
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 显示的是拜访类型，是较为重要的一个指标，可取值为：
| type | 含意 |
|---|---|
| NULL | MySQL 不拜访任何表，索引，间接返回后果 |
| system | 表只有一行记录 (等于零碎表)，这是 const 类型的特例，个别不会呈现 |
| const | 示意通过索引一次就找到了，const 用于比拟 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于 where 列表中，MySQL 就能将该查问转换为一个常量。const 会将 “ 主键 ” 或 “ 惟一 ” 索引的所有局部与常量值进行比拟 |
| eq_ref | 相似 ref，区别在于应用的是惟一索引，应用主键的关联查问，关联查问出的记录只有一条。常见于主键或惟一索引扫描 |
| ref | 非唯一性索引扫描，返回匹配某个独自值的所有行。实质上也是一种索引拜访，返回所有匹配某个独自值的所有行（多个） |
| range | 只检索给定返回的行，应用一个索引来抉择行。where 之后呈现 between，< , > , in 等操作。 |
| index | index 与 ALL 的区别为 index 类型只是遍历了索引树，通常比 ALL 快，ALL 是遍历数据文件。 |
| all | 将遍历全表以找到匹配的行 |
后果值从最好到最坏顺次是：
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;| extra | 含意 |
|---|---|
| using filesort | 阐明 mysql 会对数据应用一个内部的索引排序，而不是依照表内的索引程序进行读取，称为“文件排序”, 效率低。 |
| using temporary | 应用了长期表保留两头后果，MySQL 在对查问后果排序时应用长期表。常见于 order by 和 group by；效率低 |
| using index | 示意相应的 select 操作应用了笼罩索引，防止拜访表的数据行，效率不错。 |
本文由传智教育博学谷 – 狂野架构师教研团队公布，转载请注明出处！
如果本文对您有帮忙，欢送关注和点赞；如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源