MySQL性能优化

性能优化（Optimize）指的是在保证系统正确性的前提下，能够更快速响应请求的一种手段。而且有些性能问题，比如慢查询等，如果积累到一定的程度或者是遇到急速上升的并发请求之后，会导致严重的后果，轻则造成服务繁忙，重则导致应用不可用。它对我们来说就像一颗即将被引爆的定时炸弹一样，时刻威胁着我们。因此在上线项目之前需要严格的把关，以确保 MySQL 能够以最优的状态进行运行。

MySQL 的优化方案有哪些？

MySQL 数据库常见的优化手段分为三个层面：SQL 和索引优化、数据库结构优化、系统硬件优化。

SQL优化

加上LIMIT限制，避免查询不需要记录

　　MySQL客户端和服务器之间的通信是”半双工”的，意味着没法进行流量控制。也就是说当服务器开始响应客户端请求时，客户端必须完整地接收整个返回结果。在有必要的时候要在查询中加上LIMIT限制，避免查询不需要记录，不应该查询大量数据后再取需要的数据或者接受完几条结果后就“粗暴”地断开连接。

查询具体的字段而非全部字段

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，但 Join 语句并不会创建临时表，因此性能会更高。

尽量使用小表驱动大表

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where A.id in (select id from B);

select name from A exists (select name from B where B.Id = A.id);

内表数据大的使用exists，外表数据大的使用in;

exists表示存在，是对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

对于连续数值，使用BETWEEN不用IN，in可能会索引失效

使用in时，当IN的取值范围较大时可能会导致索引失效，走全表扫描

失效

排查慢SQL

如何排查慢查询？

慢查询：超过指定时间的SQL语句查询称为“慢查询”。
慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理。

慢查询日志指的是在 MySQL 中可以通过配置来开启慢查询日志的记录功能，超过 long_query_time 值的 SQL 将会被记录在日志中。我们可以通过设置“slow_query_log=1”来开启慢查询，它的开启方式有两种：

(1) 通过 MySQL 命令行的模式进行开启，只需要执行“set global slow_query_log=1”即可，然而这种配置模式再重启 MySQL 服务之后就会失效；
(2) 另一种方式可通过修改 MySQL 配置文件的方式进行开启，我们需要配置 my.cnf 中的“slow_query_log=1”即可，并且可以通过设置“slow_query_log_file=/tmp/mysql_slow.log”来配置慢查询日志的存储目录，但这种方式配置完成之后需要重启 MySQL 服务器才可生效。

需要注意的是，在开启慢日志功能之后，会对 MySQL 的性能造成一定的影响，因此在生产环境中要慎用此功能。

尽量使用主键查询和覆盖索引

平时在写SQL时应该尽量使用主键查询和覆盖索引，可以减少树的搜索次数也就是避免回表，从而提升查询性能，详细介绍请查看我的另一篇文章——回表和覆盖索引介绍

索引优化（避免索引失效）

应该在正确的字段上使用正确的索引，同时避免索引失效。可以查看我的另一篇博文——MySQL -- 索引

(1) 对于使用like的查询，查询如果是’%a'不会使用到索引 ,而 like 'a%'就会用到索引。最前面不能使用%和_这样的变化值;

(2) 应尽量避免在 where 子句中使用 != 或 < > 操作符，为这些操作符会导致查询引擎放弃索引而进行全表扫描。优化方法： key<>0 改为 key>0 or key<0。

(3) 在索引列上避免使用 IS NULL 或 IS NOT NULL操作，索引是不索引空值的。

补充解释:

Null值不存储在索引中，因此在索引列上带Is null 条件的查询不会使用索引，而是使用Table Access Full (全表扫描)操作解析查询语句。

如果在索引列上改条件为 Is Not Null ，因为索引列的所有非空值都存储在索引中，按道理也是可以走索引的。但是，为了解析查询语句，优化程序需要从索引中读取每一个值，在映射到表中索引返回的行。
在大多数情况下，执行全表扫描比为索引返回的所有值执行索引扫描（相关的Table Access By Index Rowid操作）效率更高。

例如：数字类型，判断大于0(a is not null改为a>0)，字符串类型设置一个默认值，判断是否等于默认值即可。

(4) 不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率。

(5) 字符串不加单引号索引失效;

说明:加了单引号后mysql会在底层对其进行隐式的类型转换(可能会自动转换为int型，使索引无效，产生全表扫描)。

(6)组合索引，如果没有使用第一列索引，索引失效；

(7) 在 MySQL 5.0 之前的版本应尽量避免在 where 子句中使用 or 来连接条件,因为当or语句前后没有同时使用索引，该索引失效而进行全表扫描，只有当or左右查询字段均为索引时，才会生效；

可以这样查询，将 or 用 union all 来替换：

select id from t where num=10 union all select id from t where num=20；

说明:在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并，简单来说就是把多条件查询，比如 or 或 and 查询的结果集进行合并交集或并集的功能，因此就不会导致索引失效的问题了。