mysql - MySQL 中的 SQL 查询耗时过长
问题描述
我的一个查询有问题，这个查询查找记录的时间太长，有时发送数据的时间太长，有时对结果进行排序我说的是 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 步：删除任何在 dateFechaHora. 当 中有一个范围时WHERE，在对该列进行测试后停止使用索引。
第 2 步：INDEX(a)如果你有INDEX(a,b). 将此“规则”扩展到任何前缀。
对于此特定查询，以下是最佳索引：
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更复杂；这里没有介绍。）- 如果您确定没有重叠，则连接
SELECTsby ，或者（较慢）如果可能存在重复。UNION ALLUNION DISTINCT - 使用括号，这样当你
ORDER BY添加到结尾时，它显然属于UNION，而不是最后SELECT。 - 根据我的 Cookbook 中的相同规则创建 2 个新索引，一个针对每个
SELECT.
但是...... UNION有一些开销，所以它可能会也可能不会加快查询速度。（我想这对这个查询没有好处。）
推荐阅读
- string - 给定一个由 0 和 1 组成的圆形字符串，求最小编号。将所有 1 放在一起的相邻掉期
- flutter - 错误：参数类型“空函数（日期时间，列表
)' 不能分配给参数 - arrays - C - 访问函数中的变量以在用户退出程序时打印总和
- sql-server - 仅用于历史更改的 SQL Server 时态表
- node.js - Mongodb查找文档值是否作为子字符串存在
- android - 使用 LockTaskMode 无法触发 NFC 读取意图
- centos7 - CentOS7 无法安装 pdo_mysql 扩展
- networking - 带有“nmcli”的接入点
- spring - 我可以为每个 @Async 方法调用使用不同的执行器吗？
- javascript - 向特定用户发送 Signalr 消息