[后台开发工程师总结系列] 6.数据库原理及MySQL

数据库系统原理

事务

事务指满足ACID特性的一组操作,可以通过commit 提交,也可以通过rollback回滚

事务的ACID特性

  1. 原子性(Atomicity)

    事务被视为不可分割的最小单元,事务的所有操作要么全部成功提交,要么全部失败回滚,回滚可以使用回滚日志来实现,回滚事务记录着执行的修改操作,回滚时反向执行即可

  2. 一致性(Consistency)

    数据在事务的执行前后都保持一致性状态,在一致性状态下,所有事务对一个数据的读取结构都是相同的

  3. 隔离性(Isolation)

    一个事务所做的修改在最终提交以前对其他事务不可见

  4. 持久性(Durability)

    一旦事务提交,则其所做修改会永远保存到数据库中,即使系统崩溃事务执行结果也不能丢失,使用重做日志来保障持久性

事务的ACID特性比较简单,但是并不容易理解,他们并不平级

  • 只有满足一致性,事务的执行结果才是正确的的
  • 在无并发的情况下,事务串行执行,隔离性一定能满足。此时只要满足原子性,一致性即可满足
  • 并发的情况下,多个事务并行执行,事务不仅满足原子性,还要满足隔离性 才能满足一致性
  • 事务满足持久性是为了维护数据库崩溃的情况

事务的自动提交 AUTO_COMMIT

MySQL默认采用自动的提交模式,即,如果不显示使用START TRANSACTION来开始一个事务,每个查询都会被当做一个事务自动提交

事务的模型抽象

成功完成的事务称为已提交,而非成功完成的事务被中止了,为了确保数据的原子性,中止事务对数据库状态不可以造成影响,因此这些影响必须被撤销。一旦中止事务的影响被撤销,我们称事务已回滚 ,数据库通过日志来支持回滚操作

一个简单事务抽象模型包括:

  • 活动的:初始状态,事务执行时处于这个状态
  • 部分提交的:最后一条语句执行后
  • 失败的:发现正常的执行不能继续后
  • 中止的:事务回滚并恢复到事务开始执行的前后
  • 提交的:成功完成后

事务的隔离性

事务处理系统通常允许多个事务并发的执行,并发有两条无法拒绝的理由

  • 提高吞吐量和资源利用率
  • 减少等待时间

数据库中并发的动机和多道程序设计的动机是相同的,而为提升效率的同时,我们必须控制事务的交互,来保证数据库的一致性,这被称为系统的并发控制

在并发执行时,通过保证所执行的任何调度效果都与没有并发效果一样,我们可以保持数据库的一致性。调度在某种意义上等价于一个串行调度,这种调度被称为可串行化调度

事务的并发

可串行化

串行化顺序可以通过拓扑排序得到。

对于优先图来说,读写、写读、写写被称为一条边,考察这个有向图中是否有环,无环的优先图被称为可串行化调度

并发一致性问题

在并发环境下,事务的隔离性很难保证,因此可能出现并发一致性问题

问题 原因 图例
丢失修改 T1和T2 两个事务都对一个事务进行修改,T1先修改T2随后修改,T2的修改覆盖了T1的修改
读脏数据 T1修改了一个数据,T2最后读取了这个数据。如果T1撤销了这次修改,那么T2读取的数据是脏数据
不可重复读 T2读取了一个数据,T1对其进行了修改,如果T2再次读取这个数据,此时读取结果和第一次不同
幻影读 T1读取某个范围内的数据,T2 在这个范围内插入新的数据,T1再次读取这个范围内的数据,此时读取的结果和第一次读取的不同

事务的隔离级别

隔离级别 简介
可串行化 即可串行化调度
可重复读 只允许读取已提交数据,事务两次读取的间隙其他事务不得更新
已提交读 只允许读取已提交的数据,允许同一事务读数据的前后不一致
未提交读 允许读取未提交数据

事务的隔离级别与对应的并发问题

