MySQL数据库的索引与事务

一、数据库索引

1.1、索引及分类

1.1.1、索引的概念

索引是一种特殊的文件，包含着对数据表中所有记录的引用指针。更通俗地说，数据库索引好比是本书前面的目录，能加快数据库的查询速度。例如需要遍历200条数据，在没有索引的情况下，数据库会遍历全部200条数据后选择符合条件的:而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

1.1.2、索引的作用

• 设置了合适的索引之后，数据库利用各种快速的定位技术，可以大大加快数据的查询速度，这也是创建索引最主要的原因。
• 当表很大时，或者查询涉及多个表时，使用索引可使查询速度加快成千倍。
• 可以降低数据库的I0成本，并且索引还可以降低数据库的排序成本。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯性。
• 可以加快表和表之间的连接。
• 在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间。

1.1.3、索引的分类

• 普通索引，这是最基本的索引类型，而且它没有唯一性之类的限制。
• 唯-性索引， 这种索引和前面的“普通索引” 基本相同，但有一个区别:索引列的所有值只能出现一.次，即必须唯一。
• 主键索引，主键是一种唯一-性索引，但它必须指定为“PRIMARY KEY”。在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索
  引的特定类型。该索引要求主键中的每个值都唯一。
• 全文索引，索引类型为FULLTEXT， 全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
• 单列索引与多列索引，索引可以是单列上创建的索引，也可以是在多列上创建的索引。多列索引可以区分其中一列可能有相同值的行。

1.1.4、创建索引的原则依据

• 表的主键、外键必须有索引。主键具有唯一 性，索引值也是唯一的，
  查询时可以快速定位到数据行。外键一般关联的是另一个表的主键，所以在多表查询时也可以快速定位。
• 数据量超过300行的表应该有索引。数据量较大时，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。
• 经常与其他表进行表连接的表，在连接字段上应该建立索引。
• 唯一性太差的字段不适合建立索引。
• 更新太频繁的字段不适合创建索引。在表中进行增加、删除、修改操作时，索引也有相应操作产生。字段更新得过于频繁，对于系统资源占用也会更多。
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引。
• 索引应该建在选择性高的字段上。如果很少的字段拥有相同值，即有很多独特值，则选择性很高。
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

1.2、创建及查看索引

1.2.1、创建普通索引的方式：

直接创建：

mysql> create index index_id on info(id);  #info是表名，id是表中的字段
mysql> show index from info;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_id |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

时创建：

create table hobby(id int(2) not null primary key, con varchar(20), index index_id (id));
mysql> show index from hobby;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| hobby |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| hobby |          1 | index_id |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.2.2、创建唯一索引的方式

mysql> create unique index index_id on zf(id); #zf表名，id表中字段
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from zf;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| zf    |          0 | index_id |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec）

1.2.3、创建主键索引的方式

mysql> create table test1(id int(2) not null primary key,name char(20));
Query OK, 0 rows affected (0.01 sec)\
#或者#
mysql> create table test2(id int(2) not null,name char(20),primary key(id));
Query OK, 0 rows affected (0.01 sec)

1.2.4、组合索引（单列索引与多列索引）

mysql> create table test4(id int(2) not null,name char(20),index index_test4(id,name));
Query OK, 0 rows affected (0.02 sec)
mysql> show index from test4;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test4 |          1 | index_test4 |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| test4 |          1 | index_test4 |            2 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

1.2.5、全文索引

mysql> create table test5(id int(2) not null,name char(20),fulltext(name));
Query OK, 0 rows affected (0.04 sec)
mysql> show index from test5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test5 |          1 | name     |            1 | name        | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

二、MySQL事务

1.1、事务的概念及特点

1.1.1、事务的概念

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

1.1.2、事务的ACUID特性

• 原子性:事务是一个完整的操作，各元素是不可分的，即原子的。事务中的所有元素必须作为一个整体提交或回滚。如果事务中的任何元素失败，则整个事务将失败。
• 一致性:当事务完成时，数据必须处于一致状态;在事务开始之前，数据库中存储的数据处于一致状态;在正在进行的事务中，数据可能处于不一致的状态;当事务成功完成时，数据必须再次回到已知的一致状态。
• 隔离性:对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据。
• 持久性:指不管系统是否发生了故障，事务处理的结果都是永久的。一旦事务被提交，事务的效果会被永久地保留在数据库中。

1.2、事务的操作

1.2.1、使用事务命令控制事务

MySQL中使用命令控制事务需要用到3个命令:

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

