MySQL 索引和事务

目录

一、MySQL 索引介绍

1、索引概述

2、索引作用

3、索引的分类

（1）普通索引

（2）唯一索引

（3）主键索引

（4）组合索引（最左前缀）

（5）全文索引（FULLTEXT）

（4）创建索引的原则依据

（5）查看索引

（6）删除索引

二、MySQL 事务

---

一、MySQL 索引介绍

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，索引可以大大加快查询的速度。这是因为使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据。索引的作用类似于图书的目录，可以根据目录中的页码快速找到所需的内容。

1、索引概述

当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点(或数据块)的指针，不需要连续存储。

记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索(Linear Search)的过程,平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说，不包含唯一的访问入口)，那么需要在N个数据块上搜索整个表格空间。

但是对于一个有序字段，可以运用二分查找(BinarySearch)，这样只需要访问 log2(N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。

索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建个索引，将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。

使用索引的副作用是需要额外的磁盘空间。对于 MyISAM 引擎而言，这些索引是被统一保存在一张表中的。如果很多字段都建立了索引，那么会占用大量的磁盘空间，这个文件将很快到达底层文件系统所能够支持的大小限制。

2、索引作用

在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时, 使用索引往往能使查询速度加快成千上万倍。

例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,均为1~1000的数值,查找对应值相等行的查询 如下所示。

mysqI> SELECT c1, c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3;

此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000X1000X1000(十亿),显然查询将会非常慢。

如果对每个表进行索引,就能极大地加速查询进程,利用索引的查询处理如 下。

从表t1中选择第一行,查看此行所包含的数据。

使用表t2上的索引,直接定位t2中与t1的值匹配的行。同理,利用表t3 上的索引,直接定位t3中与t1的值匹配的行。

扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。

在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3 上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。

利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时、在执行连接时加快了与其他表中的行匹配的速度。

3、索引的分类

在数据库表中,对字段建立索引可以大大提高查询速度。通过善用这些索引可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是非常重要的。

从物理存储的角度来划分,索引分为聚簇索引和非聚簇索引。

聚簇索引是按照数据存放的物理位置为顺序的,而非聚簇索引就不一样了;聚簇索引能提 高多行检索的速度,而非聚簇索引对于单行的检索更快。

从逻辑的角度来划分,索引分为普通索引、唯一索引、主键索引、组合索引 和全文索引。

（1）普通索引

普通索引是最基本的索引,它没有任何限制,也是大多数情况下用到的索引。它有以下几种创建方式。

直接创建索引：

CREATE INDEX index_name ON table_name(column(length));

column是指定要创建索引的列名。通常可以考虑将查询语句中在JOIN子句和 WHERE子句里经常出现的列作为索引列。

length是可选项。如果忽略length的值,则使用整个列的值作为索引。如果指定使用列前的length个字符来创建索引,就是使用列的一部分来创建索引,这样有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下, 只能对列的前缀进行索引。索引列的长度有一个最大上限255个字节(MyISAM 和InnoDB表的最大上限为1000个字节),如果索引列的长度超过了这个上限, 就只能用列的前缀进行索引。另外,BLOB或TEXT 类型的列也必须使用前缀索引。column和length的含义,在下面创建索引的操作语句中意义相同。

mysql> create database auth;    #创建库
mysql> use auth;    #进入库
mysql> create table users (id int(10),user_name char(20),user_pass char(50));    #创建表
mysql> create index aaa on users(user_name(20));    #创建索引

修改表结构的方式添加索引：

ALTER TABLE table_name ADD INDEX index_name(column(length));

mysql> alter table users add index bbb (user_pass(50));

创建表结构时，同时创建索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

INDEX index_name (title(length)));

（2）唯一索引

唯一索引与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值(注意和主键不同)。如果是组合索引，则列值的组合必须唯一。唯一索引创建方法和普通索引类似。

创建唯一索引：

 CREATE UNIQUE INDEX index_name ON table_name(column(length));

mysql> create unique index ccc on users(id);

修改表结构的时候添加唯一索引：

ALTER TABLE table_name ADD UNIQUE index_name(column(length));

mysql> alter table  users add unique ddd(user_name);

创建表的时候同时创建唯一索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

UNIQUE indexName (title(length)));

（3）主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) NOT NULL,

PRIMARY KEY ('id'));

