MySQL中的SQL查询耗时太长。
MySQL中的SQL查询耗时太长。
提问于 2018-06-09 00:10:13
我有一个查询的问题，这个查询查找记录花费的时间太长，有时发送数据和对结果排序的时间也太长，我说的是10到20秒，这个表不是很大，我有更大的表，有数百万条记录，与它们相比，这个表很小
SELECT idFactura,
strNombre,
intFolio,
CH_razon_social AS strRazonSocial,
CH_nombre_comercial AS strNombreComercial,
CH_RFC AS strRFC,
doubleTotal,
intTimbrada,
intCancelada,
dateFechaHora,
intStatus,
strSerie,
email_enviado,
strFolioFiscal,
numero_abonos,
saldoInsoluto_pagos,
tipo_comprobante,
strSerieParc
FROM tbl_factura2
WHERE dateFechaHora >= '2018-05-06'
AND dateFechaHora <= '2018-06-06'
AND intTimbrada = 1
AND intCancelada = 0
AND cfdi_33 = 1
AND RFC_usuario = 'NUSN900420SS5'
AND numero_abonos = 0
AND (strFormaPago = 'PPD'
OR strMetodoPago = '99')
ORDER
BY idFactura DESC;当我使用命令EXPLAIN时，这就是我得到的结果，你可以看到它正在使用idx_compuesto7索引，但有时它需要另一个索引，我知道这取决于哪个MySQL更适合查询，我想知道我可以更改什么或以另一种方式执行此查询以获得更好的性能，我知道查询中的OR可能有问题，但我尝试将查询作为两个不同的查询来删除OR，但我得到了相同的问题。
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl_factura2 type: ref possible_keys: idx_fecha,idx_timbrada,idx_cancelada,idx_formaPago,idx_cfdi33,idx_rfc_usuario,idx_strMetodoPago,idx_compuesto3,idx_compuesto4,idx_compuesto5,idx_compuesto6,idx_compuesto7,idx_compuesto8,idx_compuesto9,idx_compuesto10,idx_compuesto11,idx_compuesto12 key: idx_compuesto7 key_len: 52 ref: const rows: 40 Extra: Using where; Using filesort
正如您所看到的，可能的行数是40 records，但对于这个查询，当我运行它时，我得到的是1 record，我不知道这是不是可以接受的，或者不考虑整个表的总记录数
这是该表的整个索引的定义
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| tbl_factura2 | 0 | PRIMARY | 1 | idFactura | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_contador | 1 | idContador | A | 2248 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_fecha | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_timbrada | 1 | intTimbrada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_cancelada | 1 | intCancelada | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_contadorSub | 1 | idContadorSub | A | 131 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_folio | 1 | intFolio | A | 1578 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_formaPago | 1 | strFormaPago | A | 13 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_email_enviado | 1 | email_enviado | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_cfdi33 | 1 | cfdi_33 | A | 59 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_status | 1 | intStatus | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_serie | 1 | strSerie | A | 538 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_rfc_usuario | 1 | RFC_usuario | A | 17991 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_facturaexcel | 1 | facturaExcel | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_razonSocial | 1 | CH_razon_social | A | 33734 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_nombreComercial | 1 | CH_nombre_comercial | A | 2620 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_RFC | 1 | CH_RFC | A | 67469 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_folio_fiscal | 1 | strFolioFiscal | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_saldoInsoluto | 1 | saldoInsoluto_pagos | A | 7 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_tipo_comprobante | 1 | tipo_comprobante | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_existe_facprincipal | 1 | existe_facturaPrinc_pagos | A | 5 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_strMetodoPago | 1 | strMetodoPago | A | 7 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 1 | idFactura | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 2 | idContador | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto1 | 3 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto2 | 1 | idContador | A | 2306 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto2 | 2 | cfdi_33 | A | 3551 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto3 | 5 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 1 | RFC_usuario | A | 4819 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 2 | intTimbrada | A | 5997 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 3 | intCancelada | A | 8705 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto4 | 4 | cfdi_33 | A | 11244 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 2 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto5 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 1 | RFC_usuario | A | 3551 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto6 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 1 | RFC_usuario | A | 8995 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 7 | saldoInsoluto_pagos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto7 | 8 | tipo_comprobante | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 2 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 6 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto8 | 7 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 5 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 6 | strFormaPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto9 | 7 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 1 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 2 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 3 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 4 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 5 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 6 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto10 | 7 | RFC_usuario | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 1 | RFC_usuario | A | 3696 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto11 | 7 | strFormaPago | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 1 | RFC_usuario | A | 12851 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 2 | dateFechaHora | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 3 | intTimbrada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 4 | intCancelada | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 5 | cfdi_33 | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 6 | numero_abonos | A | 269877 | NULL | NULL | | BTREE | |
| tbl_factura2 | 1 | idx_compuesto12 | 7 | strMetodoPago | A | 269877 | NULL | NULL | | BTREE | |
+--------------+------------+-------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+对我来说，查询没有采用正确的索引，或者我可能需要创建另一个复合索引来修复这个问题。
这是查询中使用的字段的不同值的数量。如果此信息对某人有用，您是否发现该信息和查询方式有问题？我希望你能帮助我改进这一点，谢谢。
*************************** 1. row *************************** count(distinct dateFechaHora): 260117 count(distinct intTimbrada): 2 count(distinct intCancelada): 2 count(distinct cfdi_33): 2 count(distinct RFC_usuario): 3621 count(distinct numero_abonos): 9 count(distinct strFormaPago): 19 count(distinct strMetodoPago): 161 total_records: 263320
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-06-09 04:44:15
步骤1:删除所有列在dateFechaHora之后的索引。如果WHERE中存在范围，则在对该列进行测试后将停止使用索引。
第二步:如果你有INDEX(a,b)，那就去掉INDEX(a)。将这条“规则”扩展到任何前缀。
对于这个特定的查询，下面是最佳索引：
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, -- in any order
dateFechaHora) -- last参见。
至于删除OR，这可能会有所帮助：
(
SELECT ...
FROM tbl_factura2
WHERE ...
AND strFormaPago = 'PPD'
)
UNION DISTINCT
(
SELECT ...
FROM tbl_factura2
WHERE ...
AND strMetodoPago = '99'
)
ORDER BY idFactura DESC;加上这两个索引：
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, strFormaPago,
dateFechaHora) -- last
INDEX(intTimbrada, intCancelada, cfdi_33, RFC_usuario, numero_abonos, strMetodoPago,
dateFechaHora) -- last作为一条规则:如果你有一个妨碍性能的OR，
- 制作2个
SELECTs，将OR的一端放入其中； - 删除
ORDER BY。(LIMIT更为复杂；如果您确定不存在重叠，则不覆盖SELECTsbyUNION ALL；如果可能存在dups，则使用UNION DISTINCT(速度较慢)。 - 使用括号，因此当您将
ORDER BY添加到末尾时，它显然属于UNION，而不是最后的SELECT. - Make 2根据我的Cookbook中的相同规则，针对每个dups的新索引
但是..。UNION有一些开销，因此它可能会也可能不会加速查询。(我猜这对这个查询没有好处。)
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50764602
复制相关文章
相似问题