MySQL 使用了不正确的索引。为什么？

2024-03-26

有一个典型的Users表，包含字段：id (primary), application_id, login, phone, etc. (application_id- 选择性字段）

有几个索引：

index_users_on_application_id,
unique_index_users_on_application_id_and_login
unique_index_users_on_application_id_and_phone

查询本身非常简单：

SELECT  `users`.*
FROM `users`
WHERE `users`.`application_id` = 1234
LIMIT 10 OFFSET 0;

棘手的部分是该查询使用两个唯一索引之一（unique_index_users_on_application_id_and_login例如），然后返回按以下顺序排序的用户列表login。但我需要将它们排序id.

为此，我更新了查询：

SELECT  `users`.*
FROM `users`
WHERE `users`.`application_id` = 1234
ORDER BY id
LIMIT 10 OFFSET 0;

好了，现在解释一下，MySQL开始使用PRIMARY键而不是任何索引。但这是怎么发生的呢？如果index_users_on_application_id实际上应该包含两个字段：[application_id, id] (InnoDB)，因此该索引非常适合查询，但 MySQL 决定选择另一个字段。

如果我说IGNORE INDEX(PRIMARY)，MySQL开始使用unique_index_users_on_application_id_and_login，仍然忽略正确的索引。当ORDER BY id+0.

我也尝试过ORDER BY application_id, id为了确保索引最适合，MySQL 仍然选择错误的索引。

任何想法，为什么会发生以及如何确保 MySQL 使用正确的索引而无需明确说明USE INDEX(index_users_on_application_id)?

完整索引列表Users table:

mysql> show indexes from users;
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name                                            | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY                                             |            1 | id                   | A         |       21893 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | index_users_on_confirmation_token                   |            1 | confirmation_token   | A         |          28 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_reset_password_token                 |            1 | reset_password_token | A         |          50 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_external_user_id  |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_external_user_id  |            2 | external_user_id     | A         |         995 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_login             |            1 | application_id       | A         |          30 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          0 | index_users_on_application_id_and_login             |            2 | login                | A         |       21893 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | users_account_id_fk                                 |            1 | account_id           | A         |          44 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | users_blob_id_fk                                    |            1 | blob_id              | A         |         118 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_remember_token                       |            1 | remember_token       | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id                       |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_facebook_id       |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_facebook_id       |            2 | facebook_id          | A         |        3127 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_twitter_digits_id |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_twitter_digits_id |            2 | twitter_digits_id    | A         |         138 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_email             |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_email             |            2 | email                | A         |        2189 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_full_name         |            1 | application_id       | A         |          32 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | index_users_on_application_id_and_full_name         |            2 | full_name            | A         |        5473 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 rows in set (0.01 sec)

解释示例：

mysql> EXPLAIN SELECT  `users`.* FROM `users` WHERE `users`.`application_id` = 56374  ORDER BY id asc LIMIT 1 OFFSET 0;
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys                                                                                                                                                                                                                                                                                                  | key                                                | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | users | ref  | index_users_on_application_id_and_external_user_id,index_users_on_application_id_and_login,index_users_on_application_id,index_users_on_application_id_and_facebook_id,index_users_on_application_id_and_twitter_digits_id,index_users_on_application_id_and_email,index_users_on_application_id_and_full_name | index_users_on_application_id_and_external_user_id | 5       | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

问题本身在于，使用错误的索引会导致类似的查询（限制为 100 而不是 1）执行几分钟，而使用正确的索引则只需一瞬间的事情。

分析：

SET PROFILING = 1; SELECT `users`.* FROM `users` WHERE `users`.`application_id` = 56374  ORDER BY id asc LIMIT 1 OFFSET 0; SHOW PROFILE FOR QUERY 1; SET PROFILING = 0;
Query OK, 0 rows affected (0.00 sec)

+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
-- fields list --
+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
| 27265241 |     NULL | Some Username | NULL  | 9777  | SomeHash | AnotherHash | NULL  | NULL    | 2017-04-12 15:53:32 | 2017-09-21 13:39:51 | 2017-09-24 19:19:06 |             1234 | NULL        | NULL       |    NULL | NULL                 | NULL                   | NULL                | NULL               | 2017-07-05 10:59:59 | NULL                 | NULL              |      12345 | NULL           | NULL        | something_else | NULL              |                       1 | another_hash |          54321 |
+----------+----------+-----------------+-------+-------+----------------------------------------------------------------------------------------------------------------------------------+----------------------+-------+---------+---------------------+---------------------+---------------------+------------------+-------------+------------+---------+----------------------+------------------------+---------------------+--------------------+---------------------+----------------------+-------------------+------------+----------------+-------------+---------------------------------------------------------------------------------------+-------------------+-------------------------+--------------------------------------------------+----------------+
1 row in set (1 min 14.43 sec)

+--------------------------------+-----------+
| Status                         | Duration  |
+--------------------------------+-----------+
| starting                       |  0.000068 |
| Waiting for query cache lock   |  0.000025 |
| init                           |  0.000025 |
| checking query cache for query |  0.000047 |
| checking permissions           |  0.000026 |
| Opening tables                 |  0.000031 |
| After opening tables           |  0.000025 |
| System lock                    |  0.000025 |
| Table lock                     |  0.000026 |
| Waiting for query cache lock   |  0.000037 |
| init                           |  0.000046 |
| optimizing                     |  0.000032 |
| statistics                     |  0.000225 |
| preparing                      |  0.000042 |
| executing                      |  0.000025 |
| Sorting result                 |  0.000057 |
| Sending data                   | 42.952100 |
| end                            |  0.000070 |
| query end                      |  0.000027 |
| closing tables                 |  0.000025 |
| Unlocking tables               |  0.000028 |
| freeing items                  |  0.000028 |
| updating status                |  0.000039 |
| cleaning up                    |  0.000025 |
+--------------------------------+-----------+
24 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

您应该能够使用索引提示和优化器提示来建议正确的索引用法：

索引提示 https://dev.mysql.com/doc/refman/5.7/en/index-hints.html

优化器提示 https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

您可以直接提示该表：

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

对于您的情况，我认为最好的解决方案是按名称命名属性而不是使用星号*并使用IGNORE INDEX (unique_index_users_on_application_id_and_login, unique_index_users_on_application_id_and_phone ) FOR ORDER BY直接查询：

基于您的代码的示例：

SELECT  u.id,
        u.application_id,
        u.login,
        u.phone,
        # ... here to continue
FROM users as u
IGNORE INDEX (unique_index_users_on_application_id_and_login, unique_index_users_on_application_id_and_phone ) FOR ORDER BY
WHERE u.application_id = 1234
ORDER BY u.id
LIMIT 10 OFFSET 0;

第一次编辑
由于下面的评论，我添加了一个使主键无效的技巧。

您还可以通过以下技巧使主键无效：

SELECT u.id, 
       u.application_id,
       u.login,
       u.phone,
       #...
FROM users as u
WHERE u.application_id = 1234
ORDER BY u.id+0

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

MySQL 使用了不正确的索引。为什么？的相关文章

MySQL 将 ÅäÖ 视为 AAO？

这两个查询给了我完全相同的结果 select from topics where name Harligt select from topics where name H rligt 这怎么可能看起来mysql在搜索时会将翻译成aao
数据库中的持久日期不等于检索日期

我有一个具有 Date 属性的简单实体类此属性对应于 MySQL 日期时间列 Entity public class Entity Column name start date Temporal TemporalType TIMESTAM
如何使用MySqlCommand和prepare语句进行多行插入？(#C)

Mysql 给出了如何使用准备语句和 NET 插入行的示例 http dev mysql com doc refman 5 5 en connector net programming prepared html http dev mysq
MySQL #1093 - 您无法在 FROM 子句中指定用于更新的目标表“赠品”

I tried UPDATE giveaways SET winner 1 WHERE ID SELECT MAX ID FROM giveaways 但它给出了 1093 您无法指定目标表赠品进行更新FROM clause 本文 ht
表达与空列表相同的 IN 条件的可移植方式是什么？

例如工作于sqllite3 select from orders where custid and status in 但它失败了postgres ksysdb select code applicable objecttype from
在PHP中，如何在表格中显示数组内容

如果我在 MySQL 客户端中进行选择我将得到如下所示的输出 mysql gt select FROM group LIMIT 2 group id group supergroup id group deletable group la
SELECT DISTINCT HAVING 计算唯一条件

我已经搜索过这个问题的答案但找不到如何根据条件获取这个不同的记录集我有一个包含以下示例数据的表 Type Color Location Supplier Apple Green New York ABC Apple Green New
Oracle内置函数元数据

有没有办法获取 Oracle 内置聚合和其他功能的元数据例如AVG STDDEV SQRT ETC 我需要知道对象 id 和参数元 In the SYS ALL OBJECTS查看我找不到任何有用的东西我也尝试过搜索SYS ALL AR
存储过程总是返回0

我试图从存储过程获取返回值但它总是返回 0 c code cmd new SqlCommand cmd CommandType CommandType StoredProcedure cmd CommandText AbsentEntry
在 MySQL 中插入时检查并防止相似字符串

简要信息我有3张桌子 Set id name SetItem set id item id position TempSet id 我有一个函数可以生成新的随机组合Item桌子基本上总是在成功生成之后我在中创建一个新行Set表获取
在SQL中，如何通过查找与某一列相等的所有行来更新表的每一行，然后将另一列设置为彼此相等

所以基本上这就是伪代码但我不知道如何在 SQL 中执行此操作请帮忙 for each row in table1 loop through each row in table 2 if table1 s row column 1 tab
如何将列中的天数添加到 DB2 中的当前日期？

我正在编写此 SQL 来动态计算一定的天数如下所示但我不知道如何让它工作因为我不断收到错误 select Current Date Dynamic numbr of days calculation here from TableNa
如何解决注意：未定义索引：第 21 行 C:\xampp\htdocs\invmgt\manufactured_goods\change.php 中的 id [重复]

这个问题在这里已经有答案了我的 PHP 代码有一个问题显示注意未定义的索引我确信它非常简单因为我是初学者所以我不太清楚到底出了什么问题所以请帮助我这是代码
如何修改 Amazon RDS 实例的 my.ini 参数

在启动 MySQL 5 5 RDS 实例时 Amazon 使用默认参数组来配置 my ini 参数但控制台不允许我修改它们我怎样才能实现这个目标例如默认将存储引擎设置为MyISAM或设置字符集此处记录了这一点 http aws am
MySQL 跨表计数(*) 查询帮助

SELECT name COUNT AS count FROM t1 t2 WHERE t2 id t1 id GROUP BY t2 id 我想从 t1 获取名称以及 t2 中 id 与 t1 相同的行数到目前为止我已经得到了上面的内容
如何从 SQL Server 中的 SELECT 进行更新？

In SQL服务器可以将行插入到带有INSERT SELECT陈述 INSERT INTO Table col1 col2 col3 SELECT col1 col2 col3 FROM other table WHERE sql coo
Laravel Schema Builder 改变存储引擎

我正在尝试更改表并将其存储引擎更改为InnoDb 当我跑步时php artisan migrate它完成且没有错误然而当我检查 Sequel Pro 中的存储引擎时没有任何变化 public function up Schema ta
Solr 索引时间提升 VS 查询时间提升？

问题 1 我们可以只进行查询时间提升使用 dismax 而不在索引时间提及提升值吗问题 2 与查询时间提升相比索引时间提升有何优点缺点反之亦然查询时间和索引时间提升在索引时您可以选择提升特定文档整个文档或仅一个字段它作为
MySQL 复制是双向的

我们已经成功设置了 MySQL 文献中描述的主从复制不过我很好奇是否有人设置了双向复制例如如果安装了 Drupal 或 Wordpress 第一个主数据库服务器出现故障第二个从属数据库服务器恢复正常与此同时用户不断进行
Oracle - 获取星期几

今天是星期二为什么当我运行这个 SQL 语句时它说今天不是星期二 SELECT CASE WHEN TO CHAR sysdate Day Tuesday THEN Its Tuesday ELSE Its Not Tuesday EN

随机推荐

PCRECPP (PCRE) 从 url 代码中提取主机名问题

我有一段简单的 C 代码 int main void string text http www amazon com string a b c d e f pcrecpp RE re w w w d w if re PartialMatch
指令（嵌套在部分内部）抛出“模板必须只有一个根元素”

我试图遵循角度最佳实践建议并使用指令来封装可重用的 HTML 元素错误信息错误模板必须只有一个根元素是 partials user path to somedata html 指令代码 directive stDirectivena
如何在 React Native 中按百分比值对视图的宽度进行动画处理？

我在 React Native 中有一个 Animated View 我正在为其宽度设置动画 const pollRef useRef new Animated Value 0 current const resultWidth votes
后期绑定创建对象 VBA

我想使用后期绑定来使用以下参考 Name Microsoft Windows 通用控件 6 0 SP6 文件路径 C Windows System32 MSCOMCTL OCX GUID 831FDD16 0C5C 11D2 A9FC 00
我必须在可读事件处理程序中重复调用 Readable.read() 吗？

假设我创建了一个名为的转换流Parser它可以像普通流一样写入但可以作为对象流读取我正在使用readable使用此转换流的代码的事件 var parser new Parser parser on readable function v
Python 函数参数和操作数的计算顺序是确定性的（+记录在哪里）？

C 没有保证任何评估订单 http en cppreference com w c language eval order所以像这样的声明f g1 g2 g3 g4 可能会执行g1 g2 g3 and g4 以任何顺序尽管f 将在所有这些
试图找到 java petstore 2.0

我搜索了 java petstore 2 0 但没有成功链接http java net projects blueprints downloads html http java net projects blueprints downlo
如何在 C++ 中逐行迭代 cin？

我想迭代一遍std cin 逐行将每一行作为一个std string 哪个更好 string line while getline cin line process line or for string line getline cin
Laravel 5.2 如何在中间件中使用 config::set

我想用config set在我的中间件中全局设置值但它正在产生错误下面是我正在使用的代码 public function handle request Closure next guard admin if Auth guard gua
自定义节点环境中意外的令牌导入

我得到了SyntaxError Unexpected token import当使用我的自定义类扩展时NodeEnvironment 当不使用自定义测试环境时现代 JavaScript ES 模块等可以正常工作我正在使用节点8 11 1
如何在另一个div中克隆和重新绘制谷歌图表？

我很难弄清楚如何做到这一点因为我对谷歌图表不太熟悉我正在使用pivotTable js 一切正常但我想保存或有一个按钮将当前创建的图表克隆到另一个div 我做了一个全局变量 var wrapper 这是为了保存图表 wrappe
如何访问 Maven 项目中的 XML 文件，使其在打包时保持可用

我目前开始开发一个 Maven Web 应用程序项目该项目需要使用 jetty run exploded 目标启动以便在 Eclipse 中进行开发调试现在我有一个 XML 文件我需要在运行时访问其内容我的问题是将文件放在哪
linq聚合嵌套计数

我有以下课程 class Outer public ICollection
应用程序在 iOS 6 上崩溃：找不到符号：___sync_fetch_and_add_4

我有一个与 iOS4 和 iOS5 完美配合的应用程序它使用针对 ARM 的 Zeromq 库的静态编译版本 Apple 拒绝了我的应用程序因为他们声称它在 iOS 6 下崩溃尚未发布 wth 在使用 iOS 6 GM 尝试之后我可
__attribute__((constructor)) 到底是如何工作的？

看起来很清楚它应该设置一些东西具体什么时候运行为什么有两个括号 Is attribute 一个函数宏句法这在 C 中有效吗 C 它所使用的函数是否需要是静态的什么时候 attribute destructor run Objec
使用 IIS7 的 URL 重写捕获 Cookie 中的值

我需要为 IIS 7 5 网站编写一个 URL 重写规则捕获特定 cookie 中的值然后使用该值构建 URL 例如传入的请求如下所示 GET http myserver com test aspx HTTP 1 1 Accept t
定时器在跨线程中不起作用

我有2个全球System Windows Forms Timer in my form 两者都在表单的构造函数中初始化但还没有开始构造函数启动一个新线程该线程启用并启动两个计时器显然这就是全部交叉螺纹但它不会引发任何跨线程异常但它
从 URL 中删除 ID（类别和文章）（Joomla 3.3 / 3.4 / 3.5）

我有一个Joomla 3 3 6 编辑 3 5 网站仅分为 3 层四大类 i ii iii iiii 每个类别都分为一些中间类别 A B C 最后是文章两种语言 en pt The URL设置后Search Engine Friendl
添加到父实体时是否会自动跟踪子实体？

我想知道 EF Code First 是否会自动跟踪下面示例中的子对象 var db MyDataContext var order db Orders Find orderId order AddOrderLine Fancy Pro
MySQL 使用了不正确的索引。为什么？

有一个典型的Users表包含字段 id primary application id login phone etc application id 选择性字段有几个索引 index users on application id uni

MySQL 使用了不正确的索引。为什么？

MySQL 使用了不正确的索引。为什么？ 的相关文章

随机推荐

热门标签

MySQL 使用了不正确的索引。为什么？的相关文章