MySQL 索引与事务
1、索引概述
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引是对查询性能优化最有效的手段能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
1.1 索引的作用
建立索引的目的是加快对表中记录的查找或排序。
索引的优点：
- 建立索引的列可以保证行的唯一性，生成唯一的row Id
- 建立索引可以有效缩短数据的检索时间
- 建立索引可以加快表与表之间的连接
- 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
索引的缺点：
- 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
- 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
- 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长
1.2 索引的使用场景
数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以合理的使用索引提升查询效率。根据业务的查询语句，针对性的设置索引，不要盲目给所有列设置索引，索引虽然能够提高查询速度，但是对写会有影响。
1.3 索引的分类
基本索引类型：
- 普通索引（单列索引）
- 复合索引（组合索引）
- 唯一索引
- 主键索引
- 全文索引
2、索引的创建、查询和删除
2.1 普通索引
普通索引（单列索引）：单列索引是最基本的索引，它没有任何限制。
//先进入hzz库
MariaDB [(none)]> use hzz;
//直接创建普通索引
MariaDB [hzz]> CREATE INDEX putong1 ON hzztable(name);
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
//修改表结构方式添加索引
MariaDB [hzz]> ALTER TABLE hzztable ADD INDEX putong2(age);
Query OK, 0 rows affected (0.003 sec)
Records: 0 Duplicates: 0 Warnings: 0
//创建表时同时创建索引
MariaDB [hzz]> CREATE TABLE zzhtable(id int(7) not null,name varchar(20) not null,age tinyint,INDEX putong3(name(20)));
Query OK, 0 rows affected (0.002 sec)
//查看表中索引
MariaDB [hzz]> SHOW INDEXES FROM zzhtable \G;
*************************** 1. row ***************************
Table: zzhtable
Non_unique: 1
Key_name: putong3
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.000 sec)
//删除zzhtable表中名为putong3的索引
MariaDB [hzz]> DROP INDEX putong3 ON zzhtable;
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
//第二种删除索引的方式
MariaDB [hzz]> ALTER TABLE hzztable DROP INDEX putong1;
MariaDB [hzz]> ALTER TABLE hzztable DROP INDEX putong2;
2.2 复合索引
复合索引：复合索引是在多个字段上创建的索引。复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。因此，在复合索引中索引列的顺序至关重要。
//为zzhtable表的三列创建复合索引
MariaDB [hzz]> CREATE INDEX fuhe1 ON zzhtable(id,name,age);
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hzz]> SHOW INDEXES FROM zzhtable \G;
*************************** 1. row ***************************
Table: zzhtable
Non_unique: 1
Key_name: fuhe1
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: zzhtable
Non_unique: 1
Key_name: fuhe1
Seq_in_index: 2
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: zzhtable
Non_unique: 1
Key_name: fuhe1
Seq_in_index: 3
Column_name: age
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.000 sec)
//记得删除索引恢复环境
2.3 唯一索引
唯一索引：唯一索引和普通索引类似，主要的区别在于，唯一索引限制列的值必须唯一，但允许存在空值（只允许存在一条空值）；对于多列创建唯一索引，多个列值的组合必须唯一。
//为zzhtable表中的'id'列添加唯一索引
MariaDB [hzz]> CREATE UNIQUE INDEX weiyi1 ON zzhtable(id);
Query OK, 0 rows affected (0.004 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hzz]> SHOW INDEXES FROM zzhtable \G;
*************************** 1. row ***************************
Table: zzhtable
Non_unique: 0
Key_name: weiyi1
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.000 sec)
//为zzhtable表中的'name'和'age'两列添加唯一索引
MariaDB [hzz]> CREATE UNIQUE INDEX weiyi2 ON zzhtable(name,age);
Query OK, 0 rows affected (0.002 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hzz]> SHOW INDEXES FROM zzhtable \G;
*************************** 1. row ***************************
Table: zzhtable
Non_unique: 0
Key_name: weiyi1
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: zzhtable
Non_unique: 0
Key_name: weiyi2
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: zzhtable
Non_unique: 0
Key_name: weiyi2
Seq_in_index: 2
Column_name: age
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.000 sec)
//删除唯一索引
MariaDB [hzz]> drop index weiyi1 on zzhtable;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [hzz]> drop index weiyi2 on zzhtable;
Query OK, 0 rows affected (0.012 sec)
Records: 0 Duplicates: 0 Warnings: 0
2.4 主键索引
主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引。
//创建zztable表时给'id'列设置主键索引
MariaDB [hzz]> CREATE TABLE zztable(id int(7) NOT NULL,name varchar(20) NOT NULL,age tinyint,PRIMARY KEY(id));
Query OK, 0 rows affected (0.005 sec)
//也可创建表后添加主键索引
MariaDB [hzz]> ALTER TABLE zztable ADD PRIMARY KEY(id);
MariaDB [hzz]> SHOW INDEXES FROM zztable \G;
*************************** 1. row ***************************
Table: zztable
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.000 sec)
2.5 全文索引
全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较；全文（fulltext）跟其它索引大不相同，它更像是一个搜索引擎，目前只有char、varchar、text列上可以创建全文索引。
//给zztable表中的'name'列创建名为quanwen1的全文索引
MariaDB [hzz]> CREATE FULLTEXT INDEX quanwen1 ON zztable(name);
Query OK, 0 rows affected (0.036 sec)
Records: 0 Duplicates: 0 Warnings: 0
//修改表时添加全文索引
MariaDB [hzz]> ALTER TABLE zztable ADD FULLTEXT quanwen2(name);
//创建表时添加全文索引
MariaDB [hzz]> CREATE TABLE zztable(id int(7) NOT NULL,name varchar(20) NOT NULL,age tinyint,PRIMARY KEY(id),FULLTEXT quanwen(name));
MariaDB [hzz]> SHOW INDEXES FROM zztable \G;
*************************** 1. row ***************************
Table: zztable
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: zztable
Non_unique: 1
Key_name: quanwen1
Seq_in_index: 1
Column_name: name
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: FULLTEXT
Comment:
Index_comment:
*************************** 3. row ***************************
Table: zztable
Non_unique: 1
Key_name: quanwen2
Seq_in_index: 1
Column_name: name
Collation: NULL
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: FULLTEXT
Comment:
Index_comment:
3 rows in set (0.000 sec)
注意：有两种情况会导致索引失效：①如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引；②在where后使用or，导致索引失效（尽量少用or）。
3、事务概述
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)；在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。
3.1 事务的作用
事务处理用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行；一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成，事务只和DML语句有关。
3.2 事务四大特征
一般来说，事务是必须满足4个条件：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。(比如：A向B转账，不可能A扣了钱，B却没有收到)
-
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。（比如：A正在从一张银行卡里面取钱，在A取钱的过程中，B不能向这张银行卡打钱）
-
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
3.3 事务隔离级别
数据库系统提供了四种事务隔离级别：
- Read Uncommitted（读未提交数据）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新。
- Read Commited（读已提交数据）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新。
- Repeatable Read（可重复读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新。
- Serializable（串行化）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新（事务执行的时候不允许别的事务并发执行；事务串行化执行，事务只能一个接着一个地执行，而不能并发执行）
3.4 事务提交，回滚操作
事务提交：
//进入hzz数据库
MariaDB [(none)]> use hzz;
//创建test表
MariaDB [hzz]> CREATE TABLE test(id int(7) NOT NULL,name varchar(20) NOT NULL,age tinyint,PRIMARY KEY(id),FULLTEXT quanwen(name),INDEX putong(age));
//向表中插入数据
MariaDB [hzz]> INSERT INTO test(id,name,age) VALUES(1909001,'zhangsan',18),(1909002,'lisi',22),(1909003,'wangwu',25);
Query OK, 3 rows affected (0.001 sec)
Records: 3 Duplicates: 0 Warnings: 0
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
+---------+----------+------+
3 rows in set (0.000 sec)
//手动开启事务
MariaDB [hzz]> start transaction;
Query OK, 0 rows affected (0.000 sec)
//向表中插入新的数据
MariaDB [hzz]> INSERT INTO test(id,name) VALUE(1909004,'chenliu');
Query OK, 1 row affected (0.000 sec)
//提交事务，此时事务开启到提交所有流程正确，提交成功
MariaDB [hzz]> commit;
Query OK, 0 rows affected (0.001 sec)
//查看结果
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
+---------+----------+------+
4 rows in set (0.000 sec)
事务回滚：
//手动开启事务
MariaDB [hzz]> start transaction;
Query OK, 0 rows affected (0.000 sec)
//向表中插入新的数据
MariaDB [hzz]> INSERT INTO test(id,name) VALUE(1909005,'wuqi');
Query OK, 1 row affected (0.000 sec)
//此时表中数据已经插入，假如此时数据有问题
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
+---------+----------+------+
5 rows in set (0.000 sec)
//进行回滚
MariaDB [hzz]> rollback;
Query OK, 0 rows affected (0.001 sec)
//回滚之后刚才插入的数据消失
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
+---------+----------+------+
4 rows in set (0.000 sec)
3.4 事务保存点，回滚操作
//test表中数据
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
+---------+----------+------+
4 rows in set (0.000 sec)
//开启事务
MariaDB [hzz]> start transaction;
Query OK, 0 rows affected (0.000 sec)
//插入一行数据
MariaDB [hzz]> INSERT INTO test(id,name) VALUE(1909005,'wuqi');
Query OK, 1 row affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
+---------+----------+------+
6 rows in set (0.000 sec)
//设置保存点名为'one'
MariaDB [hzz]> savepoint one;
Query OK, 0 rows affected (0.000 sec)
//再插入一行数据
MariaDB [hzz]> INSERT INTO test(id,name,age) VALUE(1909006,'zhouba',19);
Query OK, 1 row affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
| 1909006 | zhouba | 19 |
+---------+----------+------+
6 rows in set (0.000 sec)
//设置保存点名为'two'
MariaDB [hzz]> savepoint two;
Query OK, 0 rows affected (0.000 sec)
//插入第三个数据
MariaDB [hzz]> INSERT INTO test(id,name,age) VALUE(1909007,'zhaojiu',21);
Query OK, 1 row affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
| 1909006 | zhouba | 19 |
| 1909007 | zhaojiu | 21 |
+---------+----------+------+
7 rows in set (0.000 sec)
//设置保存点名为'three'
MariaDB [hzz]> savepoint three;
Query OK, 0 rows affected (0.000 sec)
//插入第四个数据
MariaDB [hzz]> INSERT INTO test(id,name,age) VALUE(1909008,'huangshi',23);
Query OK, 1 row affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
| 1909006 | zhouba | 19 |
| 1909007 | zhaojiu | 21 |
| 1909008 | huangshi | 23 |
+---------+----------+------+
8 rows in set (0.000 sec)
//回滚只能顺序回滚到保存点，不能跳跃回滚
//先回滚至'three'
MariaDB [hzz]> rollback to three;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
| 1909006 | zhouba | 19 |
| 1909007 | zhaojiu | 21 |
+---------+----------+------+
7 rows in set (0.000 sec)
//回滚至'two'
MariaDB [hzz]> rollback to two;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
| 1909006 | zhouba | 19 |
+---------+----------+------+
6 rows in set (0.000 sec)
//回滚至'one'
MariaDB [hzz]> rollback to one;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
| 1909005 | wuqi | NULL |
+---------+----------+------+
5 rows in set (0.000 sec)
//回滚到最初状态
MariaDB [hzz]> rollback;
Query OK, 0 rows affected (0.001 sec)
MariaDB [hzz]> SELECT * FROM test;
+---------+----------+------+
| id | name | age |
+---------+----------+------+
| 1909001 | zhangsan | 18 |
| 1909002 | lisi | 22 |
| 1909003 | wangwu | 25 |
| 1909004 | chenliu | NULL |
+---------+----------+------+
4 rows in set (0.000 sec)
3.5 事务自动提交
//查看事务自动提交是否开启
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.004 sec)
//关闭事务自动提交
MariaDB [(none)]> SET autocommit=0;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.001 sec)
//开启事务自动提交
MariaDB [(none)]> SET autocommit=1;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]>
MariaDB [(none)]> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.001 sec)