原创

MySQL面试题

1 基础篇

1.1 MySQL 中有哪几种锁？

1.2 MySQL 中有哪些不同的表格？

1.3 简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

1.4 简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

1.5 MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

1.6 CHAR 和 VARCHAR 的区别？

1.7 主键和候选键有什么区别？

1.8 myisamchk 是用来做什么的？

1.9 如果一个表有一列定义为 TIMESTAMP，将发生什么？

每当行被更改时，时间戳字段将获取当前时间戳。

1.10 列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用

1.11 怎样才能找出最后一次插入时分配了哪个自动增量？

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

1.12 你怎么看到为表格定义的所有索引？

索引是通过以下方式为表格定义的：

SHOW INDEX FROM <tablename>;

1.13 LIKE 声明中的％和`_`是什么意思？

％对应于 0 个或更多字符，_只是 LIKE 语句中的一个字符。

1.14 如何在 Unix 和 MySQL 时间戳之间进行转换？

UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令

1.15 列对比运算符是什么？

在 SELECT 语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR 或 LIKE 运算符。

1.16 BLOB 和 TEXT 有什么区别？

BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写的 BLOB。

BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

1.17 MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么？

1.18 MyISAM 表格将在哪里存储，并且还提供其存储格式？

1.19 MySQL 如何优化 DISTINCT？

1.20 如何显示前 50 行？

1.21 可以使用多少列创建索引？

任何标准表最多可以创建 16 个索引列。

1.22 NOW（）和 CURRENT_DATE（）有什么区别？

NOW（）命令用于显示当前年份，月份，日期，小时，分钟和秒。
CURRENT_DATE（）仅显示当前年份，月份和日期。

1.23 什么是非标准字符串类型？

1、TINYTEXT
2、TEXT
3、MEDIUMTEXT
4、LONGTEXT

1.24 什么是通用 SQL 函数？

1.25 MySQL 支持事务吗？

1.26 MySQL 里记录货币用什么字段类型好

1.27 MySQL 有关权限的表都有哪几个?

MySQL 服务器通过权限表来控制用户对数据库的访问，权限表存放在 MySQL 数据库里，由 MySQL_install_db 脚本初始化。这些权限表分别 user，db，table_priv，columns_priv 和 host。

1.28 列的字符串类型可以是什么？

1.29 MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年，怎么优化？

1、设计良好的数据库结构，允许部分数据冗余，尽量避免 join 查询，提高效率。
2、选择合适的表字段数据类型和存储引擎，适当的添加索引。
3、MySQL 库主从读写分离。
4、找规律分表，减少单表中的数据量提高查询速度。
5、添加缓存机制，比如 memcached，apc 等。
6、不经常改动的页面，生成静态页面。
7、书写高效率的 SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1,field_2, field_3 FROM TABLE.

1.30 锁的优化策略

1、读写分离
2、分段加锁
3、减少锁持有的时间
4.多个线程尽量以相同的顺序去获取资源
5.
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。

1.31 索引的底层实现原理和优化

B+树，经过优化的 B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

1.32 什么情况下设置了索引但无法使用

1、以“%”开头的 LIKE 语句，模糊匹配
2、OR 语句前后没有同时使用索引
3、数据类型出现隐式转化（如 varchar 不加单引号的话可能会自动转换为 int 型）

1.33 实践中如何优化 MySQL

1.34 优化数据库的方法

1.35 简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

1.36 数据库中的事务是什么?

1.37 SQL 注入漏洞产生的原因？如何防止？

1.37 为表中得字段选择合适得数据类型

字段类型优先级: 整形>date,time>enum,char>varchar>blob,text优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型

1.38 存储时期

1.39 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题

1.40 解释 MySQL 外连接、内连接与自连接的区别

1.41 Myql 中的事务回滚机制概述

1.42 SQL 语言包括哪几部分？每部分都有哪些操作关键字？

1.43 完整性约束包括哪些？

1.44 什么是锁？

1.45 什么叫视图？游标是什么？

1.46 什么是存储过程？用什么来调用？

1.47 如何通俗地理解三个范式？

1.48 什么是基本表？什么是视图？

1.49 试述视图的优点？

1.50 NULL 是什么意思

