首页

事务、视图、索引

Controlling Concurrent Behavior
Virtual and Materialized Views
Speeding Accesses to Data

Haifeng Xu

(hfxu@yzu.edu.cn)

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

也参考了姜承尧写的书《MySQL技术内幕InnoDB存储引擎》（第2版）

为什么要研究事务(Transactions)

数据库系统通常会被许多用户访问, 并且很多处理(如查询、修改等)同时发生.

与操作系统支持进程的交互所不同的是, DBMS 需要对有问题的交互进行进程维持.(needs to keep processes from troublesome interactions.)

事务(Transaction)是数据库区别于文件系统的重要特性之一.

例子: Bad Interaction

用户A和用户B持有某银行同一账户和密码, 也就是说假设他们有完全相同的两张银行卡, 并且他们在同一时间从不同地点的两台 ATM 机上都取了 $\$ 100$.

如果这两人所做的事情并不是取钱, 而是修改某个文件, 将1000同时改为900. 那么就算同时发生, 也似乎没有什么问题.
但现在处理的对象是钱, DBMS 最好确保账户的两次扣除操作的记录都不会丢失.

比较: 操作系统允许两个用户同时编辑某个文档. 当同时写到磁盘时, 某个用户的更改将会丢失.

对于文件同步的情况, 当系统重启, 会出现文件可能不同步的情况.

事务(Transactions)

Transaction: 指包含数据库查询和/或修改的进程.

通常具有某些强大的并发(concurrency)方面的性质.

Formed in SQL from single statements 或明确的程序员控制

数据库系统引入事务的目的

目的: 事务会把数据库从一种一致状态转换为另一种一致状态.

在数据库提交工作时, 可以确保要么所有修改都已经保存了, 要么所有修改都不保存.

MySQL

MySQL 有很多存储引擎, 其中 MyISAM 存储引擎不支持事务. InnoDB 存储引擎支持事务.

ACID Transactions

ACID Transactions 指:

原子性(Atomic/Atomicity): 要么整个事务被执行要么什么也不干.
一致性(Consistent/Consistency): 保留数据库的约束.
独立性(Isolated/Isolation): 对用户来说好像同一时间只有一个进程在执行.
持久性(Durable/Durability): 使得进程能够在崩溃中幸存.

也就是说, 严格来说, 事务需要满足上面四个条件. 但是弱形式的事务也是被支持的.

详解

原子性(Atomicity)

原子性指整个数据库事务是不可分割的工作单位. 只有使事务中所有的数据库操作都执行成功, 才算整个事务成功. 事务中任何一条SQL语句执行失败, 已经执行成功的SQL语句也必须撤销, 数据库状态应该退回到执行事务前的状态.

一致性(Consistency)

一致性指事务将数据库从一种状态转变为下一种一致的状态. 在事务开始之前和事务结束以后, 数据库的完整性约束没有被破坏.

隔离性(Isolation)

隔离性还有其他的称呼, 如并发控制(concurrency control)、可串行化(serializability)、锁(locking) 等. 事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离, 即该事务提交前对其他事务都不可见, 通常这使用锁来实现.

持久性(Durability)

事务一旦提交, 其结果就是永久性的. 即使发生宕机等故障, 数据库也能将数据恢复. (外部原因导致数据丢失的情况不属于这里的持久性. 这里的持久性指的是保证事务系统的高可靠性(High Reliability), 而不是高可用性(High Availability).)

例子

MySQL 的 NDB Cluster 引擎, 虽然支持事务, 但是不满足 D 的要求, 即持久性的要求.
Oracle 数据库默认的事务隔离级别为 READ COMMITTED. 不满足 I 的要求, 即隔离性的要求.
MySQL 的 InnoDB 引擎, 默认的事务隔离级别为 READ REPEATABLE, 完全遵循和满足事务的 ACID 特性.

事务的分类

从事务理论的角度, 可以将事务分为以下几种类型.

扁平事务(Flat Transactions)
带有保存点的扁平事务(Flat Transactions with Savepoints)
链事务(Chained Transactions)
嵌套事务(Nested Transactions)
分布式事务(Distributed Transactions)

扁平事务

扁平事务(Flat Transactions)是事务类型中最简单的一种, 在实际生产环境中是使用最频繁的事务. 在扁平事务中, 所有操作都处于同一层次, 其由 BEGIN WORK 开始, 由 COMMIT WORK 或 ROLLBACK WORK 结束. 其间的操作是原子的, 要么都执行, 要么都回滚.

扁平事务的主要限制是不能提交或回滚事务的某一部分, 或分几个步骤提交.

带有保存点的扁平事务

带有保存点的扁平事务(Flat Transactions with Savepoints), 除了支持扁平事务支持的操作外, 允许在事务执行过程中回滚到同一事务中较早的一个状态.

保存点(Savepoint)用来通知系统应该记住事务当前的状态, 以便当之后发生错误时, 事务能回到保存点当时的状态.

对于扁平事务来说, 其隐式地设置了一个保存点, 就是事务开始时的状态点.

链事务

链事务(Chained Transactions)可视为保存点事务的一种变种.

嵌套事务

嵌套事务(Nested Transactions)是一个层次结构框架. 由一个顶层事务(top-level transaction)控制着各个层次的事务. 顶层事务之下嵌套的事务被称为子事务(subtransaction), 其控制每一个局部的变换.

可以通过保存点技术来模拟嵌套事务. 但是用保存点技术模拟的嵌套事务在锁的持有方面还是于嵌套查询有些区别.

分布式事务

分布式事务(Distributed Transactions)通常是一个在分布式环境下运行的扁平事务, 因此需要根据数据所在位置访问网络中的不同节点.

InnoDB 存储引擎支持扁平事务、带有保存点的事务、链事务、分布式事务。因此, 对有并行事务需求的用户来说, MySQL数据库或InnoDB 存储引擎就显得无能为力了. 虽然可以通过带有保存点的事务来模拟嵌套事务, 但本质上是串行的嵌套事务.

隔离层次(Isolation Levels)

SQL 定义了四种隔离层次(Isolation Levels), 所谓的隔离层次(Isolation Levels) 是指当事务在同一时间执行时哪一种相互影响是被事务所允许的.

只有一种级别("可串行化(serializable)")=ACID 事务.

