MySQL面试 - 索引篇

2021-02-09  本文已影响0人  程序猿蛋蛋哥
程序员五年状态.jpg

目录

MySQL索引是什么？

MySQL索引(键(key)).png

为什么要使用索引？（即索引的优缺点是什么？）

索引.png

创建，查看，删除索引的方式

1. 创建索引的三种方式：

第一种：建表时创建索引

CREATE TABLE student (
    id int unsigned not null auto_increment,
    num int unsigned not null,
    name varchar(255),
    primary key(id),
    unique key uk_num(num),
    index idx_name(name)
) ENGINE=InnoDB;

第二种：使用create index创建索引：能创建普通索引和唯一索引，不能创建主键索引

CREATE INDEX 索引名 ON 表名(column_list);
CREATE UNIQUE INDEX 索引名 ON 表名(column_list);
举例：mysql> CREATE INDEX idx_name ON student(name);

第三种：使用alter table创建索引

ALTER TABLE 表名 ADD INDEX [索引名](column_list);  -- 索引名可选，不写的话，默认索引名等于列名
ALTER TABLE 表名 ADD UNIQUE [索引名](column_list);
ALTER TABLE 表名 ADD PRIMARY KEY(column_list);
说明：索引名可选，不写的话，默认生成的索引名等于列名；如果是多列的联合索引，默认生成的索引名等于第一列的名字
举例：
mysql> ALTER TABLE student ADD INDEX idx_name(name); 
mysql> ALTER TABLE student ADD UNIQUE (num); -- 默认生成的唯一索引名字为num

2. 查看索引的两种方式：

第一种：SHOW INDEX FROM 表名;
第二种：SHOW KEYS FROM 表名;

3. 删除索引的两种方式：

第一种：DROP INDEX 索引名 ON 表名;
第二种：ALTER TABLE 表名 DROP INDEX 索引名;
ALTER TABLE 表名 DROP PRIMARY KEY; 
说明：
表只可能有一个primary key主键索引，所以不需要指定索引名
如果没有创建primary key主键索引，但表有一个或多个unique索引，则将删除第一个unique索引

总结：

MySQL创建查看删除索引.png

MySQL索引分类

MySQL索引.png

MySQL索引使用原则

MySQL索引使用原则.png

B-Tree索引的底层实现是什么？

B-Tree索引的底层实现是一个B+Tree而不是BTree

1. BTree和B+Tree结构在存储数据上的区别？

先分别来看一下BTree 和 B+Tree的结构：

BTree.png B+Tree.png

<1> BTree 和 B+Tree结构上的明显区别：

再来看看BTree 和 B+Tree在存储数据上的结构对比：

BTree存储数据结构.png B+Tree存储数据结构.png

<2> BTree 和 B+Tree在存储数据上的区别：

那具体看一下BTree和B+Tree 存储关键字key和数据量的情况：

BTree存储关键字key和数据量.png

BTree一个磁盘页能存储多少个关键字key呢？

这里假设关键字key，指针p都占用4byte，数据data占用1kb
计算：N*key + (N+1)p + Ndata = 16kb
则有：4Nb +4(N+1)b + 1000Nb = 16000b，1008Nb + 4b = 16000b，忽略较小的数，N = 16
BTree一个磁盘页能存储16个关键字key，孩子指针p有17个，存储数据总量 = 17 * 16kb = 272kb

B+Tree存储关键字key和数据量.png

B+Tree一个磁盘页能存储多少个关键字key呢？

计算：N*key + (N+1)p = 16kb
则有：4Nb + 4(N+1)b = 16000b，忽略较小的数，8Nb = 16000b，N = 2000
B+Tree一个磁盘页能存储2000个关键字key，孩子指针p有2001个，存储数据总量 = 2001 * 16kb = 32016kb

由此可见B+Tree能存储的关键字key和数据量跟BTree不是一个数量级别。

2. B+Tree为什么适合做索引？为什么不用红黑树或者BTree呢？

<1> 为什么不用红黑树？

总结：

<2> 为什么不用BTree而用B+Tree做索引？

总结：

3. 归纳总结

B-Tree索引.png

什么是哈希索引？

哈希索引基于Hash表实现，必须精确匹配索引所有列的查询才有效。

Hash表中的key：对于每一行数据，对所有索引列计算的一个哈希码
Hash表中的value：指向每个数据行的指针

<1> 哈希索引的数据结构：

哈希索引数据结构.png

<2> 哈希索引的限制：

什么是自适应哈希索引？

InnoDB引起有一个特殊的功能叫做“自适应哈希索引”，当索引值被使用的非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样也就有了哈希索引的一些优点，如快速的哈希查找。

什么是聚簇索引，非聚簇索引？

聚簇索引是将数据存放在索引树的叶子节点上，找到叶子节点就可以读取这行数据。InnoDB存储引擎的索引方式就是聚簇索引。一个表只能有一个聚簇索引，一般会根据主键或者唯一索引，或者以数据库内部生成的rowid为主键，来建立聚簇索引。

非聚簇索引是在索引树的叶子节点上存放数据的地址，找到该地址后，需要到磁盘中查询一次才能获取到数据。MyISAM存储引擎的索引方式就是非聚簇索引，只在索引树的叶子节点上存放地址。

聚簇索引也被称为主键索引，非聚簇索引也被称为二级索引。

对比上面的索引分类，聚簇索引不是一种单独的索引类型，而是一种数据存储方式：数据行和相邻的键值紧凑地存储在一起。

聚簇与非聚簇索引.png

<1> InnoDB的聚簇索引

InnoDB的聚簇索引本质：是在同一个结构中保存了B-Tree索引和数据行。

InnoDB通过主键聚集数据：

<2> 聚簇索引和非聚簇索引的区别？

InnoDB支持聚簇索引，MyISAM不支持聚簇索引

对比InnoDB和MyISAM的数据分布：

InnoDB数据分布.png MyISAM数据分布.png

区别总结如下：

<3> 聚簇索引的优缺点

聚簇索引.png

什么是覆盖索引？

覆盖索引是指查询的列正好是索引的一部分，那么它直接从索引上获取数据，而不需要到磁盘中查找数据，这种查询效率非常高。

比如：有一个用户表user，在用户名username上建立了索引，现在要查询user表的所有用户名：select username from user;
索引的叶子节点中包含了username的值，不需要再回表查数据行，直接取索引列的值即可，也就是说索引覆盖了要查询的username字段的值。

如下图结构所示：username上建立了索引，索引覆盖了要查询的username字段的值

索引覆盖username字段.png

覆盖索引其它相关总结如下：

覆盖索引.png

主键索引，唯一索引，普通索引

主键索引：不允许重复，不允许有空值，是唯一索引的一种特例
唯一索引：列值不允许重复，但允许有空值(NULL)。
普通索引：最基本的索引，没有任何限制，支持上面提到的三种创建索引方式。

主键唯一普通索引.png

持续更新......

上一篇 下一篇

猜你喜欢

热点阅读