Mysql-索引

数据页的结构

大量的数据页是按顺序一页一页存放的，然后两两相邻的数据页之间会采用双向链表的格
式互相引用，大致看起来如下图所示。

没有索引的话, 如何查询数据?

每个数据页里都会有一个页目录，里面根据数据行的主键存放了一个目录，同时数据行是被分散存储到不同的槽位里去的，所以实际上每个数据页的目录里，就是这个页里每个主键跟所在槽位的映射关系，如下图所示。

所以假设你要根据主键查找一条数据，而且假设此时你数据库里那个表就没几条数据，那个表总共就一个数据页，那么就太简单了！首先就会先到数据页的页目录里根据主键进行二分查找.
然后通过二分查找在目录里迅速定位到主键对应的数据是在哪个槽位里，然后到那个槽位里去，遍历槽位里每一行数据，就能快速找到那个主键对应的数据了。每个槽位里都有一组数据行，你就是在里面遍历查找就可以了。
但是假设你要是根据非主键的其他字段查找数据呢？
那就尴尬了，此时你是没办法使用主键的那种页目录来二分查找的，只能进入到数据页里，根据单向链表依次遍历查找数据了，这就性能很差了。
好，那么现在假如我们有很多数据页呢？
其实上述操作过程，就是全表扫描，在你没有任何索引数据结构的时候，无论如何查找数据，说白了都是一个全表扫描的过程，就是根据双向链表依次把磁盘上的数据页加载到缓存页里去，然后在一个缓存页内部来查找那条数据。
最坏的情况下，你就得把所有数据页里的每条数据都得遍历一遍，才能找到你需要的那条数据，这就是全表扫描！

页分裂

每个数据页中, 数据行的主键是从小到大排序的, 并且下一个数据页中主键是要比上一个数据页中数据行主键大的, 如果我们自定义主键, 下一个数据页中主键比上一个数据页中主键大, 就要重排序, 就是所谓的页分裂.

主键索引

对主键的索引实际上就是主键目录，这个主键目录呢，就是把每个数据页的页号，还有数据页里最小的主键值放在一起，组成一个索引的目录，如下图所示。

假设你有很多的数据页，在主键目录里就会有很多的数据页和最小主键值，此时你完全可以根据二分查找的方式来找你要找的id到底在哪个数据页里！所以这个效率是非常之高的，而类似上图的主键目录，就可以认为是主键索引.
但是现在问题来了，你的表里的数据可能很多很多，比如有几百万，几千万，甚至单表几亿条数据都是有可能的，所以此时你可能有大量的数据页，然后你的主键目录里就要存储大量的数据页和最小主键值，这怎么行呢？
所以在考虑这个问题的时候，实际上是采取了一种把索引数据存储在数据页里的方式来做的
也就是说，你的表的实际数据是存放在数据页里的，然后你表的索引其实也是存放在页里的，此时索引放在页里之后，就会有索引页，假设你有很多很多的数据页，那么此时你就可以有很多的索引页，此时如下图所示

但是现在又会存在一个问题了，你现在有很多索引页，但是此时你需要知道，你应该到哪个索引页里去找你的主键数据，是索引页20？还是索引页28？这也是个大问题于是接下来我们又可以把索引页多加一个层级出来，在更高的索引层级里，保存了每个索引页和索引页里的最小主键值，如下图所示。

现在就好了，假设我们要查找id=46的，直接先到最顶层的索引页35里去找，直接通过二分查找可以定位到下一步应该到索引页20里去找，接下来到索引页20里通过二分查找定位，也很快可以定位到数据应该在数据页8里，再进入数据页8里，就可以找到id=46的那行数据了。
那么现在问题再次来了，假如你最顶层的那个索引页里存放的下层索引页的页号也太多了，怎么办呢？
此时可以再次分裂，再加一层索引页，比如下面图里那样子，大家看看下图

不知道大家有没有发现索引页不知不觉中组成了多个层级，搞的是不是有点像一棵树？
没错了，这就是一颗B+树，属于数据结构里的一种树形数据结构，所以一直说MySQL的索引是用B+树来组成的，其实就是这个意思。
我们就以最简单最基础的主键索引来举例，当你为一个表的主键建立起来索引之后，其实这个主键的索引就是一颗B+树，然后当你要根据主键来查数据的时候，直接就是从B+树的顶层开始二分查找，一层一层往下定位，最终一直定位到一个数据页里，在数据页内部的目录里二分查找，找到那条数据。这就是索引最真实的物理存储结构，采用跟数据页一样的页结构来存储，一个索引就是很多页组成的一颗B+树。

主键索引也是聚簇索引

最下层的索引页，都是会有指针引用数据页的，所以实际上索引页之间跟数据页之间是有指针连接起来的，如下图。

