MySql中查询语句优化的简单思路

前言

要优化SQL，首先要找出那些执行速度慢的Sql语句。通过开启慢查询，可以实现。我们的CPU资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开SlowQueryLog功能来定位可能存在的慢查询。

1. 开启慢查询：set global slow_query_log = on
2. 指定慢查询日志目录：
   set global slow_query_log_file=’/var/lib/mysql/slow.log’
3. 指定慢查询时间阈值：set global long_query_time = 0.3
4. 分析工具：mysqldumpslow -s -r -t 10 slow-mysql.log
5. 查看慢查询日志相关参数：show variables like ‘slow_query%’;

可以用explain查询执行计划。
可以使用explain analyze(MySql8.0以后的版本)，显示更具体的执行计划，成本信息，和实际耗时
https://www.e-learn.cn/topic/3713944

查询SQL优化的几点考虑

• 首先，考虑是否选择了合适的存储引擎，
• 考虑是否可以为该表建立索引。
• 建立索引，优先考虑覆盖索引，即查询的字段也在索引中，覆盖索引可以减少回表的次数。
• 如果需要联合索引，要遵循最左前缀匹配原则，把最常访问的列放在最左边。
• 如果已经建立了索引，用解释命令查看执行计划，看看索引是否失效
• 建议SQL关键字大写，可以适当提高效率。Linux环境下默认是（数据库名，表名）大小写敏感的（可以设置关闭）。Windows环境大小写不敏感。
• 数据适当的冗余，减少多表关联

查询索引失效的情况

以下通过explain显示出mysql执行的字段内容：

• id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
• select_type: SELECT 查询的类型.
• table: 查询的是哪个表
• partitions: 匹配的分区
• type: join 类型
• possible_keys: 此次查询中可能选用的索引
• key: 此次查询中确切使用到的索引.
• ref: 哪个字段或常数与 key 一起被使用
• rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
• filtered: 表示此查询条件所过滤的数据的百分比
• extra: 额外的信息

索引查询失效的几个情况：

• like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
• or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
• 组合索引，不使用第一列，索引失效
• 数据类型出现隐式转换，比如varchar不加单引号会自动转化成int，导致索引失效
• 在索引列上使用is null或is not null，可能导致索引失效
• 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
• 对索引字段进行计算操作，使用函数
• 当优化器分析得出全表扫描速度比索引快时，mysql会使用全表扫描