SQL优化

发布时间:2023年12月25日

SQL性能分析

查看SQL执行频率

mysql连接成功后,通过show [session | global] status 命令可以提供服务器状态信息。通过以下指令,可以查看当前数据库insert、update、delete、select的访问频率

--Com后面根7个下划线,代表7个字符
show global status like 'Com_______'

慢查询日志

  1. 开启慢查询日志,和设置慢查询时间
  2. 执行sql语句
  3. 查看慢查询日志文件中的记录信息

在mysql的配置文件(linux系统配置文件在/etc/my.cnf)中配置如下信息:

#开启慢查询日志
slow_query_log=1
#设置慢查询日志时间为2s,sql执行时间超过2s,就会视为慢查询,记录慢查询日志
long_query_time=2

配置完成后,重启mysql服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log

可以根据慢查询日志,来定位执行效率比较低的SQL。

profile详情

show profile 能够在做sql优化时帮助我们了解时间都耗在哪里了。通过have_profiling参数,能够看到当前mysql是否支持profile操作:

select @@have_profiling;

默认profile是关闭的,可以通过set语句在session/global级别开启profiling;

# session级别是当前会话有效,关闭会话后恢复默认设置
# global级别是全局生效,关闭或重启后依然有效
set profiling = 1

执行一些sql语句,然后通过以下指令查看sql语句的执行耗时:

#查看每一条sql耗时基本情况
show profiles;
# 查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
# 查看指定query_id的sql语句cpu的使用情况
show profile cpu for query query_id;

explain执行计划

在执行sql语句的时候,在前面加上explain

# 示例
explain select id,nmae from student where id = 1;

explain执行计划各字段含义:

  1. id:select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下;id不同,值越大,越先执行)
  2. select_type:表示select的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
  3. type:表示连接类型,性能由到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
  4. possible_key:显示可能应用在这张表上的所以,一个或多个
  5. Key:实际使用的所以,如果为NULL,则没有使用索引
  6. Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
  7. rows:MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不是准确的
  8. filtered:表示返回结果的行数占需读取行数的百分百,filtered的值越大越好

在这里插入图片描述

SQL优化

insert优化

  1. 批量插入的时候要手动提交事务
  2. 主键最好按照顺序插入
  3. 插入大量数据的时候使用load命令

load命令示例:

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

主键优化

  1. 尽量降低主键的长度
  2. 插入数据的时候,尽量选择主键顺序插入,并使用AUTO_INCREMENT自增主键(减少对数据页的操作)
  3. 尽量不要使用UUID做主键或者是其他自然主键,如身份证
  4. 避免对主键的修改(减少对数据页的操作)

order by 排序优化

首先说两个概念:

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

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

group by 分组优化

  1. 在分组操作时,可以通过索引提高效率
  2. 分组操作时,索引的使用依然要保证最左前缀法则 (只要使用到索引,这个条件一定要满足)

limit 分页查询优化

limit 2000000,10 
# 此时需要MySQL排序钱2000010记录,仅仅返回2000000-2000010的记录,其他记录丢弃
# 查询排序的代价非常大。

优化思路:一般分页查询时,通过创建 覆盖索引 能够比较好的提高性能,可以通过覆盖索引加子查询形式进行优化

count 聚合函数优化

  1. MyISAM引擎把一个表的总行数存在磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高
  2. InnoDB引擎就麻烦了,它直接count(*) 的时候,需要把数据一行一行的从引擎中读出来,然后累积计数

count()是一个聚合函数,对于返回的结果集,一行行的判断,如果count函数的参数不是NULL,累积值就加1,否则不见,最后返回累计值。

count的几种用法:

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

安装效率排序的话,count(字段) < count(主键id) < count(1)≈count(*)
所以建议按照顺序,尽量选择最后一个。

update 更新优化

更新数据时,如果更新的字段没有索引,那么就会使用表锁,如果有索引,就会使用行锁。

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

优化:
尽量根据主键/索引字段进行数据更新

总结(必看)

在这里插入图片描述

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