面试题【Mysql】

事务的四大特性

事务是并发控制的基本单位，是一个不可分割的工作单元，保证了数据的一致性。

1. 原子性：不可分割的操作单元。事务中所有操作，要么全部成功；要么撤回执行事务之前的操作；
2. 隔离性：事务操作之间彼此独立，互不影响；
3. 一致性：执行事务前后，数据库要一致；
4. 持久性：事务一旦提交，结果就是永久的；

事务的隔离级别

• Read Uncommitted：读未提交（一个事务可以读取另一个未提交事务的数据），会发生脏读
• Read Committed：读已提交，能避免脏读（一个事务要等另一个事务提交后才能读取数据），会造成不可重复读（在一个事务中，第一次读取的数据和第二次读取的数据不一致，中间有其他事务对此数据进行了修改）（行锁）
• Repeatable Read：重复读（在开始读取数据（事务开始后），不再允许修改操作），会有幻读问题（行锁，锁设计到的行）
• Serializable：序列化，最高事务级别。在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读、幻读。这种事务级别效率低，消耗数据库性能，一般不用。（幻读，查询的时候，新增了记录，就会发现查询处的数据不一致）（表锁）

大多数的数据库事务隔离级别是 Read Committed，比如SqlServer、Oracle。

Mysql的默认隔离级别是 Repeatable Read

幻读：一个事务对表中的全部数据进行了修改，另一个事务以新增的方式也对数据进行了修改，那么操作第一个事务的人会发现表中还有未修改的行，像发生了幻觉一样。

互联网项目一般使用mysql的什么隔离级别

一般使用：读已提交（为什么myslq默认隔离级别是可重复读？）

主从复制，是基于binlog进行复制，在版本5.0以前，读已提交的隔离级别，在主从复制下是有bug的。

https://www.cnblogs.com/jywy/p/11058067.html

索引

索引，是为了协助快速查询的一个数据结构（对数据库表中一个或多个列进行排序的结构），实现通常使用B_TREE，使查询数据不会再去扫描整张表。类似于书籍的目录索引，加快检索数据。索引是需要占用物理空间的

Mysql数据库有四类索引

• index：普通索引，数据可以重复，无任何限制
• unique：唯一索引，要求值必须唯一，允许有空值
• primary key：主键索引，一种特殊的唯一索引，一个表只能有一个主键，不允许有空值
• 组合索引：在多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用
• fulltext：全文索引，是对于文本创建的索引（char，varchar，text），主要用于查找文本中的关键子字，并不是直接于索引中的值进行比较

索引可以提高查询速度，但是创建和维护索引需要消耗时间，同时也会影响插入速度。如果需要插入大量的数据，最好是先删除索引，插入数据后再建立索引。

索引生效条件

1. 最左前缀匹配，模糊查询时，使用 % 进行匹配，a%会使用索引，%a不会使用索引
2. 条件中有or，不会使用索引
3. 组合索引会按照条件生效，例如：index(a,b,c)，where a=a,c=c,b=b只有以a,b,c的顺序为条件，才能走组合索引

哪些列上适合创建索引

经常要搜索的列，经常连接的列（on），经常排序的列，where条件上的列

哪些列不适合创建索引？

查询使用很少的列（很少用到，并不能提高查询速度，相反，增加了索引，反而降低了系统的维护速度，并增加了空间物理占用）

数据值少的列（例如：性别，结果集占了表中数据行的很大比例，增加索引， 并不能明显加快检索速度）

定义为text、image、bit数据类型的列（加上无意义）

当修改性能远远大于检索性能时，不应该创建索引（新增和修改数据时，会对索引进行维护）

非空列

数据库三范式

主要是建立冗余较小，结构合理的数据库。

设计数据库时，主要还是看：需求->性能->表结构

第一范式：每一列属性都是不可再分割的，属性相近的列进行合并，确保不产生冗余数据

第二范式：每一行数据只做一件事

第三范式：数据不能存在传递关系

脏读、幻读、不可重复读

脏读：事务T1将某一值进行修改，然后事务T2读取该值，然后T1因为某种原因撤销对该值的修改，这就导致了T2读取到的数据时是无效的。

不可重复读：指在数据库访问时，一个事务内的两次相同查询却返回了不同数据。在一个事务内多次读取同一数据。在此事务还没结束时，另一个事务也访问该数据，并进行了修改。那么第一个数据读取的两次数据就有可能是不一样的。因此称为不可重复读。

