数据库学习笔记（六）——索引事务

本文链接：https://blog.youkuaiyun.com/weixin_46056458/article/details/123102373

一、索引

理解

在不同数据库中索引是不同的。 MySQL
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能
非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引是物理结构，索引表。由数据库建立、维护，当对数据表进行操作时，数据库同时会根据数据表更新索引表。
数据表按插入顺序排列，而索引表是有序的a-z。（字典目录）索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
增加索引后，提升查询速度，增删改速度降低。

（1）索引的概念

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

在数据库系统中建立索引主要有以下作用： 1. 快速取数据； 2. 保证数据记录的唯一性； 3. 实现表与表之间的参照完整性； 4. 在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

图片描述
索引表——数据表

1. 索引特点

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，高系统的性能。

2. 索引不足【时间、空间、速度】

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

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

3. ❤ 应该建索引列的特点【面试】

【经常进行搜索、根据范围搜索、排序、条件判断，主键外键应该建立索引】

在经常需要搜索的列上，可以加快搜索的速度；

在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

在经常需要根据范围(<，>，=，<=，>=，between，in)进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

4. ❤ 不应该建索引列的特点【面试】

【大量数据—常用数据—修改不高于检索】

对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
（临界值）

对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

（2） Oracle常用索引

B树索引

oracle中最常用的索引，也是默认索引，保存讲过排序过的索引列和对应的rowid值。B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值，所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同，能够适应精确查询、模糊查询和比较查询。

适合使用场景：列基数（列不重复值的个数）大时适合使用B数索引。

位图索引

创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换。

适合场景：对于基数小的列适合简历位图索引（例如性别等）

反向建索引

反向索引作为B-tree索引的一个分支，主要是在创建索引时，针对索引列的索引键值进行字节反转，进而实现分散存放到不同叶子节点块的目的。
使用传统的B-tree索引，当索引的列是按顺序产生时，相应的索引键值会基本分布在同一个叶块中。当用户对该列进行操作时，难免会发生索引块的争用。
使用反向索引，将索引列的键值进行反转，实现顺序的键值分散到不同的叶块中，从而减少索引块的争用。例如：键值1001、1002、1003，反转后1001、2001、3001，进而分散到不用的叶子节点块中。
适合场景：递增的ID或编号等列

（3） MySQL索引知识详解

https://blog.youkuaiyun.com/wangfeijiu/article/details/112454405
https://www.cnblogs.com/bypp/p/7755307.html

四种分类主键索引、唯一索引、普通索引、全文索引

二、事物

1. 什么是事务

定义:数据库事务是构成单一逻辑工作单元的操作集合

转账是生活中常见的操作,比如从A账户转账100元到B账号。站在用户角度而言,这是一个逻辑上的单一操作,然而在数据库系统中,至少会分成两个步骤来完成:
将A账户的金额减少100元
将B账户的金额增加100元。
在这个过程中可能会出现以下问题:

转账操作的第一步执行成功,A账户上的钱减少了100元,但是第二步执行失败或者未执行便发生系统崩溃,导致B账户并没有相应增加100元。
转账操作刚完成就发生系统崩溃,系统重启恢复时丢失了崩溃前的转账记录。
同时又另一个用户转账给B账户,由于同时对B账户进行操作,导致B账户金额出现异常。
所以就需要引入事务来进行控制。事务描述的是一个整体的业务，例如转账。事务保证业务所涉及的操作是一个整体，要么都执行，要么都不执行。

2.❤ 事务的ACID特性【面试】

**原子性**(Atomicity):事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功,要么全部失败。

**一致性**(Consistency):事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。一致性状态是指:

i. 系统的状态满足数据的完整性约束(主码,参照完整性,check约束等)
ii. 系统的状态反应数据库本应描述的现实世界的真实状态,比如转账前后两个账户的金额总和应该保持不变。

**隔离性**(Isolation):并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。

**持久性**(Durability):事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。

