mysql数据库常问面试题

傷城~ 2024-03-25 19:59 119阅读 0赞

**1、NOW()和CURRENT\_DATE()有什么区别？**  
NOW()命令用于显示当前年份，月份，日期，小时，分钟和秒。  
CURRENT\_DATE()仅显示当前年份，月份和日期。

**2、CHAR和VARCHAR的区别？**  
（1）CHAR和VARCHAR类型在存储和检索方面有所不同  
（2）CHAR列长度固定为创建表时声明的长度，长度值范围是1到255  
（3）当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。

**3、主键索引与唯一索引的区别**  
（1）主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。  
（2）主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。  
（3）唯一性索引列允许空值，而主键列不允许为空值。  
（4）主键列在创建时，已经默认为非空唯一索引了。  
（5） 一个表最多只能创建一个主键，但可以创建多个唯一索引。  
（6）主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。  
（7）主键可以被其他表引用为外键，而唯一索引不能。

**4、MySQL中有哪些不同存储引擎？**  
共有5种类型的存储引擎：  
MyISAM、 Heap、Merge、NNODB、ISAM

**5、SQL的生命周期？**  
（1）应用服务器与数据库服务器建立一个连接  
（2）数据库进程拿到请求sql  
（3）解析并生成执行计划，执行  
（4）读取数据到内存并进行逻辑处理  
（5）通过步骤一的连接，发送结果到客户端  
（6）关掉连接，释放资源

**6、你怎么看到为表定义的所有索引？**  
索引是通过以下方式查询  
show index from ;

**7、数据库为什么使用B+树而不是B树**  
(1)B树只适合随机检索，而B+树同时支持随机检索和顺序检索.  
(2)B+树空间利用率更高，可减少I/O次数，磁盘读写代价更低。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗。B+树的内部结点并没有指向关键字具体信息的指针，只是作为索引使用，其内部结点比B树小，盘块能容纳的结点中关键字数量更多，一次性读入内存中可以查找的关键字也就越多，相对的，IO读写次数也就降低了。而IO读写次数是影响索引检索效率的最大因素.  
(3) B+树的查询效率更加稳定。B树搜索有可能会在非叶子结点结束，越靠近根节点的记录查找时间越短，只要找到关键字即可确定记录的存在，其性能等价于在关键字全集内做一次二分查找。而在B+树中，顺序检索比较明显，随机检索时，任何关键字的查找都必须走一条从根节点到叶节点的路，所有关键字的查找路径长度相同，导致每一个关键字的查询效率相当。  
(4) B-树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作。  
(5)增删文件（节点）时，效率更高。因为B+树的叶子节点包含所有关键字，并以有序的链表结构存储，这样可很好提高增删效率。

**8、数据库三大范式是什么?**  
**第一范式：**  
每个列都不可以再拆分。  
**第二范式：**  
在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。  
**第三范式：**  
在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。  
在设计数据库结构的时候，要尽量遵守三范式，如果不遵守，必须有足够的理由。比如性能。事实上我们经常会为了性能而妥协数据库的设计。

**9、怎么优化SQL查询语句吗**  
（1）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
（2）SELECT子句中避免使用\*号，尽量全部大写SQL  
（3）应尽量避免在 where 子句中对字段进行 is null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用 IS NOT NULL  
（4）where 子句中使用 or 来连接条件，也会导致引擎放弃使用索引而进行全表扫描  
（5）in 和 not in 也要慎用，否则会导致全表扫描

**10、覆盖索引、回表等这些，了解过吗？**  
**覆盖索引：**  
查询列要被所建的索引覆盖，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。  
**回表：**  
二级索引无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表。

**11、MySQL数据库cpu飙升的话，要怎么处理呢？**  
**排查过程：**  
（1）使用top 命令观察，确定是MySQLd导致还是其他原因。  
（2）如果是MySQLd导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。  
（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。  
处理：  
（1）kill 掉这些线程(同时观察 cpu 使用率是否下降)，  
（2）进行相应的调整(比如说加索引、改 sql、改内存参数)  
（3）重新跑这些 SQL。  
**其他情况：**  
也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

**12、说说对SQL语句优化有哪些方法？（选择几条）**  
（1）Where子句中：where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。  
（2）用EXISTS替代IN、用NOT EXISTS替代NOT IN。  
（3）避免在索引列上使用计算  
（4）避免在索引列上使用IS NULL和IS NOT NULL  
（5）对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。  
（6）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描  
（7）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描

**13、Innodb的事务与日志的实现方式**  
innodb两种日志redo和undo。  
**日志的存放形式：**  
redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。  
Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodb\_undo\_tablespaces 参数把 undo log 存放在 ibdata之外。  
**事务是如何通过日志来实现的：**  
（1）因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。 Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。  
（2）当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。  
（3）如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

**14、非聚簇索引一定会回表查询吗？**  
不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询。  
举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

**15、Hash索引和B+树所有有什么区别或者说优劣呢?**  
首先要知道Hash索引和B+树索引的底层实现原理：  
hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。  
那么可以看出他们有以下的不同：  
（1）hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。  
（2）因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。  
（3） hash索引不支持使用索引进行排序，原理同上。  
（4）hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。AAAA和AAAAB的索引没有相关性。  
（5）hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。  
（6）hash索引虽然在等值查询上较快，但是不稳定。性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。  
（7）因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

