什么是索引
索引是存储引擎用了查找记录的一种数据结构
索引分类
单列索引：一个索引只包含单个列，一个表可以有多个单列索引
复合索引(多列索引)：一个索引包含多个列，比如index(name,age)，name是主导条件，先安装name进行数据结构的创建，当name相同时，再按照age排序
唯一索引：索引列的值必须唯一，可以包含多个空值
全文索引：索引的是内容中的关键词，一般用于全文检索。如果数据量很大，可以用搜索服务ES
主键索引：表会自动根据主键构建数据结构，上篇介绍的就是主键索引
索引基本操作
创建索引：create [unique|fulltext] index 索引名 on 表名 (属性名[长度][asc|desc]);
删除索引：drop index 索引名 on 表名;
查看索引：show index from 表名;
聚簇索引和非聚簇索引
以学生表为例，数据如下，id是主键：
MyISAM中的主键索引和辅助索引存储结构（假设度最大为3）：
主键索引（自动创建）,叶子节点有指针指向对应磁盘文件的数据。
辅助索引，以复合索引为例，index(name,age)，跟主键索引类似，也是叶子节点有指针指向对应磁盘文件数据。
InnoDB中的主键索引和辅助索引存储结构（假设度最大为3）：
主键索引（自动创建），叶子节点存放的是该行的数据。在磁盘中，索引和数据是放在一个文件里面的，因此InnoDB中是没有单独的学生表，主键索引就是学生表。MyISAM中索引和数据是分开的，会有单独的学生表。
InnoDB中的主键索引就是聚簇索引。
辅助索引，以复合索引为例，index(name,age)，叶子节点存放的是索引关键字+主键，通过主键再去主键索引中去查到需要的行数据
如上图，通过辅助索引找到a小明，15 这个叶子节点，如果想获取年龄信息，需要将id=1带入到主键索引结构中去查找得到id=1的行数据。这个过程叫回表。
注意点：
1、InnoDB默认使用表的主键构建成所谓的聚簇索引，如果没有主键会选择第一个非空的唯一索引代替，如果也没有，会隐式定义一个6字节的rowid主键来构建聚簇索引
2、不要使用UUID作为主键，因为UUID的随机性，会导致频繁的插入降低性能。因此大部分主键都是整型自增的。整型方便比较，自增无需在中间插入，提示了效率。
衍生概念：覆盖索引
如果索引数据结构能满足查询语句中的字段，就叫覆盖索引。
在InnoDB中，覆盖索引特别有用，比如上面的辅助索引index(name,age)，叶子节点的构成是name,age + 主键id。
如果查询语句是 select * from student where name = "小兰"，通过辅助索引查询到对应的叶子节点后，得到的数据是小兰，19 ，主键5，然后通过主键5去主键索引中查询id=5的那一行数据。
现在查询语句变成select age from student where name = "小兰"，由于叶子节点包含了age数据，就不需要回表了。这个操作就叫覆盖索引，可以提升性能。
如果语句变成select sex from student where name = "小兰"，由于sex需要回表查询，可以把索引结构变成index(name,age,sex)，适度添加字段可以构造出覆盖索引。如果为了构造覆盖索引添加的索引字段过多也会导致每个磁盘块中存储的数据个数减少，增加了树的度，反而增加了开销导致性能降低。
在MyISAM中，内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
网友评论