mysql强制使用指定索引,强制指定索引-优快云博客

当查询无法使用合适索引时，可以手动通过`FORCE INDEX`来强制使用特定索引以提高查询效率。文章介绍了如何在创建索引、查询优化及删除索引后，通过强制指定主键或自定义索引来避免全表扫描和文件排序。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

建表语句mysql> show create table seo_php_article\G;

*************************** 1. row ***************************

Table: seo_php_article

Create Table: CREATE TABLE `seo_php_article` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`title` varchar(255) NOT NULL DEFAULT '',

`user_id` bigint(20) NOT NULL DEFAULT '0',

`create_time` datetime DEFAULT NULL,

`modify_time` datetime DEFAULT NULL,

`is_delete` tinyint(4) unsigned NOT NULL DEFAULT '0',

`viewcount` int(11) unsigned NOT NULL DEFAULT '0',

`data_from` varchar(255) NOT NULL DEFAULT '' COMMENT '数据来源',

`clean_level` tinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT '数据清理等级',

`is_short` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '文章太短',

PRIMARY KEY (`id`),

KEY `index_title` (`title`),

KEY `index_createtime` (`create_time`),

KEY `index_userid` (`user_id`)

) ENGINE=InnoDB AUTO_INCREMENT=135276 DEFAULT CHARSET=utf8

1 row in set (0.04 sec)

根据一般的经验，这种分布范围非常小的字段is_delete不建议加索引mysql> select is_delete,sum(1) from seo_php_article group by is_delete;

+-----------+--------+

| is_delete | sum(1) |

+-----------+--------+

| 0 | 135233 |

| 2 | 42 |

+-----------+--------+

比如下面的查询能命中主键mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY id DESC limit 5000,500;

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

但是下面这个就不能使用到index_createtime索引了mysql> explain SELECT id FROM seo_php_article WHERE is_delete=0 ORDER BY create_time DESC limit 5000,500;

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

方案 1

这样的话，我就加个索引吧ALTER TABLE `seo_php_article` ADD INDEX `idx_is_del_ts` (`is_delete`, `create_time`);

上面的查询没有再有filesort了，也不再是全表扫描了。mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY create_time DESC limit 5000,500;

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+--------------------------+

但是注意到这里居然扫描了68815行。

新的问题

但是之前 is_delete 作为筛选条件，然后根据 id 排序的查询则会自动命中新增加的索引mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY id DESC limit 5000,500;

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+

+----+-------------+-----------------+------+---------------+---------------+---------+-------+-------+------------------------------------------+

方案 2

这里我们使用强制指定其索引为主键，就好了，需要注意。mysql> explain SELECT id FROM seo_php_article force index(PRI) where is_delete=0 ORDER BY id DESC limit 5000,500;

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+---------+---------+------+------+-------------+

也就是说前面那个查询我们也可以不加索引，而是直接强制指定其索引为index_createtime，我又测试了下：ALTER TABLE `seo_php_article` DROP INDEX `idx_is_del_ts`;

mysql> explain SELECT id FROM seo_php_article where is_delete=0 ORDER BY create_time DESC limit 5000,500;

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

+----+-------------+-----------------+------+---------------+------+---------+------+--------+-----------------------------+

1 row in set (0.04 sec)

mysql> explain SELECT id FROM seo_php_article force index(index_createtime) where is_delete=0 ORDER BY create_time DESC limit 5000,500;

+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+

+----+-------------+-----------------+-------+---------------+------------------+---------+------+------+-------------+

1 row in set (0.04 sec)