MySQL查询优化

慢sql优化
分页查询优化

• 查看慢SQL是否开启
  • show variables like ‘%slow_query_log%’;
• 如value值为OFF，开启慢SQL日志
  • set global ‘slow_query_log’ = on;
• 查看慢SQL判定时长，默认10秒
  • show variables like ‘%long_query_time%’;
• 修改慢SQL判定时长，单位秒
  • set long_query_time = 0.1;
• 查看慢SQL日志
  • Time：日志记录的时间
  • User@Host：执行sql的用户及主机
  • Query_time：执行时间
  • Lock_time：锁表时间
  • Rows_sent：发送给请求方的记录数->结果数量
  • Rows_examined：语句扫描的记录条数
  • SET timestamp：语句执行的时间点
  • select…：执行的sql语句

• 使用EXPLAIN查看是否使用了索引，并查看扫描条数。
  • explain select * from user where name = ‘关羽’;
• 使用索引时，只有查询的条数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑过滤性，过滤性好，执行效率才会快。
• 提高索引过滤性
  • 索引过滤性与索引字段、表的数据量、表设计结构都有关系。
  • 添加虚拟列，如：
    • alter table student add 虚拟列名 varchar(3) generated always as (left(列名,3)),add index(虚拟列名，字段名)
      • left(列名,3)取该列的前3个字符做为虚拟列的值
• 慢查询原因：
  • 全表扫描：explain的type属性为all
  • 全索引扫描：explain的type属性为index
  • 索引过滤性差：比如总量10W，过滤完后还剩9.5W条->注意索引字段选型、数据量和状态、虚拟列
  • 频繁的回表查询开销：不要使用select *，使用覆盖索引

• 首先开启show profiling，用于查看每条sql的执行时长。show profiling 默认是关闭的，需要手动开启。查看命令 show variables like ‘profiling’;
  开启命令 set profiling = 1;
  然后执行sql
  通过命令查看sql执行情况 show profiles;
  

  如图：

  • Query_ID 2~6： 当偏移量变化，返回值固定时，从1000开始，sql执行时长明显增加；
  • Query_ID 7~11：当偏移量规定，返回值变化是，从10000开始，sql执行时长明显增加。
    • 因为limit查询机制，每次都会从数据库的第一条记录开始扫描，越往后越慢，随着查询数据额增多，查询速度越来越慢
    • 优化方案一：
      • 利用覆盖索引优化
    • 优化方案二：
      • 利用子查询优化（先利用覆盖索引锁定偏移量的起点，然后再用limit返回所需要的条数）
        
        
        如上图：返回值均是 * 偏移量越大速度越慢，效率提升也就越显著。

其他章节 -> 跳转

end...