每一种 DBMS 都有自己的方式对事务进行补充.

选择隔离层次

在一个事务中, 我们可以这样设置事务的隔离层次: SET TRANSACTION ISOLATION LEVEL $X$
其中 $X=$

SERIALIZABLE (可串行化)
REPEATABLE READ (可重复读)
READ COMMITTED (读提交)
READ UNCOMMITTED (读未提交, 即允许脏读)

实验(MySQL)

在 InnoDB 存储引擎中, 可以使用下面的命令来设置当前会话或全局的事务隔离级别:

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{
READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE
}

如果想在 MySQL 数据库启动时就设置事务的默认隔离级别(比如希望设置为 READ COMMITTED), 则需修改 MySQL 的配置文件, 在 [mysqld] 段中加入如下行:

[mysqld]
transaction-isolation = READ-COMMITTED

查询当前会话的事务隔离级别, 可以使用

mysql> SELECT @@tx_isolation\G;
*************************** 1. row ***************************
@@tx_isolation: REPEATABLE-READ
1 row in set (0.00 sec)

查询全局的事务隔离级别, 可以使用

mysql> SELECT @@global.tx_isolation\G;
*************************** 1. row ***************************
@@global.tx_isolation: REPEATABLE-READ
1 row in set (0.00 sec)

这里我们发现本地安装的 MySQL, 其隔离级别是 REPEATABLE READ. 一般不在本地事务中使用 SERIALIZABLE 的隔离级别. SERIALIZABLE 的事务隔离级别主要用于InnoDB存储引擎的分布式事务.

设置事务隔离级别为 READ COMMITTED

mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

分布式事务

XA 指代 eXtended Architecture(扩展架构).

InnoDB 存储引擎提供了对 XA 事务的支持, 并通过 XA 事务来支持分布式事务的实现.

分布式事务(distributed transaction)指的是允许多个独立的事务资源(transactional resources)参与到一个全局的事务中.

事务资源通常是关系型数据库系统, 但也可以是其他类型的资源.

全局事务要求在其中的所有参与的事务要么都提交, 要么都回滚. 这对于事务原有的 ACID 要求又提高了一层.

另外, 在使用分布式事务时, InnoDB 存储引擎的事务隔离级别必须设置为 SERIALIZABLE.

分布式事务的应用

分布式事务常见于银行系统的转账中.

XA 事务

XA 事务由一个或多个资源管理器(Resource Managers)、一个事务管理器(Transaction Manager)以及一个应用程序(Application Program)组成.

资源管理器: 提供访问事务资源的方法. 通常一个数据库就是一个资源管理器.
事务管理器: 协调参与全局事务中的各个事务. 需要和参与全局事务的所有资源管理器进行通信.
应用程序: 定义事务的边界, 指定全局事务中的操作.

在 MySQL 数据库的分布式事务中, 资源管理器就是 MySQL 数据库, 事务管理器为连接 MySQL 服务器的客户端.

关于 XA 事务及分布式事务, 请参见相关参考文献. 推荐阅读姜承尧著《MySQL技术内幕 InnoDB存储引擎》（第2版）。

提交(Commit)

SQL 中的 COMMIT 子句使得事务得以完成.

执行 COMMIT 子句之后, 对数据库的修改才会真正持久保存在数据库中.

MySQL 的 InnoDB 引擎默认自动提交. 注意每一次提交, MySQL 都要写一次重做日志(redo log). 因此, 在写存储过程时, 不要在循环中提交.

MySQL 事务的开启方式有显示开启和隐式开启. 开启的方式会影响自动提交的设置.

MySQL 可以使用 START TRANSACTION 或 BEGIN 来显式地开启一个事务. 在显式开启事务后, 在默认设置下, MySQL 会自动执行 SET AUTOCOMMIT=0; 的命令. 并在 COMMIT 或 ROLLBACK 结束一个事务后执行 SET AUTOCOMMIT=1; 的命令.

通过 SHOW VARIABLES LIKE "%autocommit%"; 或 SELECT @@autocommit; 可以查看系统当前是否是自动提交. 通过 SET autocommit=0; 或 SET autocommit=on; 可以关闭自动提交.

实验1(MySQL)

mysql> show variables like "%autocommit%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> set autocommit=on; Query OK, 0 rows affected (0.00 sec)

实验2(MySQL)

下面通过例子说明自动提交可能会带来什么问题. 我们假设系统变量 autocommit 为 1.

以下例子来自于姜承尧著《MySQL技术内幕 InnoDB存储引擎》（第2版）.

