MySql 索引

---

1、索引本质

Mysql官方表示，索引（Index）是帮助MySQL高效获取数据排好序的数据结构，所以其实说到底，索引其实就是数据结构

2、为什么要用索引

索引用于快速找出某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySql能够快速到达一个位置去搜索文件，而不必查看所有的数据，那么将会节省很大一部分时间。你可以理解为书的目录，当我们要从一本书中找到某一章节在第几页的时候，就可以通过目录俩查询，这样比较快。

2.1 索引优点

1. 索引大大减小了服务器需要扫描的数据量
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机IO变成顺序IO
4. 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。

2.2 索引缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
2. 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
3. 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
4. 对于非常小的表，大部分情况下简单的全表扫描更高效；

2.3 索引创建原则

1. 对于经常更新的表就避免对其进行过多的索引，对于经常查询的字段应该创建索引
2. 数据量小的表最好不要使用索引，由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，这样索引可能就不会产生优化效果
3. 在唯一性差的字段上不要建立索引，比如用户表上性别字段，只有男女两个值，这种字段就不适合建立索引。

3、索引数据结构

mysql索引的数据结构是树，常用的存储引擎innodb采用的是B+Tree。
可用来作索引的数据结构有：Hash表，二叉树， 红黑树，BTree， B+Tree，下面进行简要介绍

3.1 hash表

在大多数情况下，不会使用hash表，会存在一个hash冲突的问题
hash可用于精准查询，但无法进行范围查询
但是hash访问速度很快

3.2 二叉树

假设现在有一个表，表里面有col1和col2两个字段，里面的数据如上图；

• 不使用索引，比如说现在执行sql语句 select * from t where col2 = 89，需要访问磁盘6次，当数据量很大的时候，大大的降低了查询效率

• 这时，使用二叉树作为索引的数据结构，给col2字段加上索引，还是执行sql语句select * from t where col2=89这一行的数据，现在只需要查询两次就可以了
  

• 但是如果使用col1作为索引（这里col1字段的值类似于自然主键），那么二叉树会退化成为链表，使用索引就没有什么意义了

3.3 红黑树

红黑树，又称之为自适应平衡二叉树，当左右两边的深度之差大于2时，会自动调节，也就是我们通常所说的左旋和右旋但是使用红黑树用来作索引的数据结构的话，当存储数据达到百万级别，红黑树的层级会变得特别高，IO次数会特别高，这样也是会影响效率。

3.4 BTree

B树(又称B-树)是一种自平衡的多叉搜索树 ，BTree的特点：

• 叶子节点具有相同的深度，叶子节点的指针为空，非叶子节点存储索引和数据
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排序
• 连续数据存储的磁盘不一定是连续的

3.5 B+树

特征：

• 孩子节点数量 = 关键字数量，非根节点关键字范围 ([m+1/2]-1 <= k <= m-1)
• 非叶子节点只保存索引不保存数据记录（区别B树非叶子节点同样保存数据记录）
• 所有关键字都存在于叶子节点，叶子节点之间用指针连接，提高区间访问性能
• 索引和数据的顺序保持一致

优点：

• 非叶子节点只存索引，包含数据量更多，降低树的深度，减少IO
• 叶子节点为有序链表，适合范围查询；同样链表结构也有利于提高增删效率

3.6 Mysql选用B+Tree的原因

Mysql中,只有HEAP/MEMORY引擎显式支持Hash索引,常用的InnoDB引擎默认使用的是B+Tree索引.InnoDB引擎还会实时监控表上索引的使用情况,如果认为建立hash索引可以提高查询效率,则会自动在内存中的"自适应哈希索引缓存区"建立hash索引(InnoDB中默认开启自适应hash索引)

