mysql - 解释慢查询

Question

我们有下表

CREATE TABLE variant
(
 id                         VARCHAR(105),
 chrom                      VARCHAR(12),
 condel_pred                VARCHAR(11),
 consequence                VARCHAR(97),
 dbsnp_id                   VARCHAR(23),
 most_del_score             INTEGER,
 pos                        INTEGER,
 polyphen_pred              VARCHAR(17),
 protein_change             VARCHAR(39),
 sift_pred                  VARCHAR(11),
 _13k_t2d_aa_mac            INTEGER,
 _13k_t2d_aa_maf            FLOAT,
 _13k_t2d_aa_mina           INTEGER,
 _13k_t2d_aa_minu           INTEGER,
 _13k_t2d_ea_mac            INTEGER,
 _13k_t2d_ea_maf            FLOAT,
 _13k_t2d_ea_mina           INTEGER,
 _13k_t2d_ea_minu           INTEGER,
 _13k_t2d_eu_mac            INTEGER,
 _13k_t2d_eu_maf            FLOAT,
 _13k_t2d_eu_mina           INTEGER,
 _13k_t2d_eu_minu           INTEGER,
 _13k_t2d_het_carriers      VARCHAR(4),
 _13k_t2d_het_ethnicities   VARCHAR(32),
 _13k_t2d_hom_carriers      VARCHAR(5),
 _13k_t2d_hom_ethnicities   VARCHAR(32),
 _13k_t2d_hs_mac            INTEGER,
 _13k_t2d_hs_maf            FLOAT,
 _13k_t2d_hs_mina           INTEGER,
 _13k_t2d_hs_minu           INTEGER,
 _13k_t2d_sa_mac            INTEGER,
 _13k_t2d_sa_maf            FLOAT,
 _13k_t2d_sa_mina           INTEGER,
 _13k_t2d_sa_minu           INTEGER,
 closest_gene               VARCHAR(16),
 exchp_t2d_beta             FLOAT,
 exchp_t2d_direction        VARCHAR(13),
 exchp_t2d_maf              FLOAT,
 exchp_t2d_neff             FLOAT,
 exchp_t2d_p_value          FLOAT,
 gene                       VARCHAR(20),
 in_exchp                   VARCHAR(1),
 in_exseq                   VARCHAR(1),
 in_gene                    VARCHAR(17),
 qcfail                     INTEGER,
 _13k_t2d_heta              INTEGER,
 _13k_t2d_hetu              INTEGER,
 _13k_t2d_homa              INTEGER,
 _13k_t2d_homu              INTEGER,
 _13k_t2d_mac               INTEGER,
 _13k_t2d_mina              INTEGER,
 _13k_t2d_minu              INTEGER,
 _13k_t2d_or_wald_dos_fe_iv FLOAT,
 _13k_t2d_p_emmax_fe_iv     FLOAT,
 _13k_t2d_transcript_annot  VARCHAR(10745),
 gwas_t2d_effect_allele     VARCHAR(1),
 gwas_t2d_or                FLOAT,
 gwas_t2d_pvalue            FLOAT,
 gws_traits                 VARCHAR(43),
 in_gwas                    VARCHAR(1),
 _13k_t2d_aa_eaf            FLOAT,
 _13k_t2d_ea_eaf            FLOAT,
 _13k_t2d_sa_eaf            FLOAT
 )                                                                    

有几个索引，但包括

 GWAS_T2D_PVAL_MOST_DEL_13k_T2D_EA_MAF_IDX   

(GWAS_T2D_PVALUE, MOST_DEL_SCORE, _13k_T2D_EA_MAF)

大约有 600 万行，包含大量 NULL 数据，GWAS_T2D_PVALUE 和 MOST_DEL_SCORE 对于相对较少的行(~40k 行)一起为非空).

我们正在观察运行以下查询时我们不了解的性能

SELECT * 
FROM VARIANT USE INDEX GWAS_T2D_PVAL_MOST_DEL_13k_T2D_EA_MAF_IDX) 
WHERE GWAS_T2D_PVALUE < .05 AND MOST_DEL_SCORE = 1;                      

其中有以下说明:

+----+-------------+---------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-------------+                                                                        

| id | select_type | table   | type  | possible_keys                             | key                                       | key_len | ref  | rows   | Extra       |                                                                        

+----+-------------+---------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-------------+                                                                        

|  1 | SIMPLE      | VARIANT | range | GWAS_T2D_PVAL_MOST_DEL_13k_T2D_EA_MAF_IDX | GWAS_T2D_PVAL_MOST_DEL_13k_T2D_EA_MAF_IDX | 10      | NULL | 280242 | Using where |                                                                        

+----+-------------+---------+-------+-------------------------------------------+-------------------------------------------+---------+------+--------+-------------+                                                                        

发生的情况是，如果有一段时间(例如 8 小时)没有运行查询，则查询需要很长时间才能执行(约 3 分钟)，但之后需要 <1 秒并返回 8 行。我们有两个问题:

1. 为什么第一个查询执行需要这么长时间？我们假设这是因为某些操作系统缓存或分页问题，​​因为查询缓存已关闭，并且密切相关的查询(例如将 0.05 替换为 0.1)第二次运行速度也很快。

2. 为什么即使没有缓存并且每个页面都提取到磁盘，这个查询也需要大约 3 分钟？它只返回 8 行，并且索引不应该能够直接查找这 8 行，因为前两个键位于 where 子句中的两个键上吗？为什么解释估计扫描了 280K 行而不是 8 行？我们对表进行了优化，估计还是一样。 同样令人困惑的是，单独强制使用 GWAS_T2D_PVALUE 上的索引时的解释会产生扫描 44K 行的估计，而 (GWAS_T2D_PVALUE, MOST_DEL_SCORE) 上的索引产生并估计扫描的 32K 行。根据我们对多列索引的理解，为什么 2 列索引和 3 列索引的查询性能会有所不同，而不应该都远优于 1 列索引？

Answer 1

根据查询，索引中的列是向后的，这就是您看到 using where 的原因在查询计划中。

为了引用一个陈旧的例子，让我们考虑一下电话簿。

您的查询是WHERE last_name < 'smith' AND first_name = 'john' .

名字在每个排序的姓氏组中排序的事实没有实际值(value)，因为我们仍然必须考虑大部分目录中的所有人(Smith 之前的每个人)并评估他们的第一个在每个不同的姓氏中分别命名。这就是您的行估计值如此之大的原因。

如果两个表达式都是相等比较，服务器确实可以直接转到第 8 行。如果索引中最左边的列进行相等比较，而第二列进行“小于”比较，服务器可以再次直接转到有问题的行，因为它们在索引中都是相邻的。

两个列的顺序相反的索引很可能会产生截然不同的性能。

一般来说，using where用key possible_keys 中的值还显示意味着索引正在帮助一些，但服务器仍然必须评估索引找到的内容并使用 where 子句中的表达式消除其他行。

相同查询的更快响应可能是查询缓存的作用。对类似查询的更快响应可能意味着您的 innodb_buffer_pool_size对于您的工作负载来说太小了，并且由于缺少最佳索引而需要的所有随机读取意味着在第一次执行时大量页面从磁盘加载到池中。