十问MySQL索引

MySQL索引

1、什么是索引

• 索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
• 索引一般存储在磁盘的文件中，它是占用物理空间的。
• 适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。例如：性别，不建议创建索引。

---

2、MySQL索引有哪些类型

• 按逻辑分离

  1. 主键索引：一种特殊的唯一索引，但是不允许为空值

  2. 唯一索引：索引列中的值必须是唯一的，但是允许为空值

  3. 联合索引：多个字段创建的索引，使用时遵循最左前缀原则

     最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。可以是联合索引的最左N个字段。比如你建立一个组合索引（a,b,c），其实可以相当于建了（a），（a,b）,(a,b,c)三个索引，大大提高了索引复用能力。当然，最左前缀也可以是字符串索引的最左M个字符，（MySQL 5.6引入索引下推优化实现）

  4. 普通索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

• 按物理存储分类

  1. 聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
  2. 非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

• 按数据结构分类

  1. B+树索引：所有数据存储在叶子节点，复杂的o(logn),适合范围查询
  2. 哈希索引：检索效率高，适合等值查询
  3. 全文索引：MyISAM和InnoDB都支持全文检索，一般在文本类型char，text，varchar类型上创建
  4. R-Tree索引：用来对GIS数据类型创建SPATIAL索引

3、索引什么时候失效

• 查询条件包含or，可能导致索引失效。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• 如果字段类型是字符串，where时一定用引号括起来，否则索引失效
• like以%导致索引失效
• 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效
• 在索引列上使用 mysql 的内置函数，索引失效
• 对索引列运算（如，+、-、*、/），数据类型隐形转换，索引失效
• 索引字段上使用（！= 或者 < >，not in）时，可能会导致索引失效
• 索引字段上使用is null， is not null，可能导致索引失效
• 左连接查询或者右连接查询查询关联的字段编码格式不一样，可能导致索引失效
• mysql 估计使用全表扫描要比使用索引快,则不使用索引

4、什么场景不适合建立索引

• 数据量少的表，不适合加索引
• 更新比较频繁的也不适合加索引
• 数据唯一性差的字段不适合加索引（如性别，只有两种可能数据。意味着索引的二叉树级别少，多是平级。这样的二叉树查找无异于全表扫描）
• where、group by、order by等后面没有使用到的字段，不需要建立索引
• 已经有冗余的索引的情况（比如已经有a,b的联合索引，不需要再单独建立a索引）

5、为什么要用B+树

• 为什么不是一般二叉树

  1. 如果二叉树特殊化为一个链表（二叉树的每个结点只有左子树，或者只有右子树），相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

• 为什么不是平衡二叉树

  1. 读取内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果 是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

• 为什么不是B（balance）树

  1. B+树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数有会再次减少，数据查询的效率也会更快。

  2. B+树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

     B树：一种多路的平衡搜索树，与普通的平衡二叉树的不同是，B树的每个节点可以存储多个数据，而且每个节点不止有两个子节点，最多可以有上千个子节点。B树中每个节点都存放着索引和数据，数据遍布整个树结构，搜索可能在非叶子节点结束，最好的情况是O(1)。

• 为什么不是哈希索引

  1. B+ 树可以进行范围查询，Hash 索引不能。
  2. B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
  3. B+ 树支持 order by 排序，Hash 索引不支持。
  4. Hash 索引在等值查询上比 B+ 树效率更高。
  5. B+ 树使用 like 进行模糊查询的时候，like 后面（比如 % 开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

6、什么是回表

​ 当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。（执行两次B+树）

​ 如何解决回表：索引覆盖->select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖(多列也可建立联合索引)。

7、什么是索引下推

​ 有用户表数据如下：

name	age	id
小红	12	1
小明	13	2
小丽	15	3

select * from user where name like '小%' and age = 15

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。

MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

8、查看执行计划

explain查看SQL的执行计划，这样就知道是否命中索引了。一般来说，我们需要重点关注type、rows、filtered、extra、key。

• type：表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

连接类型type	解释
system	这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的
const	通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快
eq_ref	常用于主键或唯一索引扫描，一般指使用主键的关联查询
ref	常用于非主键和唯一索引扫描
ref_or_null	这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行
index_merge	使用了索引合并优化方法，查询使用了两个以上的索引
unique_subquery	类似于eq_ref，条件用了in子查询
index_subquery	区别于unique_subquery，用于非唯一索引，可以返回重复值
range	常用于范围查询，比如：between … and 或 In 等操作
index	全索引扫描
ALL	全表扫描

• rows：该列表示MySQL估算要找到我们所需的记录，需要读取的行数。对于InnoDB表，此数字是估计值，并非一定是个准确值

• filtered：该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

• extra：该字段包含有关MySQL如何解析查询的其他信息，它一般会出现这几个值：

  extra	解释
  Using filesort	表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
  Using index	表示是否用了覆盖索引
  Using temporary	表示是否使用了临时表,性能特别差，需要重点优化。一般多见于group by语句，或者union语句
  Using where	表示使用了where条件过滤
  Using index condition	MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据

• key：该列表示实际用到的索引。一般配合possible_keys列一起看

9、索引的优缺点

• 优点

  1. 索引可以加快数据查询速度，减少查询时间
  2. 唯一索引可以保证数据库表中每一行的数据的唯一性

• 缺点

  1. 创建索引和维护索引要耗费时间
  2. 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
  3. 以表中的数据进行增、删、改的时候，索引也要动态的维护

10、聚簇索引和非聚簇的区别

​ 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。

​ 在MySQL的InnoDB存储引擎中使用B+树实现，聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

• 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
• 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。

​ 而在MyISM存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据。