如何用多个联接索引查询

Question

我有一个这个模式查询，它在我的笔记本上使用8s，在运行该服务的vm上使用更长的时间。

SELECT DISTINCT `mofs`.*
FROM `mofs`
         INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `mofs`.`id`
         INNER JOIN `isodata` ON `isodata`.`isotherm_id` = `isotherms`.`id`
WHERE `mofs`.`hidden` = FALSE
  AND (isodata.gas_id in (24))
  AND (volumeA3 is not NULL and atomicMass is not NULL)
ORDER BY `mofs`.`id` ASC
LIMIT 100;

本质上，我加入了mofs ->等温线->等温线(所有简单的一个-多个关系)，并且只选择在用户(24)提供的列表中包含D2的mofs，但是这里可能会有1-6麻木。

在条件mofs.hidden = false、mofs.volumeA3 is not NULL和mofs.atomicMass is not NULL中有几个常量，这些条件对于每个查询都是相同的。

我尝试添加一个索引:mofs, [:volumeA3, :atomicMass, :hidden], where: 'volumeA3 IS NOT NULL and atomicMass IS NOT NULL and hidden is FALSE'来捕获查询的静态部分。这并不能使它特别快。

查询解释程序：

mysql> EXPLAIN SELECT DISTINCT `mofs`.* FROM `mofs`          INNER JOIN `isotherms` ON `isotherms`.`mof_id` = `mofs`.`id`          INNER JOIN `isodata` ON `isodata`.`isotherm_id` = `isotherms`.`id` WHERE `mofs`.`hidden` = FALSE   AND (isodata.gas_id in (24))   AND (volumeA3 is not NULL and atomicMass is not NULL) ORDER BY `mofs`.`id` ASC LIMIT 100;
+----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+
| id | select_type | table     | partitions | type | possible_keys                                                                                                                 | key                          | key_len | ref                            | rows  | filtered | Extra                        |
+----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+
|  1 | SIMPLE      | mofs      | NULL       | ref  | PRIMARY,index_mofs_on_hidden,index_mofs_on_volumeA3,index_mofs_on_atomicMass,index_mofs_on_volumeA3_and_atomicMass_and_hidden | index_mofs_on_hidden         | 1       | const                          | 60373 |    25.00 | Using where; Using temporary |
|  1 | SIMPLE      | isotherms | NULL       | ref  | PRIMARY,index_isotherms_on_mof_id                                                                                             | index_isotherms_on_mof_id    | 9       | mofdb2_prod_dump2.mofs.id      |     5 |   100.00 | Using index; Distinct        |
|  1 | SIMPLE      | isodata   | NULL       | ref  | index_isodata_on_isotherm_id,index_isodata_on_gas_id                                                                          | index_isodata_on_isotherm_id | 9       | mofdb2_prod_dump2.isotherms.id |     3 |    41.45 | Using where; Distinct        |
+----+-------------+-----------+------------+------+-------------------------------------------------------------------------------------------------------------------------------+------------------------------+---------+--------------------------------+-------+----------+------------------------------+

如何加快查询速度？有没有办法添加一个索引来捕捉mof->等温线->isdata.gas_id关系？例如：在postgres中，我可以创建如下所示的物化视图：

mof_id|gas_id
1|350
1|33
2|5
2|33
...

然后查询那个视图。我能用索引实现类似的目标吗？

模式

