数据库知识点

基础知识

数据库三范式

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

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

1. 范式化
   优点：
   • 减少数据冗余
   • 表中重复数据较少，更新操作比较快
   • 范式化的表通常比反范式化的表小
     缺点：
   • 在查询的时候通常需要很多的关联，降低性能
   • 增加了索引优化的难度
2. 反范式化
   优点：
   • 可以减少表的关联
   • 更好的进行索引优化
     缺点：
   • 数据重复冗余
   • 对数据表的修改需要更多的成本

Mysql 中 varchar 和 char 的区别

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

char：

• char表示定长字符串，长度是固定的；
• 如果插入数据的长度小于char的固定长度时，则用空格填充；
• 因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；
• 对于char来说，最多能存放的字符个数为255，和编码无关

varchar：

• varchar表示可变长字符串，长度是可变的；
• 插入的数据是多长，就按照多长来存储；
• varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；
• 对于varchar来说，最多能存放的字符个数为65532

日常的设计，对于长度相对固定的字符串，可以使用char，对于长度不确定的，使用varchar更合适一些。

blob和text有什么区别？

blob用于存储二进制数据，而text用于存储大字符串。

blob没有字符集，text有一个字符集，并且根据字符集的校对规则对值进行排序和比较

DATETIME和TIMESTAMP的异同？

相同点：

• 两个数据类型存储时间的表现格式一致。均为 YYYY-MM-DD HH:MM:SS
• 两个数据类型都包含「日期」和「时间」部分。
• 两个数据类型都可以存储微秒的小数秒（秒后6位小数秒）

区别：

• 日期范围：DATETIME 的日期范围是 1000-01-01 00:00:00.000000 到 9999-12-31 23:59:59.999999；TIMESTAMP 的时间范围是1970-01-01 00:00:01.000000 UTC 到 ``2038-01-09 03:14:07.999999 UTC
• 存储空间：DATETIME 的存储空间为 8 字节；TIMESTAMP 的存储空间为 4 字节
• 时区相关：DATETIME 存储时间与时区无关；TIMESTAMP 存储时间与时区有关，显示的值也依赖于时区
• 默认值：DATETIME 的默认值为 null；TIMESTAMP 的字段默认不为空(not null)，默认值为当前时间(CURRENT_TIMESTAMP)

MySQL中 in 和 exists 的区别？

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。我们可能认为exists比in语句的效率要高，这种说法其实是不准确的，要区分情景：

• 如果查询的两个表大小相当，那么用in和exists差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
• not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

MySQL里记录货币用什么字段类型比较好？

货币在数据库中MySQL常用 Decimal 和 Numric 类型表示，这两种类型被MySQL实现为同样的类型。他们被用于保存与货币有关的数据。

例如 列名salary DECIMAL(9,2)，9(precision)代表将被用于存储值的总的小数位数，而2(scale)代表将被用于存储小数点后的位数。存储在salary列中的值的范围是从 -9999999.99到9999999.99。

DECIMAL 和 NUMERIC 值作为字符串存储，而不是作为二进制浮点数，以便保存那些值的小数精度。
之所以不使用float或者double的原因：因为 float 和 double 是以二进制存储的，所以有一定的误差。

MySQL怎么存储emoji😊?

MySQL可以直接使用字符串存储emoji。
但是需要注意的，utf8 编码是不行的，MySQL中的utf8是阉割版的 utf8，它最多只用 3 个字节存储字符，所以存储不了表情。那该怎么办？
需要使用utf8mb4编码。

drop、delete与truncate的区别？
三者都表示删除，但是三者有一些差别：

操作	delete	truncate	drop
类型	属于DML	属于DDL	属于DDL
回滚	可回滚	不可回滚	不可回滚
删除内容	表结构还在，删除表的全部或者一部分数据行	表结构还在，删除表中的所有数据	从数据库中删除表，所有数据行，索引和权限也会被删除
删除速度	删除速度慢，需要逐行删除	删除速度快	删除速度最快

因此，在不再需要一张表的时候，用drop；在想删除部分数据行时候，用delete；在保留表而删除所有数据的时候用truncate。

UNION与UNION ALL的区别？

如果使用UNION ALL，不会合并重复的记录行
效率 UNION 高于 UNION ALL

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

执行效果：

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

执行速度：

• 列名为主键，count(列名) 会比 count(1) 快
• 列名不为主键，count(1) 会比 count(列名) 快
• 如果表多个列并且没有主键，则 count(1) 的执行效率优于 count(*)
• 如果有主键，则 select count(主键) 的执行效率是最优的
• 如果表只有一个字段，则 select count(*) 最优。

一条SQL查询语句的执行顺序？

