Mysql优化11式

拼搏现实的明天。 2024-04-20 08:15 0阅读 0赞

原文链接：[https://www.toutiao.com/a6735074328859116035/?tt\_from=weixin&utm\_campaign=client\_share&wxshare\_count=1×tamp=1568339102&app=news\_article&utm\_source=weixin&utm\_medium=toutiao\_ios&req\_id=201909130945010100140400872B8E493F&group\_id=6735074328859116035][https_www.toutiao.com_a6735074328859116035_tt_from_weixin_utm_campaign_client_share_wxshare_count_1_tamp_1568339102_app_news_article_utm_source_weixin_utm_medium_toutiao_ios_req_id_201909130945010100140400872B8E493F_group_id_6735074328859116035]

本文中所提到的SQL优化技巧均是基于Mysql 索引 BTree类型 。将从以下几个方面介绍常用的SQL优化技巧：

本文篇幅较长 ，建议先收藏再阅读，便于后续查阅。

通常，我们在写完较为复杂的 SQL 时，一般会进行一下 MySQL 优化，我们要善用 EXPLAIN 查看 SQL 执行计划。

**Explain语法**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70][]

如下

![20190914210917278.png][]

执行计划包含如下信息：

*  应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描。
 *  MySQL 只有对以下操作符才会使用索引：<，<=，=，>，>=，BETWEEN，IN，以及使用 LIKE 时的 后缀模糊查询 % 。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 1][]

*  WHERE 子句中使用 LIKE进行模糊查询时，使用 %前缀模糊查询无法使用索引，从而引发全表扫描。解决 %前缀模糊查询时索引不被使用的方法就是添加覆盖索引（只访问索引的查询，索引和查询列一致，只需扫描索引而无须回表）。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 2][]

*  应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。使用 OR 的字句可以分解成多个查询，并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关，如果查询需要用到复合索引，用 UNION ALL 执行的效率更高。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 3][]

*  尽量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，UNION ALL的前提条件是两个结果集没有重复数据。
 *  应尽量避免在 WHERE 子句中使用 IN 和 NOT IN ，否则将导致全表扫描，对于连续的数值，能用 BETWEEN AND 尽量避免使用 IN。一般，用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，按照值的分布数量降序排列，减少判断的次数。
 *  尝试使用BETWEEN AND 替换 IN 。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 4][]

*  我们使用 EXISTS 替代 IN，用 NOT EXISTS 替代 NOT IN，无论在哪种情况下， NOT IN 效率都是最低的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 5][]

*  尝试使用LEFT JOIN 替换 IN。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 6][]

如上，我们使用了如下方式优化了 IN 和 NOT IN：

*  应尽量避免在 WHERE 子句中对 “=” 左边的字段进行函数、算术运算及其他表达式运算，可以将表达式运算移至“=”右边，否则将导致引擎放弃使用索引而进行全表扫描。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 8][]

*  如果在 WHERE 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项，可以改为强制查询使用索引。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 9][]

*  分页查询在我们的实际应用中非常普遍，也是最容易出问题的查询场景。比如对于下面简单的语句，一般想到的办法是在name，age，register\_time字段上创建复合索引。这样条件排序都能有效的利用到索引，性能迅速提升。

![2019091421321598.png][]

如上例子，当 LIMIT 子句变成 “LIMIT 100000, 50” 时，此时我们会发现，只取50条语句为何会这么慢?

原因很简单，MySQL并不知道第 100000条记录从什么地方开始，即使有索引也需要从头计算一次，因此会感觉非常的慢，一般我们在做翻页时，是可以获取上一页中的某个数据标志来缩小查询范围的，比如时间，可以将上一页的最大值时间作为查询条件的一部分，SQL可以优化为这样：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 10][]

EXISTS语句用来判断()内的表达式是否存在返回值，如果存在就返回 True，如果不存在就返回 False，同时它只要括号中的表达式有一个值存在，就立刻返回 True ，而不用遍历表中所有的数据。因此 EXISTS 使查询效率更高。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 11][]

*  应尽量避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描，创建表时 NULL 是默认值，但大多数时候应该使用 NOT NULL，或者使用一个默认值，如 0 作为默认值。

例如，性别，使用1表示男，2表示女，0表示未知或者是用户没有选择，默认值设置为 0，因为大部分编程语言的数字类型的默认值0。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 12][]

空值和NULL是有区别的，以一个杯子为例：

如果字段允许为空，可能会有以下问题：

但凡事没有绝对的，使用默认值的思路可以解决很大一部分可为空的问题，但不是所有都需这样做，具体还是要根据具体业务进行分析。

*  当我们对不同类型的值进行比较的时候，为了使得这些数值可比较，MySQL会做一些隐式转化（Implicit type conversion）。

SQL查询语句的条件中字段赋值与字段定义类型不匹配是一种常见的错误用法。

![20190914213601243.png][]

