MySQL 简介

image.png

一 架构

mysql架构图

MySQL的架构实现主要可以分成三层：接入层，服务层，引擎层

• 接入层负责网络连接管理，授权认证等功能，包括 SSL 加密任务，也是在此完成。

• 服务层。服务层大致上可以分成查询接口，查询优化器，解释器几个部件

  
  服务层组件

• 引擎层。
  引擎层负责数据的提取和存取，这里实现了各种查询数据接口，多线程事务，并发逻辑等等。
  层与层之间通过 API联系。因此，我们称之为层。

大致上一个 查询动作通过网络请求到达 mysql服务器以后，在连接层进行鉴权，服务层将 sql解析优化，最后调用引擎层的api执行数据查找，查找结果通过 网络回传回客户端

• 存储层。操作系统的文件管理 ，一些 IO 逻辑

二 引擎

2.1 MyISAM
frm文件：存储表的定义数据
MYD文件：存放表具体记录的数据
MYI文件：存储索引
2.2 InnoDB
-可以通过自动增长列，方法是auto_increment。

• 支持事务。默认的事务隔离级别为可重复度，通过MVCC（并发版本控制）来实现的。
• 使用的锁粒度为行级锁，可以支持更高的并发；
• 支持外键约束；外键约束其实降低了表的查询速度，但是增加了表之间的耦合度。
• 配合一些热备工具可以支持在线热备份；
• 在InnoDB中存在着缓冲管理，通过缓冲池，将索引和数据全部缓存起来，加快查询的速度；
• 对于InnoDB类型的表，其数据的物理组织形式是聚簇表。所有的数据按照主键来组织。数据和索引放在一块，都位于B+数的叶子节点上；
  2.3 Memory
  将数据存在内存，为了提高数据的访问速度，每一个表实际上和一个磁盘文件关联。文件是frm。
  支持的数据类型有限制，比如：不支持TEXT和BLOB类型，对于字符串类型的数据，只支持固定长度的行，VARCHAR会被自动存储为CHAR类型；
  支持的锁粒度为表级锁。所以，在访问量比较大时，表级锁会成为MEMORY存储引擎的瓶颈；
  由于数据是存放在内存中，一旦服务器出现故障，数据都会丢失；
  查询的时候，如果有用到临时表，而且临时表中有BLOB，TEXT类型的字段，那么这个临时表就会转化为MyISAM类型的表，性能会急剧降低；
  默认使用hash索引。
  如果一个内部表很大，会转化为磁盘表。

数据库之范式和反范式

索引

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。

B树只适合随机检索，适合文件操作，B+树同时支持随机检索和顺序检索
B+树的磁盘读写代价更低， B+树的内部结点并没有指向关键字具体信息的指针
B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束
只要遍历叶子节点就可以实现整棵树的遍历，数据库中基于范围的查询是非常频繁，B树这样的操作效率非常低

3.1 聚集索引
B+ 树索引按照存储方式的不同分为聚集和非聚集索引，聚集索引在叶子节点存储数据，非聚集索引在叶子节点存储的是健值和主键，默认是在主键上建立聚集索引，下图以B+树为例：

聚集索引查询示意图

3.2 非聚集索引
其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，他结构顺序与实际存放顺序不一定一致。
使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

覆盖索引 查询字段在索引上即为覆盖索引，查询索引里的列的数据而不需要进行回表二次查询，如index(col1, col2)，执行下面的语句

关于看join

INNER JOIN
LEFT JOIN（左连接）
RIGHT JOIN (右连接）
MySQL 使用了嵌套循环（Nested-Loop Join）的实现方式。Nested-Loop Join需要区分驱动表和被驱动表，先访问驱动表，筛选出结果集，然后将这个结果集作为循环的基础，访问被驱动表过滤出需要的数据。Nested-Loop Join分下面几种类型：

SNLJ，简单嵌套循环。这是最简单的方案，性能也一般。 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

优化
小结果集驱动大结果集。用数据量小的表去驱动数据量大的表，这样可以减少内循环个数，也就是被驱动表的扫描次数。
用来进行 join 的字段要加索引，会触发 INLJ 算法，如果是主键的聚簇索引，性能最优。
例子：第一个子查询是72075条数据，join的第二条子查询是50w数据，主要的优化还是驱动表是小表，后面的是大表，on的条件加上了唯一索引。

SQL 优化

图

遵循索引原则适合大部分的常规数据库查询场景，但不是所有的索引都能符合预期，从索引原理本身来分析对索引的创建会更有帮助。

小表的全表扫描往往会比索引更快
中大型表使用索引会有很大的查询效率提升
超大型表，索引也无法解决慢查询，过多和过大的索引会带来更多的磁盘占用和降低INSERT效率

image.png

慢查询分析
explain + show profile

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

IO瓶颈
第一种：磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 -> 分库和垂直分表。

第二种：网络IO瓶颈，请求的数据太多，网络带宽不够 -> 分库。

CPU瓶颈
第一种：SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 -> SQL优化，建立合适的索引，在业务Service层进行业务计算。

第二种：单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 -> 水平分表。