隔离级别/并发问题 脏读 不可重复读 幻影读 加锁读
未提交读 ×
提交读 × ×
可重复读 × × ×
可串行化 × × ×

隔离级别的实现

通过封锁来实保证事务的可串行化。通过共享、排他锁及两阶段封锁协议来保证串行化下的并发读

时间戳

另一类用来实现隔离性的技术为为每一个事务分配一个时间戳,系统维护两个时间戳来保证冲突情况下按照顺序访问数据项

多版本和快照隔离

快照隔离中,我们可以想象每个事务开始时尤其自身的数据库版本或快照,它从这个私有的版本中读取数据,因此它和其他事务的更新隔开。事务的更新只在私有数据库中进行,只有提交时才将信息保存,写入数据库。

并发控制

读写锁

锁一般被分为两种

  • 共享锁:简称为S锁,又称为读锁
  • 排他锁:简称为X锁,又称为写锁

这两种锁有以下规定

  • 一个事务数据对象加了X锁,就可以对数据A进行读取和更新,加锁期间其他事务不能获得A的锁
  • 一个事务数据对象加了S锁,可以对A进行读取操作,加锁期间其他事务可以对其加S锁,但是不能加X锁

意向锁

使用意向锁来支持多粒度的封锁

在行级锁、表级锁的情况下,事务想要对表A加X锁,就要检测其他事务是否对表A和表A的任意一行加了锁,那么就需要对A的每一行都检测,这非常耗时

在X/S锁之外引入了IX、IS,IX和IS都是表锁,用来表示一个事务想在某个表上加X或S锁,有以下规定:

  • 一个事务在获得某个数据行的S锁之前,必须先获得表的IS锁或更强的锁
  • 一个数据在获得某个数据航的X锁之前,必须获得表的IX锁

通过引入意向锁,输入想要对某个表A加锁,只需检测事务是否对表A加了x/Ix/S/IS锁

- X IX S IS
X × × × ×
IX × ×
S × ×
IS ×
  • 任意IX/IS 之间都是相容的,因为它只表示要加锁,并没有真正的加锁
  • S锁只与S和IS兼容

两阶段封锁协议

保证事务可串行化的一个协议是两阶段封锁协议,该协议分两个阶段

  • 增长阶段:事务可以获得锁,但是不能释放锁
  • 缩减阶段:事务可以释放锁,但是不能获得新锁

MySQL 隐式与显示锁定

MySQL 的 InnoDB 存储引擎采用两段锁协议,会根据隔离级别在需要的时候自动加锁,并且所有的锁都是在同一时刻被释放,这被称为隐式锁定。

InnoDB 也可以使用特定的语句进行显示锁定:

1
2
SELECT ... LOCK In SHARE MODE;
SELECT ... FOR UPDATE;

MySQL事务

事务的概念、隔离级别、死锁

见「数据库系统原理」

事务日志

事务日志可以帮助提高事务的效率,使用事务日志,在储存引擎上修改表时数据只需要修改其内存拷贝,再把修改行为记录到硬盘上的持久事务中,而不用每次都将修改的数据本身持久到磁盘

事务日志采用追加方式,因此日志操作是顺序I/O。采用事务日志速度快。事务日志持久后,内存中被修改的数据主键被刷回磁盘,大多数储存引擎都是这么实现的。这被称为预写日志,修改数据两次写磁盘

如果数据的修改已经持久化,而数据本身没被写入磁盘,系统崩溃后储存引擎在重启时自动恢复修改的数据。

MySQL自动提交

MySQL默认采用自动提交,不是显示的开始一个事务,每个查询都被当成一个事务提交。

有一些命令会强制提交当前的活动事务,如Alter

MySQL可以设置所有的四个隔离级别

1
set session transaction isolation level read committed

事务中混用储存引擎

MySQL不管理事务,事务由引擎实现,所以在一个中混用引擎是不可靠的

事务回滚对于非事务型表(如MyISAM)无法撤销,数据库会失去一致性

隐式和显示锁定

