MySQL进阶05_索引_SQL优化

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

• 大批量插入数据
  使用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 ;
1
2
3
4
5

6.count优化

优化思路：自己计数

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

用法:

count (*) 、 count（主键)、

count(字段)、 # 记录字段不为NULL的条数

count (1)
1
2
3
4
5
6
7

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字段加了索引再测试，就不会出现上述问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

总结