数据库

数据库

MYSQL结构

• 应用层

  • 链接处理
  • 鉴权
  • 安全管理

• 服务层

  • 安全管理
    • 备份恢复
    • 集群管理
    • 复制管理
    • …
  • 接口
    • DML
    • DDL
    • 储存过程
    • 视图
    • 触发器
    • …
  • 解析器
  • 优化器
  • 缓存

• 储存引擎层 (索引在这里，所以不同引擎有不同的索引实现方式)

  • 储存引擎
  • 物理文件
    • redolog
    • undolog
    • binlog
    • errorlog
    • querylog
    • slowlog
    • data
    • index
    • …

逻辑架构

MYSQL执行流程

• 首先连接到数据库服务器后，查询缓存。（8.0已弃用缓存。因为命中率很低）。
  • 如果有（命中），直接返回
  • 没有命中，开始查询。
• 使用解析器进行语句分析
  • 词法分析（关键字）
  • 语法分析（语法是否有误）
• 进行查询优化然后得到执行计划。（优化器）
  • 一条查询可以有很多执行方式，优化器作用是分析哪个效率比较高。
• 遵循执行计划开始执行查询操作。（调用执行引擎）
• 查询后返回结果，同时缓存。

所以整体流程：

SQL语句->查询缓存（8.0弃用）-> 解析器 -> 优化器 -> 执行器

索引

索引是一种数据结构，可以帮助我们快速的进行数据查找。 可以理解为是个目录。如果没有目录，我们就需要从头开始找。如果有了目录，可以直接找到对应位置。（不够完全准确）。索引是在引擎层实现的，不同引擎有不同的实现索引的方式。

索引即数据

使用索引的目的：

优点

• 减少磁盘IO。因为MYSQL是磁盘读取的。
• 创建唯一索引可以保证数据库表中每一行的数据唯一性
• 可以加速和表之间的链接。
• 使用分组和排序（group，orderby）的时候减少操作时间。

缺点：

• 创建和维护索引耗费时间。因为操作数据也会操作索引。
• 索引也会占用磁盘空间。
• 虽然索引加速查询时间，但是因为操作数据的时候会操作索引，所以对数据表更新的时候（添加，修改，删除）速度会变慢。因为索引也需要同时操作。

所以我们可以在进行数据操作的时候，先删除索引，操作完数据后再添加索引。

索引可能使用B树， B+树或hash等。

索引的分类

