数据库相关问题

数据库三范式

第一范式：确保每列的原子性,每列都是不可分割的最小数据单元
第二范式：在第一范式的基础上，要求每列都和主键相关
第三范式：在第二范式的基础上，要求其他列和主键是直接相关，而不是间接相关

分别说一下范式和反范式的优缺点

1.范式化

优点：

减少苏局冗余
表中重复数据较少，更新操作比较快
范式化的表通常比范式化的表小

缺点：

在查询的时候通常需要很多关联，降低性能
增加了索引优化的难度

2.反范式化

优点：

可以减少表的关联
更好的进行索引优化

缺点：

数据重复冗余
对数据表的修改需要更多的成本

Mysql 数据库索引。B+ 树和 B 树的区别

MySQL数据库索引和存储引擎有关，MyISAM和InnoDB只支持BTREE索引。MEMORY和HEAP支持HASH和BTREE索引

B+树和B树的区别

B+树非叶子节点只存储关键字和指向子节点的指针，而B树还存储了数据，在同样大小的情况下，B+树可以存储更多的关键字
B+树叶子节点存储了所有关键字和数据，并且多个节点用链表连接。可以快速支撑范围查找
B+树非叶子节点不存储数据，所以查询时间复杂度固定为O(logN)，B树查询时间复杂度不固定，最好是O(1)
参考：https://www.jianshu.com/p/ace3cd6526c4
为什么 B+ 树比 B 树更适合应用于数据库索引，除了数据库索引，还有什么地方用到了（操作系统的文件索引）
因为B树叶子节点和非叶子结点都存储了数据，这样就导致了非叶子结点能保存的关键字和指针变少，如果要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能降低

除数据库索引，还有操作系统的文件索引用到了B树。参考文章：https://blog.csdn.net/qq_33369979/article/details/89810632

聚簇索引和非聚簇索引

聚簇索引，又叫主键索引，每个表只有一个主键索引，叶子节点保存主键的值和数据
非聚簇索引，又叫辅助索引，叶子节点保存索引字段的值和主键的值

前缀索引和覆盖索引

1.前缀索引
对于列的值较长，比如BLOB、TEXT、VARCHAR，就必须建立前缀索引，即将值的前一部分作为索引。这样既可以节约空间，又可以提高查询效率。但无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。

2.覆盖索引
select的数据列从索引中就能获得，不必再从数据表中读取。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

介绍一下数据库的事务

事务是一个操作序列，这些操作要么全部执行，要么都不执行。
事务具有四大特性：A（原子性）、C（一致性）、I（隔离性）、D（持久性）

Mysql 有哪些隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

Mysql 什么情况会造成脏读、可重复度、幻读？如何解决

脏读：有两个事务A和B，A读取已经被B修改但未提交的字段，此时B回滚，那么A读取的字段就是临时且无效的。可以提高隔离级别，改成读已提交
不可重复读：有两个事务A和B，A读取了一个字段值，然后B更新并且提交事务，A再重新读取这个字段，就和之前不相等了。可以提高隔离级别，改成可重复读
幻读：有两个事务A和B，A读取某个范围内的记录时，B又在该范围内插入了新的记录并提交，当事务A再次读取该范围的记录时，会产生幻行。可以升级隔离级别到串行化，或者使用 MVCC + next-key锁机制实现

Mysql 在可重复度的隔离级别下会不会有幻读的情况，为什么？

不会。InnoDB存储引擎默认隔离级别为RR，通过MVCC + next-key锁机制解决了幻读的问题。

PS：其实严格来说，是存在幻读的。。。可以尝试一下这个操作，A开启事务，执行查询，此时B开启事务新增一条数据并提交，此时A再查询，发现没有幻读，但是如果A执行一个update操作，再查询，会发现出现了幻读。我认为应该是A在执行update操作的时候，新建了一条创建版本号为A事务版本号的记录，然后标记B事务创建的记录为待删除的，查询的版本号依据是删除版本号为空或大于当前版本号，并且创建版本号小于等于当前事务版本号，那么这里刚刚A更新的这条数据，显然也符合查询的条件，所以也会被查出来。

