本文共 3253 字，大约阅读时间需要 10 分钟。

事务和索引

（1）事务

1.什么是事务

事务是数据库应用中完成单一逻辑功能的操作集合；

• 事务就是将一组SQL语句放在同一批次内去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型
• 事务用来管理 insert,update,delete 语句

2.事务的ACID原则

• 原子性

整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（ROLLBACK）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consist)

一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账

案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发

多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

• 隔离性(Isolated)

隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的

隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。

• 持久性(Durable)

在事务完成以后，该事务对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

3.基本语法

– 使用set语句来改变自动提交模式

SET autocommit = 0; /关闭/

SET autocommit = 1; /开启/

– 注意:

— 1.MySQL中默认是自动提交

— 2.使用事务时应先关闭自动提交

– 开始一个事务,标记事务的起始点

START TRANSACTION

– 提交一个事务给数据库

COMMIT

– 将事务回滚,数据回到本次事务的初始状态

ROLLBACK

– 还原MySQL数据库的自动提交

SET autocommit =1;

– 保存点

SAVEPOINT 保存点名称 – 设置一个事务保存点

ROLLBACK TO SAVEPOINT 保存点名称 – 回滚到保存点

RELEASE SAVEPOINT 保存点名称 – 删除保存点

（2）索引

创建索引

这是最基本的索引，它没有任何限制。它有以下几种创建方式：

CREATE INDEX indexName ON table_name (column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable(   ID INT NOT NULL,    username VARCHAR(16) NOT NULL,   INDEX [indexName] (username(length))   );

删除索引的语法

DROP INDEX [indexName] ON mytable;

1.索引的作用

• 提高查询速度
• 确保数据的唯一性
• 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
• 全文检索字段进行搜索优化.

2.索引的分类

• 主键索引 (Primary Key)
• 唯一索引 (Unique)
• 常规索引 (Index)
• 全文索引 (FullText)

3.主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

4.唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

5.常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

6.全文索引

7.测试索引

8.索引准则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表建议不要加索引
• 索引一般应加在查找条件的字段

9.索引的数据结构

– 我们可以在创建上述索引的时候，为其指定索引类型，分两类

• hash类型的索引：查询单条快，范围查询慢
• btree类型的索引：b+树，层数越多，数据量指数级增长

MySQL中无论是Innodb还是MyIsam，都使用了B+树索引结构；

不用二叉查找树：不平衡，可能存在左倾或者右倾，形成一条链表；

不用平衡二叉树：AVL实现平衡的关键在于旋转操作，插入和删除可能会破坏平衡所以需要一次或者多次树旋转来重新平衡，旋转的耗时使得效率很低，维护平衡的代价太高；

不用红黑树：与AVL相比，红黑树查询效率不会降低，但是树的平衡性较低，高度更高，对于数据在磁盘等存储设备中的情况（MySQL），红黑树的过高高度会使IO的次数增加，严重影响性能；

B树：是为磁盘存储而设计的多路平衡查找树，与二叉树相比，树的非叶子节点可以有多个子树，因此B树高度远远小于AVL树和红黑树，磁盘IO次数大大减少；（Mangodb的索引使用了B树结构，MySQL使用B树的变种B+树）

B+树索引结构

B+树也是多路平衡查找树，其与B树的主要区别在于

1.B树中每个节点（包括叶子和非叶子节点）都存储真实的数据，B+树只有叶子节点存储真实的数据，而非叶子节点存储键；在MySQL中，真实数据可能是行的全部数据（Innodb的聚簇索引），也可能只是行的主键（Innodb的辅助索引），或者是行所在的地址（如MySQL的非聚簇索引）；

2.B树中一条记录只会出现一次，不会重复出现，而B+树的键则可能重复出现——在叶子节点中出现，也可能在非叶子节点中重现；

3.B+树的叶子节点之间通过双向链表连接；

4.B+树中的非叶子节点记录数比子节点少1；而B+树中非叶子节点与子节点记录数相同；

B+树相对于B的优势

• 更少的IO次数：B+数的非叶子节点只包含键，不包含真实的数据，因此每个节点存储的记录个数比B树多（阶m更大），因此B+树的高度更低，访问时需要的IO次数更少；此外，由于每个节点存储的记录数更多，对访问局部性原理的利用更好，缓存命中率更高；（局部性原理：当一个数据被使用时，其附近的数据有较大概率在短时间内被使用，B+树将相近的数据存储在同一个叶子节点，当访问其中的数据时，数据库会将该节点整个读取到缓存中，当临近数据接着被访问是，可以直接在缓存中读取，无需进行磁盘IO）
• 更适合范围查询：在B树中进行范围查询时，首先要找到查找的下限，然后对B树进行中序遍历，直到找到查找的上限；而B+树的范围查询，只需要对链表进行遍历即可；
• 更稳定的查询效率：B树的查询时间复杂度在1到树高之间，而B+树的查询复杂度则稳定为树高，因为所有真实数据都在叶子节点；

B+树存在的劣势

键会重复出现，占用更多空间；

转载地址：http://arqzi.baihongyu.com/