演讲嘉宾简介：郑旦，阿里云高级数据库专家
以下内容根据演讲视频以及PPT整理而成。
本次分享主要围绕以下三个方面：

一、MySQL基本原理
二、库表设计规范
三、诊断实践案例

一、MySQL基本原理

MySQL是非常常见的数据库，适用于多种场景中的应用，其次MySQL活跃的社区使其流行度非常高。同时，MySQL也是云上售卖最多的产品之一。
SQl执行流程
SQL是应用和数据库之间的桥梁，SQL的执行效率对应用来说至关重要。下图展示了一条SQL的执行流程：
1）首先是客户端通过MySQL协议与MySQL Server建立连接，MySQL Server负责建立连接，健全认证和管理连接。在阿里云的RDS上提供了线程池的能力，一旦打开线程池，使得通过认证的用户直接可以获取线程，这适合于大量短链接和高并发的场景。
2）连接到MySQL Server后，进入查询缓存层，如果开启了缓存或者通过语句设置了缓存开关，此时就需要检查SQL中是否包含缓存，如果存在缓存，结果直接返回，如果没有缓存则进入下一阶段。查询缓存阶段需要注意缓存本身和查询缓存都是非常消耗资源的，如果开启缓存需要提前对应用做大量评估，密集型的应用请慎重开启。若必须要开启，可以参考语句级的缓存设置，***的控制哪些查询需要进入缓存。
3）其次进入词法解析和语法解析阶段。SQL会生成一颗解析树。词法解析阶段会解析关键字，语法解析阶段判断MySQL的语法和库表，以及检查表名和列名是否都存在。同时MySQL会结合自身的规则，进行SQL的改写，如关系代数转换等。
4）之后是进入MySQL最复杂的优化器环节。优化器使用了非常多的优化策略来生成最优的执行计划，MySQL是基于成本的优化器，因此会预测多种优化策略的成本，选择成本最低的执行计划进行执行。
5）明确执行计划之后，存储引擎会调用执行计划，完成最后的SQL执行，并且将执行结果返回给客户端。如果此时开启了查询缓存，执行结果会同时放在缓存阶段。

索引类型
SQL的执行效率提升是至关重要的，SQL提效方面最常用的是索引策略。索引的基本作用主要是将随机IOh转化为顺序IO，减少IO，并且减少内存计算，如比较、排序等等。索引是快速定位记录的一种数据结构的方法，主要类型有B+Tree索引、Hash索引、空间索引（R-Tree）以及全文索引等。B+Tree索引支持等值、范围检索；Hash索引支持等值检索；空间索引（R-Tree）支持地理数据检索（多维数据）；全文索引支持非结构化数据检索。

B+Tree索引结构
以B+Tree为例，下图展示了B+Tree索引结构。左侧两列的第一列是主键索引，第二列是非主键索引，分别将主键索引和非主键索引全部插入到B+Tree中。两个树的共性都是以page为基本单位，分为根节点、分支节点、叶子节点三层，非叶子节点存放的是叶子节点的索引，叶子节点对应的是数据层，包含完整数据，并且有序，可以在检索的时候提效。非叶子节点上存放的是叶子+主键，也是有序排列，相互指向。B+Tree是一颗平衡树，任一值搜索深度相同。检索深度与IO消耗直接相关。

层高和数据量
下图创建的table主键是int类型，c1是int类型的非主键索引，c2是一般列，varchar字段。从下图可以得到表结构定义的具体信息，首先是主键的key长度（Clustered index key）是4 bytes，非主键索引key长度（Secondary index key）也是4 bytes，指针（Key pointer）是8个bytes。假设在平均行长度（Average row length）是200个bytes的情况下，page size 是16K，即16384 bytes。节点填充率（Average node occupancy）为70%的情况下，在主键索引中一个page可以存放的数据（Average row per page(Pri Key)）是page sizeAverage node occupancy/Average row length≈50行，非主键索引（Average row per page(Sec Key)）是page sizeAverage node occupancy/(Secondary index key+Clustered index key)≈1400行，非叶子节点存放的是索引+指针的信息（Non-leaf fanout），所以可以存放的数据为page sizeAverage node occupancy/(Secondary index key+Key pointer)≈1000行。下图表格中给出了不同层高下，主键索引和非主键索引数据量的情况。主键索引下在层高为2时，Non-leaf fanoutAverage row per page(Pri Key)≈50000，非主键索引下也同理Non-leaf fanout*Average row per page(Sec Key)≈140w。其它层高也同理。

那如果表行数是1000w，分别在主键索引和非主键索引，以及全表扫描下IO消耗情况如何。主键索引1000W对应的层高是3，带来了3次随机IO，非主键索引1000w对应的层高也是3层，但并不能获取到全部表的信息，还需要加上主键的IO消耗，此时等于c1的IO消耗c1数量+回到主键索引的IO消耗=（3c1数量）+1。若按照c2查询，上面没有索引，所以只能从第一行逐步查找（全表扫描），1000w行需要消耗20w的IO。这样的话三种扫描的差距就体现出来了。同时这里补充一下，MySQL可以支撑的数据量与表结构相关，与具体的SQL相关。
查询代价
主键查询优先于二级索引查询，即非主键索引。而二级索引查询优先于全表扫描，单表查询优先于连接查询，表连接数量越少越好，连接查询时的IO消耗等于驱动表的全面扫描*被驱动表的索引消耗，因此可以允许的情况下，尽可能控制join的数量。最后一条是通过使用索引避免排序代价。

二、库表设计规范

表结构设计
第一条设计规范是降低单条记录长度，日高缓存利用率。如果长度太长，每个配置下存放的记录数就会降低，缓存率自然也会跟着降低。可以在业务核心表上降低单表的记录长度。第二条是将访问频率低、大字段拆分，用主键关联，提高缓存命中率。第三条是适当冗余，不要使用多表join查询。第四条是在分库分表场景下，避免数据倾斜。

索引设计
第一条是选择过滤性高的字段建立索引，即通过distinct（col）和count（*）的比值，判断过滤性。第二条是在Join查询中连接字段建立索引，避免全表扫描。第三条是尽量使用覆盖索引，将Select item后面的列加到非主键索引中，从而避免在非主键索引中回表到主键索引的操作，无需访问主键索引表，避免随机IO。第四条是利用前缀索引，将索引长度变短，单个配置下的索引行数变多，提高缓存率。最后一条是尽量避免建重复索引，提高索引使用率。太多的索引会使得写入性能变差。

SQL书写
第一条是建议读写都采用主键索引。尽量利用索引排序，避免产生临时表，如order by。避免对查询字段进行计算。避免使用select *，字段少可以配合覆盖索引。避免使用全模糊查询。对表而言，访问的应用非常多，因此访问的SQL也会很多，在索引的设计上好综合考虑，保证核心SQL的访问。

三、诊断实践案例

在MySQL的实践中有三种案例，一类是SQL优化实践案例，另一类是主备延迟实践案例，还有空间优化实践案例。
SQL优化实践案例
减少磁盘IO访问
下图中对表建立了A、B、C三列的符合索引，前两个案例都使用了A列。第3个和第4个是范围查询.在B+Tree中，如第4个中的A的值不确定时，B的值是无法使用到的。第5个A的值是list，是固定的，A和B都可以被使用到。第6个A值没有，B值也用不到。第7个A值确定，但是B是范围值，因此C值也用不到。

返回更少数据
以select 为例，直接全部数据返回，对select 语句中写所需的列时，可以带来以下优点。首先是减少网络传输开销，只返回了所需要的列。其次是减少处理开销，还减少了客户端内存占用。还可以在字段变更时提前发现问题，减少程序BUG。最后在转换到Name和ID时，有机会使用到覆盖索引，避免回表数据。

减少交互次数
Col in()可以代替多次col=？，但注意in的范围不要太多，避免索引失败。第二个是可以使用batch DML操作，此时需要注意平衡，避免锁过大问题。第三个和第四个是阿里云RDS上特有的语法，select from update 和commit on success/rollback on fail hint语法。在，select from update适用与单行update之后获取更新后的场景，避免两次访问的开销。commit on success/rollback on fail hint语法，在提交时直接成功，若失败了可以rollback，适合于高吞吐下的优化。以上方法还是不够的话，可以回到业务逻辑中进行优化。这样的优化可以减少交互次数的网络开销，减少了语法、语义分析，执行计划生成过程中的开销。减少了事务提交次数，两阶段提交成本和IO成本。还可以减少锁持有时间。

减少CPU开销
下表有A和B列，其中Order by B 不符合符合索引的最左原则。其中最后以个Order by A[ASC/DESC]，B[DESC/ASC]，如果A和B顺序不一致，在8.0之前就不能使用这个索引，8.0之后可以。通过索引的使用可以减少CPU开销。

主备延迟实践案例
当发现有主备延迟时，首先要检查主库和备库上的容量，出现的问题一般是主库和备库的资源不一致，备库无法支撑主库的发展，就像水龙头水很大，但是桶不够大。第二个排查点是主库和备库的同步状态，如果符合同步状态，可以检查线程状态是否有锁等待。如果上面的排查不能解决主备延迟问题，还需要进一步深入排查，在主库侧做DDL变更排查，是否有超大事务，这会影响到系统的稳定性。最后在库表设计方面检查是否有不合理的设计，如无主键表，外键约束等。

空间优化实践案例
空间优化主要从三种文件入手，数据文件，临时文件和日志文件等。数据文件优化方案包括库表结构设计是否合理，检查主键设计是否合理，是否因为delete操作导致碎片放大。还包括冗余索引的检查，还需要定期的对碎片过多问题进行optimize操作。临时文件场景三种优化方案包括通过适当调大sort_buffer_size，避免操作过程中带来性能慢的问题，其次是创建合适的索引避免排序，最后是统计报表类查询考虑换存储。但对数据量大的业务，MySQL并不适合，可以考虑阿里云的RDS。最后是日志文件，首先要检查日志文件里面是否使用了大字段，其次对于没有使用订阅增量的数据，可以考虑使用truncase替代delete from，避免bin log中由于有大量的delete from清空表操作带来的日志文件。