转载自 MySQL架构及优化原理，文本对原博文就行了重新排版。

• MySQL逻辑架构整体分为三层：

  • 客户端：并非MySQL独有，注入：连接处理、授权认证、安全等功能均在这一层处理

  • 核心服务：包括查询解析、分析、优化、缓存、内置函数，所有跨存储引擎的功能也在这一层实现（存储过程、触发器、视图）

  • 存储引擎：负责MySQL中的数据存储和提取，与Linux下的文件系统类似，每种存储引擎都有其优势和劣势，中间的服务层通过API和存储引擎通信，这些API接口屏蔽不同存储引擎间的差异。

2. MySQL查询过程

MySQL查询过程，分为5步：

1. 客户端向MySQL服务器发送一条查询请求

2. 服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果，否则进入下一阶段

3. 服务器进行SQL解析、预处理、再由优化器生成对应的执行计划

4. MySQL根据执行计划，调用存储引擎的API来执行查询

5. 将结果返回给客户端，同时缓存查询结果

    

3. 客户端/服务端协议

MySQL客户端/服务端通信协议是半双工的，在任意时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接受完整个消息才能响应它，所以无法也无需将一个消息切换成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候需要设置max_allowed_packet参数，如果查询实在是太大，服务端会拒绝接受更多数据并抛出异常。

与之相反的是，服务器响应给用户用户的数据通常会很多，由多个数据包组成，但是当都武器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。

因此在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT * 以及加上LIMIT限制的原因之一。

4. 查询缓存

在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会检测这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。MySQL将缓存放在一个引用表（不是table，可以认识类似于一个HashMap的数据结构），通过一个哈希值索引，这个哈希值通过串本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（包括空格、注释等），都会导致缓存不会被命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果都不会被缓存。比如NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，这样的查询结果缓存起来没有任何的意义。

MySQL查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或者结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果缓存非常大或者碎片很多，这个操作就可能带来很呆的系统消耗，甚至导致系统僵死一会儿，而且查询缓存时对系统的额外消耗也不仅仅在写操作，读操作也不列外：

1. 任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

2. 如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗。

基于此，并不是什么情况下查询缓存都能提高系统性能，缓存和失效都会带来额外消耗，特别是写密集型应用，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。可以尝试打开查询缓存，并在数据库设计上做一些优化。

1. 用多个小表代替一个大表，注意不要过度设计。

2. 批量插入代替循环单条插入。

3. 合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适。

4. 可以通过SQL_CACHE 和 SQL_NO_CACHE 来控制谋和查询语句是否需要进行缓存。SQL_NO_CACHE 是禁止缓存查询结果，但并不意味着cache不能作为结果返回给query，之前的缓存结果之后也能查询到

可以在SELECT语句中指定查询缓存的选项，对于那些肯定要实时的从表中获取数据的查询，或者对于那些一天只执行一次的查询，都可以指定不进行查询缓存（即使用SQL_NO_CACHE）。对于那些变化不频繁的表，查询操作很固定，可以见查询操作缓存起来，这样每次执行的时候不实际访问表和查执行查询，只是从缓存获得结果，可以有效的改善查询的性能（即使用SQL_CACHE）。

查看开启的缓存情况：

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 1048576 |  # 给缓存分配的最大内存空间
| query_cache_type             | OFF     |  # 是否开启查询缓存，0 表示不开启查询缓存，1 表示始终开启查询缓存 (不要缓存使用 sql_no_cache)， 2 表示按需开启查询缓存 (需要缓存使用 sql_cache)
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

对于查询缓存的一些操作：

FLUSH QUERY CACHE：清理传缓存内存碎片

RESET QUERY CACHE：从查询缓存中移出所有查询

FLUSH TABLES： 关闭所有打开的表，同时该操作将会清空查询缓存中的内容

5.查询优化

经过前面的步骤生成的语法树被认为是合法的了，并且优化器将其转换成查询计划。多数情况下，一条查询有多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。

MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划是的成本，并选择其中成本最小的一个，在MySQL中可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本

mysql> SELECT * FROM p_product_fee WHERE total_price BETWEEN 580000 AND 680000;
mysql> SHOW STATUS LIKE 'last_query_cost'; # 显示要做多少页的随机查询才能得到最后一查询结果，这个结果是根据一些列的统计信息计算得来的，这些统计信息包括 : 每张表或者索引的页面个数、索引的基数、索引和数据行的长度、索引的分布情况等等

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确，不会考虑不受其控制的操作成本（用户自定义函数，存储过程）、MySQL认为的最优和我们所想的不一样（我们希望执行时间尽可能短，但是MySQL则选择它认为成本小的，但成本小并不意味着执行时间短等等。

MySQL的查询优化器是一个非常复杂的部件，它使用了非常多的优化策略来生成一个最优的执行计划：

1. 重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2. 优化MIN()和MAX()函数（查询某列的最小值，如果该列有索引，只需要查找B+树索引最左端，反之则可以找到最大值）

3. 提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）

4. 优化排序（在旧版本的MySQL中会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

    

6.查询系统性能

SHOW STATUS LIKE 'value';

 

value 参数的几个统计参数如下：

• Connections：连接MySQL服务器的次数

• Uptime：MySQL服务器的上线时间

• Slow_queries：慢查询次数

• Com_Select：查询操作次数

• Com_insert：插入操作的次数

• Com_update：更新操作的次数

• Com_delete：删除操作的次数

7.MySQL优化

7.1 分析查询语句

EXPLAIN、DESCRIBE用于分析SELECT语句执行情况

EXPLAIN SELECT 语句;
DESCRIBE SELECT 语句;

返回的查询结果信息：

• id：表示SELECT语句的编号

• select_type：表示SELECT语句类型。该参数有几个常用的取值，即SIMPLE表简单查询，其中不包括连接查询和子查询；PRIMARY表示主查询，或者是最外层的查询语句；UNION表示连接查询的第二个或者后面的查询语句

• table：表示查询的表

• type：表示表的连接类型。该参数有几个常用的取值，即system表示表中只有一条记录；const表示表中有多条记录，但值从表中查询一条记录；ALL表示对表进行完整的扫描；eq_ref表示多表连接时，后面的表示用UNIQUE或者PRIMARY KEY；ref表示多表查询时，后面的表示用普通索引

• unique_subquery：表示子查询中使用UNIQUE或者PRIMARY KEY；index_subquery表示子查询中使用普通索引；range表示查询语句中给出的查询范围。

• index：表示对表中的索引进行完整的扫描。

• possible_keys：表示查询中可能使用的索引。

• key：表示查询使用到的索引。

• key_len：表示索引字段的长度。

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

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

• Extra：表示查询过程的附加信息。

7.2 索引查询

1. 使用LIKE关键字查询时，若匹配字符串的第一个字符串为%时，索引不会被使用，如果不在第一个位置，索引就会被使用。

2. 多列索引是在表的多个字段上创建一个索引，只有查询条件中使用这些字段中第一个字段时，索引才会被使用。

3. 查询语句只有OR关键字时，若OR前后两个条件的列都是索引时，查询中将使用索引；若OR前后有一个条件的列不是索引，那么查询中将不会使用索引。

4. 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。

5. 使用多列索引时注意顺序和查询条件保持一致，同时删除不必要的单列索引。

6. 尽量避免在WHERE自居中使用 != 或 <>操作符，否则引擎将放弃使用索引而进行全表扫描。

7.3 优化子查询

子查询时，系统内层查询语句的查询结果建立一个临时表，然后外层查询语句再在临时表中查询记录，查询完成后撤销这些临时表，因此使用连接查询来代替子查询，这是由于连接查询不需要建立临时表，其速度比子查询要快。

1. 将字段很多的表分解为多个表：有些表设计了很多字段，其中有些字段使用频率低，当表数据量大时，查询数据的速度就会被拖慢，因此将哪些使用频率很低的字段放置字另外一个表中（另外一个表可以时 *_extract）

