数据库笔记

事务相关

ACID四特性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）事务前后数据的完整性必须保持一致。

隔离性（Isolation）事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

并发事务问题 

1. 脏读 ：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

        1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)

        2.Mary读取自己的工资 ,发现自己的工资变为了8000，欢天喜地！

        3.而财务发现操作有误，回滚了事务,Mary的工资又变为了100  像这样,Mary记取的工资数8000是一个脏数据。 

2. 不可重复读 ：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

    1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成

    2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.

    3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000

 解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

3. 幻读 : 是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

   目前工资为1000的员工有10人。

   1.事务1,读取所有工资为1000的员工。

   2.这时事务2向employee表插入了一条员工记录，工资也为1000

   3.事务1再次读取所有工资为1000的员工 共读取到了11条记录， 

 解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题

事务原理

redo log

重做日志记录的是事务提交时数据页的物理修改，用来实现事务的持久性。日志文件由两部分组成:重做日志缓冲(redo og buffer)及重做日志文件(redo log file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘,发生错误时，进行数据恢复使用。

undo log

回滚日志，用于记录数据被修改前的信息，作用包含两个:提供回滚和 MVCC(多版本并发控制)。undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undolo中的逻辑记录读取到相应的内容并进行回滚。

• Undo log销毁: undo log在事务执行时产生，事务提交时，并不会立即删除undo log，因为这些日志可能还用于MVCC.
• Undo log存储: undo og采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undo log segment.

MVCC

当前读

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select ... lockin share mode(共享锁)，select ... for update、 update、 insert、delete(排他锁)都是一种当前读。

快照读

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻寒读

• Read Committed:每次select，都生成一个快照读。
• Repeatable Read:开启事务后第一个select语句才是快照读的地方
• Serializable: 快照读会退化为当前读。

MVCC

全称 Multi-Version concurrency Control指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySOL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志、readView。

隐式字段

DB_TRX ID：最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID

DB ROLL PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本

DB ROW ID：隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

readView

ReadView(读视图)是快照读 SQL 执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务 (未提交的) id。ReadView中包含了四个核心字段:

• m ids 当前活跃的事务ID集合
• min trx id 最小活跃事务ID
• max trx id 预分配事务ID，当前最大事务ID+1(因为事务ID是自增的)（不是当前活跃的最大事务id）
• creator trx id ReadView 创建者的事务ID

（活跃事务是指事务已经开启,但还没有提交或者回滚的事务）m_ids是记录当前是活跃事务的id列表

不同的隔离级别，生成ReadView的时机不同:

• READ COMMITTED :在事务中每一次执行快照读时生成ReadView。
• REPEATABLE READ: 仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

RC下mvcc提取数据的过程

RR下mvcc提取数据的过程

存储引擎

概述

数据库结构：包括连接层（数据库连接和身份验证登），服务层（解析sql和sql的优化等），引擎层（索引的实现和数据存储方式），存储层（保存数据）

主要包括如下存储引擎：

• lnnoDB: 是Mysql的默认存储引，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性（行级锁），数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。
• MyISAM ，如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。
• MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。

MyISAM现在被一种叫mogodb的NoSql数据库取代，Memory被一种叫redis的Nosql数据库取代

索引

在数据库存储引擎中，我们需要理解索引的建立（以下图是形象的描述了索引建立前后搜索方式的变化，但不是正式的结构树）

索引的建立有多种可以考虑的元素，主要有二分搜索树，b树（可能只有一个分支），b+树，红黑树（更新操作需要浪费资源）以及hash（不支持范围查询）表等，最常用的是b+树索引。

具体的索引建构如下：

（存储的结构按照层级分为区段页行）

b+树和b树的区别

• b树的每个结点都保存了数据，而b+树只有叶子结点保存了数据
• b树的叶子节点没有指针互相连接

为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相对于二叉树，层级更少，搜索效率高;
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
• 相对Hash索引，B+tree支持范围匹配及排序操作

索引分类

• 主键索引，针对表中的主键建立的索引，默认自动创建，且只能有一个(PRIMARY)
• 唯一索引：避免同一个表中某数据列中的值重复，可以有多个(UNIQUE)
• 常规索引：快速定位特定数据，可以有多个
• 全文索引：查找文本中的关键字，而不是比较索引中的值，可以有多个（FULLTEXT）

根据存储形式分类，还可以分为一下俩类

• 聚集索引：将数据存储与索引放到一起，索引结构的叶子结点保存了行数据，必须有且只有一个
• 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键，可以存在多个（如果也存数据则数据冗余）

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

sql优化

分析sql执行频率

慢查询日志

慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。

MySOL的慢查询日志默认没有开启，需要在MySOL的配置文件(/etc/my.cnf)中配置如下信息:

profiles

查看每一条sql的执行耗时

查看每一条语句在各个阶段的耗时

explain执行计划

各个字段的含义：

分析查询语句的各个字段：

最左前缀法则

如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效(后面的字段索引失效).

索引失效

考点1：条件查询条件顺序和索引顺序不一致，索引是否失效

考点2：使用了范围查询，索引是否失效

考点3：在索引上执行运算操作会导致索引失效

考点4：字符串类型字段使用时，不加引号，索引将失效

考点5：如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效

考点6：用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

考点7：mysql发现使用索引比不使用索引更慢，则不适用索引

sql提示

SOL提示，是优化数据库的一个重要手段，简单来说，就是在SOL语句中加些人为的提示来达到优化操作的目的。

覆盖索引

尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select*。

前缀索引

当字段类型为字符串(varchar，text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘I0，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

创建索引：create index idx xxxx on table name(column(n)) ; // n代表的创建索引字段数据取前多少位

可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

索引建立的原则

• 针对于数据量较大，且查询比较频繁的表建立索引（百万级数据）
• 针对于常作为查询条件(where)、排序(order by)、分组 (group by) 操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
• 如果索引列不能存储NULL值，请在创建表时用NOT NULL约束它。当优化器知道每列是否包含NULL值，它可更好地确定哪个索引最有效地用于查询。

SQL优化

插入优化

主键优化

在InnoDB中数据都是按照主键顺序存放的，这种存储方式叫做索引组织表

页分裂：页可以存储一半也可以为空，为了保持顺序，需要将原有页分裂插入新数据

页合并：当删除多个数据，页内使用空间低于一半，会查看左右页是否可以合并，以优化空间

主键选取原则

尽量短，插入尽量按顺序，避免对主键修改

分库分表

产生背景

随着互联网及移动互联网的发展，应用系统的数据量也是成指数式增长，若采用单数据库进行数据存储，存在以下性能瓶颈:

1，10瓶颈:热点数据太多，数据库缓存不足，产生大量磁盘10，效率较低。请求数据太多，带宽不够，网络10瓶颈。

2. CPU瓶颈:排序、分组、连接查询、聚合统计等SOL会耗费大量的CPU资源，请求数太多，CPU出现瓶颈

垂直分库：将一个库中的所有表按照功能划分为不同的业务组，每一个业务对应一组表

水平分表：将同一个表的不同数据安置在不同的数据库，但是数据具体存放在哪一台数据库，就需要采用到分片原则

分片原则： 范围分片，取模分片，一致性hash算法，枚举分片，按日期分片

视图

作用

简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

安全：数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据

数据独立：视图可帮助用户屏蔽真实表结构变化带来的影响。

存储过程

存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据

库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

触发器

触发器是与表有关的数据库对象，指在 inset/update/delete 之前威之后，发并执行发器中定义的SQL语句集合。脸发器的这种特性可以协助皮用在数据库端确保数据的完整性，日志记录，数据校验等提作。

使用别名 OLD 和 NEW 来引用发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级能发。

INSERT 型触发器：NEW 表示将要或者已经新增的数据

UPDATE型触发器：OLD 表示修改之前的数据，NEW 表示将要或已经修改后的数据

DELETE型触发器：OLD表示将要或者已经删除的数据

数据库锁

意向锁

主要是由于当对某一行数据进行修改时，需要给对应行数据加上行锁，但是当另一个事务来给表加锁时需要逐行扫描查看是否已经存在行级锁，所以引入表的意向锁来判断是否存在锁

意向共享锁 (IS): 由语句 select ...lock in share mode添加。

意向排他锁 (IX): 由insert、update、delete、select ...for update 添加

行级锁

每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在InnODB存储引擎中。lnnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为以下三类:

• 行锁(Record Lock):锁定单个行记录的锁，防止其他事务对此行进行update和delete。在RC、RR隔离级别下都支持
• 间隙锁(Gap Lock):锁定索引记录间隙(不含该记录)，确保索引记录间院不变，防止其他事务在这个间隙进行inse，产生幻读。在RR隔离级别下都支持
• 临键锁(Next-Key Lock): 行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap。在RR隔离级别下支持

InnoDB实现了以下两种类型的行锁:

• 共享锁(S):允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• 排他锁(X):允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

默认情况下，innoDB在 REPEATABLE READ事务隔离级别运行，nnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁。

lnnoDB的行锁是针对于索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

间隙锁

默认情况下，InnoDB在 REPEATABLE READ事务隔离级别运行，nnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时,优化为间隙锁
• 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
• 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止

注意: 间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