特征 | MyISAM | InnoDB |
---|---|---|
聚集索引 | 否 | 是 |
压缩数据 | 是(仅当使用压缩行格式时才支持压缩MyISAM表。使用压缩行格式和MyISAM的表是只读的。) | 是 |
数据缓存 | 否 | 是 |
加密数据 | 是(通过加密功能在服务器中实现) | 是(通过加密功能在服务器中实现;在MySQL 5.7及更高版本中,支持数据静态表空间加密) |
全文搜索索引 | 是 | 是(在MySQL 5.6及更高版本中可以使用InnoDB对FULLTEXT索引的支持。) |
外键支持 | 否 | 是 |
地理空间索引支持 | 是 | 是(在MySQL 5.7及更高版本中可以使用InnoDB对地理空间索引的支持) |
锁定粒度 | 表 | 行 |
MVCC | 否 | 是 |
存储限制 | 256TB | 64TB |
事务 | 否 | 是 |
聚集索引
MyISAM中没有聚集索引,这是InnoDB特有的。官方文档是这么说的:
When you define a
PRIMARY KEY
on your table,InnoDB
uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.If you do not define a
PRIMARY KEY
for your table, MySQL locates the firstUNIQUE
index where all the key columns areNOT NULL
andInnoDB
uses it as the clustered index.If the table has no
PRIMARY KEY
or suitableUNIQUE
index,InnoDB
internally generates a hidden clustered index namedGEN_CLUST_INDEX
on a synthetic column containing row ID values. The rows are ordered by the ID thatInnoDB
assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.
简单翻译一下:
在InnoDB中,每一张表都有一个特殊的索引:聚集索引(clustered index),表中的数据都会存储在这个索引中。一般来说,聚集索引就是主键。为了实现查询、插入等数据库操作的最佳性能,你必须理解InnoDB中聚集索引是如何对每张表的最常见的查找和数据操作进行优化的。
- 如果一张表拥有主键,那么InnoDB就会用主键来做聚集索引。所以最好给每张表定义一个主键,即使这张表不具有逻辑上的主键甚至没有一个非空且唯一的字段,你也要添加一个自增的列。
- 如果一张表没有定义主键,那么MySQL就会尝试寻找一个非空(NOT NULL)且唯一(UNIQUE)的字段作为聚集索引。
- 如果一张表既没有主键,也没有非空且唯一的字段,InnoDB就会隐式地创建一个合成的列(synthetic column)来存储行ID,并且用该列创建名为GEN_CLUST_INDEX的聚集索引,表中的行会按照InnoDB分配的行ID的顺序进行存储。每当插入一个新行时就会一个6字节长的行ID,因此,所有的行都会按照出入顺序存储到硬盘上。
聚集索引如何提高查询速度?
MySQL中使用B+树做索引,然后在此基础上区分出聚集索引和非聚集索引。
聚集索引:主索引文件和数据文件为同一份文件,上图中的叶子节点上的data就是数据本身。
非聚集索引:主索引文件和数据文件是分离的,叶子节点存储的是数据存放的地址,所以非聚集索引比聚集索引多了一次读取数据的I/O操作,所以查找性能会相对差一些。
参考文献: