1. 说说自己对于 MySQL 常见的两种存储引擎:MyISAM与 InnoDB的理解
关于二者的对比与总结:
- count运算上的区别: 因为MyISAM缓存有表meta-data(行数等),因此在做COUNT(*)时对于一个结构很好
的查询是不需要消耗多少资源的。而对于InnoDB来说,则没有这种缓存。 - 是否支持事务和崩溃后的安全恢复: MyISAM 强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型
更快,但是不提供事务支持。但是InnoDB 提供事务支持事务,外部键等高级数据库功能。 具有事务
(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID
compliant))型表。 - 是否支持外键: MyISAM不支持,而InnoDB支持。
MyISAM更适合读密集的表,而InnoDB更适合写密集的的表。 在数据库做主从分离的情况下,经常选择MyISAM作为主库的存储引擎。 一般来说,如果需要事务支持,并且有较高的并发读取频率(MyISAM的表锁的粒度太大,所以当该表写并发量较高时,要等待的查询就会很多了),InnoDB是不错的选择。如果你的数据量很大(MyISAM支持压缩特性可以减少磁盘的空间占用),而且不需要支持事务时,MyISAM是最好的选择。
2. 为什么使用索引?
- 创建唯一性索引,可以保证表中每一行数据的唯一性。
- 大大加快数据的检索速度。
- 帮助服务器避免排序和临时表。
- 将随机IO变为顺序IO。
2.1 索引如何提高查询速度的?
将无序的数据变成相对有序的数据。
2.2. 索引这么多优点,为什么不对表中的每一个列创建一个索引呢?
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
- 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
3. 当MySQL单表记录数过大时,数据库的CRUD性能会明显下降,一些常见的优化措施如下:
-
限定数据的范围: 务必禁止不带任何限制数据范围条件的查询语句。比如:我们当用户在查询订单历史的时候,我们可以控制在一个月的范围内。;
-
读/写分离: 经典的数据库拆分方案,主库负责写,从库负责读;
-
垂直分区: 例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。
垂直拆分的优点:- 可以使得行数据变小,在查询时减少读取的Block数,减少I/O次数。
- 此外,垂直分区可以简化表的结构,易于维护。
垂直拆分的缺点:
- 主键会出现冗余,需要管理冗余列,并会引起Join操作,可以通过在应用层进行Join来解决。
- 此外,垂直分区会让事务变得更加复杂;
-
水平分区: 保持数据表结构不变,通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中,达到了分布式的目的。 水平拆分可以支撑非常大的数据量。 水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。
4. 什么是事务?
事务是逻辑上的一组操作,要么都执行,要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元,这个转账会涉及到两个关键操作就是:将小明的余额减少1000元,将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃,导致小明余额减少而小红的余额没有增加,这样就不对了。事务就是保证这两个关键操作要么都成功,要么都要失败。
4.1 事务的特性
- 原子性: 事务是最小的执行单位,不允许分割。事务的原子性确保动作要么全部完成,要么完全不起作用;
- 一致性: 执行事务前后,数据保持一致,多个事务对同一个数据读取的结果是相同的;
- 隔离性: 并发访问数据库时,一个用户的事务不被其他事务所干扰,各并发事务之间数据库是独立的;
- 持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
4.2 事务隔离级别
SQL 标准定义了四个隔离级别:
- READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读
- READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生
- REPEATABLE-READ(可重复读): 对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。
- SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读
InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ(可重读) 已经可以完全保证事务的隔离性要求,即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别,且并不会有任何性能损失。