CREATE PROCEDURE load1(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
COMMIT;
SET s = s+1;
END WHILE;
END;

下面的 load2 过程相比 load1 过程少了 COMMIT; 语句.

CREATE PROCEDURE load2(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
END;

而 load3 过程将执行得比 load1 和 load2 更快, 因为它将整个 WHILE 循环部分放到一个事务中.

CREATE PROCEDURE load3(count INT UNSIGNED)
BEGIN
DECLARE s INT UNSIGNED DEFAULT 1;
DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
START TRANSACTION;
WHILE s <= count DO
INSERT INTO t1 SELECT NULL,c;
SET s = s+1;
END WHILE;
COMMIT;
END;

比较执行时间

首先建立表 t1.

mysql> use test
Database changed

mysql> create table t1(
    -> id int not null auto_increment primary key,
    -> str char(80)
    -> );
Query OK, 0 rows affected (0.04 sec)

建立三个存储过程 load1, load2, load3.

mysql> delimiter $$
mysql> CREATE PROCEDURE load1(count INT UNSIGNED)
    -> BEGIN
    -> DECLARE s INT UNSIGNED DEFAULT 1;
    -> DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
    -> WHILE s <= count DO
    -> INSERT INTO t1 SELECT NULL,c;
    -> COMMIT;
    -> SET s = s+1;
    -> END WHILE;
    -> END;
    -> $$
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE PROCEDURE load2(count INT UNSIGNED)
    -> BEGIN
    -> DECLARE s INT UNSIGNED DEFAULT 1;
    -> DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
    -> WHILE s <= count DO
    -> INSERT INTO t1 SELECT NULL,c;
    -> SET s = s+1;
    -> END WHILE;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE load3(count INT UNSIGNED)
    -> BEGIN
    -> DECLARE s INT UNSIGNED DEFAULT 1;
    -> DECLARE c CHAR(80) DEFAULT REPEAT('a',80);
    -> START TRANSACTION;
    -> WHILE s <= count DO
    -> INSERT INTO t1 SELECT NULL,c;
    -> SET s = s+1;
    -> END WHILE;
    -> COMMIT;
    -> END;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

测试这三个存储过程的执行时间.

mysql> CALL load1(10000);
Query OK, 0 rows affected (39.07 sec)


mysql> select * from t1;
+-----+----------------------------------------------------------------------------------+
| id  | str                                                                              |
+-----+----------------------------------------------------------------------------------+
|   1 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   2 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   3 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   4 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   5 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   6 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   7 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   8 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|   9 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  10 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |

......

|  9994 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  9995 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  9996 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  9997 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  9998 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
|  9999 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
| 10000 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa |
+-------+----------------------------------------------------------------------------------+
10000 rows in set (0.01 sec)

这个时间还是很长的. 使用 select * from t1; 也要花费很多时间.

使用 load2() 稍微快一点.

mysql> call load2(10000);
Query OK, 1 row affected (37.09 sec)

使用 load3() 是最快的.

mysql> call load3(10000);
Query OK, 0 rows affected (0.18 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    30000 |
+----------+
1 row in set (0.01 sec)

可以对第二个存储过程 load2() 的调用进行调整, 调用之前显示开启事务, 则同样可以达到存储过程 load3() 的性能.

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> call load2(10000);
Query OK, 1 row affected (0.16 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

我们再实验一下循环插入两万行数据.

mysql> CALL load1(20000);
Query OK, 0 rows affected (1 min 11.95 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|    20000 |
+----------+
1 row in set (0.01 sec)

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)


mysql> call load2(20000)
    -> ;
Query OK, 1 row affected (1 min 11.51 sec)

mysql> truncate t1;
Query OK, 0 rows affected (0.01 sec)

mysql> call load3(20000);
Query OK, 0 rows affected (0.35 sec)

采用隐式提交的 load2() 还是比显示提交的 load1() 稍快. 而 load3() 是最快的. 因此, 不应该在一个循环中反复进行提交(commit)操作. 而且由于 MySQL 默认是自动提交, 因此最好将其设置为非自动提交.

注意事项及总结

对于不同语言的 API, 自动提交是不同的.

MySQL C API 默认的提交方式是自动提交.
MySQL Python API 则会自动执行 SET AUTOCOMMIT=0; 以禁止自动提交.

因此在选用不同的语言来编写数据库应用程序前, 应该对连接 MySQL 的 API 做好研究.

在编写应用程序时, 最好把事务的控制权限交给开发人员, 即在程序端进行事务的开始和结束.

回滚(Rollback)

SQL 中的 ROLLBACK 子句也将使得事务得以终止, 但是以退出(aborting)的形式终止的.

也就是说对数据库没有发生影响.

例如被 $0$ 除或某个违反约束的动作均会导致数据库的回滚, 即使程序员并没有作出回滚的请求.

InnoDB 引擎的自动回滚

InnoDB 存储引擎支持通过定义一个 HANDLER 来进行自动事务的回滚操作. 如在一个存储过程中发生了错误会自动对其进行回滚操作.

首先建立一个表 b, 仅有一个属性 a, 且这个属性为 PRIMARY KEY.

mysql> create table b(
    -> a int not null default 0,
    -> primary key(a)
    -> );
Query OK, 0 rows affected (0.02 sec)


mysql> delimiter //
mysql> CREATE PROCEDURE sp_auto_rollback_demo()
    -> BEGIN
    -> DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    -> START TRANSACTION;
    -> INSERT INTO b SELECT 1;
    -> INSERT INTO b SELECT 2;
    -> INSERT INTO b SELECT 1;
    -> INSERT INTO b SELECT 3;
    -> COMMIT;
    -> END;
    -> //
Query OK, 0 rows affected (0.00 sec)

调用此存储过程, 发现有 warning.

mysql> delimiter ;
mysql> call sp_auto_rollback_demo();
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1062 | Duplicate entry '1' for key 'PRIMARY' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

因此不建议在存储过程中执行事务的 BEGIN, START TRANSACTION, COMMIT 和 ROLLBACK 操作. 开发者或用户不容易察觉错误. 存储过程只需包含逻辑操作, 即对逻辑操作进行封装. 而关于事务的启动、提交和回滚等操作应该交由开发者(或所写的程序端)来完成.

例如, 可以这样来写.

mysql> delimiter ??
mysql> CREATE PROCEDURE sp_rollback_demo()
    -> BEGIN
    -> INSERT INTO b SELECT 1;
    -> INSERT INTO b SELECT 2;
    -> INSERT INTO b SELECT 1;
    -> INSERT INTO b SELECT 3;
    -> END;
    -> ??
Query OK, 0 rows affected (0.00 sec)

使用 Python 连接 MySQL 并调用此存储过程

在 Windows 下, 可以用 where python 查找python.exe所在的路径.

#! C:\\Users\\haife\\AppData\\Local\\Programs\\Python\\Python36\\python.exe
#encoding=utf-8

import MySQLdb

try:
	conn=MySQLdb.connect(host="127.0.0.1", user="root", passwd="qwerty", db="test")
	cur = conn.cursor()
	cur.execute("SET autocommit=0")
	cur.execute("CALL sp_rollback_demo")
	cur.execute("COMMIT")
#except Exception,e: # for Python2
except Exception as e: # for Python3
	cur.execute("ROLLBACK")
	#print e  # for Python2
	print(e)  # for Python3

在执行该python程序时, 可能会碰到下面的问题, 找不到名为 MySQLdb 的模块.

D:\work\cs\python\mysql_connect>python test_demo.py
Traceback (most recent call last):
  File "test_demo.py", line 4, in 
    import MySQLdb
ModuleNotFoundError: No module named 'MySQLdb'

则执行 pip install mysqlclient 安装此模块.

D:\work\cs\python\mysql_connect>pip install mysqlclient
Collecting mysqlclient
  Downloading https://files.pythonhosted.org/packages/96/da/6bc460ef7a6df3d417914f17232259a4c869e63a3809d138ae786d7cf639/mysqlclient-1.4.6-cp36-cp36m-win_amd64.whl (185kB)
    93% |██████████████████████████████  | 174kB 37kB/s eta 0:00:01    
    99% |████████████████████████████████| 184kB 37kB/s eta 0:00:    
    100% |████████████████████████████████| 194kB 47kB/s
Installing collected packages: mysqlclient
Successfully installed mysqlclient-1.4.6
You are using pip version 19.0.3, however version 20.1b1 is available.
You should consider upgrading via the 'python -m pip install --upgrade pip' command.

此时再运行 test_demo.py, 就会看到错误了.

D:\work\cs\python\mysql_connect>python test_demo.py
(1062, "Duplicate entry '1' for key 'PRIMARY'")

长事务

长事务(Long-Lived Transactions)

长事务, 即执行时间较长的事务.

例如, 对于银行系统的数据库, 其中要对含有1亿用户的表 account, 进行加息计算.

UPDATE account SET account_total = account_total * (1 + interest_rate);

对于长事务的问题, 有时可以通过转化为小批量(mini batch)的事务来进行处理.

例子: 插入进程

考虑关系 Sells(bar,beer,price), 假设 Joe's Bar 卖 Bud 啤酒仅 $\$ 2.50$, 卖 Miller 啤酒仅 $\$ 3.00$.

现在顾客 Sally 在 Sells 关系中查询 Joe 卖啤酒的最高价与最低价.

与此同时, Joe 决定停止销售 Bud 和 Miller 两种啤酒, 但以 $\$ 3.50$ 销售 Heineken.

Sally 的问题

Sally 执行下面两个 SQL 语句, 为帮助我们记住它们, 分别记为 (min) 和 (max).

(max)  SELECT MAX(price) FROM Sells
         WHERE bar='Joe''s Bar';
(min)  SELECT MIN(price) FROM Sells
         WHERE bar='Joe''s Bar';

Joe 的程序

同时, Joe 执行下面的两个步骤: (del) 和 (ins).

(del)  DELETE FROM Sells
         WHERE bar='Joe''s Bar';
(ins)  INSERT INTO Sells
         VALUES ('Joe''s Bar','Heineken',3.50);

Interleaving of Statements

尽管 (max) 必须在 (min) 之前, 以及 (del) 必须在 (ins) 之前, 这些语句的顺序上没有其他的约束, 除非我们将 Sally 和/或 Joe 的语句组装进事务.

例子: Strange Interleaving

假设这四个语句执行的次序是 (max)(del)(ins)(min).

Joe's Prices:	{2.50, 3.00}	{2.50, 3.00}		{3.50}
Statement:	(max)	(del)	(ins)	(min)
Result:	3.00			{3.50}

Sally 发现 MAX < MIN!

利用事务解决这个问题

如果我们将 Sally 的 (max)(min)语句放到一个事务中, 则不会出现上述奇怪的不相容的现象.

她在某个固定的时刻看到 Joe 公布的价格.

无论在 Joe 公布价格之前还是之后, 或在公布期间, MAX 和 MIN 的操作对象都是同一组价格.

另一个问题: Rollback

假设 Joe 执行 (del)(ins) 这两个操作. 他又觉得不妥, 希望暂时回到原来的状态, 发布了回滚的语句. 与事务不同, 回滚语句是在执行这两个语句之后执行.

如果 Sally 在 (ins) 操作之后但在数据库回滚之前执行查询操作, 则她将看到查询到的最高价是 $\$ 3.50$, 而这个价格在数据库中不存在, 因为之后数据库执行了回滚操作.

解决方案

如果 Joe 以事务的方式执行 (del)(ins) 这两个操作, 它们的影响要直到事务被 COMMIT 语句执行之后才能被其他语句看到.

如果事务执行的是 ROLLBACK, 则事务中的操作等于没有发生, 因此什么也没有改变.

可串行化的事务(Serializable Transactions)

若 Sally 的操作是 (max)(min), 而 Joe 的操作是 (del)(ins). 并且 Sally 执行的是 SERIALIZABLE 的事务, 则她可以在 Joe 执行操作之前或之后都能看到数据库中的内容, 但不是在执行期间.

隔离层次的选择是事务本身的限制行为

比如你的选择是运行 serializable 的事务, 它所影响的仅是你本人（事务本身的操作）如何查看数据库, 而不是其他人（其他事务中的操作）. 因为根据事务的隔离层次的不同，DBMS会安排其运行时数据的隔离状态。

例如: 若 Joe 运行可串行化的事务, 但 Sally 并不是这样, 则 Sally 可能在执行过程中看不到 Joe's Bar 的价格.

则 Joe 是 ...
比如对 Sally 来说, 她就如在 Joe 的事务运行期间查看数据库一样.

“读提交”事务(Read-Commited Transactions)

若 Sally 运行的是隔离层次为 READ COMMITTED 的事务, 则她只能读取提交后的数据, 但不一定在同一时间得到的是同一数据.

例如: 在 READ COMMITTED 下, (max)(del)(ins)(min) 是允许的, 只要 Joe 提交了他的语句.

Sally 执行的是 {(max)(min)} 事务, 但不是 serializable 的, 因此在 (max) 和 (min) 之间 DBMS 可能会执行其他操作. 此时如果 (del)(ins) 被执行并提交(committed), 则
Sally 会看到 MAX < MIN.

可重复读事务(Repeatable-Read Transactions)

它的要求类似于 READ COMMITTED, 但要加上: 如果数据被重复读取, 则第一次检索到的数据与第二次重复该查询得到的数据是一样的.

但下一次查询或后续的执行可能会检索到更多的元组, 即有可能检索到幻像(phantom)元组.
幻像(phantom)元组是在该事务执行时数据库插入操作带来的元组.

例子: 可重复读事务

假设 Sally 在 REPEATABLE READ 层次执行事务, 并且执行的次序是 (max)(del)(ins)(min).

(max) 查询到的是 $\$ 2.50$ 和 $\$ 3.00$ 的最大值 $\$ 3.00$.
(min) 查询到的是 $\$ 2.50$, $\$ 3.00$ 和 $\$ 3.50$ 的最小值, 因为 $\$ 2.50$ 和 $\$ 3.00$ 也被保留了. 因为它们可在较早过程中被 (max) 所读取.
这个 $\$ 3.50$ 就是幻像(phantom)元组.

读未提交(Read Uncommitted)

运行于 READ UNCOMMITTED 层次的事务可以查询到数据库中的数据, 即使这个数据是由某个未提交事务写的(甚至可能从不).

例子: 若 Sally 在 READ UNCOMMITTED 层次下运行事务, 则她可以检索到 $\$ 3.50$, 即使 Joe 后来退出了.

MySQL下事务的实验

MySQL 的事务操作模式默认是自动提交模式。除非显式地开始一个事务，否则每个查询都被当做一个单独的事务自动执行。我们可以通过设置autocommit的值改变是否是自动提交autocommit模式。通过以下命令可以查看当前autocommit模式

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

使用 SET autocommit=0; 可以关闭自动提交模式。这里设置的值为0，或者等同的设为OFF. 1等同于ON.

mysql> SET autocommit=0;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM test1;
Empty set (0.05 sec)

mysql> INSERT INTO test1 VALUES(1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

这里 autocommit已经是OFF, 但是上面的 INSERT 语句仍然是执行的。我们执行回滚操作。

mysql> rollback;
Query OK, 0 rows affected (0.05 sec)

mysql> SELECT * FROM test1;
Empty set (0.00 sec)

mysql> SET autocommit=ON;
Query OK, 0 rows affected (0.04 sec)

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO test1 VALUES(10);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM test1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM test1;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

此时会发现, 无法“回滚”到上一个状态。因为此时默认自动 commit, 因此状态已经确定。换句话说，ROLLBACK只能对于尚未 commit 的事务有用。已经 commit 的事务，由于已经“实际永久”存储，已经无法回到修改之前的状态。

References:
http://www.qttc.net/201208175.html http://www.runoob.com/mysql/mysql-transaction.html

在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

事物控制语句：

BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT；
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。

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

1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式: SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1 开启自动提交

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)

mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)

 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)

 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)

 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)

 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)

 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)

 