MVCC版本号原理参考文章 https://www.cnblogs.com/shujiying/p/11347632.html
详细测试参考文章 https://blog.csdn.net/w139074301/article/details/111052454
Mysql 事务是如何实现的
原子性：通过undo log实现的。每条数据变更都伴随一条undo log日志的生成，当系统发生错误或执行回滚根据undo log做逆向操作
持久性：通过redo log实现的。redo log记录了数据的修改日志。数据持久化到磁盘，先是储存到缓冲池里，然后缓冲池中的数据定期同步到磁盘中，如果系统宕机，可能会丢失数据，系统重启后会读取redo log恢复数据
隔离性：mysql数据库通过MVCC + next-key机制实现了隔离性
一致性：以上3大特性，保障了事务的一致性
Binlog 和 Redo log 的区别是什么，分别是什么用？
binlog是二进制文件，记录了对数据库执行更改的所有操作，不包括 select、show，因为这两个操作没有对数据本身做修改。但是若操作了数据，但是数据没有发生变化，也会记录到binlog。常用来数据恢复，数据备份。
redo log又叫做重做日志文件，记录了事务的修改，不管事务是否提交都记录下来。在实例和介质失败时，InnoDB存储引擎会使用redo log恢复到之前的状态，保证数据的完整性

谈一谈 MVCC 多版本并发控制

MVCC是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号（system version number)。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

SELECT
InnoDB会根据以下两个条件检查每行记录：
InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。
只有符合上述两个条件的记录，才能返回作为查询结果
INSERT
InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
DELETE
InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
UPDATE
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

Innodb 和 MyISAM 的区别是什么

Innodb 支持事务。MyISAM 不支持
Innodb 支持外键。MyISAM 不支持
Innodb 主键索引的叶子节点是数据文件，辅助索引的叶子节点是主键的值。MyISAM 的主键索引和辅助索引，叶子节点都是数据文件的指针
Innodb 不保存表的行数，执行 select count(*) from tb需要全表扫描。MyISAM 用一个变量保存了整个表的行数，执行上述语句只需要读取该变量，速度很快
Innodb 所有的表在磁盘上保存在一个文件中。MyISAM 存储成三个文件。
Innodb 需要更多的内存和存储。MyISAM 可被压缩，存储空间较小。
Innodb 移植方案拷贝文件、备份 binlog，或者用 mysqldump，移植较困难。MyISAM 数据以文件形式存储，在备份和回复时可以单独针对表进行操作
Innodb 支持行锁、表锁。MyISAM 支持表锁
Innodb 在5.7版本之前不支持全文索引。MyISAM 支持全文索引

Innodb 的默认加锁方式是什么，是怎么实现的

Innodb默认加锁方式是行级锁
通过给索引上的索引项加锁来实现的

如何高效处理大库 DDL

DDL是值数据定义语句，即建表，建视图这种，所以这里的问题，我认为可能是考察建表的时候注意事项。
比如数据字段的定义，遵循从小原则。表的创建，降低耦合。

Mysql 索引重建

mysqldump导出然后重新导入，drop index + recreate index
alter table xxx ENGINE = InnoDB
repaire table xxx，这种对于InnoDB的无效
OPTIMIZE TABLE xxx

对于多列索引，哪些情况下能用到索引，哪些情况用不到索引

like以%开头
or查询，必须左右字段都是索引，否则索引失效
联合索引，遵从最左匹配原则，如果不是使用第一列索引，索引失效
数据出现隐形转换，如varchar字段没加单引号，自动转为int类型，会使索引失效
索引字段使用not、<>、!=，索引失效
索引字段使用函数，索引无效

为什么使用数据库索引可以提高效率，在什么情况下会用不到数据库索引？

默认执行SQL语句是进行全表扫描，遇到匹配条件的就加入搜索结果合集。如果有索引，就会先去索引表中一次定位到特定值的行数，减少遍历匹配的行数。索引把无序的数据变成了相对有序的数据结构。

什么情况用不到数据库索引见上题回答

共享锁和排他锁的使用场景，

更新、新增、删除默认加排它锁，查询默认不加锁
共享锁，使用语法select * from tb lock in share mode，自身可以读，其他事务也可以读（也可以继续加共享锁），但是其他事务无法修改
排它锁，适用语法select * from tb for update，自身可以进行增删改查，其他事务无法进行任何操作

关系型数据库和非关系数据库的优缺点

关系型数据库

优点：
二维表格，容易理解
操作方便
易于维护
支持SQL

缺点：
读写性能较差
固定的表结构，不够灵活
应对高并发场景，磁盘I/O存在瓶颈
海量数据的读写性能差