2. 增加中间表：在查询两个表的几个字段时，经常连表查询会降低数据库查询速度，可将这些字段建立在一个中间表并将原来的那几个表的数据插入到中间表中，之后使用中间表来进行查询和统计，以此提高查询速度。

3. 增加冗余字段：表的规范化程度越高，表与表之间的关系就越多，若经常进行夺标连接查询会浪费很多时间，可增加冗余字段的方式来提高查询速度。（关于冗余字段，原文作者并没有多做解释，我找到的一个解释文章 冗余字段 ）。

7.4 优化一行数据的查询

若已知查询、更新或者删除的结果只有一条，在查询、更新或者删除语句之后添加 LIMIT 1 可以在数据库引擎找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。

7.5 JOIN级联查询

使用JOIN级联查询时，应该保证两表中JOIn字段已经建立过索引且类型相同，这样MySQL内部会启动优化JOIN的SQL语句的机制，如：如果要把DECIMAL字段和一个INT字段JOIN在一起，MySQL就无法使用它们的索引。对于哪些STRING类型，还需要有相同的字符集才行。

7.6 避免SELECT查询数据

从数据库中读出越多的数据，那么查询就会变得越慢，并且，如果数据库服务器和WEB服务器是两*立的服务器的话，这还会增加网络传输的负载。所以，应该养成一个需要什么就取什么的好习惯。

7.7 ENUM与VARCHAR

能使用ENUM就不要使用VARCHAR，ENUM类型是非常快和紧凑的，实际上ENUM保存的是TINYINT，但其外表上显示为字符串。这样用这个字段来做一些选项列表变得相当的完美。

如果有一个字段，比如“性别”、“国家”、“民族”，这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR.

7.8 从PROCEDURE ANALYSE()取得建议

PROCEDURE ANALYSE()会让MySQL帮你取分析你的字段和其实际数据，并会给你一些有用的建议。只有表中有实际的数据，那些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。

SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE([max_elements,[max_memory]])
max_elements : (默认值256) analyze 查找每一列不同值时所需关注的最大不同值的数量，analyze 还用这个值来检查优化的数据类型是否该是 ENUM，如果该列的不同值的数量超过 max_elements值 ENUM 就不做为建议优化的数据类型
max_memory : (默认值8192) analyze 查找每一列所有不同值时可能分配的最大的内存数量

mysql> DESC user_account;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| USERID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| USERNAME  | varchar(10)      | NO   |     | NULL    |                |
| PASSSWORD | varchar(30)      | NO   |     | NULL    |                |
| GROUPNAME | varchar(10)      | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
​
mysql> select * from user_account PROCEDURE ANALYSE(1)G
*************************** 1. row ***************************
             Field_name: ibatis.user_account.USERID
              Min_value: 1
              Max_value: 103
             Min_length: 1
             Max_length: 3
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 51.7500
                    Std: 50.2562
      Optimal_fieldtype: TINYINT(3) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: ibatis.user_account.USERNAME
              Min_value: dfsa
              Max_value: LMEADORS
# analyze 分析 ibatis.user_account.USERID 列最小值1，最大值103，最小长度1，最大长度3等信息，并给出修改字段的优化建议 : 建议将该字段的数据类型改成 TINYINT(3) UNSIGNED NOT NULL

7.9 尽可能使用NOT NULL

NULL会占用额外空间来记录其值是否为空，对于MyISAM表（MyISAM是MySQL的一种引擎），每个空列都需要额外的一个字节并将其四舍五入到最近的字节。MySQL难以优化应用可空列查询，它会使索引、索引统计和值更加复杂，可空列被索引后，每条记录都需要一个额外的字节，还能导致MylSAM中固定大小的索引变为可变大小的索引。在进行比较的时候会使程序更复杂，其所代表的意义在不同的数据库中会有所不同（原博文中这里还有一句我没看懂到底要表述啥的话，因此被我删除了）

