MySQL面试学习

MySQL

1.事务

事务的4大特性

事务4大特性：原子性、一致性、隔离性、持久性

1. 原⼦性： 事务是最⼩的执⾏单位，不允许分割。事务的原⼦性确保动作要么全部完成，要么全不执行
2. 一致性： 执⾏事务前后，数据保持⼀致，多个事务对同⼀个数据读取的结果是相同的；
3. 隔离性： 并发访问数据库时，⼀个⽤户的事务不被其他事务所⼲扰，各并发事务之间数据库是独⽴的；
4. 持久性： ⼀个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发⽣故障也不应该对其有任何影响。

实现保证：MySQL的存储引擎InnoDB使用重做日志保证一致性与持久性，回滚日志保证原子性，使用各种锁来保证隔离性。

2.事务隔离级别

1. 读未提交：最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. 读已提交：允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
3. 可重复读：同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改，可以阻⽌脏读和不可重复读，会有幻读。
4. 串行化：最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能产⽣⼲扰。

隔离级别	并发问题问题
读未提交	可能会导致脏读、幻读或不可重复读
读已提交	可能会导致幻读或不可重复读
可重复读	可能会导致幻读
可串行化	不会产生干扰

3.默认隔离级别-RR

MySQL默认隔离级别：可重复读；

同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本身事务⾃⼰所修改；

可能出现幻读，解决方案：

1. 把隔离级别设置成SERIALIZABLE；这样所有事务都只能顺序执行，自然不会因为并发有什么影响了，但是性能会下降许多
2. 使用MVCC解决快照读幻读问题（如简单select），读取的不是最新的数据。维护一个字段作为version，这样可以控制到每次只能有一个人更新一个版本。
3. 如果需要读最新的数据，可以通过GapLock+Next-KeyLock可以解决当前读幻读问题。

4.行锁，表锁，意向锁

InnoDB⽀持⾏级锁(row-level locking)和表级锁,默认为⾏级锁

InnoDB按照不同的分类的锁：

1. 共享/排它锁(Shared and Exclusive Locks)：行级别锁
2. 意向锁(Intention Locks)，表级别锁
3. 间隙锁(Gap Locks)，锁定一个区间
4. 记录锁(Record Locks)，锁定一个行记录

表级锁（串行化）:Mysql中锁定 粒度最大的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。

行级锁（RR、RC）:Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种：

• 记录锁（Record Lock）: 对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；
• 间隙锁（Gap Lock）: 对索引项之间的“间隙”加锁，锁定记录的范围，不包含索引项本身，其他事务不能在锁范围内插入数据。
• Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。

5.索引

Innodb和Myisam引擎

Myisam：支持表锁，适合读密集的场景，不支持外键，不支持事务，索引与数据在不同的文件

Innodb：支持行、表锁，默认为行锁，适合并发场景，支持外键，支持事务，索引与数据同一文件

哈希索引

哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

B+树索引

优点：

1. B+树的磁盘读写代价低，更少的查询次数，查询效率更加稳定，有利于对数据库的扫描
2. B+树是B树的升级版，B+树只有叶节点存放数据，其余节点用来索引。索引节点可以全部加入内存，增加查询效率，叶子节点可以做双向链表，从而提高范围查找的效率，增加的索引的范围。
3. 在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树与B+树可以有多个子女，从几十到上千，可以降低树的高度。
4. 磁盘预读原理：将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。

6. 聚簇索引和非聚簇索引

聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（主键索引）

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（辅助索引）

聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

7.数据库范式

第一范式（1NF）列不可分割

第二范式（2NF）属性完全依赖于主键 [ 消除部分子函数依赖 ]

第三范式（3NF）属性不依赖于其它非主属性 [ 消除传递依赖 ]

8.JOIN 查询

left join(左联接) 返回包括左表中的所有记录和右表中关联字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中关联字段相等的记录
inner join(等值连接) 只返回两个表中关联字段相等的行

优化

Explain

索引优化

1. 最左前缀索引：like只用于’string%'，语句中的=和in会动态调整顺序
2. 唯一索引：唯一键区分度在0.1以上
3. 无法使用索引：!= 、is null 、 or、>< 、（5.7以后根据数量自动判定）in 、not in
4. 联合索引：避免select * ，查询列使用覆盖索引

语句优化：

1. har固定长度查询效率高，varchar第一个字节记录数据长度
2. 应该针对Explain中Rows增加索引
3. group/order by字段均会涉及索引
4. Limit中分页查询会随着start值增大而变缓慢，通过子查询+表连接解决
5. count会进行全表扫描，如果估算可以使用explain
6. delete删除表时会增加大量undo和redo日志， 确定删除可使用trancate

表结构优化

1. 单库不超过200张表
2. 单表不超过500w数据
3. 单表不超过40列
4. 单表索引不超过5个

集群

1.主从复制过程

MySQl主从复制：

• 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
• 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
• 优点：
  • 作为备用数据库，并且不影响业务
  • 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

2.数据一致性问题

“主从复制有延时”，这个延时期间读取从库，可能读到不一致的数据。

**缓存记录写key法：**在cache里记录哪些记录发生过的写请求，来路由读主库还是读从库

**异步复制：**在异步复制中，主库执行完操作后，写入binlog日志后，就返回客户端，这一动作就结束了，并不会验证从库有没有收到，完不完整，所以这样可能会造成数据的不一致。

**半同步复制：**当主库每提交一个事务后，不会立即返回，而是等待其中一个从库接收到Binlog并成功写入Relay-log中才返回客户端，通过一份在主库的Binlog，另一份在其中一个从库的Relay-log，可以保证了数据的安全性和一致性。

**全同步复制：**指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。