分类： 数据库

关于MySQL InnoDB表的二级索引是否加入主键列的问题解释

leokim / / MYSQL, 数据库

  • 关于MySQL InnoDB表的二级索引是否加入主键，总结如下：

1对于MySQL InnoDB表的二级索引是否加入主键，官方也有明确的说明，建议线上MySQL的二级索引创建时强制加入主键所有的列，可以做到所有的MySQL 版本统一。

2.MySQL 5.6.9之前，InnoDB引擎层是会对二级索引做自动扩展，但是优化器不能识别出扩展的主键。

3.MySQL 5.6.9开始InnoDB引擎层是会对二级索引做自动扩展，优化器能识别出扩展的主键。

4.索引的大小一样，二级索引有没有加入主键列，在InnoDB引擎层二级索引都会自动扩展主键，这个跟版本无关。

5.有无加入主键列，二级索引的组织结构和物理大小是一样，因为在存储引擎层面组织结构是一样的。

6.在优化器层面，5.6.9之前是无法识别自动扩展的主键列，从5.6.9开始优化器的开关 use_index_extensions=on是可以识别扩展的主键列，所以在二级索引加入主键列有有利的。这也可以做到与版本无关，做到所有MySQL版本统一。

总结：加主键列，有利无害。

*下面是我的演示实例：

一.下面是在MySQL 5.5.36-log：

xxx 5.5.36-log test 11:33:54>CREATE TABLE t1 ( 
-> i1 INT NOT NULL DEFAULT 0, 
-> i2 INT NOT NULL DEFAULT 0, 
-> d DATE DEFAULT NULL, 
-> PRIMARY KEY (i1, i2), 
-> INDEX k_d (d) 
-> ) ENGINE = InnoDB; 
Query OK, 0 rows affected (0.07 sec)

插入了25行数据后：

xxxx 5.5.36-log test 11:40:01>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: t1 
type: ref 
possible_keys: PRIMARY,k_d 
key: k_d 
key_len: 4 
ref: const 
rows: 5 
Extra: Using where; Using index

分析：key_len 是4，只用到了d这列（date类型key长度是3byte，key_len=3+1byte长度）没有扩展主键。 ref：只有一个const：表明优化器只用到了i1这列。 using where;using index：已经回表了。

************************************************************************************************************************************************

下面我添加索引：`k_d_2`(d,i1,i2)

alter table t1 add key `k_d_2`(d,i1,i2);     

xxx 5.5.36-log test 11:36:11>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G 

*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: t1 
type: ref 
possible_keys: PRIMARY,k_d,k_d_2 
key: k_d_2 
key_len: 8 
ref: const,const 
rows: 1 
Extra: Using where; Using index

分析：key: k_d_2 和 key_len 是8，说明扩展主键。 ref：有2个const：表明优化器用到了i1这列。rows: 1 也说明用到了主键。

二. 同时我在MySQL 5.6.16-log也做了创建同样的表：

lxxx  5.6.16-log test 08:20:46>show variables like '%optimizer_switch%';

firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on….

use_index_extensions 已经打开。


