关于java:MySQL索引面试题2021最新版

1次阅读

共计 8143 个字符，预计需要花费 21 分钟才能阅读完成。

小伙伴们好，我是库森。

明天给大家带来了 MySQL 索引的常考面试题，看看你能答对多少~

本文收录于《面试小抄》系列，Github 地址(可下载 pdf）：https://github.com/cosen1024/… 国内 Gitee(可下载 pdf）：https://gitee.com/cosen1024/J…

这是本期的 MySQL 索引面试题目录，不会的快快查漏补缺~

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

索引是一种数据结构。数据库索引，是数据库管理系统中一个排序的数据结构，以帮助疾速查问、更新数据库表中数据。索引的实现通常应用 B 树及其变种 B + 树。更艰深的说，索引就相当于目录。为了不便查找书中的内容，通过对内容建设索引造成目录。而且索引是一个文件，它是要占据物理空间的。

MySQL 索引的建设对于 MySQL 的高效运行是很重要的，索引能够大大提高 MySQL 的检索速度。比方咱们在查字典的时候，后面都有检索的拼音和偏旁、笔画等，而后找到对应字典页码，这样而后就关上字典的页数就能够晓得咱们要搜寻的某一个 key 的全副值的信息了。

索引的长处

能够大大放慢数据的检索速度，这也是创立索引的最次要的起因。
通过应用索引，能够在查问的过程中，应用优化暗藏器，进步零碎的性能。

索引的毛病

工夫方面：创立索引和保护索引要消耗工夫，具体地，当对表中的数据进行减少、删除和批改的时候，索引也要动静的保护，会升高增 / 改 / 删的执行效率；
空间方面：索引须要占物理空间。

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

2、从利用档次来分：一般索引，惟一索引，复合索引。

一般索引：即一个索引只蕴含单个列，一个表能够有多个单列索引
惟一索引：索引列的值必须惟一，但容许有空值
复合索引：多列值组成一个索引，专门用于组合搜寻，其效率大于索引合并
聚簇索引 (汇集索引)：并不是一种独自的索引类型，而是一种数据存储形式。具体细节取决于不同的实现，InnoDB 的聚簇索引其实就是在同一个构造中保留了 B -Tree 索引(技术上来说是 B +Tree) 和数据行。
非聚簇索引：不是聚簇索引，就是非聚簇索引

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

Hash 索引

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

图片起源：https://www.javazhiyin.com/40…

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

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

B+Tree 索引

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

B+tree 性质：

n 棵子 tree 的节点蕴含 n 个关键字，不用来保留数据而是保留数据的索引。
所有的叶子结点中蕴含了全副关键字的信息，及指向含这些关键字记录的指针，且叶子结点自身依关键字的大小自小而大程序链接。
所有的非终端结点能够看成是索引局部，结点中仅含其子树中的最大（或最小）关键字。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。
B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。

B-tree：从两个方面来答复

B+ 树的磁盘读写代价更低：B+ 树的外部节点并没有指向关键字具体信息的指针，因而其外部节点绝对 B(B-)树更小，如果把所有同一外部节点的关键字寄存在同一盘块中，那么盘块所能包容的关键字数量也越多，一次性读入内存的须要查找的关键字也就越多，绝对 IO 读写次数就升高 了。
因为 B + 树的数据都存储在叶子结点中，分支结点均为索引，不便扫库，只须要扫一遍叶子结点即可，然而 B 树因为其分支结点同样存储着数据，咱们要找到具体的数据，须要进行一次中序遍历按序来扫，所以 B + 树更加适宜在 区间查问 的状况，所以通常 B + 树用于数据库索引。

Hash：

尽管能够疾速定位，然而没有程序，IO 复杂度高；
基于 Hash 表实现，只有 Memory 存储引擎显式反对哈希索引；
适宜 等值查问，如 =、in()、<=>，不反对范畴查问；
因为不是依照索引值顺序存储的，就不能像 B +Tree 索引一样利用索引实现[排序]()；
Hash 索引在查问等值时十分快；
因为 Hash 索引始终索引的 所有列的全部内容，所以不反对局部索引列的匹配查找；
如果有大量反复键值得状况下，哈希索引的效率会很低，因为存在哈希碰撞问题。

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

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

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

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

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

非汇集索引与汇集索引的区别在于非汇集索引的叶子节点不存储表中的数据，而是存储该列对应的主键（行号）
对于 InnoDB 来说，想要查找数据咱们还须要依据主键再去汇集索引中进行查找，这个再依据汇集索引查找数据的过程，咱们称为回表。第一次索引个别是程序 IO，回表的操作属于随机 IO。须要回表的次数越多，即随机 IO 次数越多，咱们就越偏向于应用全表扫描。
通常状况下，主键索引（聚簇索引）查问只会查一次，而非主键索引（非聚簇索引）须要回表查问屡次。当然，如果是笼罩索引的话，查一次即可
留神：MyISAM 无论主键索引还是二级索引都是非聚簇索引，而 InnoDB 的主键索引是聚簇索引，二级索引是非聚簇索引。咱们本人建的索引根本都是非聚簇索引。

不肯定，这波及到查问语句所要求的字段是否全副命中了索引，如果全副命中了索引，那么就不用再进行回表查问。一个索引蕴含（笼罩）所有须要查问字段的值，被称之为 ” 笼罩索引 ”。

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

MySQL 能够应用多个字段同时建设一个索引，叫做联结索引。在联结索引中，如果想要命中索引，须要依照建设索引时的字段程序挨个应用，否则无奈命中索引。

具体起因为:

MySQL 应用索引时须要索引有序，假如当初建设了 ”name，age，school” 的联结索引，那么索引的排序为: 先依照 name 排序，如果 name 雷同，则依照 age 排序，如果 age 的值也相等，则依照 school 进行排序。

当进行查问时，此时索引仅仅依照 name 严格有序，因而必须首先应用 name 字段进行等值查问，之后对于匹配到的列而言，其依照 age 字段严格有序，此时能够应用 age 字段用做索引查找，以此类推。因而在建设联结索引的时候应该留神索引列的程序，个别状况下，将查问需要频繁或者字段选择性高的列放在后面。此外能够依据特例的查问或者表构造进行独自的调整。

最左前缀准则就是最左优先，在创立多列索引时，要依据业务需要，where 子句中应用最频繁的一列放在最右边。
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 的程序能够任意调整。

= 和 in 能够乱序，比方 a = 1 and b = 2 and c = 3 建设 (a,b,c) 索引能够任意程序，mysql 的查问优化器会帮你优化成索引能够辨认的模式。

因为可能咱们索引的字段十分长，这既占内存空间，也不利于保护。所以咱们就想，如果只把很长字段的后面的公共局部作为一个索引，就会产生超级加倍的成果。然而，咱们须要留神，order by 不反对前缀索引。

流程是：

先计算残缺列的选择性 : select count(distinct col_1)/count(1) from table_1

再计算不同前缀长度的选择性 : select count(distinct left(col_1,4))/count(1) from table_1

找到最优长度之后，创立前缀索引 : create index idx_front on table_1 (col_1(4))

MySQL 5.6 引入了索引下推优化。默认开启，应用 SET optimizer_switch =‘index_condition_pushdown=off’; 能够将其敞开。

有了索引下推优化，能够在 缩小回表次数
在 InnoDB 中只针对二级索引无效

官网文档中给的例子和解释如下：

在 people_table 中有一个二级索引(zipcode，lastname，address)，查问是 SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE‘%etrunia%’AND address LIKE‘%Main Street%’;

如果没有应用索引下推技术，则 MySQL 会通过 zipcode=’95054’从存储引擎中查问对应的数据，返回到 MySQL 服务端，而后 MySQL 服务端基于 lastname LIKE‘%etrunia%’and address LIKE‘%Main Street%’来判断数据是否符合条件
如果应用了索引下推技术，则 MYSQL 首先会返回合乎 zipcode=’95054’的索引，而后依据 lastname LIKE‘%etrunia%’and address LIKE‘%Main Street%’来判断索引是否符合条件。如果符合条件，则依据该索引来定位对应的数据，如果不合乎，则间接 reject 掉。

通过 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

id：在⼀个⼤的查问语句中每个 SELECT 关键字都对应⼀个唯⼀的 id，如 explain select * from s1 where id = (select id from s1 where name = ‘egon1’); 第一个 select 的 id 是 1，第二个 select 的 id 是 2。有时候会呈现两个 select，然而 id 却都是 1，这是因为优化器把子查问变成了连贯查问。
select_type：select 关键字对应的那个查问的类型，如 SIMPLE,PRIMARY,SUBQUERY,DEPENDENT,SNION。
table：每个查问对应的表名。
type：type 字段比拟重要, 它提供了判断查问是否高效的重要依据根据. 通过 type 字段, 咱们判断此次查问是 全表扫描 还是 索引扫描 等。如 const(主键索引或者惟一二级索引进行等值匹配的状况下),ref(一般的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的笼罩索引)。

通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描, 因而在雷同的查问条件下, 它是速度最慢的.
而 index 类型的查问尽管不是全表扫描, 然而它扫描了所有的索引, 因而比 ALL 类型的稍快.
possible_key：查问中可能用到的索引(能够把用不到的删掉，升高优化器的优化工夫)。
key：此字段是 MySQL 在以后查问时所真正应用到的索引。
filtered：查询器预测满足下一次查问条件的百分比。
rows 也是一个重要的字段. MySQL 查问优化器依据统计信息, 估算 SQL 要查找到后果集须要扫描读取的数据行数.
这个值十分直观显示 SQL 的效率好坏, 原则上 rows 越少越好。
extra：示意额定信息，如 Using where,Start temporary,End temporary,Using temporary 等。

联合 B +Tree 的特点，自增主键是间断的，在插入过程中尽量减少页决裂，即便要进行页决裂，也只会决裂很少一部分。并且能缩小数据的挪动，每次插入都是插入到最初。总之就是缩小决裂和挪动的频率。

插入间断的数据：

图片来自：https://www.javazhiyin.com/40…

插入非间断的数据：

创立索引有三种形式。

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);

