MySQL数据库 常用SQL优化技巧

将从以下几个方面介绍常用的SQL优化技巧：

• 避免在 WHERE 子句中使用 != 或  <> 操作符。
• 避免在 WHERE 子句中对索引列使用  %前缀模糊查询。
• 避免在 WHERE 子句中对索引列使用 OR 来连接条件。
• 避免在 WHERE 子句中对索引列使用 IN 和 NOT IN。
• 避免在 WHERE 子句中对索引列使用计算、函数、类型转换等操作。
• 避免在 WHERE 子句中对索引列使用参数。
• 使用合理的分页方式以提高分页的效率。
• 使用 EXISTS 替换 DISTINCT 。
• 避免在 WHERE 子句中对索引列进行 NULL 值判断。
• 避免在 WHERE 子句中对索引列进行 隐式类型转换。
• 合理使用 复合索引。

善用EXPLAIN

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

Explain语法

如下

执行计划包含如下信息：

• type：连接类型。一般来说，需要保证查询至少达到 range 级别，最好能达到 ref，杜绝出现 all 级别。
• key：实际使用的索引，如果没有可用的索引，则显示为NULL，可以使用force index强制索引方式。
• key_len:  索引字段的最大可能长度，理论上长度越短越好，但并非实际使用长度。
• rows:  表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，ROWS值的大小是个统计抽样结果，并不十分准确。
• extra:  额外说明，当出现Using filesort, Using temporary的时候需要注意。

避免在 WHERE 子句中使用 != 或 <> 操作符

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

避免在WHERE 子句中对索引列使用 %前缀模糊查询

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

避免在WHERE 子句中对索引列使用 OR 来连接条件

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

尽量UNION ALL 代替 UNION ，UNION 和 UNION ALL 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，UNION ALL的前提条件是两个结果集没有重复数据。

避免在WHERE 子句中对索引列使用 IN 和 NOT IN

应尽量避免在 WHERE 子句中使用 IN 和 NOT IN ，否则将导致全表扫描，对于连续的数值，能用 BETWEEN AND 尽量避免使用 IN。一般，用 EXISTS 代替 IN 。若需要使用 IN，在 IN 后面值的列表中，按照值的分布数量降序排列，减少判断的次数。

尝试使用BETWEEN AND 替换 IN 。

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

尝试使用LEFT JOIN 替换 IN。

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

• 使用 between 替换 in ( 如果 in 的条件是连续的)
• 使用 exists 替代 in、用not exists替代 not in
• 使用 left join 替换 in 。

避免在WHERE 子句中对索引列使用计算、函数、类型转换等操作

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

避免在WHERE 子句中对索引列使用参数

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

使用合理的分页方式以提高分页的效率

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

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

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

使用EXISTS 替换 DISTINCT

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

避免在WHERE 子句中对索引列进行 NULL 值判断

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

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

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

• 空值 代表杯子是真空的。
• NULL 代表杯子中装满了空气。

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

• 查询条件就必须处理为空的情况，否则会出现一些很奇怪的问题，比如 NOT IN、!= 等负向条件查询在有 NULL 值的情况下返回永远为空结果，查询容易出错。
• 在部分数据库中会导致索引失效。
• 可空列需要更多的存储空间，导致空间变大，进而导致数据库系统查询分析变的复杂。
• 在程序中也需要每次都判断是不是空，导致程序复杂了。

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

避免在WHERE 子句中对索引列进行隐式类型转换

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

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

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

合理使用复合索引

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

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

在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

同时，复合索引的生效原则是从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用，造成断点的原因一般有：

• 前边的任意一个索引没有参与查询，后面的不生效。
• 前边的任意一个索引失效，当前索引及后面全部不生效。
• 前边的任意一个索引字段参与的是范围查询，后面的不生效。

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

• 索引列进行计算、函数、类型转换等操作。
• 索引列使用不等于，如 != 或 <> 。
• 索引列使用 IS NULL ，IS NOT NULL。
• 模糊查询LIKE 以通配符开头如 ，%str。
• 索引列使用使用 OR 来连接条件。
• 索引列使用 NOT IN 。
• 类型错误，如字段 NUM 类型 为varchar，WHERE 条件用 number，NUM = 1。
• WHERE子句和 ORDER BY使用相同的索引，并且 ORDER BY的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者降序，否则不会使用索引。
• 复合索引不符合，最佳左前缀原则或存在断点。