mysql-面试题续2

51. 何时使用聚簇索引与非聚簇索引

52. 非聚簇索引一定会回表查询吗？

不一定，如果查询语句的字段全部命中了索引，那么就不必再进行回表查询（哈哈，覆盖索引就是这么回事）。

举个简单的例子，假设我们在学生表的上建立了索引，那么当进行select age from student where age < 20的查询时，在索引的叶子节点上，已经包含了age信息，不会再次进行回表查询。

53. 组合索引是什么？为什么需要注意组合索引中的顺序？

组合索引，用户可以在多个列上建立索引,这种索引叫做组合索引。

因为InnoDB引擎中的索引策略的最左原则，所以需要注意组合索引中的顺序。

54. 什么是数据库事务？

数据库事务（简称：事务），是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

55. 隔离级别与锁的关系

回答这个问题，可以先阐述四种隔离级别，再阐述它们的实现原理。隔离级别就是依赖锁和MVCC实现的。

56. 按照锁的粒度分，数据库锁有哪些呢？锁机制与InnoDB锁算法

 

• 按锁粒度分有：表锁，页锁，行锁

• 按锁机制分有：乐观锁，悲观锁

 

57. 从锁的类别角度讲，MySQL都有哪些锁呢？

从锁的类别上来讲，有共享锁和排他锁。

• 共享锁: 又叫做读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。

• 排他锁: 又叫做写锁。当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。

锁兼容性如下：

58. MySQL中InnoDB引擎的行锁是怎么实现的？

基于索引来完成行锁的。

1. select * from t where id = 666 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将实行表锁。

59. 什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。看图形象一点，如下

 

有四个必要条件：互斥条件，请求和保持条件，环路等待条件，不剥夺条件。

解决死锁思路，一般就是切断环路，尽量避免并发形成环路。

• 如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

• 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

• 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

• 如果业务处理不好可以用分布式事务锁或者使用乐观锁

• 死锁与索引密不可分，解决索引问题，需要合理优化你的索引，

60. 为什么要使用视图？什么是视图？

为了提高复杂SQL语句的复用性和表操作的安全性，MySQL数据库管理系统提供了视图特性。

什么是视图？

视图是一个虚拟的表，是一个表中的数据经过某种筛选后的显示方式，视图由一个预定义的查询select语句组成。

61. 视图有哪些特点？哪些使用场景？

视图特点：

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。

• 视图是由基本表(实表)产生的表(虚表)。

• 视图的建立和删除不影响基本表。

• 对视图内容的更新(添加，删除和修改)直接影响基本表。

• 当视图来自多个基本表时，不允许添加和删除数据。

视图用途： 简化sql查询，提高开发效率，兼容老的表结构。

视图的常见使用场景：

• 重用SQL语句；

• 简化复杂的SQL操作。

• 使用表的组成部分而不是整个表；

• 保护数据

• 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。

62. 视图的优点，缺点，讲一下？

• 查询简单化。视图能简化用户的操作

• 数据安全性。视图使用户能以多种角度看待同一数据，能够对机密数据提供安全保护

• 逻辑数据独立性。视图对重构数据库提供了一定程度的逻辑独立性

63. count(1)、count(*) 与 count(列名) 的区别？

• count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

• count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

• count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

64. 什么是游标？

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。

65. 什么是存储过程？有哪些优缺点？

存储过程，就是一些编译好了的SQL语句，这些SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后给这些代码块取一个名字，在用到这个功能的时候调用即可。

优点：

• 存储过程是一个预编译的代码块，执行效率比较高

• 存储过程在服务器端运行，减少客户端的压力

• 允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用

• 一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率

• 可以一定程度上确保数据安全

缺点：

• 调试麻烦

• 可移植性不灵活

• 重新编译问题

66. 什么是触发器？触发器的使用场景有哪些？

触发器，指一段代码，当触发某个事件时，自动执行这些代码。

使用场景：

• 可以通过数据库中的相关表实现级联更改。

• 实时监控某张表中的某个字段的更改而需要做出相应的处理。

• 例如可以生成某些业务的编号。

• 注意不要滥用，否则会造成数据库及应用程序的维护困难。

67. MySQL中都有哪些触发器？

MySQL 数据库中有六种触发器：

• Before Insert

• After Insert

• Before Update

• After Update

• Before Delete

• After Delete

68. 超键、候选键、主键、外键分别是什么？

