大数据闯关之MySQL进阶篇（三）：SQL优化-灵析社区

写在前面

大家好，这里是立志于在有生之年看到并参与通用人工智能开发工作的Nobody，由于最近在公司要经常性地接触大数据工具，所以打算开一个大专栏对大数据工具进行学习总结整理。这部分接着之前的MySQL基础篇，接下来将对MySQL进阶部分进行学习。

以下为该部分的前置博客

大数据闯关之MySQL进阶篇（一）：存储引擎

大数据闯关之MySQL进阶篇（二）：索引

一、SQL优化

插入数据（insert）优化

批量插入，避免每一次插入数据与数据库进行连接

insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'jerry');

手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'jerry');
commit;

主键顺序插入
大批量插入数据：如果需要一次性插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入

主键优化

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表

页分裂：页可以为空，也可以填充一半。每个页包含了2-N行数据，根据主键排列。当主键乱序插入时，一页可能按照原本顺序无法插入数据，因此可能会产生页分裂的情况来按照主键顺序排列
页合并：当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。一旦被标记删除后，当页中删除的记录达到MERGE_THRESHOLD，InnoDB会开始寻找最靠近的页，看看是否可以将两个页合并以优化空间使用。
主键设计原则

在满足业务需求的情况下，尽量降低主键的长度。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
尽量不要使用UUID做主键或者是其他自然主键，如身份证号
业务操作时，避免对主键的修改

order by优化

两种形式

Using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序
Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需注意联合索引在创建时的规则
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小

group by优化

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

limit优化

覆盖索引+子查询：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化

阅读量：2012

点赞量：0

收藏量:0