如上，字段 account 的定义为 varchar 类型，在 WHERE 条件中 account 字段是数字型，两者数据类型不一样，这时是没法直接进行比较的，需要进行类型转换。**MySQL的策略是将表中 account 字段全部转换为数字型之后再比较，由于函数作用于表字段，引起索引失效，导致全表扫描**，正确的写法如下：

![2019091421370691.png][]

![20190914213752208.png][]

*  如果经常执行如上查询，那么建立三个单独索引 不如建立一个复合索引，因为三个单独索引通常数据库每次执行只能使用其中一个，虽然这样比不使用索引而进行全表扫描提高了很多效率，但使用复合索引因为索引本身就对应到三个字段上的，效率会有更大提高。

那么为什么数据库只支持一条查询语句只使用一个索引，简单的讲是**因为N个独立索引同时在一条语句使用的效果比只使用一个索引还要慢，开销太大**。

*  在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
 *  同时，复合索引的生效原则是从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用，造成断点的原因一般有：

引发索引失效，导致全表扫描的原因有：

例如我们建立了一个这样**复合索引key index （col1, col2, col3），那么其实相当于创建了（col1），(col1, col2)，(col1, col2, col3) 三个索引，即最佳左前缀特性**。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 13][]

*  当索引列有大量数据重复时，SQL查询可能不会去利用索引，并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的。如表中有“性别”字段，即使在“性别”字段建立索引也对查询效率起不了作用，**尽量不要对数据库中某个含有大量重复的值的字段建立索引。**
 *  **建立索引可以提高 SELECT 的效率，但 索引并不是越多越好**。索引同时也降低了 INSERT 及 UPDATE 的效率，因为 INSERT 或 UPDATE 时有可能会重建索引，所以怎样建索引需要慎重考虑，视实际应用情况而定。**同时，一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用的字段是否有建立索引的必要。**
 *  **对于数值字段，尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销**。**这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言，只需要比较一次就够了。**
 *  **对于字符型字段，尽量的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。**
 *  避免使用 select \* from table，用具体的字段列表代替“\*”，避免返回用不到的任何字段。
 *  尽可能的使用索引字段作为查询条件，尤其是聚簇索引，必要时可以通过index index\_name来强制指定索引，避免对大表查询时进行table scan，必要时考虑新建索引。要注意索引的维护，周期性重建索引，重新编译存储过程。

在新建临时表时，如果一次性插入数据量很大，那么可以使用 SELECT INTO 代替 CREAT TABLE，避免造成产生大量日志 ，以提高速度。**如果数据量不大，为了缓和系统表的资源，应先CREAT TABLE，然后INSER**T。

当服务器的内存够多时，配置 线程数量 = 最大连接数 + 5，使其发挥最大的效率。否则使用配置 线程数量 < 最大连接数启用SQL SERVER的线程池来解决，如果还是 线程数量 = 最大连接数+5，可能会严重的损害服务器的性能。

*  尽量避免向客户端返回大量结果数据，若数据量过大，应该考虑相应需求是否合理。尽量避免大事务操作，提高系统并发能力。

[https_www.toutiao.com_a6735074328859116035_tt_from_weixin_utm_campaign_client_share_wxshare_count_1_tamp_1568339102_app_news_article_utm_source_weixin_utm_medium_toutiao_ios_req_id_201909130945010100140400872B8E493F_group_id_6735074328859116035]: https://www.toutiao.com/a6735074328859116035/?tt_from=weixin&utm_campaign=client_share&wxshare_count=1&timestamp=1568339102&app=news_article&utm_source=weixin&utm_medium=toutiao_ios&req_id=201909130945010100140400872B8E493F&group_id=6735074328859116035
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/325488619f15458394c92b8d9f6ab5f8.png
[20190914210917278.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d068dc29baf84d8b813ff04e1ffb9f99.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f997bba7e3fd4529b188de96fcc495f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/981c9147c9c2496984bd09e5d3865890.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/41f57e0463ed43d79d2cec7ea92fa238.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/2a03959d52b9410a892bee1704391f6b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/21bd77cf7419474db6c2924fdce7a9e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/4d31a0fd46034c85bef2864a50456a16.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f0c8c6a574d1401cb9d7179327cb5e73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/20d17be2ff564ce89edcdee36e1f283b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d84cf0acaea744aeaa391a5e4522f74f.png
[2019091421321598.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/968082a8dd7441deb34f880765c0481f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/10e4a00b9c574370a31212a276ae203c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/e266be93384a4767a15fa24a6745bbbb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/d08c7e7403754670b4cad779497e3efa.png
[20190914213601243.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/b9193bd88c564518b092f4b9791ad332.png
[2019091421370691.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/7f978fe2596e401596ab43171544ea2c.png
[20190914213752208.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/809a6e8e40ab4ca28da4d64c75a4e384.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2wxODg0ODk1NjczOQ_size_16_color_FFFFFF_t_70 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/18/f5086bac01254871b40ba88b7b224257.png