SQL优化
1.插入
- insert 优化
--批量插入
insert intp tb_user values(1,"zhangsan"),(2,"lisi"),(3,"wangwu");
--手动提交事务
start transaction;
insert intp tb_user values(1,"zhangsan");
insert intp tb_user values(2,"lisi");
commit;
--主键顺序插入的性能高于乱序插入
1 2 3 4 5 7 8 9 15 21
- 大批量插入数据
使用load指令指令进行插入
2.主键优化
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)
乱序会出现页分裂的现象
- 页分裂
- 页合并
- 主键设计原则
3.order by 优化
Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort uffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。
Using index :通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。
Using index 效率 > Using filesort
都会走索引
不会走索引 默认创建的都是ASC
可以自己定义排序规则
create index idx_user_age_pho_ad on tb_user (age asc , phone desc);
4.group by 优化
- 总结
- 在分组操作时,可以通过索引来提高效率。
- 分组操作时,索引的使用也是满足最左前缀法则的。
5.limit 优化
- 一个常见又非常头疼的问题就是limit 2000000,10,此时需要MySQL排序前2000010记录,仅仅返回2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大。
大数据量,越往后分页,效率越低
优化方法
--覆盖查询+子查询
explain select * from tb_sku t,
(select id from tb_sku order by id limit 20000000,10) a
where s.id = a.id ;
6.count优化
优化思路:自己计数
count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count 函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。
用法:
count (*) 、 count(主键)、
count(字段)、 # 记录字段不为NULL的条数
count (1)
7.update优化
- 根据索引字段去更新,效率高
**InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。**一旦升级为表锁,并发性能就会降低
--例子 MySQL客户端一
begin; --开启事务
update student set no="194031529" where id =1 ;
未提交
--MySQL客户端二
begin; --开启事务
update student set no="194031528" where id =3 ;
--执行可以成功
--例子 MySQL客户端一
begin; --开启事务
update student set no="194031529" where name="lisi";
未提交
--MySQL客户端二
begin; --开启事务
update student set name="zhangsan" where id=4 ;
--执行不可以成功 原因MySQL客户端一 name没有加索引,加的是表锁,所以他把整张表都锁住了MySQL客户端二就执行不了了,得等MySQL客户端一提交
但给name字段加了索引再测试,就不会出现上述问题