mysql> select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql>

视图(Views)

视图(Views) 是指根据数据库中所存储的一个或多个表(称为基本表(base tables))以及其他视图所定义的关系.

因此, 它和通常的表十分类似. 可以使用 SELECT 语句查询视图中的数据. 视图可以动态生成.

视图的分类

视图分为两种:

虚拟视图(Virtual view): 不存储在数据库中; 通过类似查询的表达方式定义.
物化视图(Materialized view): 被实际构建和存储.

视图的作用

视图方便用户操作, 可以集中显示所需要的数据. 并且可以保障数据库系统的安全.

视图一经定义便存储在数据库中, 与其相对应的数据并没有像表那样在数据库中再存储一份.

通过视图看到的数据只是存放在基本表中的数据, 它们按照视图的定义虚拟地组织到一起.

当对视图中的数据进行修改时, 相应的基本表中的数据也要发生变化; 同样, 若基本表中的数据变更, 则着这种变化会自动反映到视图中.

视图的优点

什么情况需要使用视图

那些经常使用的查询可以被定义为视图.
需要考虑安全性, 限定用户的操作权限只能是表或若干个表的联合的某个子集.

安全性的理解

通过视图, 用户只能查询和修改他们所能见到的数据. 数据库中其他数据是无法看到的, 也无法修改.
数据库授权命令(GRANT)可以使每个用户对数据库的检索限制到特定的数据库对象上, 但不能授权到数据表的特定行和特定的列上. 通过视图, 用户的操作可以被限制在数据集的不同子集上.

