MySQL 基础架构
一条 SQL 查询语句在 MySQL 内的执行过程，是怎样的？
上图是 MySQL 的基本架构示意图。MySQL 大致可以分为两部分：Server 层 和 存储引擎层。
Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎。不同的存储引擎共用一个 Server 层。存储引擎向 Server 层提供统一的调用接口（存储引擎 API），包含了几十个底层函数，像"读取索引第一条内容"、“读取索引下一条内容”、“插入记录"等等。
从 MySQL 5.5.5 版本开始， InnoDB 成为了默认存储引擎。
接下来看一条 SQL 查询语句的执行过程，如 select * from T where id=10;。
连接器
第一步就是与服务端建立连接。连接器负责跟客户端建立连接、获取权限、维持和管理连接。
客户端可以采用 TCP/IP、命名管道或共享内存、Unix 域套接字这几种方式之一来与服务端建立连接。
连接命令：
mysql -h$ip -P$port -u$user -p连接建立后，连接器会进行身份认证，并获取权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。也就是说，一个用户成功建立连接后，修改权限对当前连接是无效的。重新建立连接才会生效。
每一个连接建立，服务器都会专门创建一个线程来处理与这个客户端的交互，断开连接时，服务器会先把这个线程缓存起来，以便复用。避免频繁创建和销毁线程。
MySQL 服务器线程分配的太多会影响系统性能，所以要限制连接数量。
连接完成后，如果没有后续的动作，这个连接就处于空闲状态，可以使用 show processlist 命令查看。Command 列显示为 Sleep 的，就是空闲连接。
客户端如果太长时间没动静，连接器就会自动将它断开。可以设置 wait_timeout 来控制，默认值是 8 小时。
连接被断开之后，再次发送请求，就会收到一个错误提醒：“Lost connection to MySQL server during query”。需要重连，再执行请求。
数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。
建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，尽量使用长连接。
长连接的弊端
全部使用长连接后，可能会发现，有些时候 MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。
怎么解决这个问题？可以考虑以下两种方案。
- 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
- 如果是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行
mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。
查询缓存
MySQL 处理查询请求时，会把刚刚处理过的查询请求和结果以 key-value 的形式，缓存在内存中。如果下一次有一模一样的请求过来，优先从缓存中查找结果。如果命中缓存，就不需要再执行后面的复杂操作，直接返回结果。
查询缓存可以在不同客户端之间共享。
查询缓存的弊端
- 两个查询请求在任何字符上的不同（例如：空格、注释、大小写），都会导致缓存不会命中。
- 如果查询请求中包含某些系统函数、用户自定义变量和函数、一些系统表，如
mysql、information_schema数据库中的表，那这个请求就不会被缓存。 - 查询缓存的失效非常频繁。只要表的结构或者数据被修改，如对表使用了
insert、update、delete、truncate table、alter table、drop table或drop database语句，那这个表上所有的查询缓存都会被清空。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务表，很长时间才会更新一次。
按需使用查询缓存
一般只有在静态表才建议使用查询缓存，静态表极少更新的表。比如，一个系统配置表、字典表，那这张表上的查询才适合使用查询缓存。MySQL 提供了“按需使用”的方式。可以将my.cnf 中的参数 query_cache_type 设置成 DEMAND。那么，默认的 SQL 语句不会使用查询缓存。而对于确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：
my.cnf：
# 0 代表关闭查询缓存 OFF
# 1 代表开启 ON
# 2（DEMAND）代表当 sql 语句中有 SQL_CACHE 关键词时才缓存
query_cache_type=2select SQL_CACHE * from T where ID=10;
-- 查看当前 mysql 实例是否开启缓存机制
show global variables like "%query_cache_type%";MySQL 在 8.0 中删除了查询缓存的功能。
分析器
如果查询缓存没有命中，接下来就需要真正执行查询语句。MySQL 首先要对查询语句的文本做分析。
分析器先会做词法分析。分析文本里面的字符串分别是什么，代表什么。比如 select 可以判断出是一个查询语句。把字符串 “T” 识别成 “表名 T”，把字符串 “id” 识别成 “列 id”。
接下来要做语法分析，根据词法分析的结果，分析器根据语法规则，判断这个 SQL 语句是否满足 MySQL 语法。如果语句不对，就会收到 “You have an error in your SQL syntax” 的错误提醒。
优化器
经过了分析器，MySQL 就知道你要做什么了，要查询的列是哪些，表是哪个，搜索条件是什么等等。在开始执行之前，还要先经过优化器的处理。因为我们写的 SQL 语句执行起来效率可能并不是很高，优化器会对语句做一些优化，如外连接转换为内连接、表达式简化、子查询转为连接、选择索引等等。
优化器会生成一个执行计划，这个执行计划表明了应该使用哪些索引进行查询，表之间的连接顺序是啥样的。
使用 EXPLAIN 语句可以查看某个语句的执行计划。
执行器
通过分析器知道了要做什么，通过优化器知道了该怎么做，接下来就进入了执行器阶段，开始执行语句。
开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限（经过分析器之后才知道要查询、修改的表，所以连接器无法验证表的权限），如果没有，就会返回没有权限的错误（如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证）。
打开表的时候，执行器就会根据表的引擎定义，调用这个引擎提供的接口。
存储引擎
MySQL 提供了多种存储引擎，不同存储引擎管理的表具体的存储结构可能不同，采用的存取算法也可能不同。
常用存储引擎
| 存储引擎 | 描述 |
|---|---|
| InnoDB | 具备外键支持功能的事务存储引擎 |
| Memory | 置于内存的表 |
| MyISAM | 主要的非事务处理存储引擎 |
默认的存储引擎是 InnoDB。
查看当前服务器程序支持的存储引擎：
SHOW ENGINES;
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql>Support表示该存储引擎是否可用，如果值为DEFAULT则表示是默认的存储引擎。Transactions表示该存储引擎是否支持事务处理。XA表示着该存储引擎是否支持分布式事务。Savepoints表示着该列是否支持部分事务回滚。
Server 层和存储引擎层是如何交互的
准备数据：
CREATE TABLE hero (
id INT,
name VARCHAR(100),
country varchar(100),
PRIMARY KEY (id),
KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;
INSERT INTO hero VALUES
(1, 'l刘备', '蜀'),
(3, 'z诸葛亮', '蜀'),
(8, 'c曹操', '魏'),
(15, 'x荀彧', '魏'),
(20, 's孙权', '吴');name 列创建了一个二级索引。
mysql> EXPLAIN SELECT * FROM hero WHERE name < 's孙权' AND country = '蜀';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
| 1 | SIMPLE | hero | NULL | range | idx_name | idx_name | 303 | NULL | 2 | 20.00 | Using index condition; Using where |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.03 sec)key 列值为 idx_name，type 列的值为 range，表明使用 idx_name 二级索引进行一个范围查询。
那么 InnoDB 是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作，还是每取出一条符合条件的记录就进行回表一次？
其实 Server 层和存储引擎层的交互是以记录为单位的（也就是说得到一条二级索引记录后立即去回表，而不是把所有的二级索引记录都拿到后统一去回表）。整个执行过程如下：
- Server 层第一次开始执行查询，把条件
name < 's孙权'交给存储引擎，让存储引擎定位符合条件的第一条记录。 - 存储引擎在二级索引
idx_name中定位name < 's孙权'的第一条记录的name列的值为c曹操。判断 ICP（索引下推）条件。然后拿着该二级索引记录中的主键值去回表，把完整的用户记录都取到之后返回给 Server 层。 Extra列有一个Using Where，意味着 Server 层在接收到存储引擎层返回的记录之后，接着就要判断其余的WHERE条件是否成立（就是再判断一下country = '蜀'是否成立）。如果成立的话，就直接发送给客户端。不成立的话，就跳过该条记录。- 接着 Server 层向存储引擎层要求继续读刚才那条记录的下一条记录。
- 因为每条记录的头信息中都有
next_record的这个属性，所以可以快速定位到下一条记录的位置，然后继续判断 ICP 条件，然后进行回表操作，存储引擎把下一条记录取出后就将其返回给 Server 层。 - 然后重复第 3 步的过程，直到存储引擎层遇到了不符合
name < 's孙权'的记录，然后向 Server 层返回了读取完毕的信息，这时 Server 层将结束查询。
MySQL Server 层返回记录时，既不是一次性返回所有结果，也不是纯粹的逐条返回，而是采用了一种分批返回的折中方案。