MySQL面试题【你必须要知道的事】（1）

忆春风人不如故

已于 2022-03-19 09:59:38 修改

阅读量188

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

于 2022-03-16 22:17:13 首次发布

原文链接：https://blog.csdn.net/wangfeijiu/article/details/113409719

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL索引（1）（吊打面试官）

前言

BATJTMD等大厂的面试难度越来越高，但无论从大厂还是到小公司，一直未变的一个重点就是对SQL优化经验的考察。一提到数据库，先“说一说你对Mysql的见解吧？”。Mysql已经成为衡量程序猿优秀与否的硬性指标，甚至在各大厂招聘岗位职能上都有明码标注，如果是你，在这个问题上能吊打面试官还是会被吊打呢

一、MySQL索引

索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度

1、索引类别及创建方式

	按功能分类

1. 普通索引

是最基本的索引，它没有任何限制他的创建方式如下
（1）直接创建

CREATE INDEX index_name ON table(column(length))

	（2） 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

	（3）创建表的时候同时创建索引

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

2. 唯一索引

`与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。*如果是组合索引，则列值的组合必须唯一`

（1）创建唯一索引

CREATE UNIQUE INDEX indexName ON table(column(length))

	（2）修改表结构

ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

	（3）创建表的时候直接指定`

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3. 主键索引

	`是一种特殊的唯一索引，一个	表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引`

（1）直接创建

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4. 组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 
create index 索引名 on 表名（字段名1，字段名2）

使用联合索引的优点

减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销
覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一
*效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% 10%=1w，效率提升可想而知！
```
按物理分类
```

a 聚簇索引

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是表的行记录数据时，方可称该主键索引为聚簇索引。聚簇索引也可理解为将数据存储与索引放到了一块，找到索引也就找到了数据

b 非聚簇索引

数据和索引是分开的，B+树叶子节点存放的不是数据表的行记录。

虽然InnoDB和MyISAM存储引擎都默认使用B+树结构存储索引，但是只有InnoDB的主键索引才是聚簇索引，InnoDB中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能拥有一个聚簇索引。

聚簇索引的优缺点

优点

数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对于主键的排序查找和范围查找速度非常快

缺点

插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键（主键列不要选没有意义的自增列，选经常查询的条件列才好，不然无法体现其主键索引性能）
更新主键的代价很高
二级索引访问需要两次索引查找

索引可以大大提高MySQL的检索速度，为什么不对表中的每一个列创建一个索引呢

优点：

索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因
**索引可以帮助服务器避免排序和创建临时表**
**索引可以帮我们从随机I/O 转变为有序I/O**
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

缺点

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；

上面我们说了这么多的索引的概念那么我们该如何创建优秀的索引结构呢？

索引的创建准则

应该创建的列

在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的（从有序性回答）
在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不应该创建的列

对于那些在查询中很少使用或者参考的列不应该创建索引。
若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度
对于那些定义为text, image和bit数据类型的列不应该增加索引。
这些列的数据量要么相当大，要么取值很少。

2、索引的结构

	MySQL中常用的索引结构（索引底层的数据结构）有：B-TREE ，B+TREE ，HASH 等

B-Tree

B-树就是B树，多路搜索树，树高一层意味着多一次的磁盘I/O，下图是3阶B树

B-Tree 的特征

关键字集合分布在整颗树中
任何一个关键字出现且只出现在一个结点中
搜索有可能在非叶子结点结束
其搜索性能等价于在关键字全集内做一次二分查找
自动层次控制

B+Tree

B+树是B-树的变体，也是一种多路搜索树

B+树的特征

所有的数据都出现在叶子结点的链表中，且链表是有序的
不可能在非叶子节点命中
非叶子节点相当于是叶子结点的稀疏索引
每一个叶子结点都有一个指向下一个节点的指针

HASH

哈希索引就是使用一定的hash算法将把键值换算成新的hash值检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

**hash索引只支持点对点的查询（"=",“IN"和”<=>"） 并不支持范围查询例如WHERE price > 100**
原因：因为hash索引本就是将hash算法后的值进行存储，因此无法判断hash后的数据是否还是原先的值的大小，所以无法进行比较

3 索引的存储位置

索引是占据物理空间的，在不同的存储引擎中，索引存在的文件也不同。存储引擎是基于表的，以下分别使用MyISAM和InnoDB存储引擎建立两张表。

存储引擎为MyISAM

*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
*.MYD：MyISAM DATA，用于存储MyISAM表的数据
*.MYI：MyISAM INDEX，用于存储MyISAM表的索引相关信息

存储引擎为MyISAM

*.frm：与表相关的元数据信息都存放在frm文件，包括表结构的定义信息等
ibd；表的数据与索引结构，该表的索引(B+树)的每个非叶子节点存储索引，叶子节点存储索引和索引对应的数据

4 补充知识点（MySQL 中的 key与index的区别）

key：等价普通索引 key 键名 (列)
	primary key：
		约束作用（constraint），主键约束（unique，not null，一表一主键，唯一标识记录），规范存储主键和强调唯一性
		为这个key建立主键索引
	unique key：

		约束作用（constraint），unique约束（保证列或列集合提供了唯一性）
		为这个key建立一个唯一索引；
	foreign key：

		约束作用（constraint），外键约束，规范数据的引用完整性
		为这个key建立一个普通索引；

5 InnoDB和MyISAM索引实现

InnoDB 的索引实现

	nnoDB使用B+TREE存储数据，除了主键索引为聚簇索引，其它索引均为非聚簇索引。
	一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引。
	InnoDB表的索引和数据是存储在一起的，.idb表数据和索引的文件

聚簇索引（主键索引）

B+树 叶子节点包含数据表中行记录就是聚簇索引（索引和数据是存放在一块的）

可以看到叶子节点包含了完整的数据记录，这就是聚簇索引。因为InnoDB的数据文件（.idb）按主键聚集，所以InnoDB必须有主键
（MyISAM可以没有），如果没有显示指定主键，
则选取首个为唯一且非空的列作为主键索引，如果还没具备，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形

非聚簇索引

在聚簇索引之外创建的索引（不是根据主键创建的）称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，
而是主键值。首先通过辅助索引找到主键值，然后到主键索引树中通过主键值找到数据行

InnoDB索引的优化

主键索引不易定义的过大因为辅助索引也会包含主键列，如果主键定义的比较大，其他索引也将很大。如果想在表上定义、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。
InnoDB中尽量不使用非单调字段作主键（不使用多列），因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择

MyISAM 的索引存储

 MyISAM也使用B+Tree作为索引结构，但具体实现方式却与InnoDB截然不同。MyISAM使用的都是非聚簇索引
 MyISAM表的索引和数据是分开存储的，.MYD表数据文件 .MYI表索引文件

MylSAM的主键索引

可以看到叶子节点的存放的是数据记录的地址。也就是说索引和行数据记录是没有保存在一起的，所以MyISAM的主键索引是非聚簇索引。

MylSAM的辅助索引

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。 MyISAM辅助索引也是非聚簇索引

7InnoDB和MyISAM的索引检索过程

对于InnoDB和MyISAM而言，主键索引是根据主关键字来构建的B+树存储结构，辅助索引则是根据辅助键来构造的B+树存储结构，彼此的索引树都是相互独立的。

InnoDB辅助索引的访问需要两次索引查找，第一次从辅助索引树找到主键值，第二次根据主键值到主键索引树中找到对应的行数据。

MyISM使用的是非聚簇索引，表数据存储在独立的地方，这两棵（主键和辅助键）B+树的叶子节点都使用一个地址指向真正的表数据。由于索引树是独立的，通过辅助键检索无需访问主键的索引树

假想一个表如下图存储了4行数据。其中Id作为主索引，Name作为辅助索引。图示清晰的显示了聚簇索引和非聚簇索引的差异。

8 聚簇索引和非聚簇索引的区别

聚簇索引的叶子节点存放的是数据行（主键值也是行内数据），支持覆盖索引；而二级索引的叶子节点存放的是主键值或指向数据行的指针。
由于叶子节点(数据页)只能按照一棵B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引。

9 最左前缀原则

最左匹配原则就是指在联合索引中，如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配
在 InnoDB 中联合索引只有先确定了前一个（左侧的值）后，才能确定下一个值。如果有范围查询的话，那么联合索引中使用范围查询的字段后的索引在该条 SQL 中都不会起作用
值得注意的是，in 和 = 都可以乱序，比如有索引（a,b,c），语句 select * from t where c =1 and a=1 and b=1，这样的语句也可以用到最左匹配，因为 MySQL 中有一个优化器，他会分析 SQL 语句，将其优化成索引可以匹配的形式，即 select * from t where a =1 and a=1 and c=1

10 前缀索引

有时候需要索引很长的字符列，这会让索引变得大且慢。**通常可以以某列开始的部分字符作为索引，这样可以大大节约索引空间，从而提高索引效率。**但这样也会降低索引的选择性。索引的选择性是指不重复的索引值和数据表的记录总数的比值， 索引的选择性越高则查询效率越高。
前缀字符并非越多越好，需要在索引的选择性和索引IO读取量中做出衡量

11 覆盖索引

覆盖索引其形式就是，搜索的索引键中的字段恰好是查询的字段（或是组合索引键中的其它字段）。覆盖索引的查询效率极高，原因在与其不用做回表查询

典型使用场景： 全表count查询，根据某关键字建立索引，直接count（关键字）即可，如果是count() 则需要回表搜索。（此项做保留，近期发现count() 也是使用了using index，有可能是新版本mysql内部做了优化处理）

注：遇到以下情况，执行计划不会选择覆盖查询

select选择的字段中含有不在索引中的字段，即索引没有覆盖全部的列。
where条件中不能含有对索引进行like的操作

12 全文索引

通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引，就是为这种场景设计的，通过建立倒排索引, 可以极大的提升检索效率,解决判断字段是否包含的问题。

倒排索引（英语：Inverted index），也常被称为反向索引、置入档案或反向档案，是一种索引方法，
被用来存储在全文搜索下某个单词在一个文档或者一组文档中的存储位置的映射。它是文档检索系统中最常用的数据结构

创建全文索引

//建表的时候
FULLTEXT KEY keyname(colume1,colume2)  // 创建联合全文索引列

//在已存在的表上创建
create fulltext index keyname on xxtable(colume1,colume2);

alter table xxtable add fulltext index keyname (colume1,colume2);

使用全文索引：

全文索引有独特的语法格式，需要配合match 和 against 关键字使用

match()函数中指定的列必须是设置为全文索引的列
against()函数标识需要模糊查找的关键字

全文索引关键字阈值

这其实跟全文搜索的关键词的长度阈值有关，可以通过show variables like ‘%ft%’;查看。可见InnoDB的全文索引的关键词最小索引长度为3。上文使用的是InnoDB引擎建表，同时也解释为什么只有3a以上才有搜索结果。

全文索引模式IN NATURAL LANGUAGE MODE

默认情况下，或者使用 IN NATURAL LANGUAGE MODE 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高
MySQL在全文查询中会对每个合适的词都会先计算它们的权重，如果一个词出现在多个记录中，那它只有较低的权重；相反，如果词是较少出现在这个集的文档中，它将得到一个较高的权重。

MySQL默认的阀值是50%。如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语

 上文关键词长度阈值是3，所以相当于只有两条记录：aaa 和 aaaa ，aaa 权重 2/2=100% 
 自然语言的搜索将不会搜索这类词语aaa了 而是进行精确查找 aaaa不会出现在aaa的结果集中

布尔全文索引 IN BOOLEAN MODE

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，这个模式和lucene中的BooleanQuery很像,可以通过一些操作符,
来指定搜索词在结果中的包含情况。

建立如下表：

CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT (title,body)
) ENGINE=InnoDB

- （AND）全文索引列必须包含该词且全文索引列（之一）有且仅有该词
- （NOT）表示必须不包含,默认为误操作符。如果只有一个关键词且使用了- ，会将这个当成错误操作，相当于没有查询关键词；如果有多个关键词，关键词集合中不全是负能符（~ -），那么-则强调不能出现

-- 查找title,body列中有且仅有apple（是apple不是applexx 也不是 xxapple）但是不含有banana的记录
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple -banana' IN BOOLEAN MODE);

提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后

-- 返回同时包含apple（是apple不是applexx 也不是 xxapple）和banana或者同时包含apple和orange的记录。但是同时包含apple和banana的记录的权重高于同时包含apple和orange的记录。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+apple +(>banana <orange)' IN BOOLEAN MODE);

12 索引失效

范围查询
如搜索键值以通配符%开头（如：like ‘%abc’），则索引失效，直接全表扫描；若只是以%结尾，则不影响索引构建
如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描。 select * from user where YEAR(birthday) < 1990
or 的条件列除了同时是主键的时候，索引才会生效。其他情况下的，无论条件列是什么，索引都失效
如果列是字符串类型，传入条件是必须用引号引起来，不然报错或索引失效。==隐式类型转换导致的索引失效） ==
** 普通索引使用 != 索引失效，主键索引没影响**。where语句中索引列使用了负向查询，可能会导致索引失效。
负向查询包括：NOT、!=、<>、NOT IN、NOT LIKE等。
order by 对主键索引排序会用到索引，其他的索引失效

忆春风人不如故

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL面试题【你必须要知道的事】（1）

MySQL索引（1）（吊打面试官）文章目录MySQL索引（1）（吊打面试官）前言一、MySQL索引1、索引类别及创建方式1. 普通索引2. 唯一索引3. 主键索引4. 组合索引a 聚簇索引b 非聚簇索引聚簇索引的优缺点索引的创建准则应该创建的列不应该创建的列2、索引的结构B-TreeB+TreeHASH3 索引的存储位置存储引擎为MyISAM存储引擎为MyISAM4 补充知识点（MySQL 中的 key与index的区别）5 InnoDB和MyISAM索引实现InnoDB 的索引实现MyISAM 的索引
复制链接

扫一扫