xxx 5.6.16-log test 08:20:46>CREATE TABLE t1 (

    ->   i1 INT NOT NULL DEFAULT 0,
    ->   i2 INT NOT NULL DEFAULT 0,
    ->   d DATE DEFAULT NULL,
    ->   PRIMARY KEY (i1, i2),
    ->   INDEX k_d (d)
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

xxx 5.6.16-log test 08:21:04>EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

分析：key: k_d_2 和 key_len 是8，说明MySQL 自动对二级索引做了扩展主键。 ref：有2个const：表明优化器识别了扩展主键。

三.索引大小：

在二级索引后面加上主键列，存储空间不会增加。


下面是我的分析：

一.下面是MySQL 5.6.16：

CREATE TABLE `t1` ( 
`i1` int(11) NOT NULL DEFAULT '0', 
`i2` int(11) NOT NULL DEFAULT '0', 
`d` date DEFAULT NULL, 
PRIMARY KEY (`i1`,`i2`), 
KEY `k_d` (`d`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; 

CREATE TABLE `tt1` ( 
`i1` int(11) NOT NULL DEFAULT '0', 
`i2` int(11) NOT NULL DEFAULT '0', 
`d` date DEFAULT NULL, 
PRIMARY KEY (`i1`,`i2`), 
KEY `k_d` (`d`,`i1`,`i2`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


通过储存过程对表插入数据：

call proc_insert(500000); 插入50w行数据：

下面是索引的大小，大小一样：

xxx test 03:38:36>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='t1';
+————–+ 
| index_length | 
+————–+ 
| 8929280 | 
+————–+ 
1 row in set (0.00 sec) 
xxx 5.6.16-log test 03:43:42>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='tt1'; 
+————–+ 
| index_length | 
+————–+ 
| 8929280 | 
+————–+ 
1 row in set (0.01 sec)

数据文件大小，大小也是一样的：

-rw——- 1 mysql myinstall 36M 1月 23 15:38 t1.ibd 
-rw——- 1 mysql myinstall 36M 1月 23 15:39 tt1.ibd


二.下面是MySQL 5.5.36：

表t1、tt1和上面的结构一致。

索引大小：

xxx 5.5.36-log (none) 03:48:05>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='t1'; 
+————–+ 
| index_length | 
+————–+ 
| 8929280 | 
+————–+ 
1 row in set (0.00 sec) 

xxx 5.5.36-log (none) 03:48:06>SELECT index_length FROM information_schema.TABLES WHERE table_schema='test' and table_name='tt1'; 

+————–+ 
| index_length | 
+————–+ 
| 8929280 | 
+————–+ 
1 row in set (0.00 sec)

数据文件大小：也是一样

-rw-rw—-. 1 mysql myinstall 36M 1月 23 15:39 tt1.ibd 
-rw-rw—-. 1 mysql myinstall 36M 1月 23 15:39 t1.ibd


引申：

关于key_len及create table的规范：

key_len:

1.对于定长数据类型(int、char(N)、date等)实际字段类型的字节数，如果字段不是not null，则还需1byte存储字段是否为空。

2.对于不定长数据类型(varchar(N)、datetime(mysql 5.6开始是变长)等)实际字段类型的字节数 + 2byte储存字段长度，如果字段不是not null，则还需1byte存储字段是否空。


所以在创建表的时候：

create table txxx(

id int …

c1 varchar(30) not null default '0000'

)

也可以起到减少二级索引的长度。

MySQL事务autocommit自动提交

leokim / / MYSQL, 数据库

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

通过以下命令可以查看当前autocommit模式

1
2
3
4
5
6
7
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.04 sec)

从查询结果中，我们发现Value的值是ON，表示autocommit开启。我们可以通过以下SQL语句改变这个模式

1
mysql> set autocommit = 0;

值0和OFF都是一样的，当然，1也就表示ON。通过以上设置autocommit=0，则用户将一直处于某个事务中，直到执行一条commit提交或rollback语句才会结束当前事务重新开始一个新的事务。

举个例子：

张三给李四转账500元。那么在数据库中应该是以下操作：

1，先查询张三的账户余额是否足够

2，张三的账户上减去500元

3，李四的账户上加上500元

以上三个步骤就可以放在一个事务中执行提交，要么全部执行要么全部不执行，如果一切都OK就commit提交永久性更改数据；如果出错则rollback回滚到更改前的状态。利用事务处理就不会出现张三的钱少了李四的账户却没有增加500元或者张三的钱没有减去李四的账户却加了500元。

MySQL默认的存储引擎是MyISAM，MyISAM存储引擎不支持事务处理，所以改变autocommit没有什么作用。但不会报错，所以要使用事务处理的童鞋一定要确定你所操作的表示支持事务处理的，如InnoDB。如果不知道表的存储引擎可以通过查看建表语句查看建表的时候有没有指定事务类型的存储引擎，如果没有指定存储引擎默认则是MyISAM不支持事务的存储引擎。

当然，事务处理是为了保障表数据原子性、一致性、隔离性、持久性。这些都是要消耗系统资源的，要谨慎选择。

mysql事务，select for update，及数据的一致性处理

leokim / / MYSQL

在MySQL的InnoDB中，预设的Tansaction isolation level 为REPEATABLE READ（可重读）

在SELECT 的读取锁定主要分为两种方式：

　　SELECT … LOCK IN SHARE MODE　

　　SELECT … FOR UPDATE

　　这两种方式在事务(Transaction) 进行当中SELECT 到同一个数据表时，都必须等待其它事务数据被提交(Commit)后才会执行。

　　而主要的不同在于LOCK IN SHARE MODE 在有一方事务要Update 同一个表单时很容易造成死锁。

　　简单的说，如果SELECT 后面若要UPDATE 同一个表单，最好使用SELECT … UPDATE。

　　举个例子:

　　假设商品表单products 内有一个存放商品数量的quantity ，在订单成立之前必须先确定quantity 商品数量是否足够(quantity>0) ，然后才把数量更新为1。代码如下:

SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;

 

为什么不安全呢?

少量的状况下或许不会有问题，但是大量的数据存取「铁定」会出问题。如果我们需要在quantity>0 的情况下才能扣库存，假设程序在第一行SELECT 读到的quantity 是2 ，看起来数字没有错，但

是当MySQL 正准备要UPDATE 的时候，可能已经有人把库存扣成0 了，但是程序却浑然不知，将错就错的UPDATE 下去了。因此必须透过的事务机制来确保读取及提交的数据都是正确的。

 

于是我们在MySQL 就可以这样测试，代码如下:

SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;

此时products 数据中id=3 的数据被锁住(注3)，其它事务必须等待此次事务 提交后才能执行

SELECT * FROM products WHERE id=3 FOR UPDATE 如此可以确保quantity 在别的事务读到的数字是正确的。

UPDATE products SET quantity = '1' WHERE id=3 ; COMMIT WORK;

提交(Commit)写入数据库，products 解锁。

注1: BEGIN/COMMIT 为事务的起始及结束点，可使用二个以上的MySQL Command 视窗来交互观察锁定的状况。

注2: 在事务进行当中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT … 则不受此影响。

注3: 由于InnoDB 预设为Row-level Lock，数据列的锁定可参考这篇。

注4: InnoDB 表单尽量不要使用LOCK TABLES 指令，若情非得已要使用，请先看官方对于InnoDB 使用LOCK TABLES 的说明，以免造成系统经常发生死锁。

 

 

MySQL SELECT … FOR UPDATE 的Row Lock 与Table Lock

上面介绍过SELECT … FOR UPDATE 的用法，不过锁定(Lock)的数据是判别就得要注意一下了。由于InnoDB 预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

举个例子:

假设有个表单products ，里面有id 跟name 二个栏位，id 是主键。

例1: (明确指定主键，并且有此数据，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

例2: (明确指定主键，若查无此数据，无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

例2: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

例3: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

乐观锁和悲观锁策略

悲观锁：在读取数据时锁住那几行，其他对这几行的更新需要等到悲观锁结束时才能继续 。

乐观锁：读取数据时不锁，更新时检查是否数据已经被更新过，如果是则取消当前更新，一般在悲观锁的等待时间过长而不能接受时我们才会选择乐观锁。

数据库中INFORMATION_SCHEMA的说明及使用

leokim / / MYSQL, 数据库

第一个查询看看库里有多少个表，表名等

select * from INFORMATION_SCHEMA.TABLES

information_schema这张数据表保存了MySQL服务器所有数据库的信息。如数据库名，数据库的表，表栏的数据类型与访问权限等。再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information_schema表里面。

Mysql的INFORMATION_SCHEMA数据库包含了一些表和视图，提供了访问数据库元数据的方式。

元数据是关于数据的数据，如数据库名或表名，列的数据类型，或访问权限等。有些时候用于表述该信息的其他术语包括“数据词典”和“系统目录”。

 

下面对一些重要的数据字典表做一些说明：

SCHEMATA表：提供了关于数据库的信息。

TABLES表：给出了关于数据库中的表的信息。

COLUMNS表：给出了表中的列信息。

STATISTICS表：给出了关于表索引的信息。

USER_PRIVILEGES表：给出了关于全程权限的信息。该信息源自mysql.user授权表。

SCHEMA_PRIVILEGES表：给出了关于方案（数据库）权限的信息。该信息来自mysql.db授权表。

TABLE_PRIVILEGES表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。

COLUMN_PRIVILEGES表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。

CHARACTER_SETS表：提供了关于可用字符集的信息。

COLLATIONS表：提供了关于各字符集的对照信息。

COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。

TABLE_CONSTRAINTS表：描述了存在约束的表。

KEY_COLUMN_USAGE表：描述了具有约束的键列。

ROUTINES表：提供了关于存储子程序（存储程序和函数）的信息。此时，ROUTINES表不包含自定义函数（UDF）。

VIEWS表：给出了关于数据库中的视图的信息。

TRIGGERS表：提供了关于触发程序的信息。

深入理解数据库磁盘存储（Disk Storage）

leokim / / 数据库, 数据结构与算法

数据库管理系统将数据存储在磁盘、磁带以及其他的裸设备上，虽然这些设备的访问速度相比内存慢很多，但其非易失性和大容量的特点使他们成为数据存储的不二之选。

本文主要讨论大型数据库产品的磁盘存储内部结构，这对于深入理解数据库各种数据结构具有至关重要的作用。

数据库磁盘存储的体系结构


以上两图分别展示了存储器分级结构以及磁盘内部物理结构，不是本文重点，不赘述。需要强调的是：一次完整的输入输出（IO）操作的时间=磁盘轴旋转时间（旋转延迟）+磁盘臂移动时间（寻道时间）+数据传输时间。三者所需时间的平均经验值为：0.004秒、0.008秒和0.0005秒。所以，一次完整的IO时间的经验值是0.0125秒，即1/80秒。

对于大型数据库而言，即便是这极短暂的0.0125秒，频繁的IO操作会将这微不足道的时间累积得非常可观，因此磁盘存储的优化对于数据库效率的提升是非常必要和重要的。不同的数据库产品的磁盘存储内部实现是不同的，本文只讨论Oracle、DB2大型数据库产品，二者细节上虽有不同，但结构大体上是一样的。

Oracle和DB2数据库的存储模型如图：


可以看出，数据库中的数据都存储在表空间中。表空间即是管理将逻辑数据库设计映射到操作系统物理存储中的一个数据库对象，用于指明数据的物理位置。关于表空间，以后再讨论。

Oracle数据库磁盘存储的逻辑结构为：一个数据库（Database）对应多个表空间（Tablespace），一个表空间对应多个段（Segment），一个段对应多个区（Extent），一个区对应多个数据块（Data Block），真正的数据就保存在数据块中。这里有以下几点需要说明：

1.Oracle中一个数据块的大小默认是2KB（支持2KB,4KB,8KB，16KB,32KB），而DB2中则默认是4KB（支持4KB,8KB,16KB,32KB）；

2.Oracle中有段（Segment）的概念，而DB2中没有这一概念，表空间直接是各个容器（数据文件）中的区（Extent）组成，不过也还是有一个很弱化的Extent组概念。下面提到的关于Segment的内容则全部是针对Oracle的；

3.Oracle中的数据块称为Oracle Block，而DB2中则直接称为Data Page（数据页）。

4.Oracle中的Extent称为区，而DB2中则称为扩展数据块。为方便阅读，本文中统称为区。

                                 Oracle磁盘存储逻辑结构


  

                                           DB2磁盘存储逻辑结构

要注意：这里的表空间，段，区，数据块（页）全部都是数据库中的逻辑概念，并不是物理存在的，那么数据库磁盘存储的逻辑结构如何映射到操作系统磁盘存储的物理结构中呢？

先来看看表空间的物理映射。表空间在操作系统上是由容器（Container）承载的，对于系统管理表空间（SMS）【注意：Oracle不存在系统管理表空间，其表空间全部都是数据库管理的】，其唯一的容器是文件目录；而对于数据库管理表空间，其容器则是文件或者裸设备（比如磁带、磁盘柜）。这里我们不讨论系统管理表空间，只关注数据库管理表空间下的数据库磁盘存储。由于数据库对于文件和裸设备这两种容器操作上是同等对待的，所以以文件容器为例进行讨论。文件容器本身是一个由DMS表空间使用的预分配大小的文件，一个表空间可以有多个文件容器，即有多个数据文件。也就是说：一个逻辑上的表空间映射为多个物理上的数据文件。

再来讨论数据块（页）的物理映射。我们知道，物理结构上，操作系统中的文件是由多个操作系统的块（Block）组成的（Linux，Unix系统的块大小为512B，而Windows系统的块大小为1KB），而上面说了，数据库中的数据是以数据块（页）为单位存放的，，那么数据库中的数据块（页）和操作系统的块是什么关系呢？事实上，若干个操作系统块组成一个数据库的数据块（页）。对应区（extent）和段（segment）的映射并没有物理单位与之对应，而是在一个数据文件中会包含多个段，每个段里又包含多个区（每个段中的区的个数不一定是一样的；DB2中数据文件中直接以区为单位，没有段的概念），每个区包含若干数据块（每个区中数据块的数量也不一定一样）。另外，和表空间一样，段也是可以跨文件的，即一个段可以由不同文件中的区所组成。

数据库磁盘存储的逻辑/物理映射图解如下（DB2中没有Segment这一层）：


关于这些逻辑单位的具体讨论以后再说。现在只需要了解大体的体系结构。通过上面的介绍可以看到，数据库管理的表空间自成一体，具有平台无关性，俨然是一个小型的独立文件系统了。

数据库磁盘存储的内部实现

了解了磁盘存储的体系结构，再来看一看数据库系统中，数据具体是如何进行存储的。

当创建一张表（Table）的时候，会为其指定表空间，一旦表成功创建，数据库系统就要为表提供磁盘空间。

Oracle数据库会自动为一张表分配一个Segment（段），这个Segment称为Data Segment（数据段）【注：一张表只能被分配一个数据段。Oracle一共有四种类型的段，分别是Data Segment（数据段），Index Segment（索引段），Rollback Segment（回滚段）和Temp Segment（临时段）】。当为表分配的数据段全部写满的时候，数据库管理系统会为这个数据段增加新的区（Extent），也就是说，数据段空间分配完后并不是需要多少空间就为段增加多少空间，也不是直接在区中增加数据块，而是一次性增加一个Extent（这样做避免了频繁的Segment扩容），Extent是空间分配的最小单位，而且Extent在表空间中的各个容器上是均衡分配的。另外，数据块（页）是最小的存储单位，也即最小的I/O单位，所有数据都是按块（页）存储，读出的时候也是直接将整个数据块（页）读入内存中的。至于DB2，其方案与Oracle基本相同，所不同的是没有Segment分配的问题。

我们以DB2为例，看看数据存储具体是怎样的。【参考牛新庄：深入解析DB2】

一个DMS表空间可以有多个容器（文件或裸设备），DB2将Extent均衡的写到各个容器上。即当需要请求一个Extent时，数据库管理器这个Extent分配到下一个容器上。这种方案保证了各容器的均衡利用，提高了并行访问效率。如左图：

                                         表空间容器均衡写


                      表空间容器，Extent，数据页和表空间之间的关系


新建一个称为HUMANRES的DMS表空间，其Extent大小为2个Page（即数据块），每个Page大小为4KB。表空间有4个容器，每个容器内有少量已分配的Extent：表空间中DEPARTMENT和EMPLOYEE表中都占用了7个Page，按照上面介绍的分配规则，这两个表中的数据都会写到四个不同的容器中（7个Page需要分配4个Extent，每个Extent依次分配到各个容器中），另外，一个Extent只能由一个表所写，即便表数据不能完全利用Extent中的空间，Extent中的空闲空间也只能空着，不能继续写入其他表中的数据（这是由Extent是空间最小分配单位决定的）。如右图。

数据块（页）存储

下面进一步深入，分析一下数据库磁盘存储最小单元数据块（Data Block or Data Page）内的具体结构是怎样的。对于Oracle和DB2，二者的数据块结构是不同的。以下分别讨论。至于其他的数据库产品，不在讨论之列。

Oracle数据库的数据块（Data Block）结构及相关特性

（参照：http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm）

数据块格式

Oracle 数据块的格式无论是表、索引还是cluster 数据，格式都是很类似的，如图：


公共和变量块头( Common and Variable Header)

头部包含了通用块信息，例如块的地址和段的类型 ( 例如表或者索引 )

表目录（Table Directory）

这部分信息包含了在这个块中该表或该索引的相关信息。

行目录（Row Directory）

这部分包含了数据块中的实际行的信息 ( 包括行数据区域中每行的地址 ) ，一旦数据块头部的这个行目录的空间被分配了，那么即使该行删除了，这段空间仍然不能回收。

因此一个当前为空的数据块，此区域包含数据块中存储的数据行的信息（每个数据行片断（ row piece ） 在行数据区（ row data area ）中的地址）。 [ 一个数据块中可能保存一个完整的数据行，也可能只保存数据行的一部分 ，所以文中使用 row piece] 。只有在数据块中插入（ insert ）新数据时，行目录区空间才会被重新利用。

头部信息区（Overhead ）

块头（ header/Common and Variable ），表目录（ Table Directory ），行目录（ Row Directory ）这三部分合称为头部信息区（ Overhead ）。头部信息区不存放数据，它存放的整个块的信息。头部信息区的大小是可变的。一般来说，头部信息区的大小介于 84 字节（ bytes ）到 107 字节（ bytes ）之间。

可用空间（Free Space）

可用空间是一个块中未使用的区域，这片区域用于新行的插入和已经存在的行的更新。可用空间也包含事务条目，当每一次 insert 、 update 、 delete 、 select ..for update 语句访问块中一行或多行数据，将会请求一条事务条目，事务条目的请求空间与操作系统相关，在多数操作系统中大约所需 23 个字节。

行数据（Row Data）

这部数据块包含了表或索引的数据，行也可能跨数据块，这也就是行迁移现象。


可用空间管理(Free Space Management)

可用空间可能是自动或人工管理。

可用空间是由 Oracle 内部的段自动管理的，段内的可用 / 已用空间用位图来跟踪。自动段空间管理提供了以下好处：

l  使用便捷

l  更好的空间利用率，特别是那些行大小变化很大的对象

l  并发访问的动态调整

l  性能 / 空间的平衡

数据块可用空间的利用和压缩

Delete 和 update( 把原值变小 ) 可增加数据块的可用空间。在以下情况下 insert 语句才能有效地利用已释放的空间。

假如 insert 语句在同一个事务中，而 insert 前面的语句刚好释放了相应的空间，这时候 insert 语句可以利用该空间

假如 insert 语句与释放空间的语句不在同一个事务中，那么只有当其他事务提交后并且刚好需要空间的时候， insert 语句才能利用该空间。

释放的空间也可能不是连续的，只有当 以下两个条件都满足的时候， Oracle 才会合并和压缩数据块的可用空间。

1 一个 insert 或 update 语句试图使用足够空间创建新行的时候；

2 自由空间是分散的以至于不能插入毗邻空间的时候；

这就是所谓的“块重组（Block Reorganization）”。事实上，Oracle和DB2在对待碎片空间上的策略是一致的，即：行数据被删除后，该空间空置，当块空间不足时进行重组。而其他数据库的策略是行数据删除后，其他行数据顺移以保证可用空间是连续的。显然，这种做法影响数据库效率。

 

行链接和行迁移（Row Chaining and Migrating ）

行链接（ Row Chaining ）：如果我们往数据库中插入（ INSERT ）一行数据，这行数据很大，以至于一个数据块存不下一整行， Oracle 就会把一行数据分作几段存在几个数据块中，这个过程叫行链接（ Row Chaining ）。

如果一行数据是普通行，这行数据能够存放在一个数据块中；如果一行数据是链接行，这行数据存放在多个数据块中。

行链接又称为行跨页，Oracle允许行跨页，但DB2是不允许的。


行迁移 (Row Migrating) ：数据块中存在一条记录，用户执行 UPDATE 更新这条记录，这个 UPDATE 操作使这条记录变长，这时候， Oracle 在这个数据块中进行查找，但是找不到能够容纳下这条记录的空间，无奈之下， Oracle 只能把整行数据移到一个新的数据块。原来的数据块中保留一个“指针”，这个“ 指针”指向新的数据块。被移动的这条记录的 ROWID 保持不变。

 


PCTFREE和PCTUSED

PCTFREE 参数用于指定块中必须保留的最小空闲空间百分例。之所以要预留这样的空间，是因为 UPDATE 时，需要这些空间。如果 UPDATE 时，没有空余空间， Oracle 就会分配一个新的块，这会产生行迁移（ Row Migrating ），进行空间预留能够一定程度上保证数据库访问效率。

PCTUSED 也是用于设置一个百分比，当块中已使用的空间的比例小于这个百分比的时候，这个块才被标识为有效状态。只有有效的块才被允许插入数据。

PCTFREE和PCTUSED作用示意图如下：

DB2数据库的数据页（Data Page）结构及相关特性

（参照：http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.admin.perf.doc%2Fdoc%2Fc0007337.html）

数据页格式

在标准表中，数据在逻辑上按数据页的列表进行组织。这些数据页根据表空间的Extent大小在逻辑上分组到一起。这个Extent组类似于Oracle中的Segment，但Extent组没有Segment那样的强制概念。

每个数据页都具有相同的格式。每一页的最前面都是页头，后面跟着槽（Slot）目录，然后是可用空间和记录。

页头（Header）

用于存放BPS HEADER和一些页的信息字段，其中BPS HEADER占48字节，整个页头大概占91个字节。

槽目录（Slot Directory）

槽目录类似Oracle的数据块中的行目录，槽目录中的每个条目都与该页中的一个记录相对应。槽目录中的条目代表记录开始位置在数据页中的字节偏移。值为 -1 的条目与已删除的记录相对应。

可用空间（Free Space）

DB2可用空间严格来说包括通常意义上的常规可用空间和嵌入的可用空间。其中常规可用空间可用直接存储数据，而嵌入的可用空间通常是记录被删除后产生的碎片空间，不能直接使用，只有当页重组后合并到常规可用空间后才能被使用，这一点和Oracle类似。

记录（Record）

已经存储了数据的空间，类似于Oracle中的行数据。即表中的行存放于页面中成为记录。根据数据页大小以及记录大小的不同，每个数据页中包含的记录数（即行数据数）也会有所变化。大多数页仅包含用户记录（即普通的数据库数据）。但是，少数页包含由数据服务器用于管理表的特殊内部记录。例如，在标准表中，每 500 个数据页就有一个可用空间控制记录（FSCR）。这些记录用于映射后续每 500 个数据页（直到下一个 FSCR 为止）中可供新记录使用的可用空间。另外，在DB2 V9之前，每个数据页最多可以存放255条记录，而DB2 V9之后，每个数据页理论上可以存放65000条记录（与RID有关，暂不讨论），但实际上受限于数据页大小，每个数据页大概能存放2300多条记录。

行链接和行迁移

DB2是不允许行（记录）跨页的，即不允许行链接。但是允许行迁移（DB2中又称为行溢出），

当表中存在变长数据类型时，容易发生行溢出现象，行溢出有时也叫行迁移，它表示当我们更新变长数据类型的字段时，由于更新的数据长度比原数据长，以至于当前的数据页无法存放整行数据时，就需要把这行数据存放到一个新的数据页，并在原来的数据页内存放该行新位置的指针以指向新行位置，被移动的数据的RID（RID以后讨论）保持不变。显然，如果大规模出现行迁移的现象，那么必然会对数据库访问的效率产生严重影响（I/O大幅增加），如果表中发现大量行迁移现象，建议进行重组（REORG）操作，重新规划数据存储位置消除行溢出。



对应DB2数据页还需要强调的一点是：DB2的数据页和Oracle数据块一样，也有PCTFREE和PCTUSED的概念，其含义与作用也大致相同，不赘述。


另外可以看到，Oracle的数据块结构和DB2的数据页结构是非常相似的。还有一点，二者的行数据都是从高位开始向低位写，而行目录则是由低位向高位写，为什么要这样呢？因为数据的插入是一个两头写的过程，既要将数据写入到可用空间中成为行数据或记录，又要更新头部后面的行目录或槽目录，如果同侧写不利于空间的扩展。下图是一个直观图：



数据行结构

现在对数据块（页）的具体结构已经有了一个大概的了解，那么表中的一行数据是怎样以行数据（记录）的形式保存在数据块（页）中的呢？这个问题看似简单，实际上，一个数据行（记录，即上图中的Row）的结构是非常复杂的，这里以ＤB2中数据行的结构为例。如图：


各参数解释如下：


可以看到，当元组（即表中的行）中存在变长列的时候，不管该列位于什么位置，在数据行（记录）中都被挪到最后面存储，而定长列则顺序存储。

【注：表的属性列在定义的时候指定了数据类型，有的数据类型是变长的，比如varchar，其大小随实际值的变化而变化，有的列数据类型的定长的，比如int为32位，物理该列上的实际值是多少，都占用4个字节。一旦列中出现变长数据类型的列，则该元组为变长元组。】

下图是状态位A的每一位的含义（1个字节=8位），状态位B是未使用的。


下面看看一个定长的元组（行）在数据行（记录）中具体是如何存放的：


有些复杂，将此图与前面贴的结构图对照着看。【注意：所有数据在数据页的数据行（记录）中都是以十六进制存放的，且为逆序存放】

下面是变长的元组（行）在数据行（记录）中具体存放方式，与定长元组的存放方式大同小异：


对于含大对象数据类型的元组，其大对象数据并不与数据行存放在一起，而是与数据行分开放置于数据库的不同页中，在该元组（行）的数据行中存放一个指向该大对象的指针。如图：

MySQL ACID及四种隔离级别的解释

leokim / / MYSQL, 技术, 数据库

以下内容出自《高性能MySQL》第三版，了解事务的ACID及四种隔离级有助于我们更好的理解事务运作。

下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

1、检查支票账户的余额高于或者等于200美元。

2、从支票账户余额中减去200美元。

3、在储蓄帐户余额中增加200美元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

 

可以用START TRANSACTION语句开始一个事务，然后要么使用COMMIT提交将修改的数据持久保存，要么使用ROLLBACK撤销所有的修改。事务SQL的样本如下：

1. start transaction;

2. select balance from checking where customer_id = 10233276;

3. update checking set balance = balance – 200.00 where customer_id = 10233276;

4. update savings set balance = balance + 200.00 where customer_id = 10233276;

5. commit;

 

ACID表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个很好的事务处理系统，必须具备这些标准特性：

 

原子性（atomicity）

　　一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性（consistency）

     数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性（isolation）

     通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

持久性（durability）

　　一旦事务提交，则其所做的修改不会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。持久性是个有占模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必，而且不可能有能做到100%的持久性保证的策略。）

 

隔离级别：

READ UNCOMMITTED（未提交读）

　　在READ UNCOMMITTED级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，READ UNCOMMITTED不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非真的有非常必要的理由，在实际应用中一般很少使用。

READ COMMITTED（提交读）

　　大多数数据库系统的默认隔离级别都是READ COMMTTED（但MySQL不是）。READ COMMITTED满足前面提到的隔离性的简单定义：一个事务开始时，只能"看见"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候叫做不可重复读（nonrepeatble read），因为两次执行同样的查询，可能会得到不一样的结果

REPEATABLE READ(可重复读)

　　REPEATABLE READ解决了脏读的问题。该隔离级别保证了在同一个事务中多次读取同样记录结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom Read）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）解决了幻读的问题。

SERIALIZABLE（可串行化）

　　SERIALIZABLE是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，SERIALIZABLE会在读取每一行数据都加锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。

打钩说明该隔离级别还存在这种情况，打X代表该隔离级别已经解决了这种情况：022015137336388.jpg

MyISAM引擎和InnoDB引擎的特点

leokim / / MYSQL, 技术, 数据库

随着MySQL的不断更新，由于各存储引擎功能特性差异较大，这篇文章主要是介绍如何来选择合适的存储引擎来应对不同的业务场景，朋友们可以根据业务需求，选择合适的存储引擎。^.^

MyISAM

特性

不支持事务：MyISAM存储引擎不支持事务，所以对事务有要求的业务场景不能使用

表级锁定：其锁定机制是表级索引，这虽然可以让锁定的实现成本很小但是也同时大大降低了其并发性能

读写互相阻塞：不仅会在写入的时候阻塞读取，MyISAM还会在读取的时候阻塞写入，但读本身并不会阻塞另外的读

只会缓存索引：MyISAM可以通过key_buffer缓存以大大提高访问性能减少磁盘IO，但是这个缓存区只会缓存索引，而不会缓存数据

适用场景

不需要事务支持（不支持）

并发相对较低（锁定机制问题）

数据修改相对较少（阻塞问题）

以读为主

数据一致性要求不是非常高

最佳实践

尽量索引（缓存机制）

调整读写优先级，根据实际需求确保重要操作更优先

启用延迟插入改善大批量写入性能

尽量顺序操作让insert数据都写入到尾部，减少阻塞

分解大的操作，降低单个操作的阻塞时间

降低并发数，某些高并发场景通过应用来进行排队机制

对于相对静态的数据，充分利用Query Cache可以极大的提高访问效率

MyISAM的Count只有在全表扫描的时候特别高效，带有其他条件的count都需要进行实际的数据访问

InnoDB

特性

具有较好的事务支持：支持4个事务隔离级别，支持多版本读

行级锁定：通过索引实现，全表扫描仍然会是表锁，注意间隙锁的影响

读写阻塞与事务隔离级别相关

具有非常高效的缓存特性：能缓存索引，也能缓存数据

整个表和主键以Cluster方式存储，组成一颗平衡树

所有Secondary Index都会保存主键信息

适用场景

需要事务支持（具有较好的事务特性）

行级锁定对高并发有很好的适应能力，但需要确保查询是通过索引完成

数据更新较为频繁的场景

数据一致性要求较高

硬件设备内存较大，可以利用InnoDB较好的缓存能力来提高内存利用率，尽可能减少磁盘 IO

最佳实践

主键尽可能小，避免给Secondary index带来过大的空间负担

避免全表扫描，因为会使用表锁

尽可能缓存所有的索引和数据，提高响应速度

在大批量小插入的时候，尽量自己控制事务而不要使用autocommit自动提交

合理设置innodb_flush_log_at_trx_commit参数值，不要过度追求安全性

避免主键更新，因为这会带来大量的数据移动

NDBCluster

特性

分布式：分布式存储引擎，可以由多个NDBCluster存储引擎组成集群分别存放整体数据的一部分

支持事务：和Innodb一样，支持事务

可与mysqld不在一台主机：可以和mysqld分开存在于独立的主机上，然后通过网络和mysqld通信交互

内存需求量巨大：新版本索引以及被索引的数据必须存放在内存中，老版本所有数据和索引必须存在与内存中

适用场景

具有非常高的并发需求

对单个请求的响应并不是非常的critical

查询简单，过滤条件较为固定，每次请求数据量较少，又不希望自己进行水平Sharding

最佳实践

尽可能让查询简单，避免数据的跨节点传输

尽可能满足SQL节点的计算性能，大一点的集群SQL节点会明显多余Data节点

在各节点之间尽可能使用万兆网络环境互联，以减少数据在网络层传输过程中的延时

想了解更多关于MyISAM和InnoDB的性能对比，可以参考Percona的这个

http://www.percona.com/blog/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/

http://imysql.com/2014/11/01/mysql-faq-convert-myisam-to-innodb-tips.shtml

JP retuen 涉及到的表

leokim / / MYSQL

JP retuen 设计到的表

select group_concat(voucher_id) from voucher where prefix='JP50' AND voucher_no BETWEEN 2146 AND 2160
select * from voucher_action where voucher_id in(16146,16147,16148,16149,16150,16151,16152,16153,16154,16155,16156,16157,16158,16159,16160) and action_id = 30
select * from voucher_campaign_issue where voucher_id in(16146,16147,16148,16149,16150,16151,16152,16153,16154,16155,16156,16157,16158,16159,16160)
select * from voucher_request_approve where request_id = 23070
select * from voucher_batch where batch_id = 46366
select * from voucher_batch_no_section where batch_id = 46366

UPDATE voucher SET type_id =0,status_id=1,stock_id=35, expiry_date=NULL
WHERE voucher_id IN (16146,16147,16148,16149,16150,16151,16152,16153,16154,16155,16156,16157,16158,16159,16160,16161,16162,16163,16164,16165,16166,16167)DELETE FROM voucher_actionWHERE voucher_id IN (16146,16147,16148,16149,16150,16151,16152,16153,16154,16155,16156,16157,16158,16159,16160,16161,16162,16163,16164,16165,16166,16167) AND action_id = 30DELETE FROM voucher_campaign_issueWHERE voucher_id IN (16146,16147,16148,16149,16150,16151,16152,16153,16154,16155,16156,16157,16158,16159,16160,16161,16162,16163,16164,16165,16166,16167)DELETE FROM voucher_request_approveWHERE request_id = 23070