Mysql架构、索引介绍及原理（一）

目标：

• 掌握Mysql的各组件及各组件的功能 
• 理解Mysql简版执行流程和详细执行流程 
• 掌握MyIsam和InnoDB的区别并说明使用场景 
• 掌握Mysql日志文件及主要日志文件的作用 
• 理解Mysql的数据文件及作用 
• 使用命令查看mysql日志 配置my.cnf开启二进制日志、通用查询日志、慢查询日志等 
• 掌握索引、分类、优劣势 
• 使用命令创建、查看、删除索引 
• 理解索引的原理和存储结构

---

一、MySQL架构篇

逻辑架构

MySQL架构总共三层

　　首先，最上层的服务并不是MySQL独有的，大多数给予网络的客户端/服务器的工具或者服务都有类似的架构。比如：连接处理、授权认证、安全等。

　　

　　第二层的架构包括大多数的MySQL的核心服务。包括：查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数）。同时，所有的跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。

 

　　第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。服务器通过API和存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明化。存储引擎API包含十几个底层函数，用于执行“开始一个事务”等操作。但存储引擎一般不会去解析SQL（InnoDB会解析外键定义，因为其本身没有实现该功能），不同存储引擎之间也不会相互通信，而只是简单的响应上层的服务器请求。

详细的MySQL系统架构图 

MySQL模块包括：

1. 连接器（Connectors） 
2. 系统管理和控制工具（Management Serveices & Utilities） 
3. 连接池（Connection Pool） 
4. SQL接口（SQL Interface）
5. 解析器（Parser）
6. 查询优化器（Optimizer）
7. 查询缓存（Cache和Buffer）
8. 存储引擎（Pluggable Storage Engines）

首先，我们对各个模块做一简单介绍

1、Connectors

　　指的是不同语言中与SQL的交互。

　　

2、Connection Pool

　　管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。

 

３、 Management Serveices & Utilities

　　系统管理和控制工具。

 

4、 SQL Interface

　　接受用户的SQL命令，并且返回用户需要查询的结果。

5、 Parser

　　SQL命令传递到解析器的时候会被解析器验证和解析。对一条SQL语句（  select * from t1 ）进行 词法分析 根据分词 ----- 》形成语法树。 语法分析： 符合SQL的语法 SQL的语法 ： SQL 92标准。 limit是 MYSQL自己的语法。

6、 Optimizer

　　查询优化器：SQL语句在查询之前会使用查询优化器对查询进行优化。

       mysql 觉得你写的SQL 不是完美的需要进行优化。 那么会优化什么呢？ MySQL会对SQL语句进行索引使用优化， 只使用一个 使用最优索引。

       我们可以通过explain查看MySQL执行索引优化结果。一般在进行多表关联查询时，尽量以小表驱动大表。Where条件在执行时，MySQL会根据 where条件从左到右 自动找过滤力度最大的条件先执行，例如 where id=1 and sex='男' 和 where sex='男' and id=1 两个条件过滤语句MySQL执行顺序是一致的。

7 、Cache和Buffer

　　查询缓存：主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。

　　 MySQL8.0后不再使用 Cache和Buffer

8 、存储引擎接口

　　MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。 注意：存储引擎是基于表的，而不是数据库。

      以表为单位 creat table xxx() engine=InnoDB/Memory/MyISAM MySQL的存储引擎是针对表进行指定的。（engine=InnoDB\myisam）

存储引擎	说明
MyISAM	高速引擎，拥有较高的插入，查询速度，但不支持事务、不支持行 锁、支持3种不同的存储格式。包括静态型、动态型和压缩型。
InnoDB	5.5版本后MySQL的默认数据库，支持事务和行级锁定，事务处 理、回滚、崩溃修复能力和多版本并发控制的事务安全，比 MyISAM处理速度稍慢、支持外键（FOREIGN KEY）
ISAM	MyISAM的前身，MySQL5.0以后不再默认安装
MRG_MyISAM（MERGE）	将多个表联合成一个表使用，在超大规模数据存储时很有用
Memory	内存存储引擎，拥有极高的插入，更新和查询效率。但是会占用和 数据量成正比的内存空间。只在内存上保存数据，意味着数据可能 会丢失
Falcon	一种新的存储引擎，支持事物处理，传言可能是InnoDB的替代者
Archive	将数据压缩后进行存储，非常适合存储大量的独立的，作为历史记 录的数据，但是只能进行插入和查询操作
CSV	CSV 存储引擎是基于 CSV 格式文件存储数据(应用于跨平台的数据 交换)

xtraDB存储引擎是由Percona公司提供的存储引擎，该公司还出品了Percona Server这个产品，它是基 于MySQL开源代码进行修改之后的产品。 阿里对于Percona Server服务器进行修改，衍生了自己的数据库（alisql）。

InnoDB和MyISAM存储引擎区别：

	Innodb	Myisam
存储文件	.frm 表定义文件  .ibd 数据文件和索引文件	.frm 表定义文件  .myd 数据文件  .myi 索引文件
锁	表锁、行锁	表锁
事务	支持	不支持
CRDU	读、写	读多
count	扫表	专门存储的地方 （加where也扫表）
索引结构	B+ Tree	B+ Tree
外键	支持	不支持

存储引擎的选型：

  InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比 较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的 更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚 （rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择 MyISAM能实现处理高效率。如果应用的完整性、并发性要求比 较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度， 对数据的安全性要求较低，不需要持久保存，可以选择MEMOEY。它对表的大小有要求，不能建立太大 的表。所以，这类数据库只使用在相对较小的数据库表。 

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择 InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询 的临时表，可以选择MEMORY存储引擎。

---

 

详细执行流程图

 

---

 

物理结构 

• MySQL是通过文件系统对数据和索引进行存储的。 
• MySQL从物理结构上可以分为日志文件和数据索引文件。 
• MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。 
• 日志文件采用顺序IO方式存储、数据文件采用随机IO方式存储。

日志文件 

      错误日志（errorlog）: 默认是开启的，而且从5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误 信息,以及 MySQL每次启动和关闭的详细信息。 

      二进制日志（bin log）:记录数据变化 binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描 述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息。如果是DDL语句，则直接记录到 binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中。 生产中开启binlog，进行数据备份、恢复、主从同步。

     通用查询日志（general query log）: 啥都记录 耗性能 生产中不开启

     慢查询日志（slow query log）: SQL调优 定位慢查询select语句。 默认是关闭的。 需要通过以下设置进行开启：

            

--开启慢查询日志 
slow_query_log=ON 
--慢查询的阈值 
long_query_time=3 
--日志记录文件如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不 是绝对路径名，文件则写入数据目录。 
slow_query_log_file=file_name

记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句。

      重做日志（redo log） 

      回滚日志（undo log） 

      中继日志（relay log）

查看日志开启情况：

show variables like 'log_%';

数据文件

SHOW VARIABLES LIKE '%datadir%';

InnoDB数据文件

     .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息 

     .ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。

    . ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文 件。

MyIsam数据文件 

    .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息 

    .myd文件：主要用来存储表数据信息。 

    .myi文件：主要用来存储表数据文件中任何索引的数据树。