关于sql调优的浅显了解

sql状态及慢sql定位

想进行调优我们首先肯定是需要找到需要优化的sql，不然就像没头苍蝇一样没法进行下一步了

这里介绍几种可以查看sql状态帮助定位的一些方法

通过show status命令

shou status命令可以查看当前数据库的状态信息，会显示出很多当前数据库的状态信息，对于sql状态我们主要关注一下两种前缀的参数

1. Com_xxx表示的是每个 xxx 语句执行的次数，我们通常关心的是 select 、insert 、update、delete 语句的执行次数
   • Com_select：执行 select 操作的次数，一次查询会使结果 + 1
   • Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次
   • Com_update：执行 UPDATE 操作的次数
   • Com_delete：执行 DELETE 操作的次数
2. 以 Innodb_ rows_xxx表示的语句操作执行影响的数据库行数
   • Innodb_rows_read：执行 select 查询返回的行数。
   • Innodb_rows_inserted：执行 INSERT 操作插入的行数。
   • Innodb_rows_updated：执行 UPDATE 操作更新的行数。
   • Innodb_rows_deleted：执行 DELETE 操作删除的行数。

通过这些参数我们可以了解到当前的数据库是以查询为主还是以更新为主，以此来决定一些架构上的优化

定位慢sql

学习到的有两种方法

1. 通过慢查询日志来定位慢sql
2. 使用show processlist命令查看线程状态信息定位慢sql

慢查询日志

mysql提供了一个慢查询的日志记录功能可以把查询 sql语句时间大于多少秒的语句写入慢查询日志，日常维护中可以通过慢查询日志的记录信息快速准确地判断问题所在。用 --log-slow-queries 选项启动时，mysql 会写一个包含所有执行时间超过 long_query_time 秒的 sql语句的日志文件，通过查看这个日志文件定位效率较低的 sql。

比如我们可以在 my.cnf 中添加如下代码，然后退出重启 MySQL。

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

通常我们设置最长的查询时间是 2 秒，表示查询时间超过 2 秒就记录了，通常情况下 2 秒就够了，然而对于很多 WEB 应用来说，2 秒时间还是比较长的。

但是慢查询日志只有在查询结束后才会记录，如果一个慢查询长时间未完成我们是无法定位到它的，所以我们需要第二种定位方式

使用show processlist命令

show processlist 命令可以查看当前 Mysql 正在进行的线程，包括线程的状态、是否锁表等，可以实时的查看 sql 执行情况

下面就来解释一下各个字段对应的概念

• Id ：Id 就是一个标示，在我们使用 kill 命令杀死进程的时候很有用，比如 kill 进程号。
• User：显示当前的用户，如果不是 root，这个命令就只显示你权限范围内的 sql语句。
• Host：显示 IP ，用于追踪问题
• Db：显示这个进程目前连接的是哪个数据库，为 null 是还没有 select 数据库。
• Command：显示当前连接锁执行的命令，一般有三种：查询 query，休眠 sleep，连接 connect。
• Time：这个状态持续的时间，单位是秒
• State：显示当前 sql语句的状态，非常重要，下面会具体解释。
• Info：显示这个 sql语句。

State 列非常重要，关于这个列的内容比较多，读者可以参考一下这篇文章

https://blog.youkuaiyun.com/weixin_34357436/article/details/91768402

这里面涉及线程的状态、是否锁表等选项，可以实时的查看 SQL 的执行情况，同时对一些锁表进行优化。

慢sql分析

通过上述步骤找到慢sql以后，我们可以通过explain（执行计划）来查看sql语句的执行计划，比如

其中我们主要优化的注意点key属性上，其代表此sql语句所使用的索引是什么

我们需要了解查询的表是否设计了索引，索引设计的是否合理，sql语句书写的是否合理，是否由于不规范sql导致了索引失效从而导致sql语句执行缓慢

那么都有什么因素会导致索引失效呢？

1. 不合规使用联合索引

2. 使用like查询时将%放在第一个字符的位置

   //不合法用法
   select * from t_person where id like '%1';
   //合法用法
   select * from t_person where id like '1%';
   

3. 索引列参与了计算

   select * from t_person where id + '1' = '2';
   //但是从右侧计算是可以的，但是为啥要这样写呢
   select * from t_person where id  = '2' - '1';
   

4. 对索引列使用了函数

   select * from t_person where concat(id,'1')  = 1;
   

5. 在索引列上使用 IS NOT NULL

   select * from t_person where id IS NOT NULL;
   

6. 在索引列上使用除=以外的符号如>，<，!=

   select * from t_person where id > 0;