of {$slidecount} ½ {$title} ATZJG.NET {$author}

首页






事务、视图、索引
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.

目录

为什么要研究事务(Transactions)

为什么要研究事务(Transactions)

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

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

例子: Bad Interaction

例子: Bad Interaction

你和你的国内合作伙伴在同一时间从不同的 ATM 机上都取了 $\$ 100$.

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

事务(Transactions)

事务(Transactions)

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

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

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

ACID Transactions

ACID Transactions

ACID Transactions 指:

可选的: 弱形式的事务经常也被支持.

提交(Commit)

提交(Commit)

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

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

回滚(Rollback)

回滚(Rollback)

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

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

例子: 插入进程

例子: 插入进程

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

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

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

Sally 的问题

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 的程序

同时, 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

Interleaving of Statements

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

例子: Strange Interleaving

例子: 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 公布的价格.

另一个问题: Rollback

另一个问题: Rollback

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

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

解决方案

解决方案

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

隔离层次(Isolation Levels)

隔离层次(Isolation Levels)

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

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

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

选择隔离层次

选择隔离层次

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

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

可串行化的事务(Serializable Transactions)

可串行化的事务(Serializable Transactions)

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

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

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

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

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

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

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

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

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

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

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

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

例子: 可重复读事务

例子: 可重复读事务

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

读未提交(Read Uncommitted)

读未提交(Read Uncommitted)

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

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

MySQL下事务的实验

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,用来禁止使用当前会话的自动提交。

事物控制语句:

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)

视图(Views) 是指根据存储的表(称为base tables)和其他视图定义的关系.

分为两种:

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

声明视图

声明视图

声明方式:
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]

例子: 视图的定义

例子: 视图的定义

场景

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

于是可以创建一个名为 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)
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 也是可以的.

例子: 检索视图

例子: 检索视图

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

查询的例子:

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)

视图上的触发器(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;

解释视图的插入

解释视图的插入

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

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

INSTEAD OF 触发器

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)

物化视图(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 中完成.

例子: Axess/Class Mailing List

例子: 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)

例子: 数据仓库(Data Warehouse)

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

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

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

索引(Indexes)

索引(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 万条记录,

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

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

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

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

索引的分类

索引的分类

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

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

索引的设计原则

索引的设计原则

声明索引(Declaring Indexes)

声明索引(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)

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

在已经存在的表中创建索引, 可以使用 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 TABLEDROP 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$ 的那些元组.

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

SELECT price FROM Beers, Sells
WHERE manf='Pete''s' AND
    Beers.name=Sells.beer AND
    bar='Joe''s Bar';
  1. 利用索引 BeerInd 得到 Pete's 公司生产的所有啤酒.
  2. 然后利用索引 SellInd 得到 Joe's Bar 出售的那些啤酒的价格.

Database Tuning

Database Tuning

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

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

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

例子: Tuning

例子: Tuning

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

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

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

微调顾问(Tuning Advisors)

微调顾问(Tuning Advisors)

是主要的研究推力.

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

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

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

End






Thanks very much!

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