【面向校招】数据库 —— Mysql索引

2022-05-21  本文已影响0人  胡小毛

1. 索引是什么？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。更通俗的说，索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中，它是要占据物理空间的。

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。比如我们在查字典的时候，前面都有检索的拼音和偏旁、笔画等，然后找到对应字典页码，这样然后就打开字典的页数就可以知道我们要搜索的某一个key的全部值的信息了。

2. 索引有哪些优缺点？

索引的优点

索引的缺点

3. 哪些列上适合创建索引？创建索引有哪些开销？

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。

创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响

4. 索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

  1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
  3. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

5. MySQL有哪几种索引类型？

1、从存储结构上来划分：BTree索引（B-Tree或B+Tree索引），Hash索引，full-index全文索引，R-Tree索引。这里所描述的是索引存储时保存的形式，

2、从应用层次来分：普通索引，唯一索引，复合索引。

3、根据中数据的物理顺序与键值的逻辑（索引）顺序关系： 聚集索引，非聚集索引。

6. 说一说索引的底层实现？

Hash索引

基于哈希表实现，只有精确匹配索引所有列的查询才有效，对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），并且Hash索引将所有的哈希码存储在索引中，同时在索引表中保存指向每个数据行的指针。

图片来源：https://www.javazhiyin.com/40232.html

[图片上传失败...(image-f8109c-1653141194846)]

B-Tree索引（MySQL使用B+Tree）

B-Tree能加快数据的访问速度，因为存储引擎不再需要进行全表扫描来获取数据，数据分布在各个节点之中。

[图片上传失败...(image-b502fc-1653141194846)]

B+Tree索引

是B-Tree的改进版本，同时也是数据库索引索引所采用的存储结构。数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都指向相邻的叶子节点的地址。相比B-Tree来说，进行范围查找时只需要查找两个节点，进行遍历即可。而B-Tree需要获取所有节点，相比之下B+Tree效率更高。

B+tree性质：

[图片上传失败...(image-a89abf-1653141194846)]

7. 为什么索引结构默认使用B+Tree，而不是B-Tree，Hash，二叉树，红黑树？

B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

B+tree： 从两个方面来回答

Hash：

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

不使用平衡二叉树的原因如下：

最大原因：深度太大(因为一个节点最多只有2个子节点)，一次查询需要的I/O复杂度为O(lgN),而b+tree只需要O(log_mN),而其出度m非常大，其深度一般不会超过4 平衡二叉树逻辑上很近的父子节点，物理上可能很远，无法充分发挥磁盘顺序读和预读的高效特性。

8. MyISAM和InnoDB实现BTree索引方式的区别

1）MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。 索引文件和数据文件是分离的

2）InnoDB

9. 主键索引和非主键索引

TYPE INDEX
id int id(primary key)
k int k
name varchar

假设有如上表结构, 那么建立起的索引结构如下图

[图片上传失败...(image-3adc58-1653141194846)]

从图中看出, 根据叶子节点内容的不同, 索引类型分为主键索引和非主键索引.

10. 讲一讲聚簇索引与非聚簇索引？

在 InnoDB 里，索引B+ Tree的叶子节点存储了整行数据的是主键索引，也被称之为聚簇索引，即将数据存储与索引放到了一块，找到索引也就找到了数据。

而索引B+ Tree的叶子节点存储了主键的值的是非主键索引，也被称之为非聚簇索引、二级索引。

聚簇索引与非聚簇索引的区别：

11. 非聚簇索引一定会回表查询吗？

不一定，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。一个索引包含（覆盖）所有需要查询字段的值，被称之为"覆盖索引"。

举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行select score from student where score > 90的查询时，在索引的叶子节点上，已经包含了score 信息，不会再次进行回表查询。

12. 联合索引是什么？为什么需要注意联合索引中的顺序？

MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。

具体原因为:

MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。

当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。

13. 回表

当执行 SELECT * FROM t WHERE id = 500 时, 即主键查询方式, 则需要搜索ID这颗B+树; 当执行 SELECT * FROM t WHERE k = 5 时, 即普通索引查询方式, 则先在k这棵树查找到主键的值, 再从ID这棵树中查找到对应的行.

当我们执行SQL搜索数据时, 如果需要先从非主键索引中查询到主键的值, 再从主键索引中查询到对应的数据, 这个过程就被称为回表. 所以应该尽量使用主键查询.

14. 索引维护 (页分裂与页合并)

B+树为了有序性, 需要对插入和删除数据时做出对应的维护. 当插入数据时, 如在上图中插入ID=400的数据, 那么从逻辑上来说, 需要移动后面的数据, 空出位置.

若此时R5所在数据页满了, 则需要申请一个新的数据页, 然后移动部分数据到新数据页中, 这个过程被称为页分裂. 页分裂影响了数据页的空间利用率, 而且在分裂过程中, 性能也会有所影响.

若相邻两个数据页因为删除导致利用率很低后, 那么会将这两个数据页的数据合并到一个数据页中, 这个过程被称为页合并. 即页分裂的逆过程.

15. 覆盖索引

如果执行了语句 SELECT id FROM t WHERE k between 3 and 5 时, 只需要查询 id 的值, 而 id 已经在 k 的索引树上, 所以不需要再回表去查询整行, 直接返回查询结果, 索引 k 已经覆盖了这条SQL查询的需求, 被称为 覆盖索引. 覆盖索引能够减少树的搜索次数, 不需要再次回表查询整行, 所以是一个常用的性能优化手段.

16. 讲一讲MySQL的最左前缀原则?

最左前缀原则 就是利用索引列中最左的字段优先进行匹配

最左前缀原则就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。

