2.3SQL优化
2.3.1 插入数据
批量插入
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;
主键顺序插入
主键顺序插入: 0 9 21 1
主键顺序插入 1 2 3
顺序插入的性能高于乱序插入的性能
大批量的数据插入
如果一次性插入大批量数据,使用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';
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;
走的是全表扫描,后面是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;
二者运行的结果是一样的,并且可以看到,最后的是using index
运行结果:
- 当给字段倒序排序:
explain select id ,age ,phone from tb_user order by age desc,phone desc;
运行结果:
可见也没有变化,不过多了一个Backward index scan ,因为使用了desc,所以相当于反向扫描索引
- 当交换排序位置:
explain select id ,age ,phone from tb_user order by phone , age;
运行结果:
出现了using filesort,原因是因为,建立索引时,age字段是在phone字段前面的,这个sql实际上违背了最左前缀法则
- 根据age升序,phone倒序:
explain select id ,age ,phone from tb_user order by age , phone desc;
运行结果:
因为当创建索引时,并没有指定排序索引排序顺序(默认是升序),所以这时一个升序一个降序,就需要额外的空间了
那么怎么优化呢?
可以根据对应的顺序,从新再建立一个索引
#age升序排列 phone降序排列
create index index_user_age_phone_ad on tb_user(age asc , phone desc);
再次执行后的结果:
已经没有出现filesort了,使用的索引也是刚刚建立的索引,这样的索引叶子节点:
总结
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC\DESC)
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
2.3.4 group by优化
explain select profession,count(*) from tb_user group by profession;
此时用到了临时表 using temporary
加上索引age profession status
explain select profession,age,count(*) from tb_user group by profession,age;
用到的是using index
- 在分组操作时,可以通过索引来提高效率
- 分组操作时,索引的使用也是满足最左前缀法则
2.3.5 limit优化
#查找第一百万后的十条数据
select * from tb_sku limit 9000000,10;
执行用时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;
执行耗时 10s左右,比原来的减少了10s左右的时间
2.3.6 count优化
#查询总数据量
select count(*) from tb_user;
- MyISAM引擎把一个表的总数据存在了磁盘上,因此执行count(*) 的时候会直接返回这个数,效率很高
- InnoDB引擎就比较麻烦了,他执行count(*)的时候,需要把数据一行一行的从引擎里读出来,然后累计计数
优化思路:
目前没有很好的优化思路,可以自己计数
select count(profession) from tb_user;
查询的是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的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则 会从行锁升级为表锁
,尽量根据主键\索引进行更新操作