MySQL的慢sql

1. 什么是慢sql

   1. 每执行一次sql，数据库除了会返回执行结果以外，还会返回sql执行耗时，以mysql数据库为例，当我们开启了慢sql监控开关后，默认配置下，当sql的执行时间大于10s，会被记录到慢sql的日志文件中
   2. 这个值可以重新设置，生产环境慢sql一般设置为0.1-0.2s，当我们将其设置为0.2s时。当前数据库所有sql的执行时长超过0.2s的都会被视为慢sql

2. 慢sql的危害

   1. 当出现慢查询时，DDL操作都会被阻塞，也就是说创建表，修改表，删除表，执行数据备份等操作都需要等待，这对实时备份重要数据的系统来说是不可容忍的
   2. 慢查询可能会占用mysql大量的内存，严重的时候会导致应用的进程因超时被kill，无法返回结果给到客户端
   3. 造成数据库幻读，不可重复读的概率更大，假设该慢sql是一个更新操作，但因执行时间过长未提交，而另一条sql也在更新数据并且已经提交，当用户再次查询的时候，看到的数据可能与实际结果不符
   4. 严重影响用户体验：sql的执行时间越长，页面加载数据耗时也就越长

3. 如何定位慢sql

   1. 开启慢sql监控

      1. show variables like ‘slow_query_log%’; 查询是否开启慢sql监控

         1. show_query_log OFF 默认是关闭状态
         2. slow_query_log_file /var/lib/mysql/ecs-203056-slow.log 慢sql的日志存储文件

      2. set global slow_query_log = 1 ;

         1. 开启慢sql查询，执行成功后，客户端需要重新连接才能生效
         2. 如果想关闭慢sql监控，将其配置为0就可以了
         3. 当服务器重启之后，当前配置会失效

      3. 配置慢sql阈值

         1. 默认的慢sql的阈值是10s，
            1. show variable like 'long_query_time; 查询慢sql阈值
            2. long_query_time 10 默认是10s
         2. set global long_query_time = 0.2;
            1. 将慢sql阈值配置为0.2秒’
            2. 然后退出客户端，重新连接服务器，就生效了
            3. 当服务器重启之后，当前配置会失效

      4. 永久开启慢sql监控

         1. 以上的操作，当服务器不重启会一直有效，但是当服务器一旦重启之后，配置就会失效，如果想要永久生效，可以通过修改全局配置文件my.cnf使之永久生效

         2. 打开my.cnf配置文件，添加如下配置变量

            1. [mysqld]
               slow_query_log = ON
               slow_query_log_file = /var/lib/mysql/ecs-203056-slow.log
               long_query_time = 1
               

            2. 重启mysql服务器

            3. systemctl restart mysqld
               

      5. 慢sql监控

         1. 慢SQL日志内容详解

            1. Time:表示客户端查询时间
            2. root[root]表示客户端查询用户和IP
            3. Query_time:表示查询耗时
            4. Lock_time:表示等待table lock的时间，注意innodb的行锁等待是不会反应在这里的
            5. Rows_sent:表示返回了多少行记录(结果集)
            6. Rows_examined:表示检查了多少条记录

         2. 除此之外，我们还可以借助mysqldumpslow命令工具，分析慢sql的数据情况，可以通过如下参数进行组合分析

            1. -s         表示按何种方式排序，支持的参数如下
                           al: 平均锁定时间
                           ar: 平均返回记录数
                           at: 平均查询时间
                           c: 访问次数
                           l: 锁定时间
                           r: 返回记录
                           t: 查询时间
               -t NUM       返回前面多少条的数据
               -g PATTERN   后边搭配一个正则匹配模式，大小写不敏感
               

         3. 常见用法如下

            1. 查询返回记录集最多的10个sql

               1. mysqldumpslow -s r -t 10 /var/lib/mysql/ecs-203056-slow.log
                  

            2. 查询访问次数最多的10个sql

               1. mysqldumpslow -s c -t 10 /var/lib/mysql/ecs-203056-slow.log
                  

            3. 查询按照时间排序的前10条里面含有做链接的查询语句

               1. mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/ecs-203056-slow.log
                  

      6. 慢sql是怎么发生的

         1. 

         2. 一条sql语句执行时，总结起来大概分为以下几个步骤

            1. 若查询缓存打开则会优先查询缓存，若命中则直接返回结果给客户端
            2. 若缓存未命中，此时mysql需要搞清楚这条语句需要做什么，则通过分析器进行词法分析，语法分析
            3. 搞清楚要做什么之后，mysql会通过优化器对sql进行优化，生成一个最优的执行计划
            4. 最后通过执行器与存储引擎提供的接口进行交互，将结果返回给客户端

         3. 在mysql执行过程中，优化器可能会对我们即将要执行的sql进行改造，改造思路如下

            1. 根据搜索条件，找出sql中所有可能使用的索引
            2. 然后计算全表扫描的成本开销
            3. 接着计算使用不同索引执行查询的成本开销
            4. 最后会对比各种执行方案的成本开销，找出开销值最小的那一个

         4. 影响成本开销值的计算，主要是IO成本和CPU成本这两个指标

         5. 从IO视角看

            1. 当表的数据量越大，需要的IO次数也就越多
            2. 从磁盘读取数据比缓存读取数据，IO消耗的时间更多
            3. 全表扫描比通过索引快速查找，IO消耗的时间和次数更多

         6. 从CPU视角看

            1. 当sql中有排序，子查询等复杂的操作时，CPU需要先把数据存到临时表中，在对数据进行加工，需要的CPU资源更多
            2. 全表扫描相比于通过索引快速查找，需要的CPU资源也更多

         7. 在没有开启缓存的情况下，当表的数据量越大，如果sql又没有走索引，很容易发生查询慢的问题