1、下面演示事务命令的使用方式，假设需要往数据表IT_ salary中插入两条数据它们在一个事务中提交。
mysql> begin;
mysql> insert into IT_salary values('nginx 工程师','小王','38','016','大专','10','12000');
mysql> insert into IT_salary values('apache 工程师','小李','29','019','大专','9','15500');
mysql> commit;
mysql> select * from IT_salary;
+------------------+--------+--------+----------+--------+--------+--------+
| 岗位类别         | 姓名   | 年龄   | 员工ID   | 学历   | 年限   | 薪资   |
+------------------+--------+--------+----------+--------+--------+--------+
| apache 工程师    | 小李   | 29     | 019      | 大专   | 9      | 15500  |
| nginx 工程师     | 小王   | 38     | 016      | 大专   | 10     | 12000  |
+------------------+--------+--------+----------+--------+--------+--------+
使用begin开始事务，然后执行了两条插入语句，最后用commit，两条数据插入到了数据表中，这两条语句是作为一个整体操作的。
使用begin开始事务，执行操作语句后必须使用commit进行提交，否则数据不能自动提交。

2、rollback回滚的使用，假设插入一条数据后，需要恢复到插入前数据库的状态，使用rollback执行事务回滚。
mysql> delete from IT_salary;
mysql> begin;
mysql> insert into IT_salary values('nginx 工程师','小王','38','016','大专','10','12000');
mysql> select * from IT_salary;
+-----------------+--------+--------+----------+--------+--------+--------+
| 岗位类别        | 姓名   | 年龄   | 员工ID   | 学历   | 年限   | 薪资   |
+-----------------+--------+--------+----------+--------+--------+--------+
| nginx 工程师    | 小王   | 38     | 016      | 大专   | 10     | 12000  |
+-----------------+--------+--------+----------+--------+--------+--------+
1 row in set (0.00 sec)
mysql> rollback;
mysql> select * from IT_salary;
Empty set (0.00 sec)
可以看到，使用begin开始事务，然后执行了插入命令，查询表数据可以看到插入的数据，但是执行rollback回滚命令后，再查看表数据，先前插入的数据已经没有了，rollback使数据表回滚到begin开始之前的状态。

3、很多时候一个事务会包含多条语句，而出现问题需要回滚时，并不一定是要回滚到begin之前的状态，有可能是某条语句执行后的状态，这时要使用savepoint定义回滚点，rollback决定回滚到的位置。
mysql> delete from IT_salary;                                                            
mysql> begin;                                                                            
mysql> insert into IT_salary values('nginx 工程师','小王','38','016','大专','10','12000');
mysql> savepoint sq;
mysql> insert into IT_salary values('apache 工程师','小李','29','019','大专','9','15500');
mysql> savepoint sa;
mysql> rollback to savepoint sq;
mysql> select * from IT_salary;
+-----------------+--------+--------+----------+--------+--------+--------+
| 岗位类别        | 姓名   | 年龄   | 员工ID   | 学历   | 年限   | 薪资   |
+-----------------+--------+--------+----------+--------+--------+--------+
| nginx 工程师    | 小王   | 38     | 016      | 大专   | 10     | 12000  |
+-----------------+--------+--------+----------+--------+--------+--------+
1 row in set (0.00 sec）

1.2.2、使用set设置控制事件

使用set命令进行控制
set autocommit=0：禁止自动提交
set autocommit=1：开启自动提交（默认）

1、实际 生MySQL启动时autocommit的值默认是1，修改为0，则需要手动提交，也就是使用commit命令，回滚使用rollback命令。
mysql> set autocommit=0;
mysql> insert into IT_salary values('nginx 工程师','小王','38','016','大专','10','12000');
mysql> insert into IT_salary values('apache 工程师','小李','29','019','大专','9','15500');
mysql> commit;
mysql> select * from IT_salary;
+------------------+--------+--------+----------+--------+--------+--------+
| 岗位类别         | 姓名   | 年龄   | 员工ID   | 学历   | 年限   | 薪资   |
+------------------+--------+--------+----------+--------+--------+--------+
| apache 工程师    | 小李   | 29     | 019      | 大专   | 9      | 15500  |
| nginx 工程师     | 小王   | 38     | 016      | 大专   | 10     | 12000  |
+------------------+--------+--------+----------+--------+--------+--------+
2 rows in set (0.00 sec)
使用setautocaommit=0设置禁止自动提交，最后使用commit提交事务。不使用commit提交操作语句，和使用begin的情况相同，执行的语句都会失效。