1. 为什么读这本书？

这个问题可以分解为两个问题：首先，为什么要掌握数据库内部原理；其次，为什么是Mysql而不是其他数据库。对于第一个问题，掌握数据库内部原理帮助我们在日常项目中，更好的设计数据库、数据表和查询语句；遇到数据库性能问题时知道如何排查；能够应对数据库挑战大的项目，比如高并发情况下有何合理使用数据库等。那么为什么选择Mysql的书呢？首先，我最近做过的几个项目都是用的mysql；再者mysql确实很流行，如果我们关注数据库排行榜，就会发现mysql常年占据第二的位置（第一是oracle）；DB engines网站大概每季度发布一次数据库排行榜，排名标准是数据库在搜索引擎和各大技术论坛中的搜索热度，比如最近发布的2020年的top 5就是oracle，mysql，微软的sql server，postgresql和mangodb；前四个都是结构型数据库；其实mysql和oracle同属oracle甲骨文公司，mysql是被oracle收购的，mysql开源，oracle不是。在国内，mysql的应用也很热，比如阿里的淘宝就使用Mysql集群，实例数2000+，双十一期间单库承受6.5万QPS。

2. Mysql逻辑架构

2.1 客户端-服务端架构

客户端可以是我们开发的应用，或用于连接数据库的client工具；服务端是安装mysql了的机器。客户端向服务端建立连接时需要下列信息：1）host:用hostname或ip地址表示，hostname会经过DNS解析得到ip地址；可配置SkipNameResolve禁用DNS查找，改用ip地址，避免由于DNS不可用导致无法建立连接。2）Port：默认是3306，可配置；3）认证：username/password/client host；4）其他参数：比如字符编码约定等。

客户端与服务端之间的通信协议：采用半双工方式，即发送请求和返回应答不能同时发生。对应的问题是缺乏流量控制：客户端发送请求时，服务端必须等待请求发送完成才能响应；优化方式是配置max allowed packet；同样的，客户端接受应答也必须一直接收直到服务器发送完毕；优化方式是使用limit。

配置use result来决定客户端缓存查询结果或者采用流处理；缓存有利于服务端尽快返回应答，但结果过大时占用太多客户端内存；采用流处理，即边接收数据边处理，这样服务器的资源会被请求长时间占用。

2.2 服务端的存储引擎架构

存储引擎架构实现了查询处理与数据存储/提取相分离。

2.2.1 连接层、线程缓存

用于连接处理，认证授权；mysql运行在服务器上是一个进程，对每个连接创建一个线程，服务器会维护线程缓存threadCache，以减少新建/销毁线程的开销，提高处理新连接的速度；创建新连接时，首先查看线程缓存中是否存在空闲线程，存在则直接分配给新连接，否则创建新线程，连接关闭时，查看线程缓存是否已满，已满则销毁线程，否则把线程放回线程池。线程缓存过大会占用较多内存，同时空闲线程数增多，空闲线程大约占256k内存，因此当连接线程数在150-175区间内时，可设置缓存大小为75。

2.2.2 服务层

服务层包含以下四个模块，同时提供内置函数如日期/数学函数的解析，并提供跨存储引擎功能如存储过程、触发器和视图等。

1）查询缓存：key为SQL语句的hashcode；针对读操作，计算SQL的hashcode，查看是否有缓存记录，缓存命中则检查用户权限，权限信息也在缓存中，权限符合则直接返回数据。

2）解析器&预处理器：解析器验证SQL关键字和引号匹配等问题，并通过关键字把SQL解析为解析树；预处理器进一步验证解析树的合法性并验证权限，如表和数据列是否存在，别名是否有歧义等。

3）优化器：输入是合法的解析树，优化器基于成本选择最优执行计划并交给执行引擎。

4）执行引擎：输入是执行计划，通过API与存储引擎交互。

2.2.3 存储引擎层

负责数据存储和提取；服务层通过API与存储引擎通信，API屏蔽了存储引擎的实现细节，Mysql支持多种存储引擎，用户可以根据应用场景选择合适的存储引擎：InnoDB为默认存储引擎。

一个SQL查询的整体过程：客户端发送请求到服务器；服务器首先查询缓存，如果缓慢命中则直接返回；否则发送sql到解析器，解析器生成解析树并交给预处理器；经过预处理的解析树发送到查询优化器，优化器生成执行计划并交给执行引擎；执行引擎通过API调用存储引擎，存储引擎是数据的实际拥有者和维护者，返回检索结果；检索结果回到服务层进行排序/分组等数据处理。查询结果从服务端传回到客户端：即使没有数据返回，如delete操作，也会返回查询影响到的行数等信息；采用增量逐步返回的方式，即查询产生第一条结果时就开始向客户端返回结果，好处是服务器端无需存储大量结果，节约内存，并且客户端能更快的拿到结果。基于TCP传输，每行数据封装为一个包，传输过程中网络可能会对包进行缓存批量传输。

