关于数据库知识点的深度探析(续)

续上篇把数据库剩下的点补充完

数据库知识点（续）

上篇发现写着写着字太多，想了下还是以问答的方式来总结。

---

数据库索引

什么是索引？怎么进行查询优化？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。
换句话说，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

利用索引进行查询优化时，有以下几大原则：

• 最左前缀匹配原则
  mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配
• =和in可以乱序
• 尽量选择区分度高的列作为索引
• 索引列不要参与计算
• 尽量的扩展索引，少新建索引

有哪些索引？什么是聚合索引？索引覆盖又是什么？

MySQL目前主要有以下几种索引类型：

1. 普通索引
   最基本的索引，它没有任何限制。
   CREATE INDEX index_name ON table(column(length))
2. 唯一索引
   索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
   CREATE UNIQUE INDEX indexName ON table(column(length))
3. 主键索引
   特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引（primary key）。
4. 组合索引
   指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。
5. 全文索引
   主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like(目前只有char、varchar，text 列上可以创建全文索引)。

聚集索引：
定义：<u>数据行的物理顺序与索引的逻辑顺序相同</u>，一个表中只能拥有一个聚集索引。

非聚集索引和聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，再使用主键的值通过聚集索引查找到需要的数据。
不管以任何方式查询表，最终都会利用主键通过聚集索引来定位到数据，聚集索引（主键）是通往真实数据所在的唯一路径。

但凡是总有例外，这里就引出覆盖索引：
我们知道，当为字段建立索引以后，字段中的内容会被同步到索引之中，如果为一个索引指定两个字段，那么这个两个字段的内容都会被同步至索引之中。
先看这个SQL语句

//建立索引
create index index_birthday_and_user_name on user_info(birthday, user_name);
//查询生日在1991年11月1日出生用户的用户名
select user_name from user_info where birthday = '1996-09-24'

通过非聚集索引index_birthday_and_user_name查找birthday等于1991-09-24的叶节点的内容，然而，叶节点中除了有user_name表主键ID的值以外，user_name字段的值也在里面，因此不需要通过主键ID值的查找数据行的真实所在，直接取得叶节点中user_name的值返回即可。

存储结构

mysql是怎么存储数据的？

在 InnoDB 存储引擎中，所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）
同一个数据库实例的所有表空间都有相同的页大小；默认情况下，表空间中的页大小都为 16KB。
MySQL 使用 InnoDB 存储表时，会将表的定义和数据索引等信息分开存储，其中前者存储在 .frm 文件中，后者存储在 .ibd 文件中。

数据存储

• .frm 文件
  无论在 MySQL 中选择了哪个存储引擎，所有的 MySQL 表都会在硬盘上创建一个 .frm 文件用来描述表的格式或者说是定义；.frm 文件的格式在不同的平台上都是相同的。
• .ibd 文件
  有两个部分，一是系统表空间文件，其中存储了 InnoDB 系统信息和用户数据库表数据和索引，是所有表公用的。

详见数据库磁盘存储

SQL执行过程

讲一讲一条SQL语句怎么执行的？

1. 首先通过连接器。主要负责将 mysql 客户端和服务端建立连接，连接成功后，会获取当前连接用户的权限。这里获取到的权限对整个连接都有效。
2. 查询缓存。连接成功后，即开始要正式执行SQL语句了，但是在执行查询之前，mysql 会去看下有没有该条语句的缓存内容(大小写敏感的hash查找)，如果有缓存直接从缓存中读取并返回数据，不再执行后面的步骤了，结束查询操作。
3. 分析器。先对 sql 语句进行解析。分析器主要对 sql 语句进行语法和语义分析，检查单词是否拼写错误，还有检查要查询的表或字段是否存在。
4. 优化器。通过分析器之后，会基于成本的优化器cbo，从众多执行路径中选择一个成本最小的执行路径来执行计划（MySQL生成的指令树）。
5. 执行器。通过存储引擎执行指令树并返回结果。

数据库模块

数据库有哪些模块？

1. 初始化模块
   初始化模块就是在MySQL Server 启动的时候，对整个系统做各种各样的初始化操作，比如各种buffer，cache 结构的初始化和内存空间的申请，各种系统变量的初始化设定，各种存储引擎的初始化设置，等等。
2. 核心API
   核心API 模块主要是为了提供一些需要非常高效的底层操作功能的优化实现，包括各种底层数据结构的实现，特殊算法的实现，字符串处理，数字处理等，小文件I/O，格式化输出，以及最重要的内存管理部分。
3. 网络交互模块
   底层网络交互模块抽象出底层网络交互所使用的接口api，实现底层网络数据的接收与发送，以方便其他各个模块调用，以及对这一部分的维护。
4. Client& Server 交互协议模块
   实现了客户端与MySQL 交互过程中的所有协议。
5. 用户模块
   主要包括用户的登录连接权限控制和用户的授权管理。
6. 访问控制模块
   根据用户模块中各用户的授权信息，以及数据库自身特有的各种约束，来控制用户对数据的访问。
7. 连接管理、连接线程和线程管理
   连接管理模块负责监听对MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。
8. Query 解析和转发模块
   在MySQL 中我们习惯将所有Client端发送给Server 端的命令都称为query，在MySQL Server 里面，连接线程接收到客户端的一个Query 后，会直接将该query 传递给专门负责将各种Query 进行分类然后转发给各个对应的处理模块。
9. QueryCache 模块
   将客户端提交给MySQL 的Select 类query 请求的返回结果集cache 到内存中，与该query 的一个hash 值做一个对应。
10. Query 优化器模块
    根据客户端请求的query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略。
11. 表变更管理模块
12. 表维护模块
13. 系统状态管理模块
14. 表管理器
    表管理器的工作主要就是维护.frm文件，以及一个cache，该cache 中的主要内容是各个表的结构信息。
15. 日志记录模块
16. 复制模块

17. 存储引擎接口模块

    
    关系图