开发者学堂课程【数据库常见问题排查：常见问题排查方法】学习笔记，与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/68/detail/1169
常见问题排查方法
内容介绍：
一、MySQL 基本原理
二、库表设计规范
一、MySQL 基本原理
1.SQL 执行流程
SQL 是应用和数据库之间的一个桥梁，SQL 的执行效率对于应用来说至关重要。SQL的执行流程，首先是客户端和 MySQL server 之间，通过 MySQL 协议建立连接，MySQL server 在这个阶段负责建立连接，健全认证以及管理连接。在阿里云的 RDS 以及 PolarDB 上面，都提供了线程池的能力，一旦打开线程池，让通过认证的用户,直接获取线程，然后降低了新起线程的开销。非常适合于大量短链接以及高并发的一个场景，连接到 server 端之后，下一步会进入到查询缓存层。
在查询缓存层，如果你开启缓存或者是在语句上面设置的缓存开关，在这个环节，都会在缓存中判断这条语句的查询结果是否有缓存，如果存在缓存的话，结果直接返回，如果没有缓存，就进入到下一个阶段。查询缓存这里需要注意的是，缓存本身以及缓存失效是比较消耗资源的，所以如果要开启，一定要结合应用进行大量的评估，对于密集型的应用，要慎重去开启，如果业务一定要去做这里的开启，做与聚集的缓存设计。
接下来进入到词法解析和语法解析阶段，在这个阶段，会把 SQL 生成一个解析数。词法解析阶段，会对关键字，进行一些关键的识别，在语法解析阶段，会判断MySQL 的语法以及表名列名是否存在，同时在这个阶段，MySQL 会结合在他内部的规则然后进行一些 SQL 的改写，比如是关系代数转换等。
然后进入到了 MySQL 最复杂的环节—优化器，他使用了非常多的优化策略来去生成最优的一个执行计划。MySQL 它是基于成本的一个优化器，它会预测多种执行计划的成本，选择成本最低的执行计划来执行，明确执行计划之后，存储引擎会调用执行计划，然后完成最后的 SQL 执行，然后把执行结果返回给客户端。这里稍微提一下，如果这个时候开启了查询缓存，并且这个查询可以被缓存，然后执行结果就会放在缓存阶段。经过这一章节的分享，应该知道了一条 SQL 的一个生命的周期。但是对于 SQL 来说，它的执行效率是非常重要的。
2.索引类型
索引作用
-随机 IO 转化为顺序 IO
-减少 IO
-减少内存计算(比较、排序)
索引：快速定位记录的一种数据结构
-B+Tree 索引：等值、范围检索
-Hash 索引：等值检索
-空间索引(R-Tree)：地理数据检索(多维)
-全文索引：非结构化数据检索
结合 MySQL 常用的 B+Tree 索引进行展开的一个细节，左边是一个有两个字段的十行数据的一张表，其中字段一是主键索引，字段二是非主键索引。中间这张图，然后是把主键索引全部插入到 B+Tree 中，整合到 B+Tree 中，然后最右边图是把非主键索引整合到 B+Tree 中，在这两棵树中，他们的共性。第一个是以 page 为基本单位，一个 page 是6K，然后树的一个结构都是分成三层，根节点，分支节点，叶节点。在分支结点里面，存放的都是叶子节点的索引。在叶子节点里面，它是对应的我们的数据层，在主键索引的 B+Tree 里面，在叶子层存放的是具体的一个数据。它包含了完整的数据，并且按照主键有序排列，然后所有的数据之间，双向指针相互指向，然后可以在检索的时候提项。同时在非主键索引的叶子节点里面存放的是索引加主键，他也是根据索引有序排列，并且双向指针相互指向。B+Tree它作为一棵平衡树，然后对于任何一个值，它的一个检索深度的话是相同的。
3.层高和数据量
固定的表结构：table 的 id 是 int 类型作为主键，C1 是 int 类型作为非主键索引，C2是 varchar 字段，从表结构定义里面，获得以下信息，第一个主键的K的长度是四个字节。对于非主键索引，int 类型的长度也是四个字节，指针是八个字节，Page size 是16384个字节，在平均行长度是200字节，节点的填充率是70%的情况下，每个叶对于主键，非主键，以及非叶结点扇出上面具体的一个数值。
在主键索引下，一个叶是16K，填充率是70%，除以每个叶子节点上面存放的所有的数据（200字节），大约是一个 page 下面存放了50行主键。对于非主键索引来说，在叶子节点存放的是索引加主键的信息，因此是四加四个字节，一个 page 存放的非主键索引是1400个。对非叶子节点存放的是索引加指针的信息，所以他的非叶子节点的扇出的计算就是16 K乘以填充率10%，然后除以12，大约是1000，结合上面这些数据，可以看到在层高分别是2，3，4的情况下，针对主键索引和非主键索引分别的数据量的情况，简单看一下这个计算的方法。
扇出乘以主键可以存放的行数大约是5万，一样的，在非主键索引下面，他也是扇出乘以这个叶可以存放的非主键索引的一个行数是一百四十万，然后同理，可以得到三层四层分别支撑的一个量。就是知道了这个体量，来去看一下它对应的 IO 消耗，在不同的数据量下面，他 IO 消耗的一个情况。
假设这张表的表行数是一千万，看一下在一千万的数据量的情况下，分别在主键索引，非主键索引以及全表扫描下他的IO消耗情况。对应着来看如果 while 条件里面是 ID 等于某一个值，因为 ID 是主键，所以它是一个主键查询，然后因为是一千万行对应的层高是三，所以这次查询，它带来的是三次随机 IO。对于非主键索引，一千万行它的层高也是三，那他获取到C1等于某个给定的值的时候，它的 IO也是三，但是这个时候它并不能去获取到全部表的数据，他只能获取对应的主键信息，它需要有一个回表的操作，回到主表，回到主键的索引表里面去获取到所有的数据。因此他还要再加上主键的 IO消耗，因为 C1他并不是唯一键。所以这里面的 IO消耗的具体数值等于找到C1索引的一个 IO 消耗3和C1的数量乘以回到主键索引表里面的 IO 消耗，就是 C1的数量乘以三,也就是三乘以C1的数量加一，然后这块是对于按照 C1列来查询的时候，它的一个 IO 消耗，然后看一下，如果要按照 C2字段去查找的时候，IO 消耗是怎样的？
