Sql优化

2024-01-02 09:22:15

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档


前言

SQL优化

插入数据

insert优化

  • 建议使用批量插入
  • 手动提交事务
  • 主键顺序插入

当出现大批量插入数据
此时使用insert语句性能较低,推荐使用load语句
在这里插入图片描述
如何使用load?

  1. 客户端链接服务器加上参数 --load-infilemysql --local-infile -u -root -p
  2. 设置全局参数local_infile为1.开启从本地加载文件导入数据的开关
    我们可以使用:select @@localhost_infile;查看开关是否开启
    在这里插入图片描述
    表示没有开启
set global local_infile = 1;

在这里插入图片描述
此时就打开了

  1. 执行load指令,将准备好的数据加载到表结构当中。
load data local infile '文件路径' into table 表名 fields terminated by ',' lines terminated by '/n';

表示每个字段用“,”分割,每行用“/n“分割。

主键优化

  • 数据组织方式:
    在这里插入图片描述
    ”黄色的块“相当于是一个个的页;
    在这里插入图片描述
    一个区包含64个页,一个页可以为空,

页分裂

主键顺序插入
主键乱序插入:
在这里插入图片描述
此时插入50这个数据会如何操作呢?
在这里插入图片描述
因为50这个数据插入不进去1页,所以他会重新区开辟一个页面
将50以及其他元素写入
而此时1数据页下一个页面不再是2数据页
而是3数据页,因此,原本的指针链表会重置。
这种现象就叫做–页分裂

页合并

在这里插入图片描述
当页中删除记录达到MERGE_THRESHOLD(合并页阈值,可以自己设置)(默认50%)的时候,会开始寻找靠近的页,观察是否能够合并。

主键设计原则

  • 满足业务需求的情况下,尽量降低主键的长度。
  • 插入数据时,尽量选择顺序插入,选择AUTO_INCREMENT主键。
  • 尽量不要使用UUID做主键或者其他自然主键,如身份证号。
  • 业务操作时,尽量避免对主键的修改

order by优化

  1. Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
  2. Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高
    在这里插入图片描述
    可以看到,因为没有满足最左前缀法则而产生的Using filesort
    在这里插入图片描述
    因为创建索引时,没有指定顺序,默认时升序,而此时我们age升序,phone降序,就会产生额外的排序。
    可以通过show index from 表名查看
    在这里插入图片描述
    Collation中的‘A’就表示为升序排列
    在这里插入图片描述
    可以通过创建索引指令来实现,一个升序排列一个降序排列
    在这里插入图片描述
    叶子节点排列顺序如上。

总结:

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

group by优化

同样的,group by使用索引时也要满足最左前缀法则
在这里插入图片描述
在这里插入图片描述
因为不满足最左前缀法则,所以使用了临时表,性能降低。
在这里插入图片描述
但是,在分组前方,加一个‘过滤’where profession='软件工程'
我们可以看到,索引正常使用马,没有使用到临时表。

  • 在分组操作时,可以通过索引来提高效率
  • 分组操作时,索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat,则句式可以是select ... where profession order by age,这样也符合最左前缀法则

limit优化

常见的问题如limit 2000000, 10,此时需要 MySQL 排序前2000000条记录,但仅仅返回2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化方案:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

– 此语句耗时很长

select * from tb_sku limit 9000000, 10;

– 通过覆盖索引加快速度,直接通过主键索引进行排序及查询

select id from tb_sku order by id limit 9000000, 10;

– 下面的语句是错误的,因为 MySQL 不支持 in 里面使用 limit

 select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);

– 通过连表查询即可实现第一句的效果,并且能达到第二句的速度

select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;

count优化

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高(前提是不适用where);
InnoDB 在执行 count(*) 时,需要把数据一行一行地从引擎里面读出来,然后累计计数。
优化方案:自己计数,如创建key-value表存储在内存或硬盘,或者是用redis

count的几种用法:

  • 如果count函数的参数(count里面写的那个字段)不是NULL(字段值不为NULL),累计值就加一,最后返回累计值
  • 用法:count(*)、count(主键)、count(字段)、count(1)
  • count(主键)跟count(*)一样,因为主键不能为空;count(字段)只计算字段值不为NULL的行;count(1)引擎会为每行添加一个1,然后就count这个1,返回结果也跟count(*)一样;count(null)返回0

各种用法的性能:

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

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

update优化

对于update优化,主要为防止行级锁升级为表级锁
在这里插入图片描述
我们在第一个客户端开启事务,更新id为1 的这行字段,此时,在我们未提交事务的时候,就把id为1这一行锁住了。
其他修改无法修改成功这一行,但是对其他行没有修改限制
在这里插入图片描述
因为我们name字段没有索引,所以把整张表都锁住了,其他修改语句无法成功。
因此我们在更新的时候,要根据索引字段进行更新。

在InnoDB的行锁时针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行级锁升级为表级锁。

总结:

在这里插入图片描述

文章来源:https://blog.csdn.net/weixin_70496041/article/details/135316903
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。