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 主从分离，读写分离；分库分表，在项目中怎么实现？

https://shardingsphere.apache…
关键配置
1. 配置主数据源，从数据源。主数据源可以是多个，多个就是为了分库，一个主库，可以配置多个从库
2. 配置分库分表规则
3. 配置主库的从库有哪些
参照网友写的例子：https://github.com/yudiandemi…
当插入一条数据时，首先根据分库规则，找到数据要落入到哪个分库，假设有两个分库，路由规则为 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 超线程，所以实际取值为 4 2=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

创建索引

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

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 方法跟调用普通方法一样，没有加上开启事务的逻辑。