MySQL 查询优化

1. 数据库性能参数

查询 mysql 数据库的一些运行状态

show status; 

查看操作次数

show status like 'Com_(CRUD)';

查看 mysql 数据库启动多长时间，myisam 存储引擎长时间启动需要进行碎片整理

show status like 'uptime';

查看慢查询

show status like 'slow_queries';

查询慢查询时间

show variables like 'long_query_time';

设置慢查询时间

set long_query_time = 0.5;

2. 查询优化

2.1 查看 SQL 执行计划

使用命令：

EXPLAIN/DESCRIBE/DESC 
SELECT * FROM...;

2.2 每个字段说明

id：SELECT 标识符。这是 SELECT 的查询序列号。

select_type：表示 SELECT 语句的类型。它可以是以下几种取值：

SIMPLE：表示简单查询，其中不包括连接查询和子查询；
PRIMARY：表示主查询，或者最外层的查询语句；
UNION：表示连接查询的第 2 个或后面的查询语句；
DEPENDENT UNION：连接查询中的第 2 个或后面的 SELECT 语句，取决于外面的查询；
UNION RESULT：连接查询的结果；
SUBQUERY：子查询中的第一个 SELECT 语句；
DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询；
DERIVED：导出表的 SELECT (FROM 语句的子查询）。

table：表示查询的表。

type：（★重要★）表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型：

该表仅有一行的系统表。这是 const 连接类型的一个特例，平时不会出现，这个也可以忽略不计
const：
数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的査询优化中作为常量对待。const 表查询速度很快，因为它们只读取一次。const 用于使用常数值比较 PRIMARY KEY 或 UNIQUE 索引的所有部分的场合。在下面查询中，tb1_name 可用 const 表：
```
SELECT  *  from tb1_name WHERE primary_key=1;

SELECT * from tb1_name WHERE primary_key_part1=1 AND primary_key_part2=2
```
eq_ref
mysql 手册是这样说的:”对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了 const 类型。它用在一个索引的所有部分被联接使用并且索引是 UNIQUE 或 PRIMARY KEY”。eq_ref 可以用于使用=比较带索引的列。
在下面例子中，MySQL 可以使用 eq_ref 来处理 ref_tables：
```
 SELECT * FROM ref_table,other_table WHERE ref_table.key_cloumn = other_table.cloumn;
    SELECT * FROM ref_table, other_tbale WHERE ref_table.key_cloumn_part1 = other_table.cloumn AND ref_table.key_cloumn_part2 = 1;
```
ref
对于来自前面的表的任意组合，将从该表中读取所有匹配的行。这种类型用于索引既不是 UNIQUE 也不是 PRIMARY KEY 的情况，或者查询中使用了索引列在左子集，既索引中左边的部分列组合。ref 可以用于使用=或者<=>操作符的带索引的列。
以下的几个例子中，mysql 将使用 ref 来处理 ref_table：
```
 select * from ref_table where key_column=expr; 
 select * from ref_table,other_table where ref_table.key_column=other_table.column; 
 select * from ref_table,other_table where ref_table.key_column_part1=other_table.column and ref_table.key_column_part2=1;
```
ref_or_null
这种连接类型类似 ref，不同的是 mysql 会在检索的时候额外的搜索包含 null 值的记录。在解决子查询中经常使用该链接类型的优化。
在以下的例子中，mysql 使用 ref_or_null 类型来处理 ref_table：
```
 select * from ref_table where key_column=expr or key_column is null;
```
上面这五种情况都是很理想的索引使用情况。
index_merge
该链接类型表示使用了索引合并优化方法。在这种情况下，key 列包含了使用的索引的清单，key_len 包含了使用的索引的最长的关键元素。

unique_subquery

该类型替换了下面形式的 IN 子查询的 ref：

value in (select primary_key from single_table where some_expr)

index_subquery
这种连接类型类似 unique_subquery。可以替换 IN 子查询，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：
```
value in (select key_column from single_table where some_expr)
```
range
只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引。ken_len 包含所使用索引的最长关键元素。当使用 =, <>, >,>=, <, <=, is null, <=>, between, 或 in 操作符，用常量比较关键字列时，类型为 range。下面介绍几种检索制定行的情况：
```
select * from tbl_name where key_column = 10; 
select * from tbl_name where key_column between 10 and 20; 
select * from tbl_name where key_column in (10,20,30); 
select * from tbl_name where key_part1= 10 and key_part2 in (10,20,30);
```
index
连接类型跟 ALL 一样，不同的是它只扫描索引树。它通常会比 ALL 快点，因为索引文件通常比数据文件小。
ALL （性能最差）
对于前面的表的任意行组合，进行完整的表扫描。如果第一个表没有被标识为 const 的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免 ALL。

possible_keys

possible_keys 字段是指 MySQL 在搜索表记录时可能使用哪个索引。如果这个字段的值是 NULL，就表示没有索引被用到。这种情况下，就可以检查 WHERE 子句中哪些字段哪些字段适合增加索引以提高查询的性能。创建一下索引，然后再用 explain 检查一下。

key

key 字段显示了 MySQL 实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是 NULL。想要让 MySQL 强行使用或者忽略在 possible_keys 字段中的索引列表，可以在查询语句中使用关键字 force index, use index 或 ignore index。参考 SELECT 语法。

key_len

key_len 字段显示了 mysql 使用索引的长度。当 key 字段的值为 NULL 时，索引的长度就是 NULL。注意，key_len 的值可以告诉你在联合索引中 MySQL 会真正使用了哪些索引。

ref

表示使用哪个列或常数与索引一起来查询记录。

rows

显示 MySQL 在表中进行查询时必须检查的行数。

Extra

本字段显示了查询中 mysql 的附加信息。以下是这个字段的几个不同值的解释：

distinct
MySQL 当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。
not exists
MySQL 在查询时做一个 LEFT JOIN 优化时，当它在当前表中找到了和前一条记录符合 LEFT JOIN 条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：
select * from t1 left join t2 on t1.id=t2.id where t2.id is null;
假使 t2.id 定义为 not null。这种情况下，MySQL 将会扫描表 t1 并且用 t1.id 的值在 t2 中查找记录。当在 t2 中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是 null，就不会再在 t2 中查找相同 id 值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql 只需要在 t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。
range checked for each record (index map: #)
MySQL 没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。
using filesort
MySQL 需要额外的做一遍从已排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 where 条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。
using index
字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
using temporary
MySQL 需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了 group by 和 order by 子句，它以不同的方式列出了各个字段。
using where
where 子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表中的所有记录，否则的话当查询的 extra 字段值不是 using where 并且表连接类型是 all 或 index 时可能表示有问题。
Using sort_union(…), Using union(…), Using intersect(…)
这些函数说明如何为 index_merge 联接类型合并索引扫描
Using index for group-by
类似于访问表的 Using index 方式,Using index for group-by 表示 MySQL 发现了一个索引,可以用来查询 GROUP BY 或 DISTINCT 查询的所有列,而不要额外搜索硬盘访问实际的表。

如果你想要让查询尽可能的快，那么就应该注意 extra 字段的值为 using filesort 和 using temporary 的情况。说明 SQL 需要优化了。

2.3 使用索引查询需要注意

索引可以提供查询的速度，但并不是使用了带有索引的字段查询都会生效，有些情况下是不生效的，需要注意。

2.3.1 使用 LIKE 关键字的查询

在使用 LIKE 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

2.3.2 使用联合索引的查询

MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段。对于联合索引，只有查询条件中使用了这些字段中第一个字段时，索引才会生效。

2.3.3 使用 OR 关键字的查询

查询语句的查询条件中只有 OR 关键字，且 OR 前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。

2.4 子查询优化

MySQL 从 4.1 版本开始支持子查询，使用子查询进行 SELECT 语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的 SQL 操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL 需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

优化：

可以使用连接查询（JOIN）代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

3. 数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

3.1 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

3.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。

通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

3.3 增加冗余字段

设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：

冗余字段的值在一个表中修改，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

4. 插入数据的优化

插入数据时，影响插入速度的主要是索引、唯一性校验、一次插入的数据条数等。

插入数据的优化，不同的存储引擎优化手段不一样，在 MySQL 中常用的存储引擎有，MyISAM 和 InnoDB，两者的区别：

参考：http://www.cnblogs.com/panfeng412/archive/2011/08/16/2140364.html

4.1 MyISAM

4.1.1 禁用索引

对于非空表，插入记录时，MySQL 会根据表的索引对插入的记录建立索引。如果插入大量数据，建立索引会降低插入数据速度。

为了解决这个问题，可以在批量插入数据之前禁用索引，数据插入完成后再开启索引。

禁用索引的语句：

ALTER TABLE table_name DISABLE KEYS

开启索引语句：

ALTER TABLE table_name ENABLE KEYS

对于空表批量插入数据，则不需要进行操作，因为 MyISAM 引擎的表是在导入数据后才建立索引。

4.1.2 禁用唯一性检查

唯一性校验会降低插入记录的速度，可以在插入记录之前禁用唯一性检查，插入数据完成后再开启。

禁用唯一性检查的语句：

SET UNIQUE_CHECKS = 0; 

开启唯一性检查的语句:

SET UNIQUE_CHECKS = 1;

4.1.3 批量插入数据

插入数据时，可以使用一条 INSERT 语句插入一条数据，也可以插入多条数据。

第二种方式的插入速度比第一种方式快。

4.1.4 使用 LOAD DATA INFILE

当需要批量导入数据时，使用 LOAD DATA INFILE 语句比 INSERT 语句插入速度快很多。

4.2 InnoDB

4.2.1 禁用唯一性检查

用法和 MyISAM 一样。

4.2.2 禁用外键检查

插入数据之前执行禁止对外键的检查，数据插入完成后再恢复，可以提供插入速度。

禁用：

SET foreign_key_checks = 0;

开启：

SET foreign_key_checks = 1;

4.2.3 禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成后再恢复，可以提高插入速度。

禁用：

SET autocommit = 0;

开启：

SET autocommit = 1;

5. 服务器优化

5.1 优化服务器硬件

服务器的硬件性能直接决定着 MySQL 数据库的性能，硬件的性能瓶颈，直接决定 MySQL 数据库的运行速度和效率。

需要从以下几个方面考虑：

1、配置较大的内存。足够大的内存，是提高 MySQL 数据库性能的方法之一。内存的 IO 比硬盘快的多，可以增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘的 IO。

2、配置高速磁盘，比如 SSD。

3、合理分配磁盘 IO，把磁盘 IO 分散到多个设备上，以减少资源的竞争，提高并行操作能力。

4、配置多核处理器，MySQL 是多线程的数据库，多处理器可以提高同时执行多个线程的能力。

5.2 优化 MySQL 参数

通过优化 MySQL 的参数可以提高资源利用率，从而达到提高 MySQL 服务器性能的目的。

MySQL 的配置参数都在 my.conf 或者 my.ini 文件的 [mysqld] 组中，常用的参数如下：

6. 优化 LIMIT 分页

在分页偏移量很大的时候，如 LIMIT 10000,20 这样的查询，MySQL 需要查询 10020 条记录然后只返回最后 20 条，前面 10000 条记录都被抛弃，这样代价非常高。

优化的最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列，对于偏移量很大的时候，这样做的效率回提升很大，如下：

SELECT file_id, description FROM film ORDER BY title LIMIT 50, 5;

修改为：

SELECT film.film_id, film.description FROM film 

INNER JOIN (SELCT film_id FROM film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);

这里“延迟关联”将大大提升查询效率，它让 MySQL 扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询需要的所有列。

文档信息

本文作者：Zhang jinmiao
本文链接：http://zhangjinmiao.github.io/mysql/2018/04/03/Mysql%E6%9F%A5%E8%AF%A2%E4%BC%98%E5%8C%96.html
版权声明：自由转载-非商用-非衍生-保持署名（创意共享3.0许可证）

山川尽美