服务器的数据存储与分布：数据库的数据包括表定义和具体数据与索引；表定义存储在服务层，数据/索引存储在存储引擎层。文件系统中，Mysql将每个数据库(schema)保存在数据目录下的子目录。建表时，mysql在数据库子目录下创建与表同名的frm文件保存表定义。windows系统对文件系统大小写不敏感，类unix系统则对大小写敏感。

3.详解优化器

优化器的输入是合法的解析树，基于成本选择最优执行计划并交给执行引擎。执行计划是指令树的形式，其他关系型数据库大多生成查询字节码。用户可以用explain命令请求优化器解释优化过程，查看生成的执行计划。

优化分为静态和动态优化：静态优化基于解析树，不依赖查询参数值，又称编译时优化；动态优化考虑查询参数值和索引条目对应的数据行数，每次查询时重新评估，又称运行时优化。

3.1 成本

成本大致取决于完成查询所需扫描的行数。成本单位是随机读取一个数据页(大小4K)的成本，例如查询A成本为1000，表示需要1000个数据页随机读取才能完成查询A。优化器请求存储引擎获取存储引擎中的统计信息，如数据/索引的页面个数，索引基数，索引和数据行长度，索引分布等；并基于这些信息计算成本。通过show status like ‘last query cost’查询成本。

成本估算不准确会导致无法得到最优执行计划，导致成本估算不准确的因素：1）存储引擎提供的数据不准确，如InnoDB由于其MVCC架构无法维护数据表的准确行数；2）IO的不确定性，如数据页可能存在于内存/磁盘顺序或随机读写，导致成本高的执行计划实际可能更快。

优化器不总是基于成本计算，如当存在全文搜索的match子句且存在全文索引时，优先使用全文索引，即使存在更快的方式。

3.2 具体的优化类型

1）关联表顺序重排：理想情况下第一个关联表只需要扫描很少的行，这样有利于执行更少的循环和回溯；后续表可以根据索引查询，也就是说关联列需要是索引列。Mysql采用嵌套循环关联方式，例如select tbl1.col1, tbl2.col2 from tbl1 join tbl2 using col3 where tbl1.col1 in (5, 6);按联表查询的声明顺序遍历，首先iterate over tbl1 where tbl1.col1 in (5, 6)，针对每一行执行iterate over tbl2 where tbl2.col3 = tbl1.col3，查询出全部行。

2）min/max函数优化：1. 能够使用索引时，通过查找B-tree的最左端/最右端优化min/max函数；执行计划显示select tables optimized away，表示优化器已经在执行计划中把该表移除，用常数取代；2. 不能使用索引时，如select min(actor_id) from actor where name=‘hahah’，且name没有索引，此时需要全表扫描；或者Mysql可以主键扫描，找到第一个满足name条件的数据并返回。

3）提前终止查询：1. 使用limit，当mysql发现已经满足查询需求，则立刻终止查询；2. Mysql发现不成立的条件如1>2，则立即终止查询；3. 对于distinct/not exist/left join，如select film.id from film left outer join actor using filmId where actor.filmId is null，用于查找所有没有演员的电影，一个电影可以有很多演员，当优化器发现电影有一名演员则跳过该电影。

4）in(): 很多数据库系统认为in等同于多个or子句；mysql将in中的数据进行排序，通过二分查找确定是否满足条件，当in子句包含大量值，mysql的速度更快。

5）表达式等价转换：1. 使用等价变换规则规范表达式，如（a<b and b=c）and a=5可改写为b>5 and b=c；2. 预估并转化为常数表达式：如select film.id,film_actor. id from film join actor on filmId where film.id=1，简化为select id from actor where filmId=1；3. 等值传播: select film.id from film join actor using filmed where filmed > 500，filmid> 500的条件同样适用于film表和action表。

6）把子查询优化掉

7）将外连接转化为内连接：outer/inner join

3.3. 优化器的局限性

1）避免在in中包含子查询：如select * from film where filmId in (select filmId from film_actor where actorId=1)，我们以为会首先执行子循环查到filmId，然后再用filmId去查film；但事实上Mysql先对film表做全表扫描，对filmId逐个执行子查询，如果film是很大的表，则对性能影响更大。解决方式是改写查询语句：select film.* from film join film_actor using(film_id) where actorId=1; 

2）优化器不考虑并发，也无法利用多核特定来并行执行查询；

3）当在同一个表上查询和更新时，通过用as生成临时表的方式来解决。

3.4 hint

谨慎使用hint影响优化器，容易在mysql升级后，让新版的优化策略失效。常用hint包括：

1）Delayed：用于日志系统的insert，mysql把要插入的数据放在缓存，等待表空闲时批量写入，并立即返回应答到客户端；

2）Straight join：固定关联表的顺序；当Mysql没能选择正确的关联顺序，或者可选顺序太多导致Mysql花费大量时间进行成本计算时，用于减少搜索空间。

3）Small/big result：small result告诉Mysql结果集很小，可在内存中的创建临时表或进行排序；big result告诉Mysql结果集可能很大，建议用磁盘临时表做排序。

4）Cache：告诉优化器结果放在查询缓存

5）Use/ignore index：告诉优化器使用或者不用哪些索引

