mysql 面试题

1. 数据库的三范式是什么？什么是反范式？

第一范式（1NF）：数据表中的每一列(字段)，必须是不可拆分的最小单元，也就是确保每一列的原子性。

第二范式（2NF）：满足1NF后要求表中的所有列，都必需依赖于主键，而不能有任何一列与主键没有关系（例如订单表只能存储订单信息，商品表只能存储商品信息，订单表要是存储了商品信息，则违反了第二范式）。

第三范式（3NF）：满足2NF后，要求：表中的每一列都要与主键直接相关，而不是间接相关（表中的每一列只能直接依赖于主键，其它非主键列不能相互依赖）

范式可以避免数据冗余，减少数据库的空间，减轻维护数据完整性的麻烦。然而，通过数据库范式化设计，将导致数据库业务涉及的表变多，并且可能需要将涉及的业务表进行多表连接查询，这样将导致性能变差，且不利于分库分表。

反范式：指的是通过增加冗余或重复的数据来提高数据库的读性能。

2. MySQL 中 varchar 与 char 的区别？varchar(50) 中的 50 代表的涵义？

varchar 与 char 的区别：

1. char 是一种固定长度的类型，存储的时候，不足部分会用空格填充，取数据的时候会去掉空格；varchar 则是一种可变长度的类型，按实际长度存储。
2. char 因为长度固定，存取速度还是要比varchar要快得多，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以会占据多余的空间
3. char 长度的取值可以0-255之间；varchar的数据类型长度支持到了65535，因为起始位和结束位占去了3个字节，所以其整体最大长度为65532字节

5.0版本以上，varchar(50)，指的是50字符，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放50个。varchar(50) 和 (200) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度(memory引擎也一样)。所以，实际场景下，选择合适的 varchar 长度还是有必要的。

3. int(11) 中的 11 代表什么涵义?

11代表的并不是长度，而是字符的显示宽度，在字段类型为int时，无论你显示宽度设置为多少，int类型能存储的最大值和最小值永远都是固定的。

如：

字段a，类型int(11)，值为1，则存储为: 00000000001（字段设置了zerofill 才能看出这个效果）

字段b，类型int(5)，值为1234567890，仍然可以存储为：1234567890 这10个数字

4. 金额(金钱)相关的数据，选择什么数据类型？

首先不能选择float和double，查询会有精度丢失问题

方式一：使用 int 或者 bigint 类型。如果需要存储到分的维度，需要乘以100 进行放大

方式二：使用 decimal 类型，避免精度丢失。如果使用 Java 语言时，需要使用 BigDecimal 进行对应。

5. 一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15？

• 如果我们使用的存储引擎是 InnoDB ，如果新增一条记录（不重启 MySQL 的情况下），这条记录的 ID 是18 ；但是如果重启 MySQL 的话，这条记录的 ID 是 15 。因为 InnoDB 表只把自增主键的最大 ID 记录到内存中，所以重启数据库或者对表 OPTIMIZE 操作，都会使最大 ID 丢失。
• 但是，如果我们使用的存储引擎是 MyISAM ，那么这条记录的 ID 就是 18 。因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里面，重启 MYSQL 后，自增主键的最大 ID 也不会丢失。

6. InnoDB 和 MyISAM 的区别

• InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

• InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

• InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大；MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的

• InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）

• Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

• InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

• InnoDB表必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键），而Myisam可以没有

• Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

  • Innodb：frm是表定义文件，ibd是数据文件

  • Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

• InnoDB支持崩溃后的恢复，而MyISAM不支持

7. 说说 InnoDB 的关键特性？

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

• 异步IO

• 刷新邻接页

https://www.cnblogs.com/zhs0/p/10528520.html

8. InnoDB为什么推荐使用自增ID作为主键

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

9. 什么是索引，索引的好处和坏处，索引的使用场景

索引，类似于书籍的目录，想找到一本书的某个特定的主题，需要先找到书的目录，定位对应的页码。MySQL 中存储引擎使用类似的方式进行查询，先去索引中查找对应的值，然后根据匹配的索引找到对应的数据行。

索引的好处：

• 提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
• 降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本。

索引的坏处：

• 占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
• 降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。

索引的使用场景：

• 对非常小的表，大部分情况下全表扫描效率更高
• 对中大型表，索引非常有效
• 特大型的表，建立和使用索引的代价随着增长，可以使用分区技术来解决，更常用的是“分库分表”，目前解决方案有 Sharding Sphere、MyCAT 等等。

10. 索引的类型有哪些

• 普通索引：最基本的索引，没有任何约束。

• 唯一索引：与普通索引类似，但具有唯一性约束。

• 主键索引：特殊的唯一索引，不允许有空值。

• 复合索引：将多个列组合在一起创建索引，可以覆盖多个列。

• 外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性和实现级联操作。

• 全文索引：MySQL 自带的全文索引只能用于 InnoDB、MyISAM ，并且只能对英文进行全文检索，一般使用全文索引引擎。

  常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。
  

11. MySQL 索引的“创建”原则

• 最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，而不是出现在 SELECT 关键字后的列。
• 索引列的基数越大（列中的重复数据少），索引效果越好。
• 根据情况创建复合索引，复合索引可以提高查询效率。
• 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
• 对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

12. MySQL 索引的“使用”注意事项

• 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

  注意，column IS NULL 也是不可以使用索引的。
  

• 应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：SELECT id FROM t WHERE num = 10 OR num = 20 。

• 应尽量避免在 WHERE 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。

• 应尽量避免在 WHERE 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

• 不要在 WHERE 子句中的 = 左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

• 复合索引遵循前缀原则。

• 如果 MySQL 评估使用索引比全表扫描更慢，会放弃使用索引。如果此时想要索引，可以在语句中添加强制索引。

• 列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

• LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

