初识MySQL--索引与事务

拓展

在开始了解索引之前，得学会安装 MySQL 和它的一些基本的命令，可以看看之前的文章：

• 在Centos7下安装Mysql源码编译安装5.7版本
  
• 初识MYSQL–MYSQL基本操作
  
• Mysql增删改查|SQL语句（史上最全|实战教学）
  

索引及分类

1·索引的概念：

• 什么是索引，不用专业术语来描述，通俗的来讲，就是一本书的目录，或一本小说的目录，有了目录就可以快速定位我需要找到的内容。专业点的来讲，就是为了提高数据库的搜索效率而对某写字段中的值建立的目录。

2·索引的作用

• 知道了它的概念，那么作用不言而喻。但是有利有弊，得到什么就必须牺牲什么，索引使我们可以加快查询表中的记录，那么也会付出相对应的代价：1·增加了数据库的存储空间；2·在插入和修改数据时要花费较多的时间。

以上是代价，但是相比代价，索引的作用更为突出：

• 1·最主要的就是有了索引就会加快数据查询速度
• 2·当库中的表很大时，索引的作用就体现的非常明显，一个字’快‘
• 3·降低数据库的 I0 成本，就是输出和输入成本
• 4·创建唯一性索引，可以保持数据库表中每一行数据的唯一性
• 5·加快表与表之间的连接

3·索引的分类

• 普通索引：这是最基本的索引类型，没有唯一性限制
• 唯一性索引：和普通索引基本相同，唯一的区别是索引列的所有值只能是唯一的
• 主键索引：是唯一索引的特定类型，它必须指定为“PRIMARY KEY”。在数据库中为表定义主键将自动创建主键索引。
• 全文索引，索引类型为FULLTEXT,全文索引可以在CHAR、VARCHAR的列上创建
• 单列索引与多列索引：索引可以是单列上创建的索引，可以是多列上创建的索引

4·创建索引的原则依据

凡是有规矩，创建索引也必须有条件的，不能乱创建，乱创建反而会增加数据库的负担，因为索引也会消耗系统资源。以下是一些创建的依据

• 表的主键、外键必须有索引
• 数据量超过2000 行的表应该有索引
• 经常与其他表进行连接的表，在连接字段上需要索引
• 唯一性太差的字段不适合建立索引
• 更新频繁的字段不适合创建
• 经常出现在 where 子句中的字段，需要建立
• 应该建立选择性较高的字段上
• 可以在小字段上建立索引

索引详细信息表

创建及查看索引

1·创建普通索引：

create index 索引名字 on 表名（列名）；
1

索引已经创建成功了，我们再来查看你创建的索引格式。

2·创建唯一性索引：

创建唯一性索引、并且查看，以下图为例

语法 ： CREATE UNIQUE INDEX 索引名 ON 表名(索引字段);
1

例：

CREATE UNIQUE INDEX unique_index ON student(id);
# 创建唯一索引
SHOW INDEX FROM student;
# 查看索引信息
1
2
3
4

3 创建主键索引

它有两种方式，一种是创建表的同时就创建了主键，一种是创建表时没有指定主键，这里演示第二种，它的命令格式是：

语法：ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
1

例：

ALTER TABLE student ADD PRIMARY KEY(id);
# 将student这个表的id字段修改成主键索引
SHOW INDEX FROM student;
# 查看它的索引信息
1
2
3
4

4· 删除索引

创建了索引，但是在不需要的时候需要删除，下面是演示删除索引；

drop  index id_index on student（表名）; -----（直接删除索引）                 
alter table student（表名） drop index unique_index; ----- （修改表时删除索引）
alter table seudent（表名） drop primary key; ----- (删除主键索引)             
1
2
3

事务的概念及特点

事务的概念

• 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体以起向系统提交或撤销操作请求，通俗的来说就是这一组命令要么都执行，要么不执行。
• 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
• 事务适用于用户同时操作数据库系统的场景，比如：银行、保险公司、证券交易系统等，通过事务的整体性保证数据的一致性
• 事务是保持了一组操作的平稳性和可预测性的技术。

总结一下：你执行的一系列操作，要么完整的执行，要么完全不执行。一般情况下，操作顺利进行，最终操作成功。但是如果在这一系列过程中任何一个环节除了差错，数据库中的所有信息都必须保持在你开始操作前的状态。否则，数据库的信息将会一片混乱而不可预测。

事务的 四大特性

1·事务的原子性：事务时一个完整的操作，各个元素是不可分的。
2·事务的一致性：当完成时，数据必须处于一致状态；在事务开始之前，数据库中储存的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务完成时，数据必须再次回到已知的一致状态
3·隔离性：对数据进行修改的所有并发事务时彼此隔离的，这表名事务必须时独立的，它不会以任何方式依赖于或影响其他事务。
4·持久性：不管系统是否发送了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。

MySQL事务操作的两种方法

• 使用事务处理命令控制
• 使用set设置事务的处理方式

事务处理命令控制：

1·事务处理命令控制：

• begin ：表示开始一个事务，后面会有多条数据库操作语句执行。
• commit：表示提交一个事务，对应前面的begin操作，他们之间的数据库操作语句以起完成
• rollback：表示回滚一个事务，在begin和commit之间，如果某一个数据库操作语句出现错误，执行 rollback 回滚，数据库就会回到begin之前的状态，也就是操作语句都没执行。

事务处理命令控制举例

1·事务提交范例

开始事务

2·事务回滚范例

开始事务

然后插入一条事务

我们现在将这个事务开始回滚。

3.rollback 定义回滚点

• 很多时候一个事务会包含很多条语句，而出现问题需要回滚时，不一定就直接回到起点，这样的话之前写的语句就全都白费了，那么这时候就要使用 savepoint 定义回滚点，这个回滚点就相当于 VMware 虚拟机中拍个快照，有这么一个意思。

 begin;
 #开始事务
 insert into student values(27,'郑从宇','18');
 #插入一条数据
 savepoint s1;
 #将当前操作设置回滚点名为S1
 insert into student values(35,'zcy','27');
 #再插入一条数据
 savepoint s2;
 #将当前操作设置回滚点名为S1
 rollback to savepoint s1;
 #将事务回滚到S1的操作
 select * from student;
 #查看结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14

savepoint 举例：