前言
作为一名高级开发程序员,在日常开发工作中我们会根据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进行合适的优化可以对性能带来飞跃的提升,同时也可以有效的提高工作效率。