索引在建表时候的形成

有规定主键索引的时候，采用主键索引。
没有规定主键索引时，采用第一列唯一非空列作为索引。
如果上述情况不存在，sql会自动生成一列隐性主键索引。

索引的分类

索引在数据结构中

主键索引的查找过程

首先从B+ Tree的根节点开始，找到属于其索引数据范围内的数据段对应的子节点，进入到下一个子节点。最后判断递归到叶子节点。
在叶子节点中，通过二分查找去寻找到对应的索引值相等的位置，或者大于小于的话可以通过B+ Tree独特的双向链表结构找到大于小于索引的值。
最后在主键索引中，B+ Tree的叶子节点存储的就是其对应的数据结构体的值。找到之后返回即可。

辅助索引的查找过程

基本等同与主键索引的查找过程，但是有一点要注意的是，在没有联合索引的情况下，辅助索引的叶子节点存储的是主键索引的索引值。所以，设置辅助索引的目的就是为了更方便的确定主键索引。这之后会有一次徽标的操作，也就是在遍历完一次辅助索引的B+ Tree之后再次遍历一次主键索引的B+ Tree。
那么在联合索引的情况下，辅助索引的叶子节点存储的就是联合索引的值了。之后再根据最左匹配原则来进行数据的查询。
优化辅助索引回表的关键在于：如果当需要的数据直接出现在叶子节点中（无论是主键索引还是辅助索引），那么sql语句解释器都会直接输出结果。那么我们就可以通过查询联合索引的方式来查询我们所需要查询的列。

例如：我们需要查询某个姓名的学生的姓名，语文成绩，数学成绩；那么我们就可以将（姓名，语文成绩，数学成绩作为联合索引）, select name, chinese, math from student where name = 'zhangsan'；那么这样就可以节约一次回表的操作了。

B+ Tree的优点

在查询效率和占用空间上来讲，其更加扁平化的结构相较于二叉搜索树来讲，在查询上结构更加高效，因为二叉搜索树只能最多有两个分支，而B+ Tree能有好多个分支。
相较于B Tree 来讲，其结构做的优化主要有两个点：
- 将所有具体的数据放在叶子节点，索引放在非叶子节点：这样相较于B Tree 中数据也放在非叶子节点可以提高查询的效率，因为我们只需要定位一个索引。
- 叶子节点以双向链表的形式进行连接，有效的方便了比较运算的查询。B Tree 中 where 条件有大于小于的需要一个个遍历，而在 B+ Tree 中只需要通过链表就可以了。

索引在物理结构中

主键索引（聚簇索引）

主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
一般主键索引都是采用递增的数字进行存储，因为这样的话对于索引的增删改查的操作的效率都能够有所提升。

普通索引（二级索引）

二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。
可以通过查询联合索引的方式来查询我们所需要查询的列，以提高查询效率。

索引在字段特性中

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

索引在字段个数中

按照字段个数分的话，可以分为单列索引和联合索引。单列索引包括主键索引，普通索引，前面都有讲过。主要这个部分梳理联合索引的知识点。

联合索引的最左匹配原则

定义：在建立联合索引的时候，由于最左匹配原则的约束，字段的顺序不同是会产生不一样的结果的。主要体现在以最左边的字段作为第一个的排序。如果 where 条件构建的时候，没有以最左边第一个排序作为条件出现的话，那么联合索引就会失效。我们可以认为联合索引中的个体字段（除了最左字段）是全局无序，局部有序的。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

where a=1；
where a=1 and b=2 and c=3；
where a=1 and b=2；

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

where b=2；
where c=3；
where b=2 and c=3；

联合索引对于普通字段查询的优化

优化辅助索引回表的关键在于：如果当需要的数据直接出现在叶子节点中（无论是主键索引还是辅助索引），那么sql语句解释器都会直接输出结果。那么我们就可以通过查询联合索引的方式来查询我们所需要查询的列。

联合索引失效的情况

这里我们可以看到，对于a来讲，是有序的，但是对于b来讲，是无序的。而一个索引要使用的条件必须是有序的。

1	select * from t_table where a > 1 and b = 2

a字段可以通过索引进行查询，但是在a > 1 的条件下，b是无序的，所以无法通过索引进行查询。所以在这个where条件中，只有a的查询能够运用得上索引。