• 功能分类：
  • 普通索引
    • 在创建普通索引时，不附加任何限制条件，只是用于提高查询效率。这类索引可以创建在任何数据类型中，其值是否唯一和非空，要由字段本身的完整性约束条件决定。建立索引以后，可以通过索引进行查询。例如，在表student的字段name上建立一个普通索引，查询记录时就可以根据该索引进行查询。
  • 唯一索引
    • 使用UNIQUE参数可以设置索引为唯一性索引，在创建唯一性索引时，限制该索引的值必须是唯一的，但允许有空值。在一张数据表里可以有多个唯一索引。

    • 例如，在表student的字段email中创建唯一性索引，那么字段email的值就必须是唯一的。通过唯一性索引，可以更快速地确定某条记录。

  • 主键索引
    • 主键索引就是一种特殊的唯一性索引，在唯一索引的基础上增加了不为空的约束，也就是NOTNULL+UNIQUE，一张表里最多只有一个主键索引。
    • 这是由主键索引的物理实现方式决定的，因为数据存储在文件中只能按照一种顺序进行存储。
  • 全文索引
    • 全文索引(也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对于小的数据集，它的用处比较小。使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。例如，表student的字段information是TEXT类型，该字段包含了很多文字信息。在字段information上建立全文索引后，可以提高查询字段information的速度。
    • 全文索引典型的有两种类型:自然语言的全文索引和布尔全文索引。
    • 自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过50%的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。
• 物理实现方式：
  • 聚簇索引
    • 聚簇索引是指的针对主键构建的索引。
  • 非聚簇索引
    • 非聚簇索引是指的针对非非主键构建的索引。也叫二级索引或者辅助索引。
• 作用字段个数：
  • 单列索引
    • 在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。一个表可以有多个单列索引。
  • 联合索引
    • 联合索引是在表的多个字段组合上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如，在表中的字段id、name和gender上建立一个多列索引` idx_id_name_gender’，只有在查询条件中使用了字段id时该索引才会被使用。如果一开始使用了字段name则不会被使用。使用联合索引时遵循最左匹配（前缀）原则。

HASH结构

复杂度O(1)。所以从检索效率来看，HASH比树快。树是O(log2N)

但是为什么我们还用树呢？

• 哈希索引只可以判断 =, !=, 和 IN查询。范围查询就不行了，复杂度会变成O(N)
• 哈希索引数据存储是没有顺序的。order by 会导致重新排序
• 联合索引的情况下，哈希是把索引键合并后计算的，无法对单独一个或多个索引键查询
• 索引列重复值很多，会导致哈希冲突，就会导致需要遍历桶内链表来进行比较。非常耗时。

树

单纯使用树的话，极端情况下树会退化成链表，会导致深度很大。我们知道树的每一层在数据库内代表一次IO操作。所以效率很差

AVL树（包括红黑树）

为了解决树深度过大的问题，我们有AVL树，也就是平衡二叉树。左右两侧树深度差不超过1.

但是这个时候，虽然深度会降低，但是依旧深度很大，因为他是二叉树。为此，为了降低深度，我们可以用多叉树。来减少树的深度，增加树的广度。

B树

进一步优化AVL树且实施我们的多叉树策略，我们有B树

B树作为多路平衡查找树，它的每一个节点最多可以包括M个子节点，M称为B树的阶。每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中包括了x个关键字，那么指针数就是x+1。对于一个100阶的B树来说，如果有3层的话最多可以存储约100万的索引数据。对于大量的索引数据来说，采用B树的结构是非常适合的，因为树的高度要远小于二叉树的高度。

B树和B+树的区别：

关键字 = 索引（此处索引不仅可以是聚簇索引代表的主键，也可以是非聚簇索引当中创建索引的那个值本身）

• 最大的区别就是B+树只有叶子结点储存真实数据，其余节点只储存目录。但是B树不仅叶子节点可以储存真实数据，其余节点也可以储存。
• 从上图看到了，B树的叶子结点之间没有联系。但是B+树的叶子结点使用的是双链表链接。
• B+树中，子节点数量和关键字数量相同。B树中子节点数量是关键字数量+1.
• B+树中，关键字（索引）自己本身不仅存在于目录节点中，也存在数据节点中。而且这个节点是子节点中所有关键字中最大或者最小的值。这也就是为什么我们子节点数量和关键字数量相同。而B树则不是，他是那种间隔形的。所以会+1.

B树的优点：

• B树可以在内部节点同时存储键（目录）和值（数据），因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

B+树的优点：

• （页大小是固定的）由于B+树的内部节点只存放键（目录），不存放值（数据），因此，在一页中可以存放更多的键（目录）。一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。
• B+树的叶节点由一条链相连，因此，当需要进行一次全数据遍历的时候，B+树只需要使用O(logN)时间找到最小的一个节点，然后通过链进行O(N)的顺序遍历即可。而B树则需要对树的每一层进行遍历，这会需要更多的内存置换次数，因此也就需要花费更多的时间
• 查询效率更稳定。因为B树可能有的时候节点距离根节点近，速度快，离得远则速度慢。而B+树不存在这个问题，因为数据都在叶子结点上面。

B+树

• B+树的每一个节点都是一个数据页。页内数据用链表链接。
• 数据页之间可以不连续。所以数据页之间的实现方式是一个双向链表。
• 叶子结点（第0层，最下层）储存的是我们的真实数据。数据之间是单向链表链接。
  • 每一个叶子结点之间是链表链接。因为可能有页分裂这种情况。
• 所有的非叶子结点储存的都是目录页（也是数据页）。我们理解为一种大目录套小目录。非叶子结点只储存键值（目录索引信息）。里面每一个值是一个指针（指向一个子节点）和一个索引值（子节点中最小的关键字）
  • 目录和数据通过一个record_type字段判断。
• 数据层都会为所有的主键生成一个页目录page directory。所以用主键查找的时候可以使用二分法。

一般来讲，B+树不会超过4层。树的层次越低，我们IO次数越少。因为我们理解为，在最上层进行IO，即加载数据页，找到第二层目录，然后把第二层目录IO进来，再次查找。以此类推。所以是树的层数越少，IO次数越少。然后，假设真实数据每个页是100个，然后每个目录页存1000个目录，那么树两层就是十万，三层就是一个亿。四层就是一千亿。

B+树分为聚簇索引和非聚簇索引

聚簇索引

聚簇索引是指的针对主键构建的索引。

聚簇索引不是一种单独索引类型。而是一种数据存储方式。也就是我们上面B+树举的例子（所有真实数据都在叶子结点）。**以主键作为B+树索引的键值而构建的B+树索引，我们称之为聚集索引。换言之，在B+树中，叶子节点存储整条记录的数据，这样的索引为聚集索引。 **

注意这张图有缺陷。一个框（一个节点）的两个页之间有双向链表。这里表示的是页和页可以不连续。需要注意这一点。

特点：

1.使用主键值的大小进行数据和页的排序，这包括三个方面的含义:

• 页内的数据是按照主键的大小顺序排成一个单向链表。
• 各个存放用户数据的页（叶子节点）也是根据页中用户数据的主键值大小顺序排成一个双向链表。
• 存放目录数据的页（其他节点）分为不同的层次，在同一层次中的页也是根据页中目录数据的主键大小顺序排成一个双向链表。
• 不需要显式使用index创建索引。因为我们的数据表一定会有主键。

优点：

• 数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
• 聚簇索引对于主键的排序查找和范围查找速度非常快
• 按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。（构建索引的优点）

缺点：

• 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
• 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新·二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

限制：

• 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MylSAM并不支持聚簇索引。
• 由于数据物理存储排序方式只能有一种，所以每个MysQL的表只能有一个聚簇索引。一般情况下就是该表的主键。所以一个表只可以有一个主键。
• 如果没有定义主键，Innodb会选择非空的唯一索引代替。如果没有这样的索引，Innodb会隐式的定义一个主键来作为聚簇索引。
• 为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

非聚簇索引

非聚簇索引是指的针对非非主键构建的索引。也叫二级索引或者辅助索引。

• 针对主键进行构建索引是聚簇索引。如果对非主键进行索引构建就需要构建非聚簇索引。

• 非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

• 我们这里看到叶子结点的蓝色部分储存的都是我们数据的非主键值。我们对此创建了索引的时候这里就是非聚簇索引。这里我们蓝色部分也是按照升序排列的。对应的黄色部分是主键的值。而且我们发现，这里叶子结点仅仅储存了我们创建索引的那一列的值（蓝色部分），和该索引对应的主键（黄色部分）。没有其余数据列（紫色的值）。因为非聚簇索引不储存表中的数据。

• 假设我们现在是select *。意思是返回的时候必须返回所有的值，即黄色+蓝色+紫色。但是假设我们现在是通过非聚簇索引查找，也就是通过蓝色值查找。那么我们就会用到上面的这个树。我们会首先通过蓝色的值拿到主键（黄色的值），然后再次通过主键去找到对应的数据条目。这个过程就叫做回表。这个过程会查两次B+树。
• 非聚簇索引不影响数据在聚簇索引中的组织。所以可以有多份。也就是你可以用index建多个索引。

为什么非聚簇索引不保存数据（为何要回表）

因为每一个表可以有一个聚簇索引和多个非聚簇索引。如果非聚簇索引也保存全部表数据，那么整个表的数据会储存很多份。是无意义的。

使用聚簇索引和非聚簇索引的场景

使用聚簇索引查询效率高。因为无需回表操作。但是修改效率低。可能导致页分裂。

使用非聚簇索引修改效率高。

联合索引

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了”name，age，school”的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

联合索引把使用频繁，区分度高的放在最左侧。为了可以复用索引而不是新建索引。比如把a拓展成(a,b) 而不是新建(a,b)

覆盖索引

我们说过在使用非聚簇索引的时候需要回表。因为非聚簇索引索引储存的是索引和其对应的主键值。但是如果我们使用的是联合索引呢？我们上文也提到了，联合索引可以把两个字段对应的信息+对应主键的值存起来。（只储存对应字段+主键，并不储存全表数据）。假如我们的联合索引（非聚簇索引）在使用的时候，刚好满足了我们的查询需求，是不是就不用回表查询了呢。或者是我们不使用联合索引，但是我们在非聚簇索引查找的时候，刚好要查找的数据是主键。是不是也不用回表了呢？

举个例子，假如我们现在表有C1 C2 C3 C4 C5。C1是主键。

• 我们假如想要依照C2来查找数据，我们会给C2加一个非聚簇索引。然后找到了对应的主键然后再去聚簇索引中查找（回表）。假如我们现在是SELECT C1 FROM table WHERE C2 = ...。我们通过C2的非聚簇索引查找。但是我们发现C1刚刚好也在索引中，则不用回表。
• 假如我们现在创建了联合索引。C2和C3。我们现在是SELECT C1, C2, C3 FROM table WHERE C2 = ... AND C3 =... (注意遵循最左匹配)。我们通过C2和C3的联合索引查找，然后发现需要返回的数据C1 C2 C3全部都在这个联合索引内。所以不用回表。

最左匹配

我们上面提到了，联合索引需要符合最左匹配。什么意思？

假如我们创建的是C2 C3 C4的联合索引，那么我们使用的时候，必须按照这个顺序来。也就是下列三种情况可以触发索引：

• C2
• C2 C3
• C2 C3 C4
• C2 C4 这种情况叫索引截断，会触发索引下推功能。
• C2% C3同上
• C2% C4同上

因为储存排序的时候是按照C2排序，如果C2相等则按照C3排序，如果C3相等按照C4排序。也就是按照前一个字段的排序基础上再进行下一个字段的排序。也就是整体上，只有第一个字段的索引是绝对有序的，剩下字段的索引是相对有序。这个最左匹配可以是最左侧的N个字段或者是N个字符。

索引下推

假设我们有一个市民表。我们有一个联合索引（name, age）。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”。那么，SQL语句是这么写的select * from tuser where name like '张%' and age=10 and ismale=1

注意这里用了模糊匹配。虽然模糊匹配在通配符后置的时候可以用到索引，但是模糊匹配后面的联合索引也会失效（推测叫索引截断）。所以个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录后，一个个回表然后对比字段。（我们这里是select*，不满足索引覆盖）。

我们注意到，因为是模糊匹配了（产生了索引截断），数据库不关心age是什么。他会返回所有模糊匹配的情况然后回表查询四次。

但是我们有了索引下推后，数据库在联合索引(name, age)内部就判断了age是否是10。如果不是10就跳过，所以只需要回表两次。

索引失效场景：

• 对索引使用了左模糊匹配或者左右模糊匹配（like)
  • %data 或%data%
• 使用函数
• 进行表达式计算
• 范围条件右边的列索引失效。（>,<)
• 不等于（！=， <>)
• 隐式类型转换
• 没有遵循最左匹配原则。
• where字句中的or
  • 如果or前的条件列是索引列，or后的不是索引列，索引会失效。
• 字符串条件没有使用 ‘’
• 两列数据做比较，即便两列都创建了索引，索引也会失效。
• 查询条件使用is null时正常走索引，使用is not null时，不走索引。
• 查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
• 查询条件使用not exists时，索引失效。
• 当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。

适合创建索引的情况：

• 字段数值有唯一性限制。
  • 索引本身有约束作用。比如唯一索引和主键索引。所以如果某个字段是唯一的，可以直接创建唯一性索引或者是主键索引。
• 频繁做为WHERE查询条件的列
• 做为外键的数据列。
• 经常GROUP BY 或 ORDER BY的列
• UPDATE DELETE的WHERE条件列
• DISTINCT字段需要创建索引
• 多表JOIN链接（查询）操作，注意创建索引的细节。
  • 首先，连接表的数量尽量不要超过3张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。
  • 其次，对 WHERE 条件创建索引，因为WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有WHERE条件过滤是非常可怕的。
  • 最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如course_id在student_info表和course表中都为int(11)类型，而不能一个为int另一个为varchar类型。
• 使用列的类型小的创建索引。我们这里所说的类型大小指的就是该类型表示的数据范围的大小。
  • 我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT，BIGINT等，它们占用的存储空间依次递增，能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT 就不要使用INT。这是因为数据类型越小，在查询时进行的比较操作越快 。数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。 这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键使用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0。
• 使用字符串前缀创建索引。
  • 假设我们的字符串很长，那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时，那就意味着在对应的B+树中有这么两个问题:
    • ·B+树索引中的记录需要把该列的完整字符串存储起来，更费时。而且字符串越长，在索引中占用的存储空间越大。
    • 如果B+树索引中索引列存储的字符串很长，那在做字符串比较时会占用更多的时间。
  • 我们可以通过截取字段的前面一部分内容建立索引，这个就叫前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置，但是能定位到相应前缀所在的位置，然后根据前缀相同的记录的主键值回表查询完整的字符串值。既节约空间，又减少了字符串的比较时间，还大体能解决排序的问题。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间，如果只检索字段前面的若干字符，这样可以提高检索速度。
• 区分度高的（重复值少的）列适合做为索引。
  • 列的基数指的是某一列中不重复数据的个数，比方说某个列包含值2，5，8，2，5，8，2，5，8，虽然有9条记录，但该列的基数却是3。也就是说，在记录行数一定的情况下，列的基数越大，该列中的值越分散;列的基数越小，该列中的值越集中。这个列的基数指标非常重要，直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。
  • 联合索引把区分度高(散列性高)的列放在前面。这样可以提升性能。
  • 所以比如像性别，这种值只有几个种类的列，不适合做为索引。反而会降低数据更新速度。
• 使用最频繁的列放到联合索引的左侧
  • 这样也可以较少的建立一些索引。同时，由于”最左前缀原则”，可以增加联合索引的使用率.
• 在多个字段都要创建索引的情况下，联合索引优于单值索引。做到了索引复用。尽量扩展索引而不是新建索引。

不适合创建索引的情况：

• where中使用不到的字段，不要设置索引。（包括GROUP BY, ORDER BY)
• 数据量小的表不要使用索引
  • 因为效率替身不大，反而索引占据磁盘空间，而且可能触发回表操作。
• 有大量重复值的列不适合作为索引（高于10%）
  • 上面提到过，比如性别就不适合。
• 避免对经常更新的表（字段）创建过多的索引
  • 更新字段的时候也要更新索引，浪费性能。
  • 更新的表创建过多的索引会导致表更新的速度降低。
• 尽量不要使用无序的值作为索引
  • 因为索引是有序的。使用无序值的话，插入的时候，更新索引会导致大量的中间插入，导致页分裂（数据转移）。
• 删除很少使用的索引
• 不要定义冗余/重复索引
  • 也就是尽量做到索引复用，使用联合索引。比如index(a), index(a,b) 和 index(a,b,c) 这三个索引根据最左匹配原则相当于index(a,b,c)
  • 重复索引比如给主键增加了一个唯一索引。没有意义。

创建索引的注意事项（附加）

• 尽量设置为NOT NULL，可以让优化器优化。
• 使用数据类型小的字段。
• 取离散值大的字段。
• 索引不宜太多，一般5个以内。
  • 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；
  • 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；
  • 索引表的数据是排序的，排序也是要花时间的；
  • insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；
  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

索引优化

物理查询优化（比如使用索引，表连接）

逻辑查询优化（换一种写法）

EXPLAIN

EXPLAIN是我们提到的优化器部分的东西。可以模拟优化器执行SQL查询语句，并不会去真正的执行这条SQL，从而知道 MySQL 是如何处理你的SQL语句的。可用来分析你的查询语句或是表结构的性能瓶颈。

它能做什么？

• 表的读取顺序

• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

它里面有什么

• id
  • 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id。在有多条记录的时候可能会出现id相同的情况。因为他们用的是一个SELECT关键字。也就是几个SELECT，几个不同的ID
  • id相同的时候，执行顺序从上到下。
  • id不同的时候，如子查询（嵌套查询会有新的select）的时候，序号会递增。ID值越大的越先执行，因为需要先执行子查询，再执行外部查询。很好理解。
  • id相同+不同，id越大越先执行+从上到下顺序执行
• select_type
  • 表示该条SQL查询的类型，如子查询、联合查询等。
  • simple 简单的 select 查询，查询中不包含子查询或 union
  • primary 查询中若包含任何的子查询，最外层的查询被标记为 primary
  • subquery 在 select 或 where 中包含子查询
  • ..太多了不是重点不写了。
• table
  • 表名。表示用到了哪几张表，若出现了 derived 的情况，则表示产生了中间表，常见产生中间表的情况有子查询和联合查询等。
  • 查询的每一行记录都对应一个单表。使用了子查询或联合查询的时候就会有多条记录。
• partitions
  • 匹配的分区信息
• type 重点。
  • 针对单个表的访问方法。下面的这些种类靠前的性能越好，靠后的性能越差。
  • system
    • 表中只有一行记录（等同于系统表），这是const 类型的特列，平时不会出现，可以忽略不计
  • const
    • 表示通过索引一次就查找到了这条记录，当我们根据主键索引或者是唯一辅助索引与常数进行等值匹配的时候，对单表的访问方法就是const。因为只匹配一行数据，所以很快。
  • eq_ref
    • 唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配。在连接查询时，如果被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的(如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较），则对该被驱动表的访问方法就是eq ref
  • ref
    • 非唯一索引扫描，返回匹配某个单独值的所有行，本质也是一种索引访问，它返回某个匹配值的多行数据。当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref
  • fulltext
  • ref_or_null
    • 当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref or null
  • index_merge
  • unique_subquery
    • 是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，就是unique_subquery
  • index_subquery
  • range 只检索指定范围的行，使用一个索引来选择行，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引.
  • index full index scan，index 与 all 的区别为 index 类型只遍历索引树，这通常比 all 快，因为索引文件通常比数据文件小，也就是说 index 和 all 虽然都是读全表，但index 是从索引中读取的，而all使用硬盘中读取
  • all full table scan，将遍历全表以找到匹配的行
• possible_keys
  • 可能用到的索引
• key
  • 实际用到的索引。每张表的一次查询只会用到一个索引。
• key_Len重点。
  • 实际用到的索引的长度
• ref
  • 当使用索引列等值查询时，与索引列进行等职匹配的对象信息。
• rows重点。
  • 预估需要读取的记录条数，越少越好
• filtered
  • 一个表经过某个搜索条件过滤后剩余的记录条数百分比
• extra重点。
  • 额外信息字段。

事务

一组逻辑操作单元。使数据从一种状态转换为另外一种。

ACID

• 原子性：事务所有操作要么全部完成要么全部不完成。主要依靠undo.log日志实现，即在事务失败时执行回滚。undo.log日志会记录事务执行的SQL，当事务需要回滚时，通过反向补偿回滚数据库状态。（依靠事务日志）
• 持久性：主要依靠事务日志。修改的时候先修改日志，再对磁盘的数据修改。这样可以保证发生意外了，也可以进行恢复。
  • 主要依靠redo.log日志实现。首先，mysql持久化通过缓存来提高效率，即在select时先查缓存，再查磁盘；在update时先更新缓存，再更新磁盘。以减少磁盘IO次数，提高效率。但由于缓存断电就没了，所以需要redo.log日志。在执行修改操作时，SQL会先写入到redo.log日志，再写入缓存中。这样即使断电，也能保证数据不丢失，达到持久性。
  • redo日志是顺序写，在文件尾部进行添加操作。这样也算侧面实现了顺序写。但是数据库文件（innodb文件不是顺序写）。
• 隔离性：一个事物的执行不能被其他事务干扰。比如一个事物内部操作的数据不能被同时（并发）执行的其他事务所干扰。（就是加锁。线程安全）
• 一致性：数据前后是从一个合法状态转变为另一个合法状态。不会出现非法状态的转移。具体是和业务有关的。比如余额不能小于0，因为我们规定他不能小于0.（依靠事务日志）

数据并发问题

• 脏写
  • 事务A修改了另外一个未提交的事务B修改过的数据。
  • 比如原来的数据叫张三，事务B先把数据修改为李四，然后事务A把数据修改为王五。假如此时事务B进行了回滚，那么事务A的修改也将没有作用。也就是发现事务A修改了也提交了事务，但是数据没有变化。
  • 默认隔离级别下，事务A的更新语句会被阻塞（等待状态）。数据库默认会避免此情况。
• 脏读
  • 事务A读取了已经被事务B更新但是还未提交的数据。（读到了其他事务未提交的数据）
  • 假如事务B现在把余额从100改为200，但是此时没有提交。这时候事务A进行数据读取，他会读到一个200的余额。但是如果此时事务B发生了回滚，余额将会被还原至100。此时事务A在此前读取到的余额200将会是临时而且无效的。（过期的）
• 不可重复读
  • 事务A在一个期间内重复多次读取同一个数据，但是数据发生了变化。（前后读取数据不一致）
  • 假如事务A现在读取了一个字段，假如是100。此时事务B修改并提交了这个字段为200。这个时候A再次读取发现这个值变为了200。这就是不可重复读。
• 幻读
  • 事务A在一个期间内多次查询符合条件的记录数量，出现了前后查询到的数据条数不一致。（前后读取记录数量不一致[多了]）
  • 假如事务A现在查询账户余额大于100的记录，发现有五条。这时，事务B对这个表进行了插入。假设插入了三条账户余额大于100的记录，并且提交了（隐式）事务。此时事务A再次查询账户余额大于100的记录则会发现数据条数从五条变为八条。记录数量前后不一致了。
  • 注意 删除不算幻读。删除算作每一条记录都发生了不可重复读。

数据隔离级别

• 读未提交（read uncommitted)
  • 事务可以看到其他未提交的事务的执行结果。此隔离级别不能避免脏读，不可重复读和幻读。
• 读已提交（read committed)
  • 事物只可以看到已经提交的事物的执行结果。次级别满足基础隔离级别。可以避免脏读，但是不可以避免不可重复读和幻读。
• 可重复读（repeatable read）
  • 事务A读取到一条数据后，如果这时事务B对该数据进行了修改并提交，那么事务A再次读取该数据将不会发生改变。读取到的依旧是原来的数据。是MySQL InnoDB引擎的默认隔离级别。可以避免脏读，不可重复读。不可避免幻读。
• 串行化（serializable）
  • 会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。也就是事务执行期间，禁止其他事物对该表进行修改，插入和删除操作。所有问题都可以避免，但是性能很低。

redo log重做日志

• 提供了再写入操作。恢复提交事务修改的页操作。保证持久性。
• 我们访问页面之前，需要让磁盘把页面数据缓存到内存中的buffer pool中后在可以访问。所有的变更都必须先更新缓冲池的数据。然后缓冲池内的脏页（修改过的数据）会用一定的频率更新至磁盘。
• 所以我们修改操作会先在内存内修改数据，然后同时记录日志。如果在持久化到磁盘之前发生错误，则可以通过重做日志重做之前丢失的操作。所以是操作是在内存操作，并写入日志，然后系统等待一个合适的时机再写入磁盘。
• 重做日志也有自己的缓存。也是先写入重做日志缓存。然后再写入日志磁盘文件。
• 重做日志是循环写的。

为什么需要redolog

• 恢复在内存中但突发故障没有刷入磁盘导致丢失的操作。

  • 只要修改就把数据写入磁盘会导致大量的IO操作。哪怕修改一个字节也要读取整个页面然后再写回整个页面，非常耗费性能。
  • 随机IO刷新较慢。因为一个事务可能包含多个语句，就算一条语句也可能修改多个页面。这些页面可能不相邻。不相邻就会意味着数据库在把内存中（缓冲池）中的数据刷入硬盘的的时候会进行很多随机IO。比如机械硬盘需要巡道。

    优点

• 降低刷盘频率。
• 日志占用空间小。因为只储存表ID，页号，偏移量和需要更新的内容。

特点

redo日志是顺序写入磁盘的。因为每条日志都是在日志文件末尾进行追加。所以是顺序写入磁盘的。也就是用了顺序IO，速度较快。

undo log回滚日志

• 回滚记录到某个特定版本。保证原子性和一致性。
• 在事务中更新数据之前，需要先把对应操作的逆向操作写入回滚日志。select操作不写入回滚日志。
• undo log会产生redo log。因为回滚日志也需要持久性保护。

为什么需要undo log

• 回滚数据。
  • 注意，回滚数据是逻辑层面恢复而不会物理性恢复，也就是不会恢复原来的数据结构或者是页面具体的物理特性，比如开辟内存空间等，仅仅是把操作进行逆向操作。比如删除的数据，恢复回去。
• MVCC
  • InnoB存储引擎中MVCC的实现是通过undo来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读取。

两个日志底层的区别

• redolog是储存引擎层生成的日志。记录的是物理级别的页修改操作。比如在页号A，偏移量B的位置写入了C。
• undolog是储存引擎层生成的日志。记录的是逻辑操作。比如INSERT了一行数据，那么undolog内会记录一个相反的DELETE操作。（每一个修改的逆向操作）

redo log跟bin log 的区别，redo log是存储引擎层产生的，而bin log是数据库层产生的。假设一个事务，对表做10万行的记录插入，在这个过程中，一直不断的往redo logl顶序记录，而bin log不会记录，直到这个事务提交，才会一次写入到bin log文件中。

锁

• 从数据操作类型划分：
  • 共享锁（读锁）
    • 两个事务一起读没问题。不阻塞
  • 排他锁（写锁）
    • 在一个事物写入没有完成之前，禁止其他事务进行写入和读取操作。
  • 对于InnoDB来说，读锁和写锁可以加在表上也可以加在行上。
• 从数据操作粒度划分：
  • 锁定的数据量越少，发生锁争用的可能就越小，系统的并发程度就越高。但锁的各种操作(获取锁、释放锁、检查锁状态)都会增加系统开销。因此锁的粒度越小，系统开销就越大。因此在选择锁的粒度时，需要在锁开销和并发程度做平衡。
  • 全局锁
    • 整个数据库全部锁定。
    • 一般用于全库的逻辑备份
  • 表（级）锁
    • 表锁响应的是非索引字段。
    • 表锁会锁定整张表，是最基本的策略，不依赖存储引擎
    • 因为粒度大，所以开销小，加锁快。发生锁冲突几率高。 不会死锁。但是并发性能低
    • 意向锁
      • 为了协调行锁和表锁的关系。支持多个粒度的锁并存。
      • 举个例子。假如T1对表的某一行加锁了。如果此时T2要对整张表加锁，则需要遍历（假如有10万条，开销很大）这张表有没有某个部分已经被加锁了。如果有则需要等待。意向锁的作用就是，如果我们加了行锁，系统会在他的上一层加一个意向锁，告诉系统这张表/这个页已经有锁了。可以达到快速判断。
    • 自增锁。
      • 很好理解。一个字段设置为自增的时候我们不用显式赋值。系统会自动增添。为了保证增添这个自增字段的顺序，会有一个自增锁防止多条插入的时候对这个自增值同时修改。好理解。
    • 元数据锁。
      • 保证表结构一致
  • 页（级）锁
    • 锁定一个数据页。开销介于表锁和行锁之间。会发生死锁，并发度一般。
  • 行（级）锁
    • MYSQL的行锁依赖于索引。因为行锁要加在索引对应的行上。
    • 锁住具体的某一行（某条记录）。依赖储存引擎。
    • 锁粒度小，开销大，加锁慢。发生锁冲突几率低。会出现死锁情况，并发性能高。
    • MyISAM不支持行锁。
    • 记录锁
      • 仅仅锁住一条记录
    • 间隙锁
      • 主要是为了在RR（可重复读取）隔离级别下的幻读问题。幻读是记录前后不一致。也可用MVCC解决。间隙锁锁定一个范围，不包括记录本身
      • 因为事务执行第一次操作的时候，幻读导致的记录还不存在，导致没办法给幻读的数据加记录锁。所以我们可以在这个区间设置一个间隙锁，禁止在某个区间插入。可以避免幻读。
    • 临键锁
      • 相当于记录锁+间隙锁。锁住一个范围包括记录本身

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

解决方案：

• 一直等待直到超时。
• 判断如果一个操作会导致死锁，则进行回滚。选择成本最低的（持有最少的行级排他锁的事物回滚）。
• 尽可能的一次锁定所需要的所有资源。
• 容易产生死锁的部分可以提升锁粒度，比如使用表锁。
• 使用分布式或乐观锁

隔离级别和锁的关系

• 在Read Uncommitted级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突
• 在Read Committed级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；
• 在Repeatable Read级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁。
• SERIALIZABLE 是限制性最强的隔离级别，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

MVCC

多版本并发控制。解决并发问题（一致性读），总加锁性能低，所以用这个。

一致性读也就是快照读。也就是我读取的时候如果某个数据被更新了，我需要读取更新之前的值。这样查询的时候不需要等到另外的事务释放锁。如果读到了其他事务更新的值，那就是脏读。如果一直在等，那就是串行化了。

不加锁的简单SELECT都叫快照读。也就是不加锁的非阻塞读。

MVCC = 隐藏字段+ undolog + readview

只有InnoDB支持

零碎知识点：

• 数据库的一个页一般是16KB。
• 数据页物理上不连续，逻辑连续。是双向链表。数据页的编号也可能不是顺序的。（不然一个大数据库上哪儿弄那么多连续空间）。
• 数据页里面的记录按照主键顺序组成单向链表。
• 视图：将重复使用的联结查询的表创建为一个虚拟表，方便后续查询使用。
• 游标：数据缓冲区，用于存放语句执行结果。可以上下查看前后的行
• 储存过程：函数

可能的题：

• 为了减少IO，索引树会一次性加载吗?
  • 数据库索引是存储在磁盘上的，如果数据量很大，必然导致索引的大小也会很大，超过几个G。
  • 当我们利用索引查询时候，是不可能将全部几个G的索引都加载进内存的，我们能做的只能是:逐一加载每一个磁盘页，因为磁盘页对应着索引树的节点。所以我们选用了B+树。让磁盘IO尽可能低。
• B+树的存储能力如何?为何说一般查找行记录，最多只需1~3次磁盘IO
  • InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节)，指针类型也一般为4或8个字节，也就是说一个页(B+Tree 中的一个节点)中大概存储
  • 16KB/(8B+8B)=1K个键值(因为是估值，为方便计算，这里的K取值为10^3。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3103= 10亿条记录。(这里假定一个数据页也存储10^3条行记录数据了)
• 为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引?
  • B+树的磁盘读写代价更低。B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说Io读写次数也就降低了。树高更低，磁盘IO次数更少。
  • B+树的查询效率更加稳定。由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
  • B+树叶子结点用双向链表链接。适合范围查询。
• Hash索引B+树索引的区别
  • 我们之前讲到过B+树索引的结构，Hash索引结构和B+树的不同，因此在索引使用上也会有差别。
  • Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表。
  • Hash索引不支持联合索引的最左侧原则(即联合索引的部分索引无法使用)，而B+树可以。对于联合索引来说，Hash索引在计算Hash值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用。
  • Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引数据是有序的，可以起到对该字段ORDER BY排序优化的作用。
  • 同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面后模糊查询（比如%结尾）的话就可以起到优化作用。
  • InnoDB和MyISAM不支持哈希索引。

Redis

redis主机宕机后，从机变成主机的同时 主机恢复了，这时候会发生什么？

原有主机会变成新的主机的从节点，成为从节点之后就是清空原有数据，然后再重新和主机进行RDB文件的数据同步。