java面试题（数据库）

Mysql

优化

如何定义慢查询

定位：mysql自带慢日志：记录了所有超过执行时间的sql日志。在配置文件中开启配置，设置慢日志的时间，如：2秒会记录到日志中，可以拿到慢sql，调试阶段才开启，会损耗资源。

sql分析：在sql前添加explain 会输出sql执行的参数：1.通过key和key_len检查是否命中了索引。2.通过type查看是否有进一步优化的空间，是否存在全盘扫描或者全表扫描。3.通过extra建议判断，是否出现了回表情况，如果有，可以尝试添加索引或者修改返回字段来修复。

索引的基本原理  

索引是帮助mysql高效获取数据的数据结构，是有序地；

可以提高检索效率，降低数据库的io成本，因为不需要全表扫描；

通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗；

索引的数据结构与具体的存储引擎有关。比较常用的是Hash索引和B+树索引；

索引的底层数据结构

mysql的InnoDB引擎默认使用B+树的数据结构来存储索引；

B+树索引：阶数更多，路径更短；磁盘读写代价B+树更低，非叶子节点只存储指针，叶子阶段存储数据；B+树便于扫库和区间查询，因为叶子节点是一个双向链表。

Hash索引： 采用一定的hash算法，把键值换算成新的hash;等值查询，那么hash索引有明显优势，前提是健值都是唯一的，如果健值不唯一，那就需要先找到健的位置，然后再扫描链表，找到对应的值;范围查询，hash索引就不好用了

聚簇索引和非聚簇索引的区别

聚簇索引：将数据与索引放在一起，B+树的叶子节点保存了整行数据，有且只有一个（一般为主键，当没有设置时，会初始化设置一个）；

非聚簇索引：将数据与索引分开存放，索引结构的叶子节点指向了数据的位置，通过位置找到数据，可以有多个；

回表查询：通过非聚簇索引找到对应的主键值，到聚簇索引中查找整行数据，这个过程就是回表。

区别：

1.查询聚簇索引可以直接获取数据，非聚簇索引需要二次查询

2.聚簇索引适合范围查询  ，非聚簇索引适合排序

1. 什么是覆盖索引

        覆盖索引指的是查询使用了索引，返回的列，必须在索引中全部能找到；使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *；

        超大分页怎么处理：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低；我们可以使用覆盖索引+子查询。

1. 索引的设计原则

        1.数据量较大，且查询比较频繁的表（单表10万+数据）

        2.常作为查询条件、排序、分组的字段

        3.内容区分度高

        4.内容较长，使用前缀索引

        5.尽量使用联合索引

        6.控制索引的数量

        7.如果索引列不能存储null值，创建时用not null进行约束

1. Myisam和Innodb的区别

1. InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引

1. InnoDB的主键索引的叶子节点存储着行数据，因此主键索引非常高效

1. MyISAM索引的叶子节点存储的是行数据地址，需要再寻址一次才能得到数据。

 d.  InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据，因此查询时做到覆盖索引会

非常高效

1. mysql中索引类型及对数据库的性能的影响

主键：特殊的唯一索引，数据列不允许重复，不允许为null，一个表只能有一个

唯一索引:数据列不允许重复，允许为null，一个表允许多个列创建唯一索引，保证数据的唯一性

普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值

索引可以极大地提高数据的查询速度，提高系统的性能；但是删除、新增、修改的速度会降低；每一个索引都要占用物理空间。

1. 最左前缀原则是什么

最左优先，在创建索引时，最频繁的一列放在最左侧

1. Innodb是如何实现事务的

以update为例：

1. Innodb收到update语句后，根据条件查询数据所在页，并缓存在Buffer Pool中

1. 执行update语句，修改Buffer Pool数据

1. 针对update语句生成一个redoLog对象, 并存入LogBuffer中

1. 针对update语句生成undo Log日志, 用于事务回滚

1. 如果事务提交, 那么则把redo Log对象及逆行持久化, 后续还有其他机制将数据页持久化到磁盘中；如果事务回滚, 则利用undo log 日志进行回滚

1. B树和B+树的区别，为什么Mysql使用B+树

B树：对节点排序、一个节点可以存多个元素，多个元素也排序了

B+树：拥有B树的特点、叶子节点之间有指针、非叶子节点在叶子节点上有冗余，并且排好序

因为索引是用来加快查询的，而B+树通过对数据进行排序可以提高查询速度，B+树通过一个节点可以存储更多的元素，使得B+树更加矮胖，所需要的IO更少，并且一页只有16KB，一般情况下任务深度为3的B+树可以存2000万行数据。利用B+树中的叶子节点有序链表可以很好地支持范围查找和全表扫描。

1. mysql锁的类型有哪些

1. 行锁：指锁住表的某一行或多行，其他事务访问时，被锁住的行不能访问，其他正常

1. 表锁：指锁住整个表，其他请求只能读，不能写；直到读锁释放，才能写入

1. 死锁：多个进程在执行过程中，争夺资源造成相互等待，无法继续执行

1. 乐观锁：假设数据不会冲突，所以在数据提交更新时才会检测，如果冲突则返回错误信息

