java八股文之数据库

1.如何定位慢查询

1.使用运维工具（例如Skywalking），监测哪个接口慢，然后排查出对应的慢sql
2.在mysql中开启慢日志查询，设置超过多少秒的就定义为慢Sql，一旦sql执行时间超过设置时间，就会被记录进日志文件中。（注：此功能会损耗sql性能，故建议只在调试阶段开启）

2.如何分析慢SQL语句

1. 使用sql自带的EXPLAIN执行缓慢sql，然后重点关注key和key_len字段，key字段表示这次查询命中了哪些索引；key_len表示实际查询过程中，索引使用的长度。两者结合判断是否发生了索引失效
2. 通过type字段（表示连接类型）查看sql是否有进一步优化空间，及是否存在全索引扫描（index）或全盘扫描（ALL）
3. 通过extra字段（这个字段展示的是额外的信息），查看是否发生了回表查询的情况，如果发生，可以尝试添加索引或返回改字段来修复这个问题。

3.介绍一下数据库索引

1. 索引(index)是帮助MySQL高效获取数据的数据结构（有序）
2. 提高数据检索的效率，降低数据库的IO成本（不需要全表扫描）
3. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

4.介绍一下索引底层结构

MySQL的InnoDB引擎采用的B+树的数据结构来存储索引
B+树优点：

1. 相对于红黑树，B+数阶数更多，是一个矮胖书，所以路径更短
2. 磁盘读写代价B+树比B树更低，非叶子节点只存储指针，叶子阶段存储数据
3. B+树便于扫库和区间查询，叶子节点是一个双向链表

5.介绍一下聚集索引和非聚集索引（二级索引）

聚集索引

1. 将数据存储和数据放在了一起，索引结构的叶子节点保存了一整行的数据
2. 聚集索引必须有，且只有一个
3. 如果有主键，聚集索引就是主键
4. 没有主键，第一个唯一索引就是聚集索引
5. 4和5都不满足，则InnoDB会自动生成一个rowid作为隐藏聚集索引

非聚集索引（二级索引）

数据与索引分开存放，索引结构的叶子节点关联的是对应主键

6.介绍一下回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表查询
ps：优化回表查询可以用覆盖索引，即设计你的查询和索引，使得所有在SELECT、WHERE、JOIN ON、ORDER BY以及GROUP BY中使用的列都包含在索引中。

7.介绍一下覆盖索引

覆盖索引是指select查询语句使用了索引，需要在返回的列，可以在索引中全部能够找到，无需再次回表查询。如果使用主键查询，因为主键是包含整行数据的聚集索引，所以必定是走覆盖索引。

8.MYSQL超大分页怎么优化

可以使用覆盖索引+子查询方式优化。超大分页一般都是在数据量比较大时，使用了Iimit分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以采用覆盖索引和子查询来解决。先分页查询数据的id字段，确定了id之后，再用子查询来过滤，只查询这个id列表中的数据就可以了因为查询的时候，走的覆盖索引，所以效率可以提升很多。

9.说一说索引的创建原则

1. 针对于数据量较大，且查询比较频繁的表建立索引。单表超过10万数据（增加用户体验）（重要）
2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。（重要）
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。（重要）
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。（重要）
7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

10.索引失效的情况有哪些

最左前缀法则：在查询条件中使用了复合索引时，数据库引擎会从复合索引的最左边开始匹配，并且只有在满足最左前缀的情况下，才会使用该索引来加速查询。

1. 违反最左前缀法则
2. 范围查询右边的列，不能使用索引
3. 不要在索引列上进行运算操作，索引将失效
4. 字符串不加单引号，造成索引失效。（会发生自动类型转换，在索引上发生任何类型转换都会造成索引失效）
5. 以%开头的Like模糊查询，索引失效

11.说一说sql的优化经验

1.表的设计优化（参考阿里开发手册《嵩山版》）

1. 比如设置合适的数值(tinyint int bigint),要根据实际情况选择
2. 比如设置合适的字符串类型(char和varchar)char定长效率高，varchari可变长度，效率稍低

2.SQL语句优化

1. SELECT语句务必指明字段名称（避免直接使用select*)
2. SQL语句要避免造成索引失效的写法
3. 尽量用union all代替union union会多一次过滤，效率低
4. 避免在where子句中对字段进行表达式操作（就是函数操作，如调用截取字符串的函数等）
5. Join优化能用inner join就不用left join或right join,如必须使用，一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join或right join,不会重新调整顺序

3.主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响可以采用读写分离的架构。
读写分离可以解决数据库的写入，影响的查询效率。

12.说一说事务的特性（ACID）

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

13.说一说事务的隔离级别

概念介绍：

• 脏读： 一个事务读到另外一个事务还没有提交的数据。
• 不可重复读： 一个事务先后读取同一条记录之间，因为另一个事务的变更，导致两次读取的数据不同。
• 幻读： 在一个事务中执行同一查询两次，但在两次查询之间，另一个事务插入了新的行或删除了某些行，导致第一次和第二次查询得到的结果集不同。

