Mysql深入了解---索引

索引 —采用B+Tree 存储

• 索引是帮助mysql高效获取数据的排好序的数据结构
• 其实索引除了使用B+Tree存储 还可以用 Hash表存储
  expain: 前面加expain 分析sql执行的情况

B+Tree

• 多叉平衡树
• 节点中的数据从左到右依次递增
• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用双向指针连接，提高区间访问的性能(范围查找)

B-Tree

• 每一个节点都存储data，没有冗余索引。
• 没有双向指针

为什么B+Tree把data都移到叶子节点，并且增加双向链表？(B+Tree比B-Tree的改进)
将data移动到叶子节点，非叶子节点存储的索引数变多，在高度相等的情况下，能存储的data更多，查询效率自然就高了

为什么使用B+Tree而不使用红黑树或者跳表
局部性原理： 数据和程序都有聚集成群的侵向。之前被读取过的数据，很可能很快被下一次读取
磁盘预读原理： 内存和磁盘交互的基本单元是页，每次读取都是页的整数倍
最最主要的问题是和操作系统中磁盘的I/O有关。数据库利用了磁盘预处理原理，将单个节点设置为页的整数倍的大小(磁盘存储的基本单位)，每次读取一个节点就相当于一次I/O操作。B+Tree读取一个节点所需要加载的节点个数比红黑树，跳表要少的多(红黑树和跳表加载一个节点需要加载很多父节点)，并且B+Tree的根节点加载后的重复利用率也高

Hash表

• 将索引值与行对应地址存成键值，值通过运算得到地址，取到对应行的地址
• 索引的效率看似比B+Tree 还要高， 但是当遇到范文查找时 > < 等，hash索引便失效了

索引的分类

按数据结构分：

• B+Tree数索引
• hash索引
• FULL TEXT 索引 (全文索引，尽量不要答 没什么用)

按逻辑分：

• 主键索引 一个表只有一个 不允许为空
• 唯一索引 列值必须唯一，允许有空值
• 常规索引
• 全文索引 快速定位特殊数据 select * from table where MATCH(列) AGAINST(‘%关键字%’)
• 联合索引

联合索引

• 联合索引的排序是根据排列的优先顺序依次进行比较排序
• 最左前缀原则，查询时必须含有左边的列，才会走索引 。因为联合索引的排序是根据排列的优先顺序进行排序的，假如没有用第一个字段单看第二个字段是没有排序的，根本走不了索引，还是需要全表扫描。

按物理存储分：

• 聚集索引
• 非聚集索引

聚集索引

• 索引和数据存储在一块
• 只有InnoDB引擎的主键是聚集索引

非聚集索引

• 索引文件和数据文件是分离的，
• MyISAM在俩张表。B+Tree的叶子节点是存储的数据的地址，innoDB的叶子节点存储的数据是主键的ID

索引原则

1. 索引不是越多越好
2. 不要在经常变动的数据加索引(尤其是update)，维护起来非常复杂
3. 小数据量的表不需要加索引
4. 索引一般加在常用来查询的字段上

数据库data文件夹中的各个文件：

一个文件夹对应一个数据库
table.frm 表定义信息
table.ibd 数据和索引的存储 (InnoDB存储引擎)
table.MYD 数据存储
table.MYI 索引存储

存储引擎

存储引擎是形容数据库表的！！

InnoDB

• 表数据文件本身就是按B+Tree组织的一个索引结构的文件
• 聚集索引-叶节点包含了完整的数据记录
• 主键索引是聚集索引，data存储完整数据，辅助索引非聚集索引，data存储主键id
• 支持事务
• 支持外键
• InnoDB存储引擎 是支持hash索引的，不过，我们必须启用，hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了

为什么InnoDB的表必须要建主键？
如果InnoDB表没有建主键，则mysql会自动为表添加主键。
InnoDB是根据主键组织的一个索引结构文件，所以必须有主键
为什么InnoDB推荐使用整形的自增主键？
在根据索引查找时，会跟索引进行大小的比较，整形适合大小的比较，而uuid效率低
整形占用内存必uuid要小的多，节约成本(主要是占用运行时内存的空间)
并且整形自增对insert语句带来的维护索引的成本要低，insert语句执行也会更快

MyISAM

• MyISAM索引文件和数据文件是分离的(非聚集)
• 主索引和辅助索引都是非聚集索引，data存地址
• 不支持事务
• 不支持外键

存储引擎对比

存储引擎	InnoDB	MyISAM
事务支持	支持	不支持
数据行锁定	支持	不支持(是用表锁)
外键约束	支持	不支持
全文索引	不支持	支持
表空间大小	较大约为MyISAM的俩倍	较小
索引	主键索引采用聚集索引,辅助索引的data存主键的值	主索引和辅助索引一样，data全是存地址 ，但是主索引的值唯一

memory

• 磁盘文件中只存储表的结构。而其数据文件，都是存储在内存中
• 默认使用哈希索引，也可以使用B+tree索引
• 重启或关机，数据会消失，用的很少

常规使用操作：

• MyISAM 节约空间
• INNODB 安全性高，事务的处理，多表多用户操作
  在物理上的区别：
• InnoDB在数据库中只有一个.frm文件，以及上级目录下的ibdata1文件
• MyISAM对于文件 .frm 表结构文件，.MYD 数据文件 .MYI 索引文件

索引设置的原则

1. 对查询频率高，数据量比较大的建立索引
2. 索引字段经常选择 where子句中最常用，过滤效果最好的
3. 使用唯一索引，区分度越高，使用索引的效率越高
4. 索引不是多多益善，对增删改比较频繁的表，不要建立索引
5. 使用最左前缀，N个列组合而成的组合索引，相当于是创建了N个索引，如果where子句中使用了索引的前几个字段，则可以用此来提高效率

有关索引的专有名次解释

回表：

• 针对聚型索引。从某一个索引中获取到主键id，在从聚型索引中获取到全量的值
  索引覆盖：
• 当查询的是主键id和索引节点时，一次查询可以直接获取到这俩个值
  最左匹配：
• 1 2 3为联合索引，查询条件为1 2可以走索引查询，查询条件为2 1 同样可以走索引查询（mysql优化器会进行优化，选择合适的顺序来执行）
• 其实关于会不会走索引，并不是一定满足最左原则，其实跟查询的列有关。如果查询的列满足索引覆盖，即使破坏了最左原则，也会走索引
  索引下推：
• 现在的mysql都是默认开启索引下推的

CREATE TABLE t (
  col1 INT,
  col2 INT,
  col3 VARCHAR(100),
  INDEX idx_col1_col2 (col1, col2)
);
SELECT * FROM t WHERE col1 = 10 AND col2 > 5 AND col3 = 'example';

没有索引下推时：

1. MySQL通过索引idx_col1_col2找到col1 = 10的所有行。
2. 对于找到的每一行，通过回表读取实际数据行。
3. 对所有读取的数据行应用col2 > 5 AND col3 = 'example’的条件进行过滤。

有索引下推时：

1. MySQL通过索引idx_col1_col2找到col1 = 10的所有行。
2. 在索引级别应用col2 > 5的条件进行初步过滤，只保留符合col2 > 5的索引记录。
3. 对初步过滤后的索引记录进行回表操作，读取实际数据行。
4. 对读取的数据行应用col3 = 'example’的条件进行最终过滤。