MySQL数据库

MySQL数据库

一、MySQL有几种数据引擎？有什么区别？

1.InnoDB:每个表只有一个idb文件(索引和数据放在一起)；支持行锁；支持savePoints部分回滚机制。

二、什么是脏读，幻读，不可重复读？如何处理？

1.脏读：事务读取数据读取到其它事务未提交的数据(更新)。

不可重复读：在一个事务过程中多次查询结果不一致(读到其他事务已经提交的数据)。

幻读：在一个事务过程中多次查询的记录数不一致(重点在于insert和delete)。

问题的根本原因在于MySQL事务的并发性所引起的。

2.解决方式：加锁，事务隔离，MVCC。

加锁：①脏读：类似于读者写者问题，对数据库的操作同时只能有一个事务写多个事务读。在修改时增加一个排他锁，在读取时增加一个共享锁，多个事务只要是读操作就可以获得共享锁。

三、事务基本特性和隔离级别？

1.多个数据操作组成一个完整的事务单元，事务内的所有操作要么同时成功要么同时失败。事务特性包括ACID：

原子性atomicity：事务是不可分割的，要么同时成功要么完全失败。

一致性consistenct：事务内的操作保持一致性，如果中间有操作失败，则要对前面的操作进行回滚。

隔离性isolation：事务在进行数据操作时，要进行事务隔离，防止多个事务之间相互干扰。

持久性durability：事务一旦提交，对应数据状态就会永久保存到数据库中。即使系统故障也不会丢失。

2.事务隔离级别：set transaction level xx设置下次事务隔离级别。MySQL当中有五种隔离级别：

NONE:不使用事务。

READ UNCOMMITED:允许脏读。

READ COMMITED:防止脏读，最常用隔离级别。

REPEATABLE READ:防止脏读和不可重复读。MySQL默认。

SERIALIZABLE：事务串行，可以防止脏读，幻读，不可重复读。

隔离级别越高，事务安全性越高，但是事务性能越低。一般保证数据库的并发性，让它的性能更多的提高，而把事务安全问题移到应用程序中来进行控制。

四、MySQL的锁有哪些？什么是间隙锁？

1.按照粒度可分为如下：

①行锁：粒度小，但是加锁资源消耗开销大，InnoDB支持。又分为共享锁和排他锁(InnoDB会自动对增删改加排他锁，查询也可以手动添加select * where FOR UPDATE)，自增锁(用来维护自增序列)。

②表锁：粒度大，开销小。

③全局锁：粒度大，加锁后数据库处于只读状态，只允许读操作，所有修改操作都会被挂起。一般用于全库备份时候。

2.InnoDB常见锁算法：

①比方说我有一个user表，其中的key userid有三个值(1,4,9)。如果事务进行update set xx where userid=4,那么就会执行记录锁(具体执行操作某一条记录时锁住该记录,且记录存在)，用于唯一索引。

②间隙锁：RR隔离级别下用于锁住某个开区间，比如要select xx where xx BETWEEN A and B，目的是防止其他事务在间隔中插入数据，产生幻读。一般用于①非唯一索引②唯一索引等值查询记录不存在③范围查询。

③next key临键锁:对非唯一索引进行更新for update等操作时，会获得该条记录所在的左开右闭区间的锁。当希望禁用间隙锁提高系统性能，可以将隔离级别降为RC使用NEXT key。仅仅针对于非唯一索引。

五、MySQL的索引结构是怎么样的？为什么用B+树不用B树？什么是主索引和辅助索引，聚簇索引和非聚簇索？什么是索引覆盖？非聚簇索引一定会导致回表吗？

1.二叉树->AVL树->红黑树->B树->B+树

二叉树的问题在于：当树结构非常不平衡(所有节点只有右孩子)时，查询效率会非常低。

AVL树，树中所有节点的左孩子和右孩子深度之差小于等于1。AVL树相比于二叉树，查找效率最差也是O(logn)。

红黑树，特点是树根为黑；不能有连续红节点相连接；每个节点到所有叶子节点的黑高都相等；所有叶子节点都是黑。相比于AVL树，插入删除效率红黑树更好一些(因为AVL树要更多的旋转次数来保证其特性，而红黑树相当于不是那么严苛的AVL树，算是一种折中方案)而查询效率是AVL树更好一些(比如对于这样一个红黑树，左子树只有五个直接相连的黑节点，而右子树我可以让五个黑节点当中每两个中间就插入一个红节点，这样树左右子树节点个数就非常不平衡，导致查询时间增加)。

n阶B树，非叶子节点中的关键字个数至少要等于⌈n/2⌉-1，最多等于n-1；而每个节点的子节点数目等于该节点中关键字个数加一。所有叶子节点都是在同一层。相比于红黑树，有两个优点：一根据局部性原理，B树一个节点最多可以获得n-1个关键字的信息；二树高相比于红黑树更小，效率更高。

