MySQL索引与事务

MySQL索引

一、索引的概念
1.1 什么是索引
索引是一个排序的列表，在列表当中存储索引的值以及索引值对应数据所在的物理行。

索引值和数据是一个映射关系。

1.2 索引的作用
使用索引之后，不需要扫描全表来定位数据。加快数据库查询速度。

索引可以是表中的一列，也可以是多个列。

设置了索引之后，数据库可以利用索引快速定位，能够大大提高查询速度。这也是创建索引的主要原因。
尤其是在表的数据很大，以及涉及多个表查询时，索引可以大大地提高查询速度。
建立索引不仅能提高查询速度，在恢复数据库的数据时，也能提高性能。
可以加快表与表之间连接查询的速度。
索引的副作用

创建的索引也需要占用额外的磁盘空间。INNODB存储引擎的表数据文件和索引文件在一块，相对占的空间小一点。
更新一个包含索引的表，比没有索引的表需要花费更多的时间。表要更新，索引也要更新，所以速度要慢一些。

1.3 创建索引的原则
表的主键和外键必须有索引。主键是唯一的，外键是关联主表的，查询时可以快速定位。
一般的，一张表有超过300行的数据，应该要创建索引。
经常与其他表进行连接的表，在连接字段上应该创建索引。
更新太频繁的字段不适合创建索引。
经常作为where语句条件列的字段，应该建立索引
经常使用group by 和 order by的字段上应该建立索引。
选择一个性能高的字段作为索引（重复量少）
索引应该建立在小字段上（字段长度较短）

1.4 索引的类型
1、 b-tree 索引 ：(b-树索引)
绝大部分的数据都是使用b-tree索引。b-tree索引是一个典型的数据结构，其包含的组件主要有以下几个：

叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。

分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。

根节点：一个 b-tree索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

b-tree索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 b-tree索引必须遵循左边前缀原则，要考虑以下几点约束：

查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。
 2、 hash索引 ：哈希索引
根据索引对应的hash值的方法获取表的记录行，速度较慢。

二、创建索引（b-tree）

2.1 创建索引的语句

方法一：建表的时候创建索引index

 remark text ：text也是数据类型的一种，和char，varchar性质相同，都是字符串，text不需要长度的参数，可以作为大文本的列，可以存储65535个字符。
index name_index (name) ：创建索引的格式
index ：创建索引
name_index ：索引的名称，定义是一般列名在前，index在后，下划线连接
(name) ：创建索引的列
方法二：向表中添加索引

2.2 删除索引

删除索引删除的是索引对应的Key_name，可以用drop和alter删除：

2.3 主键索引/唯一键索引

主键(PRIMARY KEY)和唯一键(UNIQUE KEY)不需要额外声明，创建时系统自动添加索引，并自动为索引命名。

2.4 唯一索引

唯一索引 ：unique index ，不允许索引的值重复，可以用key的方式创建，也可以用索引的方式创建.

2.5 组合索引（联合索引）

一次性给多个列创建索引，形成一个组合

 

使用联合索引，需要同时查询联合索引所在的所有列。constraint 约束名 unique (列名) ：这样的形式定义了一个唯一性的联合约束。这样多列的唯一性约束要求不能所有被约束的列的内容完全一致，但是可以部分一致。比如三列联合约束，仅有两列的内容一致，则不会报错；如果存在这三列数据全都一致的行，则会报错。

2.6 全文索引

适用于模糊查询，检索大文本使用的。

 

 查看索引会发现，索引类型不是b-tree，是fulltext，对全文索引查询的方式有一些区别

MATCH(remark): MATCH() 是 MySQL 中用来执行全文搜索的函数。在这里，它指定了要搜索的列是 remark 列。
AGAINST(‘vip’): AGAINST() 是 MATCH() 函数的一部分，用于指定要搜索的文本或表达式。在这里，它表示搜索包含单词 ‘vip’ 的记录。

MySQL事务
 一、什么是事务
1.1 事务的介绍
事务是一个机制，一个操作序列，直白点说就是一组或一条数据库的操作命令（SQL语句）。

把所有的命令作为一个整体向系统提交或者撤销的操作，要么都成功，要么都失败（回滚到提交前的状态）。

事务是一个不可分割的工作逻辑单元，在数据库上执行并发操作时，事务是最小的控制单元。

