索引*

聚簇索引/非聚簇索引

  • 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

  • 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。包括Myisam的索引、辅助索引等

算法种类

  • B+tree索引（重点掌握，之后文章详细讲解）

  • Hash索引

  • full-text索引

  • R-tree索引

B+tree索引

聚簇索引

非聚簇索引

hash索引

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：

  • 无法用于排序与分组；

  • 只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

全文索引

MyISAM 存储引擎支持全文索引，用于查找文本中的关键词，而不是直接比较是否相等。

查找条件使用 MATCH AGAINST，而不是普通的 WHERE。

全文索引使用倒排索引实现，它记录着关键词到其所在文档的映射。

空间索引

MyISAM 存储引擎支持空间数据索引（R-Tree），可以用于地理数据存储。空间数据索引会从所有维度来索引数据，可以有效地使用任意维度来进行组合查询。

分类

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：即一个索引包含多个列

优化

  1. 查找条件全部用到索引：复合索引，查找条件应尽量都是索引列并且按照顺序

  2. 最左前缀法则：复合索引，按照顺序，从左到右

    • a->c：a 有效，c 无效

    • b->c：b、c 都无效

    • c：c 无效

  3. 不能对索引做运算处理：

    • 计算，如：+、-、*、/、!=、<>、is null、is not null、or（要求两边都是索引即可）

    • 函数，如：sum()、round()等等

    • 手动/自动类型转换，如：id = "1"，本来是数字，给写成字符串了

  4. 索引不能放到范围的右边：复合索引，abc，如果b是范围，c是等于，则索引失效

  5. 减少select*操作：尽量使用覆盖索引，即查询的字段为索引值

  6. like模糊匹配索引失效：%号用到左边和两边都会导致索引失效

    • 使用复合索引，即 like 字段是 select 的查询字段，如：select name from table where name like "%张三%"，这样实际上是遍历整个b+树

    • 使用 like "张三%"

  7. order by、group by尽量使用索引，where先于having，尽量替换

事务 *

是什么

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

事务最经典也经常被拿出来说例子就是转账了。

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

四大特性(ACID)

关系性数据库需要遵循ACID规则，具体内容如下：

  1. 原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

    • undo log(回滚日志)：InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

  2. 一致性： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；

    • 原子性+隔离的目的是实现一致性

  3. 隔离性： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；

    • 通过两种隔离机制实现

    • 写写：通过行锁机制

    • 写读：通过MVCC实现

  4. 持久性： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

    • mysql使用bufferpool的方式刷脏，提高了sql的执行效率，bufferpool的作用:当查找是会优先查找bufferpool，如果没有在从磁盘中加载到bufferpool中；当行修改时，也是先写入bufferpool，bufferpool的数据会定期刷新到磁盘上

    • redo log(重做日志)，用于实现持久性，修改时先写入redo log，在更新到bufferpool中

    • 但是为什么redo log日志刷到磁盘快呢

      • 刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。

      • 刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

脏读/幻读/不可重复读

  • 脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

  • 不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

  • 幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

隔离级别

为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

隔离级别

脏读

不可重复读

幻影读

READ-UNCOMMITTED

√

√

√

READ-COMMITTED

×

√

√

REPEATABLE-READ

×

×

√

SERIALIZABLE

×

×

×

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

  • READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。

  • READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。

  • REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。

  • SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

这里需要注意的是：Mysql 默认采用的 REPEATABLE_READ隔离级别 Oracle 默认采用的 READ_COMMITTED隔离级别

事务隔离机制的实现基于锁机制和并发调度。其中并发调度使用的是MVVC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。

因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容): ，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读）并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到**SERIALIZABLE(可串行化)**隔离级别。

MVCC *

全称Multi-Version Concurrency Control，即多版本并发控制，主要是为了提高数据库的并发性能。

同一行数据平时发生读写请求时，会上锁阻塞住。但mvcc用更好的方式去处理读—写请求，做到在发生读—写请求冲突时不用加锁。

这个读是指的快照读，而不是当前读，当前读是一种加锁操作，是悲观锁。

当前读&快照读

当前读

它读取的数据库记录，都是当前最新的版本，会对当前读取的数据进行加锁，防止其他事务修改数据。是悲观锁的一种操作。

如下操作都是当前读：

  • select lock in share mode (共享锁)

  • select for update (排他锁)

  • update (排他锁)

  • insert (排他锁)

  • delete (排他锁)

  • 串行化事务隔离级别

快照读

快照读的实现是基于多版本并发控制，即MVCC，既然是多版本，快照读读到的数据不一定是当前最新的数据，有可能是之前历史版本的数据。

如下操作是快照读：

  • 不加锁的select操作（注：事务级别不是串行化）

数据库并发场景

  • 读-读：不存在任何问题，也不需要并发控制

  • 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读

  • 写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失

MVCC解决的问题

mvcc用来解决读—写冲突的无锁并发控制，就是为事务分配单向增长的时间戳。为每个数据修改保存一个版本，版本与事务时间戳相关联。

读操作只读取该事务开始前的数据库快照。

解决问题如下：

  • 并发读-写时：可以做到读操作不阻塞写操作，同时写操作也不会阻塞读操作。

  • 解决脏读、幻读、不可重复读等事务隔离问题，但不能解决上面的写-写 更新丢失问题。

提高并发性能的组合拳：

  • MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突

  • MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

实现原理

它的实现原理主要是版本链，undo日志 ，Read View来实现的

版本链

我们数据库中的每行数据，除了我们肉眼看见的数据，还有几个隐藏字段，分别是id、db_roll_pointer、db_row_id。

  • db_trx_id

    6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID。

  • db_roll_pointer（版本链关键）

    7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

  • db_row_id

    6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以db_row_id产生一个聚簇索引。

  • 实际还有一个删除flag隐藏字段, 记录被更新或删除并不代表真的删除，而是删除flag变了

