Java OpenResty Spring Spring Boot MySQL Redis MongoDB PostgreSQL Linux Android Nginx 面试 小程序 Arthas JVM AQS juc Kubernetes Docker DevOps


SQL 优化

SQL 面试 性能优化 大约 1301 字

insert

  • 批量插入,500-1000条一批次
  • 手动提交事务
  • 主键顺序插入
  • 大批量数据插入使用MySQL提供的load指令

update

  • where条件后字段需建立索引,避免将行锁升级为表锁
  • InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁

order by

  • Using filesort(使用文件排序)、Using index
  • 根据排序字段建立合适的索引,多字段排序时也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则,默认都是ASC
  • 如果不可避免地出现 filesort,大数据量排序时,可适当增大排序缓存区的大小sort_buffer_size(默认256K
show variables like 'sort_buffer_size';

输出:

mysql> show variables like 'sort_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| sort_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)

group by

  • Using temporary(使用临时表)、Using index
  • 建立索引
  • 同样遵循最左前缀法则

limit

  • 通过覆盖查询+子查询的方式
select t.* from test t, (select id from test order by id limit 10000000, 10) temp where t.id = temp.id;
  • 改成前一页后一页,携带每组记录最后一条的id请求下一次分页数据
select * from test where id > ? order by id limit 10;

count

  • count(主键)InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
  • count(字段)
    • 没有not null约束:InnoDB引擎会遍历整张表,把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
    • not null约束:InnoDB引擎会遍历整张表,把每一行的字段值都取出来,返回给服务层,直接按行进行累加。
  • count(1)InnoDB引擎会遍历整张表,但不取值。服务层对于返回的每一行,放一个数字1进去,直接按行进行累加。
  • count(*)InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。

按效率排序:count(主键)<count(字段)<count(1)count(*),所以尽量使用count(*)

阅读 309 · 发布于 2022-05-07

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb

扫描下方二维码关注公众号和小程序↓↓↓

扫描二维码关注我
昵称:
随便看看 换一批