MySQL进阶-SQL优化

目录

insert优化

主键优化

页分裂

 order by优化

测试：

group by优化

limit优化

count优化

update优化

---

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;

优化方案三

主键顺序插入，性能要高于乱序插入-----后文会讲解

主键乱序插入 : 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语句插入性能较低，此时可以使 用MySQL数据库提供的load指令进行插入。操作如下：

左图为待插入的数据，右图为sql中搜索的数据，大概有100w数据插入需要19秒左右。

-- 第一步：客户端连接服务端时，加上参数 -–local-infile
mysql –-local-infile -u root -p
-- 第二步设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile = 1;
-- 第三步执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table tb_user fields
terminated by ',' lines terminated by '\n' ;

主键优化

接下来我们来说一下主键优化策略。为什么说顺序插入会优于乱序插入。

我们都知道在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表 (index organized table IOT)。

 行数据，都是存储在聚集索引的叶子节点上的。而我们之前也讲解过InnoDB的逻辑结构图：

 在InnoDB引擎中，数据行是记录在逻辑结构 page 页中的，而每一个页的大小是固定的，默认16K。 那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行row在该页存储不小，将会存储 到下一个页中，页与页之间会通过指针连接。

页分裂

页可以为空，也可以填充一半，也可以填充100%。每个页包含了2-N行数据(如果一行数据过大，会行 溢出)，根据主键排列。

主键顺序插入效果。

插入第一页。

 

 

 可以看出顺序插入时，数据会依次插入到每页当中。

主键乱序插入效果

假如，我们已经存入以下数据

 此时再插入id为50的记录，我们来看看会发生什么现象

因为索引结果的叶子结点是有序的，按照顺序由应该在47之后

 但是47所在的1#页，已经写满了，存储不了50对应的数据了。 那么此时会开辟一个新的页 3#。

 乱序插入有页分裂现象，因此顺序插入性能会优于乱序，因此在插入数据时，一定使得数据顺序插入。

【主键设计原则】

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

 order by优化

MySQL的排序，有两种方式：

• Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作。
• 如果缓冲区大小不够的话会在磁盘中进行排序，效率是非常低的，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
• Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要 额外排序，操作效率高。
• 对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序 操作时，尽量要优化为 Using index。

测试：

在之前测试时，把tb_user表所建立的部分索引直接删除

drop index idx_user_phone on tb_user;
drop index idx_user_phone_name on tb_user;
drop index idx_user_name on tb_user;

执行排序SQL

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

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

 

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

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

 同时mysql也支持降序联合索引，跟正序是一样的，我们看一下他们的结构图。

 由上述的测试,我们得出order by优化原则:

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

 group by优化

我们主要来看看索引对于分组操作的影响。首先我们先将 tb_user 表的索引全部删除掉 。

 接下来，在没有索引的情况下，执行如下SQL，查询执行计划：

explain select profession , count(*) from tb_user group by profession;

 然后，我们在针对于 profession ， age， status 创建一个联合索引。

create index idx_user_pro_age_sta on tb_user(profession , age , status);

紧接着，再执行前面相同的SQL查看执行计划。

 

• 我们发现，如果仅仅根据age分组，就会出现 Using temporary ；
• 而如果是 根据 profession,age两个字段同时分组，则不会出现 Using temporary。
• 原因是因为对于分组操作， 在联合索引中，也是符合最左前缀法则的。

limit优化

数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记 录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

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

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(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽 量使用 count(*)。

update优化

针对update，我们需要注意行锁升级为表锁的情况。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

update course set name = 'SpringBoot' where name = 'PHP' ;

当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。 导致该update语句的性能 大大降低。

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