一篇文章带你学会SQL优化！

SQL优化的本质

        SQL优化用一句话进行概括就是：如何高效利用索引。没错就这么简单！后面论述的对update也好，order by也好基本都是想方设法利用索引提高效率

插入数据优化

        Insert优化

        如果我们需要一次性插入多条数据进入数据库表中，那么一次次插入语句就会多次开启提交事务，还会多次进行磁盘IO这些都是影响性能的，所以尽量使用批量插入的方式

        优化前的语句

insert into tb_test values(1,'tom');
insert into tb_test values(2,'cat');
insert into tb_test values(3,'jerry');
.....

        优化后的语句

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');

        还能进行优化的点就是手动去开启、提交事务这样也可以避免MySQL中事务的启动开启提交

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;

        由于索引底层采用的是升序存储的，所以主键顺序插入的性能会高于乱序出入的性能

主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3

主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89

        大批量插入数据优化

        如果一次性需要插入很多数据（百万级别的数据），那么insert的效率就不尽如人意了。所以可以使用load指令进行插入操作，主要插入的数据必须格式一致如下图所示

        

        要插入的数据中每一行都是表中的一条数据，并且使用统一的分隔符逗号进行分分隔

        可以使用下面的指令将数据脚本文件插入到数据库表中

-- 客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p

-- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;

-- 执行load指令将准备好的数据，加载到表结构中，指定分隔符是逗号，指定换行符是'\n'
load data local infile '/root/sql1.log' into table tb_user fields terminated by ',' lines terminated by '\n' ;

主键优化

        上面说到乱序插入的性能是不如顺序插入的，那么接下来就来看下具体的原因，并且还会提到主键设计的原则

        我们上篇文章说到了，索引的数据结构的B+Tree，所以数据都是顺序存放在叶子节点的，记录的每一行的数据，而在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储到下一个页中，页与页之间会通过指针连接。

        页分裂

        如果是顺序插入的话那么就不会有页分裂的问题，我们可以先来看下页分裂的过程

        ①. 从磁盘中申请页， 主键顺序插入

        ②. 第一个页没有满，继续往第一页插入

         ③. 当第一个也写满之后，再写入第二个页，页与页之间会通过指针连接

        ④. 当第二页写满了，再往第三页写入

         这是正常的顺序插入的结果，但是如果是乱序插入的话，就会出现页分裂

        ①. 加入1#,2#页都已经写满了，存放了如图所示的数据

        ②. 此时再插入id为50的记录，我们来看看会发生什么现象，可以看到是不能插入到第三页的

         ③. 只能插入到47后面，但是第一页的空间不够，就会产生分裂，将第一页中一半的数据移动到 三页，然后再将50插入到三页中去

        

        ④. 最后再将这些页重新进行链接，这样才能维护索引的结构，所以这样是比较消耗性能的

        页合并 

        当我们对数据进行删除的时候，达到一定的阈值的时候就会触发页合并，也就是将空缺的页通过后面的数据进行补充

        假设现在的页中数据的情况是这样的

        当对页中的数据进行删除的时候，并没有用真正意义上的删除，而是将数据所占用的标记为可以使用，这样其他的数据都可以来使用这份空间

        

        当我们删除记录达到MERGE_THRESHOLD（默认为50%）时，就会触发页合并

        这样新插入的数据就会插入到第三页中

        索引设计原则

        从索引的结构我们就可以看出，索引的设计不应该过于复杂，这样的话索引在顺序存储的过程中不利于维护，所以下面的索引设计中应该尽量满足的一些规范：

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

        

Order by优化

        MySQL的排序方式

        MySQL的排序方式有两种：

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

        对于这两种排序方式，用脚指头想也知道使用索引的排序方式性能肯定更好一些，所以要尽量使用Using index

        现在我们创建一个联合索引

-- 创建索引
create index idx_user_age_phone_aa on tb_user(age,phone);

        再对age和phone进行排序

explain select id,age,phone from tb_user order by age;

        使用explain命令可以看到是走的索引

        使用索引的规则也遵循最左前缀法则，上篇文章中有提到，这里就不做过多赘述

explain select id,age,phone from tb_user order by phone , age;

        创建索引的时候是age在前，但是语句中的phone在前，所以会使索引失效 

        

        联合索引在创建的时候默认都是ASC排序，如果在排序中指定了排序的类型，如果两个字段排序的类型不同也会使索引失效

-- 都进行降序排列，这是OK的，默认采用的是升序
explain select id,age,phone from tb_user order by age desc , phone desc ;


-- age使用升序，phone使用降序的话，phone的索引就会失效，因为创建时默认是ASC
explain select id,age,phone from tb_user order by age asc , phone desc ;

         要解决也很简单，再创建一个索引不就好了

create index idx_user_age_phone_ad on tb_user(age asc ,phone desc);

        综上所述,我们得出order by优化原则:

1.  根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
2.  尽量使用覆盖索引。
3.  多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
4.  如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

 Group by 优化

        我们先看一下没有使用索引时，分组是如何进行的

        可以看到是使用了一张临时表，这样效率就不太高了

        那么我们可以将之前的索引都删了，创建一个联合索引

-- 针对于 profession ， age， status 创建一个联合索引。
create index idx_user_pro_age_sta on tb_user(profession , age , status);

        然后 再执行相同的语句，可以看到这次就使用了索引

       

         但是如果单单是对age进行分组的话还是会采用临时表，还是因为最左前缀法则

所以，在分组操作中，我们需要通过以下两点进行优化，以提升性能：

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

Limit 优化

        假设现在有1000w条数据，如果我想分页查询前十条数据，那么相应速度肯定是很快的，但是如果我要查询的是最后10条数据呢？那样是不是就很慢了，所以就得对这部分进行优化。

        优化思路：我们为什么一定得根据分页去查询全部数据呢？我们为什么不先查询到最后十条数据的主键id，再通过id去查询数据，这样不就可以利用到索引了

        由于MySQL中不支持 in 的子查询中带有limit参数，所以采用一个连表查询的方式

explain select * from tb_sku t , (select id from tb_sku order by id
limit 2000000,10) a where t.id = a.id;

Count 优化

        在使用count的时候，如果数据量非常大那么就非常的耗时，但是很遗憾的是在MySQL中从count暂时还没有特别好的优化方案，但是可以借助其他中间件来优化，就是我们可以自己进行计数，例如使用redis来维护一个count的值，需要的时候自己去取就行，所以这里着重的看下count的用法

         从表中我们可以看出按照效率进行排序的话：count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)或者count(1)

update优化

        我们需要注意的就是在MySQL中update语句中where条件后面的字段，如果设置了索引的，那么修改时会对这一行的数据进行锁定，如果没有设置索引，那么整张表的数据都被锁定，所以update的条件尽量使用索引，否则十分影响性能

        InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

        注：如果我对profession，age都做了单列索引但是phone没有

-- 为profession创建索引
create index user_profession on tb_uer(profession)

-- 为age创建索引
create index user_age on tb_uer(age)

        但是我的查询条件中包含了phone，那么是行锁还是表锁呢？

update tb_user set profession = '软件工程' where age = '29' and  phone = '17799990023'

        这个是行锁，虽然where中有没有索引是字段，但是通过age字段就可以筛选出对应的数据，然后再在这些数据中再次通过phone字段进行筛选，所以锁住的就是满足age = ‘29’的这些行数据