n阶B+树，根节点至少有两个关键字，非叶子节点至少⌈n/2⌉，最多n个；每个关键字作为子节点中最小的值；非叶子节点只进行索引，所有数据都存在叶子节点中。每个叶子节点含有相邻叶子节点的指针，叶子节点按照关键字从小到大顺序排列。B+树有两种有两种遍历方式，从树根遍历到叶子或者是数据从小到大。

2.MySQL选择B+树而不选择B树的原因在于，它B+树扫库只需要扫一遍叶子节点即可，并且因为它每个叶子节点都含有相邻叶子节点的指针，因此比较适合范围查询和区间查询。而对于B树遍历效率比较低。

3.主索引(索引覆盖)是指按照该表的主键key建立索引；而辅助索引是指按照表中非主键key进行索引。辅助索引一般要搜索两边索引，第一次查复辅助索引获得主键，然后再在主索引中查找该主键。

非聚簇索引指(MyISAM)叶子节点存放的是该条记录在数据文件中的物理地址；聚簇索引指(InnoDB)叶子节点存放的是整行记录。InnoDB中如果表设置了主键，那么主索引就是聚簇索引，如果没有主键就选择一个非空unique作为聚簇索引。否则会创建一个隐藏的row-id作为聚簇索引。

4.索引覆盖是指把非主键索引改为和包含主键多字段的联合索引，这样就可以在一个索引树上直接找到数据，而不需要再去主索引进行二次查找。

5.如果SQL语句全部命中，则当前索引已经包含所要查询信息，就不需要进行回表。比如说id是主键，对于select age from xx where age>20就不需要回表查询。

六、什么是MySQL主从集群？主从不一致如何解决？什么是半同步复制？

1.首先主节点先把MySQL更新操作写到binlog中，然后把binlog同步发送给从节点，并把内容写入到relaylog中，再创建一个线程把更新内容同步到从节点。

写操作只能在主库进行，而读操作可以在主库或者从库进行，从库可以帮主库分担读请求，进行负载均衡，提升读性能。这就是MySQL的主从复制，读写分离。

2.主库把数据同步到从库中还没有完成时，这时候有一个读请求就会导致主从不一致。解决方案如下：

①业务强制接受，因为毕竟一般的业务都是读多写少。

②强制性读主库，但其实这样的话从库的利用率就大大降低，仅仅只是在主库挂时，作为一个备份。

③可以设置一个延时，写操作一定时间内读只能读主库，之后才可以读主或者从。

3.半同步复制指的是主节点等待至少一个从节点接受并记录事件后，才完成本事务的提交。不需要向同步复制一样等待所有从节点的确认。可以保证如果主节点崩溃，那么至少已经发送给了一个从节点。

七、什么是水平分片和垂直分片？分库分表后SQL如何执行？

1.当表中的数据量比较大时，需要对数据进行分片。

垂直分片是指根据业务场景需求把一个库中的多张表拆分到不同的库中，或者是根据数据相关性把一张表中的不同列拆分到不同表中。

水平分片是指把一张表按照行拆分到不同表中，可以从根本上解决表中数据量过大的问题。策略如按取模，时间，范围进行行分片。阿里建议一个表中的数据超过500w或者数据文件超过2G就需要分库分表。

2.SQL语句解析->SQL路由->SQL语句改写->结果归并。

八、什么是MySQL的左连接，右连接，内连接，外连接，交叉连接？

左连接left join：获得左表中匹配的记录。

右连接right join：获得右表中的匹配记录。

内连接inner join：获得两张表中拼接起来的匹配的记录。

外连接outer join：获得两张表中匹配的记录，以及某张表中不匹配的记录。

交叉连接：两个表中记录两两进行笛卡尔积。

九、什么是数据库死锁？

有两个事务并发执行时，事务A先锁住了行a，接下来要对行b进行修改；事务B锁住了行b，要对行a进行修改。这时候就会因为请求保持而导致死锁。

解决：通过设置一个定时器，过一段时间发起死锁检测，如果发现有事务超时则进行事务回滚，让其他事务继续执行。

十、最左匹配原则？

最左匹配原则是针对联合索引来说的。
最左匹配原则是指按照最左边的索引key来构建B+树。比如按照(a,b)构建联合索引，它的B+树如下：

可以看到叶子节点数据时按照最左边索引a进行排列的，只有当a的值相等时此时的最左索引就会变成b，往后找新的索引。因此a=2时，b是有序的。
遇到最左索引的范围查询时，后面的索引就会失效，停止匹配。比如我按照(a,b,c)构建联合索引，那么查询条件是a=1 and b>2 and c=8时，首先如果a=1有多个匹配数据，此时最左索引就会变成b,而此时b进行的是范围查询，因此后面c索引字段就会失效。

---

---

今日总结

学习