)
文章目录索引失效及优化模糊查询失效or连接失效违反最左前缀失效联合索引范围查询后的索引失效索引参与运算(包括函数)索引失效字符串不加单引号索引失效辨识度过低不走索引索引尽量使用覆盖查询查看索引使用情况导入文件优化SQL语句优化order by优化:FileSort优化group by优化针对老版本子查询优化or优化limit优化索引提示索引失效及优化总结索引失效及优化以下提到的索引失效不是绝对的严谨点来说应该是可能导致无法进行高效索引范围定位或使优化器最终选择全表扫描。模糊查询失效or连接失效上图OR 两边的列各自有索引优化器使用 Index Merge 来合并两个索引的扫描结果。违反最左前缀失效联合索引范围查询后的索引失效索引参与运算(包括函数)索引失效注意MySQL 8.0.13 中可以创建函数索引来专门优化这类查询字符串不加单引号索引失效辨识度过低不走索引索引尽量使用覆盖查询查看索引使用情况导入文件优化SQL语句优化INSERT优化原始方式insertintotable_namevalues(a,b);insertintotable_namevalues(c,d);优化方式insertintotable_namevalues(a,b),(c,d);插入时手动开启事务并按顺序插入。order by优化:FileSort优化早期 MySQL 的 Filesort 主要有两种实现思路两次扫描算法MySQL 4.1 之前主要采用这种方式。先读取排序字段和能够定位原数据行的信息进行排序排序完成后再根据行位置读取查询所需的其他字段因此可能需要两次访问数据内存占用较小但随机 I/O 较多。一次扫描算法MySQL 4.1 开始引入改进后的排序方式将排序字段和查询所需的其他字段一次性读入 Sort Buffer排序完成后可以直接返回结果减少再次读取数据的开销但会占用更多排序内存。旧版本 MySQL 中max_length_for_sort_data曾用于影响 Filesort 对这两种方式的选择但从MySQL 8.0.20开始该参数已经被废弃并且不再产生作用因此在 MySQL 8 中不应再通过调大max_length_for_sort_data来优化排序。在现代 MySQL 8 中优化 Filesort 更应该优先考虑尽量利用索引完成ORDER BY避免额外排序减少不必要的查询字段和需要参与排序的数据量根据实际排序情况合理设置sort_buffer_size而不是简单地全局调大group by优化针对老版本在MySQL 8.0之前GROUP BY 默认会按分组字段排序,MySQL 8.0开始已经取消 GROUP BY 的隐式排序。同样也可以用索引来提高效率。下图mysql5版本下图mysql5版本子查询优化or优化limit优化索引提示索引失效及优化总结1.or有一侧非索引会失效可用Union代替。2.模糊查询%号前置不走索引可以覆盖解决。3.违反最左前缀失效4.索引字段参与运算失效5.字符不加单引号会失效6.IN(not in)、is(is not)匹配辨识度低的值会失效可以用强制索引解决。7.一个以上的二级索引参与分组索引失效。