mysql> create table t1 (id int(10),title char(25),time int(10),primary key (id),index aaa (title(25));

（4）组合索引（最左前缀）

平时用的 SQL 查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL 的效率，就要考虑建立组合索引。在组合索引的创建中，有两种场景，即为单列索引和多列索引。下面通过一个场景来具体说明单列索引和多列索引。

在一个 t2 表中，有name，age，sex 三个字段，分别分三次建立了INDEX 普通索引。那么在 select * from t2 where name =’’ AND age =‘’ AND sex=’’；数据查询语句中就会分别检索三条索引，虽然扫描效率有所提升但却还未达到最优。这个时候就需要使用到组合索引(即多列索引)，如下所示。

mysql> create table t2 (name char(10),age int(3),sex tinyint(1),index ddd(name,age,sex));

在 MySQL 中，有一个知识点叫最左原则。下面的 select 语句的 where 条件是依次从左往右执行的。

mysql> select * from t2 where name='' and age='' and sex='';

若使用的是组合索引 index t2(name，age，sex)。在查询中，name、age、sex 的顺序必须如组合索引中一致排序，否则索引将不会生效，例如：

mysql> select * from t2 where age='' and name='' and sex='';

如果采用以上查询方式，这条组合索引将无效化，所以一般在建立索引时，要先想好相应的查询业务，尽量避免虽然有索引，但是使用不上的问题。

（5）全文索引（FULLTEXT）

对于较大的数据集，将资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间、非常消耗硬盘空间的做法。

创建表的全文索引：

CREATE TABLE 'table' (

'id' int(11) NOT NULL AUTO_INCREMENT,

'title' char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,

'content' text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,

'time' int(10) NULL DEFAULT NULL,

PRIMARY KEY ('id'),

FULLTEXT (content));

修改表结构添加全文索引：

ALTER TABLE article ADD FULLTEXT index_content(content);

直接创建索引：

CREATE FULLTEXT INDEX index_content ON article(content);

mysql> create fulltext index eee on users(user_name);

（4）创建索引的原则依据

数据库建立索引的原则：

• 确定针对该表的操作是大量的查询操作还是大量的增删改操作。
• 尝试建立索引来帮助特定的查询。检查自己的sql语句，为那些频繁在where 子句中出现的字段建立索。
• 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间同时复合索引也占磁盘空间。
• 对于小型的表，建立索引可能会影响性能。
• 应该避免对具有较少值的字段进行索引。
• 避免选择大型数据类型的列作为索引。

索引建立的原则：

索引查询是数据库中重要的记录查询方法，要不要建立索引以及在那些字段上建立索引都要和实际数据库系统的查询要求结合来考虑，下面给出实际生产环境中的一些通用的原则：

• 在经常用作过滤器的字段上建立索引。
• 在 SQL 语句中经常进行 GROUP BY、ORDER BY的字段上建立索引。
• 在不同值较少的字段上不必要建立索引，如性别字段。
• 对于经常存取的列避免建立索引。
• 用于联接的列(主健/外健)上建立索引。
• 在经常存取的多个列上建立复合索引，但要注意复合索引的建立顺序要按照使用的频度来确定。
• 缺省情况下建立的是非簇集索引，但在以下情况下最好考虑簇集索引，如：含有限数目(不是很少)唯一的列;进行大范围的查询;充分的利用索引可以减少表扫描 I/0的次数，有效的避免对整表的搜索。当然合理的索引要建立在对各种查询的分析和预测中，也取决于 DBA 所设计的数据库结构。

（5）查看索引

show index ftom tablename\G

show keys ftom tablename\G

mysql> show index from t1\G;
mysql> show keys ftom t1\G

以 t1 表为例，查看t1 表的索引内容

mysql> show index from t1\G;
*************************** 1. row ***************************Table: t1Non_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment: 
Index_comment: Visible: YESExpression: NULL

字段解析：

Table	表的名称。
Non_unique	如果索引不能包括重复词，则为0;如果可以，则为1。
Key name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。
Column_name	列名称。Collation:列以什么方式存储在索引中。在 MySQL 中，有值'A’(升序)或 NULL(无分类)。
Cardinality	索引中唯一值数目的估计值。通过运行 ANALYZE TABLE 或myisamchk-a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为NULL。Null:如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type	用过的索引方法(BTREE,FULLTEXT, HASH,RTREE)
Comment	备注。

（6）删除索引

索引在创建之后,是会占用一定的磁盘空间的,因此表内如果有不再使用的索引从数据库性能方面考虑，最好是删除无用索引。

DROP INDEEX 索引名 ON 表名;

ALTEER TABLE 表名 DROP INDEX 索引名;

mysql> drop index aaa on users;
mysql> alter table users drop index bbb;

二、MySQL 事务

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

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性,保证成批的SQL 语句要么全部执行要么全部不执行。
• 事务用来管理 insert,update,delete 语句

一般来说，事务是必须满足4个条件(ACID)：原子性(Atomicity，或称不可分割性)、一致性(Consistency)、隔离性(Isolation，又称独立性)、持久性(Durability)。

• 原子性：一个事务(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rol1back)到事务开始前的状态，就像这个事务从来没有执行过一样;
• 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作;
• 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交(Readuncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable);
• 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

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

事务控制语句包含：

BEGIN或START TRANSACTION	显式地开启一个事务;
COMMIT	也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改变为永久性的;
ROLLBACK	又可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改;
SAVEPOINT identifier	SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT;
RELEASE SAVEPOINT identifier	删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常;
ROLLBACK TO identifier	把事务回滚到标记点;
SET TRANSACTION	用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

MYSQL 事务处理主要有两种方法：

(1)用 BEGIN，ROLLBACK，COMMIT 来实现

BEGIN        #开始一个事务
ROLLBACK        #事务回滚
COMMIT        #事务确认

(2)直接用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0        #禁止自动提交
SET AUTOCOMMIT=1        #开启自动提交

示例：

mysql> select * from users;    #查看表内容
Empty set (0.00 sec)mysql> insert into users values(1,'zhangsan','111');    #添加表内容
Query OK, 1 row affected (0.01 sec)mysql> select * from users;    #查看是否有内容
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)mysql> rollback;    #回滚
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;		#查看
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)mysql> set autocommit=0;		#关掉自动提交
Query OK, 0 rows affected (0.00 sec)mysql> insert into users values(2,'lisi','111');
Query OK, 1 row affected (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from users;
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
+------+-----------+-----------+
1 row in set (0.00 sec)

mysql> begin;	#事务的开始
Query OK, 0 rows affected (0.00 sec)mysql> insert into users values(4,'wangwu','111');
Query OK, 1 row affected (0.00 sec)mysql> commit;    #事务的提交
Query OK, 0 rows affected (0.01 sec)mysql> select * from users;
+------+-----------+-----------+
| id   | user_name | user_pass |
+------+-----------+-----------+
|    1 | zhangsan  | 111       |
|    4 | wangwu    | 111       |
+------+-----------+-----------+
2 rows in set (0.00 sec)