MySQL 笔记

这是一篇笔者使用 MySQL 后，重新了解 MySQL 的要点记录。

致谢

把这篇文章献给JavaGuide，正是在《Java面试突击》这本书的带领下，我早早的开始了春招实习的准备工作。

虽然三个月过去了，我仍然没有拿到 Offer ，但是还是感谢他。

正文

MySQL 引擎

InnoDB

  • InnoDB是一个事务型(A原子C一致I隔离D持久)的存储引擎，有行级锁定和外键约束。

  • InnoDB不支持FULLTEXT类型的索引。

  • InnoDB没有保存表的行数，当SELECT COUNT(*) FROM TABLE时需要扫描全表。

  • 支持自动增加列属性auto_increment。

  • 支持故障恢复。

MyISAM

  • MyISAM拥有较高的插入、查询速度，但不支持事务、不支持外键约束。

  • 设计之时就考虑到 数据库被查询的次数要远大于更新的次数。

  • ISAM(索引顺序访问方法)执行读取操作的速度很快，而且不占用大量的内存和存储资源。

  • 不支持主动恢复。

引擎对比

事务、外键支持

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

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

表锁差异

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

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

存储结构

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

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

数据库的事务

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

ACID特性

A 原子性，即数据库在执行事务的过程中，所有操作都应该作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

C 一致性，即数据库在执行事务后，不应该破坏数据库的完整性约束。（非空、唯一、主键、外键约束）

I 隔离性，即数据库多个事务并发执行时，事务之间应该互不影响。

D 持久性，即一个事务一旦被提交，他对数据库的修改应该永久保存到磁盘上。

在隔离性中，数据库具有隔离级别的说法。

事务隔离级别

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树需要更多的磁盘读写消耗。

在内存数据未被命中时，CPU会将目标数据从磁盘读取到内存中，而磁盘需要寻道、旋转、传输等大量的时间资源消耗。

许可协议

本作品采用知识共享署名 4.0 国际许可协议(CC BY-NC-SA 4.0)进行许可，转载时请注明原文链接，图片在使用时请保留全部内容，可适当缩放并在引用处附上图片所在的文章链接。

上一页Redis 相关下一页SNMP 相关

最后更新于