面试之MySQL看这个就够了

说说自己对于 MySQL 常见的两种存储引擎：MyISAM与 InnoDB的理解

关于二者的对比与总结:

1. count运算上的区别：因为MyISAM有缓存表meta-data（行数等），因此在做COUNT(*)时对于一个结构很好的查询是不需要消耗多少资源的。而对于InnoDB来说，则没有这种缓存。

2. 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务 (commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

3. 是否支持外键： MyISAM不支持，而InnoDB支持。 MyISAM更适合读密集的表，而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下，经常选择MyISAM作为主库的存储引擎。 一般来说，如果需要事务支持，并且有较高的并发读取频率(MyISAM的表锁的粒度太大，所以当该表写并发量较高时，要等待的查询就会很多了)，InnoDB是不错的选择。如果你的数据量很大（MyISAM支持压缩特性可以减少磁盘的空间占用），而且不需要支持事务时，MyISAM是好的选择。 

即

MyISAM：有缓存表、查询快、不提供事务支持、不支持外键、适合读密集的表

InnoDB：无缓存表、查询较慢、提供事务支持、支持外键、适合写密集的表

数据库索引

1.为什么要使用索引？

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
• 可以大大加快数据的检索速度（主要原因）
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO
• 可以加速表和表之间的连接，在实现数据的 参考完整性方面特别有意义

2.为什么不对表中的每一个列创建一个索引呢？

• 当对表中的数据进行增删改时，索引也要动态的维护，这样会减低数据的维护速度
• 索引需要占据物理空间，都建立的话占据空间大
• 创建索引和维护索引耗费时间，且随数据量的增加而增加

3.索引是如何提高查询速度的？

• 将无序的数据变为有序的数据，二分查找对数据建立索引

4.使用索引的注意事项？

• 对经常使用的列创建索引
• 在经常使用WHERE子句中的列上创建索引，加快条件判断速度
• 在经常需要排序的列上创建索引，利用索引的排序加快排序查询速度
• 特大型表不适合建索引
• 在经常用在连接的列上（主要是外键），可以加快连接的速度
• 避免where子句对字段施加函数，会无法命中索引
• 使用InnoDB时要使用与业务无关的自增主键作为主键，即使用逻辑主键，而不是业务主键
• 打算加索引的列设为NOT NUll，否则将导致引擎放弃索引进行全表扫描
• 删除长期未用的索引
• 使用limit offset 查询缓慢时，可借助索引提高性能

 

5.MySQL主要使用的两种数据结构

• 哈希索引：底层数据结构是哈希表，适用于绝大多数需求为单条记录查询
• BTree索引：使用的是B树中的B+树，但两种存储引擎MyISAM和InnoDB的实现方式是不同的

 

6.MyISAM和InnoDB实现BTree索引方式的区别

• MyISAM:B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜素算法搜素素引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址读取相应的数据记录。这被称为“非聚簇素引”。
• InnoDB：其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data城域保存了完整的数据记录。这个索引的key是数据表的主键.因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

 

7.覆盖索引介绍

• 什么是覆盖索引：包涵所有需要查询的字段的值，不用回表
• 覆盖索引使用实例：现在我创建了索引（username.age），在查询数据的时候：select username，age from user where username='Java'and age=22.要查询出的列在叶子节点都存在！所以，就不用回表。

 

8.选择索引和编写利用这些索引的查询的3个原则

• 单行访问是很慢的，最好读取的块中包含尽可能多的所需要的行
• 按顺序访问是很快的
• 覆盖索引是很快的

9.最左前缀原则

• MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是 (name,city)o而左前原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以 被用到。

 

10.当MySQL单表记录数过大时，数据库的CRUD性能会明显下 降，一些常见的优化措施如下

•  限定数据的范围： 务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时 候，我们可以控制在一个月的范围内
•  读/写分离： 经典的数据库拆分方案，主库负责写，从库负责读
• 垂直分区： 根据数据库里面数据表的相关性进行拆分。 例，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分， 把一张列比较多的表拆分为多张表垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂
• 4. 水平分区： 保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分，表的行数超过200万 行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多 个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。水平拆分可以支持非常大的数据量。需要注意的一点是:分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。水平拆分能够支持非常大的数据量存储，应用端改造也少，但分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂 度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择 客户端分片架构，这样可以减少一次和中间件的网络I/O。

数据库分片的两种常见方案：

客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 ShardingJDBC 、阿里的TDDL是两种比较常用的实现。

中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

 

11.事物隔离级别

什么是事物？

事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就 是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩 溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。 

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

12.并发事务带来的问题 

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操 作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这 时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个 事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事 务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1， 终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务 也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的 数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发 事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就 好像发生了幻觉一样，所以称为幻读。

不可重复读的重点是修改，幻读的重点在于新增或者删除。

 

12.事务隔离级别 

SQL 标准定义了四个隔离级别：

• READ-UNCOMMITTED(读取未提交)： 低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻 读或不可重复读
• READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读 仍有可能发生
• REPEATABLE-READ（可重复读）: 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修 改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
• SERIALIZABLE(可串行化): 高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务 之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

一个事务对数据库进行操作，这种操作的范围是数据库的全部行，然后第二个事务也在对这个数据库操作，这种操作 可以是插入一行记录或删除一行记录，那么第一个是事务就会觉得自己出现了幻觉，怎么还有没有处理的记录呢? 或 者 怎么多处理了一行记录呢? 幻读和不可重复读有些相似之处 ，但是不可重复读的重点是修改，幻读的重点在于新增或者删除。