上一篇介绍了4种进行MySQL性能优化排查的小技巧,本篇就通过从增、删、改、查的语法中如何进行优化,帮助大家更好理解MySQL语法,进行性能优化。
学习完如何使用调优工具定位需要优化的SQL后,下面就来认识SQL的增、删、查、改进行优化技巧吧。
插入数据的优化点:主要在于最大程度上利用每一次数据库连接,避免频繁创建数据连接,因此,常见的优化方式如下:
load命令的使用:
数据组织方式:
在MySQL的InnoDB引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(Index Organized Table IOT),相关概念如下:
页(Page): 存放的就是具体的行数据
特点: 页可以为空、也可以填充一半,或者填充100%。每个页包含了2-N行数据(如果一行数据太大,会行溢出),页中数据根据主键排序【InnoDB中规则每个页中至少大于2行,如果只有一行,证明形成了链表,在innodb中是允许的】。页与也之间存在指针相互指向。
页分裂:
如果插入数据是数据的主键时乱序插入,因为InnoDB中数据是按照主键的顺序存放在页中的,它会找到本应该插入的数据页50%的位置(该数据页因为乱序插入已经满了),然后将之后的元素以及新插入的元素放到新申请的页中。然后指针重新指向的现象。
页合并:
注意: 在InnoDB中,当删除一个记录时,实际上记录并没有被物理删除,知识记录被标记(flaged)为删除,并且它的空间变得允许被其他记录声明使用。
定义: 当页中数据被删除到MERGE_THRESHOLD(默认是页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
MERGE_THRESHOLD参数在创建表或者索引时可以进行指定,默认就是页的一半。
主键设计原则:
使用explain关键字查看SQL语句的执行计划,注意:出现Using index的前提时走了覆盖索引,多字段排序时,也遵循最左前缀法则。
1、Using filesort: 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓存区sort buffer中完成排序操作。所有不是通过索引直接返回排序结果的排序都叫Filesort排序。
2、Using index: 通过有序索引顺序扫描直接返回有序数据,这种情况称为using index,它不需要额外排序,操作效率高。
3、Backward index scan;Using index: 没有进行额外排序,但是进行了反向扫描索引。
4、Using index;Using filesort: 没有直接通过索引返回有序数据,需要走过sort buff进行排序,效率也是较低。
Using filesort优化方式:
分组操作中,主要是索引起了优化效果。使用explain关键字查看SQL语句的执行计划分组情况如下:
优化技巧: 通过索引来提高效率,注意是否满足最左前缀法则
现象: 在大数量时分页时,越往后的数据,需要耗时越大,效率越大
优化: 子查询(多表关联) + 覆盖索引
方式: 先查询到需要筛选数据的主键,然后再进行数据子查询或者表关联查询到需要的具体数据
这个话题已经是老生常谈了,但是总有人争论不休,其实,最优权威的是官方的说法,官方是推荐使用count(*)而不是其他,下面来认识各种count用法的一个区别。
MyISAM引擎会把一个表中的总行数存储到磁盘中,在执行count(*)不带where条件时,可以直接拿到该数据,效率很高。
InnoDB在count时,需要将数据一行行从引擎读取出来,然后累计计数(大数量的情况下是比较耗时的,主要是由存储引擎决定的)。
优化思路:借助内存数据库手动维护总条数,插入时加1,删除时减1等
count的用法:
推荐使用:count(*)
更新数据时where条件一定要使用索引字段,否则就会从行锁升级为表锁,并发情况下,性能降低。
跟插入语句类似,要利用批量删除的方式,最大程度减少数据库连接,事务提交的消耗。
道理千遍,不如实践。性能优化更多的是结合理论的基础上进行亲自实践,这样才能够在众多的方式中找到符合的方式,希望本篇文章能够给大家一些启发。
博主最近开源了一个名为“轮子之王”的项目,其中集成了开发中常用的各项功能(现已集成有:csv、excel、ftp、文件服务器等导入导出轮子),有兴趣的小伙伴可以通过下面的地址跳转看看,希望能够帮助大家提高开发效率。
Gitee地址: 轮子之王
Github地址: 轮子之王