逻辑数据的独立性

视图可帮助用户屏蔽基础表结构变化带来的影响.

声明视图

声明方式:
CREATE [MATERIALIZED] VIEW
<name> AS <query>;

默认是虚拟视图.

实验

根据 MySQL 5.5 的手册, MySQL 5.5 没有物化视图, 也没有表的快照(snapshots).

视图的建立命令为

CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS <query>
[WITH [CASCADED | LOCAL] CHECK OPTION]

REPLACE 表示替换已经创建的视图, 如果有 OR REPLACE 子句, 则必须在视图上具有 DROP 权限.
ALGORITHM 表示视图所用的算法:
- UNDEFINED 表示 MySQL 将自动选择算法;
- MERGE 表示将使用的视图语句与视图定义合并起来, 使得视图定义的某一部分取代语句对应的部分;
- TEMPTABLE 表示将视图的结果存入临时表, 然后用临时表来执行语句.
WITH [CASCADED | LOCAL] CHECK OPTION 表示视图在更新时保证在视图的权限范围之内.
- CASCADED 为默认值, 表示更新视图时要满足所有相关视图和表的条件;
- LOCAL 表示更新视图时满足该视图本身定义的条件即可;
CREATE VIEW ... 语句要求具有针对视图的 CREATE VIEW 权限, 以及针对 SELECT 语句选择的每一列上的某些权限. 对于在 SELECT 语句中其他地方使用的列, 必须具有 SELECT 权限.
默认情况下, 将对当前数据库创建新的视图. 如果要给指定数据库创建视图, 则应在视图名前加上数据库名, 形如 db_name.view_name

例子: 视图的定义

场景

假定情报人员需要收集某些人喜欢的啤酒种类, 以了解被调查人员的喜好. 但是他只能跟踪此人是否进入了某个酒吧, 等该人离去后, 他再去酒吧了解酒吧卖什么啤酒.

于是可以创建一个名为 mayDrink(drinker,beer) 的视图. 它“包含”了这样的 drinker-beer 对, 酒客(drinker)经常到卖这种啤酒(beer)的酒吧.

CREATE VIEW mayDrink AS
    SELECT drinker, beer
    FROM Frequents, Sells
    WHERE Frequents.bar=Sells.bar;

实验

test_bar 中的表有 7 个. 分别是:

mysql> show tables;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| Bars               |
| Beers              |
| Drinkers           |
| Frequents          |
| Likes              |
| PotBuddies         |
| Sells              |
+--------------------+
7 rows in set (0.00 sec)

现在建立一个名为 mayDrink 的视图.

mysql> CREATE VIEW mayDrink AS
    ->     SELECT drinker, beer
    ->     FROM Frequents, Sells
    ->     WHERE Frequents.bar=Sells.bar;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+--------------------+
