推广

首页 > 行业资讯 > 推广 > 正文内容

推广

MySQL面试 – 索引篇

iseeyu3年前 (2024-02-21)推广138

程序员五年状态.jpg

MySQL索引是什么？
为什么要使用索引？
创建，查看，删除索引的方式
- 1. 创建索引的三种方式：
- 1. 查看索引的两种方式：
- 1. 删除索引的两种方式：
MySQL索引分类
MySQL索引使用原则
B-Tree索引的底层实现是什么？
- 1. BTree和B+Tree结构在存储数据上的区别？
  - <1> BTree 和 B+Tree结构上的明显区别：
  - <2> BTree 和 B+Tree在存储数据上的区别：
- 1. B+Tree为什么适合做索引？为什么不用红黑树或者BTree呢？
  - <1> 为什么不用红黑树？
  - <2> 为什么不用BTree而用B+Tree做索引？
- 1. 归纳总结
什么是哈希索引？
- <1> 哈希索引的数据结构：
- <2> 哈希索引的限制：
什么是自适应哈希索引？
什么是聚簇索引，非聚簇索引？
- <1> InnoDB的聚簇索引
- <2> 聚簇索引和非聚簇索引的区别？
- <3> 聚簇索引的优缺点
什么是覆盖索引？
主键索引，唯一索引，普通索引

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结构上的明显区别：

B+Tree 所有的数据都存储在叶子节点上。
B+Tree 每个叶子节点都有指向下一个叶子节点的链接，形成一种链式结构，这样的好处是：可以从任意一个叶子节点开始遍历，来获取所有的数据。

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

BTree存储数据结构.png

B+Tree存储数据结构.png

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

在BTree的节点中存放有孩子指针，关键字key，以及key对应的数据，而在B+Tree中，内部非叶子节点只存放孩子指针和关键字key，而key对应的所有数据存放在叶子节点上。
B+Tree非叶子节点不存储key对应的数据，因此相对于BTree，在同一块磁盘页上(InnoDB默认为16kb)可以存储更多的key和孩子指针，所以B+Tree存储的数据量比BTree大很多。

那具体看一下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> 为什么不用红黑树？

总结：

红黑树是二叉的，即一个节点最多有两个孩子，而BTree或B+Tree可以有很多孩子，即一个节点可以存储很多关键字
遍历时，每次读入内存的key值更多，相对来说I/O就降低，因此BTree或B+Tree性能更好一些

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

总结：

B+Tree 非叶子节点只存储 key 值和孩子指针，不存储key对应的数据，因此相对于BTree节点可以存储更多的关键字和数据量，每次读入内存的key值就更多，相对来说I/O就降低。
B+Tree 的叶子节点存储的是全量数据，并且有序，只需要遍历叶子节点就可以对所有的 key 进行扫描，查询范围时效率更高。

3. 归纳总结

B-Tree索引.png

什么是哈希索引？

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

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

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

哈希索引数据结构.png

<2> 哈希索引的限制：

哈希索引只包含哈希值和行指针，不存储字段值，不能使用索引中的值来避免读取行
哈希索引数据不是按照索引值顺序存储的，无法用于排序
哈希索引使用全部索引列来计算哈希值，不支持部分索引列匹配查找
哈希索引只支持等值比较查询：=，in()，<=>，不支持任何范围查找
当出现哈希冲突时，存储引擎必须遍历链表中所有行指针，逐行比较

什么是自适应哈希索引？

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

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

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

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

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

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

聚簇与非聚簇索引.png

<1> InnoDB的聚簇索引

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

InnoDB通过主键聚集数据：

主键为聚簇索引
若没有主键，选择一个唯一非空索引代替
若没有唯一非空索引，InnoDB会隐式定义一个主键来作为聚簇索引

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

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

对比InnoDB和MyISAM的数据分布：

InnoDB数据分布.png

MyISAM数据分布.png

区别总结如下：

InnoDB聚簇索引通过主键来聚集数据；若没有主键，则选择唯一非空索引，若没有唯一非空索引，则隐式生成一个主键
InnoDB聚簇索引聚集的就是表的数据行，每个叶子节点包含了主键值，事务ID，事务回滚指针以及数据行所有的剩余列。
InnoDB二级索引(非聚簇索引)不聚集数据，叶子节点存储的也不是数据的地址(即指向数据行物理位置的指针)，而是数据行的主键值。因此二级索引查找数据行需要两次索引查找。
MyISAM不支持聚簇索引，主键索引和二级索引都不聚集数据，叶子节点存储的是数据的地址。

<3> 聚簇索引的优缺点

聚簇索引.png

什么是覆盖索引？

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

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

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

索引覆盖username字段.png

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

覆盖索引.png

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

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

主键唯一普通索引.png

持续更新……

扫描二维码推送至手机访问。

转载请注明出处https://www.0291.com.cn/post/57571.html

返回列表

上一篇：从0开始搭建ClickHouse高可用集群

下一篇：Elasticsearch 基础

西安企业做百度推广，一个月花三千和花三万到底差在哪？

4|最近跟几个西安的老板聊天，发现一个挺有意思的现象：同样做百度推广，有人一个月花三千块，询盘不断；有人一个月砸三万，水花都没见着。问他们区别在哪，大多数人说不清楚，只知道'钱花了，没效果'。 5| 6|百度推广这东西，说复杂也复杂，说简单也简单。核心就一件事：让你的潜在客户搜某个词的时候，你的信息...