• 超键：在关系模式中，能唯一知标识元组的属性集称为超键。

• 候选键：是最小超键，即没有冗余元素的超键。

• 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

• 外键：在一个表中存在的另一个表的主键称此表的外键。。

69. SQL 约束有哪几种呢？

• NOT NULL: 约束字段的内容一定不能为NULL。

• UNIQUE: 约束字段唯一性，一个表允许有多个 Unique 约束。

• PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。

• FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。

• CHECK: 用于控制字段的值范围。

70. 谈谈六种关联查询，使用场景。

• 交叉连接

• 内连接

• 外连接

• 联合查询

• 全连接

• 交叉连接

71. varchar(50)中50的涵义

• 字段最多存放 50 个字符

• 如 varchar(50) 和 varchar(200) 存储 "jay" 字符串所占空间是一样的，后者在排序时会消耗更多内存

72. mysql中int(20)和char(20)以及varchar(20)的区别

• int(20) 表示字段是int类型，显示长度是 20

• char(20)表示字段是固定长度字符串，长度为 20

• varchar(20) 表示字段是可变长度字符串，长度为 20

73. drop、delete与truncate的区别

74. UNION与UNION ALL的区别？

• Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

• Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

• UNION的效率低于 UNION ALL

75. SQL的生命周期？

• 服务器与数据库建立连接

• 数据库进程拿到请求sql

• 解析并生成执行计划，执行

• 读取数据到内存，并进行逻辑处理

• 通过步骤一的连接，发送结果到客户端

• 关掉连接，释放资源

76. 一条Sql的执行顺序？

77. 列值为NULL时，查询是否会用到索引？

列值为NULL也是可以走索引的

计划对列进行索引，应尽量避免把它设置为可空，因为这会让 MySQL 难以优化引用了可空列的查询，同时增加了引擎的复杂度

78. 关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

• 我们平时写Sql时，都要养成用explain分析的习惯。

• 慢查询的统计，运维会定期统计给我们

优化慢查询：

• 分析语句，是否加载了不必要的字段/数据。

• 分析SQl执行句话，是否命中索引等。

• 如果SQL很复杂，优化SQL结构

• 如果表数据量太大，考虑分表

79. 主键使用自增ID还是UUID，为什么？

如果是单机的话，选择自增ID；如果是分布式系统，优先考虑UUID吧，但还是最好自己公司有一套分布式唯一ID生产方案吧。

• 自增ID：数据存储空间小，查询效率高。但是如果数据量过大,会超出自增长的值范围，多库合并，也有可能有问题。

• uuid：适合大量数据的插入和更新操作，但是它无序的，插入数据效率慢，占用空间大。

80. mysql自增主键用完了怎么办？

自增主键一般用int类型，一般达不到最大值，可以考虑提前分库分表的。

81. 字段为什么要求定义为not null？

null值会占用更多的字节，并且null有很多坑的。

82. 如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列，盐，用户身份证号等固定长度的字符串，应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

83. Mysql驱动程序是什么？

这个jar包：mysql-connector-java-5.1.18.jar

Mysql驱动程序主要帮助编程语言与 MySQL服务端进行通信，如连接、传输数据、关闭等。

84. 如何优化长难的查询语句？有实战过吗？

• 将一个大的查询分为多个小的相同的查询

• 减少冗余记录的查询。

• 一个复杂查询可以考虑拆成多个简单查询

• 分解关联查询，让缓存的效率更高。

85. 优化特定类型的查询语句

平时积累吧：

• 比如使用select 具体字段代替 select *

• 使用count(*) 而不是count(列名)

• 在不影响业务的情况，使用缓存

• explain 分析你的SQL

86. MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：

• 使用top 命令观察，确定是mysqld导致还是其他原因。

• 如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。

• 找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

• kill 掉这些线程(同时观察 cpu 使用率是否下降)，

• 进行相应的调整(比如说加索引、改 sql、改内存参数)

• 重新跑这些 SQL。

其他情况：

也有可能是每个sql消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等

87. 读写分离常见方案？

• 应用程序根据业务逻辑来判断，增删改等写操作命令发给主库，查询命令发给备库。

• 利用中间件来做代理，负责对数据库的请求识别出读还是写，并分发到不同的数据库中。（如：amoeba，mysql-proxy）

 

88. MySQL的复制原理以及流程

