Mysql进阶Sql优化总结

前言

作为一名高级开发程序员，在日常开发工作中我们会根据APM工具定位到性能慢的SQL，然后进行执行计划分析并作出调整，主要的思路是建立索引、使用到索引，避免扫描等，但是有时候忽略了一些基本语法和使用规范，比如以下列举的7种场景都是我们应该熟练掌握的，如下：

一、插入优化

mysql数据插入可以从以下4种方案进行优化：

• 使用批量插入数据
• 手动控制事务的提交，即：开始事务、批量插入、结束事务
• 主键顺序插入
• 大批量插入，使用load命令。例：load data local infile

二、主键优化

主键优化思路是基于熟悉mysql底层存储结构（B+数据、节点使用页存储，有序的数据结构）之后，我们按照以下思路进行优化：

• 主键长度尽量短 使用顺序插入
• 避免使用uuid，可以使用自增长组件，防止页分裂

三、order by 优化

排序优化我们主要通过执行计划中的扩展字段（最后一列）确认，如：

• using index:直接通过索引返回数据，性能高
• using filesort:需要返回的结果在排序缓冲区排序

我们要优化策略就是在排序的sql将 filesort优化为index ,可以针对排序的字段建立合适的索引

四、group by 优化

对经常使用到排序的字段建立组合索引，使之进行索引覆盖，在编写sql语法的时候一定要尊从最左匹配原则

五、limit优化

limit在大数据量查询时，越到后面的页数据查询越慢，由于msyql底层是将前面的所有的数据查询出，然后根据分页的大小截取，所以性能越来越慢。
可以使用 覆盖索引 + 子查询的方式进行优化，例如：

• 使用limit查询到对应的主键id (索引覆盖)
• 通过子查询 in id查询数据集合

六、count优化

有时我们的分页查询数据列表性能很高，但是统计总数慢，所以我们也会遇到优化count查询场景。
count优化在innoDb引擎下没有特别好的优化，只能通过附加表来记录表的总数，新增和删除的时候进行同步维护，非常繁琐；MyISAM引擎下则直接可以使用，其底层已经记录的行数信息。
另外count聚合函数使用性能可以参考如下：

• count(字段) < count(主键id) < count(1) 约等于 count(*)

原理是：count函数的参数会进行判断，如果不为null则计数，否则不计数。另外对应字段和主键id需要进行取值判断，常量不需要取值，而*号数据底层做了优化不取值直接计数。

七、update优化

更新操作应尽量使用主键、索引字段进行数据更新。
原理：使用主键、索引字段使用行锁保证数据并发安全，如果没有使用则升级表锁进行控制，从而降低数据库更新的性能。

总结

熟练掌握mysql innoDB存储引擎的索引特性，灵活应用索引机制结合业务本身对sql进行合适的优化可以对性能带来飞跃的提升，同时也可以有效的提高工作效率。