13. 以下三条 SQL 如何建索引，只建一条怎么建？

WHERE a = 1 AND b = 1
WHERE b = 1
WHERE b = 1 ORDER BY time DESC

• 以顺序 b , a, time 建立复合索引，CREATE INDEX table1_b_a_time ON index_test01(b, a, time)。
• 对于第一条 SQL ，因为最新 MySQL 版本会优化 WHERE 子句后面的列顺序，以匹配复合索引顺序。

14. mysql 执行计划怎么看，及各个参数的含义

查询语句前添加 explain 关键字就可以查看执行计划。

EXPLAIN SELECT 1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OcpwBsF3-1626996726209)(D:/%25E6%259D%25A8%25E6%2596%25B9%25E8%25B6%2585/java-skill-tree/Java%2520%25E6%258A%2580%25E8%2583%25BD%25E6%25A0%2591.assets/image-20210708172619262.png)]

各参数含义：

• id：是一个有顺序的编号，是查询的顺序号，有几个select 就显示几行。id的顺序是按select 出现的顺序增长的，id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为null最后执行
• selectType 表示查询的类型，主要用于区分普通查询、联合查询、子查询等复杂的查询
  • SIMPLE：表示此查询不包含union查询或子查询
  • PRIMARY：表示此查询是最外层的查询(包含子查询)
  • SUBQUERY：子查询中的第一个select
  • UNION：表示此查询是UNION的第二或随后的查询
  • DEPENDENT UNION ：UNION 中的第二个或后面的查询语句，取决于外面的查询
  • UNION RESULT： UNION 的结果
  • DEPENDENT SUBQUERY：子查询中的第一个select，取决于外面的查询，即子查询依赖于外层的查询结果
  • DERIVED：from子句的子查
• table：表示该语句查询的表名
• partitions：匹配的分区信息
• type：针对单表的访问方法，优化sql的重要字段，也是我们判断sql性能和优化程度的重要指标，它的取值类型范围：
  • const：通过索引一次命中，匹配一行数据
  • system：表中只有一行记录，相当于系统表
  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
  • ref：非唯一性索引扫描，返回匹配某个值的所有
  • range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
  • index：只遍历索引树；
  • ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。基本就是随表的数据增多，执行效率越慢

执行效率：system > const > eq_ref > ref > range > index > ALL

• possible_keys：可能用到的索引信息

• key：真正用到的索引

• key_len：实际使用到的索引长度

• ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息（显示哪个字段或者常量与key一起被使用）

• rows：预估的需要读取的记录条数

• filtered： 某个表经过搜索条件过滤后剩余记录条数的百分比

• Extra：一些额外的信息，比如说Using index condition; Using where; Using filesort

  using index：使用覆盖索引
  using index condition：查询的列未被索引覆盖，where筛选条件使用了索引
  using temporary：用临时表保存中间结果，常用于 group by 和 order by 操作中，通常是因为 group by 的列上没有索引，也有可能是因为同时有group by和order by，但group by和order by的列又不一样，一般看到它说明查询需要优化了
  using filesort：MySQL有两种方式对查询结果进行排序，一种是使用索引，另一种是filesort（基于快排实现的外部排序，性能比较差），当数据量很大时，这将是一个CPU密集型的过程，所以可以通过建立合适的索引来优化排序的性能
  

15. MySQL 索引的原理

索引本质上就是一种通过减少查询需要遍历行数，加快查询性能的数据结构，避免数据库进行全表扫描，好比书的目录，让你更快的找到内容。（一个表最多16个索引）

（1）索引的优点：
减少查询需要检索的行数，加快查询速度，避免进行全表扫描，这也是创建索引的最主要的原因。
如果索引的数据结构是B+树，在使用分组和排序时，可以显著减少查询中分组和排序的时间。
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（2）索引的缺点：
当对表中的数据进行增加、删除和修改时，索引也要进行更新，维护的耗时随着数据量的增加而增加。
索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

索引的使用场景：

（1）在哪些列上面创建索引：
WHERE子句中经常出现的列上面创建索引，加快条件的判断速度。
按范围存取的列或者在group by或order by中使用的列，因为索引已经排序，这样可以利用索引加快排序查询时间。
经常用于连接的列上，这些列主要是一些外键，可以加快连接的速度；
作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

（2）不在哪些列建索引？
区分度不高的列。由于这些列的取值很少，例如性别，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
在查询中很少的列不应该创建索引。由于这些列很少使用到，但增加了索引，反而降低了系统的维护速度和增大了空间需求。
当添加索引造成修改成本的提高 远远大于 检索性能的提高时，不应该创建索引。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。
定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。

索引的分类：

（1）普通索引、唯一索引、主键索引、全文索引、组合索引。

• 普通索引：最基本的索引，没有任何限制
• 唯一索引：但索引列的值必须唯一，允许有空值，可以有多个NULL值。如果是组合索引，则列值的组合必须唯一。
• 主键索引：一种特殊的唯一索引，不允许有空值。
• 全文索引：全文索引仅可用于 MyISAM 表，并只支持从CHAR、VARCHAR或TEXT类型，用于替代效率较低的like 模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。
• 组合索引：主要是为了提高mysql效率，创建组合索引时应该将最常用作限制条件的列放在最左边，依次递减。

（2）聚簇索引与非聚簇索引：

如果按数据存储的物理顺序与索引值的顺序分类，可以将索引分为聚簇索引与非聚簇索引两类：

• 聚簇索引：表中数据存储的物理顺序与索引值的顺序一致，一个基本表最多只能有一个聚簇索引，更新聚簇索引列上的数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引
• 非聚簇索引：表中数据的物理顺序与索引值的顺序不一致的索引组织，一个基本表可以有多个聚簇索引。

索引的数据结构：

常见的索引的数据结构有：B+Tree、Hash索引。