3. 事务操作

JDBC事务：一个connection中的事务。默认开启

public void JdbcTransfer() { 
    java.sql.Connection conn = null;
     try{ 
        conn = conn =DriverManager.getConnection("jdbc:oracle:thin:@host:1521:SID","username","userpwd"）;
       **// 将自动提交设置为 false，
       **// 若设置为 true 则数据库将会把每一次数据更新认定为一个事务并自动提交
       ** conn.setAutoCommit(false);
 
         stmt = conn.createStatement(); 
         //1. 将 A 账户中的金额减少 500 
         stmt.execute("\
         update t_account set amount = amount - 500 where account_id = 'A'");
         //2.  将 B 账户中的金额增加 500 
         stmt.execute("\
         update t_account set amount = amount + 500 where account_id = 'B'");
 
         // 提交事务
         conn.commit();
         // 事务提交：转账的两步操作同时成功
     } catch(SQLException sqle){            
         try{ 
             // 发生异常，回滚在本事务中的操做
            conn.rollback();
             // 事务回滚：转账的两步操作完全撤销
             stmt.close(); 
             conn.close(); 
         }catch(Exception ignore){ 
 
         } 
         sqle.printStackTrace(); 
     } 
}

若自动提交为true，则1和2两个步骤形成两个事物，分别提交；关闭自动提交，则1和2形成的是一个事物，均完成之后才提交。
多个DML操作时一定要 conn.setAutoCommit(false);，才可以封成一个事物。

JTA事务：多个connection中的事务，跨connection的事务。多个数据库，必须容器（tomcat、jboss）支持

public void JtaTransfer() { 
        javax.transaction.UserTransaction tx = null;
        java.sql.Connection conn = null;
         try{ 
             tx = (javax.transaction.UserTransaction) context.lookup("java:comp/UserTransaction");  //取得JTA事务，本例中是由Jboss容器管理
             javax.sql.DataSource ds = (javax.sql.DataSource) context.lookup("java:/XAOracleDS");  //取得数据库连接池，必须有支持XA的数据库、驱动程序  
             tx.begin();
            conn = ds.getConnection();
 
             // 将自动提交设置为 false，
             //若设置为 true 则数据库将会把每一次数据更新认定为一个事务并自动提交
             conn.setAutoCommit(false);
 
             stmt = conn.createStatement(); 
             // 将 A 账户中的金额减少 500 
             stmt.execute("\
             update t_account set amount = amount - 500 where account_id = 'A'");
             // 将 B 账户中的金额增加 500 
             stmt.execute("\
             update t_account set amount = amount + 500 where account_id = 'B'");
 
                         //其他的conn连接
                         conn1 = ....
 
             // 提交事务
             tx.commit();
             // 事务提交：转账的两步操作同时成功
         } catch(SQLException sqle){            
             try{ 
                 // 发生异常，回滚在本事务中的操做
              tx.rollback();
                 // 事务回滚：转账的两步操作完全撤销
                 stmt.close(); 
                 conn.close(); 
             }catch(Exception ignore){ 
 
             } 
             sqle.printStackTrace(); 
         } 
     }

PS：spring声明式事务（事物管理方式）。分布式事务：redis控制。

4 DDL操作中的事务

事务分为显式事务和隐式事务。
显示事务需要用commit控制，隐式事务不需要commit控制，自动执行。
【DML操作全是显示事物，DDL操作全是隐式事物，DQL不用事物】

//eg： DDL操作完成后，会执行一次隐式事务。

delete from users where userId = ?//没有commit，所以不会同步底层数据。
create table t_temp (....);

上述代码执行后，delete会直接影响物理表中的数据。以为DDL操作会自动执行隐式事务。意味着，自动commit。

三、❤ 事物隔离级别【面试】

在这里插入图片描述

读未提交—（脏读）—> 不可重复读 —（不可重复读）—> 重复读—（幻读）—> 序列化

1. Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。

2 Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

3. Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！