mysql索引

mysql索引

使用了B+树的数据结构

索引分类

唯一索引(UNIQUE)；

索引列中的值必须是唯一的，但是允许为空值。

主键索引()；

索引列中的值必须是唯一的，但是允许为空值。

普通索引(NORMAL)；

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

全文索引(FULLTEXT)；

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，
在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

空间索引(SPATIAL)

MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

前缀索引

待写。。。

MyISAM索引

MyISAM的数据文件和索引文件是分开存储的。
MyISAM使用B+树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

innodb索引

InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式，是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

InnoDB创建索引的具体规则如下：

在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

辅助索引

innodb索引中除了聚簇索引都是辅助索引，聚簇索引等同于主键索引；
主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

联合索引

最左匹配原则：

最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列a列从左到右递增排列，但是b列和c列是无序的，
b列只有在a列值相等的情况下小范围内递增有序，而c列只能在a，b两列相等的情况下小范围内递增有序。

B+树会先比较a列来确定下一步应该搜索的方向，往左还是往右。如果a列相同再比较b列。
但是如果查询条件没有a列，B+树就不知道第一步应该从哪个节点查起。

可以说创建的idx_abc(a,b,c)索引，相当于创建了(a)、（a,b）（a,b,c）三个索引。、

组合索引的最左前缀匹配原则：使用组合索引查询时，mysql会一直向右匹配直至遇到范围查询
(>、<、between、like)就停止匹配

使用方式	能否用上索引
select * from users where a = 1 and b = 2	能用上a、b
select * from users where b = 2 and a = 1	能用上a、b（有MySQL查询优化器）
select * from users where a = 2 and c = 1	能用上a
select * from users where b = 2 and c = 1	不能

覆盖索引

覆盖索引并不是说是索引结构，覆盖索引是一种很常用的优化手段。
因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取数据还需要再根据主键查询主键索引再获取到数据。
但是试想下这么一种情况，组合索引查询时，如果我只需要abc字段的，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，
而不需要回表。这种情况就是覆盖索引。

select a,b,c from test  where a=2 and b=4 and c=9;

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，
索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。使用覆盖索引，这个过程称为成位回表

推荐链接(敖 丙)