一般对索引有以下认知：
- 索引可以加快数据库的检索速度
- 表经常进行
INSERT/UPDATE/DELETE操作就不要建立索引了，换言之：索引会降低插入、删除、修改等维护任务的速度。 - 索引需要占物理和数据空间。
- 了解过索引的最左匹配原则
- 知道索引的分类：聚集索引和非聚集索引
- Mysql支持Hash索引和B+树索引两种
看起来好像啥都知道，但面试让你说的时候可能就GG了：
- 使用索引为什么可以加快数据库的检索速度啊？
- 为什么说索引会降低插入、删除、修改等维护任务的速度。
- 索引的最左匹配原则指的是什么？
- Hash索引和B+树索引有什么区别？主流的使用哪一个比较多？InnoDB存储都支持吗？
- 聚集索引和非聚集索引有什么区别？
- ........
1、索引的基础知识
首先Mysql的基本存储结构是页(记录都存在页里边)：
- 各个数据页可以组成一个双向链表
- 而每个数据页中的记录又可以组成一个单向链表
- 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
- 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
所以说，如果我们写select * from user where username = 'Java3y'这样没有进行任何优化的sql语句，默认会这样做：
- 定位到记录所在的页
- 需要遍历双向链表，找到所在的页
- 从所在的页内中查找相应的记录
- 由于不是根据主键查询，只能遍历所在页的单链表了
很明显，在数据量很大的情况下这样查找会很慢！
2、主键策略
这两点都是必须遵守的。
- 主键不可修改
对于数据库来说，主键其实是可以修改的，只要不和其他主键冲突就可以。但是，对于应用来说，如果一条记录要修改主键，那就会出大问题。
因为主键的第二个作用是让其他表的外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须全部修改外键。很多Web应用的数据库并不是强约束（仅仅引用主键但并没有设置外键约束），修改主键会导致数据完整性直接被破坏。
- 业务字段不可用于主键
所有涉及到业务的字段，无论它看上去是否唯一，都决不能用作主键。例如，用户表的Email字段是唯一的，但是，如果用它作主键，就会导致其他表到处引用Email字段，从而泄露用户信息。
类似的，看上去唯一的用户名、身份证号等，也不能用作主键。对这些唯一字段，应该加上unique索引约束。
此外，修改Email实际上是一个业务操作，这个操作就直接违反了上一条原则。
那么，主键应该使用哪个字段呢？
主键必须使用单独的，完全没有业务含义的字段，也就是主键本身除了唯一标识和不可修改这两个责任外，主键没有任何业务含义。
3、索引提高检索速度
索引做了些什么可以让我们查询加快速度呢？
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过“目录”就可以很快地定位到对应的页上了！
其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。
Innodb推荐使用自增主键作为id：
也是因为，这样增加索引的时候，只需要往b+树的右边插入
3、索引降低增删改的速度
B+树是平衡树的一种。
平衡树：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。
如果一棵普通的树在极端的情况下，是能退化成链表的(树的优点就不复存在了)
B+树是平衡树的一种，是不会退化成链表的，树的高度都是相对比较低的(基本符合矮矮胖胖(均衡)的结构)【这样一来我们检索的时间复杂度就是O(logn)】！从上一节的图我们也可以看见，建立索引实际上就是建立一颗B+树。
- B+树是一颗平衡树，如果我们对这颗树增删改的话，那肯定会破坏它的原有结构。
- 要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度
B+树删除和修改具体可参考：
4、哈希索引
除了B+树之外，还有一种常见的是哈希索引。
哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
- 本质上就是把键值换算成新的哈希值，根据这个哈希值来定位。
看起来哈希索引很牛逼啊，但其实哈希索引有好几个局限(根据他本质的原理可得)：
- 哈希索引也没办法利用索引完成排序
- 不支持最左匹配原则
- 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
- 不支持范围查询
5、InnoDB支持哈希索引吗？
主流的还是使用B+树索引比较多，对于哈希索引，InnoDB不算是支持hash索引，因为索引一般用来存储数据的，但是这个哈希索引使用来快速找到内存中的页的！
6、聚集和非聚集索引
简单概括：
- 聚集索引就是以主键创建的索引
- 非聚集索引就是以非主键创建的索引
区别：
- 聚集索引在叶子节点存储的是表中的数据
- 非聚集索引在叶子节点存储的是主键和索引列
- 使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表查询)
非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了~
非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。
- 此时就涉及到了哪个列会走索引，哪个列不走索引的问题了(最左匹配原则-->后面有说)
- 创建多个单列(非聚集)索引的时候，会生成多个索引树(所以过多创建索引会占用磁盘空间)
在创建多列索引中也涉及到了一种特殊的索引-->覆盖索引
- 我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值
- 最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
- 覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！
比如说：
- 现在我创建了索引
(username,age)，在查询数据的时候：select username , age from user where username = 'Java3y' and age = 20。 - 很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~
- 所以，能使用覆盖索引就尽量使用吧~
7、索引最左匹配原则
最左匹配原则：
- 索引可以简单如一个列
(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。 - 最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配
- 因此，列的排列顺序决定了可命中索引的列数。
例子：
-
select * from t where a=1 and b=1 and c =1; #这样可以利用到定义的索引（a,b,c）,用上a,b,c select * from t where a=1 and b=1; #这样可以利用到定义的索引（a,b,c）,用上a,b select * from t where b=1 and a=1; #这样可以利用到定义的索引（a,b,c）,用上a,c（mysql有查询优化器） select * from t where a=1; #这样也可以利用到定义的索引（a,b,c）,用上a select * from t where b=1 and c=1; #这样不可以利用到定义的索引（a,b,c） select * from t where a=1 and c=1; #这样可以利用到定义的索引（a,b,c），但只用上a索引，b,c索引用不到也就是说通过最左匹配原则你可以定义一个联合索引，但是使得多数查询条件都可以用到该索引。 值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。也就是：
select * from t where a=1 and b>1 and c =1; #这样a,b可以用到（a,b,c），c索引用不到这条语句只有 a,b 会用到索引，c 都不能用到索引。这个原因可以从联合索引的结构来解释。
但是如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引，因为优化器会自动改写为最优查询语句
select * from t where a=1 and b >1 and c=1; #如果是建立(a,c,b)联合索引，则a,b,c都可以使用索引 #优化器改写为 select * from t where a=1 and c=1 and b >1; 这也是最左前缀原理的一部分，索引index1:(a,b,c)，只会走a、a,b、a,b,c 三种类型的查询，其实这里说的有一点问题，a,c也走，但是只走a字段索引，不会走c字段。 另外还有一个特殊情况说明下，select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的也只会有 a与b 走索引，c不会走。 select * from table where a = '1' and b > ‘2’ and c='3' 这种类型的sql语句，在a、b走完索引后，c肯定是无序了，所以c就没法走索引，数据库会觉得还不如全表扫描c字段来的快。 以index （a,b,c）为例建立这样的索引相当于建立了索引a、ab、abc三个索引。一个索引顶三个索引当然是好事，毕竟每多一个索引，都会增加写操作的开销和磁盘空间的开销。
总结
在 InnoDB 中联合索引只有先确定了前一个（左侧的值）后，才能确定下一个值。如果有范围查询的话，那么联合索引中使用范围查询的字段后的索引在该条 SQL 中都不会起作用。值得注意的是，in 和 = 都可以乱序，比如有索引（a,b,c），语句 select * from t where c =1 and a=1 and b=1，这样的语句也可以用到最左匹配，因为 MySQL 中有一个优化器，他会分析 SQL 语句，将其优化成索引可以匹配的形式，即 select * from t where a =1 and a=1 and c=1
8、 =、in自动优化顺序
不需要考虑=、in等的顺序，mysql会自动优化这些条件的顺序，以匹配尽可能多的索引列。
例子：
- 如有索引
(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。
9、索引总结
索引在数据库中是一个非常重要的知识点！上面谈的其实就是索引最基本的东西，要创建出好的索引要顾及到很多的方面：
- 1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询
（>,<,BETWEEN,LIKE）就停止匹配。 - 3，尽量选择区分度高的列作为索引，区分度的公式是
COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。 - 4，索引列不能参与计算，尽量保持列“干净”。比如，
FROM_UNIXTIME(create_time) = '2016-06-06'就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ：create_time = UNIX_TIMESTAMP('2016-06-06')。 - 5，尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
- 6，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。
扫一扫
975
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
