MySQL数据库面试题

数据库设计

• 如何设计一个数据库？
  

索引

• 数据库为什么需要使用索引进行查询？

使用索引进行查询主要原因是可以，提高数据库的查询效率。
数据库存储方式是在硬盘中分区分块进行存储，如果不使用索引进行检索，则会引起全表扫描会把数据所在硬盘对应的块全部加载进内存中（查询的次数越多I/O越多，效率越低），再从内存中找到对应的数据。如果数据量很大查询效率会大大降低。

• 多叉树之 B+tree
  B+ 树就是为了拆分索引数据与业务数据的平衡多叉树。
  

MySQL数据库的索引使用的就是B+tree，B+ 树中，非叶子节点只保存索引数据，叶子节点保存索引数据与业务数据。这样即保证了叶子节点的简约干净，数据量大大减小，又保证了最终能查到对应的业务数。既提高了单次 I/O 数据的有效性，又减少了 I/O 次数。

如图：我们只需要把真实的业务数据，换成数据所在地址就可以了，此时，业务数据所在的地址在 B+ 树中充当业务数据。

• 索引类型
  主键索引
  唯一索引
  普通索引
  组合索引
  全文索引
  MySQL数据库Innodb引擎，默认使用主键作为索引。

• 数据库索引失效的场景？

1、如果查询条件用or，必须or条件中的每个列都加上索引，否则无效。

2、对于复合索引（又叫多列索引或者联合索引），如果查询的列不使用复合索引的第一部分，则不使用索引。例如，tb表的复合索引为（key1，key2，key3），则查询select * from tb where key2=1 and key2>5将不会使用索引。

3、如果like是以%开头的，则该列上的索引不会被使用。例如：select * from tb where key1 like “%a”; 即使key1列上建立了索引，该查询也不会使用索引。

4、存在索引列的数据类型隐形转换，则不使用索引。例如：列key1的数据类型为字符串，select * from tb where key1=1; 将不会使用索引。要想使用索引，必须将字符串用引号印起来。

5、where字句里对索引列有数学运算或者使用函数，则不使用索引。

原文链接：https://blog.csdn.net/Mary19920410/article/details/77606341

引擎

MySQL数据库在5.5版本之前默认使用MyISAM作为默认引擎，5.5版本之后使用Innodb作为默认引擎。

1. Innodb支持事务，MyISAM不支持
2. Innodb支持外键，MyISAM不支持
3. Innodb不支持全文索引，MyISAM支持全文索引，查询效率上MyISAM要更高
4. MyISAM引擎在系统崩溃后，恢复起来更困难
5. Innodb是聚焦索引，数据文件是和索引绑在一起的，而MyISAM是非聚焦索引，数据文件是分离的，索引保存的是数据文件的指针。
6. Innodb支持行锁，也支持表锁。 MyISAM只支持表锁。

数据库锁

• 按锁的粒度划分，可分为表级锁、行级锁、页锁（介于表锁和行锁之间）
• 按锁的级别划分，可分为共享锁、排它锁
• 按加锁方式划分，可分为自动锁（意象锁，MyISAM表锁，update，delete，insert时候上的锁）、显示锁
• 按操作划分，可分为DML锁（对数据操作时的锁）、DDL锁（对表结构变更时的锁）

数据库的悲观锁和乐观锁？

悲观锁：数据库本身的锁，排它锁
乐观锁：不使用数据库提供的锁机制实现

1. 乐观锁实现在表中添加数据的版本号字段，操作之前查询出版本号，操作时根据该版本号的值作为条件进行操作（如果变更则操作失败）
2. 使用时间戳

数据库的排它锁和共享锁？

