MySQL单表SQL查询优化：慢查询问题技术求助

Hey there, let's dig into why your query is running so slow and how to fix it. First, let's recap your scenario clearly:

Your Problem Query #

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';

Fetching just 95 rows shouldn't take 10 seconds—this is almost certainly an indexing inefficiency issue.

Key Issue with Your Current Index Setup #

From the partial SHOW INDEXES output you shared, it’s likely you don’t have a targeted composite index that aligns with your query’s filter logic. MySQL can only efficiently use one index per table in most cases, so single-column indexes on individual fields (like dateFechaHora or RFC_usuario) won’t cut it here—they’ll force MySQL to either do a full table scan or a partial index scan followed by expensive row lookups (回表操作).

Step-by-Step Fixes #

1. Create an Optimized Composite Index

The golden rule for composite indexes is: put equality conditions first, then range conditions (since range conditions break the left-prefix rule for subsequent columns). Your equality filters are RFC_usuario, intTimbrada, intCancelada, cfdi_33—these should come first, followed by the range on dateFechaHora.

Run this to create the index:

CREATE INDEX idx_factura_rfc_status_date ON tbl_factura (
  RFC_usuario, 
  intTimbrada, 
  intCancelada, 
  cfdi_33, 
  dateFechaHora
);

Why this order? Equality conditions let MySQL narrow down the result set to a tiny subset first, then the range on dateFechaHora filters that subset quickly. Also, RFC_usuario has higher selectivity (fewer rows per value) than the boolean int fields, so putting it first maximizes index efficiency.

2. Verify the Index is Used with EXPLAIN

Always confirm the index is being picked up by checking the execution plan. Run:

EXPLAIN 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';

Look for these critical signs in the output:

• type should be range or ref (not ALL, which means a full table scan)
• key should show idx_factura_rfc_status_date
• rows should be close to 95 (this is MySQL’s estimate of rows it needs to scan)

3. Optional: Use a Covering Index for Even Better Performance

If you don’t actually need every column (SELECT *), replace * with only the columns you need, and add those columns to the end of the index to make it a covering index. This avoids "table lookups" (fetching rows from the main table after using the index):

-- Example: if you only need id, total, and fecha columns
CREATE INDEX idx_factura_covering ON tbl_factura (
  RFC_usuario, 
  intTimbrada, 
  intCancelada, 
  cfdi_33, 
  dateFechaHora,
  id, total
);

4. Check for Implicit Type Conversions

Make sure dateFechaHora is a DATE or DATETIME type, not a string. If it’s stored as a string, MySQL can’t use the index efficiently because it has to convert every value to compare with your date strings.

Quick Additional Checks #

• Run ANALYZE TABLE tbl_factura; to update MySQL’s table statistics, which helps it choose the best index.
• Ensure your MySQL server has enough memory allocated to the buffer pool (this reduces disk I/O for index and table lookups).

内容的提问来源于stack exchange，提问作者Ashe