我们的程序通过连接池向 MySQL 发送了一条sql语句，MySQL 就按照要求给我们返回了正确的结果，有时我们不免好奇，这个过程中究竟发生了什么？它是如何工作的？有什么方法可以加速我们的查询？需要解答这些疑问，首先我们需要对 MySQL 的架构体系有所了解。 下图为 MySQL Server 的体系结构：
MySQL 的层次
MySQL Server 在逻辑上一共分为三层：连接层、解析层、存储引擎层。各个层次负责不同的分工：
- 连接层，主要包含连接池，负责与程序的连接、授权认证、缓存等等；
- 解析层，主要包括了 Sql Interface、解析器、优化器以及缓存，主要负责解析用户传递的sql，并对 sql 的检索过程进行优化形成执行方案。
- 存储引擎层，主要包括 MySQL 的各种引擎以及所产生的各种数据、Schema、索引以及日志文件存储，存储引擎层提供了相关的数据查询检索接口与上层进行交互，依据上层生成的执行方案查找、存储数据。
Sql 执行过程
那么 sql 从外部连接器发送给 MySQL Server 后，具体的执行过程是怎么样的呢？
- 1、连接池鉴权，判定当前用户是否有当前操作的权限
- 2、如若为查询操作则看缓存是否有数据，缓存命中则直接返回
- 3、传递给解析器进行sql词法分析
- 4、优化器依据词法分析结果，决定后续处理方式，生成执行计划
- 5、依据上面生成的执行计划，调用存储引擎 api
- 6、如若为删改数据或该表等操作，如若开启 binlog 则在事务最终提交前写入 binlog 日志
- 7、返回相关处理结果
Query Cache
从上图我们可以看到，在真正执行 sql 解析前，会有一次查询缓存的过程，目的是为了加速数据查询的速度，这对于读多写少的场景看起来是在合适不过了，但实际上这个缓存真的有效吗？《Query Cache，看上去很美》这篇文章中提到了一个生产数据：
一个更新频繁的BBS系统。下面是一个实际运行的论坛数据库的状态参数：
QCache_hit 5280438
QCache_insert 8008948
Qcache_not_cache 95372
Com select 8104159
可以看到，数据库一共往QC中写入了约800W次缓存，但是实际命中的只有约500W次。也就是说，每一个缓存的使用率约为0.66次。
从缓存命中率上来看0.66的缓存命中率实在是太低了，那为何会有这个问题呢？我想应该有如下几个方面的原因：
- 1、Query Cache 是按照 sql 进行缓存的，需要 sql 完全一致才能命中
- 2、Query Cache 使用场景有限在有些场景如子查询、函数、schema、临时表等等下将会失效
- 3、MySQL中一个表中的任意一条数据更新都会引发缓存失效
由上面的分析可以看出，query cache的开启对性能提升有限，因此很多DBA 都建议生产环境中禁用该功能，MySQL 本身也在5.7.20将查询缓存标记为废弃，并在 MySQL 8.0 以上版本中移除了该功能。如下是MySQL官方文档中所提到的:
NOTE
The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.
缓存具体机制可以查看这篇文章《Mysql 缓存机制》，作者写的很详细。
优化器与执行计划
在具体执行获取数据前，mysql会对sql的执行方案进行优化，通过优化器得到最终的执行计划。所谓的执行计划指的就是MySQL将如何执行一条Sql语句，包括Sql查询的先后顺序、使用索引、索引长度、排序等信息。有个时候我们的程序可能加载数据非常慢，也有可能我们明明建立了索引，却并没有什么效果，通过执行方案我们可以知道mysql到底进行那些操作，这对于我们的 sql 优化有着重要意义。那么我们要如何查看我们的sql最终的执行计划是什么呢？
查看执行计划语法
explain 你的sql
通过上述方式即可获得查看你的sql的执行计划。你会获得例如这样的结果：
执行计划包含信息
| 列名 | 作用 |
|---|---|
| id | 表示执行计划中的各条sql记录的查询顺序 |
| select_type | sql查询类型 |
| table | 执行计划中的该条查询使用了哪个表 |
| partions | 分区，在使用分区表的时候会用到 |
| type | 查询类型，通过该字段可以了解查询使用索引的类型 |
| possible_keys | 可能可以用到的索引 |
| key | 实际使用的索引 |
| key_len | 索引长度 |
| ref | 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值 |
| rows | 大致反映所需要读取的行数,基于统计实现，非真实查询数据量 |
| filtered | 大致反映所需过滤的数据,基于统计实现，非真实查询数据量 |
| extra | 其他信息，如使用了索引、使用了文件排序等等 |
id 列详解
id 表明执行计划的执行顺序。共有如下三种可能性：
- 1、id相同，则从上到下依次执行
- 2、id不同，id较大的语句先执行，一般发生在子查询的情况下
- 3、上述两种情况同时存在，id较大的语句先执行，id相同的从上到下依次执行
select_type 详解
select_type select的类型。
| 类型 | 解析 |
|---|---|
| SIMPLE | 简单查询（不使用 union 或子查询的查询） |
| PRIMARY | 主查询的意思，复杂查询中最外层的 select |
| UNION | union查询中的第二个及第二个以后的查询标记为union |
| DEPENDENT UNION | union查询中的第二个及第二个以后的查询标记为union，需要依赖外层查询 |
| UNION RESULT | union查询结果集 |
| SUBQUERY | 子查询中的第一个查询标记为subquery |
| DEPENDENT SUBQUERY | 子查询中的第一个查询标记为subquery，需要依赖外层查询 |
| DERIVED | 衍表，在From后where前的子查询 |
| MATERIALIZED | 物化子查询，如果子查询执行一次即可以得到结果,即子查询的结果是稳定的,则这样的子查询可以被缓存起来，多次使用。缓存到内存中，如果内存中放不下，则会写外存。在MySQL中，这个缓存对应的是临时表 |
| UNCACHEABLE SUBQUERY | 无法缓存的子查询，对于第一行必需重新执行 |
| UNCACHEABLE UNION | 不可缓存的子查询里 UNION 中第二个及之后的 SELECT |
table 详解
table 表明查询的是哪张表，有如下三种情况。
| 类型 | 解析 |
|---|---|
| unionM,N | 引用id为M和N UNION后的结果集 |
| derivedN | 引用id为N的结果派生表的结果集，派生表可以是一个结果集，例如派生自FROM中子查询的结果 |
| subqueryN | 引用id为N的物化子查询结果集 |
partions 详解
表明本次查询匹配结果所在分区标的那个分区。
type 详解
type 为查询的连接类型。执行效率依次为system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL
| 类型 | 解析 |
|---|---|
| system | 一个表里面只有一条记录的情况 |
| const | 常量，表里面至多只有一条数据匹配的情况，如按照主键或唯一索引查询 |
| eq_ref | 多表join时，对于来自前面表的每一行，在当前表中只能找到一行。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型 |
| ref | 对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的最左前缀或索引不是主键或唯一索引时，使用ref类型 |
| fulltext | 全文索引,优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 |
| ref_or_null | 同ref，增加了null判断 |
| index_merge | 表明查询用到了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range |
| unique_subquery | 用于where中的主键in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。 |
| index_subquery | 类似于unique_subquery。适用于非唯一索引，可以返回重复值。 |
| range | 索引范围查询，如 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN()操作符 |
| index | 同all全表查询差不多，唯一区别是用到了索引树扫描 |
| ALL | 全表扫描 |
possible_keys 详解
possible_keys表示可能可以用到的索引。
keys 详解
表明实际使用的索引。
key_len 索引长度
key_len显示了MySQL使用索引的长度，对于联合索引，通过这个字段可以看出MySQL具体使用了联合索引中那些部分的索引。 那么索引长度是如何计算的呢？
普通字段索引长度 = 索引列长度 + 字段允许为空需要加1
字符字段索引长度 = 单字符所占字节*索引列长度 + 字段允许为空需要加1 + 动态列还需再加2
- 1.MySQL 如果索引列可以为null，则需要加1个字节，经过测试在MySQL5.7上如果数据集中没有改字段没有为null的数据，则MySQL 会进行优化，不会加上1。
- 2、对于普通非字符字段，即本身的长度
- 对于字符字段，则与字符集有关，长度为单个字符所占字节*长度，可变长字符串还需增加2个字节
举个例子，例如有这么一个表,对idNo与name联合建立了一个索引：
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`idNo` char(18) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INX_IDNO_NAME` (`name`,`idNo`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
那么 INX_IDNO_NAME 索引的长度为：18×4 + 1 + 50×4 + 1 + 2 = 276
ref 详解
连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows 详解
大致反映所需要扫描的行数,基于统计实现，非真实查询数据量。这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
filtered 详解
大致反映所需过滤后的数据,基于统计实现，非真实查询数据量。
extra 详解
extra 显示了MySQL处理查询的附加信息，通常有如下的信息：
| 类型 | 解析 |
|---|---|
| distinct | 使用了distinct关键字 |
| Using filesort | 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果 |
| Using index | 使用了覆盖索引 |
| Using where | 使用了严格条件匹配，除了特定需要获取或扫描全表数据的情况，一般这种情况都可能是查询问题 |
| Using temporary | 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高 |
索引
索引的实质为数据结构，MySQL的索引与具体的引擎实现有关，MySQL 中索引从数据结构可以分为：
- B-Tree 索引，使用b+ tree
- Hash 索引，只在Memory 引擎中实现
- 全文索引，底层也是基于b+ tree，基于倒排索引实现
- R-Tree 索引，空间索引
从索引组织上来划分:
- 聚集索引
- 非聚集索引
从逻辑角度上来划分：
- 主键索引
- 唯一索引
- 单列索引
- 复合索引
- 空间索引
SQL优化
一般来说，常见引发索引失效的情况有如下几种：
- 索引列上做了操作如（计算、函数、自动/手动类型转换 如字符串没有加''号）
- 索引列使用了 != 或 <> 判断
- 索引字段使用了 is null / is not null 的非空判断
- 索引列使用了 like 以通配符开头如'%关键字'
- 索引列使用了or
那么我们要如何对SQL进行优化呢？原则是什么？
整体上将，MySQL 的优化原则应该遵循：
- 尽可能的最大化利用索引
- 减少数据扫描量
- 减少回表查询次数
从表结构方面：
- 字段类型选择遵循小而简单的原则，在满足功能的前提下对于索引字段列我们需要优先选择占用空间较小的类型，可以使用数值的就不用字符串，整体上来讲，字符串比较要比数值的比较开销大。
- 对需要频繁进行连表查询的字段在变化情况不大并且在显示上没有大的影响的情况下考虑建立冗余。
- 只建立合适的索引，对于长期不用的索引考虑删除。
- 对于大表的ALTER TABLE非常耗时，可用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表的方式处理。
- 对于索引来说，索引的长度是有限的。对于MyISAM来说索引最大长度为1000，而对于InnoDB来说则只有767。对于单列索引的建立原则上应该尽可能的短。而对于联合索引来说，我们可以把使用频繁的索引放在最左边，使用时遵循最左原则，虽然MySQL优化器能够一定程度上识别并使用正确的索引。
- 对于数据比较重复的字段如性别这种情况索引效率太低就没有必要建立索引
从SQL优化方面：
- 使用覆盖索引，尽量减少查询字段数量
- 使用联合索引，减少索引数量，提高索引复用
- 避免多个范围条件,MySQL无法同时使用多个索引来优化范围条件
- 使用索引顺序来优化排序，因为排序与索引顺序不一致的话会使用file sort 带来额外开销
- 使用 union 或子查询来优化 or
- 优化limit，大数据量下的limit 如 limit 1000000,20 这种情况下 MySQL 需要扫描前1000020条记录，并将前1000000 条记录丢弃，只返回20条记录。这种情况可以通过子查询来延迟扫描，尽可能的减少扫描的数据。 比如：
select id,name,age from user inner join (
select id from user limit 10000,10
) b using (id)