1. Hash索引仅能满足“=”、“in"和“<=>" (不等于)的查询,不能使用范围查询,因为经过相应的hash算法处理之后的hash的值的大小关系并不能保证和hash运算之前的完全一样
2. Hash索引无法用来实现数据的排序操作
3. Hash索引不能利用部分索引检查,对于组合索引,Hash索引在计算hash值的时候是组合索引的键合并之后再一起计算出来hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,hash索引不能被使用
4. Hash索引在任何时候都不能避免表扫描,由于不同索引健也会存在相同的hash值,所以即使满足某个hash值的数据的记录数,也无法从hash索引中直接完成查询,还是要回表查询数据
5. Hash索引再遇到大量Hash值相等的情况下,性能并不一定就会比B+Tree索引的高.存在hash碰撞和冲突
6. B+Tree树类型，一方面（非叶子节点只存索引以及节点支持多个索引值）大大减少树层高，减少IO操作，另外叶子节点包括了所有的索引值，并且有序连续，有序提升了范围查询和排序的效率

4、树的搜索算法

有二叉树如图：

4.1 前序遍历

• 先访问根节点，再访问左子节点，最后访问右子节点{10,7,3,8,15,12,16}

// 前序遍历
public void preOrdeSearch(Node parent) {
    if (parent == null) {
        return;
    }
    // 输出根节点数据
    System.out.print(parent.data + " -> ");
    // 访问左子树节点
    preOrdeSearch(parent.left);
    // 访问右子数节点
    preOrdeSearch(parent.right);
}

4.2 中序遍历

• 先访问左子节点，再访问根节点，最后访问右子节点{3,7,8,10,12,15,16}

// 前序遍历
public void preOrdeSearch(Node parent) {
    if (parent == null) {
        return;
    }
    // 访问左子树节点
    preOrdeSearch(parent.left);
    // 输出根节点数据
    System.out.print(parent.data + " -> ");
    // 访问右子树节点
    preOrdeSearch(parent.right);
}

4.3 后序遍历

• 先访问左子节点，再访问右子节点，最后访问根节点{3,8,7,10,12,16,15,10}

// 前序遍历
public void preOrdeSearch(Node parent) {
    if (parent == null) {
        return;
    }
    // 访问左子树节点
    preOrdeSearch(parent.left);
    // 访问右子树节点
    preOrdeSearch(parent.right);
     // 输出根节点数据
    System.out.print(parent.data + " -> ");
}

4.4 深度优先搜索（depth-first search - DFS）

使用栈实现，从顶点开始压栈
搜索算法规则：

• 规则1：如果可能，访问一个邻接的未访问顶点，标记它，并将它放入栈中。
• 规则2：当不能执行规则 1 时，如果栈不为空，就从栈中弹出一个顶点。
• 规则3：如果不能执行规则 1 和规则 2 时，就完成了整个搜索过程。

如图所示遍历顺序为 [A -> B -> F -> I -> J -> C-> D -> E -> H -> K-> L]

4.5 广度优先搜索（breadth-first search - BFS）

使用队列实现，先进先出，先访问邻接点
搜索算法规则：

• 规则1：访问下一个未访问的邻接点（如果存在），这个顶点必须是当前顶点的邻接点，标记它，并把它插入到队列中
• 规则2：如果已经没有未访问的邻接点而不能执行规则 1 时，那么从队列列头取出一个顶点（如果存在），并使其成为当前顶
• 规则3：如果因为队列为空而不能执行规则 2，则搜索结束

具体示例：[A -> B -> C -> D -> E -> F -> H-> I -> J-> K]

5、Mysql索引类型

5.1 主键索引

一张表只能有一个主键索引，通常是自增id, 但是如果是分布式或者高并发架构下最好不使用顺序的自增id作为主键,容易引起主键的争用

5.2 唯一索引

可以是一列或者多列,可以有一个或者多个

5.3 单列索引和多列索引

索引可以是单列索引，也可以是多列索引。
（1）单列索引就是常用的一个列字段的索引，常见的索引。
（2）多列索引就是含有多个列字段的索引，也叫组合索引

5.4 聚集索引、非聚集索引

聚集索引,也叫聚簇索引,严格来说只是一种数据存储方式 InnoDB