非关系型数据库

优点：
不需要SQL解析，读写性能高
可以使用硬盘或者内存作为载体，速度快
基于键值对，数据没有耦合性，方便扩展
部署简单

缺点：
不支持SQL，增加了学习成本
没有事务

Mysql 什么情况会造成慢查，如何查看慢查询

响应时间超过阈值会产生慢查询日志。一般有以下情况会造成查询慢

没有设置索引，或查询没有用到索引
I/O吞吐量过小
内存不足
网络速度慢
查询的数据量过大
锁或者死锁
返回了不必要的行或列
查询语句存在问题，需要优化
慢查询日志默认是关闭的，如果非必要，不要开启，会影响性能。

使用SHOW VARIABLES LIKE ‘slow_query%’;

slow_query_log，慢查询开启关闭状态

slow_query_log_file，慢查询日志存储位置，用文本编辑器打开存储位置的文件，查询慢查询

如何处理慢查询，你一般是怎么处理慢查询的

把数据、日志、索引放到不同的I/O设备上，增加读取速度
纵向、横向分割表，减少表的尺寸
升级硬件
根据查询条件，建立索引，索引优化
提高网速
扩大服务器内存
分库分表

Mysql 中 varchar 和 char 的区别

varchar会根据存储的内容改变长度，char是定长，如果长度不够，则使用空格补齐

数据库外键的优缺点

优点：
能最大限度的保证数据的一致性和完整性
增加ER图的可读性

缺点：
影响数据操作的效率
增加开发难度，导致表过多

有没有使用过数据库的视图

使用create view view_name as select * from tb创建视图
使用select * from view_name正常查询视图

Mysql 中插入数据使用自增 id 好还是使用 uuid，为什么？

单实例或单节点组，不担心网络爬虫获取数据量，推荐使用自增id，性能更好
分布式场景。20个节点下的小规模分布式场景，推荐uuid。20~200个节点的中规模分布式场景，推荐自增id+步长的策略。200以上节点，推荐推特雪花算法的全局自增ID

Mysql 有哪些数据类型，使用的时候有没有什么注意点

整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、INT、BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TIMESTAMP、TIME、YEAR

使用的时候建议遵循从小原则。

使用char和vahrchar的时候，注意char会去掉字符串末尾的空格
使用text和blob的时候，注意定期清理碎片空间，使用OPTIMIZE TABLE命令
浮点数会造成精度丢失，尽量使用定点数DECIMAL

Mysql 集群有哪几种方式，分别适用于什么场景

组建MySQL集群的方式：
LVS + Keepalived + MySQL
DRBD + Heartbeat + MySQL
MySQL + Proxy
MySQL Cluster
MySQL + MHA
MySQL + MMM

场景：
如果是双主复制，不需要数据拆分，可以使用MHA或Keepalived或Heartbeat
如果是双主复制，需要数据拆分，采用Cobar
如果是双主复制+Slave，还做了数据拆分，需要读写分离，采用Amoeba

Mysql 主从模式如何保证主从强一致性

主从复制原理：master写数据留下写入日志，slave根据master留下的日志模仿数据执行过程写入
所以有两个步骤可能导致主从复制不一致：

master日志写入不成功
slave根据日志模仿不成功
解决办法；
1.master上修改配置
1
2
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
上述两个选项的作用是：保证每次事务提交后，都能实时刷新到磁盘中，尤其是确保每次事务对应的binlog都能及时刷新到磁盘中
slave上修改配置
1
2
3
master_info_repository = "TABLE"
relay_log_info_repository = "TABLE"
relay_log_recovery = 1
上述前两个选项的作用是：确保在slave上和复制相关的元数据表也采用InnoDB引擎，受到InnoDB事务安全的保护，而后一个选项的作用是开启relay log自动修复机制，发生crash时，会自动判断哪些relay log需要重新从master上抓取回来再次应用，以此避免部分数据丢失的可能性。

Mysql 集群如何保证主从可用性

使用HA检测工具。HA工具部署在第三台服务器上，同时连接主从，检测主从是否存活。如果主库宕机则及时将从库升级为主库，将原来的主库降级为从库

Mysql 读写分离有哪些解决办法

配置多数据源
使用中间件代理
参考：https://www.jianshu.com/p/bc45c8bccf3c
参考资料
https://leetcode-cn.com/circle/discuss/6xECGC/