（1）Hash索引：MySQL中只有Memory存储引擎支持hash索引，是Memory表的默认索引类型。hash索引把数据以hash值形式组织起来，因此查询效率非常高，可以一次定位。

hash索引的缺点：

- Hash索引仅能满足等值的查询，不能满足范围查询、排序。因为数据在经过Hash算法后，其大小关系就可能发生变化。
- 当创建组合索引时，不能只使用组合索引的部分列进行查询。因为hash索引是把多个列数据合并后再计算Hash值，所以对单独列数据计算Hash值是没有意义的。
- 当发生Hash碰撞时，Hash索引不能避免表数据的扫描。因为仅仅比较Hash值是不够的，需要比较实际的值以判定是否符合要求。

（2）B+Tree索引：B+Tree是mysql使用最频繁的一个索引数据结构，是Innodb和Myisam存储引擎模式的索引类型。B+Tree索引在查找时需要从根节点到叶节点进行多次IO操作，在查询速度比不上Hash索引，但是更适合排序等操作。

B+Tree索引的优点：

- 页内节点不存储内容，每次IO可以读取更多的行，大大减少磁盘I/O读取次数
- 带顺序访问指针的B+Tree：B+Tree所有索引数据都存储在叶子结点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针，这样做是为了提高区间查询效率。

为什么使用B+Tree作为索引：

索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，磁盘I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的数据结构要尽量减少查找过程中磁盘I/O的存取次数。

（1）局部性原理与程序预读：

由于磁盘本身存取就比主存慢很多，再加上机械运动耗费，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。程序运行期间所需要的数据通常比较集中。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页的整倍数。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

（2）B+Tree索引的性能分析：

上文说过一般使用磁盘I/O次数评价索引结构的优劣。我们先从B树分析，B树检索一次最多需要访问h个节点，同时，数据库巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，即每次新建节点时，直接申请一个页的空间，这样就保证一个节点在物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，这样就实现了每个节点只需要一次I/O就可以完全载入。B树中一次检索最多需要h-1次I/O（根节点常驻内存），时间复杂度为O(h)=O(logdN)。一般实际应用中，出度d是非常大的数字，通常超过100，因此h非常小。综上所述，用B树作为索引结构效率是非常高的。

而红黑树这种结构，虽然时间复杂度也为O(h)，但是h明显要深的多，并且由于逻辑上很近的节点，在物理上可能很远，无法利用局部性，所以IO效率明显比B树差很多。

另外，B+Tree更适合作为索引的数据结构，原因和内节点出度d有关。从上面分析可以看到，d越大索引的性能越好，而出度d的上限取决于节点内key和data的大小，由于B+Tree内节点去掉了data域，因此可以拥有更大的出度，磁盘IO的次数也就更少了。

（3）B+树索引 和 B树索引 的对比？

根据B-Tree 和 B+Tree的结构，我们可以发现B+树相比于B树，在文件系统或者数据库系统当中，更有优势，原因如下：

• B+树有利于对数据库的扫描：B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题，而B+树只需要遍历叶子节点就可以解决对全部关键字信息的扫描，所以范围查询、排序等操作，B+树有着更高的性能。
• B+树的磁盘IO代价更低：B+树的内部结点的data域并没有存储数据，因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多，相对来说I/O读写次数也就降低了。
• B+树的查询效率更加稳定：由于B+树的内部结点只是叶子结点中关键字的索引，并不存储数据。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

（4）MySQL的 InnoDB 和 MyISAM 存储引擎中B+Tree索引的实现？

MyISAM和InnoDB都是使用B+树索引，MyISAM的主键索引和辅助索引的Data域都是保存行的地址，但是InnoDB的主键索引保存的不是行的地址，而是保存该行的所有所有数据，而辅助索引的Data域保存的则是主索引的值。

索引的长度限制：

对于 Innodb 的组合索引，如果各个列中的长度超过767字节的，则会对超过767字节的列取前缀索引；对于 Innodb 的单列索引，如果列的长度超过767的，则取前缀索引（取前255字符）
对于 MyISAM 的组合索引，所创建的索引长度和不能超过1000 bytes，否则会报错，创建失败；对于 MyISAM 的单列索引，最大长度也不能超过1000，否则会报警，但是创建成功，最终创建的是前缀索引（取前333个字符）

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

https://blog.csdn.net/u013235478/article/details/50625677

https://blog.csdn.net/tongdanping/article/details/79878302

16. 聚簇索引的注意点有哪些

聚簇索引表最大限度地提高了 I/O 密集型应用的性能，但它也有以下几个限制：

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。

关于这一点，可能面试官会换一个问法。例如，为什么主键需要是自增 ID ，又或者为什么主键需要带有时间性关联。

2、更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB 表，我们一般定义主键为不可更新。

MySQL 默认情况下，主键是允许更新的。对于 MongoDB ，其 主键是不允许更新的。

3、二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

当然，有一种情况可以无需二次查找，基于非主键索引查询，但是查询字段只有主键 ID ，那么在二级索引中就可以查找到。

4、主键 ID 建议使用整型。因为，每个主键索引的 B+Tree 节点的键值可以存储更多主键 ID ，每个非主键索引的 B+Tree 节点的数据可以存储更多主键 ID 。

17. 数据库的四种事务隔离级别

读未提交（read-uncommitted）：能读到其它事务未提交的数据，不能防止脏读、不可重复读、幻读问题

读已提交（read-committed）：能读到其它事务已提交的数据，能防止脏读，不能防止不可重复读、幻读。（Oracle默认的隔离级别）

可重复读（repeatable-read）：能重复读取同一个数据，读的时候加一把锁，防止其它事务对这一数据进行更新操作，能防止脏读、不可重复读，不能防止幻读。（MySQL默认的隔离级别）

