mysql bool字段_MySQL里bool类型字段是否需要添加索引测试

#测试环境

AMD A8-7650K + 8G + 1TB + Win7 x64

MySQL Community Server v5.5.47

#随机生成3276800条记录

insert into `test_index` values(null,round(rand()),round(rand())); -- 重复N次

insert into `test_index` select null,is_haveindex,is_noindex from `test_index`; -- 重复N次

#各种字段记录数

+---------------------------------------+

| is_haveindex | is_noindex |

|---------|---------|---------|---------|

| 1 | 0 | 1 | 0 |

|---------|---------|---------|---------|

| 1458176 | 1818624 | 1671168 | 1605632 |

+---------------------------------------+

#MyISAM引擎

CREATE TABLE `test_index` (

`id` int(11) NOT NULL AUTO_INCREMENT, `is_haveindex` tinyint(4) DEFAULT NULL, `is_noindex` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`), KEY `is_haveindex` (`is_haveindex`)

) ENGINE=MyISAM AUTO_INCREMENT=3276801 DEFAULT CHARSET=utf8mb4;

select count(*) from test_index where is_haveindex=1

时间: 0.469s

select count(*) from test_index where is_haveindex=0

时间: 0.733s

select count(*) from test_index where is_noindex=1

时间: 0.224s

select count(*) from test_index where is_noindex=0

时间: 0.225s

#InnoDB引擎

CREATE TABLE `test_index` (

`id` int(11) NOT NULL AUTO_INCREMENT, `is_haveindex` tinyint(4) DEFAULT NULL, `is_noindex` tinyint(4) DEFAULT NULL,

PRIMARY KEY (`id`), KEY `is_haveindex` (`is_haveindex`)

) ENGINE=InnoDB AUTO_INCREMENT=3276801 DEFAULT CHARSET=utf8mb4;

select count(*) from test_index where is_haveindex=1

时间: 0.501s

select count(*) from test_index where is_haveindex=0

时间: 0.631s

select count(*) from test_index where is_noindex=1

时间: 1.451s

select count(*) from test_index where is_noindex=0

时间: 1.511s

#结论

自己看吧……