通过在查询字段中添加而不是在索引中，MYSQL索引速度更快

Question

我无法理解mysql的以下索引行为，

以下查询花费了 25 秒并返回 64776:

SELECT count(*)
FROM Analisi analisi0_
  INNER JOIN Paziente paziente3_ ON analisi0_.ID_PAZIENTE = paziente3_.ID_PAZIENTE
WHERE 1 = 1
 AND   paziente3_.nome LIKE 'MARIA%';

以下查询花费了 7 秒并始终返回 64776:

SELECT count(*)
FROM Analisi analisi0_
  INNER JOIN Paziente paziente3_ ON analisi0_.ID_PAZIENTE = paziente3_.ID_PAZIENTE
WHERE 1 = 1
 AND   paziente3_.nome LIKE 'MARIA%'
AND   ( paziente3_.DATA_NASCITA > 0  ||   paziente3_.DATA_NASCITA is NULL);

第一个慢查询的解释是:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  paziente3_      range   PRIMARY,IDX_NOME    IDX_NOME    123     178176  100 Using where; Using index
1   SIMPLE  analisi0_       ref FK_ANALISI_PAZIENTE FK_ANALISI_PAZIENTE 4   elettroforesi_light.paziente3_.ID_PAZIENTE  1   100 Using index

而第二个查询的解释是:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   SIMPLE  paziente3_      range   PRIMARY,IDX_NOME,IDX_DATA_NASCITA   IDX_NOME    123     178176  50.07   Using index condition; Using where; Using MRR
1   SIMPLE  analisi0_       ref FK_ANALISI_PAZIENTE FK_ANALISI_PAZIENTE 4   elettroforesi_light.paziente3_.ID_PAZIENTE  1   100 Using index

PAZIENTE 表上的索引:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
paziente    0   PRIMARY 1   ID_PAZIENTE A   1404534             BTREE       
paziente    1   IDX_COGNOME 1   COGNOME A   161711          YES BTREE       
paziente    1   IDX_NOME    1   NOME    A   169702          YES BTREE       
paziente    1   IDX_DATA_NASCITA    1   DATA_NASCITA    A   15513           YES BTREE       
paziente    1   IDX_SESSO   1   ID_SESSO    A   1               BTREE       
paziente    1   FK_PAZIENTE_SPECIE  1   ID_SPECIE   A   1               BTREE       

表 ANALISI 上的索引:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
analisi 0   PRIMARY 1   ID_ANALISI  A   1454102             BTREE       
analisi 1   FK_ANALISI_PAZIENTE 1   ID_PAZIENTE A   1454102             BTREE       
analisi 1   FK_ANALISI_SESSIONE 1   ID_SESSIONE A   56498               BTREE       
analisi 1   FK_ANALISI_PATOLOGICO   1   ID_PATOLOGICO   A   1           YES BTREE       
analisi 1   FK_ANALISI_TIPO_VALIDAZIONE 1   ID_TIPO_VALIDAZIONE A   1               BTREE       
analisi 1   FK_ANALISI_UTENTE_MODIFICA  1   ID_UTENTE_MODIFICA  A   1           YES BTREE       
analisi 1   FK_ANALISI_UTENTE_VALIDAZIONE   1   ID_UTENTE_VALIDAZIONE   A   1           YES BTREE       
analisi 1   FK_ANALISI_UTENTE_CANCELLAZIONE 1   ID_UTENTE_CANCELLAZIONE A   1           YES BTREE       
analisi 1   FK_ANALISI_ANALISI_LINK 1   ID_ANALISI_LINK A   1           YES BTREE       
analisi 1   FK_ANALISI_ANALISI_IFE  1   ID_ANALISI_IFE  A   227         YES BTREE       
analisi 1   FK_ANALISI_ANALISI_NOTA 1   ID_ANALISI_NOTA A   17132           YES BTREE       
analisi 1   FK_ANALISI_ANALISI_DATI 1   ID_ANALISI_DATI A   1454102         YES BTREE       
analisi 1   IDX_CAMPIONE    1   CAMPIONE    A   102648          YES BTREE       
analisi 1   IDX_REPARTO 1   REPARTO A   132         YES BTREE   

我真的不明白为什么如果两个都使用 IDX_NOME 并且 IDX_NOME 不包含 DATA_NASCITA 结果会更好。

Answer 1

由于 Like 是 mysql 中代价高昂的操作，因此在第二个查询中，查询上有更多过滤器，因此首先执行过滤器，这会提供较少数量的结果，然后like 应用于这些结果。

由于在第一个查询中，第一个查询过滤器后的结果可能包含更多数量的记录，当您添加时，这些记录会在第二个查询中减少

( paziente3_.DATA_NASCITA > 0  ||   paziente3_.DATA_NASCITA is NULL);

作为查询的AND过滤器。