索引(详细)

索引

基础知识储备

数据一般都会持久化到磁盘，在要操作数据的时候要把磁盘中的数据读入到内存中，在内存中进行相关的处理，但是在内存和磁盘进行交互的时候，不是你想读一个字节就一个字节，有最小的逻辑单元即页，在Innodb这种存储引擎中，默认为16kb。

磁盘预读(预读的长度一般为页的整数倍)
页书存储器的逻辑块，操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，那个存储块为一页（在许多操作系统中，页大小通常为4k），主存和磁盘以页为交换单位交换数据。

索引是什么？

• 索引是帮助mysql高效获取数据的数据结构
• 索引存储在文件系统中(那么就会涉及到IO)
  需要考虑减少IO次数
• 索引的文件存储形式与存储引擎有关
• 索引的文件的数据结构
  hash（冲突且不适合范围查找）
  二叉树（不论是二叉树还是红黑树，都会因为树的深度变多而造成IO次数变多，影响数据读取效率）
  B树（每个节点放一页数据，B树非页节点存数据）
  B+树

聚簇索引和非聚簇索引

InnoDB中数据和索引合在一起，MyISAM是分开的。
使用InnoDB的时候通过一个键组织数据，有主键的话是主键，没有的话用唯一键，在没有的话使用rowid(隐藏列)，所以InnoDB不管怎么都有一个聚簇索引。

聚簇索引叶子节点data放数据，而非聚簇索引叶子节点的data放地址。

回表(会查找两棵树)

mysql默认是给唯一键创建的(唯一且非空)，只是主键是唯一键的一个特殊。
现在如果既有唯一键又有主键，主键会创建一棵树，唯一键会再创建一棵树(辅助索引或二级索引),但是叶子节点存放的不会再是整行的记录，而放的是主键(非空唯一键或者rowid)，会查找两棵树。

在根据辅助索引或者叫做二级索引进行查找的时候，如果查找辅助索引的列，会先查找根据辅助索引找到主键，再根据主键找到数据，这个过程就称为回表。

如果在数据很少的时候，有的时候使用索引比不使用索引还要慢，可能就是因为回表，因为要查找两棵树。

索引覆盖和回表

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’
select id from stu where name =""; //索引覆盖，因为根据name创建的那棵二叉树的值本身就是主键id，所以不需要再去查找。

select id from stu where name =""; //需要查两个表。

覆盖索引例子

stu表的id为主键索引，name为辅助索引

当发起一个索引覆盖查询时,在explain的extra列可以看到using index的信息

最左匹配（组合索引中使用）

如果索引列包含多个列，那么称为组合索引。

在包含多个列的查询过程中，会依靠先查第一个列，再查第二个列。

如组合索引为name、age，下面的1、2、4会走组合索引。

• 1、where name = ? and age =?
• 2、where name=？
• 3、where age=？
• 4、where age=？ and name=？

MyISAM和InnoDB区别

• 一个是聚簇索引，一个是非聚簇索引
• InnoDB支持事务，MyISAM不支持事务。
• MyISAM只支持表锁，InnoDB既支持表锁，也支持行锁。

索引的分类

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• 组合索引

索引的匹配方式

要看懂explain的计算方式，可以帮助你分析用了哪些索引

MVCC(多版本并发控制)

索引下推、谓词下推、MRR

看下