Mysql: поиск по полю с выставленным битом (по битовой маске)

Допустим есть таблица, где есть много записей. И нужно поработать с фильтрами по битовому полю.

Вот таблица:

CREATE TABLE IF NOT EXISTS `tt` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` bigint(20) unsigned NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `tt` (

`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

`type` bigint(20) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

Выставляется бит так:

UPDATE tt SET type = type & 60 WHERE id = 1

1	UPDATE tt SET type = type & 60 WHERE id = 1

Убирается так:

UPDATE tt SET type = type & ~60 WHERE id = 1

1	UPDATE tt SET type = type & ~60 WHERE id = 1

Так вот.
Нужно сделать выборку только тех записей, у кого выставлен бит.

В лоб. Не используется индекс по type.

EXPLAIN EXTENDED 
SELECT 
	* 
FROM `Monamour2`.`Anketa` AS tt
WHERE tt.type & 1 << 60
LIMIT 15;
--     id  select_type  table   type    possible_keys  key     key_len  ref        rows  filtered  Extra      
-- ------  -----------  ------  ------  -------------  ------  -------  ------  -------  --------  -----------
--      1  SIMPLE       tt      ALL     (NULL)         (NULL)  (NULL)   (NULL)  4926268    100.00  Using where

EXPLAIN EXTENDED

SELECT

FROM `Monamour2`.`Anketa` AS tt

WHERE tt.type & 1 << 60

LIMIT 15;

-- id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------ ----------- ------ ------ ------------- ------ ------- ------ ------- -------- -----------

-- 1 SIMPLE tt ALL (NULL) (NULL) (NULL) (NULL) 4926268 100.00 Using where

Магия. Используется индекс по type

EXPLAIN EXTENDED 
SELECT 
	* 
FROM `Monamour2`.`Anketa` AS tt
WHERE tt.type & 1 << 60
    AND tt.type >= 0xFFFFFFFF & ~((2 << FLOOR(LOG(2, 0xFFFFFFFF & ~(1 << 60)))) - 1)
LIMIT 15;
--     id  select_type  table   type    possible_keys  key     key_len  ref        rows  filtered  Extra      
-- ------  -----------  ------  ------  -------------  ------  -------  ------  -------  --------  -----------
--      1  SIMPLE       tt      range   type           type    8        (NULL)  2463134    100.00  Using where

EXPLAIN EXTENDED

SELECT

FROM `Monamour2`.`Anketa` AS tt

WHERE tt.type & 1 << 60

AND tt.type >= 0xFFFFFFFF & ~((2 << FLOOR(LOG(2, 0xFFFFFFFF & ~(1 << 60)))) - 1)

LIMIT 15;

-- id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------ ----------- ------ ------ ------------- ------ ------- ------ ------- -------- -----------

-- 1 SIMPLE tt range type type 8 (NULL) 2463134 100.00 Using where

Но всё равно просмотрено много строк из таблицы. Значительно лучше так:

Используется ещё и covering index

EXPLAIN EXTENDED 
SELECT 
	tt.* 
FROM tt 
INNER JOIN 
(
	SELECT 
		tt.oid 
	FROM tt  
	WHERE tt.type & 1 << 62 
		AND tt.type >= 0xFFFFFFFF & ~((2 << FLOOR(LOG(2, 0xFFFFFFFF & ~(1 << 60)))) - 1) LIMIT 15
) AS tt2 
USING(oid);

--     id  select_type  table       type    possible_keys  key      key_len  ref        rows  filtered  Extra                   
-- ------  -----------  ----------  ------  -------------  -------  -------  ------  -------  --------  ------------------------
--      1  PRIMARY      <derived2>  ALL     (NULL)         (NULL)   (NULL)   (NULL)        6    100.00                          
--      1  PRIMARY      aa          eq_ref  PRIMARY        PRIMARY  4        a.oid         1    100.00                          
--      2  DERIVED      a           range   type           type     8        (NULL)  2463134    100.00  Using where; Using index

EXPLAIN EXTENDED

SELECT

tt.*

FROM tt

INNER JOIN

(

SELECT

tt.oid

FROM tt

WHERE tt.type & 1 << 62

AND tt.type >= 0xFFFFFFFF & ~((2 << FLOOR(LOG(2, 0xFFFFFFFF & ~(1 << 60)))) - 1) LIMIT 15

) AS tt2

USING(oid);

-- id select_type table type possible_keys key key_len ref rows filtered Extra

-- ------ ----------- ---------- ------ ------------- ------- ------- ------ ------- -------- ------------------------

-- 1 PRIMARY <derived2> ALL (NULL) (NULL) (NULL) (NULL) 6 100.00

-- 1 PRIMARY aa eq_ref PRIMARY PRIMARY 4 a.oid 1 100.00

-- 2 DERIVED a range type type 8 (NULL) 2463134 100.00 Using where; Using index

Здесь самый «тяжёлый» запрос использует covering index.

Что можно почитать по теме:
http://stackoverflow.com/questions/1457218/bitwise-supersets-and-subsets-in-mysql
http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html

Mysql: поиск по полю с выставленным битом (по битовой маске)

Similar Posts

LEAVE A COMMENT Отменить ответ