mysql> show create table mofs;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mofs  | CREATE TABLE `mofs` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `hashkey` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `database_id` bigint DEFAULT NULL,
  `cif` mediumtext,
  `void_fraction` float DEFAULT NULL,
  `surface_area_m2g` float DEFAULT NULL,
  `surface_area_m2cm3` float DEFAULT NULL,
  `pld` float DEFAULT NULL,
  `lcd` float DEFAULT NULL,
  `pxrd` text,
  `pore_size_distribution` text,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `pregen_json` json DEFAULT NULL,
  `mofid` text,
  `mofkey` text,
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  `atomicMass` float DEFAULT NULL,
  `volumeA3` float DEFAULT NULL,
  `batch_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_rails_42b2867304` (`database_id`),
  KEY `index_mofs_on_hashkey` (`hashkey`),
  KEY `index_mofs_on_name` (`name`),
  KEY `index_mofs_on_hidden` (`hidden`),
  KEY `index_mofs_on_pld` (`pld`),
  KEY `index_mofs_on_lcd` (`lcd`),
  KEY `index_mofs_on_void_fraction` (`void_fraction`),
  KEY `index_mofs_on_surface_area_m2g` (`surface_area_m2g`),
  KEY `index_mofs_on_surface_area_m2cm3` (`surface_area_m2cm3`),
  KEY `mofid_exact_match_idx` (`mofid`(768)),
  KEY `mofkey_exact_match_idx` (`mofkey`(768)),
  KEY `index_mofs_on_volumeA3` (`volumeA3`),
  KEY `index_mofs_on_atomicMass` (`atomicMass`),
  KEY `index_mofs_on_batch_id` (`batch_id`),
  KEY `index_mofs_on_volumeA3_and_atomicMass_and_hidden` (`volumeA3`,`atomicMass`,`hidden`),
  CONSTRAINT `fk_rails_42b2867304` FOREIGN KEY (`database_id`) REFERENCES `databases` (`id`),
  CONSTRAINT `fk_rails_c2906db3ef` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=167396 DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table isotherms;
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| isotherms | CREATE TABLE `isotherms` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `doi` varchar(255) DEFAULT NULL,
  `digitizer` varchar(255) DEFAULT NULL,
  `temp` float DEFAULT NULL,
  `simin` text,
  `adsorbate_forcefield_id` bigint DEFAULT NULL,
  `molecule_forcefield_id` bigint DEFAULT NULL,
  `mof_id` bigint DEFAULT NULL,
  `adsorption_units_id` bigint DEFAULT NULL,
  `pressure_units_id` bigint DEFAULT NULL,
  `composition_type_id` bigint DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `batch_id` bigint DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_isotherms_on_mof_id` (`mof_id`),
  KEY `index_isotherms_on_adsorption_units_id` (`adsorption_units_id`),
  KEY `index_isotherms_on_pressure_units_id` (`pressure_units_id`),
  KEY `index_isotherms_on_composition_type_id` (`composition_type_id`),
  KEY `fk_rails_8886e0d88b` (`adsorbate_forcefield_id`),
  KEY `fk_rails_180e64ceb3` (`molecule_forcefield_id`),
  KEY `index_isotherms_on_doi` (`doi`),
  KEY `index_isotherms_on_batch_id` (`batch_id`),
  CONSTRAINT `fk_rails_10527b19a8` FOREIGN KEY (`composition_type_id`) REFERENCES `classifications` (`id`),
  CONSTRAINT `fk_rails_180e64ceb3` FOREIGN KEY (`molecule_forcefield_id`) REFERENCES `forcefields` (`id`),
  CONSTRAINT `fk_rails_1b8cd34a98` FOREIGN KEY (`pressure_units_id`) REFERENCES `classifications` (`id`),
  CONSTRAINT `fk_rails_7931af24f5` FOREIGN KEY (`adsorption_units_id`) REFERENCES `classifications` (`id`),
  CONSTRAINT `fk_rails_8886e0d88b` FOREIGN KEY (`adsorbate_forcefield_id`) REFERENCES `forcefields` (`id`),
  CONSTRAINT `fk_rails_94b5964f6a` FOREIGN KEY (`mof_id`) REFERENCES `mofs` (`id`),
  CONSTRAINT `fk_rails_ea429d3060` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6368886 DEFAULT CHARSET=utf8 |
+-----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table isodata;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| isodata | CREATE TABLE `isodata` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `isotherm_id` bigint DEFAULT NULL,
  `gas_id` bigint DEFAULT NULL,
  `pressure` float DEFAULT NULL,
  `loading` float DEFAULT NULL,
  `bulk_composition` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_isodata_on_isotherm_id` (`isotherm_id`),
  KEY `index_isodata_on_gas_id` (`gas_id`),
  CONSTRAINT `fk_rails_279fe04b2e` FOREIGN KEY (`isotherm_id`) REFERENCES `isotherms` (`id`),
  CONSTRAINT `fk_rails_b1baf3536c` FOREIGN KEY (`gas_id`) REFERENCES `gases` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17313801 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

如果isotherms_mofs_join是一个多到多的表，那么它可能需要更好的索引；请参见多对多。