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超线程,所以实际取值为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

创建索引

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

阅读 193

推荐阅读