memory存储引擎的表可以选择使用BTREE索引或者HASH索引,分别有自己的适用范围。
如果童鞋没有文中提到的表,想跟着测试请看最下面的附注。
- hash索引的适用范围及缺陷
1、只用于 = 或<=>操作符的等式比较。
2、优化器不能使用hash索引加速order by操作;
3、当~ mysql不能确定在两个值之间大约多少行。若将一个myisam表改为hash索引的memory表时,会影响一些查询的执行效率。
4、只能使用整个关键字搜索一行。 - BTREE索引
适用于 >、<、>=、<=、between、!=或者<> ,或者 like ‘parent’(parent不以通配符开始)操作符。 - 执行sql简单了解下两种索引。
1、下面范围查找适合btree和hash索引:select * from customers where age=1 or age in(26,2,3);
;
2、下面范围查询只适用于BTREE索引:
select * from customers where age >20 and age<30;
select * from customers where name like 't%' or name between 'tom' and 'jerry';
eg:创建一个和city表完全相同的memory 存储引擎的表city_memory:
CREATE TABLE `city_memory` (
`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(50) NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON
UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`),
KEY `idx_fk_country_id` (`country_id`),
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES
`country` (`country_id`) ON UPDATE CASCADE
) ENGINE=Memory DEFAULT CHARSET=utf8;
insert into city_memory select * from city;
当对索引字段进行范围查询的时候,只有BTREE 索引可以通过索引访问:
explain select * from city where country_id>1 and country_id<101 \G ;
而hash索引实际上是全表扫描的:explain select * from city_memory where country_id>1 and country_id<101 \G ;
当使用memory表时,如果默认创建hash索引,则要注意sql语句的编写,确保可以使用上索引,如果一定要使用范围查询,那么在创建索引时,则应该选择创建成BTREE索引。
附注:测试数据准备:
CREATE TABLE `customers` (
`id` int(10) NOT NULL,
`name` varchar(20) DEFAULT NULL,
`age` int(3) DEFAULT NULL,
`birthday` datetime DEFAULT NULL,
`married` smallint(1) DEFAULT NULL COMMENT '1 :0δ',
`photo` blob COMMENT 'Ƭʹƴ',
`remak` varchar(100) DEFAULT NULL COMMENT 'ע',
`ctime` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
`sex` char(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (1, 'xianzi', 26, NULL, 0, NULL, NULL, '2018-10-5 20:36:07', NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (2, 'yanhua', 2, NULL, 0, NULL, NULL, '2018-10-5 20:36:10', NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (3, 'haidao', 3, NULL, 0, NULL, NULL, '2018-10-5 20:36:13', NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (4, 'xiao_lang', 14, NULL, 0, NULL, NULL, NULL, NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (5, 'yaxian_baby', 2, NULL, 0, NULL, NULL, NULL, NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (6, 'tom', 1, NULL, 0, NULL, NULL, NULL, NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (7, 'jerry', 1, NULL, 0, NULL, NULL, NULL, NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (8, '1', 11, NULL, 0, NULL, NULL, NULL, NULL);
INSERT INTO `customers` (`id`, `name`, `age`, `birthday`, `married`, `photo`, `remak`, `ctime`, `sex`) VALUES (9, '2', 21, NULL, 0, NULL, NULL, NULL, NULL);
CREATE TABLE `city` (
`city_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`city` varchar(50) NOT NULL,
`country_id` smallint(5) unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`city_id`),
KEY `idx_fk_country_id` (`country_id`),
CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=223 DEFAULT CHARSET=utf8;