为什么MySQL选择看起来效率较低的索引？

Question

我遇到了一些我继承的数据库/查询的问题。这是针对一些大型数据集和正在完成的报告。

我试图调整和调整以获得一些改进。

发生了什么是我不是100％清楚MySQL如何决定使用哪个索引。

为什么下面列出的第一个查询不使用查询2中使用的索引。在查询2中，我正在做什么我是，假设查询引擎应该这样做，取小表，获取适当的值，然后将它们应用于搜索更大的表格，并使用适当的索引。

我在这里做错了什么？或者说，我是什么误解约键，索引，并加入外国是如何在这里工作:)

这里有2个相关表格

表1
〜450行

CREATE TABLE `client_accounts_dim` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`client_id` int(10) unsigned NOT NULL, 
`service_provider_id` int(10) unsigned NOT NULL, 
`account_number` varchar(45) NOT NULL, 
`label` varchar(128) DEFAULT NULL, 
`service_provider_name` varchar(45) NOT NULL, 
`client_name` varchar(45) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `client_id` (`client_id`,`account_number`) 
) ENGINE=InnoDB; 

表2
〜1100万行

CREATE TABLE `invoices_fact` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`invoice_number` varchar(45) NOT NULL COMMENT ' ', 
... 
... 
`tracking_number` varchar(45) DEFAULT NULL, 
`division_id` int(11) DEFAULT NULL, 
`client_accounts_dim_id` int(10) unsigned NOT NULL, 
`invoice_date_dim_id` bigint(20) DEFAULT NULL, 
`shipment_date_dim_id` bigint(20) NOT NULL, 
`received_date_dim_id` bigint(20) NOT NULL, 
PRIMARY KEY (`id`), 
KEY `fk_invoice_details_client_accounts_dim1_idx` (`client_accounts_dim_id`), 
KEY `invoice_date_dim_id` (`invoice_date_dim_id`), 
KEY `shipment_date_dim_id` (`shipment_date_dim_id`,`client_accounts_dim_id`,`division_id`,`tracking_number`), 
CONSTRAINT `fk_invoice_details_client_accounts_dim1` FOREIGN KEY (`client_accounts_dim_id`) REFERENCES `client_accounts_dim` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION 
) ENGINE=InnoDB; 

在这里我做一个基本的查询加盟

个

SELECT count(distinct tracking_number) as val, p.division_id as division_id 
FROM client_accounts_dim c, invoices_fact p 
WHERE c.id = p.client_accounts_dim_id 
AND p.division_id IN (2,3,7) 
AND c.client_id = 17 
AND p.shipment_date_dim_id between 20120101 and 20121108 
GROUB BY p.division_id; 

奔跑在28S
解释收益率

+----+-------------+-------+------+------------------------------------------------------------------+---------------------------------------------+---------+---------+------+-------------+ 
| id | select_type | table | type | possible_keys             | key           | key_len | ref  | rows | Extra  | 
+----+-------------+-------+------+------------------------------------------------------------------+---------------------------------------------+---------+---------+------+-------------+ 
| 1 | SIMPLE  | c  | ref | PRIMARY,client_id            | client_id         | 4  | const | 49 | Using index | 
| 1 | SIMPLE  | p  | ref | fk_package_details_client_accounts_dim1_idx,shipment_date_dim_id | fk_package_details_client_accounts_dim1_idx | 4  | c.id | 913 | Using where | 
+----+-------------+-------+------+------------------------------------------------------------------+---------------------------------------------+---------+---------+------+-------------+ 

查询在那里我做了加入 “手动” 先运行一个查询，然后把client_accounts_dim_ids英寸

SELECT count(distinct tracking_number) as val, p.division_id as division_id 
FROM invoices_fact p 
WHERE division_id in (2,3,7) 
AND p.client_accounts_dim_id IN (232, 233, 234, 277, 235, 236, 279, 237, 238, 239, 240, 241, 242, 243, 244, 245, 246, 247, 248, 249, 250, 251, 278, 280, 262, 263, 264, 252, 256, 254, 259, 261, 257, 266, 276, 267, 255, 258, 274, 273, 272, 271, 269, 270, 268, 275, 253, 265, 260) 
AND p.shipment_date_dim_id between 20120101 and 20121108 
GROUP BY p.division_id; 

奔跑1.6s
说明收益率：

+----+-------------+-------+-------+------------------------------------------------------------------+------------------------+---------+------+---------+--------------------------+ 
| id | select_type | table | type | possible_keys             | key     | key_len | ref | rows | Extra     | 
+----+-------------+-------+-------+------------------------------------------------------------------+------------------------+---------+------+---------+--------------------------+ 
| 1 | SIMPLE  | p  | range | fk_package_details_client_accounts_dim1_idx,shipment_date_dim_id | shipment_date_dim_id | 19  | NULL | 4991810 | Using where; Using index | 
+----+-------------+-------+-------+------------------------------------------------------------------+------------------------+---------+------+---------+--------------------------+ 

Answer 1

MySQL确实应该首先查看最小的表，它是 - client_accounts_dim。你已经给它client_id索引，所以它可以很容易地将client_id=17的信息。

然后，mysql需要采取id并加入到invoice_fact。您已经为此任务提供了fk_invoice_details_client_accounts_dim1_idx。这听起来很合理！

现在，两个问题，一个硬，一个容易。第一：

一旦MySQL的发现你的指数client_accounts_dim.client_id = 17行，它是如何得到它需要加入CLIENT_ID？

而第二个：

一旦MySQL的加入到invoices_fact.client_accounts_dim_id，它是如何从你的WHERE子句申请的其余信息？

对于第一个问题，我读过的InnoDB把主键进入以后的所有指标，但我不能把我的手指上，说它将使用它为您的加入明确的解释。我建议使这一明确的综合指数：

client_accounts_dim (client_id, id) 

对于第二个问题，一旦MySQL已经发现，在指数的加盟信息，就必须去阅读，以了解哪些从磁盘中的所有相应的行他们在您指定的部门和日期范围内。另一个综合指数救援：

invoices_fact (client_accounts_dim_id, division_id, shipment_date_dim_id) 

注：将列2 & 3以正确的顺序，用最低的基数列第一。现在

，MySQL能够只是困扰你的索引来收集行的完整列表！

从上面的讨论联接的列

除此之外，它看起来像你只使用一个多列 - invoices_fact.tracking_number。如果您添加到您的索引，MySQL能得到它需要从索引查询，而没有从磁盘读取基础行的一切。

invoices_fact (client_accounts_dim_id, division_id, shipment_date_dim_id, tracking_number) 

注：tracking_number是一个宽列，这将增强自己的指数，减缓写道，占用更多的磁盘空间，等等。你可能测试两者兼得。

希望这会有所帮助。