| Tables_in_test_bar |
+--------------------+
| Bars               |
| Beers              |
| mayDrink           |
| Drinkers           |
| Frequents          |
| Likes              |
| PotBuddies         |
| Sells              |
+--------------------+
8 rows in set (0.00 sec)

注意不是 show views;

mysql> show create view mayDrink\G;
*************************** 1. row ***************************
                View: mayDrink
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`haifeng`@`localhost` SQL SECURITY DEFINER VIEW `mayDrink` AS select `Frequents`.`drinker` AS `drinker`,`Sells`.`beer` AS `beer` from (`Frequents` join `Sells`) where (`Frequents`.`bar` = `Sells`.`bar`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

使用 show create table mayDrink 也是可以的.

例子: 检索视图

可以把视图当作一个基本表(即存储的表)来查询.

如果是充分简单的视图, 有时称为可更新视图(updatable views), 则可以把对视图的更新转变成一个等价的对基本表的更新. 更新操作最终作用在基本表上.

查询的例子:

SELECT beer FROM CanDrink
WHERE drinker='Sally';

实验

mysql> select beer from CanDrink 
    -> where drinker='John von Neumann';
+-----------------+
| beer            |
+-----------------+
| Stella Artois   |
| 健力士黑啤      |
| 喜力            |
| 安贝夫          |
| 百威            |
| 科罗娜          |
| 米勒            |
| 纯种苦啤酒      |
| 贝克            |
| 麒麟            |
+-----------------+
10 rows in set (0.00 sec)

视图上的触发器(Triggers on Views)

一般的, 是不可能修改一个虚拟视图的, 因为它并不存在.

但是名为 INSTEAD OF 的触发器可以用来解释视图的修改在此种方式下是可行的.

例子: 视图 Synergy 具有 (drinker,beer,bar) 元组, 其中的 bar 销售 beer, drinker 经常去 bar, 且喜欢这种 beer.

例子: 视图

CREATE VIEW Synergy AS
  SELECT Likes.drinker, Likes.beer, Sells.bar
  FROM Likes, Sells, Frequents
  WHERE Likes.drinker=Frequents.drinker
      AND Likes.beer=Sells.beer
      AND Sells.bar=Frequents.bar;

SELECT ... 选取每个属性的一个拷贝
FROM ... WHERE ... 将三个关系 Likes, Sells, Frequents 作自然连接.

解释视图的插入

我们不能插入数据到 Synergy 中, 因为它是一个虚拟视图.

但是我们可以使用 INSTEAD OF 触发器将一个 (drinker,beer,bar) 三元组转化为三个针对关系 Likes, Sells, Frequents 的插入语句.

Sells.price 将不得不为 NULL.

INSTEAD OF 触发器

CREATE TRIGGER ViewTrig
  INSTEAD OF INSERT ON Synergy
  REFERENCING NEW ROW AS n
  FOR EACH ROW
  BEGIN
    INSERT INTO LIKES VALUES(n.drinker, n.beer);
    INSERT INTO SELLS(bar, beer) VALUES(n.bar, n.beer);
    INSERT INTO FREQUENTS VALUES(n.drinker, n.bar);
  END;

MySQL 目前尚不支持 instead of 触发器.

物化视图(Materialized Views)

问题: 每次基本表改变后, 物化视图也要跟着改变.

对于基本表的每次变化不能提供重新计算视图.

解决方案: 周期性重建物化视图, 否则会过时.

实验(under MySQL)

在 MySQL 下, 检查 CanDrink 中的内容是否会因其基本表中的内容改变而改变. 首先注意到 CanDrink 中有这样一条记录 atzjg | 南非啤酒

mysql> select * from CanDrink where drinker="atzjg";
+---------+-----------------+
| drinker | beer            |
+---------+-----------------+
| atzjg   | 健力士黑啤      |
| atzjg   | 南非啤酒        |
| atzjg   | 朝日            |
| atzjg   | 科罗娜          |
| atzjg   | 纯种苦啤酒      |
| atzjg   | 健力士黑啤      |
| atzjg   | 南非啤酒        |
| atzjg   | 喜力            |
| atzjg   | 朝日            |
| atzjg   | 科罗娜          |
| atzjg   | 纯种苦啤酒      |
+---------+-----------------+
11 rows in set (0.00 sec)

我们希望更改 atzjg 或者南非啤酒. 注意 CanDrink 的基本表是 Frequents 和 Sells. 不过 Sells 中有外键约束, 因此要更改南非啤酒, 必须通过更改 Beers 表才可以. 另一种方式是更改 atzjg. 这可以在 Frequents 中完成.

视图的其他操作

刚才讲到视图的插入本质上是对基本表的插入. 视图的更新也是对于基本表进行更新. INSERT, UPDATE, DELETE 都类似表.

实验(MySQL)

创建视图

假设我们已经创建了 mayDrink 视图. 查询其结构使用 DESC mayDrink;

mysql> desc mayDrink;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| drinker | char(20)    | NO   |     |         |       |
| beer    | varchar(20) | NO   |     |         |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

更改视图的结构

视图中属性来源于基本表, 因此如果要更改属性类型, 这个只能通过对基本表进行修改来实现. 比如现在将 drinker 的类型改为 varchar(30)

mysql> alter table frequents modify drinker varchar(30);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc maydrink;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| drinker | varchar(30) | NO   |     |         |       |
| beer    | char(20)    | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.04 sec)

更改视图的定义

使用 CREATE OR REPLACE. 下面是已经定义的视图 maydrink.

mysql> show create view maydrink\G;
*************************** 1. row ***************************
                View: maydrink
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `maydrink` AS select `frequents`.`drinker` AS `drinker`,`sells`.`beer` AS `beer` from (`frequents` join `sells`) where (`frequents`.`bar` = `sells`.`bar`)
character_set_client: gbk
collation_connection: gbk_chinese_ci
1 row in set (0.00 sec)

我们将其稍作修改:

mysql> CREATE or REPLACE VIEW mayDrink AS
    ->     SELECT drinker AS person, Frequents.bar, beer
    ->     FROM Frequents, Sells
    ->     WHERE Frequents.bar=Sells.bar;
