Mysql高级（三）---SQL优化

2.3SQL优化

2.3.1 插入数据

批量插入

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

手动提交事务

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;
1
2
3
4
5
6
7

主键顺序插入

主键顺序插入： 0 9 21 1
主键顺序插入 1 2 3 
1
2

顺序插入的性能高于乱序插入的性能

大批量的数据插入

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

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

#设置全局参数local_infile 为1 ，开启从本地文件中导入数据的开关
set global_infile = 1;

#执行load指令将准备好了的数据，加载到表结构当中
#root下的sql.log文件，加到表tb_user中，分割条件为 ',' 每行进行换行符换行
load data local infile 'root/sql.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
1
2
3
4
5
6
7
8
9

2.3.2 主键优化

数据组织方式

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

页分裂

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

主键乱序插入时：

因为链表是有序的，所以他会从第一个数据页的50%处断开，加入到新的数据页中：

接着会把50加入到新的数据页后面，对链表排指针重新设置（因为再指向2#下已经不是有序的），指向下一个数据页为3#：

页合并

当删除一行记录时，实际上记录并没有被物理页删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用

当页中删除的纪录达到MERGE_THRESHOLD（合并页的阈值，可以自己设置，在创建表后者创建索引时指定，默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可将两个页合并以优化空间使用。

当删除的数据超过一半时，会将后面的数据页补刀前面的数据页中：

如果在加入新的数据，往后面添加：

主键设计原则

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

2.3.3 order by优化

Using filesort

通过表的所有或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

# age phone 不是索引
select id ,age ,phone from tb_user order by age;

#查看执行计划
explain select id ,age ,phone from tb_user order by age;
1
2
3
4
5

走的是全表扫描，后面是Using filesort

Using index

通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高

（以下查询未用*，避免回表查询）

• 给age、phone建立联合索引，再次执行

#id age phone 是联合索引
select id ,age ,phone from tb_user order by age;

#查看执行计划
explain select id ,age ,phone from tb_user order by age;

explain select id ,age ,phone from tb_user order by age,phone;
1
2
3
4
5
6
7

二者运行的结果是一样的，并且可以看到，最后的是using index

运行结果：

• 当给字段倒序排序：

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

运行结果：

可见也没有变化，不过多了一个Backward index scan ，因为使用了desc，所以相当于反向扫描索引

• 当交换排序位置：

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

运行结果：

出现了using filesort，原因是因为，建立索引时，age字段是在phone字段前面的，这个sql实际上违背了最左前缀法则

• 根据age升序，phone倒序：

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

运行结果：

因为当创建索引时，并没有指定排序索引排序顺序（默认是升序），所以这时一个升序一个降序，就需要额外的空间了

那么怎么优化呢？

可以根据对应的顺序，从新再建立一个索引

#age升序排列 phone降序排列
create index index_user_age_phone_ad on tb_user(age asc , phone desc);
1
2

再次执行后的结果：

已经没有出现filesort了，使用的索引也是刚刚建立的索引，这样的索引叶子节点：

总结

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

2.3.4 group by优化

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

此时用到了临时表 using temporary

加上索引age profession status

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

用到的是using index

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

2.3.5 limit优化

#查找第一百万后的十条数据
select * from tb_sku limit 9000000,10;
1
2

执行用时20s左右，在大数据量的情况下，数据越往后，时间越长，查询排序的时间非常大

怎么优化呢？—覆盖索引+子查询

#查找id从9000001 - 9000010的数据，然后将结果看做成一张表
select id from tb_sku order by id limit 9000000,10;

#使用多表连查
select s.* from tb_sku s ,(select id from tb_sku order by id limit 9000000,10) a where s.id = a.id;
1
2
3
4
5

执行耗时 10s左右，比原来的减少了10s左右的时间

2.3.6 count优化

#查询总数据量
select count(*) from tb_user;
1
2

• MyISAM引擎把一个表的总数据存在了磁盘上，因此执行count(*) 的时候会直接返回这个数，效率很高
• InnoDB引擎就比较麻烦了，他执行count(*)的时候，需要把数据一行一行的从引擎里读出来，然后累计计数

优化思路：

目前没有很好的优化思路，可以自己计数

select count(profession) from tb_user;
1

查询的是profession不为null的数据数

用法：

count()是一个聚合函数，对于返回的结果集，一行一行的判断，如果count函数的参数不是null，累计值就+1，否则不加，最后返回累计值

• count(主键)：遍历整张表的数据，把每一行的id都取出来，返回给服务层，服务层拿到主键之后，直接按行累加（主键不可能为null）
• count(字段)
  • 没有not null约束：InnoDB引擎会把整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null计数累加。
  • 有not null约束：InnoDB会遍历整张表把每一行的字段值取出来，返回给服务层，直接按行进行累加
• count(1)：InnoDB遍历整张表，但不取值。服务层对于返回的每一行，放一个数字"1"进去，直接按行进行累加
• count(*)：不取值，直接进行累加（InnoDB做过优化）

按照效率排序：

count(字段) < count(主键id) < count(1) = count(*)，所以尽量使用count(*)

2.3.7 update优化

在执行update语句的问题，InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则 会从行锁升级为表锁

，尽量根据主键\索引进行更新操作