• 主从复制原理，简言之，就三步曲，如下：

  • 主数据库有个bin-log二进制文件，纪录了所有增删改Sql语句。（binlog线程）

  • 从数据库把主数据库的bin-log文件的sql语句复制过来。（io线程）

  • 从数据库的relay-log重做日志文件中再执行一次这些sql语句。（Sql执行线程）

  如下图所示：

上图主从复制分了五个步骤进行：

步骤一：主库的更新事件(update、insert、delete)被写到binlog

步骤二：从库发起连接，连接到主库。

步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。

步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log

步骤五：还会创建一个SQL线程，从relay log里面读取内容，从ExecMasterLog_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

89. MySQL中DATETIME和TIMESTAMP的区别

存储精度都为秒

区别：

• DATETIME 的日期范围是 1001——9999 年；TIMESTAMP 的时间范围是 1970——2038 年

• DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区

• DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节

• DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

90. Innodb的事务实现原理？

• 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。

• 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。

• 隔离性：通过锁以及MVCC,使事务相互隔离开。

• 一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性。

91. 谈谈MySQL的Explain

Explain 执行计划包含字段信息如下：分别是 id、selecttype、table、partitions、type、possiblekeys、key、key_len、ref、rows、filtered、Extra 等12个字段。

我们重点关注的是type，它的属性排序如下：

1. system > const > eq_ref > ref > ref_or_null >

2. index_merge > unique_subquery > index_subquery >

3. range > index > ALL

92. Innodb的事务与日志的实现方式

有多少种日志

innodb两种日志redo和undo。

日志的存放形式

• redo：在页修改的时候，先写到 redo log buffer 里面， 然后写到 redo log 的文件系统缓存里面(fwrite)，然后再同步到磁盘文件（ fsync）。

• Undo：在 MySQL5.5 之前， undo 只能存放在 ibdata文件里面， 5.6 之后，可以通过设置 innodbundotablespaces 参数把 undo log 存放在 ibdata之外。

事务是如何通过日志来实现的

• 因为事务在修改页时，要先记 undo，在记 undo 之前要记 undo 的 redo， 然后修改数据页，再记数据页修改的 redo。Redo（里面包括 undo 的修改） 一定要比数据页先持久化到磁盘。

• 当事务需要回滚时，因为有 undo，可以把数据页回滚到前镜像的 状态，崩溃恢复时，如果 redo log 中事务没有对应的 commit 记录，那么需要用 undo把该事务的修改回滚到事务开始之前。

• 如果有 commit 记录，就用 redo 前滚到该事务完成时并提交掉。

93. MySQL中TEXT数据类型的最大长度

• TINYTEXT：256 bytes

• TEXT：65,535 bytes(64kb)

• MEDIUMTEXT：16,777,215 bytes(16MB)

• LONGTEXT：4,294,967,295 bytes(4GB)

94. 500台db，在最快时间之内重启。

• 可以使用批量 ssh 工具 pssh 来对需要重启的机器执行重启命令。

• 也可以使用 salt（前提是客户端有安装 salt）或者 ansible（ ansible 只需要 ssh 免登通了就行）等多线程工具同时操作多台服务

95. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？

监控的工具有很多，例如zabbix，lepus，我这里用的是lepus

96. 你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？

主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

97. 你们数据库是否支持emoji表情存储，如果不支持，如何操作？

更换字符集utf8-->utf8mb4

98. MySQL如何获取当前日期？

SELECT CURRENT_TIME(); #获取当前时间
SELECT CURRENT_TIME();16:50:16
SELECT CURRENT_DATE (); # 2023-08-03
SELECT CURRENT_TIMESTAMP(); #2023-08-03 16:48:58

99. 一个6亿的表a，一个3亿的表b，通过外间tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。

1、如果A表TID是自增长,并且是连续的,B表的ID为索引 select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

 

100. Mysql一条SQL加锁分析

一条SQL加锁，可以分9种情况进行哈：

• 组合一：id列是主键，RC隔离级别

• 组合二：id列是二级唯一索引，RC隔离级别

• 组合三：id列是二级非唯一索引，RC隔离级别

• 组合四：id列上没有索引，RC隔离级别

• 组合五：id列是主键，RR隔离级别

• 组合六：id列是二级唯一索引，RR隔离级别

• 组合七：id列是二级非唯一索引，RR隔离级别

• 组合八：id列上没有索引，RR隔离级别

• 组合九：Serializable隔离级别