InnoDB采用两阶段锁定协议,事务执行过程中随时可以执行锁定,只有在commit和rollback时释放,并且所有的锁在同一时刻释放,这些属于隐式锁,InnoDB在需要时自动加锁

同样InnoDB支持显示加锁,一般来说显示使用LOCK TABLES语句不但没有必要还会严重影响性能,实际上InnoDB行锁性能更好

MySQL并发控制

读写锁

详见「数据库系统原理」

锁粒度

一种提高资源并发的方式是让锁的对象更有选择性。尽量只锁需要修改的部分数据,而不是所有的资源。任何时候,互相之间不发生冲突,锁的数量越少并发程度越高。

加锁也需要消耗资源,获得锁、检查锁、释放锁都会增大开销。所谓锁策略,就是在所的开销和安全性之间寻求一种平衡。大多数商业数据库没有提供更多的选择,一般是在表上施加行级锁。而MySQL提供可多种选择,每种储存引擎都可以实现自己的锁策略和锁粒度

  • 表锁

表锁是MySQL中最基本的锁策略,并且是开销最小的锁。它锁定整张表,用户对表的写操作都需要获得整个锁

尽管储存引擎管理自己的锁,MySQL本身还是会使用有效的表锁,例如Alter table语句使用表锁

  • 行级锁

行级锁可以最大程度的支持并发处理(最大的锁开销),InnoDBXtraDB实现了行级锁,行级锁只在引擎层面实现,而服务器层面并不了解锁的情况

MySQL多版本并发控制(MVCC)

MVCC,Multi-Version Concurrency Control,多版本并发控制。MVCC 是一种并发控制的方法,一般在数据库管理系统中,实现对数据库的并发访问;在编程语言中实现事务内存。

如果有人从数据库中读数据的同时,有另外的人写入数据,有可能读数据的人会看到『半写』或者不一致的数据。有很多种方法来解决这个问题,叫做并发控制方法。最简单的方法,通过加锁,让所有的读者等待写者工作完成,但是这样效率会很差。MVCC 使用了一种不同的手段,每个连接到数据库的读者,在某个瞬间看到的是数据库的一个快照,写者写操作造成的变化在写操作完成之前(或者数据库事务提交之前)对于其他的读者来说是不可见的。

当一个 MVCC 数据库需要更一个一条数据记录的时候,它不会直接用新数据覆盖旧数据,而是将旧数据标记为过时(obsolete)并在别处增加新版本的数据。这样就会有存储多个版本的数据,但是只有一个是最新的。这种方式允许读者读取在他读之前已经存在的数据,即使这些在读的过程中半路被别人修改、删除了,也对先前正在读的用户没有影响。这种多版本的方式避免了填充删除操作在内存和磁盘存储结构造成的空洞的开销,但是需要系统周期性整理(sweep through)以真实删除老的、过时的数据。对于面向文档的数据库(Document-oriented database,也即半结构化数据库)来说,这种方式允许系统将整个文档写到磁盘的一块连续区域上,当需要更新的时候,直接重写一个版本,而不是对文档的某些比特位、分片切除,或者维护一个链式的、非连续的数据库结构。

MVCC 提供了时点(point in time)一致性视图。MVCC 并发控制下的读事务一般使用时间戳或者事务 ID去标记当前读的数据库的状态(版本),读取这个版本的数据。读、写事务相互隔离,不需要加锁。读写并存的时候,写操作会根据目前数据库的状态,创建一个新版本,并发的读则依旧访问旧版本的数据。

MySQL实现了自己的多版本并发控制(MVCC),可以认为MVCC是行级锁的一个变种,它在很多情况下避免了加锁操作,因此开销更低。

MVCC的实现是通过保存数据在某个时间点的快照来实现的。不管需要执行多长时间,每个事务看到的数据都是一致的。根据事务开始的时间不同,事务对同一张表,同一时刻数据可能是不一样的

