Mysql的强制索引(Force Index)都为我们做了哪些优化?

本文探讨了在 MySQL 的 InnoDB 引擎中使用 force index 提升 count(*) 查询效率的现象。通过对同一 SQL 语句的不同执行计划进行对比,发现 force index 改变了 type 和 rows 的值,显著提升了查询速度。

原本只是想验证一下选择不同索引对innodb count(*)查询速度的影响。
各位顺道可参考下这篇文章 [InnoDB系列] -- innodb表如何更快得到count(*)结果。

测试过程中没想到同样的一条sql语句仅仅是增加了force index后查询速度几乎快了一倍。
select count(*) from http_log_3 force index(time) where time >= 000000    //1 row in set (11 min 19.35 sec)
select count(*) from http_log_3 where time >= 000000    //1 row in set (20 min 5.86 sec)


但实际上通过explain分析可知其实这两条sql语句使用的都是time索引,完全一样!
在这个特例当中使用force index(time)后影响的并不是索引key的选择(优化器默认也使用time索引),而是type及rows.


很想知道这是为什么,rows是如何被估算出来的,可有公式?

测试环境:
数据库 mysql 5.1.34,innodb引擎,使用innodb_file_per_table选项。
使用表分区方式创建数据表(按日分区共十个),表中一共有5000万数据,即每个分区各500万。

测试输出:

--------------
explain partitions select count(*) from http_log_3 force index(time) where time >= 000000
--------------

+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
| id | select_type | table      | partitions                        | type  | possible_keys | key  | key_len | ref  | rows     | Extra                    |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | range | time          | time | 3       | NULL | 25000141 | Using where; Using index |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+--------------------------+
1 row in set (0.01 sec)

--------------
explain partitions select count(*) from http_log_3 where time >= 000000
--------------

+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
| id | select_type | table      | partitions                        | type  | possible_keys            | key  | key_len | ref  | rows     | Extra                    |
+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | index | time_ip,time_domain,time | time | 3       | NULL | 50000291 | Using where; Using index |
+----+-------------+------------+-----------------------------------+-------+--------------------------+------+---------+------+----------+--------------------------+
1 row in set (0.01 sec)

--------------
explain partitions select count(*) from http_log_3
--------------

+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
| id | select_type | table      | partitions                        | type  | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | http_log_3 | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10 | index | NULL          | time | 3       | NULL | 50000291 | Using index |
+----+-------------+------------+-----------------------------------+-------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

--------------
select count(*) from http_log_3 force index(time) where time >= 000000
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (11 min 19.35 sec)

--------------
select count(*) from http_log_3 where time >= 000000
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 5.86 sec)

--------------
select count(*) from http_log_3
--------------

+----------+
| count(*) |
+----------+
| 50000000 |
+----------+
1 row in set (20 min 6.32 sec)

 

 

 

MySQL 中,如果希望优化器在查询时强制使用某个特定索引,可以使用 `FORCE INDEX` 语法。该语法用于明确指定查询必须使用某个索引,即使该索引在查询优化器的评估中不是最优选择。这在某些特定场景下可以提升查询性能,尤其是在优化器未能正确评估数据分布或访问路径时[^1]。 ### 语法格式 ```sql SELECT * FROM table_name FORCE INDEX (index_name) WHERE condition; ``` 其中: - `table_name` 是要查询的数据表名。 - `index_name` 是希望强制使用的索引名称。 - `condition` 是查询条件。 ### 使用示例 假设存在一个名为 `orders` 的表,并且该表有一个名为 `idx_order_date` 的索引,希望在查询中强制使用该索引: ```sql SELECT * FROM orders FORCE INDEX (idx_order_date) WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'; ``` 该查询会忽略优化器的默认行为,直接使用 `idx_order_date` 索引进行数据检索。 ### 注意事项 1. **避免与 `USE INDEX` 混用** 在同一个查询中,不能同时使用 `USE INDEX` 和 `FORCE INDEX`。`USE INDEX` 是一种提示性语法,表示优化器可以选择不使用指定的索引,而 `FORCE INDEX` 则强制要求优化器必须使用指定的索引。因此,两者不能同时出现在同一个查询中[^1]。 2. **性能影响** 虽然 `FORCE INDEX` 可以在某些情况下提升查询性能,但也可能导致性能下降,尤其是在数据分布发生变化或索引不再适用的情况下。因此,在使用 `FORCE INDEX` 时应结合实际查询计划进行评估。 3. **索引维护** 如果强制使用的索引被删除或重命名,查询将失败。因此,维护索引结构时需要特别注意依赖于该索引的查询语句是否仍然有效[^2]。 4. **保留字问题** 在 MySQL 中,某些关键字是保留字,不能直接用作标识符(如列名、表名、索引名等),除非使用引号括起来。例如,在使用 `FORCE INDEX` 时,如果索引名或表名使用了保留字,需要使用反引号(`)包裹[^3]。 ### 查询当前无用索引 在决定是否强制使用某个索引之前,可以通过以下查询查看系统中是否存在无用的索引: ```sql SELECT * FROM sys.schema_unused_indexes; ``` 此查询需要 `performance_schema` 已启用,并可以帮助识别那些从未被使用的索引,从而进行清理或优化[^2]。 ---
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值