java面试题-Mysql常见面试题

Mysql面试题

如何定位慢查询？

方案一：可以使用Arthasl、Prometheus 、Skywalking等开源工具进行监控

方案二：可以使用mysql提供的慢查询日志的功能，这个默认是没有开启的，需要在MySQL了系统配置文件中进行配置，并且也可以设置sql执行超过多少时间来记录到这个日志文件中，一般这个值都是配置2秒，只要sql执行超过2秒就会记录到慢查询日志中

一个sql语句执行慢，应该如何分析？

我通常使用的是mysql自带的执行计划explain来查看sql语句的执行情况，比如这里面考科一通过key和key_len检查是否命中了索引，命中了哪个索引，通过type字段查看sql语句是否有进一步的的优化空间，是否存在全索引扫描和全表扫描，第三个可以通过extra建议来判断，是否出现了回表的情况，如果出现了回表查询通过添加索引和修改返回字段来避免回表查询

什么是索引？

索引是帮助Mysql快速获取数据的数据结构，主要是用来提高检索数据的效率，降低数据库的IO成本，通过索引列对数据进行排序，提高了数据排序的效率，索引也不是越多越好，要根据实际情况创建索引，索引多了会影响写数据的效率

mysql索引底层的数据结构

mysql默认的存储引擎InnoDb采用的是B+树的数据结构来存储索引,选择B+树的主要原因：

1、B+树的阶数更多，层数更低，路径就更短，效率就越高

2、B+树的数据都存储在叶子节点，磁盘的读写代价更低

3、叶子节点是一个双向链表，便于扫库和区间查询

什么是聚簇索引什么是非聚簇索引 ?

聚簇索引：也叫聚集索引，是指将数据和索引放到一块，B+树的叶子节点保存了整行数据，有且只有一个，一般使用的是主键来当做聚簇索引

非聚簇索引：也叫做二级索引，是指数据和索引不放到一块，B+树的叶子节点保存了整行数据的唯一标识一般是主键，可以有多个，一般我们自己定义的索引都是非聚簇索引

什么是回表查询？

回表查询是值先通过二级索引查询到数据的主键，在通过主键到聚簇索引查询所需要数据的过程

什么是覆盖索引？

覆盖索引是指在select查询的时候，使用了索引，在返回的列中在索引能够全部找到，如果使用id查询，直接走的就是聚集索引，一次索引扫描，就能返回数据，性能高

如果按照二级索引查询数据的时候，返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *，尽量在返回的列中都包含添加索引的字段

MYSQL超大分页怎么处理 ?

超大分页一般是在数据量比较大时，使用利limit进行分页查询，并且需要对数据进行排序，这个时候效率就很低，我们可以使用覆盖索引和子查询来进行优化

先分页查询数据的id字段，在确定id字段之后再通过子查询来过滤，只查询这个id列表的数据就可以了，因为查询id的时候走的是覆盖索引，效率可以提升很多

索引创建的原则有哪些？

1、为数据量大，查询比较频繁的表创建索引

2、常作为查询条件、排序、分组的字段创建索引

3、创建索引的字段内容区分度要高

4、对内容教长的字段使用前缀索引

5、尽量使用联合索引，这样可以尽可能的使用覆盖索引，避免回表查询

6、要控制索引的数量，索引并不是越多越好，索引会影响增删改的效率

7、如果索引列不能存储null值，在创建表时使用not null约束他

什么情况下索引会失效？

1、违背了最左前缀法则

2、范围查询右边的列，索引会失效

3、在索引列上做运算操作

4、字符串不加单引号，主要是要进行类型转换

5、以%开头的like模糊查询

可以从哪几个方面对sql进行优化？

从几个方面进行sql的优化，第一是建表的时候可以进行优化，第二是使用索引优化，第三是编写sql语句的时候，对sql语句进行优化，第四可以采用主从复制，读写分离的架构，不让写操作影响读的效率，第五如果数据量特别大的话，可以进行分库分表

创建表的时候怎么进行优化？

这个可以参考阿里出的开发手册《嵩山版》，比如对不同的内容选择不同的数据类型，比如tinyint、int、bigint这些类型，还有char和varchar，如果数据长度基本差不多尽量使用char，如果数据长度不一样那就选择varchar，特别长的可以使用text

怎么使用索引优化？

1、为数据量大，查询比较频繁的表创建索引

2、常作为查询条件、排序、分组的字段创建索引

3、创建索引的字段内容区分度要高