InnoDB的MVCC是通过在每个记录后面保存两个隐藏的列来实现的。这两个列一个保存了行的创建时间,一个保存了行的过期时间。当然储存的是系统版本号。每开始一个事务,系统版本号都会递增,事务开始时刻的系统版本号作为事务的版本号。

  • SELECT

    InnoDB会根据以下两个条件检查记录

    A. InnoDB只查找版本早于当前事务版本的数据行,这样可以确保事务读取的行要么在开始前存在,要么自身插入或修改

    B. 行的删除版本要么未定义,要么大于当前事务版本号。这样可以确保事务读到的行在事务开始前未被删除

  • INSERT

    InnoDB为新插入的每一行保存当前系统的版本号

  • DELETE

    InnoDB为每一行保存当前系统的版本号作为删除标识

  • UPDATE

    InnoDB插入一行新的记录,保存当前系统的版本号,同时保存当前系统到原来的行作为删除标识

这样通过版本号的方法使得大多数读操作不需要加锁。

MVCC只在可重复读和已提交读两个隔离级别工作,其他的隔离级别与MVCC不兼容

Next-Key Locks

Next-Key Locks 是 MySQL 的 InnoDB 存储引擎的一种锁实现。

MVCC 不能解决幻读的问题,Next-Key Locks 就是为了解决这个问题而存在的。在可重复读(REPEATABLE READ)隔离级别下,使用 MVCC + Next-Key Locks 可以解决幻读问题。

Record Locks

锁定一个记录上的索引,而不是记录本身。

如果表没有设置索引,InnoDB 会自动在主键上创建隐藏的聚簇索引,因此 Record Locks 依然可以使用。

Gap Locks

锁定索引之间的间隙,但是不包含索引本身。例如当一个事务执行以下语句,其它事务就不能在 t.c 中插入 15。

1
SELECT c FROM t WHERE c BETWEEN 10 and 20 FOR UPDATE;

Next-Key Locks

它是 Record Locks 和 Gap Locks 的结合,不仅锁定一个记录上的索引,也锁定索引之间的间隙。例如一个索引包含以下值:10, 11, 13, and 20,那么就需要锁定以下区间:

1
2
3
4
5
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

MySQL引擎

B+ 树原理

1. 数据结构

B Tree 指的是 Balance Tree,也就是平衡树。平衡树是一颗查找树,并且所有叶子节点位于同一层。

B+ Tree 是基于 B Tree 和叶子节点顺序访问指针进行实现,它具有 B Tree 的平衡性,并且通过顺序访问指针来提高区间查询的性能。

在 B+ Tree 中,一个节点中的 key 从左到右非递减排列,如果某个指针的左右相邻 key 分别是 keyi 和 keyi+1,且不为 null,则该指针指向节点的所有 key 大于等于 keyi 且小于等于 keyi+1。

img

2. 操作

进行查找操作时,首先在根节点进行二分查找,找到一个 key 所在的指针,然后递归地在指针所指向的节点进行查找。直到查找到叶子节点,然后在叶子节点上进行二分查找,找出 key 所对应的 data。

插入删除操作会破坏平衡树的平衡性,因此在插入删除操作之后,需要对树进行一个分裂、合并、旋转等操作来维护平衡性。

3. 与红黑树的比较

红黑树等平衡树也可以用来实现索引,但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构,主要有以下两个原因:

(一)更少的查找次数

平衡树查找操作的时间复杂度和树高 h 相关,O(h)=O(logdN),其中 d 为每个节点的出度。

红黑树的出度为 2,而 B+ Tree 的出度一般都非常大,所以红黑树的树高 h 很明显比 B+ Tree 大非常多,查找的次数也就更多。

(二)利用磁盘预读特性

为了减少磁盘 I/O 操作,磁盘往往不是严格按需读取,而是每次都会预读。预读过程中,磁盘进行顺序读取,顺序读取不需要进行磁盘寻道,并且只需要很短的旋转时间,速度会非常快。

操作系统一般将内存和磁盘分割成固定大小的块,每一块称为一页,内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小,使得一次 I/O 就能完全载入一个节点。并且可以利用预读特性,相邻的节点也能够被预先载入。

