SQL优化

最新推荐文章于 2024-11-11 21:16:16 发布

Dynamic_Pro

最新推荐文章于 2024-11-11 21:16:16 发布

阅读量42

点赞数

文章标签： sql 数据库 java

本文链接：https://blog.csdn.net/weixin_53611788/article/details/129602551

版权

插入数据

insert优化

批量插入，每次批量插入1000条数据
手动提交事务
按主键顺序插入

大批量插入数据

使用MySQL数据库提供的 load 指令进行插入
顺序插入性能更好

#客户端连接服务器时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/xxx.sql' into table 'tb_user' 
fields terminated by ',' lines terminated by '\n';

主键优化

几个概念

数据组织方式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表
页分裂：当不按主键顺序插入数据时，会新开辟一个页，用于维护主键的顺序性，并更改页之间指针的指向
页合并：删除一条记录时，在页中做标记，并不会立刻删除，当一个页删除元素达到了阈值，InnoDB会查找相邻的页是否可以合并
MERGE_THRESHOLD：合并页的阈值，默认50%，可以在创建表或创建索引时指定

主键设计原则

满足业务需求的情况下，尽量降低主键的长度。二级索引叶子结点存放主键，主键越长，二级索引所占空间越大。
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。避免页分裂现象。
尽量不用使用UUID做主键或者其他自然主键，如身份证号。它们是无序的。
在业务操作时，避免对主键的修改。修改主键会改变索引结构，代价比较大。

order by优化

根据排序字段建立合适的索引，多字段排序也遵循最左前缀法则
尽量使用覆盖索引，避免回表查询
多字段排序，一个升序一个降序，可以在创建索引时指定索引的排序规则
如果不可避免的出现filesort，大数据排序时，可以适当增大排序缓冲区的大小 sort_buffer_size（默认256k），如果超过阈值，会在磁盘文件中进行排序，效率很低

MySQL的两种排序方式

Using filesort：需要将返回的结果在排序缓冲区排序
Using index：通过索引顺序扫描直接返回有序数据，不需要额外排序，效率高

#默认创建一个升序排序的联合索引
create index idx_user_age_phone_aa on tb_user(age,phone);
#使用了idx_user_age_phone_aa索引，Extra内容为 Using index
explain select id,age,phone from tb_user order by age,phone;
#使用了idx_user_age_phone_aa索引，Extra内容为 Backward index scan;Using index
explain select id,age,phone from tb_user order by age desc,phone desc;
#不满足最左前缀法则，Extra内容为 Using index;Using filesort
explain select id,age,phone from tb_user order by phone,age;
#age排序用到了联合索引，但倒序排序phone时需要额外的排序，Extra内容为Using index;Using filesort
explain select id,age,phone from tb_user order by age asc,phone desc;

#对特殊的排序规则，可以按不同的排序规则创建索引
create index idx_user_age_phone_ad on tb_user(age asc,phone desc);
#此时将使用idx_user_age_phone_ad索引，Extra内容为Using index
explain select id,age,phone from tb_user order by age asc,phone desc;

group by 优化

建立索引提高性能，建立的索引也满足最左前缀法则
尽量使用覆盖索引，避免回表查询

#创建一个联合索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);

#使用了idx_user_pro_age_sta索引，Extra内容为 Using index
explain select profession,count(*) from tb_user group by profession;
#不满足最左前缀法则，用到了临时表，Extra内容为 Using index;Using temporary
explain select profession,count(*) from tb_user group by age;
#where和group by 连用，如果遵循最左前缀法则，也会用到索引，Extra内容为 Using index
explain select profession,count(*) from tb_user where profession='软件工程' group by age;

limit 优化

官方给的优化方式是：覆盖索引+子查询

#原始SQL
select * from tb_sku order by id limit 9000000,10;
#覆盖查询后的SQL
select id from tb_sku order by id limit 9000000,10;
#覆盖查询+子查询后的SQL,但当前版本不支持在 in 后 用到 limit
select * from tb_sku where id in (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;

count 优化

MyISAM 引擎把一个表的总行数存在了磁盘中，count(*)会直接返回总行数

InnoDB 引擎执行count(*)，需要遍历所有行数据，然后进行累加计数

优化思路：借助Redis自行维护计数，插入数据时记录数+1，删除记录时记录数-1

count的几种用法

count(主键)：InnoDB 引擎会遍历整张表，把每一行的主键id取出，返回给服务层。服务层拿到主键后进行累加
count(字段)：
- 有 not null 约束，InnoDB 引擎遍历整表取出每行字段值，返回给服务层。服务层直接累加
- 没有 not null 约束，InnoDB 引擎遍历整表取出每行字段值，返回给服务层。服务层判断是否为空，不为空则计数累加
count(1)：InnoDB 引擎会遍历整张表，但不取值。服务层对返回的每一行，放一个数字1进去，直接进行累加
count(*)：InnoDB做过优化，不会取出全部字段，而是在服务层直接累加
按照效率排序：count(*) > count(1) > count(主键) > count(字段)