串行化（serializable）：最高的隔离级别，并发事务串行化执行，不会相互干扰，能防止脏读、不可重复读、幻读问题。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NA4QS4XE-1626996726216)(D:/%25E6%259D%25A8%25E6%2596%25B9%25E8%25B6%2585/java-skill-tree/Java%2520%25E6%258A%2580%25E8%2583%25BD%25E6%25A0%2591.assets/image-20210709095046755.png)]

18. 数据库事务的四大特性（ACID）及实现原理

原子性（Atomicity）：事务是一个不可分割的逻辑工作单元，事务中的一系列操作要么全部成功，要么全部失败。

一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态。我们假设用户A和用户B两个人的账户加起来有5000块钱，那么不管A和B之间如何转账，事务结束后两个人的账户加起来还得是5000块钱，这就是一致性状态。

隔离性（Isolation）：多个用户并发访问数据库时，数据库为每一个用户开启一个事务，多个并发事务之间相互隔离，不会相互干扰。

持久性（Durability）：一个事务一旦被提交了，那么对数据库中的数据的改变是永久性的，即使数据库系统出现故障也不会丢失提交事务的操作。

实现原理：

原子性：原子性是通过MySQL的回滚日志undo log来实现的：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

Innodb事务的隔离级别是由MVVC和锁机制实现的：

① MVCC（Multi-Version Concurrency Control，多版本并发控制）是 MySQL 的 InnoDB 存储引擎实现事务隔离级别的一种具体方式，用于实现读已提交和可重复读这两种隔离级别。而读未提交隔离级别总是读取最新的数据行，无需使用 MVCC。读序列化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC是通过在每行记录后面保存两个隐藏的列来实现的，一个保存了行的事务ID，一个保存了行的回滚段指针。每开始一个新的事务，都会自动递增产生一个新的事务ID。事务开始时会把该事务ID放到当前事务影响的行事务ID字段中，而回滚段的指针有该行记录上的所有版本数据，在undo log回滚日志中通过链表形式组织，也就是说该值实际指向undo log中该行的历史记录链表。

在并发访问数据库时，对正在事务中的数据做MVCC多版本的管理，以避免写操作阻塞读操作，并且可以通过比较版本解决幻读。

② 锁机制：

MySQL锁机制的基本工作原理就是：事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

排它锁解决脏读
共享锁解决不可重复读
临键锁解决幻读

持久性的依靠redo log日志实现，在执行SQL时会保存已执行的SQL语句到一个redo log文件，但是为了提高效率，将数据写入到redo log之前，会先写入到内存中的redo log buffer缓存区中。写入过程如下：当向数据库写入数据时，执行过程会首先写入redo log buffer，redo log buffer中修改的数据会定期刷新到磁盘的redo log文件中，这一过程称为刷盘（即redo log buffer写日志到磁盘的redo log file中 ）。

redo log buffer的使用可以大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时redo log buffer中修改的数据在内存还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。为了确保事务的持久性，在当事务提交时，会调用fsync接口对redo log进行刷盘 ，刷新频率由 innodb_flush_log_at_trx_commit变量来控制的：

0：表示不刷入磁盘；
1：事务每次提交的时候，就把缓冲池中的数据刷新到磁盘中；
2：提交事务的时候，把缓冲池中的数据写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件。可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

一致性指的是事务不能破坏数据的完整性和业务的一致性 ：其实是通过其他三个特性来保证的（原子性、隔离性、持久性）

数据的完整性： 实体完整性、列完整性（如字段的类型、大小、长度要符合要求）、外键约束等
业务的一致性：例如在银行转账时，不管事务成功还是失败，双方钱的总额不变。

19. 数据库事务的并发问题

实际场景下，事务并不是串行的，所以会带来如下三个问题：

• 更新丢失：两个或多个事务操作相同的数据，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题：最后的更新覆盖了其他事务所做的更新。
• 脏读：指事务A正在访问数据，并且对数据进行了修改（事务未提交），这时，事务B也使用这个数据，后来事务A撤销回滚，并把修改后的数据恢复原值，B读到的数据就与数据库中的数据不一致，即B读到的数据是脏数据。
• 不可重复读：在一个事务内，多次读取同一个数据，但是由于另一个事务在此期间对这个数据做了修改并提交，导致前后读取到的数据不一致；
• 幻读：在一个事务中，先后两次进行读取相同的数据（一般是范围查询），但由于另一个事务新增或者删除了数据，导致前后两次结果不一致。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

20. mvcc的实现原理

详细原理：https://blog.csdn.net/huaishu/article/details/89924250

MVVC 是一种基于多版本的并发控制协议，只有在InnoDB引擎下存在，只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁，MVCC最大的好处：读不加锁，读写不冲突

InnoDB在每行数据都增加三个隐藏字段，一个唯一行号，一个记录创建的版本号，一个记录回滚的版本号

事务快照是用来存储数据库的事务运行情况。一个事务快照的创建过程可以概括为：

1. 查看当前所有的未提交并活跃的事务，存储在数组中
2. 选取未提交并活跃的事务中最小的XID，记录在快照的xmin中
3. 选取所有已提交事务中最大的XID，加1后记录在xmax中

Read View （主要是用来做可见性判断的）：创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。

对于Read View快照的生成时机，也非常关键，正是因为生成时机的不同，造成了RC，RR两种隔离级别的不同可见性；

在innodb中(默认repeatable read级别)，事务在begin/start transaction之后的第一条select读操作后，会创建一个快照(Read View)，将当前系统中活跃的其他事务记录记录起来
在innodb中(read committed级别)，事务中每条select语句都会创建一个快照(Read View)

