MySQL-面试

目录

1、MySQL 为什么使用 B+ 树来作索引，对比 B 树它的优点和缺点是什么？

2、数据库的事务隔离级别有哪些？各有哪些优缺点？

3、简述乐观锁以及悲观锁的区别以及使用场景

4、产生死锁的必要条件有哪些？如何解决死锁？

5、聚簇索引和非聚簇索引有什么区别？

6、MySQL中 InnoDB 和 MylSAM 的区别是什么？

7、数据库如何设计索引，如何优化查询？

1、MySQL 为什么使用 B+ 树来作索引，对比 B 树它的优点和缺点是什么？

参考文档链接地址

MySQL索引是为了提升数据库的查询性能，满足等值、范围、全表扫描等功能，尽可能多的存储更大数据集。

B+树，加强版的多路平衡查找树，

特点：

1)、节点中的关键字的数量和路数相等

2)、B+树的根节点和枝节点都不会存储数据，只有叶子节点才存储数据，非叶子节点中存储的关键字数量会更多，路数也会更多，树的高度会被降低，磁盘IO次数也会更少

3)、B+树的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的

B+树优势：

1)、B+树是B树的一个变种，B树能解决的问题，它都能解决，

2)、扫库、扫表能力更强，B+树只需要遍历叶子节点，B树则需要进行树的遍历。

3)、B+树的磁盘读写能力相对于B树来说更强，根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多

4)、排序能力更强，因为叶子节点上有下一个数据区的指针，数据形成了链表

5)、效率更加稳定。B+树永远是在叶子节点拿到数据，所以IO次数是稳定的

B树优势：

1)、查询临近根节点的数据，磁盘IO次数少，性能强

2、数据库的事务隔离级别有哪些？各有哪些优缺点？

参考地址1   参考地址2

因为事务的并发操作会带来脏读、不可重复读、幻读，为了解决这些问题数据库提供了多种隔离级别，可针对业务需求进行配置。

隔离级别：

1)、未提交读：

没有解决事务并发中的任何一个问题(脏读、不可重复读、幻读)

原理：不加锁。

2)、提交读：只解决了脏读问题，

原理： MVCC+锁(记录锁)

3)、可重复读：解决了脏读、不可重复读问题，在Innodb中间隙锁解决了幻读；

原理： MVCC+锁(记录锁、临键锁、间隙锁)

普通的select使用快照读(MVCC)，加锁的select(in shard mode/for update)以及更新操作update delete等语句使用当前读，底层使用锁(记录锁、临键锁、间隙锁)

4)、串行化：解决了脏读、不可重复读、幻读问题，

原理：所有的事务都串行执行，也就是对数据的操作需要排队，已经不存在事务的并发操作了，所以它解决了所有的问题。(为所有select语句都被隐式的转化为select ... in shard mode，会和update、delete互斥)

3、简述乐观锁以及悲观锁的区别以及使用场景

参考地址1 参考地址2
 

悲观锁：总是假设最坏的情况，每次拿数据的时候都会认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁(共享资源每次只给一个线程使用，其他线程阻塞，用完后再把资源转给其他线程)。

乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型，这样可以提高吞吐量

使用场景：

适用于写比较少的情况下(多读场景)，即冲突真的很少发生的时候，这样可以省去锁的开销，加大了系统的整个吞吐量；但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的retry，这样反倒是降低了性能，所以一般多写的场景下使用悲观锁比较合适。

乐观锁缺点：

1)、ABA问题

2)、循环时间长开销大

自旋CAS也就是不成功就一直循环直到成功，如果长时间不成功，会给CPU带来非常大的执行开销

3)、只能保证一个共享变量的原子操作

4、产生死锁的必要条件有哪些？如何解决死锁？

参考地址1     参考地址2     参考地址3

死锁产生的必要条件：

1)、互斥：共享资源X和Y只能被一个线程占用

2)、占有且等待：线程T1获得了共享资源X，在等待共享资源Y的时候，不释放共享资源X

3)、不可抢占：其他线程不能强行抢占线程T1占有的资源

4)、循环等待：线程T1等待线程T2占有的资源，线程T2等待线程T1占有的资源，就是循环等待

解决死锁：

按照前面说的四个死锁的发生条件，我们只需要破坏其中一个就可以避免死锁

其中互斥这个条件不能破坏，其他三个条件都有办法破坏，

1)、对于"占有且等待"这个条件，我们可以一次性申请所有的资源，这样就不存在等待了

2)、对于“不可抢占”这个条件，占有部分资源的线程进一步申请其他资源的时候，如果申请不到，可以主动释放它所占用的资源，这样不可抢占的条件就破坏了

3)、对于“循环等待”这个条件，可以靠按序申请资源来预防。所谓按序申请，是指资源是有线性顺序的，申请的时候可以先申请资源序号小的，再申请资源序号大的，这样线性化后自然就不存在循环了

5、聚簇索引和非聚簇索引有什么区别？

参考地址1    参考地址2

聚簇索引：将数据存储和索引放在一起，通常和主键同义，叶子节点存储真实的数据，并且和实际磁盘存储的顺序是一致的。

非聚簇索引：聚簇索引以外的索引称之为非聚簇索引，叶子节点存储聚簇索引的索引值

1)、一张表只能有一个聚簇索引，但是可以有多个非聚簇索引

2)、通常使用聚簇索引的查询更快，一定没有回表操作，使用非聚簇索引大概率会回表

3)、聚簇索引值必须不为NULL且不重复

6、MySQL中 InnoDB 和 MylSAM 的区别是什么？

参考地址1

1)、 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

2)、 存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。

InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3)、 可移植性、备份及恢复

MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。

4)、 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

5)、 AUTO_INCREMENT

MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

6)、 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

7、 全文索引

MyISAM：支持 FULLTEXT类型的全文索引

InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

8、 表主键

MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。

InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。

9、 表的具体行数

MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。

InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。

10、 CURD操作

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

11、 外键

MyISAM：不支持

InnoDB：支持

7、数据库如何设计索引，如何优化查询？

设计：

1)、在用于where判断、order排序和join的on、group by的字段上创建索引

2)、一张表的索引数量不要太多---浪费空间、更新变慢

3)、过长的字段建立前缀索引

4)、区分度的字段，例如性别，不要建立索引--离散度太多，导致扫描的行过多

5)、频繁更新的字段，不要作为主键或者索引 --页分裂

6)、随机无序的值不建议作为索引，例如身份证、UUID -- 无序、分裂

7)、联合索引把散列度高的值放在前面

优化查询：

基于开发成本的原则优化：

1、优化SQL及索引

使用explain工具分析SQL语句命中索引的情况，避免全表扫描、using filesort(不能使用索引排序，用到了额外的排序)、using temporary(用来临时表， distinct、group by、join等)。

2、基于主从复制实现读写分离，减少节点压力

3、增加分布式缓存

4、垂直分库 (按业务拆分库，订单库、商品库）

5、垂直拆分表

6、水平拆分表(单表的存储量很大并且业务数据增长也很快，进而需要做水平拆分)

7、换存储引擎(mongo、es)

8、提升服务器的硬件资源，比如增加cpu 内存 网络带宽等

9、mysql调优，增加bufferPool的大小，bufferPool是mysql内部实现的缓存，会将从磁盘中读取的数据存放到

bufferpool缓冲池中，这个值越大能缓存的数据就越大，提升性能。