【275期】面试官：你对MySQL中的索引了解多少？

前言

因为现在使用的mysql默认存储引擎是Innodb，所以本篇文章重点讲述Innodb下的索引，顺带简单讲述其他引擎。希望小伙伴们能通过这片文章对mysql的索引有更加清晰的认识，废话不多说，我们开始吧。

索引介绍

首先，我们先带着一些问题来看接下来的内容。

索引是个什么东西？
我们可以创建哪些索引？
哪些字段适合建立索引呢？
索引是不是越多越好呢？
为什么我们不建议使用uuid、身份证号等数据做为主键？
为什么不建议使用select * from table？
我们使用模糊匹配 ’%三‘ ’张%‘ 在前在后会影响索引的使用吗？

上面的问题我们大家可能都存在或者部分存在疑惑，接下来就是解惑的时间。

什么是索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

mysql中索引有哪些类型

普通索引

通索引是mysql里最基本的索引，没有什么特殊性，在任何一列上都能进行创建。

EXPLAIN SELECT *
FROM user_innodb
WHERE name LIKE '%张' and phone = '13204776301';

这种情况下因为phone在索引第一位，所以无论有没有name这个条件都会走索引。

我们可以看到extra里面存在Using index condition（ICP），ICP的全名是index condition pushdown索引条件下推。

ICP 索引条件下推

索引条件下推（ICP）是针对MySQL使用索引从表中检索行的情况的一种优化。如果不使用ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给MySQL服务器，后者将评估WHERE行的条件。

启用ICP后，如果WHERE可以仅使用索引中的列来评估部分条件，则MySQL服务器会将这部分条件压入WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数以及MySQL服务器必须访问存储引擎的次数。

索引条件下推式优化的适用性取决于以下条件：

ICP用于 range， ref， eq_ref，和 ref_or_null访问方法时，有一个需要访问的全部表行。
ICP可用于InnoDB 和MyISAM表，包括分区表InnoDB和 MyISAM表。
对于InnoDB表，ICP仅用于二级索引。ICP的目标是减少全行读取次数，从而减少I / O操作。对于 InnoDB聚集索引，完整的记录已被读入InnoDB 缓冲区。在这种情况下使用ICP不会减少I / O。
在虚拟生成的列上创建的二级索引不支持ICP。InnoDB 支持虚拟生成的列上的二级索引。
引用子查询的条件不能下推。
涉及存储功能的条件不能下推。
存储引擎无法调用存储的功能。
触发条件不能下推。