InnoDBMyISAM的数据分布对比

MyISAM 数据分布很简单,数据按照插入数据储存在磁盘上

它很容易创建索引,并且,索引没有什么不同

这是索引1号 Col1

这是索引2号Col2

InnoDB支持聚簇数据,使用非常不同的方式储存同样的数据。

1544680914971

该图显示整个表,而非只有索引,InnoDB中聚簇索引就是表

InnoDB

是 MySQL 默认的事务型存储引擎,只有在需要它不支持的特性时,才考虑使用其它存储引擎。

实现了四个标准的隔离级别,默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下,通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读。

主索引是聚簇索引,在索引中保存了数据,从而避免直接读取磁盘,因此对查询性能有很大的提升。

内部做了很多优化,包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。

支持真正的在线热备份。其它存储引擎不支持在线热备份,要获取一致性视图需要停止对所有表的写入,而在读写混合场景中,停止写入可能也意味着停止读取。

MyISAM

设计简单,数据以紧密格式存储。对于只读数据,或者表比较小、可以容忍修复操作,则依然可以使用它。

提供了大量的特性,包括压缩表、空间数据索引等。

不支持事务。

不支持行级锁,只能对整张表加锁,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但在表有读取操作的同时,也可以往表中插入新的记录,这被称为并发插入(CONCURRENT INSERT)。

可以手工或者自动执行检查和修复操作,但是和事务恢复以及崩溃恢复不同,可能导致一些数据丢失,而且修复操作是非常慢的。

如果指定了 DELAY_KEY_WRITE 选项,在每次修改执行完成时,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区,只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。

  • 事务:InnoDB 是事务型的,可以使用 Commit 和 Rollback 语句。
  • 并发:MyISAM 只支持表级锁,而 InnoDB 还支持行级锁。
  • 外键:InnoDB 支持外键。
  • 备份:InnoDB 支持在线热备份。
  • 崩溃恢复:MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多,而且恢复的速度也更慢。
  • 其它特性:MyISAM 支持压缩表和空间数据索引。

InnoDB 储存引擎

InnoDB是MySQL默认的事务型引擎,也是最重要,最广泛使用的储存引擎。它被设计用来处理大量的短期事务,短期事务大部分情况下都是正常提交的,很少被回滚。InnoDB的性能和崩溃自动回复的特性使其在非事务型储存的需求中也很流行。

InnoDB数据储存在表空间中,表空间是InnoDB管理的一个黑盒子,由一系列数据文件组成。

InnoDB通过MVCC来支持高并发,并且实现了四个标准的隔离级别,其默认级别是可重复读,并且通过间隙锁策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引的间隙进行锁定。

InnoDB表基于聚簇索引建立。InnoDB和MySQL其他引擎有很大不同,聚簇索引对主键的查询有很高的性能。不过他的二级索引必须包含主键列,所以如果主键很大的话,其他的二级索引就会很大。所以索引较多的话主键应尽可能的小

InnoDB内部有很多优化,从磁盘读数据的可预测性读,自动创建hash索引,插入缓冲器操作。

MyISAM引擎

在MySQL5.1之前的版本,MyISAM都是默认的储存引擎,MySQL有几个致命的缺点

  1. 不支持事务和行级锁
  2. 崩溃后无法安全恢复

但其优点在于对于只读数据,速度较快

MyISAM将表储存在两个文件中,数据文件和索引文件,采用非聚簇索引(详见索引部分)

比较

  • 事务: I 是事务型的,支持提交和回滚
  • 并发:M 只支持表级锁, I 还支持行级锁
  • 外键: I 支持外键
  • 备份: I支持在线热备份
  • 崩溃恢复: M崩溃后损坏的几率较高,I 有完善的日志恢复机制

MySQL索引

索引(在MySQL中也称键)是储存引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。

索引优化是对查询性能优化最有效的手段,能够轻易将查询性能提升若干个数量级。

