>我有这个问题,我正在进行此查询,但花费的时间太长,例如 10 秒才能找到 95 条记录
SELECT * FROM tbl_factura WHERE dateFechaHora >= '2018-04-01' AND dateFechaHora <= '2018-04-30' AND intTimbrada = 1 AND intCancelada = 0 AND cfdi_33 = 1 AND RFC_usuario = 'FRANCISCOI10'
mysql> show indexes from tbl_factura;
+-------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_factura | 0 | PRIMARY | 1 | idFactura | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_idContador | 1 | idContador | A | 3875 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_dateFechaHora | 1 | dateFechaHora | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_intTimbrada | 1 | intTimbrada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_intCancelada | 1 | intCancelada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_idContadorSub | 1 | idContadorSub | A | 113 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_intFolio | 1 | intFolio | A | 21139 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_formaPago | 1 | strFormaPago | A | 12 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_email_enviado | 1 | email_enviado | A | 7 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_cfdi33 | 1 | cfdi_33 | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_intStatus | 1 | intStatus | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_status | 1 | intStatus | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_serie | 1 | strSerie | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_rfc_usuario | 1 | RFC_usuario | A | 1875 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_facturaexcel | 1 | facturaExcel | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_razonSocial | 1 | CH_razon_social | A | 58133 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_nombreComercial | 1 | CH_nombre_comercial | A | 2397 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_RFC | 1 | CH_RFC | A | 38755 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto1 | 1 | idFactura | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto1 | 2 | idContador | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto1 | 3 | cfdi_33 | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto2 | 1 | idContador | A | 2835 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto2 | 2 | cfdi_33 | A | 4471 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto3 | 1 | dateFechaHora | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto3 | 2 | intTimbrada | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto3 | 3 | intCancelada | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto3 | 4 | cfdi_33 | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto3 | 5 | RFC_usuario | A | 232534 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto4 | 1 | RFC_usuario | A | 3633 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto4 | 2 | intTimbrada | A | 4306 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto4 | 3 | intCancelada | A | 6459 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_compuesto4 | 4 | cfdi_33 | A | 9301 | NULL | NULL | | BTREE | |
| tbl_factura | 1 | idx_folio_fiscal | 1 | strFolioFiscal | A | 232534 | NULL | NULL | | BTREE | |
+-------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+
这个表大约有 254,809 条记录,我们有更大的表和更复杂的查询,它们不需要很长时间,这是查询的解释
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tbl_factura
type: ref
possible_keys: idx_dateFechaHora,idx_intTimbrada,idx_intCancelada,idx_cfdi33,idx_rfc_usuario,idx_compuesto3,idx_compuesto4
key: idx_compuesto4
key_len: 64
ref: const,const,const,const
rows: 493
Extra: Using where
1 row in set (0.45 sec)
所以我想知道您是否可以在这些数据上看到不好的东西,并在可能的情况下帮助我找到更好的方法来做到这一点,这个查询的索引有问题吗?我希望你能帮助我,谢谢。
感兴趣的是dateFechaHora
、intTimbrada
、intTimbrada
、cfdi_33
和RFC_usario
列,因为它们用于查询的WHERE
。 总的来说,idx_compuesto4
看起来不错。但尝试也包括dateFechaHora
。
假设统计信息是最近的(以确保ANALYZE TABLE tbl_factura;
运行(,索引列表中各个列上的索引表明,dateFechaHora
(大致(存在232534不同的值。对于intTimbrada
,intTimbrada
和cfdi_33
,每个都是5。对于RFC_usario
,有 1875 个不同的值。
进一步假设,每列的所有值或多或少都是"均匀分布的"(即对于列x
表中有n个不同的值,并且由x
诱导的所有分区都具有相似的基数(,intTimbrada
,intTimbrada
和cfdi_33
不是很有选择性。因此,如果我们将它们包含在复合索引中,它们应该排在最后。
对于dateFechaHora
来说,这并不容易,因为查询会要求一个范围。但是,升序索引有助于将记录集快速划分为值低于最小值的记录和值高于范围的记录。看着你的查询,我注意到,你想要上个月到期的发票(我猜这就是那个表的内容?(。其他猜测是,这是通常的情况(随着时间的流逝,月份会"滚动"(,并且您过去到期的发票很多,但将来的发票相对较少。也就是说,大于范围最大值的截止日期数远远小于小于其最小值的到期日期数。换句话说,对到期日大于最小范围的发票进行分区,我们可以从索引中快速获得,并过滤掉到期日大于最大值的少数发票并不是一个太糟糕的方法。
但是我对dateFechaHora
有点问题.我注意到,idx_dateFechaHora
的基数等于PRIMARY
的基数。所以我想dateFechaHora
是一个datetime
,即使当天到期的发票dateFechaHora
也有所不同。这让我有点头疼那个232534在不同的月份会是什么,所以我有点不确定dateFechaHora
和RFC_usario
的顺序。我猜更有可能的是,你的用户(RFC_usario
是用户,对吧?(,而不是你做生意的几个月。所以我想dateFechaHora
应该先走。然后RFC_usario
,因为它的选择性比其他的要好得多,其次是其他的。
CREATE INDEX idx_compuesto5
ON (dateFechaHora,
RFC_usuario,
intTimbrada,
intCancelada,
cfdi_33);
你也可以尝试互换dateFechaHora
和RFC_usuario
的顺序,如果这样更好。正如我上面所说,我不太确定。
包括相对"坏"的列intTimbrada
、intCancelada
和cfdi_33
的缺点是使索引更大。也就是说,它们可以在内存中的索引拟合(很大程度上(与相反的拟合之间产生差异。因此,如果没有它们,磁盘 IO 总体上可能会更少,这当然更好。所以也许你想尝试把他们踢出去,如果是这样的话。
当然,这一切都可能是错误的,因为我对数据一无所知。例如,可能只有很少的记录cfdi_33 = 1
.这可能会使cfdi_33
非常好。如果MySQL的优化器可以在该级别上播放。我不知道。您可能也想考虑一下。