幻读：比如第一个事务对表中的数据进行了修改，涉及到所有行。第二个事务通过插入一条新数据来修改表中的数据。那么就会发生，操作第一个事务的用户表中还有未修改的事务行，就像发生了幻觉一样。

不可重复读和幻读区别：在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现重复读了。但这种方法却无法锁住insert数据，所以当事务A读取或修改了全部数据，事务B还是可以insert提交数据，这时事务A就会发现莫名多了数据，这就是幻读。幻读是不能通过行锁来避免的。需要Serializable隔离级别，读用读锁，写用写锁，读锁和写锁互斥，这样可以有效的避免脏读，不可重复读，幻读，但会极大降低数据库的并发能力。

存储引擎MyISAM和InnoDB区别

InnoDB支持事务，支持外键，适合频繁修改以及涉及到安全性较高的应用，是默认引擎，不支持全文索引，支持行锁（某些情况下会锁整表，比如where name like "%abc%"），

MyISAM不支持事务，不支持外键，适合查询和插入为主的应用，支持全文索引

• Memory，将表中数据存储到内存中（所以数据处理速度快），但安全性不高，且不能建立过大的表；
• MyIsam，具有较高的插入，查询速度，但不支持事务，不支持外键
• InnoDB，支持事务，支持外键，安全性高

Char和Varchar区别

Char是固定长度，长度不够，将会用空格填充（尾部），长度范围是1-255，超过时只能用varchar或text

Varchar是可变长度，当存储的字符小于设定长度时，将按实际长度存储。

Mysql中有哪几种锁？

MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁

表级锁：开销小，加锁快，不会出现死锁。锁粒度大，发生锁冲突概率高，并发量低。

行级锁：开销大，加锁慢，会出现死锁。锁粒度小，发生锁冲突概率低，并发度高。

delete、drop、truncate区别

truncate和delete只删除数据，不删除表结构，drop删除表结构，并释放所占空间

删除数据速度：drop > truncate > delete

delete需事务管理，commit后才能生效

存储过程

存储过程是一组为了完成特定功能的sql语句集，经编译后存储在数据库中，用户通过指定存储过程名字并给定参数来调用它。

主从复制原理

https://www.cnblogs.com/alter888/p/11131883.html

主库开启binlog功能并授权从库连接主库，从库通过change master得到主库的相关同步信息然后连接主库进行验证，主库IO线程根据从库slave线程的请求，从master info开始记录的位置向下开始取信息，同时把取到的位置点和最新的位置与binlog信息一同发给从库IO线程，从库将相关的sql语句存放在relay-log里面，最终从库的sql线程将relay-log里的sql语句应用到从库上，至此，整个同步过程完成。然后无限重复上述过程。

主：开启binlog功能，binlog线程记录了所有改变数据库数据的语句，放进master上的binlog中；

从：io线程，负责从master上拉取binlog内容，放进自己的relay-log中；

从：sql执行线程，执行relay-log中的语句；

方式

同步复制：master变化，必须等待slave完成后，才进行返回，不可取

异步复制：master只完成自己的操作即可，至于slave是否收到二进制文件，是否完成操作，不关心，也是mysql的默认设置

半同步复制：master只保证slave中的一个操作成功，就返回，其他slave不管。google为mysql引入的

 

master负责写操作，slave不应该进行写操作（通知不到其他数据库，不能保证数据一致性）

slave的存在实现了数据备份，保证了数据库高可用。一旦master宕机，将会选举新的slave为master；也可以分担负载，将读操作分散到slave上

读写分离的架构，应用程序不应该指定master的地址（master可能宕机被更换），需要找一个代理或插件，帮我们做；还有slave也需要做负载。

 

https://www.jianshu.com/p/70d63d5ff1cc

主库会在事务提交时，将数据库变更作为事件，记录在二进制bin log文件中

主库推送bin log的事件到从库的中继日志relay log中，之后，从库根据relay log日志做数据变更操作（读取最后一次更新到位置）

https://mp.csdn.net/postedit/82740881

 

undo log / redo log

为了实现事务的原子性，要不全部commit，要不全部rollback。一旦事务完成，所有修改必须记录下，保证一致性。

undo log：记录修改前的值（数据回滚使用，每次数据提交前，都需要将数据写入到undo log文件中）

redo log（重做日志）：数据实时写入磁盘，当系统奔溃时，可依据redo log进行数据重做（记录修改后的值）

bin log：事务提交时，一次性将事务中的sql语句写入文件；可用于本机恢复和主从同步