索引基础

B-树索引

B-树索引是人们经过长期探索发展出目前最适合数据库系统的数据结构,它高效的利用了索引以及机械磁盘的空间。在大多数情况下爱,其不需要全表扫描来获取需要的数据,取而代之的是从根节点进行搜索,依次根据指针向下查找。B树索引有以下特点:

  • 全值匹配 与所有列匹配
  • 匹配最左前缀 索引查找性为“Allen”的人
  • 匹配列前缀 like “J%”
  • 匹配范围值
  • 精确匹配某一列与并范围匹配某一列
  • 只访问索引查询(覆盖索引)

B树索引的不足

  • 如果不是从最左列开始,则无法使用查找
  • 不能跳过索引中的列
  • 范围列的右侧无法再使用索引

可以发现,索引列的顺序十分重要

hash索引

hash索引基于哈希表实现,只有精确匹配所有列的查询才有效。每一行数据索引都会计算一个hash码

hash查找速度很快,但也有以下限制:

  • hash索引只包含hash值和行指针,而不储存字段的值
  • hash索引并不是按照索引值储存,无法排序
  • hash索引不支持部分索引的匹配查找,只支持等值查找

值得注意的是,InnoDB有一个特殊的功能叫自适应哈希索引,当InnoDB某些值使用非常频繁时,他会在内存中基于B树再创建一个hash索引

索引的优点
  • 索引大大减小了服务器需要扫描的数据量
  • 索引可以帮助服务器避免排序和临时表
  • 索引可以将随机IO变为顺序IO

聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据储存方式。当表有聚簇索引时,表示数据行和相邻的键值紧凑的储存在一起。一个表只能有一个聚簇索引。

InnoDB默认通过主键来聚集数据

如果没有定义主键、InnoDB会选择一个唯一的非空索引,如果没有这样的索引,InnoDB会隐式的定义一个主键作为聚簇索引的主键

聚簇索引的优点
  • 可以把相关数据保存在一起,例如根据用户ID来聚集数据,这样可以最小化磁盘读取数据页
  • 数据访问更快,举措索引将索引和数据保存在统一个B树中,因此聚簇比非聚簇索引的查找更快
  • 覆盖索引世界使用主键值
聚簇索引的缺点
  • 聚簇索引最大限度提高IO密集应用的性能,但是数据如果都在内存中(例如缓存)访问顺序就不重要了
  • 插入速度依赖于插入顺序
  • 更新聚簇索引列的代价很高,强制每个更新移动到位
  • 页分裂问题
  • 聚簇索引可能导致全表扫描变慢(尤其是稀疏表时)
  • 二级索引可能比想象的大,因为二级索引叶子节点包含了主键列

覆盖索引

索引是一种查找数据的方式,但是MySQL也可以使用索引来直接获取列的数据,这样就不需要读取数据的行。

  • 如果一个索引包含(或者说覆盖)所有要查询字段的值,我们就称之为“覆盖索引。!!覆盖索引从辅助索引中即可得到查询数据,简单的说就是辅助索引就包含了所要查询的值!!
  • 例如创建某个辅助索引(name、age)查询数据时,select username,age from user where username=’Java’ and age = 22 要查询的列叶子节点都存在,不需要回表索引若干问

索引若干问

使用索引的原因?

通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性;

大大加快数据的检索速度(这是创建索引最重要的原因);

帮助服务器避免排序和临时表,将随机IO变为顺序IO;

加速表与表之间的连接,在实现数据参考完整性方面有重要意义;

为什么不对每一列创建索引?

当数据增加、删除和修改时,索引也需要动态维护,这样就降低了数据的维护速度

除了数据表占数据空间之外,索引还要需要占据一定的物理空间,如果建立聚簇索引,空间占用更大

创建、维护索引耗费时间、这种耗费随数据量增加而增加

索引提高查询速度的原因?

将无序的数据变成相对有序的数据(像查目录一样)