1	select * from t_table where a >= 1 and b = 2

a和b都运用到了联合索引进行排序，原因是 a 存在等于1的情况，那么在这个情况下，a固定下来，那么所有 a = 1的行中就会执行到以 b 为索引的查询，所以此时 b 能用得上索引。但是也是仅限于 a = 1 的情况下。a > 1仍然用不上索引。

1	SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2

在mysql中，between and是大于等于，小于等于的关系，所以其实和上一个的道理是一样的。只要最左的联合索引元素有等于号出现，那么第二个元素就一定能够用得上索引。

SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？

都用到了联合索引进行排序。

索引的缺点

会占用一定的数据内存空间，索引过多可能带来数据库内存负担的加重。
创建和维护索引需要时间，这个时间会随着数据量的增大而增大。
会降低修改删除索引的效率，因为每一次对于索引进行修改时，B+ Tree 都会对于索引进行动态维护

索引的适用范围

唯一的不重复的非空字段，最好还能够是新增的时候有序排列的字段。当索引唯一的时候查询效率是比不唯一的索引效率要高的。非空是因为查询的时候还需要加上空字段会导致查询效率的降低。
经常用于查询语句 where 的字段，对于经常用于查询语句的字段建立索引，有利于提升查询效率
经常需要用于排序 order by 的字段，因为索引本身是有序的，所以提前对于排序字段建立好索引有利于执行sql语句的时候效率的提升。

索引的不适用情况

不经常用于查询，排列的字段。因为索引主要就是用于能够更好的排列顺序，如果不用那也就意味着没有必要建立索引。
重复度高的情况下不建议使用索引，最好是唯一字段才使用索引。内部优化器有一个规则，当索引重复度高于一个标准的时候，那么将会替换成为全局搜索。比如只有男和女的性别，各占50%，所以没有必要建立索引。
要经常修改的字段，因为字段的更新意味着索引的重新排序，对于数据库性能上会有一定的消耗。
数据量太少，没有必要建立索引。

优化索引的方式

前缀索引优化

使用前缀索引是为了减小索引字段大小，可以增加一个索引页中存储的索引值，有效提高索引的查询速度。在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

不过，前缀索引有一定的局限性，例如：

order by 就无法使用前缀索引；
无法把前缀索引用作覆盖索引；

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

主键索引最好是自增的

如果我们使用了自增的主键，那么我们要插入一条数据的时候，做的事情是只需要在链表的末尾加入一个元素即可，时间复杂度不高。

但是如果我们使用了非自增的主键，那么我们首先要查询这个主键的大小相较于其他大小的位置，在进行插入，那么相比于尾插法明显效率是得到了提升的。而且非自增主键还会造成内存碎片的危害，浪费存储空间。

索引列最好设置为NOT NULL

索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂.

NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式中至少会用 1 字节空间存储 NULL 值列表。

索引失效的情况

当我们使用了左模糊匹配或者左右模糊匹配的时候，比如 select * from db where name like %ming 或者 select * from db where name like %ming% 索引会失效。原因是其是按最左前缀原则进行匹配的，即使对于字段内部也是一样，从左到右进行匹配。
当对索引值进行计算或者函数运算的时候，索引会失效。因为索引是根据他本身存入的值进行排序，运算后就不是原有的值了。那么解决这个问题可以将该函数设为索引。
设置联合索引的时候没有遵循最左匹配原则。
查询条件中设置or条件语句只要有一部分没有遵循索引，那么其将会进行全表查询。道理也很简单，or是求两部分的交集，即使前面用了索引后面也要进行全表查询，所以直接全部使用全表查询。

count(1), count(*)和count(字段)哪个查询效率更高？

结论：count(1) = count(*) > count(主键字段) > count(其他字段)

count(1)如何查询

当只有主键索引没有二级索引的时候：

InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。

当存在二级索引的时候，循环遍历找值的对象就变成了二级索引了。

count(*)如何查询

count(*)在编译的时候会转换为count(0)。

count(字段)如何查询

对于这个查询来说，会采用全表扫描的方式来计数，所以它的执行效率是比较差的。因为他统计的是在表中该字段不为空的值的字段有几个。而count（常量）统计的是该表中有几条数据。