如果一颗大的B+树索引数据结构里，叶子节点就是数据页自己本身，那么此时我们就可以称这颗B+树索引为聚簇索引！
如果你的数据页开始进行页分裂了，他此时会调整各个数据页内部的行数据，保证数据页内的主键值都是有顺序的，下一个数据页的所有主键值大于上一个数据页的所有主键值。
同时在页分裂的时候，会维护你的上层索引数据结构，在上层索引页里维护你的索引条目，不同的数据页和最小主键值。

针对主键之外的字段建立的二级索引

比如你基于name字段建立了一个索引，那么此时你插入数据的时候，就会重新搞一颗B+树，B+树的叶子节点也是数据页，但是这个数据页里仅仅放主键字段和name字段。
name字段的索引B+树里，叶子节点的数据页中的name值都是按大小排序的，同时下一个数据页里的name字段值都大于上一个数据页里的name字段值，这个整体的排序规则都跟聚簇索引按照主键的排序规则是一样的。

此时针对select * from table where name='xx’这样的语句，你先根据name字段值在name字段的索引B+树里找，找到叶子节点也仅仅可以找到对应的主键值，而找不到这行数据完整的所有字段。
所以此时还需要进行“回表”，这个回表，就是说还需要根据主键值，再到聚簇索引里从根节点开始，一路找到叶子节点的数据页，定位到主键对应的完整数据行，此时才能把select *要的全部字段值都拿出来。
因为我们根据name字段的索引B+树找到主键之后，还要根据主键去聚簇索引里找，所以一般把name字段这种普通字段的索引称之为二级索引，一级索引就是聚簇索引，这就是普通字段的索引的运行原理。
其实我们也可以把多个字段联合起来，建立联合索引，比如name+age
此时联合索引的运行原理也是一样的，只不过是建立一颗独立的B+树，叶子节点的数据页里放了id+name+age，然后默认按照name排序，name一样就按照age排序，不同数据页之间的name+age值的排序也如此。

四种索引类型

1. normal普通索引(默认)

用于加速, 没有任何限制, 值可以重复

2. SPATIAL空间索引

空间索引是MYISAM存储引擎中使用的.

3. FULLTEXT全文索引

更像一个搜索引擎, 用来搜索文本中包含的关键字, 用于MYISAM存储引擎

4. UNIQUE唯一索引

值必须唯一, 但是可以为空

两种方法

1. HASH

hash索引只有Memory存储引擎中才支持
HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高

2. BTREE

INNODB存储引擎都是用的BTree索引.

索引优化

什么情况下不适合创建索引

1. 频繁被修改的字段
2. 数据区分度不高的字段, 如性别

索引命中问题

1. like %开头不会命中索引
2. or老版本必须所有条件都要加索引才会命中, 新版本可以命中, 一般尽量少使用or, 尽量使用in
3. 负向条件如!=, <>, not in, not exists, not null等, 不会命中, 尽量使用in
4. 联合索引最左匹配查询, 如 a,b,c三个字段 a | (a, b) | (a, b, c) 才会命中索引

• 联合索引, 区分度最高的字段在最左边
• 建立了(a, b)索引, 就不必再建立a索引了
  存在等号和非等号查询条件时, 即使非等号的区分度再高, 建立索引也要把等号的放在最前面
  最左侧查询需求, 并不是sql语句中where顺序要和联合索引一致, 但是一般要保持这个良好习惯

5. 范围条件>, <, between等会用到索引, 但是范围列后的其他列不能用到索引

假如有联合索引 (empno、title、fromdate)，那么下面的 SQL 中 emp_no 可以用到索引，而 title 和 from_date 则使用不到索引。select * fromemployees.titles where emp_no < 10010’ and title='Senior Engineer’and from_date between ‘1986-01-01’ and ‘1986-12-31’

6. where中用计算属性不能命中索引
7. 强制类型转换不能命中索引

如字符串类型的数字, 必须要加引号, ‘5’

优化索引

1. 利用覆盖索引查询, 避免回表查询
2. 有order by, group by的场景, 注意索引的有序性

对于语句 where a=? and b=? order by c，可以建立联合索引(a,b,c)
如果索引中有范围查找，那么索引有序性无法利用，如 WHERE a>10 ORDER BY b;，索引(a,b)无法排序。
索引列不要存在null值, 不然可能存在一些不符合预期的结果.

3. 业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。

不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

4. 超过三个表最好不要 join

需要 join 的字段，数据类型必须一致，多表关联查询时，保证被关联的字段需要有索引。

5. 如果明确知道只有一条结果返回，limit 1 能够提高效率
6. 性能优化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好

• consts：单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
• ref：使用普通的索引（Normal Index）。
• range：对索引进行范围检索。
• 当 type=index 时，索引物理文件全扫，速度非常慢。

7. 单表索引建议控制在5个以内
8. 单索引字段数不允许超过5个

创建索引时避免以下错误观念

• 索引越多越好，认为一个查询就需要建一个索引。

• 宁缺勿滥，认为索引会消耗空间、严重拖慢更新和新增速度。

• 抵制惟一索引，认为业务的惟一性一律需要在应用层通过“先查后插”方式解决。

• 过早优化，在不了解系统的情况下就开始优化。