MYSQL面试题(80-100)

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里面读取内容，从Exec_Master_Log_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、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 等12个字段。 我们重点关注的是type，它的属性排序如下：

system > const > eq_ref > ref > ref_or_null >
index_merge > unique_subquery > index_subquery >
range > index > ALL

92. 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之外。

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

因为事务在修改页时，要先记 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_DATE();

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隔离级别