MySQL 索引

索引分析：

索引概述：

定义：

MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。
结论：索引是数据结构

结构：

MySQL支持多种存储引擎 而各种存储引擎对索引的支持也各不相同
因此MySQL数据库支持多种索引类型 如BTree索引、哈希索引、全文索引等等
目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构

利弊：

索引优点：

• 大大提高数据查询速度

索引缺点：

• 维护索引需要耗费数据库资源

• 索引要占用磁盘空间

• 当对表的数据进行增删改的时候 因为要维护索引 所以速度收到影响

原因：

为什么要使用索引呢 =>

因为 我们都希望查询数据的速度能尽可能的快 =>

但数据就是那么多想要优化只能从查询算法下手 =>

但是数据本身的数据结构不足以满足各种查询算法的数据结构
即便逻辑上相邻的记录磁盘上也并不是一定物理相邻的
(例如 理论上不可能同时将两列都按顺序进行组织) =>

所以 在数据之外 数据库系统还维护着满足特定查找算法的数据结构
这些数据结构以某种方式引用（指向）数据
这样就可以在这些数据结构上实现高级查找算法 这种数据结构 就是索引

结论：

索引不是越多越好，而是仅为那些常用的搜索字段建立索引效果才是最佳的！

索引分类：

索引类型：

聚集索引和非聚集索引的区别

简单来说：

聚集索引就是整张表最重要的索引结构
在聚集索引的叶子节点 挂着表中的行数据

而非聚集索引一般是为了优化查询而创建的
非聚集索引和实际数据是分开存放的 非聚集索引的叶子节点挂着的是聚集索引的key
所以非聚集索引最终的目的是查出聚集索引的key 再通过聚集索引查询行数据 这个过程也被称为回表查询

本质区别：

表记录的排列顺序和索引的排列顺序是否一致

聚集索引：

• 定义：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同 一个表中只能拥有一个聚集索引
• 优点：聚集索引 表记录的排列顺序 和 索引的排列顺序 保持一致 所以查询效率相当快
• 缺点：聚集索引修改起来比较慢 因为它需要保持表中记录和索引的顺序需要一致 在插入新记录的时候就会对数据也重新做一次排序

非聚集索引：

• 定义：索引的逻辑顺序 与磁盘上的 物理存储顺序 不同 一个表中可以拥有多个非聚集索引
• 优点：数据的插入不会造成 索引重新排序 写入相对较快
• 缺点：索引的层次比较多 查询比较慢

索引种类

• 主键索引：PRIMARY KEY
  • 设定为主键后 据库自动建立索引 innodb为聚集索引 主键索引列值不能有空(Null)
  • 如果没有主键 会取唯一索引字段作为聚集索引
  • 没有主键 也没有唯一索引 innodb会自动创建一个rowid的聚集索引
• 单值索引：（普通索引）
  • 即一个索引只包含单个列 一个表可以有多个单列索引
• 唯一索引：
  • 索引列的值必须唯一 但允许有空值(Null) 空值也必須唯一
• 复合索引：
  • 即一个索引可以包含多个列 多个列共同构成一个复合索引
  • eg: SELECT id (name age) INDEX WHERE name AND age;
• 全文索引：Full Text （MySQL5.7之前，只有MYISAM存储引擎支持全文索引）
  • 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在Char 、Varchar 上创建。

创建方式：

主键索引创建

-- 建表语句：建表时，设置主键，自动创建主键索引
CREATE TABLE t_user (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20)
);

-- 查看索引
SHOW INDEX FROM t_user;

单列索引创建

-- 建表时创建单列索引：
-- 这种方式创建单列索引，其名称默认为字段名称：name
CREATE TABLE t_user (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20)，
    KEY(name)
);

-- 建表后创建单列索引：
-- 索引名称为：name_index 格式---> 字段名称_index
CREATE INDEX name_index ON t_user(name)

-- 删除单列索引
DROPINDEX 索引名称 ON 表名

唯一索引创建

-- 建表时创建唯一索引：
CREATE TABLE t_user2 (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20),
    UNIQUE(name)
);

-- 建表后创建唯一索引：
CREATE UNIQUE INDEX name_index ON t_user2(name);

复合索引创建

-- 建表时创建复合索引：
CREATE TABLE t_user3 (
    id VARCHAR(20) PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    KEY(name,age)
);

-- 建表后创建复合索引：
CREATE INDEX name_age_index ON t_user3(name,age);

-- 复合索引查询的2个原则
-- 1.最左前缀原则
-- eg: 创建复合索引时，字段的顺序为 name,age,birthday
-- 在查询时能利用上索引的查询条件为： 
SELECT * FROM t_user3 WHERE name = ?
SELECT * FROM t_user3 WHERE name = ? AND age = ?
SELECT * FROM t_user3 WHERE name = ? AND birthday = ?
SELECT * FROM t_user3 WHERE name = ? AND age = ? AND birthday = ?
-- 而其他顺序则不满足最左前缀原则：
... WHERE name = ? AND birthday = ? AND age = ? -- 不满足最左前缀原则
... WHERE name = ? AND birthday = ? -- 不满足最左前缀原则
... WHERE birthday = ? AND age = ? AND name = ? -- 不满足最左前缀原则
... WHERE age = ? AND birthday = ? -- 不满足最左前缀原则


-- 2.MySQL 引擎在执行查询时，为了更好地利用索引，在查询过程中会动态调整查询字段的顺序！
-- 这时候再来看上面不满足最左前缀原则的四种情况：
-- 不满足最左前缀原则，但经过动态调整顺序后，变为：name age birthday 可以利用复合索引！
... WHERE name = ? AND birthday = ? AND age = ? 
-- 不满足最左前缀原则，也不能动态调整（因为缺少age字段），不可以利用复合索引！
... WHERE name = ? AND birthday = ? 
-- 不满足最左前缀原则，但经过动态调整顺序后，变为：name age birthday 可以利用复合索引！
... WHERE birthday = ? AND age = ? AND name = ?
-- 不满足最左前缀原则，也不能动态调整（因为缺少name字段），不可以利用复合索引！
... WHERE age = ? AND birthday = ?

数据结构：

B Tree：（多路平衡查找树）

B树弥补的红黑树节点少 层级多的问题

在B树中有阶的概念 阶是指一个节点下最多有几个子节点
下图是一个五阶的B树（N阶的B树 每个节点可以存放N个指针和N-1个KEY）
指针指向下级节点

结构图：

B+Tree：

B+树是B树的变种 可以从下图看出来结构上跟B树是大致一样的
但是B+树的每个节点数据 最终都会出现在叶子节点
叶子节点之上的层级节点都是 索引层级 做为索引的
这样的好处是 上层节点只存储指针和KEY 不存储实际的数据
所用内存更小 也就能提高阶数 =》减少层级 =》查找更迅速（查询速度也会更稳定 否则波动性较大）
且叶子节点之间是有一个单向的指针连接的

结构图：

MySQL B+Tree：

在MySQL中的B+树 是稍有变化的
MySQL中的B+树 叶子节点是一个双向链表 且数据都存放在一个页内（利于区间访问）

结构图：