非空字段：应该指定列为 NOT NULL，除非你想存储 NULL。在 mysql 中，含有空值的列很难进行查问优化，因为它们使得索引、索引的统计信息以及比拟运算更加简单。你应该用 0、一个非凡的值或者一个空串代替空值；
取值离散大的字段：（变量各个取值之间的差别水平）的列放到联结索引的后面，能够通过 count()函数查看字段的差别值，返回值越大阐明字段的惟一值越多字段的离散水平高；
索引字段越小越好：数据库的数据存储以页为单位一页存储的数据越多一次 IO 操作获取的数据越大效率越高。

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) 的索引，那么只须要批改原来的索引即可。

通常通过索引查问数据比全表扫描要快。然而咱们也必须留神到它的代价。

索引须要空间来存储，也须要定期维护，每当有记录在表中增减或索引列被批改时，索引自身也会被批改。这意味着每条记录的 I * NSERT，DELETE，UPDATE 将为此多付出 4，5 次的磁盘 I /O。因为索引须要额定的存储空间和解决，那些不必要的索引反而会使查问反应时间变慢。应用索引查问不肯定能进步查问性能，索引范畴查问 (INDEX RANGE SCAN) 实用于两种状况:

基于一个范畴的检索，个别查问返回后果集小于表中记录数的 30%。
基于非唯一性索引的检索。

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 导致索引生效

