为什么我的 mysql 模糊查询没有使用索引

1.案例

1.1 初始化测试数据

测试数据有两张简化的表，一个是商品类目表 goods_category，一个是商品表。两张表关系是类目与商品是一对多的关系，即一个类目可以对应多个商品。初始化脚本如下（testdb.sql)

#创建商品表
drop table goods;
CREATE TABLE `goods` (
  `goods_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `goods_name` varchar(128) NOT NULL DEFAULT '' COMMENT '商品名称',
  `category_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '类目id',
  PRIMARY KEY (`goods_id`),
  KEY idx_category_id (`category_id`),
  KEY idx_goods_name (`goods_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

#创建类目表
drop table goods_category;
CREATE TABLE `goods_category` (
    `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品类目id',
    `category_name` varchar(128) NOT NULL DEFAULT '' COMMENT '商品类目名',
    PRIMARY KEY (`category_id`),
    KEY idx_category_name (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品类目表';

#插入测试数据
INSERT INTO `goods` (`goods_id`, `goods_name`, `category_id`) VALUES
(1, '女鞋1', 1),
(2, '女鞋2', 1),

(2, '女鞋2', 1),
(3, '女鞋3', 1),
(4, '男T恤1', 2),
(5, '男T恤2', 2),
(6, '女_包1', 3),
(7, '女_包2', 3),
(8, '女_包3', 3),
(9, '女_包4', 3),
(10, '女_包5', 3),
(11, '女_包6', 3);

INSERT INTO `goods_category` (`category_id`, `category_name`) VALUES
(2, 'T恤'),
(3, '箱包'),
(1, '鞋');
DELIMITER $$

DROP PROCEDURE IF EXISTS proc_initData$$

CREATE PROCEDURE proc_initData()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i<=10000 DO
        INSERT INTO test.goods (`goods_id`, `goods_name`, `category_id`) VALUES(i+100, CONCAT('女_包', i+100), 3);
        SET i = i+1;
    END WHILE;
END$$

DELIMITER ;
CALL proc_initData();

1.2 模糊查询条件左侧以通配符开头不能应用索引

我们知道对于模糊查询，通配符放在左侧是用不上索引的，这个很好理解，因为每条记录都是有可能匹配的，只有检查每条记录才能确定是不是满足模糊匹配条件，例如：

mysql> explain select * from goods where goods_name like '%T恤1';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | goods | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10122 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

1.3 模糊查询条件以通配符结尾可以应用索引

MySQL 文档指出以通配符结尾的话，是可以应用上索引的，例如：

mysql> explain select * from goods where goods_name like '男T恤%';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra
        |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | goods | NULL       | range | idx_goods_name | idx_goods_name | 514     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+--------------