MySQL架构和索引

雨点打透心脏的1/2处 2021-12-15 11:33 233阅读 0赞

## MySQL架构 ##

### 逻辑架构图： ###

大概分为四层，这个见仁见义，有不同的分法：

![1526344-20190515232702273-1920232856.png][]

*  第一层Connectors：处理不同语言与SQL的交互
 *  第二层Connection Pool ：连接池，管理缓存用户连接，线程处理等需要缓存的需求
 *  第三层Managerment Services & Utilities :系统管理和控制工具
 *  第四层 SQL Interface下的不同储存引擎的库：接受sql命令，到不同的库查询数据

### 执行流程图 ###

###  ###

![1526344-20190515232721954-1202648803.png][]

和上面的逻辑架构图结合，说说比较容易理解的东西：

Paeser ：解析器，见名知意，就是解析sql的一个模块，主要的功能：

*  对sql语句进行解析，分解成数据结构，然后有针对性的转发给不同的模块进行处理

Optimezer：查询优化器

*  这个mysql自带的一个优化机制，在查询之前会自动的对我们的sql语句进行优化

Cache和Buffer：查询缓存

*  在我们select数据后，会将结果集cache到缓存中，在表中数据没有变化是，作为缓存一直有效，当表中数据发生变更，自动失效

储存引擎接口：

*  可插拔储存引擎，你可以把它想象成一根又一根的内存条，代表不同的储存引擎，可以拔下来，也可以插上去，每个开发者都可以根据自己的意愿来开发，提一句：储存引擎是基于单表的，而不是基于数据库的，在创建表的时候可以指定，默认是InnoDB

### 储存引擎 ###

show engines ：查看储存引擎

写写日常使用的两种存储引擎 ：MyISAM 和 InnoDB

MyISAM是之前的版本，也是ISAM的变更版本，支持表锁，不支持事务，读取特别快使用

InnoDB是5.5版本之后默认的存储引擎，支持表锁，行锁，支持事务，或许就是为了弥补MyISAM不支持事务和外键的产生的吧

![1526344-20190515232814831-505697936.png][]

![1526344-20190515232955398-136049835.png][]

### MySQL的物理结构之日志文件 ###

mysql是通过文件系统对数据进行管理的，从文件上可以分为日志文件和数据文件

我们先说日志文件，这个还是比较重要的：

*  日志文件包括：错误日志，查询日志，慢查询日志，二进制日志，中继日志等...

错误日志：err log

*  默认是开启的，且在5.5.7后无法关闭，记录启动和关闭已经运行过程中发生的错误信息 ：hostname.err

二进制日志 ：bin log

*  这个也很重要，主要做数据恢复，和用于实现mysql的主从复制
 *  需要通过配置 log-bin=mysql-bin进行开启：mysql-bin-000001,=.log
 *  bin log记录了数据所有的ddl和dml语句，但不包括select语句内容，以顺序保存，描述了数据的变更顺序，对于灾难恢复至关重要

通用查询日志：general query log

*  记录所有的CRUD，并发情况下数据量惊人，默认关闭，不做理会

慢查询日志

*  默认是关闭的，slow\_query\_log=ON开启，定义：long\_query\_time ,可收集查询耗时超过该时间的sql语句，便于后续优化

中继日志

*  在主从复制的环境下产生的日子，主要作用有接收主机饭来的同步数据便于从机做同步支撑

### MySQL的物理结构之数据文件 ###

查看MySQL的数据文件 ： show variables like '%datadir%';

在给出的路径下，有以下几种文件类型：

*  .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
 *  .ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息
 *  .myd文件：主要用来存储使用MyISAM存储引擎的表数据信息。
 *  .myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树。

## MySQL索引 ##

*  索引的作用： 优化查询速度，是一种特殊的数据结构 比如B+ Tree，他们包含对表中对应添加了索引的记录的引用指针，书页和书的关系，索引是在储存引擎中实现的，也就是说不同的储存引擎会有不同的索引

MyISAM和InnoDDB讲解：这两个使用的都是B+ Tree

### 索引的分类 ###

单列索引：

*  普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
    
            eg：create index index\_name on table(colume(length))
    
                    alter table table\_name add index index\_name on(column(length))
 *  唯一索引：索引列中的值必须是唯一的，但是允许为空值
    
           eg：create unique index index\_name on table(column(length))
 *  主键索引：是一种特殊的唯一索引，不允许有空值

组合索引：在表中多个字段组合上创建的索引，想使用索引，需遵循最左前缀原则

*  全文索引： 只能在MyISAM引擎中使用，且只能char varchar text类型字段上使用
 *  空间索引：一般不会使用，不晓得；

删除索引：

*  drop index index\_name on table;

### 索引的储存结构：B Tree 和 B+ Tree ###

树的高度一般都是在2-4这个高度，树的深度影响 IO 读写的次数，影响性能

B Tree 和 B+ Tree最大区别在于：非叶子节点是否储存数据 B Tree的叶子和非叶子节点都会储存数据

B+ Tree 只有叶子节点才会储存数据，非叶子节点储存指针，指向另一个装载数据的容器

![1526344-20190515233043514-1854557450.png][]

![1526344-20190515233052868-1711392215.png][]

非聚集索引：

*  叶子节点只会储存数据行的指针，简单来说就是真是数据不在叶子节点上，在其他地方
 *  主键索引和辅助索引都会储存指针的值

聚集索引：

*  主键索引的节点会储存数据行，也就是数据和索引实在一起的

转载于:https://www.cnblogs.com/msi-chen/p/10872907.html

[1526344-20190515232702273-1920232856.png]: /images/20211214/0661232729614eb3a439c64fde301e97.png
[1526344-20190515232721954-1202648803.png]: /images/20211214/a2aa2508c6294df39cab7ec178a6b8db.png
[1526344-20190515232814831-505697936.png]: /images/20211214/1be0119158714174abd43905bf1e7338.png
[1526344-20190515232955398-136049835.png]: /images/20211214/cf0c0c7d1844418f98d55f51a4efd2a3.png
[1526344-20190515233043514-1854557450.png]: /images/20211214/9485ed08d0474ceba66d1852be1c6487.png
[1526344-20190515233052868-1711392215.png]: /images/20211214/3feaf539b1b441ae87ad04cdff3509e6.png