数据库

MySQL 引擎

InnoDB

  • InnoDB是一个事务型(A原子C一致I隔离D持久)的存储引擎，有行级锁定和外键约束。
  • InnoDB不支持FULLTEXT类型的索引。
  • InnoDB没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。
  • 支持自动增加列属性auto_increment。
  • 支持故障恢复。

MyISAM

  • MyISAM拥有较高的插入、查询速度，但不支持事务、不支持外键约束。
  • 设计之时就考虑到 数据库被查询的次数要远大于更新的次数。
  • ISAM(索引顺序访问方法)执行读取操作的速度很快，而且不占用大量的内存和存储资源。
  • 不支持主动恢复。

引擎对比

事务、外键支持

MyISAM不支持事务和外键，而InnoDB支持。

MyISAM是非事务安全型的，而InnoDB是事务安全型的，默认开启自动提交。

全文索引

MyISAM

支持(FULLTEXT类型的)全文索引。

InnoDB

不支持(FULLTEXT类型的)全文索引。

表锁差异

MyISAM

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

InnoDB

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

存储结构

MyISAM

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

InnoDB

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

事务隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

  • 脏读(Drity Read)
    • 某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
  • 不可重复读(Non-repeatable read)
    • 在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
  • 幻读(Phantom Read)
    • 在一个事务的两次查询中数据数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

索引

为什么使用索引？

  • 加快数据检索速度。
  • 避免排序耗时。
  • 将随机I/O变为顺序I/O。

为什么不全部使用索引？

  • 对表中内容进行修改的时候，索引也要动态维护。
  • 索引会占据一定的空间。

索引覆盖

如果一个索引包含所有需要查询的字段的值，称为覆盖索引。即只需扫描索引而无须回表。

回表即通过索引找到了对应项的主键，再通过主键访问数据。

最左前缀

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。

如User表的name和city加联合索引就是name - city

name=xx and city=xx; //命中索引

where name=xx; //命中索引

where city=xx; //未命中索引

冗余索引

根据最左前缀，重复命中的索引。name - city 和 name。

B树 / 平衡多路查找树

  • innoDB的主索引B+树叶子节点中包含的是数据，辅助索引子节点中包含的是主键。
  • innoDB引擎在查询辅助索引的时候会查询两次，首先通过辅助索引得到主键值，然后再查询主索引。
  • MyISAM的主索引B+树叶子节点中包含的是行号。MyISAM的辅助索引在结构上和主索引没有本质的区别。

为什么索引不使用红黑树实现？

如果用红黑树存储，增加或者减少数据时红黑树需要做旋转之类的操作来保持平衡，那么就需要把所有节点都加载到内存中，查找时也需要全部加载到内存，所以不适合。

B树与B+树的区别

  • B+树是在B树的基础上改造的，B+树的数据都在叶子节点上，同时，叶子节点之间还加了指针形成链表。
  • B+树在数据库的索引中用得比较多，如果需要查询多条数据，B树需要做局部的中序遍历，可能要跨层访问。而B+树由于所有数据都在叶子结点，不用跨层，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了。
  • 这也是为什么没有在数据库中用Hash的原因，查找一个数Hash更快，但是查找多个数字的时候就没有B+树快。

大表优化问题

垂直分区/列拆分

简化表结构/主键冗余。

水平分区/行拆分

数据表结构不变/分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库。