• 所有使用NULL值的情况，都可以通过设置一个有意义的值来代替，这样有利于代码的可读性和可维护性，并能从约束上增强业务数据的规范性。

• NULL值到非NULL值的更新无法做到原地更新，更容易发生索引断裂，从而影响性能。

  注：把NULL列改为NOT NULL带来的性能提升很小，除非确定它带来了问题，否则不要把它当成优先的优化措施，更重要的是使用的列的类型的适当性。

• NULL值在timestamp类型下容易出现问题，特别时没有启动参数explicit_for_timestamp。

• NOT IN、!=等负向条件查询在有NULL值的情况下返回永远为空结果，查询容易出错。

  mysql> CREATE TABLE table_2(id INT(11) NOT NULL,user_name VARCHAR(20) NOT NULL);
  mysql> CREATE TABLE table_3(id INT(11) NOT NULL,user_name VARCHAR(20));
  mysql> INSERT INTO table_2 VALUES(4,'zhaoliu_2_1'),(2,'lisi_2_1'),(3,'wangmazi_2_1'),(1,'zhangsan_2'),(2,'lisi_2_2'),(4,'zhaoliu_2_2'),(3,'wangmazi_2_2');
  mysql> INSERT INTO table_3 VALUES(1,"zhaoliu_2_1"),(2, null);
  ​
  # NOT IN子查询在有NULL值的情况下返回永远为空结果，查询容易出错
  mysql> SELECT user_name FROM table_2 WHERE user_name NOT IN(SELECT user_name FROM table_3 WHERE id!=1);
  Empty set (0.00 sec)
  ​
  # 单列索引不存 NULL值，复合索引不存全为 NULL值，如果列允许为 NULL，可能会得到“不符合预期”的结果集
  # 如果name允许为 NULL，索引不存储 NULL值，结果集中不会包含这些记录。所以，请使用 NOT NULL约束以及默认值
  mysql> SELECT * FROM table_3 WHERE user_name!='zhaoliu_2_1';
  Empty set (0.00 sec)
  ​
  # 如果在两个字段进行拼接 : 比如题号+分数，首先要各字段进行非null判断，否则只要任意一个字段为空都会造成拼接的结果为null
  mysql> SELECT CONCAT("1",null);
  +------------------+
  | CONCAT("1",null) |
  +------------------+
  | NULL             |
  +------------------+
  1 row in set (0.00 sec)
  ​
  # 如果有 NULL column 存在的情况下，count(NULL column)需要格外注意，NULL 值不会参与统计
  mysql> SELECT * FROM table_3;
  +----+-------------+
  | id | user_name   |
  +----+-------------+
  |  1 | zhaoliu_2_1 |
  |  2 | NULL        |
  +----+-------------+
  2 rows in set (0.00 sec)
  mysql> SELECT COUNT(user_name) FROM table_3;
  +------------------+
  | count(user_name) |
  +------------------+
  |                1 |
  +------------------+
  1 row in set (0.00 sec)
  ​
  # 注意 NULL 字段的判断方式，= NULL 将会得到错误的结果
  mysql> CREATE INDEX idx_test ON table_3(user_name);
  mysql> SELECT * FROM table_3 WHERE user_name IS NULL;
  +----+-----------+
  | id | user_name |
  +----+-----------+
  |  2 | NULL      |
  +----+-----------+
  1 row in set (0.00 sec)
  ​
  mysql> DESC SELECT * FROM table_3 WHERE user_name='zhaoliu_2_1';
  +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  | id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
  +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  |  1 | SIMPLE      | table_3 | NULL       | ref  | idx_test      | idx_test | 23      | const |    1 |   100.00 | NULL  |
  +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
  1 row in set, 1 warning (0.00 sec)
  ​
  mysql> DESC SELECT * FROM table_3 WHERE user_name=null;
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  |  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
  1 row in set, 1 warning (0.00 sec)
  ​
  mysql> DESC SELECT * FROM table_3 WHERE user_name IS NULL;
  +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
  | id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
  +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+