relay log：定时请求主节点bin log，拷贝到relay log，重放

Mysql高并发环境解决方案

水平分库分表，由单点分布到多点数据库中，从而降低单点数据库压力（mycat，shardjdbc）

集群方案：解决DB宕机带来的单点DB不能访问问题

读写分离

数据库崩溃时事务的恢复机制

undo log是为了实现事务的原子性，undo log先于数据持久化到磁盘，这样在数据出现问题后可以进行回滚

每个事务在提交前，都需要将数据和undo log写入磁盘

redo log是新数据的备份。在事务提交前，只要将Redo log持久化即可，不需要将数据持久化。当系统奔溃时，虽然数据没有持久化，但是Redo log已经持久化，系统可以根据Redo log的内容，将所有数据恢复到最新状态。

innodb引擎下的日志

错误日志，查询日志，慢查询日志，二进制日志（bin-log，记录对数据库更改的所有操作），事务日志

事务是如何通过日志来实现的？

事务日志是通过redo和innoDB的引擎日志缓存（innoDB log buffer）来实现的

当开始一个事务的时候，会记录该事务的日志序列号（log sequence number）；

当事务执行时，会往innoDB存储引擎的日志的日志缓冲里面插入事务日志；

当事务提交时，必须将存储引擎的日志缓冲写入磁盘；

也就是，写数据前先写入日志

行锁

InnoDB是基于索引来完成行锁的。

select * from table where id=1 for update；

for update 可以根据条件来完成行锁定，如果条件不是索引键的列，那么InnoDB将完成表锁。

乐观锁和悲观锁

悲观锁：总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加锁（读锁、写锁、行锁等），当其他线程想要访问数据时，都需要阻塞挂起。可以依靠数据库实现，如行锁、读锁和写锁等，都是在操作之前加锁，在Java中，synchronized的思想也是悲观锁。

乐观锁：每次去取数据的时候总认为不会有其他线程对数据进行修改，因此不会上锁，但是在更新时会判断其他线程在这之前有没有对数据进行修改，一般会使用版本号机制

乐观锁适用于多读的应用类型，这样可以提高吞吐量

version方式

 

一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。

核心SQL代码：

update table set x=x+1, version=version+1 where id=#{id} and version=#{version};  

视图

视图是虚拟的表，具有和物理表相同的功能，对视图的修改会影响基本表。

临时表

临时表可以手动删除

临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表。

创建临时表与创建表语法相似，唯一之处是多了关键字：TEMPORARY

非关心型数据库与关系型数据库

关系型数据库：复杂查询（方便在多表之间做复杂查询），事务支持（对于安全性能要求高的数据访问得以实现）

非关系型数据库：基于键值对，无需sql解析（MongoDB具有复杂查询功能，redis set nx属于原子操作，可以对事务支持）

内连、外连、交叉连、笛卡尔积

内连接：只匹配连接的行

左外连接：包含左边表的全部行，以及右表中的全部匹配行

右外连接：包含右边表的全部行，以及左表中的全部匹配行

交叉连接（Corss Join）：生成笛卡尔积，它不使用任何匹配或者选取条件，而是将一个数据源的每一行与另一个数据源的每一行都匹配。

不带WHERE条件子句，它将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积（例如：T_student和T_class，返回4*4=16条记录），如果带where，返回或显示的是匹配的行数。

有where子句，往往会先生成两个表行数乘积的数据表，然后才根据where条件从中选择。

全连接：返回左表和右表中的所有行，当某行在另一表中没有匹配行，则另一表中的列返回空值。

https://blog.csdn.net/weixin_39241397/article/details/79772379

count(*)、count(1)、count(column)

count(*)、count(1)对行的数目进行计算,包含NULL

count(column)对特定的列的值具有的行数进行计算,不包含NULL值。

Hash索引和B+树索引的特点

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一步到位

B+树索引需要从根节点到枝节点，最后才能访问到叶节点，这样多次访问。

为什么使用B+树索引，而不使用Hash索引？

hash索引，仅仅能满足=、in查询，不能使用范围查询。因为经过hash算法后的hash值大小关系，并不能保证和hash算法前一致。因此，也不能用来做排序，组合索引等

sql优化

通过druid或者开启数据库慢查询，找出查询慢点sql语句，然后通过explain sql进行分析，就可以看到具体表，是否使用了索引，那个索引被使用到了，表之间的连接，关联查询到结果集数量等。如果数据量过大，默认给出时间范围进行查询（在有排序的情况下，会导致全表扫描）