对一个表进行创建索引后,开发报告说之前可以查询出结果的查询在创建索引之后查询不到结果:
mysql> SELECT count(*) FROM `node` WHERE uid='1655928604919847' AND is_deleted='0';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set, 1 warning (0.00 sec)
而正确的结果是
mysql> SELECT count(*) FROM `test_node` WHERE uid='1655928604919847' AND is_deleted='0';
+----------+
| count(*) |
+----------+
| 107 |
+----------+
1 row in set (0.00 sec)
为什么加上索引之后就没有结果了呢？查看表结构如下:
mysql> show create table test_node \G
*************************** 1. row ***************************
Table: test_node
Create Table: CREATE TABLE `test_node` (
`node_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键anto_increment',
....
`is_deleted` bit(1) NOT NULL DEFAULT b'0', ---is_deleted 是bit 类型的！
`creator` int(11) NOT NULL,
`gmt_created` datetime NOT NULL,
...
PRIMARY KEY (`node_id`),
KEY `node_uid` (`uid`),
KEY `ind_n_aid_isd_state` (`uid`,`is_deleted`,`state`)
) ENGINE=InnoDB AUTO_INCREMENT=18016 DEFAULT CHARSET=utf8
问题就出现在bit 类型的字段上面。
为加索引之前
mysql> explain SELECT count(*) FROM `test_node` WHERE uid='1655928604919847' AND is_deleted='0' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: node
type: ref
possible_keys: node_uid
key: node_uid
key_len: 8
ref: const
rows: 197
Extra: Using where
1 row in set (0.00 sec)
对该表加上了索引之后,原来的sql 选择了索引
mysql> explain SELECT count(*) FROM `test_node` WHERE uid='1655928604919847' AND is_deleted='0' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test_node
type: ref
possible_keys: node_uid,ind_n_aid_isd_state
key: ind_n_aid_isd_state
key_len: 13
ref: const,const
rows: 107
Extra: Using where; Using index
1 row in set (0.00 sec
去掉使用ind_n_aid_isd_state索引,是有结果集的！
mysql>SELECT count(*) FROM `test_node` ignore index(ind_n_aid_isd_state) WHERE uid='1655928604919847' AND is_deleted='0';
+----------+
| count(*) |
+----------+
| 107 |
+----------+
1 row in set (0.00 sec)
分析至此,我们知道了问题出在索引上面。
KEY `ind_n_aid_isd_state` (`uid`,`is_deleted`,`state`)
sql 先从 test_node 表中选择中 uid='1655928604919847'的记录,然后从结果集中选择is_deleted='0'的行,但是对于bit类型的记录,在索引中存储的内容与'0'不等。所以选择不出is_deleted='0'的行,因此结果几为0.
接下来,我们对mysql的bit位做一个介绍。
MySQL5.0以前,BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本,BIT是一个完全不同的数据类型!
使用BIT数据类型保存位段值。BIT(M)类型允许存储M位值。M范围为1到64,BIT(1)定义一个了只包含单个比特位的字段, BIT(2)是存储2个比特位的字段,一直到64位。要指定位值,可以使用b'value'符。value是一个用0和1编写的二进制值。例如,b'111'和b'100000000'分别表示7和128。如果为BIT(M)列分配的值的长度小于M位,在值的左边用0填充。例如,为BIT(6)列分配一个值b'101',其效果与分配b'000101'相同。
MySQL把BIT当做字符串类型, 而不是数据类型。当检索BIT(1)列的值, 结果是一个字符串且内容是二进制位0或1, 而不是ASCII值”0″或”1″.然而,
如果在一个数值上下文检索的话, 结果是比特串转化而成的数字.当需要与另一个值进行比较时,如果存储值’00111010′(是58的二进制表示)到一个BIT(8)的字段中然后检索出来,得到的是字符串 ':'---ASCII编码为58,但是在数值环境中, 得到的是值58
解释到这里，刚开始的问题就迎刃而解了。
问题是存储的结果值容易混淆,存储00111001时,返回时的10进制数,还是ASCII码对应的字符？
来看看具体的值
root@rac1 : test 22:13:47> CREATE TABLE bittest(a bit(8));
Query OK, 0 rows affected (0.01 sec)
root@rac1 : test 22:21:25> INSERT INTO bittest VALUES(b'00111001');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 22:28:36> INSERT INTO bittest VALUES(b'00111101');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 22:28:54> INSERT INTO bittest VALUES(b'00000001');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:11:30> insert into bittest values(b'00111010');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:12:24> insert into bittest values(b'00000000');
Query OK, 1 row affected (0.00 sec)
root@rac1 : test 20:16:42> select a,a+0,bin(a) from bittest ;
+------+------+--------+
| a | a+0 | bin(a) |
+------+------+--------+
| | 0 | 0 |
| | 1 | 1 |
| 9 | 57 | 111001 |
| : | 58 | 111010 |
| = | 61 | 111101 |
+------+------+--------+
5 rows in set (0.00 sec)
从结果中可以看到 存储情况
root@rac1 : test 20:14:59> select a,a+0,bin(a),oct(a),hex(a) from bittest;
+------+------+--------+--------+--------+
| a | a+0 | bin(a) | oct(a) | hex(a) |
+------+------+--------+--------+--------+
| | 0 | 0 | 0 | 0 |
| | 1 | 1 | 1 | 1 |
| 9 | 57 | 111001 | 71 | 39 |
| : | 58 | 111010 | 72 | 3A |
| = | 61 | 111101 | 75 | 3D |
+------+------+--------+--------+--------+
5 rows in set (0.00 sec)
模拟线上环境对表bittest 加上索引：
root@rac1 : test 22:30:13> alter table bittest add key ind_a(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@rac1 : test 20:55:11> select * from bittest where a='0';
Empty set (0.00 sec) ---结果集为空。
查看执行计划，使用了索引。
root@rac1 : test 20:55:17> explain select * from bittest where a='0';
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | SIMPLE | bittest | ref | ind_a | ind_a | 2 | const | 1 | Using where; Using index |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
强制不走索引的话，结果集含有记录：
root@rac1 : test 20:55:25> select * from bittest ignore index (ind_a) where a='0';
+------+
| a |
+------+
| |
+------+
1 row in set (0.00 sec)
下面我们查看一下where 条件的 布尔值：
root@rac1 : test 21:00:11> select b'0'=0;
+--------+
| b'0'=0 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
root@rac1 : test 21:00:22> select b'0'='0';
+----------+
| b'0'='0' |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
bit 类型的 b'0'==0,b'0'!='0' ,哪个值 等于'0'? 答案是ascii 值为48的
root@rac1 : test 21:01:18> select b'110000'='0';
+---------------+
| b'110000'='0' |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)
root@rac1 : test 21:01:28> select b'110000'+0;
+-------------+
| b'110000'+0 |
+-------------+
| 48 |
+-------------+
1 row in set (0.00 sec)
如果使用 a=0 作为条件的话，依然有结果
root@rac1 : test 21:00:25> explain select * from bittest where a=0;
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | SIMPLE | bittest | ref | ind_a | ind_a | 2 | const | 1 | Using where; Using index |
+----+-------------+---------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
root@rac1 : test 21:00:35> select * from bittest where a=0;
+------+
| a |
+------+
| |
+------+
1 row in set (0.00 sec)
所以，可以做一个小结：
对于bit 类型的数值
不使用使用索引，mysql 检索bit的值是不管是数值还是字符，mysql会对where 条件进行类型转化,将字符转换为数值，并比较数值对应的ascii码，如果值为1，则返回结果，否则，结果为空。
root@rac1 : test 21:08:37> select * from bittest ignore index (ind_a) where a='48';
+------+
| a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
将字符串'48'转化为数值的48也即b'110000'，和字符'0'的ascii 码做比较
root@rac1 : test 21:08:48> select * from bittest ignore index (ind_a) where a=48;
+------+
| a |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
使用索引时:bit位在索引中存储的格式是bin类型，即'0'/'1'bit位，且不会对字符串进行数值转换。
root@rac1 : test 21:08:58> select * from bittest where a=57;
+------+
| a |
+------+
| 9 |
+------+
1 row in set (0.00 sec)
字符'9'对应的ASCII码代码为57 而不是字符串'57'
root@rac1 : test 21:09:10> select * from bittest where a='57';
Empty set (0.01 sec)
