慢sql优化（二）

最新推荐文章于 2024-11-01 11:28:07 发布

必吃早饭选手

最新推荐文章于 2024-11-01 11:28:07 发布

阅读量61

点赞数

分类专栏： mysql 文章标签： sql 数据库

本文链接：https://blog.csdn.net/xiaochaochoa/article/details/134372081

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

慢sql优化（二）

具体慢sql优化

隐式转换

隐式转换是指在查询条件中，将字符串类型的字段与数字类型的常量进行比较，MySQL会将字符串类型的字段转换为数字类型，然后再进行比较。这种转换会导致索引失效，从而导致全表扫描，影响查询性能。

最左匹配

MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，相当于建立了(a)、(a,b)、(a,b,c)三个索引。

深分页问题

例如：select id,name,balance from account where create_time> ‘2020-09-19’ limit 100000,10;

1、通过普通二级索引树idx_create_time，过滤create_time条件，找到满足条件的主键id。

2、通过主键id，回到id主键索引树，找到满足记录的行，然后取出需要展示的列（回表过程）

3、扫描满足条件的100010行，然后扔掉前100000行，返回。
方法：减少回表次数来优化

标签记录法：就是标记一下上次查询到哪一条了；局限性：需要一种类似连续自增的字段。
select id,name,balance FROM account where id > 100000 limit 10;
延迟关联法：就是把条件转移到主键索引树，然后减少回表
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN (SELECT a.id FROM account a WHERE a.create_time > ‘2020-09-19’ limit 100000, 10) AS acct2 on acct1.id= acct2.id;

in元素过多

分批进行

order by 走文件排序导致的慢查询

Using index condition；Using filesort
order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。

rowid排序初始化sort_buffer，放入需要排序的age字段，以及主键id

全字段排序初始化sort_buffer，放入需要查询的name、age、city字段

调整max_length_for_sort_data、sort_buffer_size等参数优化；

索引字段上使用is null， is not null，索引可能失效

单个name字段加上索引，并查询name为非空的语句，其实会走索引
单个card字段加上索引，并查询name为非空的语句，其实会走索引
但是它两用or连接起来，索引就失效了

索引字段上使用（！= 或者 < >），索引可能失效

左右连接，关联的字段编码格式不一样

user表的name字段编码是utf8mb4，而user_job表的name字段编码为utf8

group by使用临时表

group by使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。

如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。

如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

优化方法：
group by 后面的字段加索引

order by null 不用排序：执行计划中，不再出现“Using filesort”

尽量只使用内存临时表

使用SQL_BIG_RESULT：消除临时表

SQL_SMALL_RESULT：显示指定用内存表(memory引擎)

SQL_BIG_RESULT：显示指定用磁盘临时表(myisam引擎或innodb引擎)

两者区别在于，使用磁盘临时表可以借助主键做去重排序，适合大数据量；使用内存表写入更快，然后在内存中排序，适合小数据量

delete + in子查询不走索引

避免在where子句中使用or来连接条件

使用union all来代替or
分开两条sql

1.使用or可能会使索引失效，从而全表扫描：

尽量使用数值替代字符串类型

1.主键(id)：primary key优先使用数值类型int,tinyint
2.性别(sex):0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

1.因为引擎在处理查询和连接时会逐个比较字符串中每一个字符：
2.而对于数字型而言只需要比较一次就够了；
3.字符会降低查询和连接的性能，并会增加存诸开销；

批量插入性能提升

默认新增$QL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显

避免在索引列上使用内置函数

使用索引列上内置函数，索引失效。

mysql优化之表碎片

表碎片的产生的原因是经常进行删除插入更改等操作，每当删除了一行内容，这段空间就会变空白，日复一日的大量操作就会让这种空白占用更大的空间。
当执行插入操作时，Mysql会尝试使用空白空间，但如果某个空白空间一致没被合适的数据占用，该空白就会一直留着。
表碎片会存在于表空间内，每当mysql要从磁盘读取数据时，就会需要跨越额外的空白碎片才能取到需要的数据，即会造成额外的IO支出。
所以，定期清理表碎片是很有必要的。不过主要注意的是：清理表碎片的过程会锁表，尽量选在业务低峰期进行。mysql官方建议每周或者每月整理一次即可。

使用【OPTIMIZE TABLE 你的表】，以下简称‘OPT’

使用OPT出现阻塞其它sql执行，业务高峰期执行会导致查询失败，或者接口返回失败
OPT会深度清理表空间以及表的碎片，将之前删除数据占用的空间和索引重新整理
OPT命令在表数据千万级别时严重耗时，建议档表数据量小的时候整理
2、使用【ALTER TABLE 你的表 ENGINE=INNODB;】
此命令不会阻塞其它sql
此命令不会深度清理碎片，但是可以基本达到OPT命令一样的效果
以六千万级别的数据为例，耗时大概在4小时左右
可以重复执行，重复执行会重新清理表碎片