MySQL索引、事物

一、MySQL索引介绍

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

1.索引概述 

        当数据保存在磁盘类存储介质上时，它是作为数据块存放。这些数据块是被当作一个整体来访问的，这样可以保证操作的原子性。硬盘数据块存储结构类似于链表，都包含数据部分，以及一个指向下一个节点(或数据块)的指针，不需要连续存储。
        记录集只能在某个关键字段上进行排序，所以如果需要在一个无序字段上进行搜索，就要执行一个线性搜索(Linear Search)的过程，平均需要访问 N/2 的数据块，N 是表示所占据的数据块数目。如果这个字段是一个非主键字段(也就是说，不包含唯一的访问入口)，那么需要在N个数据块上搜索整个表格空间。
        但是对于一个有序字段,可以运用二分査找(Binary Search),这样只需要访问 log2 (N)的数据块。这就是为什么数据表使用索引后性能可以得到本质上提高的原因。
        索引是对记录集的多个字段进行排序的方法。在一张表中为一个字段创建一个索引,将创建另外一个数据结构，包含字段数值以及指向相关记录的指针，然后对这个索引结构进行排序，允许在该数据上进行二分法排序。 

2.索引作用

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

3.索引的分类

        在数据库表中，对字段建立索引可以大大提高查询速度。通过善用这些索引，可以令MySQL的查询和运行更加高效。索引是快速搜索的关键。MVSQL索引的建立对于 MySQL的高效运行是非常重要的。下面介绍几种常见的 MySQL 索引类型。
        从物理存储的角度来划分,索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。
        从逻辑的角度来划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

(1)普通索引 

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

直接创建索引：
mysql>CREATE INDEX index_name ON table_name(column(length));
修改表结构添加索引：
mysqI>ALTER TABLE table_name ADD INDEX index_name (column(length));
创建表结构时，同时创建索引：
INDEX index_name (title(length));

 (2)唯一索引

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

直接创建索引：
mysql>CREATE UNIQUE INDEX index_name ON table_name(column(length));
修改表结构添加索引：
mysqI>ALTER TABLE table_name ADD UNIQUE INDEX index_name (column(length));
创建表结构时，同时创建索引：
INDEX index_name (title(length));

 (3)主键索引

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

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

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

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

index user(name, age, sex)

        在 MySQL 中，有一个知识点叫最左原则。下面的 select 语句的 where 条件是依次从左往右执行的。若使用的是组合索引index user(name,age,sex)。在查询中，name、age、sex的顺序必须如组合索引中一致排序，否则索引将不会生效。所以一般在建立索引时，要先想好相应的查询业务，尽量避免虽然有索引，但是使用不上的问题。

(5)全文索引（FULLTEXT）

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

修改表结构添加全文索引:
mysqI>ALTER TABLE article ADD FULLTEXT index _content(content);
直接创建索引:
mysqI>CREATE FULLTEXT INDEX index content ON article(content);

 4.查看索引

show index from tablename;
show keys from tablename;

        以上语句结果中的个字段意义如下：
        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:备注。

5.删除索引

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

DROP INDEX 索引名 ON 表名;
ALTER TABLE 表名 DROP INDEX 索引名;

二、MySQL事务

        MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，要删除一个人员，即需要删除人员的基本资料，又需要删除和该人员相关的信息，如信箱，文章等等。这样，这些数据库操作语句就构成一个事务。
        事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行；是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元；适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等通。过事务的整体性以保证数据的一致性。

1.事务的ACID特性

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

(1)原子性（Atomicity）

        事务是一个完整的操作，事务的各元素是不可分的
        事务中的所有元素必须作为一个整体提交或回滚
        如果事务中的任何元素失败，则整个事务将失败

(2)一致性（Consistency）

        当事务完成时，数据必须处于一致状态
        在事务开始前，数据库中存储的数据处于一致状态
        在正在进行的事务中，数据可能处于不一致的状态
        当事务成功完成时，数据必须再次回到已知的一致状态

(3)隔离性（Isolation）

        对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
        修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

(4)持久性（Durability）

        指不管系统是否发生故障，事务处理的结果都是永久的
        一旦事务被提交，事务的效果会被永久地保留在数据库中

2.事务控制语句

        在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务必须使用命令 BEGIN 或 STARTTRANSACTION，或者执行命令 SETAUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
        事务控制语句包含：
        BEGIN 或 START TRANSACTION:显式地开启一个事务;
        COMMIT:也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改变为永久性的;
        ROLLBACK:又可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改;
        SAVEPOINTidentifier:SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT;
        RELEASE SAVEPOINT identifier:删除一个事务的保存点，当没有指定的保存点时执行该语句会抛出一个异常;
        ROLLBACK TO identifier:把事务回滚到标记点;
        SETTRANSACTION:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和SERIALIZABLE.

3.事务处理 

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

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

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

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

        SETAUTOCOMMIT=0 禁止自动提交
        SETAUTOCOMMIT=1 开启自动提交