例如：b = 2 如果建立(a,b)顺序的索引，是匹配不到(a,b)索引的；但是如果查询条件是a = 1 and b = 2,就可以，因为优化器会自动调整a,b的顺序。

比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，因为c字段是一个范围查询，它之后的字段会停止匹配。如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

最左匹配原则的原理

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引.最左匹配原则都是针对联合索引来说的

同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

优点：最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

17. 讲一讲前缀索引？

在对字符串创建索引, 如INDEX(name)中, 若字符串非常大, 那么响应的空间使用和维护开销也非常大, 就可以使用字符串从左开始的部分字符创建索引（把很长字段的前面的公共部分作为一个索引，就会产生超级加倍的效果）, 减少空间和维护的成本, 但是也会降低索引的选择性。但是，我们需要注意，order by不支持前缀索引 。

索引的选择性指的是 : 不重复的索引值和数据表的记录总数(#T)的比值, 范围为 1/#T 到 1 之间, 索引选择性越高则查询效率越高. 对于BLOB, TEXT, VARCHAR等类型的列, 必须使用前缀索引, MySQL不允许索引这些列的完整长度.

流程是：

  1. 先计算完整列的选择性 SELECT COUNT(DISTINCT name)/COUNT(1) FROM t
  2. 在计算不同前缀长度N的选择性 SELECT COUNT(DISCTINCT LEFT(name, N)) / COUNT(1) FROM t
  3. 看哪个N更靠近1, 进行索引的创建

18. 了解索引下推吗？

对于SQL语句 SELECT * FROM t WHERE name LIKE '陈%' AND age = 10 , INDEX(name, age) 情况来说

在 MySQL5.6 之前没有引入索引下推优化时, 执行流程如下图, 在定位完name字段的索引后, 需要一条条进行回表查询, 然后再判断其他字段是否满足条件.

[图片上传失败...(image-4324e3-1653141194846)]

而 MySQL5.6 引入了索引下推优化后, 可以在所有遍历过程中, 对索引中包含的字段先进行判断过滤, 然后再进行后续操作, 减少了回表次数.

[图片上传失败...(image-b4dbc3-1653141194846)]

19. 怎么查看MySQL语句有没有用到索引？

通过explain，如以下例子：

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26';

id select_type table partitions type possible_keys key key_len ref filtered rows Extra
1 SIMPLE titles null const PRIMARY PRIMARY 59 const,const,const 10 1

20. 为什么官方建议使用自增长主键作为索引？

结合B+Tree的特点，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。

插入连续的数据：

图片来自：https://www.javazhiyin.com/40232.html

[图片上传失败...(image-b06b90-1653141194846)]

插入非连续的数据：

[图片上传失败...(image-689d34-1653141194846)]

21. 如何创建索引？

创建索引有三种方式。

1、 在执行CREATE TABLE时创建索引

CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);

2、 使用ALTER TABLE命令去增加索引。

ALTER TABLE table_name ADD INDEX index_name (column_list);

ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

其中table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。

索引名index_name可自己命名，缺省时，MySQL将根据第一个索引列赋一个名称。另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。3、 使用CREATE INDEX命令创建。

CREATE INDEX index_name ON table_name (column_list);

22. 创建索引时需要注意什么？

23. 建索引的原则有哪些？

1、最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3、尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4、索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5、尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

24. 使用索引查询一定能提高查询的性能吗？

通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的I* NSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

25. 什么情况下不走索引（索引失效）？

1、使用!= 或者 <> 导致索引失效

2、类型不一致导致的索引失效

3、函数导致的索引失效

如：

SELECT * FROM user WHERE DATE(create_time) = '2020-09-03';

如果使用函数在索引列，这是不走索引的。

4、运算符导致的索引失效

SELECT * FROM user WHERE age - 1 = 20;

如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

5、OR引起的索引失效

SELECT * FROM user WHERE name = '张三' OR height = '175';

OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段，那么索引不会失效，反之索引失效。

6、模糊搜索导致的索引失效

SELECT * FROM user WHERE name LIKE '%冰';

当%放在匹配字段前是不走索引的，放在==后面==才会走索引。

7、NOT IN、NOT EXISTS导致索引失效

26. 自适应哈希索引

InnoDB中不存在哈希索引, 但是哈希索引确实有利于快速查找, 于是InnoDB引入了"自适应哈希索引", 在某些索引值被使用的非常频繁时, InnoDB会在内存中的B+树结构之上创建一个哈希索引, 用于这些频繁使用的索引值的快速查找, 使得其存有哈希快速查找的特点.

27 .索引相关高频面试题

  1. 索引是什么? 索引优缺点?
  1. MySQL索引类型
  1. 索引底层实现? 为什么使用B+树, 而不是B树, BST, AVL, 红黑树等等?
  2. 什么是聚簇索引和非聚簇索引?
  3. 非聚簇索引一定会回表吗?
    (不一定, 覆盖索引不会回表)
  4. 什么是联合索引?为什么需要注意联合索引中的字段顺序?
  5. 什么是最左前缀原则?
  6. 什么是前缀索引?
  7. 什么是索引下推?
  8. 如何查看MySQL语句是否使用到索引?
    EXPLAIN SQL语句
    possible_key: 可能用到的索引(可以查看是否有冗余索引)
    key: 真正使用到的索引
  9. 为什么建议使用自增主键作为索引?
    (索引维护可能造成页分裂, 自增主键减少数据的移动和分裂)
  10. 建立索引的原则
  1. 什么情况下索引失效?

【面向校招】全力备战2023Golang实习与校招

欢迎群共同进步：
QQ群：1007576722

上一篇 下一篇

猜你喜欢

热点阅读