如上图，db_row_id是数据库默认为该行记录生成的唯一隐式主键，db_trx_id是当前操作该记录的事务ID，而db_roll_pointer是一个回滚指针，用于配合undo日志，指向上一个旧版本。

每次对数据库记录进行改动，都会记录一条undo日志，每条undo日志也都有一个roll_pointer属性（INSERT操作对应的undo日志没有该属性，因为该记录并没有更早的版本），可以将这些undo日志都连起来，串成一个链表，所以现在的情况就像下图一样：

对该记录每次更新后，都会将旧值放到一条undo日志中，就算是该记录的一个旧版本，随着更新次数的增多，所有的版本都会被roll_pointer属性连接成一个链表，我们把这个链表称之为版本链，版本链的头节点就是当前记录最新的值。另外，每个版本中还包含生成该版本时对应的事务id，这个信息很重要，在根据ReadView判断版本可见性的时候会用到。

undo日志

Undo log 主要用于记录数据被修改之前的日志，在表信息修改之前先会把数据拷贝到undo log里。

当事务进行回滚时可以通过undo log 里的日志进行数据还原。

Undo log 的用途

  • 保证事务进行rollback时的原子性和一致性，当事务进行回滚的时候可以用undo log的数据进行恢复。

  • 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

undo log主要分为两种：

  • insert undo log

    代表事务在insert新记录时产生的undo log , 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

  • update undo log（主要）

    事务在进行update或delete时产生的undo log ; 不仅在事务回滚时需要，在快照读时也需要；

    所以不能随便删除，只有在快速读或事务回滚不涉及该日志时，对应的日志才会被purge线程统一清除

Read View(读视图)

事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照。

记录并维护系统当前活跃事务的ID(没有commit，当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以越新的事务，ID值越大)，是系统中当前不应该被本事务看到的其他事务id列表。

Read View主要是用来做可见性判断的, 即当我们某个事务执行快照读的时候，对该记录创建一个Read View读视图，把它比作条件用来判断当前事务能够看到哪个版本的数据，既可能是当前最新的数据，也有可能是该行记录的undo log里面的某个版本的数据。

Read View几个属性

  • trx_ids: 当前系统活跃(未提交)事务版本号集合。

  • low_limit_id: 创建当前read view 时“当前系统最大事务版本号+1”。

  • up_limit_id: 创建当前read view 时“系统正处于活跃事务最小版本号”

  • creator_trx_id: 创建当前read view的事务版本号；

Read View可见性判断条件

  • db_trx_id < up_limit_id || db_trx_id == creator_trx_id（显示）

    如果数据事务ID小于read view中的最小活跃事务ID，则可以肯定该数据是在当前事务启之前就已经存在了的,所以可以显示。

    或者数据的事务ID等于creator_trx_id ，那么说明这个数据就是当前事务自己生成的，自己生成的数据自己当然能看见，所以这种情况下此数据也是可以显示的。

  • db_trx_id >= low_limit_id（不显示）

    如果数据事务ID大于read view 中的当前系统的最大事务ID，则说明该数据是在当前read view 创建之后才产生的，所以数据不显示。如果小于则进入下一个判断

  • db_trx_id是否在活跃事务（trx_ids）中

    • 不存在：则说明read view产生的时候事务已经commit了，这种情况数据则可以显示。

    • 已存在：则代表我Read View生成时刻，你这个事务还在活跃，还没有Commit，你修改的数据，我当前事务也是看不见的。

MVCC和事务隔离级别

上面所讲的Read View用于支持RC（Read Committed，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。

RR、RC生成时机

  • RC隔离级别下，是每个快照读都会生成并获取最新的Read View；

  • 而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View，之后的查询就不会重复生成了，所以一个事务的查询结果每次都是一样的。

解决幻读问题

  • 快照读：通过MVCC来进行控制的，不用加锁。按照MVCC中规定的“语法”进行增删改查等操作，以避免幻读。

  • 当前读：通过next-key锁（行锁+gap锁）来解决问题的。

RC、RR级别下的InnoDB快照读区别

  • 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View， 将当前系统活跃的其他事务记录起来，此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；

  • 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见

  • 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View, 这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因

总结

从以上的描述中我们可以看出来，所谓的MVCC指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SEELCT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。

锁 *

行锁

  • 写读：一个线程先写，另一个线程后读，后读的数据是旧数据

  • 写写：一个线程先写，另一个线程后写，第二个线程的修改会被卡主

表锁

  • 当索引失效，修改数据就会锁住整张表

间隙锁

  • 根据范围条件锁定范围，带来的问题的是会把整个范围锁住，存在部分无辜的数据不能操作

  • 依赖非唯一的update语句、select 条件非唯一 后追加 FOR UPDATE（lock in share mode）;

注意的问题：那个事务锁定哪个字段区间的问题，例如我事务a锁定了a列的1-3区间，事务b锁定了b列的2-5区间，在事务b执行插入只要满足事务a对a列的锁定即可。（一开始不会用，走的坑），现在用在会经常查询和频繁修改的地方,不频繁的一半会用乐观锁解决。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

常见的解决死锁的方法

1、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。

2、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；

3、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；

4、如果业务处理不好可以用分布式事务锁或者使用乐观锁

数据库的乐观锁和悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。

悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。在查询完数据的时候就把事务锁起来，直到提交事务。实现方式：使用数据库中的锁机制

乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性。在修改数据的时候把事务锁起来，通过version的方式来进行锁定。实现方式：一般会使用版本号机制或CAS算法实现。

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。

但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。