第四章 数据库
1、基本知识
1.1 MySQL数据库中的 char 与 varchar的区别是什么?
MySQL中的 char 和 varchar 都是用于存储字符串的数据类型，但它们在存储方式和性能上有所不同。以下是它们的主要区别：
第一点呢，就是存储方式不同：
char：定长字符串，长度是固定的，不管实际存储的字符串长度如何，都会占用固定长度的存储空间。如：char(10) 会始终占用10个字符的空间。
varchar：变长字符串，长度不固定。占用的空间与实际存储的字段长度有关。 如：varchar(10) 表示最多可以存储10个字符，如果存储的字符串长度不足10，假设为5，只会占用5个字符空间。
第二点呢，就是性能不同：
对于char，由于其固定长度，操作会快些，但是会存储浪费磁盘空间的问题。
对于varchar，由于长度可变，操作时会相对慢一点，但是可以节省磁盘空间，尤其是存储的数据长度不固定时。
所以呢，我们在设计表结构的时候，需要根据具体的场景来选择具体的数据类型。 就比如啊，如果是手机号、身份证号这样的字段，由于长度固定，我们就直接选择char类型即可，并指定长度，如：char(11)、char(18)。再比如，像用户名、备注信息这类长度不固定的，我们直接选择varchar类型，长度根据页面原型和需求文档确定。
1.2 什么是事务以及事务的四大特性？
必答内容：
事务是数据库中的基本概念，是指一组操作的集合，而这一组操作要么同时成功，要么同时失败，从而保证数据库中数据的正确性和完整性。
那事务呢，具有四大特性，也就是我们常说的ACID，分别是：原子性、一致性、隔离性、持久性。 那接下来，我就分别来聊聊这四大特性。
1). 原子性指的是事务中的这一组操作，是不可分割的最小操作单元了，操作要么全部成功，要么全部失败。
2). 一致性是指在事务操作的前后，必须使数据处于一致的状态。
3). 隔离性指的是数据库中提供了隔离机制，保证事务在不受外部并发操作的影响的独立环境中运行。
4). 持久性就比较简单了，就是事务一旦提交或回滚了，它对数据库的改变就是永久的。
可能继续发问的问题：
1). 你刚才提到了并发事务，那并发事务回引发哪些问题?
并发事务引发的问题，主要有这么几个：
脏读：就是一个事务，读取到了另一个事务还没有提交的数据。
不可重复读：指的是在同一个事务中，先后读取同一条记录，但两次读取的数据不同。
幻读：指的是一个事务按照条件查询数据时，没有对应的行，但是插入时，又发现这行数据已经存在了好像出现了幻觉。
2). 如何解决这些问题呢?
那这些问题，在数据库系统中都已经解决了。在数据库中提供了不同的隔离级别来解决这些问题， 分别有以下几种：
READ UNCOMMITED ：读未提交。 这种隔离级别下，会出现脏读、不可重复读、幻读问题。
READ COMMITED：读已提交。 这种隔离级别，解决了脏读问题，但是会出现不可重复读、幻读问题。
REPEATABLE READ：可重复读。这种隔离级别，解决了脏读、不可重复读问题，但是会出现幻读问题。
SERIALIZABLE：串行化。解决了上述所有的并发事务问题。
而在MySQL数据库中，默认的隔离级别是 REPEATABLE READ（可重复读）。
3). 那为什么没有用SERIALIZABLE(串行化) 这种隔离级别呢?
其实，隔离级别，也不是越高越好。因为隔离级别高了，确实可以解决并发事务引发的问题，但是隔离级别越高，性能也越低。
2、索引基础
2.1 MySQL数据库索引的数据结构？
必答内容：
在MySQL中的索引类型有多种哈，比如像B+tree索引、Hash索引等，但在InnoDB存储引擎中默认使用的是B+tree的索引。
可能继续追问的问题：
1). B+tree索引结构的特点是什么?
B+tree呢，也叫多路平衡搜索树，也就是一个节点中可以存储多个key，多个key，也就对应多个指针，大数据量的情况下，树的高度更低。（树的阶数更多，高度更低，检索更快）。
在B+tree中，所有的数据都是存放在叶子节点的，非叶子节点，仅仅起到索引数据的作用。
而在B+tree的叶子节点中，形成了一个双向链表，便于区间范围查询。
2). 那为什么MySQL数据库的索引结构要采用B+tree?
参考 2.4
2.2 知道什么是聚簇索引，什么是二级索引吗？
必答内容：
这个还是比较清楚的，因为这个是我们在项目中进行SQL语句优化的理论基础。
聚簇索引，有时候也称为聚集索引，他的特点呢就是数据与索引存放在一块儿，B+tree的叶子节点保存了整行数据，而且在一张表中，聚簇索引有且仅有一个，默认主键索引就是聚簇索引。
二级索引，有时候也称为非聚簇索引 或 辅助索引，指的是数据和索引分开存储，B+tree的叶子节点保存对应的主键，二级索引在一张表中可以有多个。
进阶内容（细节）：
那刚才提到，默认主键索引就是聚簇索引；虽然在项目开发中我们建议每一张表都必须要添加一个主键，但是从数据库本身来说，一张表是可以没有主键的，那如果没有主键，MySQL数据库会自动的选择第一个非空的唯一索引作为聚簇索引；而如果一张表既没有主键，又没有唯一索引，那这个时候MySQL数据库又会自动生成一个rowID作为聚簇索引。简单说，一张表必须有且仅有一个聚簇索引。
所以，从这个角度讲，在二级索引的叶子节点中存储的其实是对应的聚集索引的值（如果有主键，就是主键值；没有主键，有非空的唯一索引，那就是唯一索引的值；如果既没有主键，也没有非空的唯一索引，那就是自动生成的rowID的值）。
帮助理解的图示：
聚簇索引的叶子节点中存储的data，就是这个主键对应的这一行的数据。
二级索引叶子节点中存储的值，就是这个字段值对应的主键ID。
2.3 什么是回表查询 ？
必答内容：
在MySQL默认的InnoDB存储引擎中，有两类索引，分别是：聚簇索引和二级索引。 聚簇索引，他的特点呢就是数据与索引存放在一块儿，B+tree索引结构的叶子节点保存了整行数据，而且在一张表中，聚簇索引有且仅有一个，默认主键索引就是聚簇索引。二级索引，指的是数据和索引分开存储，B+tree的叶子节点保存对应的主键，二级索引在一张表中可以有多个。
所谓回表查询，就指的是，在执行这条SQL语句的时候，先根据二级索引去检索出对应的主键值；然后再根据主键值，到聚簇索引中查询出对应的数据，这个过程就叫回表查询。 所以回表查询，是需要扫描两次索引的，性能相对来说会差一些。
进阶内容：
所以，在项目开发中，我们进行SQL优化的时候，如果需求允许的情况下，尽量避免回表查询，主要从以下几个方面来做：
1). 业务允许的情况下，尽可能根据主键查询，使用聚集索引-避免回表查询。
2). 为表中的字段，根据业务需求创建合适的联合索引，查询时使用索引覆盖-避免回表查询。
3). 使用索引下推，减少回表查询的次数。【索引下推，是mysql5.6之后提供的功能】
可能继续发问的问题：
你刚才提到索引下推，简单聊聊什么是索引下推？
索引下推（Index Condition Pushdown），是MySQL5.6后提供的功能，指的是在多条件查询SQL执行时，提前判断对应的搜索条件是否满足，满足了再去回表（就是将本应该在 server 层进行筛选的条件，下推到存储引擎层来进行筛选判断，这样能有效减少回表），通过减少回表次数进而提高查询效率。
2.4 为什么MySQL索引结构是B+tree ?
必答内容：
其实这个问题，我们可以做一个假设啊。
假设索引结构是二叉搜索树、平衡二叉树 或 红黑树等，其实本质都是二叉树，一个节点下最多只能有两个子节点，如果这张表要存储的数据量比较大，二叉树的层级将会非常深，检索效率会很低。
而如果索引结构是Btree，在B树中，非叶子节点和叶子节点既要要存储key和指针，还要存放数据，而InnoDB的物理存储结构中，一页（Page）的大小是固定的，就是16KB。 那这一页中能够存储的key的数量并不多，就会造成大数据量情况下，树的层级较深，检索速度慢。 还有一个问题，就是由于 非叶子节点和叶子节点既要要存储key，还要存放数据，查找效率并不稳定。 （有些数据，只需要一次查找，有些数据，可能需要五六次，有些...）
所以，在MySQL数据库中才使用了B+tree作为索引的数据结构。 主要有以下优势：
在B+tree中，非叶子节点并不存放数据，只存放key和指针，所以一页（Page）中能够容纳的key将更多，相同数据量的情况下，树的层级要浅的多，检索效率高。
所有的数据都存储在B+tree的叶子节点中，也就意味着无论什么数据，都需要找到叶子节点才能查询到对应的数据，检索效率更加稳定。
第三是B+树数据都存储在B+tree的叶子节点，并形成了一个双向链表，便于区间范围查询。
可能继续发问的问题：
那MySQL的B+tree的索引结构，树的高度一般是多高呢？
嗯，这个高度其实是可以计算出来的，一般高度在2-3层，如果高度为3，基本上就可以容纳一两千万的数据了。如何计算呢？
我们的索引是在页（Page）中存储的，而一个页的大小模式为（16KB）。
对于非叶子节点来说，页中存储的除了具体的key之外，还有一个就是指针 。(假设主键为bigint占8个字节，指针占6个字节)
那么我们就可以大概计算出一页中可以存储的key数量为：16 1024 / 14 = 1170 。也就意味着一个页（Page）中约可以存1170个key
假设一行数据的大小为1KB，一页可以存16条数据。那两层的B+tree可以容纳：117016=18720条数据。
那三层的B+tree可以容纳：1170 1170 16 = 21902400 条数据。
帮助理解的图示：
3、索引优化
3.1 索引创建的原则 ?
必答内容：
好的，我们知道索引确实可以提高查询的效率，但前提是需要针对于数据库表创建合适的索引。创建索引的时候，主要考虑一下几点原则：
1). 针对于数据量较大，且查询比较繁琐的表创建索引。（单表超过10w记录）
2). 针对于经常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择为区分度高的列建立索引，如果该字段是唯一的，建立唯一索引，效率更高。（区分度越高，效率越高）。
4). 在varchar类型的字段上，建议指定索引长度（建立前缀索引），没必要对全字段建立索引，根据实际文本区分度决定索引长度就可以。
5). 尽量建立联合索引，而且在联合索引中将区分度高的字段放在前面，减少单列索引。（查询时，联合索引很多时候可以索引覆盖，避免回表，提高效率）
6). 在满足业务需求的前提下，建立适当的索引，索引不宜过多。（索引过多，会增加维护索引的成本，影响增删改的效率）
可能会继续发问的问题：
1). 索引的创建只是第一步，要想提高查询效率，还得正确的使用索引，避免索引失效，你知道索引失效的场景吗？ 【参照 3.2】
3.2 简单聊聊索引失效的场景 ?
必答内容：
好的，索引失效这个问题，确实是在项目开发中非常常见的一类问题。那我就结合我之前的项目经验来聊一下，之前遇到的一些索引失效的场景：
第一类呢，就是在联合索引使用的时候，违反最左前缀法则，比如查询的条件并不是从索引最左边的列开始的。
第二类呢，就是范围查询（非等值查询）右侧的列，不能使用索引。
第三类呢，就是在索引列上进行运算或函数操作，索引将失效。非常典型的，像我们基于substring这样的函数截取字段值。
以及如果在条件匹配时，需要进行隐式类型转换的时候。比如：where gender = 1，而gender是char类型，这种字符串不加引号，虽然查也能查出来，但也会造成索引失效。
还有像常见的，以 % 开头的like模糊匹配，索引也会失效。
当然这样的场景很多啊，上面这几个只是比较常见的索引失效的场景，所以在项目开发中，编写SQL时，就要避免这些情况的发生。
进阶内容：
而对于索引是否生效，到底走哪个索引，以及具体SQL语句的执行性能到底怎么样。 这个我们可以借助于 explain 来查看sql语句的执行计划 ，具体问题具体分析。
我们可以通过explain执行计划中的 key 来确定此次查询是否使用了索引，以及通过 type 来判定SQL的执行性能，一定要规避 type 为all全表扫描的情况，还有像 extra 的信息也需要关注一下。
可能继续发问的问题：
你刚才提到最左前缀法则，那什么是最左前缀法则？
最左前缀法则是针对于联合索引的，指的是 查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
就比如，我按照 name，profession，age 这个顺序，针对于这三个字段，创建了联合索引。那么此时如何是根据 name 查询是会走联合索引的；根据 name, profession 也是会走索引的；根据 name，profession，age 也会走索引。但是如果根据 profession 或 profession，age 查询是不会走索引的，因为没有从最左边的字段 name 开始查询。
帮助理解的图示及说明：
4、SQL性能分析及优化
4.1 如何定位出项目中的慢SQL?
必答内容：
这个问题，在项目开发中，是非常常见的。 在我们之前的项目中，用到了链路追踪组件 SkyWalking，通过SkyWalking我们就能够知道，所有请求的调用链路及执行耗时，在显示的报表中，我们就可以看出哪一个接口比较慢，也可以看到这个接口执行过程中，每一个部分的具体耗时，包括SQL的执行具体时间也都可以看到，通过这个就可以定位慢SQL了。
那如果在一些项目中，没有用到这类的监控工具，也可以开启MySQL的慢查询日志，通过MySQL的慢查询日志来定位慢SQL。比如：我们可以在配置文件中配置一下，只要SQL语句的执行耗时超过1秒，我就需要将其记录在慢查询日志中，最终我们只需要通过这份慢查询日志，就能够知道哪些SQL的执行效率比较低。
可能继续问道的问题：
而具体执行耗时多长时间才算慢 ，时间一般设置为多长呢？
这个时间，其实没有一个定数，不同性质的项目之间也有差异，所以这个慢查询的界定时间，一般都要根据项目的情况来设置一个经验值，我们之前的项目中，设置的是1秒。
4.2 在项目中如何知道SQL语句的执行性能，以及索引是否生效？
必答内容：
这个我们可以借助于MySQL中提供的 explain 关键字，在查询的SQL语句之前，加上explain来查询SQL语句的执行计划。
当然explain查看到的执行计划信息比较多，我们主要关注几个核心指标就可以了。比如：
通过 key 、key_len 就能够知道是否命中索引。
通过 type 指标，就能知道该SQL的性能怎么样，有没有进一步优化的可能。一定要规避all全表扫描的情况。 type指标性能由好到坏，依次是：NULL > system > const > eq_ref > ref > range > index > all
还需要关注一个指标，就是extra额外的信息。 通过这一项，我们就能够知道，有没有回表查询，有没有涉及到file sort排序。
然后，我们就可以针对 explain 查看到的执行计划，针对于SQL进行优化了。
4.3 谈谈你在SQL优化方面的经验？
必答内容：
嗯，这个话题就比较大了。 那我们在项目中，优化SQL的查询执行效率，会从多个维度来考虑的。
第一个呢，就是表的设计。
比如数据类型的选择，数值类型到底选择 tinyint、int还是bigint，要根据实际需要选择。字符串类型，到底选择char还是varchar，也需要根据具体业务确定。（char定长字符串，效率高；varchar变长字符串，效率略低）
还需要考虑主键的设计，主键在设计时，尽量考虑递增顺序插入的主键，比如：自增主键 或 雪花算法生成的主键。（这样可以规避页分裂、页合并现象的产生）
第二个呢，就是索引的创建。
针对于数据量较大，且查询比较繁琐的表创建索引。（单表超过10w记录）
针对于经常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择为区分度高的列建立索引，如果该字段是唯一的，建立唯一索引，效率更高。（区分度越高，效率越高）。
在varchar类型的字段上，建议指定索引长度（建立前缀索引），没必要对全字段建立索引，根据实际文本区分度决定索引长度就可以。
尽量建立联合索引，而且在联合索引中将区分度高的字段放在前面，减少单列索引。（查询时，联合索引很多时候可以索引覆盖，避免回表，提高效率）
在满足业务需求的前提下，建立适当的索引，索引不宜过多。（索引过多，会增加维护索引的成本，影响增删改的效率）
第三个呢，就是索引的使用。
编写DQL时，在满足业务需要的情况下，要尽量避免索引失效的情况。 【索引失效的情况，见 3.2】
尽量使用索引覆盖，避免回表查询，提高性能。
那这些情况呢，都可以通过 explain 关键字来查看SQL语句的执行计划。
进阶回答：
那如果从数据库层面来讲，也可以基于读写分离的模式，来降低单台服务库的访问压力，从而提高效率。
当然，如果数据量过大，也可以考虑对目前项目中的数据库进行分库分表处理。