1.51 主键、外键和索引的区别？

1.52 你可以用什么来确保表格里的字段只接受特定范围里的值?

1.53 说说对 SQL 语句优化有哪些方法？（选择几条）

2 索引篇

2.1 索引是什么？

索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
索引一般存储在磁盘的文件中，它是占用物理空间的。
正所谓水能载舟，也能覆舟。适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

2.2 MySQL 索引有哪些类型？

数据结构维度

B+ 树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
哈希索引：适合等值查询，检索效率高，一次到位。
全文索引：MyISAM和InnoDB中都支持使用全文索引，一般在文本类型char、text、varchar类型上创建。
R-Tree索引：用来对GIS数据类型创建SPATIAL索引。

物理存储维度

聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

逻辑维度

主键索引：一种特殊的唯一索引，不允许有空值。
普通索引：MySQL中基本索引类型，允许空值和重复值。
联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
唯一索引：索引列中的值必须是唯一的，但是允许为空值。
空间索引：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。

2.3 索引什么时候会失效？

查询条件包含or，可能导致索引失效。
如果字段类型是字符串，where时一定用引号括起来，否则索引失效。
like通配符可能导致索引失效。
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
在索引列上使用 mysql 的内置函数，索引失效。
对索引列运算（如+、-、*、/），索引失效。
索引字段上使用（!= 或者 < >、not in）时，可能会导致索引失效。
索引字段上使用is null、is not null，可能导致索引失效。
左连接查询或者右连接查询关联的字段编码格式不一样，可能导致索引失效。
mysql 估计使用全表扫描要比使用索引快，则不使用索引。

2.4 哪些场景不适合建立索引？

数据量少的表，不适合加索引。
更新比较频繁的也不适合加索引。
区分度低的字段不适合加索引（如性别）。
where、group by、order by等后面没有使用到的字段，不需要建立索引。
已经有冗余的索引的情况（比如已经有a,b的联合索引，不需要再单独建立a索引）。

2.5 为什么要用 B+ 树，为什么不用二叉树？

可以从几个维度去看这个问题，查询是否够快，效率是否稳定，存储数据多少，以及查找磁盘次数，为什么不是二叉树，为什么不是平衡二叉树，为什么不是 B 树，而偏偏是 B+ 树呢？

为什么不是一般二叉树？
如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

为什么不是平衡二叉树呢？
我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据，如果是 B 树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

那为什么不是 B 树而是 B+ 树呢？

B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。innodb 中页的默认大小是 16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的 IO 次数也会再次减少，数据查询的效率也会更快。
B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。因此 B+ 树使得范围查找、排序查找、分组查找以及去重查找变得异常简单。

2.6 一次 B+ 树索引树查找过程

假设有以下表结构，并且初始化了这几条数据：