**16、select for update有什么含义，会锁表还是锁行还是其他？**  
select for update 含义：  
select查询语句是不会加锁的，但是select for update除了有查询的作用外，还会加锁，而且它是悲观锁哦。至于加了是行锁还是表锁，这就要看是不是用了索引/主键啦。  
没用索引/主键的话就是表锁，否则就是是行锁。

**17、你们数据库是否支持emoji表情存储，如果不支持，如何操作？**  
更换字符集utf8–>utf8mb4

**18、索引的数据结构（b树，hash）分别介绍**  
索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。  
**B树索引：**  
MySQL通过存储引擎取数据，基本上90%的人用的就是InnoDB了，按照实现方式分，InnoDB的索引类型目前只有两种：BTREE（B树）索引和HASH索引。B树索引是MySQL数据库中使用最频繁的索引类型，基本所有存储引擎都支持BTree索引。通常我们说的索引不出意外指的就是（B树）索引（实际是用B+树实现的，因为在查看表索引时，MySQL一律打印BTREE，所以简称为B树索引）  
**哈希索引：**  
简要说下，类似于数据结构中简单实现的HASH表（散列表）一样，当我们在MySQL中用哈希索引时，主要就是通过Hash算法（常见的Hash算法有直接定址法、平方取中法、折叠法、除数取余法、随机数法），将数据库字段数据转换成定长的Hash值，与这条数据的行指针一并存入Hash表的对应位置；如果发生Hash碰撞（两个不同关键字的Hash值相同），则在对应Hash键下以链表形式存储。当然这只是简略模拟图。

**19、索引最左匹配原则？**  
在创建联合索引时候，一般需要遵循最左匹配原则。即联合索引中的属性识别度最高的放在查询语句的最前面。

**20、建立索引的目的是什么？**  
（1）快速访问数据表中的特定信息，提高检索速度。  
（2）创建唯一性索引，保证数据库表中每一行数据的唯一性。  
（3）加速表和表之间的连接。  
（4）使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

**21、索引对数据库系统的负面影响是什么？**  
创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

**22、为数据表建立索引的原则有哪些？**  
（1）在最频繁使用的、用以缩小查询范围的字段上建立索引。  
（2）在频繁使用的、需要排序的字段上建立索引

**23、什么情况下不宜建立索引？**  
（1）对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。  
（2）对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等  
（3）频繁增、删、改的表不宜建立索引。

**24、什么是最左前缀原则？什么是最左匹配原则？**  
最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。  
当我们创建一个组合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。。  
**25、myisamchk是用来做什么的？**  
它用来压缩MyISAM表，这减少了磁盘或内存使用。

**26、说说分库与分表的设计？**  
**分库分表方案:**  
（1）水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。  
（2）水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。  
（3）垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。  
（4）垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。  
**常用的分库分表中间件：**  
sharding-jdbc、Mycat、TDDL、Oceanus、vitess、Atlas  
**分库分表可能遇到的问题：**  
（1）事务问题：需要用分布式事务啦  
（2）跨节点Join的问题：解决这一问题可以分两次查询实现  
（3）跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。  
（4）D问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID  
（5）跨分片的排序分页问题（后台加大pagesize处理？）

**27、什么情况下设置了索引但无法使用**  
（1）以“%”开头的LIKE语句，模糊匹配  
（2）OR语句前后没有同时使用索引  
（3）数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）  
（4）对字段进行计算或使用函数  
（5）反向操作。如is not null  
**28、如何删除索引?**  
**删除主键索引：**  
alter table 表名 drop primary key（因为主键只有一个）。这里值得注意的是，如果主键自增长，那么不能直接执行此操作（自增长依赖于主键索引）  
**删除其他索引：**  
alter table 表名 drop index索引名

**29、什么是数据库连接池?为什么需要数据库连接池呢?**  
数据库连接池原理：  
在内部对象池中，维护一定数量的数据库连接，并对外暴露数据库连接的获取和返回方法。  
应用程序和数据库建立连接的过程：  
(1)通过TCP协议的三次握手和数据库服务器建立连接  
(2)发送数据库用户账号密码，等待数据库验证用户身份  
(3)完成身份验证后，系统可以提交SQL语句到数据库执行  
(4)把连接关闭，TCP四次挥手告别。  
数据库连接池好处：  
(1)资源重用 (连接复用)  
(2)更快的系统响应速度  
(3)新的资源分配手段  
(4) 统一的连接管理，避免数据库连接泄漏

**30、列对比运算符是什么？**  
在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。

**31、按照锁的粒度分，数据库锁有哪些呢？**  
按锁粒度分有：  
表锁，页锁，行锁  
按锁机制分有：  
乐观锁，悲观锁

**32、LIKE声明中的％和\_是什么意思？**  
％对应于0个或更多字符，\_只是LIKE语句中的一个字符。

**33、如何在Unix和MySQL时间戳之间进行转换？**  
UNIX\_TIMESTAMP是从MySQL时间戳转换为Unix时间戳的命令  
FROM\_UNIXTIME是从Unix时间戳转换为MySQL时间戳的命令

**34、如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？**  
对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。 我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。 而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

word文档下载地址：[mysql数据库常问面试题][mysql]

[mysql]: https://download.csdn.net/download/ma286388309/87586822