数据库索引-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

可以通过 EXPLAIN 关键字来查看是否使用了索引。

1.索引的优缺点

优点：使用索引可以大大加快数据的检索速度（大大减少检索的数据量）, 减少 IO 次数，这也是创建索引的最主要的原因。通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

缺点：创建索引和维护索引需要耗费许多时间。当对表中的数据进行增删改的时候，如果数据有索引，那么索引也需要动态的修改，会降低 SQL 执行效率。索引需要使用物理文件存储，也会耗费一定空间。

2.索引的分类？

功能分类：

主键索引（聚簇索引）：加速查询 + 列值唯一（不可以有 NULL）+ 表中只有一个。

在 MySQL 的 InnoDB 的表中，当没有显示的指定表的主键时，InnoDB 会自动先检查表中是否有唯一索引且不允许存在 null 值的字段，如果有，则选择该字段为默认的主键，否则 InnoDB 将会自动创建一个 6Byte 的自增主键。

普通索引：仅加速查询。
唯一索引：加速查询 + 列值唯一（可以有 NULL）。
覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值。
联合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并。
全文索引：对文本的内容进行分词，进行搜索。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。一般不会使用，效率较低，通常使用搜索引擎如 ElasticSearch 代替。

数据结构：

①、B+树索引：

最常见的索引类型，一种将索引值按照一定的算法，存入一个树形的数据结构中（二叉树），每次查询都从树的根节点开始，一次遍历叶子节点，找到对应的值。查询效率是 O(logN)。B+树的非叶子节点只存储键值，不存储数据，而叶子节点存储了所有的数据，并且构成了一个有序链表。

②、Hash 索引：

基于哈希表的索引，查询效率可以达到 O(1)，不适合范围查询。哈希表是键值对的集合，通过键(key)即可快速取出对应的值(value)，因此哈希表可以快速检索数据（接近 O（1））。

1.为何能够通过 key 快速取出 value 呢？

原因在于 哈希算法（也叫散列算法）。通过哈希算法，我们可以快速找到 key 对应的 index，找到了 index 也就找到了对应的 value。

hash = hashfunc(key)
index = hash % array_size

2.hash冲突：

数组+链表+红黑树来解决，

红黑树是一种自平衡二叉查找树，通过在插入和删除节点时进行颜色变换和旋转操作，使得树始终保持平衡状态，它具有以下特点：

每个节点非红即黑；
根节点总是黑色的；
每个叶子节点都是黑色的空节点（NIL 节点）；
如果节点是红色的，则它的子节点必须是黑色的（反之不一定）；
从任意节点到它的叶子节点或空子节点的每条路径，必须包含相同数目的黑色节点（即相同的黑色高度）。

存储位置：

①、聚簇索引：

聚簇索引的叶子节点保存了一行记录的所有列信息。也就是说，聚簇索引的叶子节点中，包含了一个完整的记录行。

更新代价大

②、非聚簇索引：

它的叶子节点只包含一个主键值，通过非聚簇索引查找记录要先找到主键，然后通过主键再到聚簇索引中找到对应的记录行，这个过程被称为回表。

3.Hash 索引和 B+ 树索引区别是什么？

B+ 树索引可以进行范围查询，Hash 索引不能。
B+ 树索引支持联合索引的最左侧原则，Hash 索引不支持。
B+ 树索引支持 order by 排序，Hash 索引不支持。
Hash 索引在等值查询上比 B+ 树索引效率更高。
B+ 树使用 like 进行模糊查询的时候，LIKE 'abc%' 的话可以起到索引优化的作用，Hash 索引无法进行模糊查询。

4.覆盖索引了解吗？

它指的是一种索引能够“覆盖”查询中所涉及的所有列，换句话说，查询所需的数据全部都可以从索引中直接获取，而无需访问数据表的行数据（也就是无需回表）。

5.什么是最左前缀原则？

在使用联合索引时，应当遵守最左前缀原则，联合索引在 B+ 树中是复合的数据结构，按照从左到右的顺序依次建立搜索树

6.什么是索引下推（ICP）优化？

例如一张表，建了一个联合索引（name, age），查询语句：select * from t_user where name like '张%' and age=10;，由于name使用了范围查询，根据最左匹配原则：

不使用 ICP，引擎层查找到name like '张%'的数据，再由 Server 层去过滤age=10这个条件，这样一来，就回表了两次，浪费了联合索引的另外一个字段age。

但是，使用了索引下推优化，把 where 的条件放到了引擎层执行，直接根据name like '张%' and age=10的条件进行过滤，减少了回表的次数。

7.创建索引有哪些注意点？

①、选择合适的列作为索引

经常作为查询条件（WHERE 子句）、排序条件（ORDER BY 子句）、分组条件（GROUP BY 子句）的列是建立索引的好候选。
区分度低的字段，例如性别，不要建索引
频繁更新的字段，不要作为主键或者索引
不建议用无序的值(例如身份证、UUID )作为索引，当主键具有不确定性，会造成叶子节点频繁分裂，出现磁盘存储的碎片化

②、避免过多的索引

每个索引都需要占用额外的磁盘空间。
更新表（INSERT、UPDATE、DELETE 操作）时，所有的索引都需要被更新。
维护索引文件需要成本；还会导致页分裂，IO 次数增多。

8.索引哪些情况下会失效呢？

索引失效也是慢查询的主要原因之一，常见的导致索引失效的情况有下面这些：

SELECT * ，（如果不走索引大概率是因为 where 查询范围过大导致的），但它可能会带来一些其他的性能问题比如造成网络传输和数据处理的浪费、无法使用索引覆盖;
创建了组合索引，但查询条件未遵守最左匹配原则;
在索引列上进行计算、函数、类型转换等操作;
以 % 开头的 LIKE 查询比如 LIKE '%abc';;
查询条件中使用 OR，且 OR 的前后条件中有一个列没有索引，涉及的索引都不会被使用到;
IN 的取值范围较大时会导致索引失效，走全表扫描(NOT IN 和 IN 的失效场景相同);
发生隐式转换

数据库索引