4、对内容教长的字段使用前缀索引

5、尽量使用联合索引，这样可以尽可能的使用覆盖索引，避免回表查询

6、要控制索引的数量，索引并不是越多越好，索引会影响增删改的效率

7、如果索引列不能存储null值，在创建表时使用not null约束它

在查询时避免索引失效的情况，比如遵循最左前缀法则，避免在索引列上左运算操作、字符串要加单引号避免类型转换，模糊查询尽量不要以%开头，范围查询尽量放到后面，因为范围查询右边的列，索引会失效

平时对sql语句做了哪些优化呢？

1、在使用select查询的时候务必指明字段，不要使用select * 进行查询

2、在写sql语句的时候尽量避免索引失效的写法，尽量使用索引

3、如果是聚合查询，尽量使用union 代替union，因为union会多一次过滤，会将重复的数据过滤，会影响性能

4、表关联的话尽量使用inner join，不要使用left join和right join，如果使用一定要小表驱动大表

事务的特性？

原子性：事务是不可分割的最小的操作单元，要么全部失败，要么全部成功

一致性：事务完成时，必须使所有的事务都保持一致的状态。

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。事务与事务之间是隔离的

持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务带来了哪些问题？

1、脏读问题：指的是一个事务读取到了另一个事务没有提交的数据，这样子数据是有问题的，对这样有问题的数据进行操作是有问题的

2、不可重复读：指的是一个事务里面两次读取同一行的数据是不一样的，因为有可能另一个事务对这行数据进行了修改并已提交，因此两次读取的数据出现了不一样的情况，因此为不可重复读

3、幻读：幻读是与不可重复读类似，幻读是指在一个事务中读取到了其他事务新插入已提交的数据，导致同一个事务在后面的查询中多出来几条数据，像出现了幻觉一样，简单来说，不可重复读就是更新引发的并发事务问题，幻读就是插入引发的并发事务问题。

怎么解决并发事务问题，mysql默认的隔离级别是什么？

mysql有四种隔离级别分别是：

读未提交：他解决不了并发事务的三个问题，所以项目中一般不使用

读已提交：它可以解决脏读问题，但是解决不来不可重复读和幻读问题

可重复读：它可以解决不可重复读问题，但是解决不了幻读问题

序列化：它可以解决以上所有并发问题，但是效率特别低，所以一般也不使用

mysql默认隔离级别是可重复读，一般使用的时候都是使用的是mysql默认的隔离级别

undo log和redo log的区别

redo log用来记录数据页的物理变化，当从缓存池同步数据到磁盘出错时，可以用redo log进行数据的恢复，保证了事务的持久性

undo log是一个逻辑日志，用来记录增删改的逆操作，比如当执行一条delete，undo log则会记录一条相应的insert语句，在进行回滚的时候，就可以从undo log中读取相应的内容进行回滚，undo log保证了数据的原子性和一致性

事务中的隔离性是如何保证的呢？(你解释一下MVCC)

事务的隔离性是由锁和MVCC来实现的

MVCC的意思是并发版本控制，指维护一个数据的多个版本，使得读写操作没有冲突，它的底层实现是通过隐藏字段，undo log日志和readview实现，

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

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

readview：解决的是一个事物查询选择的版本问题，在内部定义了一些匹配规则和当前的一些事务id判断该访问哪个版本的数据，不同级别的快照读是不一样的，在RC隔离级别下，每一次在进行快照读的时都会生成readview，在RR隔离级别下，复用改事务第一次快照读时生成的readview

MySQL主从同步原理

mysql的主从复制的核心就是二进制日志，具体步骤如下：

1、mysql在事务提交时，会将数据变更（DDL和DML）记录到Binlog日志文件中

2、从库开启一个I/O线程读取主库的Binlog日志，写入到从库的中继日志Relay log中

3、从机中的另一个sql线程读取Relay log中的数据，进行主从同步，这样就保证了主从数据一致

用过MySQL的分库分表吗？

没有用过，但是了解过，一般情况下只有在单表数据超过1000万条，或单表数据量达到了20G，才回进行分库分表

具体拆分策略：

水平分库：将一个库中的数据水平拆分到多个库中，解决海量数据的存储和高并发问题

水平分表：将一个表的数据水平拆分到多个表中，解决单表存储和性能问题

垂直分库：根据业务进行拆分，高并发下提高磁盘IO和网络连接数

垂直分表：冷热数据分离，多表互不影响，提高单表查询效率