新鲜出炉，2020最详细MySQL面试题（附答案）

数据库隔离级别分为哪几种？

read uncommitted
read committed
repeatable read
serial

如果将数据库隔离级别设置为read uncommitted，会有什么问题

会出现脏读。脏读例子，如现在有两个事务，分别是事务a，事务b。一开始数据库中的row1的money列值为90。事务b，开启一个事务，将row1的money列设置为100，但是还没有提交，这时候，事务a开启一个事务，首先读取row1的数据money列为100，此时事务b回滚事务，此时a读取到的值为100，是脏读，因为数据库中的数据是90.a事务读取到了没有提交的数据就是脏读，解决脏读的方法就是将隔离级别升级为RC（read committed）

什么是不可以重复读

原始数据为row1的money列数据值90
事务a开启事务，读取到row1的money列为90
然后事务b开启事务，将row1的money列改为100，并提交
此时事务a再次去读取row1的money列，发现现在row1的money列变成了100。事务a发现我同一个事务读取都两个不同的值，这个就是不可重复度，要解决不可重复读，需要将隔离级别设置为repeatable read，设置为可重复的状态。

什么是幻读

数据库表table1里有1行数据
事务a开启一个事务，读取table1的数据，一共有1条数据，然后事务b开启一个事务往表里插入一条数据，并提交
此时事务a在查询table1的数据，发现现在有2条数据。这时候前后两次查询不一致，感觉出现了幻觉，这个就是幻读。解决幻读的方法就是将数据库的隔离级别设置为serial级别，serial级别就是锁住这个表，当有事务查询该表时，其他事务不允许修改该表的数据，性能会比较差

MySQL的引擎有几种？区别是什么？

Innodb
支持事务，是事务安全的，提供行级锁与外键约束，有缓冲池，用于缓冲数据和索引
适用场景：用于事务处理，具有ACID事物支持，应用于执行大量的insert和update操作的表
MyISAM
不支持事务，不支持外键约束，不支持行级锁，操作时需要锁定整张表，不过会保存表的行数，所以当执行select count(*) from tablename时执行特别快
适用场景：用于管理非事务表，提供高速检索及全文检索能力，适用于有大量的select操作的表

MySQL主从分离，读写分离；分库分表，在项目中怎么实现？

关键配置
1.配置主数据源，从数据源。主数据源可以是多个，多个就是为了分库，一个主库，可以配置多个从库
2.配置分库分表规则
3.配置主库的从库有哪些
当插入一条数据时，首先根据分库规则，找到数据要落入到哪个分库，假设有两个分库，路由规则为mod运算，一条数据id为3，那么插入这条数据落入到哪个分库运算如下 3%2=1，那么这条数据会落入1分库，涉及更新，新增操作都是只会走主数据库，同步过程由MySQL去实现的。当查询一条数据id为3的数据，因为是读写分离，首先会进行选择去哪个分库去查，3%2=1，说明数据落在1分库，然后会1分库的从库查询，具体请求哪个分库，是可以配置具体的负载均衡策略。loadBalanceAlgorithmType: ROUND_ROBIN是其中的一种

MySQL索引类型有哪几种？

FULLTEXT，HASH，BTREE，RTREE。
重点需要了解B+tree，什么是B+Tree,为什么B+Tree 能够查询得快？
唯一索引，非唯一索引，单索引，组合索引。多个单索引与组合索引的区别是什么？
我们在MySQL中的数据一般是放在磁盘中的，读取数据的时候肯定会有访问磁盘的操作，磁盘中有两个机械运动的部分，分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟，而磁盘移动则是在盘片旋转到指定位置以后，移动磁臂后开始进行数据的读写。那么这就存在一个定位到磁盘中的块的过程，而定位是磁盘的存取中花费时间比较大的一块，毕竟机械运动花费的时候要远远大于电子运动的时间。当大规模数据存储到磁盘中的时候，显然定位是一个非常花费时间的过程，但是我们可以通过B树进行优化，提高磁盘读取时定位的效率。

为什么B类树可以进行优化呢？我们可以根据B类树的特点，构造一个多阶的B类树，然后在尽量多的在结点上存储相关的信息，保证层数尽量的少，以便后面我们可以更快的找到信息，磁盘的I/O操作也少一些，而且B类树是平衡树，每个结点到叶子结点的高度都是相同，这也保证了每个查询是稳定的。

总的来说，B/B+树是为了磁盘或其它存储设备而设计的一种平衡多路查找树(相对于二叉，B树每个内节点有多个分支)，与红黑树相比，在相同的的节点的情况下，一颗B/B+树的高度远远小于红黑树的高度(在下面B/B+树的性能分析中会提到)。B/B+树上操作的时间通常由存取磁盘的时间和CPU计算时间这两部分构成，而CPU的速度非常快，所以B树的操作效率取决于访问磁盘的次数，关键字总数相同的情况下B树的高度越小，磁盘I/O所花的时间越少。

