阅读 1795

后端程序员必看的MySQL索引面试知识点

原创作者,公众号【程序员读书】,欢迎关注公众号,转载文章请注明出处哦。

与数据库索引有关的知识,说实在的,真的是很复杂,本来想好好看看这方面的东西,然后写篇文章详细谈谈的,后来发现索引的知识太难太深,要谈得全面又详细真的很难,所以最后还是把自己学到的和想到的变成下面一个个的问题,希望能对大家帮助!

知识点

问题1:什么是数据库索引?

数据库索引是数据库系统中一个重要的概念,索引也叫做key,是一种用于提升数据库查询效率的数据结构,我们可以把索引理解成一本书的目录,通过目录我们可以快速找到对应章节的内容,同样的,通过数据库索引,我们可以快速找到数据表中对应的记录。

总而言之,索引就像给数据表建了一个目录一样。

问题2:为什么在使用索引?

1 . 使用索引大大减少了存储引擎需要扫描的数据量,如果没有使用索引的话,每查询一行数据都要对数据表进行扫描,这样的话会非常慢。

2 . 由于索引已经排好序的,所以对数据表进行ORDER BYGROUP BY等操作时,可以很快得到结果。

3 . 索引可以将随机的I/O转为顺序的I/O,避免高昂的磁盘IO成本,提升查询效率。

问题3:MySQL索引在哪个模块中实现的?

MySQL的索引是在存储引擎这一层实现的,因此每一种存储引擎都有不同的实现方式,对同一种索引的处理方式也完成不同。

问题4:为什么设置了索引却不起作用?

如果使用以%开头的LIKE语句进行模糊匹配,则无法使用索引,如:

SELECT * FROM users WHERE name LIKE '%小张%';

SELECT * FROM users WHERE name LIKE '%小张';
复制代码

不过以%为结尾则可以使用索引,如:

SELECT * FROM users WHERE name LIKE '张%';
复制代码

OR语句前后没有同时使用索引,比如下面的语句,字段id有索引,而字段name没有创建索引,那么下面的语句只能全表扫描,无法用到索引:

SELECT * FROM users id = 10 or name='test'
复制代码

问题5:MySQL索引底层使用什么数据结构?

MySQL中,大部分情况下,索引都是使用B-Tree作为底层数据结构,B-Tree只是一种泛称,实际上不同的存储引擎使用B-Tree时,有不同的变种,比如InnoDB使用的是B+Tree

另外也有一些特殊的索引结构,比如哈希索引,哈希索引底层则使用的是哈希表,在MySQL中,只有Memory存储引擎支持哈希索引。

问题6:什么情况下数据表不适合创建索引?

1 . 对于用于存储归档历史数据的且很少用于查询的数据表,不建议创建索引。

2 . 数据量比较小的数据表,而且未来数据也不会有太大增长的数据,不应该建索引,比如用于保存配置的数据表。

3 . 修改频繁,且修改性能远大于查询性能时,不应该再创建索引。

问题7:什么是回表?

回表是对Innodb存储引擎而言的,在InnoDB存储引擎中,主键索引的叶子节点存储的记录的数据,而普通索引的叶子节点存储的主键索引的地点。

当我们通过主键查询时,只需要搜索主键索引的搜索树,直接可以得到记录的数据。

当我们通过普通索引进行查询时,通过搜索普通索引的搜索树得到主键的地址之后,还要再使用该主键对主键搜索树进行搜索,这个过程称为回表。

问题8:聚簇索引与非聚簇索引的区别?

聚簇索引:聚簇索引的顺序就是数据的物理存储顺序,并且索引与数据放在一块,通过索引可以直接获取数据,一个数据表中仅有一个聚簇索引。

非聚簇索引:索引顺序与数据物理排列顺序无关,索引文件与数据是分开存放。

问题9:MySQL主键索引、唯一索引与普通索引的区别?

设置为主键索引的字段不允许为NULL,而且一张数据表只能有一个主键索引。

设置为唯一索引的字段,其字段值不允许重要。

普通索引可以包含重复的值,也可以为NULL

问题10:索引可以提高查询性能,那是不是索引创建越多越好?

索引作为一个数据表的目录,本身的存储就需要消耗很多的磁盘和内存存储空间。

并助在写入数据表数据时,每次都需要更新索引,所以索引越多,写入就越慢。

尤其是糟糕的索引,建得越多对数据库的性能影响越大。

问题11:MyISAM与InnoDB在处理索引上有什么不同?

MyISAM存储引擎是非聚族索引,索引与数据是分开存储的,索引文件中记录了数据的指针

InnoDB存储引擎是聚族索引,即索引跟数据是放在一块的,InnoDB一般将主键与数据放在一块,如果没有主键,则将unique key作为主键,如果没有unique key,则自动创建一个rowid作为主键,其他二级索引叶子指针存储的是主键的位置。

问题12:什么是索引的最左前缀原则?

MySQL数据库不单可以为单个数据列创建索引,也可以为多个数据列创建一个联合索引,比如:

CREATE TABLE test(
    a INT NOT NOT,
    b INT NOT NOT,
    KEY(a,b)
);
复制代码

当我们使用下面的查询语句时,由于WHERE语句中查询的条件就是联合索引,所以可以很快查询到数据。

SELECT * FROM test WHERE a=1 AND b=1; 
复制代码

同样,下面的语句也会利用上面创建的联合索引,这是因为MySQL会按照索引创建的顺序进行排序,然后根据查询条件从索引最左边开始检测查询条件是否满足该索引,由于字段a在最左边,所以满足索引。

SELECT * FROM test WHERE a=1; 
复制代码

而使用字段b进行查询时,则为满足,因为从最左边匹配到的是字段a,所以MySQL判断为不满足索引条件。

SELECT * FROM test WHERE b=1; 
复制代码

从上面例子可以很好地了解索引的最左前缀原则,同时也说明了索引顺序的重要性。

问题13:什么是覆盖索引?

如果一个索引中包含查询所要的字段时,此时不需要再回表查询,我们就称该索引为覆盖索引。

比如下面的查询中,字段id是主键索引,所以可以直接返回索引的值,显著提升了查询的性能。

SELECT id FROM users WHERE id BETWEEN 10 AND 20;
复制代码

小结

当然,上面列出的只是索引的一小部分知识点,有什么回答不对的地方,欢迎指出。


如果你觉得文章不错,欢迎扫码关注,你的关注就是我写作的最大动力