6）optimizer search depth：控制优化器穷举执行计划的限度

7）Switch <优化器特性名称>：开启/关闭优化器特性，如索引合并

4. 详解查询缓存

查询缓存的key是查询语句、数据库版本等元数据的哈希值，查询语句中的空格/大小写不一致都会导致缓存不命中，因此使用统一的编码规则是一个好习惯；此外，包含不确定数据如now()的查询不会被缓存。查询缓存会跟踪查询中涉及的每个表，表数据发生变化则缓存失效；另外，内存不够也会把某些缓存条目逐出缓存。

查询缓存完全存储在内存中，包括查询结果数据块及元数据；每个数据块存储数据块类型、大小、数据本身和指向前后数据块的指针，数据块类型包括查询结果、查询和数据表的映射和查询文本；元数据记录可用、已使用内存，以及已使用内存分别存储哪种数据类型。查询缓存大小可配置，元数据固定占用40K内存。当有查询结果需要缓存时，Mysql会申请一小块内存(缓存最小单位)，此时由于查询还未结束无法确切知道所需内存大小；Mysql把查询结果放入缓存，空间不够就再申请一块，如果查询完成后申请的内存有剩余，则释放空闲内存。这个过程中会导致内存碎片，选择合适的缓存最小单位可以减少内存碎片。

缓存的成本除了内存，还有对于读写操作增加了额外步骤：读操作增加读取缓存的操作，返回结果时增加写入缓存的操作；写操作需要将对应缓存设置为失效。另外，把缓存设置失效的操作需要对查询缓存加锁，如果有大量查询其结果被缓存，那么这些查询都需要等待锁。

什么时候应该开启查询缓存？复杂的查询语句适合用缓存，写密集型应用不适合，因为缓存主要提高读操作的效率，写操作相反还要增加失效缓存的成本。建议通过性能测试，只在有明确收益时使用查询缓存，在应用中使用缓存或者使用redis可能是更灵活的缓存方案。考虑到表修改会触发缓存清空，小数据表优于大数据表；考虑到批量写入只需要一次内存清空，批量写入优于单条写入。

查询缓存有以下配置项：1）queryCacheType：用于配置是否打开缓存，可配置为on、off和onDemand，配置为onDemand表示查询语句中写明sql cache时才放入缓存；2）queryCacheSize和queryCacheMinResUnit：配置缓存大小和缓存最小单位；3）QueryCacheLimit：单条查询的可缓存最大容量，如果查询结果过大则不会被缓存，但直到所有查询完成才能判定，此时已经分配了部分内存，因此，如果事先知道结果很大不用缓存，则通过sql no cache告诉查询缓存。

5. 更多存储引擎

5.1 常见存储引擎

1）InnoDB：Mysql默认事务型引擎，同样被Oracle收购。InnoDB把数据存储在表空间tablespace，包含多个数据文件。每个表的数据和索引存放在单独文件中。使用B+Tree存储索引。对于读操作支持可预测读和自适应哈希索引；对于写操作，提供插入缓冲区来加速插入操作；支持热备份，无需停止写操作就可以获取一致性视图，而其他存储引擎需要定时关闭服务器等来执行备份。

2）Mysql的其他内建存储引擎：Archive引擎适合日志和数据采集类应用，只支持select insert；Blackhole引擎丢弃所有数据，仅记录日志，不推荐；Csv引擎像访问数据表一样访问放在mysql数据目录下的csv文件；Fedarated联邦引擎：作为代理把查询请求发送到其他mysql服务器，不推荐；Memory引擎把数据存储在内存中，存取快，重启数据丢失，用于代替临时表保存中间结果；NDB集群引擎与mysql集群相关，用于追求高可用的分布式系统。

3）第三方存储引擎：Mysql提供插件式的存储引擎API，可扩展性强。如面向列的引擎，Mysql默认面向行，每行数据一起存储，查询也是以行为单位，但海量数据处理中面向列的方式效率更高：数据按照块排序，每块对应一组元数据；处理查询时，访问元数据决定是否跳过该块。

5.2 如何选择合适的存储引擎？

选择存储引擎是以数据表为单元，因为存储引擎是存储表数据的地方，一个表的数据不会分散在多个存储引擎。

1）优先选择InnoDB，除非需要用到InnoDB不具备的特性；2）不建议混合使用多种存储引擎；3）日志型应用：例如记录网站的所有访问信息，对插入速度要求高，可选择MyISAM或者Archive引擎。如果需要分析日志，建议把数据复制到备库，在备库上进行耗时的分析；4）大数据量超过10T则需要建立数据仓库。

5.3 如何修改数据表的存储引擎？

1）Alter table <tableName> engine=<engine>；该语句执行时间长，Mysql按行把数据从源数据表复制到新数据表，源表加上读锁；因此繁忙的表进行该操作需要很谨慎。另外，有外键的InnoDB表转换为不支持外键的MyISAM，再变回InnoDB后原有外键消失。

2）使用mysqldump把数据导出为文件，手动修改create table的存储引擎；之后再导入数据

3）先创建新的存储引擎表，然后通过insert/select进行数据导入。