MySQL查询优化

---

1、开启MySQL查询的缓存功能（Query Cache）

通过show variables like "%query_cache%"查看与查询缓存相关的参数：

mysql> show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 1024    |
| query_cache_size             | 3145728 |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.02 sec)

• have_query_cache
  当前版本的MySQL是否支持查询缓存，该变量是系统只读变量，无法修改。
• query_cache_type
  查询缓存的类型：0 -– 不启用查询缓存; 1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集都会缓存起来，共其他客户端使用。
• query_cache_size
  查询缓存的大小，也就是分配多大内存给查询缓存使用。如果设置为0，则无论query_cache_type设置为多少都不起作用。
• query_cache_limit
  查询缓存最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置。
• query_cache_min_res_unit
  查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K。
• query_cache_wlock_invalidate 　　
  该参数主要涉及MyISAM引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认设置为0，也即允许。

但Query Cache有如下规则，如果数据表被更改，那么和这个数据表相关的全部Cache全部都会无效，并删除。这里“数据表更改”包括: INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等。如果你的数据表更新频繁的话，那么Query Cache将会成为系统的负担。如果你的应用对数据库的更新很少，那么Query Cache将会作用显著。

参考：理解MySQL数据库查询缓存以及Query Cache，看上去很美

---

使用EXPLAIN 分析SELECT 查询

在任意的SELECT查询语句的前面加上EXPLAIN这个词，就可以分析MySQL在执行该语句时的具体信息：

# 示例1
mysql> explain select 1\G
*************************** 1. row ***************************
id           : 1
select_type  : SIMPLE
table        : NULL
type         : NULL
possible_keys: NULL
key          : NULL
key_len      : NULL
ref          : NULL
rows         : NULL
Extra        : No tables used
1 rows in set (0.05 sec)

# 示例2
mysql> explain select dept_name from hr_department d left join hr_person p on p.dept_id=d.dept_id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| 1  | SIMPLE      | d     | ALL  | NULL          | NULL | NULL    | NULL | 541  | NULL                                               |
| 1  | SIMPLE      | p     | ALL  | NULL          | NULL | NULL    | NULL | 561  | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.05 sec)

# 示例3
mysql> explain select dept_name from hr_department union  select dept_name from hr_department_copy;
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
| id | select_type  | table              | type | possible_keys | key  | key_len | ref  | rows | Extra           |
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
| 1  | PRIMARY      | hr_department      | ALL  | NULL          | NULL | NULL    | NULL | 541  | NULL            |
| 2  | UNION        | hr_department_copy | ALL  | NULL          | NULL | NULL    | NULL | 540  | NULL            |
| NULL | UNION RESULT | <union1,2>         | ALL  | NULL          | NULL | NULL    | NULL | NULL | Using temporary |
+----+--------------+--------------------+------+---------------+------+---------+------+------+-----------------+
3 rows in set (0.08 sec)

在EXPLAIN的结果行是以MySQL实际执行的查询部分的顺序出现的。

各参数含义：

• id
  表示在本次查询语句中该SELECT的执行序号：①id相同，执行顺序由上到下；②如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；③id如果相同，可以认为是一组，同组内从上往下顺序执行，不同组之间，id值越大，优先级越高，越先被执行。
• select_type
  表示在本次查询语句中该SELECT是简单类型还是复杂类型，如果是简单类型，则为SIMPLE（不包括子查询和UNION）。如果是复杂类型，则最外层标记为PRIMARY，其他部分标记如下：
  • SUBQUERY：表示不在FROM子句中的子查询
  • DERIVED：表示在FROM子句中的子查询
  • UNION：表示在UNION中的第二个以及随后的SELECT
  • UNION RESULT：表示在UNION的临时表检索结果的SELECT
    除次之外，SUBQUERY和UNION还可以标记为DEPENDENT（意味着SELECT依赖与外层查询中发现的数据）和UNCACHEABLE。
• table
  表示该SELECT对应的表，即对应行正在访问哪个表。