1. FROM：对FROM子句中的左表<left_table>和右表<right_table>执行笛卡儿积（Cartesianproduct），产生虚拟表VT1
2. ON：对虚拟表VT1应用ON筛选，只有那些符合<join_condition>的行才被插入虚拟表VT2中
3. JOIN：如果指定了OUTER JOIN（如LEFT OUTER JOIN、RIGHT OUTER JOIN），那么保留表中未匹配的行作为外部行添加到虚拟表VT2中，产生虚拟表VT3。如果FROM子句包含两个以上表，则对上一个连接生成的结果表VT3和下一个表重复执行步骤1）～步骤3），直到处理完所有的表为止
4. WHERE：对虚拟表VT3应用WHERE过滤条件，只有符合<where_condition>的记录才被插入虚拟表VT4中
5. GROUP BY：根据GROUP BY子句中的列，对VT4中的记录进行分组操作，产生VT5
6. CUBE|ROLLUP：对表VT5进行CUBE或ROLLUP操作，产生表VT6
7. HAVING：对虚拟表VT6应用HAVING过滤器，只有符合<having_condition>的记录才被插入虚拟表VT7中
8. SELECT：第二次执行SELECT操作，选择指定的列，插入到虚拟表VT8中
9. DISTINCT：去除重复数据，产生虚拟表VT9
10. ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10
11. LIMIT：取出指定行的记录，产生虚拟表VT11，并返回给查询用户

数据库架构

说说 MySQL 的基础架构?

MySQL逻辑架构图主要分三层：

• 客户端：最上层的服务并不是MySQL所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
• Server层：大多数MySQL的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
• 存储引擎层：第三层包含了存储引擎。存储引擎负责MySQL中数据的存储和提取。Server层通过API与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。*

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

MySQL数据库的索引和存储引擎有关。MyISAM和InnoDB只支出B数索引，MEMORY和HEAP支持Hash和B树索引。
B+树和B数的区别

• B+树非叶子节点只存储关键字和指向子节点的指针，而B树还存储了数据，在同样大小的情况下，B+树可以存储更多的关键字
• B+树叶子节点存储了所有关键字和数据，并且多个节点用链表连接。可以快速支撑范围查找
• B+树非叶子节点不存储数据，所以查询时间复杂度固定为O(logN)，B树查询的时间复杂度不固定，最好是O(1)

为什么 B+ 树比 B 树更适合应用于数据库索引，除了数据库索引，还有什么地方用到了（操作系统的文件索引）

因为B树叶子节点和非叶子节点都存储了数据，这样就导致了非叶子节点能保存的关键字和指针变少，如果要保存大量的数据，只能增加树的高度，导致IO操作变多，查询性能降低。
处理数据库索引，还有操作系统的文件索引用到了B树。

聚簇索引和非聚簇索引

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

前缀索引和覆盖索引

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 什么情况会造成脏读、不可重复度、幻读？如何解决

1. 脏读：有两个事务A和B，A读取已经B修改但未提交的字段，此时B回滚，那么A读取的字段就是临时且无效的。可以提交隔离级别，改成读已提交
2. 不可重复读：有两个事务A和B，A读取了一个字段值，然后B更新并提交事务，A再重新读取这个字段，就和之前不相等了。可以提高隔离级别
3. 幻读：有两个事务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版本号原理参考文章：Mysql中MVCC的使用及原理详解

详细测试参考文章：MySQL可重复读级别会不会造成幻读

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只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。
  • 行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。
    只有符合上述两个条件的记录，才能返回作为查询结果。
• inster InnoDB为新插入的每一行保存当前系统版本号作为行号
• delete InnoDB为删除的每一行保存当前系统版本号作为行删除标识
• update InnoDB为插入一行新记录，保存当前系统版本号作为版本号，同时保存当前系统版号到原来的行为作为删除标识。

Innodb 和 MyISAM 的区别是什么

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

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

Mysql 索引重建

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

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

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

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

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

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

关系型数据库
优点：

• 二维表格，容易理解
• 操作方便
• 易于维护
• 支持SQL
  缺点：
• 读写性能较差
• 固定的表结构，不够灵活
• 应对高并发场景，磁盘I/O存在瓶颈
• 海量数据的读写性能差
  非关系型数据库
  优点：
• 不需要SQL解析，读写性能高
• 可以使用硬盘或者内存作为载体，速度快
• 基于键值对，数据没有耦合性，方便扩展
• 部署简单
  缺点：
• 不支持SQL，增加了学习成本
• 没有事务

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

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

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

数据库外键的优缺点

优点：

• 能最大限度的保证数据的一致性和完整性

• 增加ER图的可读性
  缺点：

• 影响数据操作的效率

• 增加开发难度，导致表过多

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

使用create view view_name as select * from tb创建视图

使用select * from view_name正常查询视图

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

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

#UUID #唯一标识

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

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

主从复制原理：master写数据留下写入日志，slave根据master留下的日志模仿数据执行过程写入

所以有两个步骤可能导致主从复制不一致：

master日志写入不成功
slave根据日志模仿不成功

解决办法：

innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

保证每次事务提交后，都能实时刷新到磁盘中，尤其是确保每次事务对应的binlog都能及时刷新到磁盘中

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 读写分离有哪些解决办法

配置多数据源
使用中间件代理