mysql - MySQL 对约 400.000 个条目的查询速度缓慢

Question

我有以下查询，速度非常慢(2.9 seg):

SELECT post_id
FROM   ap_props
   LEFT JOIN ap_moneda
          ON ( ap_props.rela_moneda = ap_moneda.id_moneda )
   LEFT JOIN wp_posts
          ON ( ap_props.post_id = wp_posts.id )
WHERE  1 = 1
   AND wp_posts.post_status = "publish"
   AND rela_inmuebleoper = "2"
   AND rela_inmuebletipo = "1"
   AND (( approps_precio * Ifnull(moneda_valor, 0) >= 2000
          AND approps_precio * Ifnull(moneda_valor, 0) <= 6000 ))
   AND rela_barrio IN ( 6, 23085, 23086, 23087,
                        7, 23088, 23089, 23090,
                        23091, 23092, 26, 23115,
                        23116, 23117, 23118, 23119,
                        23120, 32, 43, 23123,
                        23124, 23125 )
   AND ( post_id IS NOT NULL );  

2.90808200

分析显示:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000132 |
| checking query cache for query | 0.000135 |
| Opening tables                 | 0.000023 |
| System lock                    | 0.000009 |
| Table lock                     | 0.000033 |
| init                           | 0.000074 |
| optimizing                     | 0.000030 |
| statistics                     | 0.001989 |
| preparing                      | 0.000028 |
| executing                      | 0.000007 |
| Sending data                   | 2.905463 |
| end                            | 0.000015 |
| query end                      | 0.000005 |
| freeing items                  | 0.000055 |
| storing result in query cache  | 0.000013 |
| logging slow query             | 0.000009 |
| logging slow query             | 0.000055 |
| cleaning up                    | 0.000007 |
+--------------------------------+----------+

以及解释:

+----+-------------+-----------+-------------+---------------------------------------------------------------------+-------------------------------------------+---------+---------------------------+-------+-------------------------------------------------------------------------+
| id | select_type | table     | type        | possible_keys                                                        | key                                       | key_len | ref                       | rows  | Extra                                                                   |
+----+-------------+-----------+-------------+----------------------------------------------------------------------+-------------------------------------------+---------+---------------------------+-------+-------------------------------------------------------------------------+
|  1 | SIMPLE      | ap_props  | index_merge | idx_post_id,idx_relabarrio,idx_relainmuebleoper,idx_relainmuebletipo | idx_relainmuebleoper,idx_relainmuebletipo | 5,5     | NULL                      | 58114 | Using intersect(idx_relainmuebleoper,idx_relainmuebletipo); Using where |
|  1 | SIMPLE      | ap_moneda | ALL         | NULL                                                                 | NULL                                      | NULL    | NULL                      |     3 | Using where                                                             |
|  1 | SIMPLE      | wp_posts  | eq_ref      | PRIMARY                                                              | PRIMARY                                   | 8       | metaprop.ap_props.post_id |     1 | Using where                                                             |
+----+-------------+-----------+-------------+----------------------------------------------------------------------+-------------------------------------------+---------+---------------------------+-------+-------------------------------------------------------------------------+

关于如何改进它有什么想法吗？ ap_props 和 wp-posts 中的条目数量总计约为 400.000。 ap_moneda 只有 5 个条目。

我尝试删除 IN 子句，但以下显示了相同的性能结果:

SELECT post_id from ap_props left join ap_moneda on (ap_props.rela_moneda = ap_moneda.id_moneda) left join wp_posts on (ap_props.post_id = wp_posts.ID) where 1=1 AND wp_posts.post_status = "publish" AND rela_inmuebleoper = "2" AND rela_inmuebletipo = "1" AND ( ( approps_precio * ifnull(moneda_valor,0) >= 2000 AND approps_precio * ifnull(moneda_valor,0) <= 6000) ) AND (rela_barrio=6 OR rela_barrio=23085 OR rela_barrio=23086 OR rela_barrio=23087 OR rela_barrio=7 OR rela_barrio=23088 OR rela_barrio=23089 OR rela_barrio=23090 OR rela_barrio=23091 OR rela_barrio=23092 OR rela_barrio=26 OR rela_barrio=23115 OR rela_barrio=23116 OR rela_barrio=23117 OR rela_barrio=23118 OR rela_barrio=23119 OR rela_barrio=23120 OR rela_barrio=32 OR rela_barrio=43 OR rela_barrio=23123 OR rela_barrio=23124 OR rela_barrio=23125)  AND (post_id IS NOT NULL);

2.91080400

非常感谢您的帮助!

编辑:

当前索引为:

+----------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+
| ap_props |          0 | PRIMARY              |            1 | approps_origen    | A         |          10 |     NULL | NULL   |      | BTREE      |         |
| ap_props |          0 | PRIMARY              |            2 | approps_id_aviso  | A         |      452098 |     NULL | NULL   |      | BTREE      |         |
| ap_props |          1 | idx_status           |            1 | approps_status_db | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| ap_props |          1 | idx_fecha            |            1 | approps_fecha     | A         |       64585 |     NULL | NULL   | YES  | BTREE      |         |
| ap_props |          1 | idx_post_id          |            1 | post_id           | A         |      452098 |     NULL | NULL   | YES  | BTREE      |         |
| ap_props |          1 | idx_relabarrio       |            1 | rela_barrio       | A         |        2457 |     NULL | NULL   | YES  | BTREE      |         |
| ap_props |          1 | idx_relainmuebleoper |            1 | rela_inmuebleoper | A         |           6 |     NULL | NULL   | YES  | BTREE      |         |
| ap_props |          1 | idx_relainmuebletipo |            1 | rela_inmuebletipo | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |
+----------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+

仅供引用，F 通过添加新索引 idx_appprops_precio 并通过添加“使用索引 (idx_relabarrio,idx_appprops_precio)”强制两者来修复此问题

Answer 1

如果在连接表时使用 AND 而不是先连接然后过滤结果集会怎样

尝试一下

SELECT post_id
FROM   ap_props
   LEFT JOIN ap_moneda
          ON ( ap_props.rela_moneda = ap_moneda.id_moneda  AND `table`.rela_inmuebleoper = "2"   AND `table`.rela_inmuebletipo = "1" )
   LEFT JOIN wp_posts
          ON ( ap_props.post_id = wp_posts.id  AND wp_posts.post_status = "publish")
WHERE  rela_barrio IN ( 6, 23085, 23086, 23087,
                        7, 23088, 23089, 23090,
                        23091, 23092, 26, 23115,
                        23116, 23117, 23118, 23119,
                        23120, 32, 43, 23123,
                        23124, 23125 )      
   AND (( approps_precio * Ifnull(moneda_valor, 0) >= 2000
          AND approps_precio * Ifnull(moneda_valor, 0) <= 6000 ))

   AND ( post_id IS NOT NULL );

我已将这两个条件放在连接中，不确定表名称，因此您应该注意它 table.rela_inmuebleoper = "2"AND table.rela_inmuebletipo = "1" 提供正确的表名。并检查并为列建立适当的索引