21. SQL语句的执行过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6ZVBFXpS-1626996726221)(D:/%25E6%259D%25A8%25E6%2596%25B9%25E8%25B6%2585/java-skill-tree/Java%2520%25E6%258A%2580%25E8%2583%25BD%25E6%25A0%2591.assets/5b2c927e341c730c33a3ed41eb353691.png)]

• 客户端与数据库进行通信前，通过数据库驱动与MySQL建立连接，建立完成之后，就发送SQL语句
• 为了减少频繁创建和销毁连接造成系统性能的下降，通过数据库连接池维护一定数量的连接线程，当需要进行连接时，就直接从连接池中获取，使用完毕之后，再归还给连接池。常见的数据库连接池有 Druid、C3P0、DBCP
• 优先在缓存中进行查询，如果查到了则直接返回，如果缓存中查询不到，在去数据库中查询
• 通过解析器对要执行的SQL语句进行词法解析、语法解析，最终得到抽象语法树，然后再使用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影列字段是否在表中存在等。
• 通过优化器将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算 ，最终得出一个执行计划，包括选择使用哪个索引
• 执行器根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。

Innodb存储引擎的执行过程

（1）首先MySQL执行器根据 执行计划 调用存储引擎的API查询数据
（2）存储引擎先从缓存池buffer pool中查询数据，如果没有就会去磁盘中查询，如果查询到了就将其放到缓存池中
（3）在数据加载到 Buffer Pool 的同时，会将这条数据的原始记录保存到 undo 日志文件中
（4）innodb 会在 Buffer Pool 中执行更新操作
（5）更新后的数据会记录在 redo log buffer 中
（6）提交事务在提交的同时会做以下三件事
（7）（第一件事）将redo log buffer中的数据刷入到redo log文件中
（8）（第二件事）将本次操作记录写入到 bin log文件中
（9）（第三件事）将bin log文件名字和更新内容在 bin log 中的位置记录到redo log中，同时在 redo log 最后添加 commit 标记
（10）使用一个后台线程，它会在某个时机将我们Buffer Pool中的更新后的数据刷到 MySQL 数据库中，这样就将内存和数据库的数据保持统一了

推荐博客：https://blog.csdn.net/a745233700/article/details/113927318

22. 数据库中的锁机制

当数据库中多个事务并发存取同一数据的时候，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。MySQL锁机制的基本工作原理就是，事务在修改数据库之前，需要先获得相应的锁，获得锁的事务才可以修改数据；在该事务操作期间，这部分的数据是锁定，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

按照不同的分类方式，锁的种类可以分为以下几种：

• 按锁的粒度划分：表级锁、行级锁、页级锁；
  • 表级锁：最大粒度的锁级别，发生锁冲突的概率最高，并发度最低，但开销小，加锁快，不会出现死锁；
  • 行级锁：最小粒度的所级别，发生锁冲突的概率最小，并发度最高，但开销大，加锁慢，会发生死锁；
  • 页级锁：锁粒度界于表级锁和行级锁之间，对表级锁和行级锁的折中，并发度一般。开销和加锁时间也界于表锁和行锁之间，会出现死锁；
• 按锁的类型划分：共享（锁S锁）、排他锁（X锁）；
• 按锁的使用策略划分：乐观锁、悲观锁；

不同的存储引擎支持不同的锁机制：

• InnoDB存储引擎支持行级锁和表级锁，默认情况下使用行级锁，但只有通过索引进行查询数据，才使用行级锁，否就使用表级锁。
• MyISAM和MEMORY存储引擎采用的是表级锁；
• BDB存储引擎使用的是页面锁，但也支持表级锁；

InnoDB的行锁有两种类型：

• 共享锁（S锁、读锁）：多个事务可以对同一数据行共享一把S锁，但只能进行读不能修改；
• 排它锁（X锁、写锁）：一个事务获取排它锁之后，可以对锁定范围内的数据行执行写操作，在锁定期间，其他事务不能再获取这部分数据行的锁（共享锁、排它锁），只允许获取到排它锁的事务进行更新数据。

对于update，delete，insert 操作，InnoDB会自动给涉及的数据行加排他锁；对于普通SELECT语句，InnoDB不会加任何锁

InnoDB的表锁与意向锁：

因为InnoDB引擎允许行锁和表锁共存，实现多粒度的锁机制，但是表锁和行锁虽然锁定范围不同，但是会相互冲突。当你要加表锁时，势必要先遍历该表的所有记录，判断是否有排他锁。这种遍历检查的方式显然是一种低效的方式，MySQL引入了意向锁，来检测表锁和行锁的冲突。

意向锁也是表级锁，分为读意向锁（IS锁）和写意向锁（IX锁）。当事务要在记录上加上行锁时，则先在表上加上对应的意向锁。之后事务如果想进行锁表，只要先判断是否有意向锁存在，存在时则可快速返回该表不能启用表锁，否则就需要等待，提高效率。

InnoDB行锁的实现与临键锁：

InnoDB的行锁是通过给索引上的索引项加锁来实现的。只有通过索引检索数据，才能使用行锁，否则将使用表锁。

在InnoDB中，为了解决幻读的现象，引入了临键锁(next-key)。根据索引，划分为一个个左开右闭的区间。当进行范围查询的时候，若命中索引且能够检索到数据，则锁住记录所在的区间和它的下一个区间。其实，临键锁(Next-Key) = 记录锁(Record Locks) + 间隙锁(Gap Locks)

间隙锁：当使用范围查询而不是精准查询进行检索数据，并请求共享或排它锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做间隙(GAP)。
记录锁：当使用唯一索引，且记录存在的精准查询时，使用记录锁

利用锁机制解决并发问题：

• X锁解决脏读
• S锁解决不可重复读
• 临键锁解决幻读（不能完全解决，如果锁住了辅助索引，用主键索引去删除还是能删掉）

23. SQL优化

SQL语句优化：

