一、定位效率低的SQL

我们对数据库的所有操作都会转换为一条sql的执行。所以本篇我们主要记录如何定位到慢Sql语句以及分析慢Sql语句的原因。

1.查询数据库的基本状态

介绍一些命令,辅助我们更好的分析问题

1.1查看mysql服务运行时长

命令:

show status like 'uptime';

结果:

1.2CRUD执行次数

命令:

show status like '%Com_%';

结果:

1.2.1查看执行查询的次数

命令:

show status like '%Com_select%';

结果:

1.2.2查看执行新增的次数

命令:

show status like '%Com_insert%';

结果:

1.2.3查看执行修改的次数

命令:

show status like '%Com_update%';

结果:

1.2.4查看执行删除的次数

命令:

show status like '%Com_delete%';

结果:

注意：[session|global]默认是session会话
      session:  值取出当前窗口的执行
      global:  从mysql启动到现在

# 查看当前窗口的执行查询的次数
show session status like '%Com_select%';
# 查看mysql启动到现在的执行查询的次数
show global status like '%Com_select%';

1.2.5帮助我们分析系统CRUD的比例

命令:

show status like '%Innodb_rows%';

结果:

1.3查询所有链接数

命令:

show status like 'connections';

结果:

1.4查看服务器响应的最大连接数

命令:

# 通过查询(工作环境)最大并发连接数可以作为我们配置mysql最大连接数的依据,通常mysql的最大连接数默认是100,最大可以达到16384(理论上)
show status like 'Max_used_connections';

结果:

2.定位慢查询

2.1查询慢查询次数

命令:

# 慢查询,通常指花了2s以上的查询(默认10s)
show global status like 'slow_queries';

结果:

上面可以看到我有一条慢查询记录了

2.2查看和修改慢查询时间阀值

命令:

# 可以显示当前慢查询时间
show variables like 'long_query_time';
# 可以修改慢查询时间
set global long_query_time=1;

结果:

注意:  直接修改global的long_query_time之后在当前的窗口中是没有效果的,在新打开窗口中才会有效果.如果想让本窗口也有效果的话,不用加global关键字

2.3开启慢查询并输出日志

我的mysql是5.7版本的,如果是其他版本可能不一样,这个只有百度去查了.

• 在mysql配置文件中加上如下配置:

[mysqld]
# 这个是开启慢查询,注意ON需要大写
slow_query_log=ON
# 这个是存储慢查询的日志文件.这个文件不存在的话,需要自己创建
slow_query_log_file=D:\\server2\\mysql5.7.23\\slow_query.log
# 这个是设置慢查询的时间,我设置的为1秒
long_query_time=1

• 重启Mysql服务

# 方式一:cmd命令行中输入命令
service mysql restart
# 方式二:在任务管理器中找到对应的服务,然后点击重新启动就好

• 执行慢sql语句

# 这里我的emp数据有500w条,所以查询速度很慢,已经超过了设定的1s了
# (表这些你们随便找个数据量大的表,然后进行测试就好了,只要超过1s就行)
select * from emp;

• 查询慢查询次数是否新增

show global status like 'slow_queries';

我们可以看到这里执行的慢查询次数新增了1次,说明刚刚执行的语句就是慢Sql语句

• 查看咱们配置的日志文件
  文件地址: 就是我们上面在mysqld中写的slow_query_log_file地址"D:\server2\mysql5.7.23\slow_query.log"
  文件内容如下:
  

2.4小结

咱们开启慢查询的步骤:
  1.  修改mysql配置文件
  2.  重启mysql服务
  3.  构造慢sql并执行
  4.  到日志文件中找慢sql（slow_query.log）

注意:我们在sql中直接执行命令是看不到具体那条sql语句是慢sql,所以我们才去配置输入日志,将sql语句详情输入到日志中,但是线上我们一般不这样弄,只是在性能测试阶段,我们需要定位慢sql所以需要开启

那么什么时候开启慢查询呢?
系统中所有sql都执行一遍，才能判断是否有慢sql。什么时候开启能覆盖所有sql执行？
开发者自验：
  开发完成后，需要统一打包，统一部署，统一验证。
测试人员测试：
  测试人员需要测试所有功能。
项目上线：
  开一段时间,把它关了.或者不开用户用了所有功能。

3.分析慢Sql原因-explain sql语句

explain select * from emp;

通过explain我们能获取到很多信息,接下来根据各字段进行分析:
table: 显示这一行的数据是关于哪张表的
type: 这是重要的列,显示连接使用了何种类型.从最好到最差的连接类型为const、eq_reg、ref、range、index和All.
possible_keys: 显示可能应用在这种表中的索引.如果为空,没有可能的索引.可以为相关的域从WHERE语句中选择一个合适的语句.
key: 实际使用的索引.如果为NULL,这么有使用索引.很少的情况下,MySQL会选择优化不足的索引.这种情况下,可以在SELECT语句红使用USEINDEX(indexname)来强制使用一个索引或者用IGNOREINDEX(indexname)来强制MySQL忽略索引.
key_len: 使用索引的长度.在不损失精确性的情况下,长度越短越好.
ref: 显示索引的那一列被使用了,如果可能的话,是一个常数
rows: MySQL认为必须检查的用来返回请求数据的行数

4.总结

本章主要是找出慢SQL并分析原因，具体优化后面陆续更新在此栏目中，大家可以关注栏目，方便更新后可以查看不迷路。
这只是我的学习笔记哈，有不好的地方大家可以指出，共同学习。