

技术大讲堂 | 大数据量下的MySQL优化

霆万平头哥

2023-08-25

导读：MYSQL还有一些高级的特性可以帮助优化我们的应用，比如分区、分表等。在平时工作中能够查阅相关的资料，并进行SQL调优，总结相关经验，不断优化，最终写出更加高质量的SQL语句。

整理 | bubble

出品 | 平头哥（ID：t-head）

在实际开发的项目中，随着项目的持续运行，数据表中的数据量会逐渐增加。那么如何在大数据量的情况下保持高效的查询，成为了技术人员和数据专业人士关注的焦点。

MySQL性能优化

接下来，我们会从慢查询分析、索引优化、SQL语句优化等方面与大家进行交流探讨，让系统的MySQL在面对巨大数据挑战时依然能够发挥出色的表现。

01 慢查询分析

分析慢查询步骤

开启：

可以在mysql.ini或者mysql.cnf配置文件中，加入相应的配置语句

slow_query_log=on;long_query_time=1

定位：

第一步开启慢日志后，当SQL语句触发了设置的慢查询时间，该SQL相应的信息会被写入慢查询日志，根据日志文件，找出慢查询的SQL语句。

分析：

使用EXPLAIN分析执行计划：对于发现的慢查询，使用EXPLAIN关键字来获取查询的执行计划，以便了解MySQL是如何处理该查询的。

慢查询分析语句

02 MySQL 索引优化

1. 索引的重要性

索引对于良好的性能非常关键，当数据表数据量越大时，索引对性能的影响愈发重要，在数据量较小时，不恰当的索引对性能影响可能还不明显。但随着数据量逐渐增大，性能则会急剧下降。对于我们开发人员而言，索引应该是对查询性能优化最有效的手段了。所以创建一个真正的“最优”索引，是我们开发人员必掌握的一项技能。

2. 索引的分类

普通索引：是最常见的索引类型，用于加速常用查询的执行。

唯一索引：用于确保索引列的值唯一，不允许有重复的值。

主键索引：唯一标识一行数据，可用于数据的唯一性和完整性。

组合索引：由多个列组成的索引，可用于加速多列查询的执行。

全文索引：用于支持搜索和排序等功能，可以用于大量文本数据的搜索和分析。

3. 常见的索引失效的场景

(1) 在普通索引中，查询的列不是独立的列时：

SELECT * FROM `fa_cms_archives` where channel_id + 1 = 6 limit 10000;

(2) 在普通索引中，类型隐式转换，在数据库中channel_ids字段为varchar类型，而sql语句中使用了int类型，导致走全表扫描：

SELECT * FROM `fa_cms_archives` where channel_ids=27 limit 10000;

(3) is null、is not null、!= 是日常使用较多的操作关键字，尽管 MySQL 官方文档已经明确说明is null并不会影响索引的使用。但我们遇到这几个关键字还是会让索引失效，比如：

select id,deletetime from fa_cms_archives where deletetime is null;

(4) 预期中希望语句能使用联合索引，但sql语句未满足最左侧字段优先匹配的原则：

SELECT * FROM `fa_cms_archives` where publishtime > 1683365282 limit 10000;

(5) 错误的Like使用使得索引失效。常见的like使用方式有：

方式一：like ‘%abc’；

方式二：like ‘abc%’；

方式三：like ‘%abc%’；

其中方式一和方式三，由于占位符出现在首部，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

03 MYSQL 查询性能优化

1. 查询语句的生命周期图解

当执行一条简单的sql语句

 select * from  `fa_cms_archives` where id=818;

MySQL内部的具体做了那些事情？

生命周期

2. 对SELECT* 保持怀疑

如果在我们应用程序中，看到SELECT * 时，我们应该带着怀疑的眼光，是不是真的有必要返回全部的列呢？返回所有列一方面会使优化器无法完成覆盖索引的优化，另一方面还会给服务器带来额外的I/O、内存与CPU的消耗。

从另外一个角度来说，SELECT* 返回超过需要的数据也不总是坏事，如果清楚该表的情况，比如表中数据量不大，SELECT* 对性能影响不大情况下，这样做可能会提高我们代码复用性，减少因为新添加字段可能带来的程序bug问题。甚至在清楚数据表大致的记录下，可以对SELECT* 的数据进行缓存，也是对程序接口性能的优化。

3. 覆盖索引

索引树图

覆盖索引是MySQL优化sql性能的一种非常重要而且常用的手段，通过覆盖索引，我们可以直接查询到需要的结果，而不用回表，从而大大减少树的搜索次数，非常明显的提升查询性能。比如语句：

SELECT channel_id FROM `fa_cms_archives` where channel_id in (25,26,27,28)

4. SQL语句切分

在大量清除数据场景下，如果用一个大语句一次性完成，可能会一次性锁住很多数据，沾满了整个事务日志，阻塞一些其他的重要查询。在这样的场景下我们可以将DELETE语句切分成多个语句，分批执行，尽可能的减少对其他查询的影响。

5. 分页优化

正常进行分页，我们可能用到的最终sql语句可能是这样：

select * from fa_cms_archives limit 100,10;

这样即使在特大表中查询的速度也是比较快的，但是如果修改一下偏移量，sql语句修改为这样：

select * from fa_cms_archives limit 10000000,100

这样，会明显的发现，执行查询语句的耗时会大幅度增加。

通常情况下，针对主键也就是我们的id如果是连续自增长情况下我们可以这么进行修改一下：

select * from fa_cms_archives where id between 10000000 and 10000100；

如果主键不是连续自增长情况下，可以做以下修改：

select * from fa_cms_archives where id > (select id from fa_cms_archives limit 10000000,1) limit 100

总结分析

创建一个高性能的应用程序，MySQL优化是开发人员绕不开的点。本文从慢查询定位，索引优化、SQL语句优化、一些特定场景下写SQL语句需要注意的点等方面进行了探讨。除了这些方面的优化外，MYSQL还有一些高级的特性可以帮助优化我们的应用，比如分区、分表等。希望大家能在平时工作中能够查阅相关的资料，并进行SQL调优，总结相关经验，不断优化，最终写出更加高质量的SQL语句。