• 避免 select * 查询所有字段，只返回必要的列，能分页的尽量分页查询

  如果查询的字段都在索引中，也就是覆盖索引，那么可以直接从索引中获取对应的内容直接返回，不需要进行回表，减少IO操作。除此之外，当存在 order by 操作的时候，select 子句中的字段多少会在很大程度上影响到我们的排序效率。
  

• 避免使用select的内联子查询，在select后面有子查询的情况称为内联子查询，SQL返回多少行，子查询就需要执行过多少次，严重影响SQL性能。

• 尽量使用Join代替子查询：MySQL的优化器对于子查询的处理能力比较弱，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表

• 多张大数据量的表进行JOIN连接查询，最好先过滤在JOIN

• 当子查询的结果集比较大，外表较小使用exist效率更高；当子查询的结果集较小，外表较大时，使用in效率更高。

• 避免在使用or来连接查询条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描。

• union、in、or 都能够命中索引，但推荐使用 in

• 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描；column IS NULL 也是不可以使用索引的

• 应尽量避免在 WHERE 子句中对字段进行表达式操作和函数操作

• 复合索引遵循前缀原则

• 列类型是字符串类型，查询时一定要给值加引号，否则索引失效。

• LIKE 查询，% 不能在前，因为无法使用索引。如果需要模糊匹配，可以使用全文索引。

创建索引进行优化，注意点：

• 最适合索引的列是出现在 WHERE 子句中的列，或连接子句中的列，或排序字段的列，而不是出现在 SELECT 关键字后的列。
• 索引列的基数越大（列中的重复数据少），索引效果越好。
• 根据情况创建复合索引，复合索引可以提高查询效率。
• 避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。
• 主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。
• 对字符串进行索引，应该定制一个前缀长度，可以节省大量的索引空间。

24. MySQL的主从复制

主从复制的原理

Slave从Master获取binlog二进制日志文件，然后再将日志文件解析成相应的SQL语句在从服务器上重新执行一遍主服务器的操作，通过这种方式来保证数据的一致性。由于主从复制的过程是异步复制的，因此Slave和Master之间的数据有可能存在延迟的现象，只能保证数据最终的一致性。在master和slave之间实现整个复制过程主要由三个线程来完成：

• Slave SQL thread线程：创建用于读取relay log中继日志并执行日志中包含的更新，位于slave端
• Slave I/O thread线程：读取 master 服务器Binlog Dump线程发送的内容并保存到slave服务器的relay log中继日志中，位于slave端：
• Binlog dump thread线程(也称为IO线程)：将bin-log二进制日志中的内容发送到slave服务器，位于master端

**注意：**如果一台主服务器配两台从服务器那主服务器上就会有两个Binlog dump 线程，而每个从服务器上各自有两个线程；

主从复制流程：

（1）master服务器在执行SQL语句之后，记录在binlog二进制文件中；
（2）slave端的IO线程连接上master端，并请求从指定bin log日志文件的指定pos节点位置（或者从最开始的日志）开始复制之后的日志内容。
（3）master端在接收到来自slave端的IO线程请求后，通知负责复制进程的IO线程，根据slave端IO线程的请求信息，读取指定binlog日志指定pos节点位置之后的日志信息，然后返回给slave端的IO线程。该返回信息中除了binlog日志所包含的信息之外，还包括本次返回的信息在master端的binlog文件名以及在该binlog日志中的pos节点位置。
（4）slave端的IO线程在接收到master端IO返回的信息后，将接收到的binlog日志内容依次写入到slave端的relay log文件的最末端，并将读取到的master端的binlog文件名和pos节点位置记录到master-info文件中（该文件存slave端），以便在下一次同步的候能够告诉master从哪个位置开始进行数据同步；
（5）slave端的SQL线程在检测到relay log文件中新增内容后，就马上解析该relay log文件中的内容，然后还原成在master端真实执行的那些SQL语句，再按顺序依次执行这些SQL语句，从而到达master端和slave端的数据一致性；

主从复制的好处：

（1）读写分离，通过动态增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上执行读功能。
（2）提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。
（3）在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。

MySQL支持的复制类型及其优缺点：

binlog日志文件有两种格式，一种是Statement-Based（基于语句的复制），另一种是Row-Based（基于行的复制）。默认格式为Statement-Based，如果想改变其格式在开启服务的时候使用 -binlog-format 选项，其具体命令如下：

mysqld_safe –user=msyql –binlog-format=格式 &

（1）基于语句的复制（Statement-Based）：在主服务器上执行的SQL语句，在从服务器上执行同样的语句。效率比较高。 一旦发现没法精确复制时，会自动选着基于行的复制。

优点：

① 因为记录的SQL语句，所以占用更少的存储空间。binlog日志包含了描述数据库操作的事件，但这些事件包含的情况只是对数据库进行改变的操作，例如 insert、update、create、delete等操作。相反对于select、desc等类似的操作并不会去记录。
② binlog日志文件记录了所有的改变数据库的语句，所以此文件可以作为数据库的审核依据。
缺点：

① 不安全，不是所有的改变数据的语句都会被记录。对于非确定性的行为不会被记录。例如：对于 delete 或者 update 语句，如果使用了 limit 但是并没有 order by ，这就属于非确定性的语句，就不会被记录。
② 对于没有索引条件的update，insert……select 语句，必须锁定更多的数据，降低了数据库的性能。

（2）基于行的复制（Row-Based）：把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从mysql5.0开始支持；

优点：

① 所有的改变都会被复制，这是最安全的复制方式；
② 对于 update、insert……select等语句锁定更少的行；
缺点：

① 不能通过binlog日志文件查看什么语句执行了，也无从知道在从服务器上接收到什么语句，我们只能看到什么数据改变。
② 因为记录的是数据，所以说binlog日志文件占用的存储空间要比Statement-based大。
③ 对于数据量大的操作其花费的时间有更长。

