一条sql执行慢的原因？如何优化？

折叠伞Y
120 阅读5分钟

一、sql为什么会变慢呢？

一个 SQL 执行的很慢，我们要分两种情况讨论：

1、大多数情况下很正常，偶尔很慢，则有如下原因

  • 数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
  • 执行的时候，遇到锁，如表锁、行锁。
  • sql写的烂

2、这条 SQL 语句一直执行的很慢，则有如下原因

  • 没有用上索引或者索引失效：例如该字段没有索引；或者由于对字段进行运算、函数操作导致无法用索引。
  • 有索引可能会走全表扫描

怎样判断是否走全表扫描：索引区分度（索引的值不同越多，区分度越高），称为基数，而数据量大时不可能全部扫描一遍得到基数，而是采样部分数据进行预测，那有可能预测错了，导致走全表扫描。

二、慢sql优化

数据库中设置SQL慢查询

方式一：修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）

[mysqlld]
//定义查过多少秒的查询算是慢查询，我这里定义的是2秒
long_query_time=2
#5.8、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query. log"
//记录下没有使用索引的query
log-query-not-using-indexestpspb16glos dndnorte/t

方式二：通过MySQL数据库开启慢查询:

mysql>set global slow_query_log=ON
mysql>set global long_query_time = 3600;
mysql>set global log_querise_not_using_indexes=ON;

分析慢查询日志

可以通过如下命令定位低效率执行sql：show processlist;

可以用 explain 分析执行计划。(对于执行计划的分析，也是面试官喜欢考察的一个点）

这里简单带过，之后会详细解答。主要关注这几个字段即可：

type：表示MySQL在表中找到所需行的方式，或者叫访问类型。

  • type=ALL，全表扫描，MySQL遍历全表来找到匹配行
  • type=index，索引全扫描
  • type=range，索引范围扫描
  • type=eq_ref，唯一索引
  • type=NULL，MySQL不用访问表或者索引，直接就能够得到结果（性能最好）
  1. possible_keys: 表示查询可能使用的索引
  2. key: 实际使用的索引
  3. key_len: 使用索引字段的长度
  4. rows: 扫描行的数量
  5. Extra：
    • using index：覆盖索引，不回表
    • using where：回表查询
    • using filesort：需要额外的排序，不能通过索引得到排序结果

慢sql如何优化？

对于MYSQL慢sql语句的优化，我们也可以分几个方面来进行分析（基本覆盖全面啦）：

面试从这几方面考虑：索引+sql语句+数据库结构优化+优化器优化+架构优化。

（一）索引

  1. 尽量覆盖索引，5.6支持索引下推
  2. 组合索引符合最左匹配原则
  3. 避免索引失效
  4. 在写多读少的场景下，可以选择普通索引而不要唯一索引。更新时，普通索引可以使用change buffer进行优化，减少磁盘IO,将更新操作记录到change bufer，等查询来了将数据读到内存再进行修改。
  5. 索引建立原则（一般建在where和order by，基数要大，区分度要高，不要过度索引，外键建索引）

（二）sql语句

  1. 分页查询优化

该方案适用于主键自增的表，可以把Limit查询转换成某个位置的查询。select * from tb_sku where id>20000 limit 10;

  1. 优化insert语句
    • 多条插入语句写成一条
    • 在事务中插数据
    • 数据有序插入（主键索引）

（三）数据库结构优化

  1. 将字段多的表分解成多个表

有些字段使用频率高，有些低，数据量大时，会由于使用频率低的存在而变慢，可以考虑分开。

  1. 对于经常联合查询的表，可以考虑建立中间表

（四）优化器优化

1、优化器使用MRR

原理： MRR 【Multi-Range Read】将ID或键值读到buffer排序，通过把「随机磁盘读」，转化为「顺序磁盘读」，减少磁盘IO，从而提高了索引查询的性能。

开启mrr：

image.png

对于 Myisam，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。

对于 Innodb，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。

磁盘预读： 请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取（局部性原理）

索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大

（五）架构优化

读/写分离（主库写，从库读）

（六）总结：

  1. 先设置慢查询（my.ini或数据库命令）
  2. 分析慢查询日志
  3. 定位低效率sql（show processlist）
  4. explain分析执行计划（是否索引失效，用到索引没，用了哪些）
  5. 优化（索引+sql语句+数据库结构优化+优化器优化+架构优化）

原文链接：字节面试官：一条sql执行慢的原因？如何优化？ (qq.com)

avatar
文章
阅读
粉丝