• 聚集索引表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余连续性的记录,在物理存储上也是一样连续存放的.聚集索引对应的缺点就是修改慢,因为在记录插入时,需要对数据页进行重新排序.
• 聚集索引类似于字典中的用拼音去查找汉字,拼音检索表与书记顺序都是按照a–z排列的,类似于相同的逻辑顺序和物理顺序
• MySQL innodb的主键索引是聚集索引，即主键索引的叶子节点存的是整条记录的所有字段值，MyIsam的主键索引就是非聚集索引，非聚集索引的叶子节点存的是主键字段的值。

5.5 覆盖索引和回表

索引本身是一种查找数据的高效的方式,但是MySql也可以使用索引来直接获取列的数据,这样就不再需要回表读取数据行了. 如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”

innodb引擎； 表tbl有a,b,c三个字段，其中a是主键，b上建了索引，然后编写sql语句  

SELECT a,b FROM tbl WHERE b=1;
这样就会产生**索引覆盖**，因为where条件是b字段，那么会去b的索引树里查找数据，b的索引里面已经有a,b两个字段的值，不需要再查找其他。

SELECT * FROM tbl WHERE a=1; 
这样不会产生回表，因为所有的数据在a的索引树中均能找到，a主键索引是聚集索引，数据和索引在一起，不需要回表

SELECT * FROM tbl WHERE b=1;
这样就会产生回表，因为where条件是b字段，那么会去b的索引树里查找数据，但b的索引里面只有a,b两个字段的值，没有c，那么这个查询为了取到c字段，就要取出主键a的值，然后去a的索引树去找c字段的数据。查了两个索引树，这就叫回表。

5.6 索引下推

添加复合索引 index idx_age_name (age,name)，执行下面sql查询,分析查询过程

select id,age from user where name like '张%' and age = 20;

• Mysql版本 < 5.6
  检索复合索引 idx_name_age 查询出所有 name 包含 “张” 的主键ID 然后通过聚簇索引判断出所有符合where子句的数据返回 ,此过程需要回表

• Mysql版本 >= 5.6
  检索复合索引 idx_name_age 查询所有 name 包含 “张” 的 且age =20 的数据 直接返回结果集, 无需回表。可见索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率
  
  Using Index Condition 使用了索引下推的表现

6、索引创建和使用

6.1 创建索引规则

• 唯一性太差的列不要见索引
• 多列索引要充分考虑业务场景和最左匹配原则
• 考虑索引覆盖和索引下推的场景
• 必要时可以把列的部分长度作为索引

6.2 索引使用注意事项

• 最左法则 如果索引了多列，要遵守最左法则，指的是查询从索引的最左前列开始，可以跳过但是不跳过索引中间的列。
• 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
• 存储引擎不能使用索引中范围条件右边的列。(范围之后全失效)若中间索引列用到了范围(>、<、like等)，则后面的索引全失效
• 在使用不等于(!=、<>)或like的左模糊的时候无法使用索引会导致全表扫描
• IS NULL和IS NOT NULL也无法使用索引
• 字符串不加单引号索引失效，因为这里有一个隐式的类型转换操作，更严重会导致行锁变表锁，降低SQL效率（低）
• 如果表中数据比较少, MySQL 认为使用索引会比全表查询更慢，则不会使用索引。

users表中 name字段是varchar(25),age字段是tinyint,单独建立索引

EXPLAIN SELECT id,name,age FROM users WHERE name=100;
select_type:SIMPLE  type:ALL  key:null  key_len:null   ref:null

EXPLAIN SELECT id,name,age FROM users WHERE name='100';
select_type:SIMPLE  type:ref  key:idx_name  key_len:78   ref:const

EXPLAIN SELECT id,name,age FROM users WHERE age=100;
select_type:SIMPLE  type:ref  key:idx_age  key_len:2   ref:const

EXPLAIN SELECT id,name,age FROM users WHERE age='100';
select_type:SIMPLE   type:ref  key:idx_age  key_len:2   ref:const

age字段类型是tinyint

总结:新版mysql如果是字符串类型,自身的查询优化器会自动在搜索条件的值上加引号处理,整数类型则必须查询条件也是整数才能用到索引,但是尽量保证类型一致,避免进行隐式类型转换