一、Insert优化:
1、批量插入:(单次批量500-1000条)
2、使用手动提交事务:
start transaction;
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
insert into tb_user values(xxx,xxx),(xxx,xxx),(xxx,xxx);
commit;
3、主键顺序插入:
二、大批量插入数据:
如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下:
#客户端链接服务器时,加上参数 --local-infile
mysql--local-infile -u root -p
#设置全局参数local_infile=1,开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
此时注意:主键顺序插入性能高于乱序插入
三、主键优化:
数据组织方式:
在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表成为索引组织表(IOT)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ETEbv6Sv-1687508886797)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619112246636.png)]
页分裂:
页可以为空,也可以填充一半,也可以填充100%。
每个页包含了2-N行数据(如果一行数据多大,会行一处),根据主键排列。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pqiZcWnt-1687508886798)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170029246.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OtUmf0dN-1687508886798)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170037711.png)]
页合并:
当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
当页中删除的记录打到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ngaOl6Kg-1687508886799)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170407013.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lFaCuZ4u-1687508886799)(C:\Users\FASQD\AppData\Roaming\Typora\typora-user-images\image-20230619170435787.png)]
注意:MERGE_THRESHOLD:可以自己设置,在创建表或者创建索引时指定。
主键设计原则:
(1)满足业务需求的情况下,尽量降低主键长度。
(2)插入数据时候,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
(3)尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
(4)业务操作时,避免对主键的修改。
四、order by 优化
Extra:额外信息
(1)Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫Filesort排序。
(2)Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,不需要额外排序,操作效率高。
-- show index from tb_user;
#创建索引,默认升序排列
-- create index idx_age_phone on tb_user(age,phone);
#查看执行计划可以发现使用idx_age_phone索引
-- EXPLAIN select id,age,phone from tb_user order by age,phone;
#创建索引后进行降序排列
-- EXPLAIN select id,age,phone from tb_user order by age desc,phone desc;
#一个升序一个降序会导致效率降低
-- EXPLAIN select id,age,phone from tb_user order by age asc,phone desc;
#创建对应升序/降序字段对应索引
-- create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
1、根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
2、尽量使用覆盖索引
3、多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(asc、desc)
4、如果不可避免的出现filesort,大数据量排序时候,可以适当增大排序缓冲区大小sort_buffer_size(默认256k)
五、group by优化
-- show index from tb_user;
#执行分组操作,根据profession字段分组
-- EXPLAIN select profession,count(*) from tb_user group by profession;
#创建索引
-- create index idx_user_pro_age_sta on tb_user(profession,age,status);
#执行分组操作,根据age分组(效率低)
-- EXPLAIN select age,count(*) from tb_user group by age;
#根据profession、age分组
-- EXPLAIN select profession,age,count(*) from tb_user group by profession,age;
#添加where条件,指定profession之后再根据age分组
-- EXPLAIN select age,count(*) from tb_user where profession='软件工程' group by age;
在分组操作时候,可以通过索引来提高效率。
分组操作时,索引的使用也需要满足最左前缀法则。
六、limit优化
常见问题:limit 2000000,10 => 此时需要MySQL排序前2000010记录,仅仅返回2000000-2000010的记录,其他记录丢失,查询排序代价非常大。
优化思路:一般分页查询时,通过创建覆盖索引能够比较好的提高性能,可以通过覆盖索引加子查询的形式进行优化
-- select * from tb_sku LIMIT 9000000,10;
-- 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;
七、update优化
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。
begin;
#存在主键索引,所以使用行级锁
update course set name = 'xxxx' where id = 1;
--commit;
#事务a
begin;
#name字段不存在索引,引擎使用表锁
update course set name = 'xxxx' where name = 'x';
--commit;
#事务b
begin;
#此时因为事务a已经给course表施加表锁,下面语句失效
update course set name = 'xxx' where id = 2;
--commit;