索引组织表(IOT表):为什么引入索引组织表,好处在那里,组织结构特点是什么,如何创建,创建IOT的限制LIMIT。
IOT是以索引的方式存储的表,表的记录存储在索引中,索引即是数据,索引的KEY为PRIMARY KEY。数据的查询可以通过查询索引的同时查询到数据,因为索引和数据存储在一个数据块中,减少了一次磁盘I/O。数据是按照主键顺序创建的索引,索引中有对应的数据,这样依据主键做范围扫描时,减少了读取的数据块数量,减少了磁盘I/O。也减少了索引的存储空间,因为索引和数据存在一起。如果是B树索引就需要创建对索引的存储空间。
两个好处:
一个是减少了范围扫描的磁盘I/O数据块数(页块中有数据,有索引)
一个是避免了索引自身的空间开销,因为索引和数据在一起,不需要额外的空间。这些优点都是索引组织表的特点决定。
何时使用IOT:
(1)数据的相关数据片需要存储在一起。
(2)数据必须按照指定的顺序物理存储。IOT表多用于信息获取、空间应用和OLAP应用(OLAP:Online-Analysis Process)(联机分析处理)。
HOT与IOT
myisam使用的堆组织表(Heap Organize Table, HOT)使用B-tree索引的存储格式,显示都是随机顺序。
innodb表是索引组织表(Index Organized Table, IOT),它的索引则是采用 clustered index 方式,因此主键会按照顺序存储,每次有记录有更新时,会重新整理更新其主键。因此无论是直接从 myisam 表转换过来的,还是后来插入的记录,显示时都会按照主键的顺序。
mysql> select * from duplicate_key;
+----+------+
| id | p_id |
+----+------+
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 4 | 4 |
| 6 | 6 |
| 7 | 7 |
+----+------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
此时的duplicate_key表是myisam引擎的,
update duplicate_key set id=id-1的时候会提示
Duplicate entry ‘4’ for key ‘PRIMARY’错误。
update duplicate_key set id=id-1 order by id;
如果这样做,就不会出错,原理上面已做出了说明。
如果是innodb引擎就不会出现这样的情况,因为他的聚集索引存储方式会按顺序来显示。
在myisam引擎使用的时候如果你delete了其中的几条数据,这时的表就是一个hole表。
如果你不使用表维护命令进行维护,你新插入的数据就会放到你刚刚删除的那个位置。
lnnoDB存储引擎表类型
对比Oracle 支持的各种表类型 ,InnoDB 存储引擎表更像是 Orale 中的索引组织表 ( index organized table ) 。在InnoDB存储引擎表中 ,每张表都有个主键 ,如果在创建表时没有显式地定义主键 ( Primary Key ) , 则innoDB存储引擎会按如下方式选择或创建主键 。
①首先表中是否有非空的唯一索引 ( Unique NOT NULL ),如果有,则该列即为主键;
②不符合上述条件,InnoDB存储引擎自动创建一个 6个字节大小的指针。
lnnoDB逻辑存储结构
InnoDB存储引擎的逻辑存储结构和 Oracle大致相同 ,所有数据都被逻辑地存放在一个空间中 ,我们称之为表空间 ( tablespace ) 。表空间又由段 ( segment ) 、区 ( extent ) 、页 ( page ) 组成 。页在一些文档中有时也称为块(block) , InnoDB存储引擎的逻辑存储结构大致如图4-1所示。
段:也叫表;
区:物理上连续的几个页;
页:16K
表空间
表空间可以看做是InnoDB存储引擎逻辑结构的最高层 ,所有的数据都是存放在表空间中。已经介绍了默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1 ,即所有数据都放在这个表空间内 。如果我们启用了参数innodb_file_per_table ,则每张表内的数据可以单独放到一个表空间内 。
segment:表;
extent:物理上连续的几个页;
page(block):16K
(即:将共享表空间独立出去 innodb_file_per_table 参数)
对于启用了innodb_file_per_table的参数选项,需要注意的是 ,每张表的表空间内存放的只是数据、索引和插入缓冲 ,其他类的数据,如撤销( Undo) 信息、系统事务信息、