• MyISAM引擎下查询时 将会使用共享锁锁表，此时其他的事务只能进行查询操作，增改删操作将不被允许，直到查询操作执行完成。
• MyISAM引擎下增改删操作时 将会使用排他锁锁表，此时其他的事务将不被允许对表进行任何操作，直到该事务commit提交
• Innodb引擎，如果不使用索引的情况下是使用的表级锁
• Innodb引擎在使用索引查询时，则会使用共享锁，锁住所查询的行，被锁住的行只能被查询。如果是进行增改删操作，则会使用排它锁，锁住所操作的行，被锁住的行不允许进行其他操作。

数据库事务

什么是事务？

所谓事务是一个不可分割的工作单位，是一个操作序列，要么这些操作同时执行成功，要么同时失败。

事务的ACID？

• 原子性：整个数据库的事务是不可分割的工作单位，要么一起成功，要么同时失败回滚
• 一致性：至数据库事务不能破坏数据的完整性以及逻辑上的一致性，比如数据库中有A、B两人，A有资产1000元，B有资产500元此时A向B转账500元，虽然AB两人资产均发上变化，但是整体的资产总和并未改变。
• 隔离性：在并发环境下，当不同的事物操作相同的数据是，每个事务都有各自的完整数据空间，即事务之间是相互不进行干扰的。
• 持久性：事务成功结束后，他对数据库所做的更新必须永久保存下来。

数据库事务的隔离级别？

• 读未提交 Read Uncommitted
  一个事务读取到另外一个事务未提交的数据，主流数据库都在此级别之上。
  该级别情况下产生脏读，数据会存在风险。例如当A事务将数据1000更改为900，此时B事务读取到该数据之后进行了修改操作，将数据修改为1100直接提交了。就在A事务在进行提交时出现了错误，事务进行了回滚，那么此时的数据就存在风险。
• 读已提交 Read committed
  一个事务必须等到另外一个事务提交之后才能读取到数据
  该隔离级别下，解决了脏读，但是存在不可重复读和幻读问题，例如当A事务将数据1000修改为900时，B事务查询改数据值为1000，此时A事务提交数据后，B事务再次查询改数据是发现值更改为了900，两次查询的数据不一样 即为不可重复读
• 可重复度 Repeatable Read
  是MySQL数据库的默认隔离级别。
  幻读是指：B事务以范围查询除了4条数据，当B线程对这4条数据进行修改之前，A线程B线程查询范围之中添加了一条数据并提交。此时B事务以该范围进行修改数据，发现操作了5调数据，即为幻读。
  该隔离级别下，解决了脏读和不可重复读问题，就是就是保证了B线程在A线程操作提交数据前后所查询的结果是一致的，但是在该级别下理论上是没有解决幻读问题，但是MySQL的Innodb引擎中解决了幻读问题。
  在 RR 级别下，如果查询条件能使用上唯一索引，或者是一个唯一的查询条件，那么仅加行锁，如果是一个范围查询，那么就会给这个范围加上 gap 锁或者 next-key锁 (行锁+gap锁)也就是间隙锁。
• 可串行化
  1.该级别解决了脏读，不可重复读，幻读等所有问题。
  2.这个级别模拟串行的事务执行， 就好象事务将被一个接着一个那样串行的，而不是并行的执行。 不过，使用这个级别的应用必须准备在串行化失败的时候重新发动事务。
  3.这个级别会影响数据库的执行效率
• 幻读主要是指操作的数据条数发生了变化，前后查询不一致。
• 不可重复读主要是指操作的数据内容发生了变化，前后查询不一致。

分页查询

物理分页

• 就是再查数据库的时候，只差我需要的条数（提前分页）
• 适用场景：主要用于数据量大，数据更新频繁的场合

逻辑分页

• 就是把所有的数据从数据库中查出来，然后在进行分页
• 主要用于数据量不打，数据稳定的场合

对比

• 逻辑分页，一次性将所有数据读取到内存中，占用了较大的内存空间。如果数据量大时查询效率较慢。
• 物理分页每次制度去一部分数据，占用内存空间较小。

此文章皆为自己总结，如有错漏或者不准确的地方 希望各位大佬指出。