CREATE TABLE `employee` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `sex` int(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into employee values(100,'小伦',43,'2021-01-20','0');
insert into employee values(200,'俊杰',48,'2021-01-21','0');
insert into employee values(300,'紫琪',36,'2020-01-21','1');
insert into employee values(400,'立红',32,'2020-01-21','0');
insert into employee values(500,'易迅',37,'2020-01-21','1');
insert into employee values(600,'小军',49,'2021-01-21','0');
insert into employee values(700,'小燕',28,'2021-01-21','1');

执行这条查询 SQL，需要执行几次的树搜索操作？可以画下对应的索引树结构图。

select * from Temployee where age=32;

其实这个，大家可以先画出idx_age普通索引的索引结构图，大概如下：

file

再画出id主键索引，我们先画出聚族索引结构图，如下：

file

这条 SQL 查询语句执行大概流程是这样的：

搜索idx_age 索引树，将磁盘块 1加载到内存，由于32<43，搜索左路分支，到磁盘寻址磁盘块 2。
将磁盘块 2加载到内存中，由于32<36，搜索左路分支，到磁盘寻址磁盘块 4。
将磁盘块 4加载到内存中，在内存继续遍历，找到age=32的记录，取得id=400。
拿到id=400后，回到id 主键索引树。
搜索id 主键索引树，将磁盘块 1加载到内存，因为300<400<500，所以选择中间分支，到磁盘寻址磁盘块 3。
虽然在磁盘块 3，找到了 id=400，但是它不是叶子节点，所以会继续往下找。到磁盘寻址磁盘块 8。
将磁盘块 8加载内存，在内存遍历，找到id=400的记录，拿到R4这一行的数据，好的，大功告成。

2.7 什么是回表？如何减少回表？

当查询的数据在索引树中，找不到的时候，需要回到主键索引树中去获取，这个过程叫做回表。

比如在上文中使用的查询 SQL：

select * from Temployee where age=32;

需要查询所有列的数据，idx_age普通索引不能满足，需要拿到主键 id 的值后，再回到id主键索引查找获取，这个过程就是回表。

2.8 什么是覆盖索引？

如果我们查询 SQL 的select * 修改为 select id, age的话，其实是不需要回表的。因为id和age的值，都在idx_age索引树的叶子节点上，这就涉及到覆盖索引的知识点了。

覆盖索引是select的数据列只用从索引中就能够取得，不必回表，换句话说，查询列要被所建的索引覆盖。

2.9 聊聊索引的最左前缀原则

索引的最左前缀原则，可以是联合索引的最左 N 个字段。比如你建立一个组合索引(a,b,c)，其实可以相当于建了(a)、(a,b)、(a,b,c)三个索引，大大提高了索引复用能力。

当然，最左前缀也可以是字符串索引的最左 M 个字符。比如，你的普通索引树是这样子的：

file

这个 SQL：sql select * from employee where name like '小%' order by age desc; 也是命中索引的。
file

2.10 索引下推了解过吗？什么是索引下推？

给你这个 SQL：

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是 Mysql5.6 之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键 id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

file

有些朋友可能觉得奇怪，idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6 版本之后，选出包含“小”字后，过滤age=28。

file

2.11 大表如何添加索引？

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？

我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故。可以参考以下方法：

先创建一张跟原表A数据结构相同的新表B。
在新表B添加需要加上的新索引。
把原表A数据导到新表B。
rename新表B为原表的表名A，原表A换别的表名。

2.12 如何知道语句是否走索引查询？

explain查看 SQL 的执行计划，这样就知道是否命中索引了。

当explain与SQL一起使用时，MySQL 将显示来自优化器的有关语句执行计划的信息。

file

一般来说，我们需要重点关注type、rows、filtered、extra、key。

2.12.1 type

type 表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

system：这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。
const：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，速度非常快。
eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询。
ref ：常用于非主键和唯一索引扫描。
ref_or_null：这种连接类型类似于ref，区别在于MySQL会额外搜索包含NULL值的行。
index_merge：使用了索引合并优化方法，查询使用了两个以上的索引。
unique_subquery：类似于eq_ref，条件用了in子查询。
index_subquery：区别于unique_subquery，用于非唯一索引，可以返回重复值。
range：常用于范围查询，比如：between ... and 或 in 等操作。
index：全索引扫描。
ALL：全表扫描。

2.12.2 rows

该列表示 MySQL 估算要找到我们所需的记录，需要读取的行数。对于 InnoDB 表，此数字是估计值，并非一定是个准确值。

2.12.3 filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

2.12.4 extra

该字段包含有关 MySQL 如何解析查询的其他信息，它一般会出现这几个值：

Using filesort：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于 order by 语句。
Using index ：表示是否用了覆盖索引。
Using temporary：表示是否使用了临时表，性能特别差，需要重点优化。一般多见于 group by 语句，或者 union 语句。
Using where：表示使用了 where 条件过滤。
Using index condition：MySQL5.6 之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

2.12.5 key

该列表示实际用到的索引。一般配合possible_keys列一起看。

2.13 Hash 索引和 B+ 树区别是什么？设计索引时怎么抉择？

B+ 树可以进行范围查询，Hash 索引不能。
B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
B+ 树支持 order by 排序，Hash 索引不支持。
Hash 索引在等值查询上比 B+ 树效率更高。（但是索引列的重复值很多的话，Hash 冲突，效率降低）。
B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

2.14 索引有哪些优缺点？

优点：

索引可以加快数据查询速度，减少查询时间。
唯一索引可以保证数据库表中每一行的数据的唯一性。

缺点：

创建索引和维护索引要耗费时间。
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间。
以表中的数据进行增、删、改的时候，索引也要动态维护。

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

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚簇索引是索引结构和数据分开存放的索引。

接下来，我们分不同存储引擎来聊。

在MySQL的InnoDB存储引擎中，聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。

而在MyISM存储引擎中，它的主键索引、普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据。

正文到此结束

所属分类：数据库类

本文标签： MySQL Java
版权声明： 本站原创文章，于2023年02月08日由刀锋发布，转载请注明出处

MySQL面试题

MySQL面试题

1 基础篇

1.1 MySQL 中有哪几种锁？

1.2 MySQL 中有哪些不同的表格？

1.3 简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

1.4 简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

1.5 MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

1.6 CHAR 和 VARCHAR 的区别？

1.7 主键和候选键有什么区别？

1.8 myisamchk 是用来做什么的？

1.9 如果一个表有一列定义为 TIMESTAMP，将发生什么？

1.10 列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？

1.11 怎样才能找出最后一次插入时分配了哪个自动增量？

1.12 你怎么看到为表格定义的所有索引？

1.13 LIKE 声明中的％和_是什么意思？

1.14 如何在 Unix 和 MySQL 时间戳之间进行转换？

1.15 列对比运算符是什么？

1.16 BLOB 和 TEXT 有什么区别？

1.17 MySQL_fetch_array 和 MySQL_fetch_object 的区别是什么？

1.18 MyISAM 表格将在哪里存储，并且还提供其存储格式？

1.19 MySQL 如何优化 DISTINCT？

1.20 如何显示前 50 行？

1.21 可以使用多少列创建索引？

1.22 NOW（）和 CURRENT_DATE（）有什么区别？

1.23 什么是非标准字符串类型？

1.24 什么是通用 SQL 函数？

1.25 MySQL 支持事务吗？

1.26 MySQL 里记录货币用什么字段类型好

1.27 MySQL 有关权限的表都有哪几个?

1.28 列的字符串类型可以是什么？

1.29 MySQL 数据库作发布系统的存储，一天五万条以上的增量，预计运维三年，怎么优化？

1.30 锁的优化策略

1.31 索引的底层实现原理和优化

1.32 什么情况下设置了索引但无法使用

1.33 实践中如何优化 MySQL

1.34 优化数据库的方法

1.35 简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

1.36 数据库中的事务是什么?

1.37 SQL 注入漏洞产生的原因？如何防止？

1.37 为表中得字段选择合适得数据类型

1.38 存储时期

1.39 对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题

1.40 解释 MySQL 外连接、内连接与自连接的区别

1.41 Myql 中的事务回滚机制概述

1.42 SQL 语言包括哪几部分？每部分都有哪些操作关键字？

1.43 完整性约束包括哪些？

1.44 什么是锁？

1.45 什么叫视图？游标是什么？

1.46 什么是存储过程？用什么来调用？

1.47 如何通俗地理解三个范式？

1.48 什么是基本表？什么是视图？

1.49 试述视图的优点？

1.50 NULL 是什么意思

1.51 主键、外键和索引的区别？

1.52 你可以用什么来确保表格里的字段只接受特定范围里的值?

1.53 说说对 SQL 语句优化有哪些方法？（选择几条）

2 索引篇

2.1 索引是什么？

2.2 MySQL 索引有哪些类型？

2.3 索引什么时候会失效？

2.4 哪些场景不适合建立索引？

2.5 为什么要用 B+ 树，为什么不用二叉树？

2.6 一次 B+ 树索引树查找过程

2.7 什么是回表？如何减少回表？

2.8 什么是覆盖索引？

2.9 聊聊索引的最左前缀原则

2.10 索引下推了解过吗？什么是索引下推？

2.11 大表如何添加索引？

2.12 如何知道语句是否走索引查询？

2.12.1 type

2.12.2 rows

2.12.3 filtered

2.12.4 extra

2.12.5 key

2.13 Hash 索引和 B+ 树区别是什么？设计索引时怎么抉择？

2.14 索引有哪些优缺点？

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

热门推荐

相关文章

1.13 LIKE 声明中的％和`_`是什么意思？