常用索引使用的数据结构?

  • 哈希索引

    对于哈希索引来说,底层函数就是一个hash表,因此在大多数需求为单条记录的查询时,可以选择hash索引,查询性能较快

  • BTree索引

    Mysql使用的是B+树索引,但是对于两种引擎的实现不同

MyISAMInnoDB实现B+树索引的区别

MyISAM: B+树叶节点data域存放的是数据记录的地址,在索引检索的时候,首先按照B+树 算法搜索索引,如果指定的Key存在,则取出其data域的值,然后以data域的值为地址读取相应的数据记录,被称为”非聚簇索引”

InnoDB: 数据文件本身就是索引文件,相比于MyISAM 索引文件和数据文件是分离的,其表结构本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的整条数据记录,这个索引的key正是数据表的主键,因此InnoDB本身就是主索引,这被称为“聚簇索引(聚集索引)”,而其余的索引被作为辅助索引,辅助索引data域储存记录主键的值而不是地址。这样根据辅助索引查找时,先取出主键的值,再走一遍主索引。因此设计表时主键的选择十分重要,主键不宜过长也不宜非单调。

索引的注意事项

  • 在经常需要索引的列上加快搜索速度
  • 在经常使用where子句上索引加快条件判断速度
  • 在经常需要排序的列上创建索引,因为索引已经排序,这样加快排序的查询时间
  • 中、大型表的排序都是有效的,但是特大型表不适合建索引
  • 在经常用到的连接上建索引,这些外键索引加快速度
  • 避免where子句对字段加函数,这样无法命中索引
  • InnoDB中使用与业务无关的自增主键作为主键,使用逻辑主键,不使用业务主键
  • 索引列 NOT NULL,否者引擎将放弃索引
  • 删除长期不使用索引、不使用索引的存在会造成比必要的性能损耗

