警惕 InnoDB 和 MyISAM 创建 Hash 索引陷阱
比方表 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;
打印结果:
打回原形了。只是也不要失望,尽管常见存储引擎并不支持 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 各种存储引擎的特性对照详单:
从中我们能够看出,
- 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';
打印结果:
watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" alt="SHOW TABLE STATUS WHERE NAME = 'data_dict_test'" />
參考资料
最新文章
- 使用insertBefore实现insertAdjacentHTML()
- 基于Dubbo框架构建分布式服务(三)
- 理解Certificate、App Id、Identifiers 和 Provisioning Profile
- php -- 获取当月天数及当月第一天及最后一天、上月第一天及最后一天(备忘)
- Swift学习笔记十一
- Chapter 7 代理模式
- POJ 3986 Math teacher's homework
- Angular vs React 最全面深入对比
- 关于el-dialog,我更推荐的用法
- Java数据持久层框架 MyBatis之API学习六(Mapper XML 文件详解)
- Magento2.X 后端开发简要1
- Python之进程 3 - 进程池和multiprocess.Poll
- swiper轮播图(逆向自动切换类似于无限循环)
- ESP8266 NOOS SDK libat.a Functions
- js 数组、对象转json 以及json转 数组、对象
- BZOJ 4405 [wc2016]挑战NPC 带花树 一般图最大匹配
- 微软自然语言理解平台LUIS:从零开始,帮你开发智能音箱
- [转载]Windows 8 VHD 概述与使用
- Ubuntu16.04中MySQL之中文不能插入问题
- openssl的证书格式转换
热门文章
- javascipt入门
- 常见Json字符串反序列化处理方式总结
- js对象追加到数组里
- thinkphp 5.0整合phpsocketio完整攻略,绕坑
- [luogu] P2787 语文1(chin1)- 理理思维(分块)
- shell的通俗理解
- python __future__ 的几种特性
- moble 设备多指手势识别 (tap , double_tap , pinch)
- zzulioj--1832--贪吃的松鼠(位运算好题)
- Apache Bench测试