MySQL二、基础架构解析

前言：

1. 文章内容：MySQL基础架构、SQL成本计算
2. 本文章内容来源于笔者学习笔记，内容可能与相关书籍内容重合
3. 偏向于知识核心总结，非零基础学习文章，可用于知识的体系建立，核心内容复习，如有帮助，十分荣幸
4. 相关文献：高性能MySQL、MySQL技术内幕InnoDB存储引擎、MySQL实战45讲

---

 

应用与MySQL服务器交互图：

 Server层功能和作用：

　　连接器

1. 负责跟客户端建立连接，获取权限，维持和管理连接。通过TCP握手建立连接后，连接器开始认证用户身份。
2. 每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，MySQL5.5之后，支持线程池插件，可以使用池中的少量线程来服务大量连接，不需要为每个新连接创建或销毁线程
3. 连接建立过程比较复杂，应尽量减少连接的动作，使用长连接。由于MySQL在执行过程中临时使用的内存是管理在连接对象里的，这些资源在连接断开时才释放，长连接累积下来可能导致内存占用过大，被系统强行杀掉。

　　查询缓存

1. MySQL拿到一个查询请求后，会先查询缓存，之前是否有执行该条语句。有则查询缓存中的结果返回客户端，没有则查询DB并将结果存入查询缓存后返回客户端。
2. MySQL8之后，查询缓存功能被删掉了，之前的版本可通过：query_cache_type=DEMAND来设置是否使用查询缓存，或显示的在查询时加上SQL_CACHE。
3. 大部分情况下不建议使用查询缓存：

• • 对表的更新会导致该表上所有查询缓存被清空，对于频繁有更新的库，查询缓存命中率非常低。
  • 对于静态表，很长时间更新一次的表，可以适当采用查询缓存

　　分析器/解析器

1. 当没有命中查询缓存，就开始真正的执行语句。MySQL首先要知道语句要做什么，因此会对SQL进行解析，先词法分析然后语法分析。
2. 在该阶段会判断语句是否正确，表是否存在，列是否存在等等。

• • 词法分析：分析语句中的字符串是什么，代表什么
  • 语法分析：分析语句中的语法规则是否满足MySQL语法

　　优化器

1. 语句分析完后，会进行优化器。作用是：重写查询、决定表的读取顺序、选择合适的索引。该过程结束后，这个语句的执行方案就确定了。
2. 优化器不关心存储引擎，但存储引擎会影响优化器。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。

　　执行器

1. MySQL通过分析器知道了语句要做什么，通过优化器知道了怎么做，之后进入执行器开始执行语句。
2. 开始执行时会判断用户对该表有没有执行相应操作的权限。如果没有，则返回没有权限，如果有权限，则打开表继续执行。此时执行器会根据表的引擎定义，去使用这个引擎提供的接口

SQL语句成本计算：

1. 优化器选择索引会考虑：扫描行数（行数越少代表磁盘访问次数越少，消耗CPU越少）、是否使用临时表、是否排序
2. 扫描行数判断：根据索引字段的区分度来进行统计和估算，一个索引上不同值的个数叫基数，基数越大区分度越好
3. 索引基数获取：默认选择N个数据页（N可以设置），统计这些页面上的不同值，得到一个平均值，乘以索引页面数，得到该索引基数。数据表会持续更新，当变更数据行数超过1/M（M可以设置）时，自动触发重新做索引统计。
4. 得到预估扫描行数后，普通索引会考虑回表代价，排序，临时表等，索引选择出现异常时，可以force index强制指定索引，或通过sql优化、删除误用索引、新建更适合的所以，诱导优化器进行索引选择。