MySQL进阶(6)—— 优化SQL步骤

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` 
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值