对多行表(250m+行)的select查询

nvbavucw  于 2021-06-17  发布在  Mysql
关注(0)|答案(2)|浏览(201)

我有一个select语句,mysql需要一段时间(很长时间)才能完成。所有的table都是“myisam”。mysql版本为5.6.41。
我有三张table
表1:50000行
表2:7500000行(7.5gb)
表3:250000000行(17.5gb)
我使用表上的左连接执行select语句。

select `table1`.* 
from `table1`  
inner join `table2` on `table2`.`table1_id` = `table1`.`id` 
inner join `table3` on `table3`.`table2_id` = `table2`.`id` 
where `table1`.`id` = "2" and `table3`.`parameter` = 'param'

“id”列是主键,“parameter”列应用了索引
当前表代码

CREATE TABLE `table1` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

CREATE TABLE `table2` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    `table1_id` INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
    INDEX `table1_id_index` (`table1_id`),
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

CREATE TABLE `table3` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
    `table2_id` INT(10) UNSIGNED NOT NULL,
    `parameter` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
    PRIMARY KEY (`id`), 
    INDEX `id` (`id`), 
    INDEX `id` (`parameter`), 
    INDEX `table2_id_index` (`table1_id`),
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM;

奇怪的是,当我以另一种方式重新编写查询时,没有性能问题:

select `table1`.* 
from `table3`  
inner join `table2` on `table3`.`table2_id` = `table2`.`id` 
inner join `table1` on `table2`.`table1_id` = `table1`.`id` 
where `table1`.`id` = "2" and `table3`.`parameter` = 'param'
bejyjqdl

bejyjqdl1#

确保您有适当的id索引,并确保您没有执行无用的数据转换和

create index  idx1 on table2 (table1_id, id) 
create index  idx2 on table3 (table2_id, parameter)
ny6fqffe

ny6fqffe2#

切换到innodb。它“聚集”了 PRIMARY KEY 与数据,从而避免了几次随机寻找,你是痛苦的与myisam。
同时,它的价值是什么 key_buffer_size ? 你有多少公羊?

相关问题