一个有意思的MySQL索引案例

AsiaYe

发布于 2020-05-27 22:38:05

6030

文章被收录于专栏：DBA随笔DBA随笔

01 场景分析

创建一个表：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `b` (`b`)
) ENGINE=InnoDB;

假设这个表有100w数据，其中有10w行b的值都是'1234567890'这个字符串，此时我们执行：

select * from t where b='1234567890abcd';

那么MySQL将会发生什么现象？？？

从理论上分析，因为b的字段是varchar(10)，所以匹配b='1234567890abcd'的结果一定是空，因为它的长度大于10了，应该直接返回10才对。

实际上不是这样的。

真实的执行情况如下：

1、MySQL先截取SQL语句中b的前10个字节'1234567890',查询到符合条件的索引b上的记录有10w跳，

2、然后将这10w跳记录逐一"回表"去聚集索引上进行查询。

3、发现没有记录的b值是'1234567890abcd'

4、返回结果为空。

下面的测试过程能够比较直接的反映这个问题：

mysql> show create table test1\G
*************************** 1. row ***************************
       Table: test1
Create Table: CREATE TABLE `test1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `index_b` (`b`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(*) from test1 where b='1234567890';
+----------+
| count(*) |
+----------+
|       24 |
+----------+
1 row in set (0.00 sec)

mysql> explain select *from test1 where b='1234567890abcd';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | test1 | NULL       | ref  | index_b       | index_b | 33      | const |   24 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select *from test1 where b='1234567890abcd';
Empty set (0.00 sec)

可以看到explain的结果中的扫描行数和实际上b的值为'1234567890'的行数一致，说明MySQL执行的过程中，确实找到了24行匹配的记录进行回表的。

虽然返回的结果是0条记录，如果这个表数据量非常大，此时这个返回的过程也是十分慢的，因为要做大量的回表操作。这也是一个经典查询慢的案例，是我们今天的正文的一个场景补充。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-05-18，如有侵权请联系 cloudcommunity@tencent.com 删除

sql