隔离级别：
1.读未提交（Read Uncommitted）：最低的隔离级别，允许一个事务读取另一个事务未提交的数据，容易产生脏读、不可重复读以及幻读问题。
2.读已提交（Read Committed）：大多数数据库系统的默认隔离级别，防止脏读，但不能避免不可重复读和幻读。
3.可重复读（Repeatable Read）：在MySQL的InnoDB存储引擎下，此隔离级别可以防止不可重复读，同时也能阻止大部分情况下的幻读。然而，在理论上，如果涉及范围查询，仍可能出现幻读。
4.串行化（Serializable）：最高的隔离级别，通过强制事务串行执行，完全避免了脏读、不可重复读和幻读的问题。在这种模式下，所有的并发冲突都会被彻底解决，但代价是性能下降，因为它极大地限制了并发度。

14.undo log和redo log的区别

• redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据
• undo log： 记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据

总结： redo log保证了事务的持久性，undo log保证了事务的原子性和一致性

15.数据库有哪些锁

1. 按锁粒度分类

表级锁（Table-Level Locks）： 锁定整个表，阻止其他事务对该表进行写操作（有时也包括读操作）。这类锁的开销较低，但并发性较差。
行级锁（Row-Level Locks）： 锁定表中的特定行，允许其他事务访问同一表中未被锁定的行。提供了更高的并发性，但管理成本较高。
页级锁（Page-Level Locks）： 锁定磁盘上的一个页面（通常是数据库存储的基本单位），介于表级锁和行级锁之间，在并发性和管理成本方面提供了一种折衷方案。

2. 按锁的功能分类

共享锁（Shared Locks, S锁）： 允许多个事务同时读取相同的资源，但阻止其他事务获取排他锁来修改该资源。也就是说，多个事务可以同时持有某个对象的共享锁。
排他锁（Exclusive Locks, X锁）： 当一个事务获得了排他锁后，其他任何事务都不能再获取该对象的任何类型的锁（包括共享锁和排他锁），直到当前事务释放排他锁为止。这种锁用于数据修改操作，如UPDATE、DELETE或INSERT。
更新锁（Update Locks, U锁）： 一种特殊的锁，旨在防止常见的死锁情况。在需要将共享锁升级为排他锁之前，首先获得更新锁。只有当没有其他事务持有该资源的锁时，才能成功地从更新锁转换为排他锁。
意向锁（Intention Locks）： 这些锁不是直接加在被锁定的数据上，而是加在其上级结构上（例如，如果对某行加了X锁，则会对包含该行的表加意向排他锁）。它们指示了一个事务计划在更细粒度级别上请求锁定的意图。

3. 其他类型的锁

范围锁（Range Locks）： 用于防止幻读问题，锁定查询的一个范围，阻止其他事务在这个范围内插入新的数据。
模式锁（Schema Locks）： 保护数据库对象结构的改变，比如创建索引或者修改表结构等操作

16.事务的隔离级别是如何保证的（你解释一下MVCC)

事务的隔离性是由排他锁和mvcc实现的。

其中vcc的意思是多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现主要是分为了三个部分，第一个是隐藏字段，第二个是undo log日志，第三个是readView读视图

隐藏字段是指：在mysql中给每个表都设置了隐藏字段，有一个是trx_id(事务id),记录每一次操作的事务id,是自增的；另一个字段是roll_pointer(回滚指针)，指向上一个版本的事务版本记录地址

undo log主要的作用是记录回滚日志，存储老版本数据，在内部会形成一个版本链，在多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer指针形成一个链表

readView解决的是一个事务查询选择版本的问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问那个版本的数据，不同的隔离级别快照读是不一样的，最终的访问的结果不一样。如果是rc(读已提交)隔离级别，每一次执行快照读时生成ReadView，如果是rr(可重复读)隔离级别仅在事务中第一次执行快照读时生成ReadView,后续复用

17.MYSQL主从同步原理

MySQL主从复制的核心就是二进制日志binlog，这个日志记录了所有执行过的DDL(数据定义语言)语句和DML(数据操纵语言)语句。
过程：

1. 主库在事务提交时，会把数据变更记录在二进制日志文件Binlog中。
2. 从库读取主库的二进制日志文件Binlog,写入到从库的中继日志Relay Log。
3. 从库执行一遍中继日志中的SQL语句，从而将主数据库的数据写入自己的数据库中

18.说一下分库分表

1. 水平分库，将一个库的数据拆分到多个库中，解决海量数据存储和高并发的问题
2. 水平分表，解决单表存储和性能的问题
3. 垂直分库，根据业务进行拆分，高并发下提高磁盘1O和网络连接数
4. 垂直分表，冷热数据分离，多表互不影响

其他： 分库分表后，可以用MyCat等中间件解决分库分表后出现的问题，如：分布式事务一致性问题，跨节点关联查询，跨节点分页、排序函数，主键避重等