Query OK, 0 rows affected (0.02 sec)

mysql> desc mayDrink;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| person | varchar(30)  | NO   |     |         |       |
| bar    | varchar(255) | NO   |     |         |       |
| beer   | char(20)     | YES  |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

删除视图

mysql> DROP VIEW mayDrink;
Query OK, 0 rows affected (0.00 sec)

例子: Axess/Class Mailing List

The class mailing list cs145-aut0708- students is in effect a materialized view of the class enrollment in Axess.

Actually updated four times/day. You can enroll and miss an email sent out after you enroll.

例子: 数据仓库(Data Warehouse)

沃尔玛(Wal-Mart)将每个店的销售情况存储在数据库中.

晚上, 一天的销售被用于更新数据仓库(data warehouse)= 销售 sales 的物化视图.

数据仓库是分析师用来预测趋势并将货物运送到卖得最好的地方.

索引(Indexes)

索引(Index): 是一种数据结构, 在给定一个或多个属性值时, 用于快速访问关系中的元组,

也可以是一个 hash 表, 但在 DBMS 中, it is always a balanced search tree with giant nodes (a full disk page) called a B-tree.

若不使用索引, 则 MySQL 必须从第一条记录开始读完整个表, 直到找到相关的行. 表越大, 查询数据所花费的时间越多.

如果表中查询的列有一个索引, MySQL 就能快速到达一个位置去搜寻数据文件, 而不必查看所有数据.

例如: 数据库中有 2 万条记录, 现在要执行这样一个查询:

SELECT * FROM myTable WHERE num=10000;

如果没有索引, 必须遍历整个表, 直到 num 等于 10000 的这一行被找到为止; 如果在 num 列上创建索引, 则 MySQL 不需要任何扫描, 直接在索引里面找 10000, 就可以得知这一行的位置. 索引的建立可以提高数据库的查询速度.

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

根据存储引擎定义每个表的最大索引数和最大索引长度.

所有存储引擎支持每个表至少 16 个索引, 总索引长度至少为 256 字节.

MySQL 中索引的存储类型有两种:

BTREE
HASH

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

索引的分类

MySQL 中的索引可以分为以下几类:

普通索引
- 允许在定义索引的列中插入重复值和空值.
惟一索引
- 索引列的值必须惟一, 但允许有空值.
单列索引
- 即索引是定义在单个列上的.
- 一个表可以有多个单列索引.
组合索引
- 即在表的多个属性组合上创建的索引.
- 使用组合索引时, 遵循最左前缀集合.
- 组合索引, 其列值的组合必须惟一.
全文索引
- 类型为 FULLTEXT, 在定义索引的列上支持值的全文查找.
- 允许在这些索引列中插入重复值和空值.
- 全文索引可以在 CHAR, VARCHAR 或 TEXT 类型的列上创建.
- MySQL 中只有 MyISAM 存储引擎支持全文索引.
空间索引
- 空间索引是对空间数据类型的属性建立的索引.
- MySQL 中的空间数据类型有 4 种. 分别是 GEOMETRY, POINT, LINESTRING, POLYGON.
- MySQL 使用 SPATIAL 关键字进行扩展, 使得能够用于创建正规索引类似的语法创建空间索引.
- 创建空间索引的列, 必须将其声明为 NOT NULL.
- 空间索引只能在存储引擎为 MyISAM 的表中创建.

索引的设计原则

索引并非越多越好.
对经常用于查询的属性应创建索引.
数据量小的表不要使用索引.
在不同值少的列上不要建立索引.
在指定 Unique 或 primary key 的列上, 使用惟一索引.
在频繁进行排序(ORDER BY)和分组(GROUP BY)的列上建立索引. 如果待排序的列有多个, 则可以在这些列上建立组合索引.

声明索引(Declaring Indexes)

没有标准!

CREATE INDEX BeerInd ON
    Beers (manf);
CREATE INDEX SellInd ON
    Sells (bar, beer);

实验(under MySQL)

创建普通索引

