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 存储引擎的,累加的算法也略有不同。
2、定位低效率执行SQL
可以通过以下两种方式定位执行效率低的 SQL 语句。
- 慢查询日志:通过满查询日志定位那些效率较低的SQl语句,用
--log-slow-queries[=file_name]
选项启动时,mysqld写一个包含所有执行时间超过 long_query_time 秒的 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语句,是判断问题语句的一个重要依据
3、explain分析执行计划
通过以上步骤查询到效率低的 SQL 语句后,可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
查询SQL语句的执行计划 :
explain select * from tb_item where id = 1;
explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3';
字段 | 含义 |
---|---|
id | select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。 |
select_type | 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)等 |
table | 输出结果集的表 |
type | 表示表的连接类型,性能由好到差的连接类型为( system —> const -----> eq_ref ------> ref -------> ref_or_null----> index_merge —> index_subquery -----> range -----> index ------> all ) |
possible_keys | 表示查询时,可能使用的索引 |
key | 表示实际使用的索引 |
key_len | 索引字段的长度 |
rows | 扫描行的数量 |
extra | 执行情况的说明和描述 |
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`