Mysql查询优化-范围搜索

Question

希望找到一些关于如何优化此查询的想法 - 多个范围搜索非常尴尬

SELECT t1.id, t1.custom_track_id, t1.deleted, t1.last_modified, t1.client_id
FROM tracks t1
INNER JOIN tracks t2 ON t1.custom_track_id = t2.custom_track_id
AND t1.last_modified > t2.last_modified
AND t1.deleted !=0
AND t2.deleted =0
AND t2.client_id
IN ( 103, 115, 116 ) 
WHERE t1.client_id
IN ( 103, 115, 116 ) 

它要查找和连接的所有字段都是 INT 字段。

索引(是的，里面有一些不可靠的索引):

+--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                                       | Seq_in_index | Column_name                | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tracks |          0 | PRIMARY                                        |            1 | id                         | A         |      566045 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | client_id                                      |            1 | client_id                  | A         |         589 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | featured                                       |            1 | featured                   | A         |          16 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | system_status                                  |            1 | system_status              | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | created_by                                     |            1 | created_by                 | A         |         225 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | custom_track_id                                |            1 | custom_track_id            | A         |      283022 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | custom_track_id                                |            2 | custom_artist_id           | A         |      283022 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | counterpoint_id                                |            1 | counterpoint_id            | A         |      113209 |     NULL | NULL   | YES  | BTREE      |         |               |
| tracks |          1 | system_status_2                                |            1 | system_status              | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | composition                                    |            1 | composition                | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| tracks |          1 | published_start_date                           |            1 | published_start_date       | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| tracks |          1 | published_end_date                             |            1 | published_end_date         | A         |          13 |     NULL | NULL   | YES  | BTREE      |         |               |
| tracks |          1 | deleted                                        |            1 | deleted                    | A         |          20 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | restricted_access_required                     |            1 | restricted_access_required | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            1 | client_id                  | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            2 | custom_track_id            | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            3 | deleted                    | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_deleted_featured_restrictedaccess  |            4 | last_modified              | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            1 | client_id                  | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            2 | custom_track_id            | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            3 | deleted                    | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
| tracks |          1 | mv_clientid_customtrackid_deleted_lastmodified |            4 | last_modified              | A         |         336 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+------------------------------------------------+--------------+----------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

解释:

---+---------+-------------------------------------------------+-------+--------------------------+
| id | select_type | table | type  | possible_keys                                                                                                                  | key                                           | key_len | ref                                             | rows  | Extra                    |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------------+-------+--------------------------+
|  1 | SIMPLE      | t1    | range | client_id,custom_track_id,deleted,mv_clientid_deleted_featured_restrictedaccess,mv_clientid_customtrackid_deleted_lastmodified | mv_clientid_deleted_featured_restrictedaccess | 4       | NULL                                            | 16018 | Using where; Using index |
|  1 | SIMPLE      | t2    | ref   | client_id,custom_track_id,deleted,mv_clientid_deleted_featured_restrictedaccess,mv_clientid_customtrackid_deleted_lastmodified | custom_track_id                               | 302     | synchtank_shared_application.t1.custom_track_id |     2 | Using where              |
+----+-------------+-------+-------+--------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------+---------+-------------------------------------------------+-------+--------------------------+

因此正在寻找优化查询或索引的方法。我也很好奇为什么它可以匹配的 2 个复合索引中，它选择了其中没有 last_modified 的索引。

向斯蒂芬解释一下:

+----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+
| id | select_type | table | type  | possible_keys                                 | key                                           | key_len | ref  | rows  | Extra                                       |
+----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+
|  1 | SIMPLE      | t1    | range | client_id                                     | client_id                                     | 4       | NULL |  8111 | Using where                                 |
|  1 | SIMPLE      | t2    | range | mv_clientid_deleted_featured_restrictedaccess | mv_clientid_deleted_featured_restrictedaccess | 4       | NULL | 16018 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+-----------------------------------------------+-----------------------------------------------+---------+------+-------+---------------------------------------------+

Answer 1

你可以尝试强制mysql使用索引:

SELECT 
    t1.id, 
    t1.custom_track_id, 
    t1.deleted, 
    t1.last_modified, 
    t1.client_id
FROM 
    tracks t1 FORCE INDEX(`client_id`)
INNER JOIN tracks t2 FORCE INDEX(`mv_clientid_deleted_featured_restrictedaccess`)
    ON t1.custom_track_id = t2.custom_track_id
    AND t1.last_modified > t2.last_modified 
    AND t2.deleted = 0
    AND t2.client_id IN ( 103, 115, 116 ) 
WHERE 
    t1.client_id IN ( 103, 115, 116 ) 
    AND t1.deleted !=0

通常mysql不使用最有效索引的原因是基数的错误统计，这就是为什么在具有大量列的大表上运行OPTIMIZE TABLE是一个好习惯索引。