有没有遇到过数据库死锁？如何避免

MySQL有三种锁的级别：页级、表级、行级
1.
一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。
2.用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项 目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。
3.如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

数据库的mvcc是什么？

英文全称为Multi-Version Concurrency Control,翻译为中文即 多版本并发控制。是乐观锁的一种实现方式

每行数据都存在一个版本，每次数据更新时都更新该版本。
修改时Copy出当前版本随意修改，各个事务之间无干扰。
保存时比较版本号，如果成功（commit），则覆盖原记录；失败则放弃copy（rollback）

怎么优化数据查询

(1)避免使用NULL

NULL对于大多数数据库都需要特殊处理，MySQL也不例外，它需要更多的代码，更多的检查和特殊的索引逻辑，有些开发人员完全没有意识到，创建表时NULL是默认值，但大多数时候应该使用NOT NULL，或者使用一个特殊的值，如0，-1作为默认值。

(2)仅可能使用更小的字段

MySQL从磁盘读取数据后是存储到内存中的，然后使用cpu周期和磁盘I/O读取它，这意味着越小的数据类型占用的空间越小，从磁盘读或打包到内存的效率都更好，但也不要太过执着减小数据类型，要是以后应用程序发生什么变化就没有空间了。修改表将需要重构，间接地可能引起代码的改变，这是很头疼的问题，因此需要找到一个平衡点。
在跨多个不同的数据库时使用UNION是一个有趣的优化方法，UNION从两个互不关联的表中返回数据，这就意味着不会出现重复的行，同时也必须对数据进行排序，我们知道排序是非常耗费资源的，特别是对大表的排序。

UNION ALL可以大大加快速度，如果你已经知道你的数据不会包括重复行，或者你不在乎是否会出现重复的行，在这两种情况下使用UNION ALL更适合。此外，还可以在应用程序逻辑中采用某些方法避免出现重复的行，这样UNION ALL和UNION返回的结果都是一样的，但UNION ALL不会进行排序。
系统配置
key_buffer_size = 256M
key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
read_buffer_size = 4M
读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。
join_buffer_size = 8M
联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
tmp_table_size = 256M
max_connections = 768
指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。
max_connect_errors = 10000000
wait_timeout = 10
指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
thread_concurrency = 8
该参数取值为服务器逻辑CPU数量2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为42=8
skip-networking
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
table_cache=1024
物理内存越大,设置就越大.默认为2402,调到512-1024最佳
innodb_additional_mem_pool_size=4M
默认为2M
innodb_flush_log_at_trx_commit=1
设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
innodb_log_buffer_size=2M
默认为1M
innodb_thread_concurrency=8
你的服务器CPU有几个就设置为几,建议用默认一般为8
key_buffer_size=256M
默认为218，调到128最佳
tmp_table_size=64M
默认为16M，调到64-256最佳
read_buffer_size=4M
默认为64K
read_rnd_buffer_size=16M
默认为256K
sort_buffer_size=32M
默认为256K
thread_cache_size=120
默认为60
query_cache_size=32M

创建索引

要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
in和 not in 也要慎用，否则会导致全表扫描
对于连续的数值，能用 between就不要用 in
很多时候用 exists 代替 in 是一个好的选择
模糊查询不能是%xx%,否则会全表扫描
如果在 where 子句中使用参数，也会导致全表扫描
应尽量避免在 where子句中对字段进行表达式操作
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差
select count() from table；这样不带任何条件的count会引起全表扫描
索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
任何地方都不要使用 select * from t ，用具体的字段列表代替“”，不要返回用不到的任何字段
尽量使用表变量来代替临时表。
在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
尽量避免大事务操作，提高系统并发能力。

spring 的事务传播机制有几种，默认机制是什么

事务传播行为类型

说明

PROPAGATION_REQUIRED

如果当前没有事务，就新建一个事务，如果已经存在一个事务中，加入到这个事务中。这是最常见的选择。

PROPAGATION_SUPPORTS

支持当前事务，如果当前没有事务，就以非事务方式执行。

PROPAGATION_MANDATORY

使用当前的事务，如果当前没有事务，就抛出异常。

PROPAGATION_REQUIRES_NEW

新建事务，如果当前存在事务，把当前事务挂起。

PROPAGATION_NOT_SUPPORTED

以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。

PROPAGATION_NEVER

以非事务方式执行，如果当前存在事务，则抛出异常。

PROPAGATION_NESTED

如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作。

A方法没有声明事务，b方法定义了事务，a方法中调用了b方法，如果b方法运行时抛出异常，事务是否会回滚？

不会回滚，因为a方法根本没有交给spring进行事务管理，调用a方法跟调用普通方法一样，没有加上开启事务的逻辑。