MySQL范围检查而不是内部联接上的索引使用 [英] Mysql range check instead of index usage on inner join

问题描述

我在使用MySQL(innoDB)5.0时遇到严重问题.

I'm having a serious problem with MySQL (innoDB) 5.0.

使用非常意外的查询计划执行非常简单的SQL查询.

A very simple SQL query is executed with a very unexpected query plan.

查询:

SELECT 
SQL_NO_CACHE
mbCategory.*

FROM 
MBCategory mbCategory 

INNER JOIN ResourcePermission as rp
    ON rp.primKey = mbCategory.categoryId 

where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
limit 20;

MBCategory-包含216583行

MBCategory - contains 216583 rows

ResourcePermission-包含3098354行.

ResourcePermission - contains 3098354 rows.

在MBCategory中，我有多个索引(列的顺序与索引中的顺序相同):

In MBCategory I've multiple indexes (columns order as in index):

Primary (categoryId)
A (groupId,parentCategoryId,categoryId)
B (groupId,parentCategoryId)

在ResourcePermission中，我有多个索引(列的顺序与索引中的顺序相同):

In ResourcePermission I've multiple indexes (columns order as in index):

Primary - on some column
A (primKey).

当我查看查询计划时，Mysql首先更改表顺序并从ResourcePermission中选择行，然后将其加入MBCategory表(疯狂的主意)，并且需要很长时间.因此，我添加了STRAIGHT_JOIN来强制innodb引擎使用正确的表顺序:

When I look into query plan Mysql changes tables sequence and selects rows from ResourcePermission at first and then it joins the MBCategory table (crazy idea) and it takes ages. So I added STRAIGHT_JOIN to force the innodb engine to use correct table sequence:

SELECT

STRAIGHT_JOIN SQL_NO_CACHE
mbCategory.*

FROM 
MBCategory
 mbCategory 

INNER JOIN ResourcePermission as rp
    ON rp.primKey = mbCategory.categoryId 

where mbCategory.groupId = 12345 AND mbCategory.parentCategoryId = 0 
limit 20;

但是这里的第二个问题是: 在我看来，mysql应该在联接操作上使用index A (primKey)，而不是对每个记录执行范围检查(索引映射:0x400)，并且它又需要很长时间！ 强制索引没有帮助，mysql仍对每个记录执行范围检查.

But here the second problem materialzie: In my opinion mysql should use index A (primKey) on the join operation instead it performs Range checked for each record (index map: 0x400) and it again takes ages ! Force index doesn't help, mysql still performing Range checked for each record .

MBCategory中只有23行满足where条件，并且加入后只有75行. 如何使mysql在此操作上选择正确的索引?

There are only 23 rows in the MBCategory which fulfill where criteria, and after join there are only 75 rows. How can I make mysql to choose correct index on this operation ?

推荐答案

好， 基本问题. 我欠自己一杯啤酒. 我最近要调试的系统不是我开发的系统-我的管理层已将其分配给我以提高性能(originall团队不了解此主题).

Ok, elementary problem. I owe myself a beer. The system I'm recently tunning is not a system I've developted - I've been assigned to it by my management to improve performance (originall team doesn't have knowledge on this topic).

经过数周的改进SQL查询，索引，正在由应用程序执行的sql查询数量之后，在这种情况下，我没有检查最重要的事情之一！

After fee weeks of improving SQL queries, indexes, number of sql queries that are beeing executed by application I didn't check one of the most important things in this case !!

列类型不同！

写过比某种代码还要好的开发人员.

Developer who have written than kind of code should get quite a big TALK.

感谢您的帮助！

这篇关于MySQL范围检查而不是内部联接上的索引使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！