• type
  访问类型，即MySQL如何查找表中的行。从最差到最优的顺序为：
  • ALL：全表扫描，按从第一行到最后一行的顺序去查找需要的行。
  • index：与全表扫描一样，只是MySQL扫描表时按照索引次序进行而不是行。
  • range：范围扫描，就是一个有限制的索引扫描，这比全索引扫描要好，因为不用遍历全部索引，主要是带有BETWEEN和WHERE子句里含有>的查询。
  • ref：索引访问，也叫索引查找，它返回所有匹配某个单个值的行，但有可能会返回多个符合条件的行，因此，它是查找和扫描的结合体，只有使用非唯一索引或者唯一索引的非唯一性前缀时才会发生。ref_or_null是ref的变种，它表示MySQL必须在初次查找的结果里进行第二次查找以找出NULL条目。
  • eq_ref：使用这种索引查找，意味着MySQL知道最多只返回一条符合条件的记录。MySQL使用主键或者唯一索引查找时会看到该类型。
  • const, system：当MySQL能对查询的某部分进行优化并将其转换为一个常量时，就会使用该访问类型。
  • NULL：这种访问类型意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。例如，从一个索引列里选取最小值就可以通过单独查找索引来完成，不需要再去访问表。
    SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
• possible_keys
  表示查询可以使用哪些索引，这是基于访问的列和使用的比较操作符来判断的，在后续优化中可能并不会实际用到这里列出的索引。
• key
  表示MySQL实际上采用了哪个索引。如果该索引没有出现在possible_keys中，那么MySQL可能选择了一个覆盖索引。
• key_len
  表示MySQL在索引里使用的字节的长度。
• ref
  表示之前的表在key列记录的索引中查找值所用的列或常量。
• rows
  表示MySQL为了找到所需要的行而读取的行数。
• Extra
  这一列包含的是不适合在其他列显示的额外信息，主要如下：
  • Using index：表示MySQL将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
  • Using where：表示MySQL将在存储引擎检索行后再进行过滤，不是所有带有WHERE子句的查询都会显示Using where。
  • Using temporary：表示MySQL在对查询结果排除时会使用一个临时表。
  • Using filesort：表示MySQL会对结果使用一个外部索引排序，而不是按照索引次序从表里读取行。

参考：《高性能MySQL》

---

创建（正确）索引

索引并不一定就是给主键或是唯一的字段。如果在你的表中，有某个字段你总要会经常用来做搜索，那么，请为其建立索引吧。
一些注意点：
①在Join表的时候使用相当类型的例，并将其索引

SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = xxx

此时，两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
②业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
③在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度即可。
④根据最左原则，不要使用%xxx%或%xxx的形式，而是xxx%，只有这个才会使用索引（当前前提是该字段创建了索引）。
⑤利用覆盖索引来进行查询操作，避免回表。能够建立索引的种类:主键索引、唯一索引、普通索引，而覆盖索引是一种查询的一种 效果，用explain的结果，extra列会出现:using index。
⑥建组合索引的时候，区分度最高的在最左边。

参考：阿里巴巴Java开发手册v1.2.0

---

其他

• 避免 SELECT *的出现
• 尽可能的使用 NOT NULL，除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持 NOT NULL。

NULL columns require additional space in the row to record whether their values are NULL. 
For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

• 在某些情况下，用EXISTS替代IN、用NOT EXISTS替代NOT IN：

①select * from A where exists (select * from B where B.id = A.id);
②select * from A where A.id in (select id from B);
①的执行可以表述为
对外表A中所有记录进行循环，每次循环中对内表B进行查询（如果当前行符合子条件，则选出），主要使用B中的索引。

• 如果查询的两个表大小相当，那么用in和exists差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用exists，
  子查询表小的用in。
• 无论那个表大，用not exists都比not in要快。

参考：数据库性能优化之SQL语句优化、深入研究mysql exists与in的性能及效率

---

全文思路参考
MYSQL性能优化的最佳20+条经验
20180226--更新对explain中id的解释