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

• 09, 18, 2012
•  bullgare
•  Databases
• No Comments.

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

Вот таблица:

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

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

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

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

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
   

2. Магия. Используется индекс по 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
   

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

3. Используется ещё и 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        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