Percona 5.6 InnoDB问题未正确使用索引 [英] Percona 5.6 InnoDB issue not using indexes correctly

问题描述

我刚刚在新的CentOS 6.4服务器上安装了Percona 5.6.这是一台快速运行的32核氙气，72GB内存，8个SAS RAID 10设置.到目前为止一切顺利

I just installed Percona 5.6 on my new CentOS 6.4 server. It's a fast machine 32 core xenon, 72GB ram, 8x SAS RAID 10 setup. So far so good

我的旧服务器功能稍差一些，并且仍在运行MySQL 5.1.因此，这是一个相当大的升级.但是我在使用InnoDB时遇到了一些问题，它似乎没有在某些表上正确使用索引.在我的旧计算机上的哪个位置上，相同的查询运行正常.

My old server is a bit less powerful, and was running MySQL 5.1 still. So this was quite an upgrade. But I'm having some issues with InnoDB, it is not using the indexes correctly on some tables it seems. Where on my old machine the same queries were running fine.

两个服务器都具有相同的数据库.我在旧计算机上执行了mysqldump并将其导入到新的Percona 5.6服务器上.索引保持不变.两台服务器使用相同的my.cnf配置设置.

Both servers have the same database. I did a mysqldump on the old machine and imported it onto the new Percona 5.6 server. Indexes stayed the same. Both servers use the same my.cnf config settings.

表项的索引位于:item_id, item_format, item_private，并且包含约4000万行.表格式的索引为:format_id，包含约250行.

Table items has indexes on: item_id, item_format, item_private and contains about 40 million rows. Table formats has index on: format_id and contains about 250 rows.

SELECT 
i.item_name, i.item_key, i.item_date, f.format_long
FROM
items i, formats f
WHERE 
i.item_format = f.format_id
AND
i.item_private = 0 
ORDER BY 
i.item_id DESC LIMIT 8

在我的旧服务器上，此查询大约需要0.0003 seconds.在新服务器上，它将接管100 seconds.

On my old server this query takes about 0.0003 seconds. On the new server it takes over 100 seconds.

在OLD服务器上使用EXPLAIN查询.

Query with EXPLAIN on OLD server.

+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
| id | select_type | table |  type  | possible_keys |   key   | key_len |         ref          | rows |    Extra    |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+
|  1 | SIMPLE      | i     | index  | item_format   | PRIMARY |       4 | NULL                 |    8 | Using where |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY       | PRIMARY |       4 | dbname.i.item_format |    1 |             |
+----+-------------+-------+--------+---------------+---------+---------+----------------------+------+-------------+

在新的[问题]服务器上使用EXPLAIN查询.

Query with EXPLAIN on NEW [problem] server.

+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys |     key     | key_len |        ref         | rows |              Extra              |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | f     | ALL  | PRIMARY       | NULL        | NULL    | NULL               |  219 | Using temporary; Using filesort |
|  1 | SIMPLE      | i     | ref  | item_format   | item_format | 4       | dbname.f.format_id | 3026 | Using where                     |
+----+-------------+-------+------+---------------+-------------+---------+--------------------+------+---------------------------------+

您可以看到它正在使用临时和文件排序.这似乎是速度缓慢的原因.

You can see that it's using temporary and filesort. This seems to be the reason for the slowness.

有什么想法可以解决这个问题吗?

Any idea how I could resolve this issue?

推荐答案

这听起来像:错误#70617默认的永久统计信息可能会导致意外的长查询时间

就其价值而言，这不是Percona的错误，它还存在于MySQL 5.6社区版中.

For what it's worth, this is not a Percona bug, it's also present in MySQL 5.6 community edition.

有三种可能的解决方法:

There are three possible workarounds:

1. 使用STRAIGHT_JOIN向优化器提示不要重新排序表引用.

1. Use STRAIGHT_JOIN to give a hint to the optimizer not to reorder table references.

SELECT STRAIGHT_JOIN
  i.item_name, i.item_key, i.item_date, f.format_long
FROM items i
INNER JOIN formats f
  ON i.item_format = f.format_id
WHERE i.item_private = 0 
ORDER BY i.item_id DESC LIMIT 8

我将您的JOIN重写为使用SQL-92语法，这是我建议的.

I've rewritten your JOIN to use SQL-92 syntax, which I recommend.

禁用新的 InnoDB持久统计信息功能，恢复到5.6之前的行为.

Disable the new InnoDB persistent stats feature, reverting to pre-5.6 behavior.

在您的my.cnf文件中:

In your my.cnf file:

innodb_stats_persistent=0

对数据进行重大更改后(例如，加载mysqldump之后)，手动刷新InnoDB优化器统计信息:

Refresh InnoDB optimizer stats manually after you do a significant change to the data (for example, after loading a mysqldump):

ANALYZE TABLE items;
ANALYZE TABLE formats;

PS:我在Percona工作，这个错误是我的同事 Justin Swanhart 发现的.

PS: I work at Percona, and this bug was discovered by my colleague Justin Swanhart.

这篇关于Percona 5.6 InnoDB问题未正确使用索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！