大型 MYSQL 表的索引

Question

希望你能允许我挑选你的大脑，这样我就可以在这个过程中获得一些知识。 我们有 3 张桌子 - data_product、data_issuer、data_accountbalance

CREATE TABLE `data_issuer` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`issuer_name` varchar(128) NOT NULL
PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `data_product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`issuer_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_product_name_issuer_id_260fec65_uniq` (`name`,`issuer_id`),
KEY `data_product_issuer_id_d07fa696_fk_data_issuer_id` (`issuer_id`),
CONSTRAINT `data_product_issuer_id_d07fa696_fk_data_issuer_id` FOREIGN KEY 
(`issuer_id`) REFERENCES `data_issuer` (`id`)
) ENGINE=InnoDB

CREATE TABLE `data_accountbalance` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`nominee_name` varchar(128) NOT NULL,
`beneficiary_name` varchar(128) NOT NULL,
`nominee_id` varchar(128) NOT NULL,
`account_id` varchar(16) NOT NULL,
`product_id` int(11) NOT NULL,
`register_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `data_accountbalance_date_product_id_nominee__7b8d2c6a_uniq` (`date`,`product_id`,`nominee_id`,`beneficiary_name`),
KEY `data_accountbalance_product_id_nominee_id_date_8ef8754f_idx` (`product_id`,`nominee_id`,`date`),
KEY `data_accountbalance_register_id_4e78ec16_fk_data_register_id` (`register_id`),
KEY `data_accountbalance_product_id_date_nominee_i_c3a41e39_idx` (`product_id`,`date`,`nominee_id`,`beneficiary_name`,`balance_amount`),
CONSTRAINT `data_accountbalance_product_id_acfb18f6_fk_data_product_id` FOREIGN KEY (`product_id`) REFERENCES `data_product` (`id`),
CONSTRAINT `data_accountbalance_register_id_4e78ec16_fk_data_register_id` FOREIGN KEY (`register_id`) REFERENCES `data_register` (`id`)
) ENGINE=InnoDB

运行下面的查询时，系统大约需要一个小时才能响应 -

SELECT SQL_NO_CACHE *
from data_product
INNER JOIN `data_issuer` ON (`data_issuer`.`id` = `data_product`.`issuer_id`)
INNER JOIN `data_accountbalance` ON (`data_accountbalance`.`product_id` = `data_product`.`id`)
LIMIT 100000000;

data_issuer和data_product都只有 100 条记录，但data_accountbalance很大，大约有 15,384,358 条记录。

产生的解释计划如下 -

# id select_type表分区类型possible_keys键key_len引用行过滤额外 1 简单data_product全部主要，data_product_issuer_id_d07fa696_fk_data_issuer_id 459 100 1 简单data_issuer eq_ref 主要 4 pnl.data_product.issuer_id 1 100 1 简单 data_accountbalance 参考 data_accountbalance_product_id_nominee_id_date_8ef8754f_idx，data_accountbalance_product_id_date_nominee_i_c3a41e39_idx data_accountbalance_product_id_date_nominee_i_c3a41e39_idx 4 pnl.data_product.id 493 100

有人可以帮助调整查询，这样它就不会花一个小时来运行吗？感谢您对我的任何指示。

Answer 1

如果您的查询实际上是您在那里显示的内容...... 那么这就是问题所在。 它没有 WHERE 子句。

该查询实际上将返回 15,384,358 个结果。 由于两个较小的表是典型的域表，其关系一直为 NOT NULL 关系，因此它将为 data_accountbalance 中的每一行返回 1 到 1 的结果。

实际的时间成本可能是创建一个庞大的临时表(我不确定)。 只是为了下载整个数据库，所有 3 个表，您可以考虑优化您的临时表 MySQL 配置以可能加快速度，或者最好这样做，以便在您开始执行查询时，您可以在 MySQL 准备好时读取结果(避免临时表)。 或者，也许运行此查询的脚本正在尝试将整个数据集读入内存，这需要很长时间？

下载所有数据是否有特殊原因？ 通常，您只需下载要操作的数据。 或者让MySQL进行分组，求和等，然后根据所有数据返回您想要的答案。

您希望查询返回多少行？ 如果你正在考虑少于 1500 万的东西，那么答案是添加某种 WHERE 语句或聚合函数。 根据您用于减少结果集的表和列，必须为这些列编制索引。

我希望这有帮助。:)