MySQL定位及优化慢查询SQL

• SHOW VARIABLES LIKE '%query%'查询慢日志相关信息
  
  • slow_query_log默认是off关闭的，使用时，需要改为on 打开
  • slow_query_log_file记录的是慢日志的记录文件
  • long_query_time默认是10S，每次执行的sql达到这个时长，就会被记录
  • SET GLOBAL slow_query_log = ON打开慢查询
  • SET GLOBAL long_query_time = 1将默认时间改为1S
  • SHOW STATUS LIKE '%slow_queries%'查看慢查询状态，Slow_queries 记录的是慢查询数量 当有一条sql执行一次比较慢时，这个vlue就是1（记录的是本次会话的慢sql条数）
  • 设置完需要重新连接数据库，PS：仅在这里改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改
  • my.ini中修改：
    • slow_query_log=on：开启慢sql
    • slow_query_log_file=C:\MySQL\log\logquery.log：设置慢sql日志位置
    • long_query_time=1：设置慢sql时间为1S
    • 
• 设置好后，在日志当中就可以看到慢sql了。
• 接着开始分析慢sql的原因，这个时候需要用到mysql的explain工具分析sql
  • 在要执行的sql前加上explain 例如：EXPLAIN select a.id from (select max(id) as id from users group by name) as a
    
  • 查看关键字type：一般情况效率对比system > const > eq_ref > ref > range > index > ALL，type只要不是index和all，sql性能就能不错。如果是index或all那么就需要进行优化了。
• 什么是最左前缀原则？什么是最左匹配原则？为什么需要注意联合索引中的顺序？
  • 最左前缀原则，就是最左边的优先。指的是联合索引中，优先走最左边列的索引。对于多个字段的联合索引，如 index(a,b,c) 联合索引，则相当于创建了 a 单列索引，(a,b)联合索引，和(a,b,c)联合索引（但并不是建立了多个索引树）。mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
  • =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
  • 如果建立的索引顺序是 (a,b) 那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。
• 常用的优化技巧
  • 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
  • 不要使用select * from t，用具体的字段列表代替“*”，使用星号会降低查询效率，如果数据库字段改变，可能出现不可预知隐患。
  • 应尽量避免在where子句中使用!=或<>操作符，避免在where子句中字段进行null值判断，存储引擎将放弃使用索引而进行全表扫描。
  • 避免使用左模糊，左模糊查询将导致全表扫描。
  • IN语句查询时包含的值不应过多，否则将导致全表扫描。
  • 为经常作为查询条件的字段，经常需要排序、分组操作的字段建立索引。
  • 在使用联合索引字段作为条件时，应遵循最左前缀原则。
  • OR前后两个条件都要有索引，整个SQL才会使用索引，只要有一个条件没索引整个SQL就不会使用索引。
  • 尽量用union all代替union，union需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。
  • 避免在 where 子句中对字段进行 null 值判断，对于null的判断会导致引擎放弃使用索引而进行全表扫描。
  • 索引字段类型为字符型,而查询字段为数值型,索引将会失效。
    参考链接：MySQL Explain详解