数据库通过事务的控制和事务的整体性保持数据的一致性。

1.2 事务的特性：ACID

在数据库的管理系统中，事务的特性有ACID这四种：

• A ：Atomicity ：原子性
• C ：Consistency ：一致性
• I ：Isolation ：隔离性
• D ：Durability ：持久性

ACID的具体解释：

原子性 ：事务是最小的控制单元，不可分割。事务中的所有操作要么全部完成，要么全部不完成。如果事务中任何一个操作失败，整个事务将被回滚（Rollback）到最初状态，所有的操作都不会生效。
一致性 ：事务开始之前和事务结束之后，数据的完整性没有被破坏。开始的时候数据时一致的，结束的时候数据也是一致的。这意味着事务执行后，数据库从一个一致性状态转移到另一个一致性状态。在事务进行的时候，数据可以处于不一致的状态，但是一旦事务结束，数据必须回到一致性。
隔离性 ：事务的执行不受其他事务的干扰。即使在并发执行环境下，一个事务的执行也不会受其他事务的影响。不同的事务同时操作相同的数据时，每个事务都有自己完整的数据空间——对数据的修改所发生的并发事务是隔离的。这可以通过各种隔离级别来控制。
持久性 ：一旦事务提交，它对数据库的改变是永久性的，即使系统崩溃，数据库也能够通过日志文件进行恢复，保证数据的持久性。
 1.3 数据库的隔离级别
未提交读：Read Uncommitted ，RU
允许脏读，即一个事务可以看到其他事务未提交的修改。
提交读：Read Committed ，RC
一个事务只能看到其他事务已经提交的修改，未提交的修改不可见，防止脏读。如oracle和sql server 都是RC。
可重复读：Repeatable Read ， RR
一个事务在执行中，执行两次相同的select语句，得到的结果都是相同的。RR是MySQL的默认隔离选项，防止脏读和不可重复读。
串行读：Serializable
相当于锁表，完全串行化的读，一个事务在使用，其他事务的读写都会阻塞。即不能并发读取数据。
 1.4 非可重复读的级别下可能出现的情景

一般可重复度(RR)隔离级别的数据库不会出现以下奇葩的情景，但是在非可重复读的隔离级别下，可能会有一些出乎意料的情景，在**提交读(RC)**级别下尤甚。

1. 脏读

一个事务可以看到其他事务未提交的修改。这是在RU级别下。

不可重复读
在一个事务内，多次读同一数据，一个事务没有结束，另外一个事务也访问该数据（对数据进行了修改），前一个事务读取的结果不一致。

不可更新
两个事务同时对数据进行修改，但是一方先提交，一方后提交，后提交的会覆盖先提交的。

幻读
一个事务对一个表的数据进行了修改，另一个事务也修改了表中的数据，前一个事务的修改被后一个事务的修改覆盖了，导致前一个事务发现自己的修改消失了，像出现了幻觉。

2/3/4的情况在**提交读(RC)**级别下完全可能出现，万一碰到了需要注意甄别。
 

二、事务的控制语句

开启一个事务

 提交事务

开启和提交事务范例：

提交事务后，修改写入数据库中，此时再次运行select * from cost;会得到修改后的结果。如果只是修改而没有提交，重启数据库后，查表会发现修改没有写入数据库，表的值还是一开始的值。（即张三的money=10）

设置回滚点：（类似于设置快照）

回滚：（类似于读取快照）

回滚的注意事项：

已提交的事务无法被回滚，事务提交后，事务内的回滚点自动失效
回滚到某一个回滚点后，在这个回滚点之后创建的回滚点都会失效
**不指定回滚点时，默认行为是回滚整个当前的事务。**这意味着，MySQL 将会撤销自上次 BEGIN 或 START TRANSACTION 开始以来所有未提交的更改。
 

在update cost set money=money+1 where id=5;之后：

可以先回滚到s4，还可以再回滚到s3，再回滚到s2，再回滚到s1；

但如果直接回滚到s2，则不能再回滚到s3，s4，因为这两个回滚点在s2之后，已经在回滚的过程中消失了，但还可以回滚到s1,；

如果直接rollback，则所有事务的改动全部消失，表变为一开始的状态。

如果直接commit，则不能再回滚。