代码之家 › 专栏 › 技术社区 › Dan

mysql:哪些查询可以取消哪些索引的限制?

indexing optimization mysql sql

Dan · 技术社区 · 16 年前

我使用的是mysql 5.0,对索引有点陌生。以下哪些查询可以通过索引得到帮助,我应该创建哪些索引?

(不要假设任何一个表都有唯一的值。这不是家庭作业,这只是我编的一些例子,试图让我的头在索引。)

Query1:
Select a.*, b.*
From a
Left Join b on b.type=a.type;

Query2:
Select a.*, b.*
From a,b
Where a.type=b.type;

Query3:
Select a.*
From a
Where a.type in (Select b.type from b where b.brand=5);

以下是我对这些不同类型查询将使用哪些索引的猜测:

Query1:
Create Index Query1 Using Hash on b (type);

Query2:
Create Index Query2a Using Hash on a (type);
Create Index Query2b Using Hash on b (type);

Query3:
Create Index Query2a Using Hash on b (brand,type);

Query1或Query3都不使用表A上的任何索引,这对吗?

我相信这些都应该是散列的,因为只有=或!=,对吗?

谢谢

4 回复 | 直到 16 年前

jspcal 16 年前

使用 explain mysql中的命令将提供很多关于mysql正在做什么以及如何优化查询的信息。

在q1和q2中:一个索引(a.type,所有其他a cols)和一个索引(b.type,所有其他b cols) 在第三季度:一个索引(A.B_类型,所有其他A列)和一个索引(品牌,类型)

理想情况下,您希望所有选中的列都直接存储在索引中,这样mysql就不必从索引跳回表数据来获取选中的列。但是,这并不总是可管理的(即:有时需要选择*并且索引所有列的成本太高),在这种情况下,仅索引搜索列是可以的。

所以你所说的一切都很好。

Paul Creasey 16 年前

查询3无效,但我想您的意思是

where a.type in ....

查询1与查询2相同,只是语法更好,两者可能都有相同的查询计划,并且都将使用这两个索引。

查询3将使用b.brand上的索引,但不使用其类型部分。如果您有.type的索引,它也将使用该索引。

你说得对,它们应该是散列索引。

Dmitry 16 年前

如果brand=5的b的数目接近于零,查询3可以使用a.type上的索引

如果query2是b树(因此被排序),那么它将使用索引。将哈希索引与索引联接一起使用可能会减慢查询速度(因为必须以非顺序方式读取大小(a)值)

Ken Fox 16 年前

查询优化和索引是一个庞大的主题,因此您肯定想了解mysql和您正在使用的特定存储引擎。innodb和ndb支持“using hash”;我不认为myisam支持它。

即使联接条件是相等的,您拥有的联接也将执行完整表或索引扫描;因为没有where子句,所以必须读取每一行。

使用标准的b树索引可能会更好,但是要测量它并使用“explain”调查查询计划。mysql innodb存储按主键组织的行数据,因此表上也应该有主键,而不仅仅是索引。最好可以在连接中使用主键,否则mysql将从索引中检索主键,然后执行另一次提取以获取行。该规则的一个很好的例外是,如果辅助索引包含查询中所需的所有列。这称为覆盖索引,mysql根本不需要查找行。