警惕 InnoDB 和 MyISAM 创建 Hash 索引陷阱

标签: MySQLInnoDBMyISAMHash索引索引优化
8199人阅读 评论(2) 收藏 举报
分类:
MySql 最常用存储引擎 InnoDB 和 MyISAM 都不支持 Hash 索引,它们默认的索引都是 B-Tree。但是如果你在创建索引的时候定义其类型为 Hash,MySql 并不会报错,而且你通过 SHOW CREATE TABLE 查看该索引也是 Hash,只不过该索引实际上还是 B-Tree。
比如表 data_dict 的 DDL:
CREATE TABLE `data_dict` (
  `data_type` varchar(32) NOT NULL COMMENT '数据字典类型',
  `data_code` tinyint(4) NOT NULL COMMENT '数据字典代码',
  `data_name` varchar(64) NOT NULL COMMENT '数据字典值',
  PRIMARY KEY (`data_type`,`data_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据字典表';

我们为 data_name 字段建立 Hash 索引:
ALTER TABLE data_dict ADD INDEX data_dict_dn USING HASH (data_name);

打印结果:
受影响的行: 0
时间: 0.345s

然后查看建表 DDL:
SHOW CREATE TABLE data_dict;

打印结果:
CREATE TABLE `data_dict` (
  `data_type` varchar(32) NOT NULL COMMENT '数据字典类型',
  `data_code` tinyint(4) NOT NULL COMMENT '数据字典代码',
  `data_name` varchar(64) NOT NULL COMMENT '数据字典值',
  PRIMARY KEY (`data_type`,`data_code`),
  KEY `data_dict_dn` (`data_name`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据字典表'

是 Hash,所以我们以为创建 Hash 索引成功。
事实上并非如此,我们都被 MySql 给骗了,我们使用 SHOW INDEXES FROM 语句对该表索引进行检索:
SHOW INDEXES FROM data_dict;

打印结果:
我们使用 SHOW INDEXES FROM 语句对该表索引进行检索
打回原形了。不过也不要失望,虽然常见存储引擎并不支持 Hash 索引,但 InnoDB 有另一种实现方法:自适应哈希索引。InnoDB 存储引擎会监控对表上索引的查找,如果观察到建立哈希索引可以带来速度的提升,则建立哈希索引。
我们可以通过 SHOW ENGINE INNODB STATUS 来查看当前自适应哈希索引的使用状况:
=====================================
2015-07-07 10:51:19 1d68 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 36 seconds
......
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 2633, seg size 2635, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 348731, node heap has 2 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
......

从中我们可以看到自适应哈希索引的相关信息:有使用大小、使用情况、每秒使用自适应哈希索引搜索的情况等。
MySql 各种存储引擎的特性对比详单:
MySql 各种存储引擎的特性对比详单
从中我们可以看出,
  • InnoDB 支持事务,支持行级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  • MyISAM 不支持事务,支持表级别锁定,支持 B-tree、Full-text 等索引,不支持 Hash 索引;
  • Memory 不支持事务,支持表级别锁定,支持 B-tree、Hash 等索引,不支持 Full-text 索引;
  • NDB 支持事务,支持行级别锁定,支持 Hash 索引,不支持 B-tree、Full-text 等索引;
  • Archive 不支持事务,支持表级别锁定,不支持 B-tree、Hash、Full-text 等索引;
可以使用 SHOW ENGINES 语句查看你的 MySql Server 所支持的存储引擎,比如笔者用于本机测试的 5.6.25-log Win 版的查看结果如下:
支持的存储引擎

从中可以看出,InnoDB 是该版本 MySql 的默认存储引擎,也只有 InnoDB 能够支持事务、行级别锁定、外键;支持的 MEMORY 是基于哈希的,数据都存放于内存,适用于临时表。没有看到既支持事务又支持哈希索引的 NDB 的身影。

为印证默认的存储引擎,我们创建一个测试表 data_dict_test,注意建表语句里没有定义存储引擎:

CREATE TABLE `data_dict_test` (
  `data_type` varchar(32) NOT NULL COMMENT '数据字典类型',
  `data_code` tinyint(4) NOT NULL COMMENT '数据字典代码',
  `data_name` varchar(64) NOT NULL COMMENT '数据字典值',
  PRIMARY KEY (`data_type`,`data_code`)
) DEFAULT CHARSET=utf8 COMMENT='数据字典表';
打印结果:

[SQL] CREATE TABLE `data_dict_test` (
  `data_type` varchar(32) NOT NULL COMMENT '数据字典类型',
  `data_code` tinyint(4) NOT NULL COMMENT '数据字典代码',
  `data_name` varchar(64) NOT NULL COMMENT '数据字典值',
  PRIMARY KEY (`data_type`,`data_code`)
) DEFAULT CHARSET=utf8 COMMENT='数据字典表';
受影响的行: 0
时间: 0.262s

然后查看建表 DDL:

SHOW CREATE TABLE data_dict_test;
打印结果:

CREATE TABLE `data_dict_test` (
  `data_type` varchar(32) NOT NULL COMMENT '数据字典类型',
  `data_code` tinyint(4) NOT NULL COMMENT '数据字典代码',
  `data_name` varchar(64) NOT NULL COMMENT '数据字典值',
  PRIMARY KEY (`data_type`,`data_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='数据字典表'

这次 SHOW CREATE TABLE 没有欺骗我们,明确给出的就是 InnoDB,虽然我们建表时没有指定。

笔者建议使用 SHOW TABLE STATUS 语句来查看特定表的存储引擎:

SHOW TABLE STATUS WHERE NAME = 'data_dict_test';
打印结果:

SHOW TABLE STATUS WHERE NAME = 'data_dict_test'


参考资料

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:4618870次
    • 积分:36320
    • 等级:
    • 排名:第125名
    • 原创:240篇
    • 转载:35篇
    • 译文:160篇
    • 评论:1502条
    个人微博
    最新评论
    文章存档