1. 悲观锁：当对数据库中一条数据修改时，为了避免被其他人修改，直接加锁，防止并发

1. 共享锁：当数据加上锁后，其他事务只能读锁，而不能加写锁；直到所有读锁释放完毕，才能加写锁

1. 排它锁：当一个事务为数据加上写锁时，其他请求不能再加任何锁，直到该锁释放

1. 谈谈sql优化的经验？

1.表的设计优化：

        参考阿里开发手册，如：设置合适的数值和字符串类型

2.sql语句的优化：

        1.查询时指明字段，避免使用select *

        2.避免造成索引失效的写法

        3.尽量用union all代替union，union会多一次过滤

        4.避免在where中对字段进行表达式操作

        5.join优化，尽量使用inner join，内连接会对两个表进行优化，优先把小表放到外面，大表放到里面

3.主从复制、读写分离：

        如果数据库读操作比较多，为了避免写操作造成的性能影响，可以采取读写分离的架构。

4.分库分表（数据量特别大，超过500万+）

1. mysql哪些情况下会造成索引的失效

1. 非最左匹配（以最左的为起点字段查询可以使用联合索引，否则不能使用联合索引）

1. 错误模糊查询（只有右模糊查询才能触发索引）

1. 列运算（索引列使用了运算）

1. 使用函数（索引列使用函数）

1. 类型转换（字段为字符串类型，但是传入int类型，索引失效）

1. 使用is not null

1. 使用（!=或<>）

1. 索引列使用了or

1. 主键和唯一索引的区别

1. 主键索引不允许为null，且唯一；唯一索引允许为null，允许多个列创建唯一索引

1. 主键一定会创建一个唯一索引，有唯一索引的列不一定为主键

1. 主键索引只能有一个，但可以有多个唯一索引

1. 主键可以被其他表引为外键，唯一索引不行

1. 主键的执行顺序要高于唯一索引

1. 主键是约束，但唯一索引是索引

事务

事务的基本特性

        事务是一组操作的集合，它是一组不可分割的工作单位，事务会把所有操作视为一个整体向系统提交，要么全部成功，要么全部失败。

基本特性（ACID）：

        原子性：事务的最小执行单位，不允许分隔。确保动作要么全部完成，要么不完成

        一致性：执行事务前后数据保持一致，多个事务对同一个数据的读取结果是相同的

        隔离性：一个事务的修改在最终提交前，对其他事务是不可见的

        持久性：一个事务被提交后，所做的修改永久保存在数据库中

事务的隔离原则

并发事务问题：

        1.脏读：一个事务读到了另一个事务还没提交的数据

        2.不可重复度：一个事务先后读取同一条数据，但两次读取的数据不同

        3.幻读：一个事物按照条件查询数据时，没有对应的数据行，但在插入时发现数据存在，好像出现了“幻影”。

解决并发事务问题：事务的隔离级别

        未提交读：不能解决任何问题

        读已提交：可解决脏读

        可重复读：默认的隔离级别，可解决脏读、不可重复读

        可串行化：可解决所有，但是一般不使用，会给每行加锁，会导致大量超时和锁竞争的问题

undo log和redo log的区别

undo log：记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。

redo log：记录了数据页的物理变化，服务宕机时用来同步数据。

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

什么是MVCC

多版本并发控制：读取数据时通过一种类似于快照的方式将数据保留下来，这样读锁和写锁就不冲突了，不同事务session会看到自己特定版本的数据、版本链；

MVCC只能在已提交读和可重复读两个隔离级别下工作

mysql的主从同步原理

主从同步：当主库的数据发生改变时，变化会实时同步到从库；

主从同步好处：

        水平拓展数据库的能力

        容错、高可用

        数据备份

实现：在主库机器上，主从同步事件会被写到特殊的log文件中；在从库机器上，从库读取主从同步事件，并根据读取的事件变化，在从库上做对应的更改

mysql分库分表

什么是分库分表：当数据量过大时，查询速度降低。为提升效率，将一个表中的数据分散到多个数据库的多个表中。

常用分库分表工具：MyCat、ShardingSphere

数据分片方式：

垂直分片：从业务角度将不同的表拆分到不同的库中，能解决数据库数据文件过大的问题，但不能从根本上解决查询问题。

水平分片：从数据角度将一个表中的数据拆分到不同的库或者表中，这样可以从根本上解决数据量过大造成的查询效率低的问题。

分片策略：

1. 取余：均匀存放数据，但扩容非常麻烦

1. 按照范围：比较好扩容，但数据分布不够均匀

1. 按照时间：比较容易将热点数据区分

1. 按照枚举值：例如按地区分片

1. 按照目标字段前缀指定分区：自定义业务规则分片

水平分片从理论上突破了单机数据量处理的瓶颈，并且拓展自由，是分库分表的标准解决方案

阿里开发手册建议：一个表的数据超过1000万或者数据文件达到20G（业务开始前，提前预估3年业务量）

分库分表后执行流程（ShardingSphere）：

sql解析->查询优化->sql路由->sql改写->sql执行->结果归并

分库分表问题：

跨库查询、跨库排序、分布式事务、公共表、主键重复......