整顿不易，点个赞呗！小伙伴们，下期再见~

https://blog.csdn.net/ThinkWo…

https://www.javazhiyin.com/40…

https://juejin.cn/post/684490…

https://blog.csdn.net/ThinkWo…

https://segmentfault.com/a/11…
这里也举荐一个我收集的计算机书籍仓库，仓库目前有上百本经典 cs 电子书，看经典的书籍会更悟得深~

点此链接即可中转书单，计算机必看经典书籍（含 pdf 下载）

Github 也有相应仓库，https://github.com/cosen1024/…
欢送 star。

正文完

java

发表至： java

2021-06-15

0

关于java:Java并发编程的艺术pdf

cpu-idle过低问题

关于java:如何成为中间件开发人员

公司ES升级带来的坑怎么填？

关于java:JVM经典面试题2021最新版

关于java:MySQL索引面试题2021最新版

1. 索引是什么？

2. 索引有哪些优缺点？

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

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

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

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

7. 非聚簇索引肯定会回表查问吗？

8. 联结索引是什么？为什么须要留神联结索引中的程序？

9. 讲一讲 MySQL 的最左前缀准则?

10. 讲一讲前缀索引？

11. 理解索引下推吗？

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

13. 为什么官网倡议应用自增长主键作为索引？

14. 如何创立索引？

15. 创立索引时须要留神什么？

16. 建索引的准则有哪些？

17. 应用索引查问肯定能进步查问的性能吗？

18. 什么状况下不走索引（索引生效）？