CREATE TABLE `Beers2` (
  `name` varchar(20) DEFAULT NULL,
  `manf` varchar(50) DEFAULT NULL,
  UNIQUE KEY `name` (`name`),
  INDEX(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

使用 SHOW CREATE TABLE 查看刚建立的表 Beers2.

mysql> show create table Beers2\G;
*************************** 1. row ***************************
       Table: Beers2
Create Table: CREATE TABLE `Beers2` (
  `name` varchar(20) DEFAULT NULL,
  `manf` varchar(50) DEFAULT NULL,
  UNIQUE KEY `name` (`name`),
  KEY `name_2` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

使用 EXPLAIN 语句查看索引是否正在使用.

mysql> explain select * from Beers2 where name="abc"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

ERROR: 
No query specified

别忘了我们还没有添加数据

mysql> insert into Beers2 values("abc","AB");
Query OK, 1 row affected (0.42 sec)

mysql> explain select * from Beers2 where name="abc"\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Beers2
         type: const
possible_keys: name,name_2
          key: name
      key_len: 63
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

select_type 行指定所使用的 SELECT 查询类型, 这里的值为 SIMPLE, 表示简单的查询, 不使用 UNION 或子查询. 其他可能的取值有: PRIMARY, UNION, SUBQUERY.

在已经存在的表上创建索引

在已经存在的表中创建索引, 可以使用 ALTER TABLE 语句或者 CREATE INDEX 语句.

ALTER TABLE创建索引的语法

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name](col_name[length],...[ASC | DESC]

mysql> ALTER TABLE Beers ADD INDEX BeerNameIdx(manf(20));
Query OK, 0 rows affected (0.49 sec)
Records: 0  Duplicates: 0  Warnings: 0

可以使用 SHOW INDEX FROM table_name 来查看索引.

mysql> show index from Beers\G;
*************************** 1. row ***************************
        Table: Beers
   Non_unique: 0
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 14
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: Beers
   Non_unique: 1
     Key_name: BeerNameIdx
 Seq_in_index: 1
  Column_name: manf
    Collation: A
  Cardinality: 14
     Sub_part: 20
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

创建惟一索引

创建惟一索引的主要原因是减少查询索引列操作的执行时间, 尤其是对比较大的数据表.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  UNIQUE INDEX UniqIdx(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建单列索引

先将刚才建立的 users 表删除.

DROP TABLE users;

一个表中可以创建多个单列索引.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  INDEX id_Idx(id),
  INDEX mobile_Idx(mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show create table users\G;
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  KEY `id_Idx` (`id`),
  KEY `mobile_Idx` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建组合索引

先将刚才建立的 users 表删除.

DROP TABLE users;

组合索引是指在多个属性上创建一个索引.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  INDEX multi_Idx(id,mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show create table users\G;*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  KEY `multi_Idx` (`id`,`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

创建全文索引

先将刚才建立的 users 表删除.

DROP TABLE users;

全文索引可以用于全文搜索, 只有 MyISAM 存储引擎支持 FULLTEXT 索引, 并且只为 CHAR, VARCHAR, TEXT 类型的列创建. 全文索引总是对整个列进行, 不支持局部(前缀)索引.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  `info` TEXT DEFAULT NULL,
  FULLTEXT INDEX info_Idx(info)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

MySQL5.5 中默认引擎为 InnoDB, 这里要改为 MyISAM, 不然创建引擎会出错.

下面建立的索引既是全文索引又是组合索引, 即全文组合索引.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  `info` TEXT DEFAULT NULL,
  FULLTEXT INDEX info_Idx(mobile,info)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建空间索引

空间索引也必须在 MyISAM 类型的表中创建, 且空间类型的属性必须非空.

在空间类型为 GEOMETRY 的属性上创建空间索引, 如下.

CREATE TABLE `users` (
  `id` varchar(20) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `mobile` varchar(15) DEFAULT NULL,
  `info` TEXT DEFAULT NULL,
  `g` GEOMETRY NOT NULL,
  SPATIAL INDEX spatIdx(g)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

mysql> show index from users\G;
*************************** 1. row ***************************
        Table: users
   Non_unique: 1
     Key_name: spatIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql> drop table users;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `users` (
    ->   `id` varchar(20) DEFAULT NULL,
    ->   `name` varchar(50) DEFAULT NULL,
    ->   `mobile` varchar(15) DEFAULT NULL,
    ->   `info` TEXT DEFAULT NULL,
    ->   `g` GEOMETRY,
    -> SPATIAL INDEX spatIdx(g)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL
mysql>

删除索引

可以使用 ALTER TABLE 或 DROP INDEX 语句删除索引.

mysql> alter table users drop index spatIdx;
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users\G;
Empty set (0.00 sec)

mysql> alter table users add spatial index spatIdx(g);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop index spatIdx on users;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用索引

给定值 $v$, 索引带我们到那些在索引中具有值 $v$ 的那些元组.

例子: 利用索引 BeerInd 和 SellInd 找出 Joe 的酒吧中出售的由 Pete's 生产的啤酒的价格.

SELECT price FROM Beers, Sells
WHERE manf='Pete''s' AND
    Beers.name=Sells.beer AND
    bar='Joe''s Bar';

利用索引 BeerInd 得到 Pete's 公司生产的所有啤酒.
然后利用索引 SellInd 得到 Joe's Bar 出售的那些啤酒的价格.

Database Tuning

使数据库跑得快的一个主要问题是决定要建立哪个索引.

正面: 索引会加快查询的速度.

反面: 索引也会降低其所属关系的所有更新速度, 因为关系的更新要求其索引也必须更新.

例子: Tuning

假设对于啤酒数据库我们要做的事情仅是下面两种:

插入新的记录(10%).
根据给定的酒吧及其出售的啤酒, 查询它的售价(90%).

则基于 Sells(bar,beer) 的索引 SellInd 将更有用的, 而基于 Beers(manf) 的索引 BeerInd 则是有害的.

微调顾问(Tuning Advisors)

是主要的研究推力.

因为微调是十分困难的.

An advisor gets a query load, e.g.:

从数据库的查询历史中选择随机的查询, 或者
设计者提供一个简单的工作量.

The advisor generates candidate indexes and evaluates each on the workload.

Feed each sample query to the query optimizer, which assumes only this one index is available.
Measure the improvement/degradation in the average running time of the queries.

End

Thanks very much!

This slide is based on Jeffrey D. Ullman's work, which can be download from his website.

首页

Haifeng Xu

(hfxu@yzu.edu.cn)

目录

为什么要研究事务(Transactions)

为什么要研究事务(Transactions)

例子: Bad Interaction

例子: Bad Interaction

事务(Transactions)

事务(Transactions)

数据库系统引入事务的目的

MySQL

ACID Transactions

ACID Transactions

详解

原子性(Atomicity)

一致性(Consistency)

隔离性(Isolation)

持久性(Durability)

例子

事务的分类

事务的分类

扁平事务

带有保存点的扁平事务

链事务

嵌套事务

分布式事务

隔离层次(Isolation Levels)

隔离层次(Isolation Levels)

选择隔离层次

选择隔离层次

实验(MySQL)

分布式事务

分布式事务

分布式事务的应用

XA 事务

提交(Commit)

提交(Commit)

实验1(MySQL)

实验2(MySQL)

比较执行时间

注意事项及总结

回滚(Rollback)

回滚(Rollback)

InnoDB 引擎的自动回滚

使用 Python 连接 MySQL 并调用此存储过程

长事务

长事务(Long-Lived Transactions)

例子: 插入进程

例子: 插入进程

Sally 的问题

Sally 的问题

Joe 的程序

Joe 的程序

Interleaving of Statements

Interleaving of Statements

例子: Strange Interleaving

例子: Strange Interleaving

利用事务解决这个问题

利用事务解决这个问题

另一个问题: Rollback

另一个问题: Rollback

解决方案

解决方案

可串行化的事务(Serializable Transactions)

可串行化的事务(Serializable Transactions)

隔离层次的选择是事务本身的限制行为

隔离层次的选择是事务本身的限制行为

“读提交”事务(Read-Commited Transactions)

“读提交”事务(Read-Commited Transactions)

可重复读事务(Repeatable-Read Transactions)

可重复读事务(Repeatable-Read Transactions)

例子: 可重复读事务

例子: 可重复读事务

读未提交(Read Uncommitted)

读未提交(Read Uncommitted)

MySQL下事务的实验

MySQL下事务的实验

视图(Views)

视图(Views)