（3）混合类型的复制：默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

25. MySQL的读写分离

读写分离的实现原理：

读写分离解决的是，数据库的写操作，影响了查询的效率，适用于读远大于写的场景。读写分离的实现基础是主从复制，主数据库利用主从复制将自身数据的改变同步到从数据库集群中，然后主数据库负责处理写操作（当然也可以执行读操作），从数据库负责处理读操作，不能执行写操作。并可以根据压力情况，部署多个从数据库提高读操作的速度，减少主数据库的压力，提高系统总体的性能。

读写分离提高性能的原因：

• 增加物理服务器，负荷分摊；
• 主从只负责各自的写和读，极大程度的缓解X锁和S锁争用；
• 从库可配置MyISAM引擎，提升查询性能以及节约系统开销；
• 主从复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务。

读写分离的实现方式：

（1）基于程序代码内部实现：在代码中根据select 、insert进行路由分类。优点是性能较好，因为程序在代码中实现，不需要增加额外的硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

使用AOP来做出判断，是使用写库还是读库，判断依据可以根据方法名判断，比如说以query、find、get等开头的就走读库，其他的走写库。

（2）基于中间代理层实现：代理一般介于应用服务器和数据库服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到后端数据库，有以下代表性的代理层。

通过mycat中间件进行读写分离

26. 分库分表：垂直分表、垂直分库、水平分表、水平分库

读写分离解决的是数据库读写操作的压力，但是没有分散数据库的存储压力，利用分库分表可以解决数据库的储存瓶颈，并提升数据库的查询效率。

垂直拆分：

（1）垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

优点：
（1）避免IO竞争减少锁表的概率。因为大的字段效率更低，第一，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多；第二数据量大，需要的读取时间长。
（2）可以更好地提升热门数据的查询效率。

（2）垂直分库：按照业务模块的不同，将表拆分到不同的数据库中，适合业务之间的耦合度非常低、业务逻辑清晰的系统。

优点：
（1）降低业务中的耦合，方便对不同的业务进行分级管理
（2）可以提升IO、数据库连接数、解决单机硬件存储资源的瓶颈问题

垂直拆分（分库、分表）的缺点：

• 主键出现冗余，需要管理冗余列
• 事务的处理变得复杂
• 仍然存在单表数据量过大的问题

水平拆分：

（1）水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

优点：
（1）解决了单表数据量过大的问题
（2）避免IO竞争并减少锁表的概率

（2）水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

优点：
（1）解决了单库大数据量的瓶颈问题
（2）IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库，提高了系统的稳定性和可用性

水平拆分（分表、分库）的缺点：

• 分片事务一致性难以解决
• 跨节点JOIN性能差，逻辑会变得复杂
• 数据扩展难度大，不易维护

分库分表存在的问题的解决：

（1）事务的问题：

① 方案一：使用分布式事务：

优点：由数据库管理，简单有效。
缺点：性能代价高，特别是shard越来越多。
② 方案二：程序与数据库共同控制实现，原理就是将一个跨多个数据库的分布式事务分解成多个仅存在于单一数据库上面的小事务，并交由应用程序来总体控制各个小事务。

优点：性能上有优势；
缺点：需要在应用程序在事务上做灵活控制。如果使用了spring的事务管理，改动起来会面临一定的困难。

（2）跨节点 Join 的问题：

解决该问题的普遍做法是分两次查询实现：在第一次查询的结果集中找出关联数据的id，根据这些id发起第二次请求得到关联数据。

（3）跨节点count，order by，group by，分页和聚合函数问题：

由于这类问题都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作，解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和 join 不同的是每个结点的查询可以并行执行，因此速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

分库分表后，ID键如何处理？

分库分表后不能每个表的ID都是从1开始，所以需要一个全局ID，设置全局ID主要有以下几种方法：

（1）UUID：

优点：本地生成ID，不需要远程调用，全局唯一不重复。
缺点：占用空间大，不适合作为索引。
（2）数据库自增ID：在分库分表表后使用数据库自增ID，需要一个专门用于生成主键的库，每次服务接收到请求，先向这个库中插入一条没有意义的数据，获取一个数据库自增的ID，利用这个ID去分库分表中写数据。

优点：简单易实现。
缺点：在高并发下存在瓶颈。
（3）Redis生成ID：

优点：不依赖数据库，性能比较好。
缺点：引入新的组件会使得系统复杂度增加
（4）Twitter的snowflake算法：是一个64位的long型的ID，其中有1bit是不用的，41bit作为毫秒数，10bit作为工作机器ID，12bit作为序列号。

1bit：第一个bit默认为0，因为二进制中第一个bit为1的话为负数，但是ID不能为负数.
41bit：表示的是时间戳，单位是毫秒。
10bit：记录工作机器ID，其中5个bit表示机房ID，5个bit表示机器ID。
12bit：用来记录同一毫秒内产生的不同ID。
（5）美团的Leaf分布式ID生成系统，美团点评分布式ID生成系统：

27. MySQL分区

分区就是将表的数据按照特定规则存放在不同的区域，也就是将表的数据文件分割成多个小块，在查询数据的时候，只要知道数据数据存储在哪些区域，然后直接在对应的区域进行查询，不需要对表数据进行全部的查询，提高查询的性能。同时，如果表数据特别大，一个磁盘磁盘放不下时，我们也可以将数据分配到不同的磁盘去，解决存储瓶颈的问题，利用多个磁盘，也能够提高磁盘的IO效率，提高数据库的性能。在使用分区表时，需要注意分区字段必须放在主键或者唯一索引中、每个表最大分区数为1024；常见的分区类型有：Range分区、List分区、Hash分区、Key分区

