SQL优化

# 一次性插入多条数据,可以用一条sql解决
insert into test values(10,12),(20,13),(19,25);# 500-1000条比较合适
# 如果有几万条,可以分批批量插入

# 手动提交事务
# 默认是自动提交,多条insert涉及频繁的事务开启和提交
执行insert之前,先开启事务
多条insert语句执行完之后,统一提交事务

# 主键顺序插入

# 大批量数据插入(几百万条),此时insert性能较低,使用load指令
# 导入csv文件
# local表明从客户主机上按路径读取文件,csv采用的逗号分隔符
load data local infile 'C:\\Users\\hhx\\Downloads\\log.txt' into table test character set 'gbk' fields terminated by ',';
load data local infile 'src1/step1/product.csv' into table product character set 'gbk' fields terminated by ',';

主键优化

在InnoDB存储引擎中,表数据是根据主键顺序组织存放的,叶子节点是有序的
这种存储方式的表称为索引组织表(Index Organized Table).

主键乱序插入,可能会出现页分裂

# 主键设计原则
1.满足业务需求的情况下,尽量降低主键的长度
2.插入数据时,尽量选择顺序插入
3.尽量不要使用UUID做主键或者其他自然主键(UUID是无序的,乱序插入可能会出现页分裂,且UUID太长)
4.业务操作时,避免对主键的修改

orderby优化

# mysql当中的排序有两种方式:
1.Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作
所有不是通过索引直接返回排序结果的排序都叫filesort排序
2.Using index:通过有序索引顺序扫描直接返回有序数据,这种情况不需要额外排序,操作效率高

create index index_name on tb_user(age asc,phone desc);

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

groupby优化

# 建立索引
where在group by之前执行
having在group by之后执行
所以筛选的话,优先考虑where

limit优化

一条一条读取到10100条,然后再根据offset的设置,舍弃前10000条记录,返回后面的100条记录
MySQL的实现分为引擎层和数据层,limit offset只能作用于引擎层返回的结果集
因此对引擎层来说,他也不知道前10000个是会扔掉的数据,只能先一股脑地往上传
# 会很慢
select * from tb_user limit 9000000,10;

# 一般思路:覆盖索引+子查询
explain
select t.* from test t,(select id from test order by id limit 9000000,10) p where t.id = p.id;
select a.* from test a inner join (select id from test limit 9000000,100) b on a.id=b.id;

explain
select * from test limit 9000000,10;
# 定位到第9000000条数据,利用主键自增的规律
explain
select * from test where id > 9000000 limit 10

count优化

select count(*) from test;
# MyISAM把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回,效率很高
# InnoDB执行count(*)的时候,需要一行一行的从引擎里读出来,累积计数
count()是一个聚合函数,对于返回的结果集,一行一行地判断,如果count的参数不是null,累计值就加一

count(主键):遍历整张表,把每一行的主键都取出来,返回给服务层,服务层拿到之后,直接进行累加(不用判断是否为null)
count(字段):
没有not null约束:遍历整张表,把每一行的字段值都取出来,在服务层判断是否为null
有not null约束:遍历整张表,把每一行的字段值都取出来,直接进行累加

count(1):遍历整张表,但不取值,服务层对于返回的每一行,都会放一个数字1进去,按行进行累加
count(*):InnoDB并不会取出全部字段,专门做了优化,服务层直接按行进行累加

count(*)>count(1)>count(主键)>count(字段)

update优化

更新数据时根据索引字段进行更新,是行锁(对当前行锁定)
不根据索引字段进行更新,是表锁(只有当前事务提交了,别的事务才能对这个表进行更新)

InnoDB的行锁是针对索引加的锁
更新时要使用索引,且索引不能失效,否则会从行锁升级为表锁

limit慢查询的原理和优化
limit offset慢查询背后的原因和解法
用会LIMIT
limit,offset
实践中如何优化MySQL