系统设置慢查询日志

• slow_query_log：是否开启慢查询日志，ON表示开启，OFF表示关闭。

• slow_query_log_file：慢查询日志的存储路径。

• slow_launch_time：设置慢查询的时间阈值，单位为秒。赛跑时间超过阈值的“蜗牛选手”会记录到慢查询日志中。

  
  

EXPLAIN执行计划

explain语法：explain+“需分析的sql”

例：explain select * from table1 where b=500;

• id：查询编号，每个查询都有一个唯一的id。如果有多个查询（比如子查询或联合查询），这些id会按照执行的顺序排列，帮助你理解查询的层次结构。

  
  

• select_type：查询类型，显示本行是简单还是复杂查询。比如，SIMPLE 是简单查询即不适用关联查询或子查询，PRIMARY是复杂查询中的最外层查询，SUBQUERY 则是子查询中的第一个查询，union 联合查询中第二个及后面的查询。

  
  

• table：查询涉及的表名。

  
  

• type：本次查询的表连接类型，它揭示了 MySQL 是如何访问数据的。从最优到最差的访问类型依次为：system > const > eq_ref > ref > fulltext > ref_or_null > range > index > ALL。

• system 查询对象表只有一行数据

• const 基于主键或唯一索引查询，最多返回一条结果

• eq_ref 表连接时基于主键或非NULL的唯一索引完成扫描

• ref 基于普通索引的等值查询，或者表间等值查询

• fulltext 全文检索

• range 利用索引进行范围查询

• index 全索引扫描

• ALL 全表扫描
  

  
  

• possible_keys和 key：possible_keys 显示了查询可能使用的索引，而 key 则显示了实际使用的索引。

• rows：预估找到查询目标需要读取的行数。

• Extra：包含了查询执行计划的额外信息，比如是否使用了索引覆盖扫描、是否进行了文件排序等。

  
  

SQL语句中有索引，效率就一定能提高吗？

有索引的 MySQL 语句效率不一定能提升。虽然索引是提高 MySQL 查询效率的重要手段，但索引的使用并非绝对，其效果受到多种因素影响。

• 索引的选择性：索引的选择性是指索引列中不同值的数量与表中记录总数的比率。选择性高的索引可以更快速地定位到满足查询条件的记录。

• 索引的列顺序：对于多列索引，列的顺序可能会影响查询性能。在创建多列索引时，应该考虑查询中经常使用的列顺序，并将它们放在索引的前面。

• 查询条件：复杂的查询条件可能会降低索引的效果。如使用 LIKE操作符进行模糊查询的时候，如果匹配字符串的第一个字符是通配符（如%），索引可能无法被有效使用。

• 写操作的开销：当数据发生变更（如INSERT、UPDATE、DELETE时），索引也需要进行相应的维护操作，可能会增加写操作开销。

• 存储开销：索引需要占用额外的磁盘空间来存储，这可能会增加数据库的存储成本，所以不适宜创建过多的索引。

SQL语句没有问题，为何查询效率依旧很慢？

• 锁竞争：当多个事务试图同时访问或修改同一资源时，会发生锁竞争。这可能导致事务等待其他事务释放锁，从而降低整体性能。

• 死锁：当两个或多个事务相互等待对方释放锁时，会发生死锁。这通常会导致系统性能急剧下降，甚至完全停止。

当 SQL 查询速度变慢时，应该考虑锁作为可能的原因之一。合理运用锁的类型，通过监控和日志跟踪锁的持有和释放情况，可以有效解决锁导致的性能问题。

什么时候索引失效？

• 对索引使用左或者左右模糊匹配（因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。）

  
  

• 对索引使用函数（因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。）

  
  

• 对索引进行表达式计算（因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值。如：select * from table1 where b-1 =1000;
  应改为：select * from table1 where b =1000 + 1;

• 对索引隐式类型转换，如把varchar类型当成int型去写

  
  

• 联合索引非最左匹配

  
  

• WHERE 子句中的 OR（WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。在多列OR中，可以考虑用UNION 替换）

• 范围查询 如：select * from table1 where b>=1 and b <=2000; （这条数据查询范围过大，是全表扫描，优化器选择不走索引）

• 分页优化 如：select * from table1 order by a limit 99000,10; （其中，a字段有索引）
  但结果不走索引，原因是扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。（关键是让排序时返回的字段尽可能少）
  应改为：select * from table1 f inner join (select id from table1 order by a limit 99000,10)g on f.id = g.id;
  或者 select * from table1 where id >= (select id from table1 order by a limit 99000,1) limit 10;

• 去掉不必要的查询返回字段。如：select * from table1 order by a,b; /* 根据a和b字段排序查出所有字段的值 */
  应改为：select id,a,b from table1 order by a,b; /* 根据a和b字段排序查出id,a,b字段的值 */，不走索引原因：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。

  
  

• 在排序字段上添加索引
  如：select c,id from table1 order by c; （c有索引）

• 多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句
  如：select id,a,b from table1 order by a,b; （a,b是联合索引）
  注意：select id,a,b from table1 order by b,a; （a,b是联合索引）此时不走索引，最左匹配前缀原则了解下

• 对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化
  如：select id,a,b from table1 where a=1000 order by b; （a,b是联合索引）
  如果a,b不是联合索引的话，即时有a索引，b索引，排序也不会走索引