索引原则(高性能Mysql

  • 单行访问很慢,特别是机械硬盘。如果从数据中读取的一个数据库只获取一行,浪费很多工作,最好读取尽可能多的行。
  • 顺序访问范围数据是很快的。第一,顺序I/O不需要多次的磁盘寻道,比随机I/O快的多。第二、服务器按顺序读取数据,不需要额外的排序操作,group by 也无需再次排序
  • 索引的覆盖查询时很快的,如果一个索引包含了查询所有的列,那么储存引擎就不需要再回表查询行,这避免了大量的单行访问

MYSQL优化

为什么要优化

  • 系统的吞吐量瓶颈往往处在数据库的访问速度上
  • 随着引用程序的运行,数据库中的数据会越来越多
  • 数据放在磁盘上,读取速度无法和内存比

如何优化

  • 设计数据库:数据库表、字段设计、储存引擎
  • 利用好MySQL自身提供的功能,如索引
  • 横向扩展:MySQL集群、负载均衡、读写分离
  • SQL语句的优化

字段设计原则

  • 尽量使用整形表示字符串
  • 尽可能选择小的数据类型和指定短的长度
  • 尽可能使用 not null
  • 字段注释完整、见名知意
  • 单表字段不要过多

范式

第一范式:字段原子性

如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式

关系型数据库,默认满足第一范式

第二范式:消除对主键的部分依赖

满足第一范式,并且每一个非主属性完全函数依赖于码。也就是说要确保每一列都与主键相
关,而不能只与主键部分相关(主要针对联合主键)

依赖:A字段可以确定B字段,则B字段依赖A字段。比如知道了教室号 和 上课时间,就能确定任课老师是谁。于是教室号上课时间和就能构成复合主键,能够确定去哪个教室上课,任课老师是谁等。但我们常常增加一个id作为主键,而消除对主键的部分依赖。

对主键的部分依赖:某个字段依赖复合主键中的一部分。

解决方案:新增一个独立字段作为主键。

第三范式: 消除对主键的传递依赖

并且消除传递依赖。数据库表中的每一列都要和主键直接相关,而不是间接相关

id weekday course_class course_id
1001 周一 教育大楼1521 3546
course_id course_name course_teacher
3546 Java 张三

水平分割和垂直分割

水平分割: 通过建立结构相同的几张表分别储存数据

水平切分又称为 Sharding,它是将同一个表中的记录拆分到多个结构相同的表中。

当一个表的数据不断增多时,Sharding 是必然的选择,它可以将数据分布到集群的不同节点上,从而缓存单个数据库的压力。

垂直分割:将经常使用的字段放在一个单独的表中,分割后表记录之间是意义对应的关系

垂直切分是将一张表按列切分成多个表,通常是按照列的关系密集程度进行切分,也可以利用垂直切分将经常被使用的列和不经常被使用的列切分到不同的表中。

在数据库的层面使用垂直切分将按数据库中表的密集程度部署到不同的库中,例如将原来的电商数据库垂直切分成商品数据库、用户数据库等。

复制

从主复制

主要涉及三个线程:binlog 线程、I/O 线程和 SQL 线程。

  • binlog 线程 :负责将主服务器上的数据更改写入二进制日志(Binary log)中。
  • I/O 线程 :负责从主服务器上读取二进制日志,并写入从服务器的重放日志(Replay log)中。
  • SQL 线程 :负责读取重放日志并重放其中的 SQL 语句。

1550818061611

读写分离

主服务器处理写操作以及实时性要求比较高的读操作,而从服务器处理读操作。

读写分离能提高性能的原因在于:

  • 主从服务器负责各自的读和写,极大程度缓解了锁的争用;
  • 从服务器可以使用 MyISAM,提升查询性能以及节约系统开销;
  • 增加冗余,提高可用性。

读写分离常用代理方式来实现,代理服务器接收应用层传来的读写请求,然后决定转发到哪个服务器。

1550818143995

悲观锁与乐观锁

乐观锁对应于生活中乐观的人总是想着事情往好的方向发展,悲观锁对应于生活中悲观的人总是想着事情往坏的方向发展。这两种人各有优缺点,不能不以场景而定说一种人好于另外一种人。

悲观锁

总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁,这样别人想拿这个数据就会阻塞直到它拿到锁(共享资源每次只给一个线程使用,其它线程阻塞,用完后再把资源转让给其它线程)。传统的关系型数据库里边就用到了很多这种锁机制,比如行锁,表锁等,读锁,写锁等,都是在做操作之前先上锁。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。

乐观锁

总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据,可以使用版本号机制和CAS算法实现。乐观锁适用于多读的应用类型,这样可以提高吞吐量,像数据库提供的类似于write_condition机制,其实都是提供的乐观锁。在Java中java.util.concurrent.atomic包下面的原子变量类就是使用了乐观锁的一种实现方式CAS实现的。

两种锁的使用场景

从上面对两种锁的介绍,我们知道两种锁各有优缺点,不可认为一种好于另一种,像乐观锁适用于写比较少的情况下(多读场景),即冲突真的很少发生的时候,这样可以省去了锁的开销,加大了系统的整个吞吐量。但如果是多写的情况,一般会经常产生冲突,这就会导致上层应用会不断的进行retry,这样反倒是降低了性能,所以一般多写的场景下用悲观锁就比较合适。

面试常见问题

系统原理

★★★ ACID 的作用以及实现原理。
★★★ 四大隔离级别,以及不可重复读和幻影读的出现原因。
★★☆ 封锁的类型以及粒度,两段锁协议,隐式和显示锁定。
★★★ 乐观锁与悲观锁。
★★★ MVCC 原理,当前读以及快照读,Next-Key Locks 解决幻影读。
★★☆ 范式理论。
★★★ SQL 与 NoSQL 的比较。

MySQL

★★★ B+ Tree 原理,与其它查找树的比较。
★★★ MySQL 索引以及优化。
★★★ 查询优化。
★★★ InnoDB 与 MyISAM 比较。
★★☆ 水平切分与垂直切分。
★★☆ 主从复制原理、作用、实现。
★☆☆ redo、undo、binlog 日志的作用。