数据库索引与事务相关知识总结

索引：

索引的含义和特点：

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可提高数据库中特定数据的查询速度。使用索引可以快速找出在某个或多个列中有一特定值的行，所有MySQL列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引存储类型及适用引擎：

索引是在存储引擎中实现的，因此，每种存储引擎的索引都不一定完全相同，并且每种存储引擎也不一定支持所有索引类型。

MySQL中 索引的存储类型有两种，即BTREE和HASH，具体和表的存储引擎相关；

 MyISAM和InnoDB 存储引擎只支持BTREE索引；MEMORY/HEAP存储引擎 可以支持HASH和BTREE索引

索引优缺点：

索引的优点：

（1）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

（2）可以大大加快数据的查询速度，这也是创建索引的主要原因。

（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。

（4）在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

索引的缺点：

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费 的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引 还要占一定的物理空间，如果有大量的索引，索引文件可能比数据文件更快 达到最大文件尺寸。

（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的设计原则：

（1）索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表 中的数据更改时，索引也会进行调整和更新。

（2）避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常用于查询的字段创建索引，但要避免添加不必要的字段。

（3）数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

（4）在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就无须建立索引。

（5）当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。

（6）在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

索引分类：

1．普通索引和唯一索引 普通索引是MySQL中的基本索引类型，允许在定义索引的列中插入重复值和空值。 唯一索引要求索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。主键索引是一种特殊的唯一索引，不允许有空值。

2．单列索引和组合索引 单列索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引是指在表的多个字段组合上创建的索引，只有在查询条件中使 用了这些字段的左边字段时，索引才会被使用。使用组合索引时遵循最左前缀集合。【组合索引上来讲的】

3．全文索引 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找， 允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR或者TEXT类型的列上创建。MySQL中 只有MyISAM存储引擎支持全文索引。

4．空间索引 空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据 类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。 MySQL使用SPATIAL关键字进行扩展，

使得能够用创建正规索引类似的语 法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MyISAM的表中创建。

创建索引：

Alter table tabname add [unique|fulltext|spatial] index indexname

(colname[length]) ；

删除索引：

Alter table tabname drop index indexname ；

组合索引的最左前缀原则(遇到范围匹配会停止生效)：

CREATE TABLE test_db(

vid INT ,

vname VARCHAR(50),

vage INT,

vinfo VARCHAR(200),

INDEX mul (vid,vname,vage) //创建组合索引

);

最左边的vid被使用（SQL查询优化机制），所以索引生效

Select * from table where vname=’a’ and vid = 1 and vage = 1;

<==> Select * from table where vid = 1 and vname=’a’ and vage = 1 ;

最左边的vid未被使用，索引不生效

Select * from table where vname=’a’ ;

Select * from table where vage=1 ;

Select * from table where vname=’a’ and vage = 1 or vid = 1;

事务：

数据库事务( transaction)是由一组SQL语句组成的逻辑处理单元，是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

四大特性：

（1）原子性（Atomicity）：事务具有原子不可分割的特性，要么一起执行，要么都不执行。（一个步骤出错，事务回滚到开始时） 

（2）一致性（Consistency）：在事务开始和事务结束时，数据都保持一致状态。（例如转账，转账操作前后总的钱数应是一样的） 

（3）隔离性（Isolation）：在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。 （两个操作应该彼此互不影响）

（4）持久性（Durability）：事务完成后，数据将会被持久化到数据库中。 （事务提交后，数据持久改变）

事务的两个状态：

提交（commit）和回滚（rollback）；

BEGIN ; -- 开启事务  

UPDATE account SET abalance=abalance-2000 WHERE aid=1;

SAVEPOINT A;--保存点

UPDATE account SET abalance=abalance+2000 WHERE aid=2;

COMMIT 或 rollback; --提交 commit或 回滚rollback(还可回滚到指定保存点---> rollback to A :A之前的会提交，之后的会回滚)

事务隔离级别：

定义：两个并行的事务对数据修改的隔离程度：

查看MySql的事务隔离级别：

SELECT @@transaction_isolation; 【 select @@tx_isolation;】

设置MySql的事务隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

两个事务并发执行可能出现问题：

脏读：

事务A读取到事务B未提交的事务：

例如账户余额原本为5000，事务B修改账户余额为8000，但还未提交，事务A读取到账户余额已经变成8000，然后事务B再进行事务回滚，这里事务A读取到的8000就是脏数据。这种情况就叫脏读；

设置隔离级别为Read uncommitted

事务A[事务A读取到的8000就是脏数据]	事务B
READ UNCOMMITTED;	READ UNCOMMITTED;
select * from account ;-- 5000	select * from account ;-- 5000
	begin;
	update account set balance=8000 where accid=1;
select * from account ;-- 8000 （脏数据）
	rollback

不可重复读：

例如账户余额原本为5000，事务A想修改账户余额为8000，但还未提交，事务B修改余额为8000且提交成功，此时事务A读取数据就已经更改，但事务B提交前读取到仍是5000（未提交前的数据）；

设置隔离级别为Read committed

事务A	事务B
READ COMMITTED;	READ COMMITTED;
select * from account ;-- 5000	select * from account ;-- 5000
	begin;
	update account set balance=balance+3000 where accid=1;
	Commit；
	select * from account;-- 8000
begin;
update account set balance=balance+3000 where accid=1;
Commit；
select * from account;-- 11000 （对于事务A来说，应该看到的结果是8000）

幻读：

事务A第一次读取数据为5000，此时事务B对数据进行更改提交，A再次读取时就不是5000；

设置隔离级别为Repeatable read

事务A	事务B
SET autocommit=0;	SET autocommit=0;
select * from account ;-- 5000	select * from account ;-- 5000
	update account set balance=balance+3000 where accid=1;
	select * from account ;-- 8000
select * from account ;-- 5000 事务B未提交，看到数据不变，无脏读。
	COMMIT;
select * from account ;-- 5000 事务B已经提交，还是看到数据不变，即可以重复读。
COMMIT;
select * from account ;-- 8000两事务都提交才能查看最终更新结果

设置隔离级别为Serializable

事务A	事务B
SET autocommit=0;	SET autocommit=0;
select * from account ;-- 5000	select * from account ;-- 5000
	update account set balance=balance+3000 where accid=1; （此时不会执行）
Commit；
	事务Acommit完后自动执行update语句
	Commit；
select * from account ;-- 8000	select * from account ;-- 8000

事务隔离级别：（自上而下隔离级别增加）

	脏读	不可重复读	幻读
Read uncommited	√	√	√
Read commited	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×