MySQL面试
# MySQL面试
# 一、什么是脏读，幻读，不可重复读？如何处理？
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索引字段就会失效。
# 十、范围查询如何进行的？不走索引如何进行查询？
1.假设有一个以a为字段的索引树,执行select * from t1 where a>6 会先走索引树进行查询找到a=6数据节点位置。然后再把a=6节点后面的结果(通过相邻叶子节点指针)返回即可。
2.若执行select * from t1 where b=5 则会进行全表查询，从数据节点(叶子节点)从小到大遍历。
# 十一、范围索引失效原理？
建立b,c,d字段联合索引，create index idx_t1_bcd on t1(b,c,d)；然后执行select * from t1 where b>1，实际上有两种查找方式：
①是联合索引找到b=1d的数据节点位置，然后读出该节点后面的所有节点的主键，每个节点再去主键索引树进行一次回表查询。
②直接对主键索引树的叶子节点进行全表扫描。
MySQL会根据这两种方式查询效率进行比较和优化，如果是b>1可能是②方式查找效率高一些，而如果是b>100或者是select a,b,c,d(覆盖索引)可能就是①快一些。
# 十二、order by为什么会导致索引失效？
执行select * from t1 order by b,c,d有两种方式：
①主键叶子节点全表扫描，读入磁盘后再排序。
②b,c,d索引树的叶子节点顺序就是所要排序的顺序，每个节点按照叶子节点顺序依次读出主键值，再回表查询。
# 十三、InnoDB和MyISAM的区别是什么？
1.InnoDB支持事务，而MyISAM不支持事务。InnoDB最小锁粒度是行锁，而MyISAM最小锁粒度是表锁。
2.InnoDB是聚簇索引，数据文件和索引文件都存储在ibd文件。而MyISAM是非聚簇索引，数据文件和索引文件分开存储。
3.InnoDB不保存表的具体行数，select count(*) from table是全表查询。MyISAM用一个变量保存整个表的行数。
# 十四、MySQL索引类型？什么情况下索引失效？
- MySQL索引类型
1.普通索引
2.唯一索引unique，该索引列下的值必须唯一，但允许有空值，一个表可以有多个唯一索引
3.主键索引primary key，一个表只能有一个主键索引，索引列下值必须唯一且不允许为空
4.联合索引
5.全文索引
- 索引失效
1.不符合最左前缀原则
2.走索引没有走全库查询效率快。
# 十五、介绍redo log和undo log？
Inno DB有这么两个日志，redo log和undo log。其中redo log是用于日志回放，把已经commit的事务重新做一遍。而undo log用于回滚数据，主要用于记录数据被修改前的日志，当事务回滚时需要按照undo log来进行数据复原。
事务原子性：在进行数据修改前先把历史数据保存到undo log，当事务成功commit时，通过redo log保证修改的数据全部同步到数据库中；当需要回滚时就通过undo log。
# 十六、Inno DB如何解决判断读未提交？
首先每行数据会有一个版本号id，Inno DB为每一个事务创造了一个动态数组，用来保存当前事务启动瞬间活跃的的所有事务id,然后事务每次修改完数据，就会把修改的事务id赋值给该行记录的版本号id。所以事务A读某行数据时，发现它的版本id在当前活跃事务数组中，则说明该行数据修改未提交。
# 十七、Inno DB可重复读会不会产生幻读？
RR隔离级别时普通的查询是快照读，因此快照读能解决读操作产生的幻读。而因为更新操作直接使用的是当前读的最新的数据(不管事务是否提交)，因此更新操作可能会产生幻读。
解决方案：使用for update添加间隙锁。
# 十八、MySQL如果对记录修改，是否会读到修改的值？
会。如果判断数据未改变，则不会真正执行更新操作。
# 十九、MySQL聚合函数和group by？count(字段)和count(*)区别？SQL语句执行顺序？
1.先看一个例子，找出每个部门中分数最高的同学并按照姓名升序返回表：select *,max(score) as max from student group by depy order by name desc;
按照group by A根据A列key分组后，对于每个组中所有行的数据根据不同聚合函数进行处理并返回一条记录。(max，min，avg，count)
2.count(字段)统计的是字段值不为NULL的记录数。而count(*)统计行数。
3.select 字段名(一般需要和group by字段一致) from 表名(使用join) where 条件名 group by 分组字段 having 分组条件 order by 排序字段 limit 分页
①where是用来过滤行数据，在分组前过滤；而having用来过滤分组，在分组后过滤。
②distinct对字段的value去重
# 二十、MySQL中CHAR和VARCHAR区别？删除语句？
1.CHAR是定长字符串类型；而VARCHAR是存储可变长类型。
2.drop用来删库删表。delete用来删除某行数据 delete from stu where id=10
# 二十一、MySQL的Hash索引？
哈希索引优点在于索引效率高，可以一次定位。但是仅仅只能够满足"="的等值查询，不能满足范围查询。