时间戳

首页 > 解决方案 > Mysql 在查询 tinyint 布尔字段时的奇怪行为

mysql - Mysql 在查询 tinyint 布尔字段时的奇怪行为

问题描述

我有一个voucher_codes具有以下架构的 mysql 表：

mysql> describe  voucher_codes;
+--------------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra          |
+--------------------+------------------+------+-----+---------+----------------+
| id                 | int(11)          | NO   | PRI | NULL    | auto_increment |
| user_id            | int(10) unsigned | YES  | MUL | NULL    |                |
| promo_code         | varchar(30)      | YES  | MUL | NULL    |                |
| voucher_value      | double           | NO   |     | NULL    |                |
| date_created       | datetime(6)      | NO   |     | NULL    |                |
| date_modified      | datetime(6)      | NO   |     | NULL    |                |
| redeem_flag        | tinyint(1)       | NO   |     | NULL    |                |
| fk_voucher_rule_id | int(11)          | NO   | MUL | NULL    |                |
+--------------------+------------------+------+-----+---------+----------------+

表上定义的索引如下：

mysql> show index  from voucher_codes;
+---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table         | Non_unique | Key_name                 | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| voucher_codes |          0 | PRIMARY                  |            1 | id                 | A         |    15280692 |     NULL | NULL   |      | BTREE      |         |               |
| voucher_codes |          1 | voucher_codes_user_id    |            1 | user_id            | A         |     2805369 |     NULL | NULL   | YES  | BTREE      |         |               |
| voucher_codes |          1 | voucher_codes_promo_code |            1 | promo_code         | A         |     7389780 |     NULL | NULL   | YES  | BTREE      |         |               |
| voucher_codes |          1 | fk_voucher_rule_id       |            1 | fk_voucher_rule_id | A         |          60 |     NULL | NULL   |      | BTREE      |         |               |
+---------------+------------+--------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

其中，voucher_code 为 1，redeem_flag 为 0 和 1 的记录数如下：

mysql> select count(*) from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1;
+----------+
| count(*) |
+----------+
|   135114 |
+----------+
1 row in set (3.17 sec)

mysql> select count(*) from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1;
+----------+
| count(*) |
+----------+
|  1575024 |
+----------+
1 row in set (2.56 sec)

有 1.5+ 百万条记录redeem_flagas1和 100k+ are 0。

但是与使用 0 的查询相比，使用redeem_flagas的查询花费的时间更少。结果如下：1redeem_flag

mysql> select promo_code from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1  limit 1 ;
+--------------+
| promo_code   |
+--------------+
| XXXXXXXXXX   |
+--------------+
1 row in set (3.67 sec)

mysql> explain select promo_code from voucher_codes  where redeem_flag =0 and fk_voucher_rule_id=1  limit 1 ;
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key                | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | voucher_codes | NULL       | ref  | fk_voucher_rule_id | fk_voucher_rule_id | 4       | const | 3258352 |    10.00 | Using where |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

用redeem_flagas 1 查询：

mysql> select promo_code from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1  limit 1 ;
+------------+
| promo_code |
+------------+
| XXXXXXXXX  |
+------------+
1 row in set (0.00 sec)

mysql> explain select promo_code from voucher_codes  where redeem_flag =1 and fk_voucher_rule_id=1  limit 1 ;
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys      | key                | key_len | ref   | rows    | filtered | Extra       |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
|  1 | SIMPLE      | voucher_codes | NULL       | ref  | fk_voucher_rule_id | fk_voucher_rule_id | 4       | const | 3258352 |    10.00 | Using where |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

无法理解这种行为。即使索引fk_voucher_rule_id正在被使用，但与更少的记录相比，更多的记录需要更多的时间。

更新：添加显示创建表结果：

mysql> show create table voucher_codes;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| voucher_codes | CREATE TABLE `voucher_codes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned DEFAULT NULL,
  `promo_code` varchar(30) DEFAULT NULL,
  `voucher_value` double NOT NULL,
  `date_created` datetime(6) NOT NULL,
  `date_modified` datetime(6) NOT NULL,
  `redeem_flag` tinyint(1) NOT NULL,
  `fk_voucher_rule_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `voucher_codes_user_id` (`user_id`),
  KEY `voucher_codes_promo_code` (`promo_code`),
  KEY `fk_voucher_rule_id` (`fk_voucher_rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16305657 DEFAULT CHARSET=latin1 |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

标签： mysql

解决方案

首先，使用SELECT SQL_NO_CACHE ...而不是仅仅测试查询的速度SELECT ...。查询在 0.00 时间内运行是很奇怪的。

此外，创建一个复合索引redeem_flag, fk_voucher_rule，然后重新测试您的查询以进行比较。

注意：您应该在查询中使用与索引中相同的字段顺序。此外，正如@Raymod Nijland 所说，删除冗余索引。例如，如果您有索引fk_voucher_rule, redeem_flag，它将同时用于仅使用的查询fk_voucher_rule和fk_voucher_rule, redeem_flag查询（其他方式不适用，您不能仅将其用于查询redeem_flag）。

推荐阅读