（1）Range分区：按照连续的区间范围进行分区
（2）List分区：按照给定的集合中的值进行选择分区。
（3）Hash分区：基于用户定义的表达式的返回值进行分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
（4）Key分区：类似于按照HASH分区，区别在于Key分区只支持计算一列或多列，且key分区的哈希函数是由 MySQL 服务器提供。

表分区的优点：

① 可伸缩性：

• 将分区分在不同磁盘，可以解决单磁盘容量瓶颈问题，存储更多的数据，也能解决单磁盘的IO瓶颈问题。

② 提升数据库的性能：

• 减少数据库检索时需要遍历的数据量，在查询时只需要在数据对应的分区进行查询。
• 避免Innodb的单个索引的互斥访问限制
• 对于聚合函数，例如sum()和count()，可以在每个分区进行并行处理，最终只需要统计所有分区得到的结果

③ 方便对数据进行运维管理：

• 方便管理，对于失去保存意义的数据，通过删除对应的分区，达到快速删除的作用。比如删除某一时间的历史数据，直接执行truncate，或者直接drop整个分区，这比detele删除效率更高；
• 在某些场景下，单个分区表的备份很恢复会更有效率。

28. 主键一般用自增ID还是UUID？

（1）自增ID：

使用自增ID的好处：

字段长度较 UUID 会小很多。
数据库自动编号，按顺序存放，利于检索
无需担心主键重复问题

使用自增ID的缺点：

因为是自增，在某些业务场景下，容易被其他人查到业务量。
发生数据迁移时，或者表合并时会非常麻烦
在高并发的场景下，竞争自增锁会降低数据库的吞吐能力

（2）UUID：通用唯一标识码，UUID是基于当前时间、计数器和硬件标识等数据计算生成的。

使用UUID的优点：

唯一标识，不用考虑重复问题，在数据拆分、合并时也能达到全局的唯一性。
可以在应用层生成，提高数据库的吞吐能力。
无需担心业务量泄露的问题。

使用UUID的缺点：

因为UUID是随机生成的，所以会发生随机IO，影响插入速度，并且会造成硬盘的使用率较低。
UUID占用空间较大，建立的索引越多，造成的影响越大。
UUID之间比较大小较自增ID慢不少，影响查询速度。

一般情况下，MySQL推荐使用自增ID，因为在MySQL的 InnoDB 存储引擎中，主键索引是聚簇索引，主键索引的B+树的叶子节点按照顺序存储了主键值及数据，如果主键索引是自增ID，只需要按顺序往后排列即可，如果是UUID，ID是随机生成的，在数据插入时会造成大量的数据移动，产生大量的内存碎片，造成插入性能的下降。

29. 视图View

视图是从一个或者多个表（或视图）导出的表，其内容由查询定义。视图是一个虚拟表，数据库中只存储视图的定义，不存储视图对应的数据，在对视图的数据进行操作时，系统根据视图的定义去操作相应的基本表。可以说，视图是在基本表之上建立的表，它的结构和内容都来自基本表，依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

（1）视图的优点：

简化了操作，把经常使用的数据定义为视图
安全性，用户只能查询和修改能看到的数据
逻辑上的独立性，屏蔽了真实表的结构带来的影响

（2）视图的缺点：

性能差，数据库必须把对视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

30. 存储过程Procedure

SQL语句需要先编译然后执行，而存储过程就是一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中，用户通过制定存储过程的名字并给定参数来调用它。

用程序也可以实现操作数据库的复杂逻辑，那为什么需要存储过程呢？主要是因为使用程序调用API执行，其效率相对较慢，应用程序需通过引擎把SQL语句交给MYSQL引擎来执行，那还不如直接让MySQL负责它最精通最能够完成的工作。

存储过程的优点：

（1）标准组件式编程：存储过程创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。并且DBA可以随时对存储过程进行修改，对应用程序源代码毫无影响。
（2）更快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
（3）增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判杂的断和较复运算。
（4）减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。
（5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

31. 触发器Trigger

触发器是与表有关的数据库对象，当触发器所在表上出现指定事件并满足定义条件的时候，将执行触发器中定义的语句集合。触发器的特性可以应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程，不同的是存储过程要用call来调用，而触发器不需要使用call，也不需要手工调用，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

32. 游标Cursor

游标，就是游动的标识，可以充当指针的作用，使用游标可以遍历查询数据库返回的结果集中的所有记录，但是每次只能提取一条记录，即每次只能指向并取出一行的数据，以便进行相应的操作。当你没有使用游标的时候，相当于别人一下给你所有的东西让你拿走；用了游标之后，相当于别人一件一件的给你，这时你可以先看看这个东西好不好，再自己进行选择。

优秀博客推荐：

https://blog.csdn.net/a303549861/article/details/100302267?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control

面试题：https://blog.csdn.net/a745233700/article/details/114242960

语句，从而大大减少网络流量并降低了网络负载。
（5）作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

31. 触发器Trigger

触发器是与表有关的数据库对象，当触发器所在表上出现指定事件并满足定义条件的时候，将执行触发器中定义的语句集合。触发器的特性可以应用在数据库端确保数据的完整性。触发器是一个特殊的存储过程，不同的是存储过程要用call来调用，而触发器不需要使用call，也不需要手工调用，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

32. 游标Cursor

游标，就是游动的标识，可以充当指针的作用，使用游标可以遍历查询数据库返回的结果集中的所有记录，但是每次只能提取一条记录，即每次只能指向并取出一行的数据，以便进行相应的操作。当你没有使用游标的时候，相当于别人一下给你所有的东西让你拿走；用了游标之后，相当于别人一件一件的给你，这时你可以先看看这个东西好不好，再自己进行选择。

优秀博客推荐：

https://blog.csdn.net/a303549861/article/details/100302267?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-2.control

面试题：https://blog.csdn.net/a745233700/article/details/114242960