MySQL之索引、事务详解

目录

一、索引介绍

1.1 索引的概念

1.2 索引的作用

1.3 索引的副作用

1.4 创建的原则（应用场景）

1.5 MySQL的优化（哪些字段/场景适合创建索引)

1.6 索引的分类

1.6.1 普通索引

1.6.2 唯一索引

1.6.3 主键索引

1.6.4 组合索引

1.6.5 全文索引

1.7 删除索引

二、事务介绍

2.1 事务的概念

2.2 事务的四个特性

---

一、索引介绍

1.1 索引的概念

数据库索引是一个排序的列表，一种帮助系统更快查找信息的数据，存储着索引值和这个值所对应的物理地址。无须对整个表进行扫描，通过物理地址就可以找到所需数据，是表中一列或者若干列值排序的方法，需要额外的磁盘空间。

1.2 索引的作用

数据库利用各种快速定位技术，能够大大加快查询速率当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度。可以降低数据库的IO成本，并且还可以降低数据库的排序成本。通过创建唯一性索引保证数据表数据的唯一性，可以加快表与表之间的连接。在使用分组和排序时，可大大减少分组和排序时间。

1.3 索引的副作用

索引需要占用额外的磁盘空间。对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。而InnoDB 引擎的表数据文件本身就是索引文件。当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度。在插入和修改数据时要花费更多的时间，因为索引也要随之变动。

1.4 创建的原则（应用场景）

• 表的主键、外键必须有索引
• 记录数超过300行的表应该有索引
• 经常与其他表进行连接的表，在连接字段上应该建立索引
• 唯一性太差的字段不适合建立索引
• 更新太频繁地字段不适合创建索引
• 经常出现在where子句中的字段，特别是大表的字段应该建立索引
• 索引应该建在选择性高的字段上
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

1.5 MySQL的优化（哪些字段/场景适合创建索引)

• 小字段
• 唯一性强的字段
• 更新不频繁，但查询率很高的字段
• 表记录超过300+行
• 主键、外键、唯一键

1.6 索引的分类

1.6.1 普通索引

最基本的索引类型，没有唯一性之类的限制

1.6.2 唯一索引

针对唯一性的字段，仅允许出现一次空值

1.6.3 主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。

1.6.4 组合索引

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的where条件是依次从左往右执行的，所以在使用select语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

1.6.5 全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。

1.7 删除索引

1. drop index 索引名 on 表名；
2. alter table 表名 drop 索引名;

二、事务介绍

2.1 事务的概念

MySQL事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如文章。这些数据库操作语句就构成一个事务。

事务是一种机制，一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。

2.2 事务的四个特性

• 原子性：指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
• 一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有背破坏。
• 隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。
• 持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。