优化连接查询 [英] Optimizing join query
问题描述
我遇到了以下查询的问题，在添加 ORDER BY kp_votes DESC, kp_rating DESC 后变得非常慢，问题是我已经为这些列建立了索引.
I've trouble with the following query, it became very very slow after adding ORDER BY kp_votes DESC, kp_rating DESC, the thing is I've indexes for those columns.
explain SELECT *
FROM post
JOIN post_plus
ON post_plus.news_id = post.id
WHERE category regexp '[[:<:]](131|138|139|140|141|142|143|144|145|146|147|148|149|150|151|152|153|154|155|156|157|171|172|134|136|137|23|123)[[:>:]]'
AND approve=1
AND allow_main=1
ORDER BY kp_votes DESC,
kp_rating DESC LIMIT 30;
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
| 1 | SIMPLE | post | ref | PRIMARY,allow_main,approve,approve_2 | allow_main | 1 | const | 12273 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | post_plus | ref | news_id | news_id | 5 | online.post.id | 1 | NULL |
+----+-------------+---------------+------+--------------------------------------+------------+---------+--------------------+-------+----------------------------------------------+
SHOW INDEX FROM post_plus;
SHOW INDEX FROM post_plus;
+-----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| post_plus | 0 | PRIMARY | 1 | pid | A | 32317 | NULL | NULL | | BTREE | | |
| post_plus | 1 | user_id | 1 | user_id | A | 26 | NULL | NULL | | BTREE | | |
| post_plus | 1 | news_id | 1 | news_id | A | 32317 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | kp_votes | 1 | kp_votes | A | 4616 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | kp_rating | 1 | kp_rating | A | 5386 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | kp_id | 1 | kp_id | A | 16158 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | post_uuid | 1 | post_uuid | A | 32317 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | blockedCountries | 1 | blockedCountries | A | 10 | NULL | NULL | YES | BTREE | | |
| post_plus | 1 | kp_votes_2 | 1 | kp_votes | A | 5386 | NULL | NULL | YES | BTREE | | |
+---------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
另一个订单"工作得很快……例如，如果我设置 ORDER BY fixed desc, date DESC LIMIT 30 它的执行速度快 10 倍.
another "orders" work fast... for example if I set ORDER BY fixed desc, date DESC LIMIT 30 it performs 10 times faster.
描述帖子；
+------------------+-----------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-----------------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| autor | varchar(40) | NO | MUL | | |
| date | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| full_story | text | NO | MUL | NULL | |
| xfields | text | NO | | NULL | |
| title | varchar(255) | NO | MUL | | |
| descr | varchar(200) | NO | MUL | | |
| keywords | text | NO | | NULL | |
| category | varchar(200) | NO | MUL | 0 | |
| alt_name | varchar(200) | NO | MUL | | |
| comm_num | mediumint(8) unsigned | NO | MUL | 0 | |
| allow_comm | tinyint(1) | NO | | 1 | |
| allow_main | tinyint(1) unsigned | NO | MUL | 1 | |
| approve | tinyint(1) | NO | MUL | 0 | |
| fixed | tinyint(1) | NO | | 0 | |
| allow_br | tinyint(1) | NO | | 1 | |
| symbol | varchar(3) | NO | MUL | | |
| tags | varchar(255) | NO | MUL | | |
| metatitle | varchar(255) | NO | | | |
| FileTempUUID | varchar(11) | YES | | NULL | |
| titleAlternative | varchar(255) | YES | | NULL | |
+------------------+-----------------------+------+-----+---------------------+----------------+
21 rows in set (0.01 sec)
mysql> 描述 post_plus;
mysql> describe post_plus;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| pid | int(11) | NO | PRI | NULL | auto_increment |
| news_id | int(11) | YES | MUL | NULL | |
| kp_votes | int(11) | YES | MUL | NULL | |
| kp_rating | decimal(5,3) | YES | MUL | NULL | |
| kp_id | varchar(100) | YES | MUL | NULL | |
| pdate | datetime | YES | | NULL | |
| news_read | int(11) | NO | | 0 | |
| user_id | int(11) | NO | MUL | 0 | |
| allow_rate | tinyint(1) | NO | | 1 | |
| rating | mediumint(8) | NO | | 0 | |
| vote_num | mediumint(8) | NO | | 0 | |
| votes | tinyint(1) | NO | | 0 | |
| editdate | int(11) | YES | | NULL | |
| view_edit | tinyint(1) | NO | | 0 | |
| editor | varchar(40) | NO | | | |
| reason | varchar(255) | NO | | | |
| access | varchar(150) | NO | | | |
| cover | text | YES | | NULL | |
| quality | varchar(100) | YES | | NULL | |
| post_uuid | varchar(11) | YES | MUL | NULL | |
| encoded | int(1) | YES | | NULL | |
| embed_views | int(11) | NO | | 0 | |
| blockedCountries | varchar(128) | YES | MUL | NULL | |
+------------------+--------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)
编辑*
我尝试添加:
on post (approve, allow_main, category) (depending on the cardinality it can be different order) and use category IN () because that can take advantage of good indexes while regexp can't.
on post_plus (news_id, kp_votes, kp_rating) (order matters here) that will help with the sorting
正如 Károly Nagy 所建议的那样，但没有帮助.mysql 拒绝使用 kp_votes 和 kp_rating 索引.
as it was suggested by Károly Nagy, but it didn't help. mysql refuse using kp_votes and kp_rating indexes.
推荐答案
在没有看到实际数据的情况下，我只能告诉以下内容:
Without seeing the actual data I can only tell the followings:
结果集相当大，表格缺乏适当的索引.正如我所看到的，你有单列索引而不是复合索引，它更适合查询.news_id 索引用于连接表，因此即使您在 kp_* 列上有索引，它们也无法使用.
The result set is quite big and the tables lack proper indexes. As I see you have single column indexes instead of composite indexes which fits the query better. The news_id index is being used for joining the tables so even if you have indexes on the kp_* columns they cannot be used.
这里你需要的是两个复合索引:
What you need here is two compozite index:
- 在 post (approve, allow_main, category) 上(取决于基数，它可以是不同的顺序)并使用 category IN () 因为这样可以利用好的索引而 regexp 不能.
- 在 post_plus (news_id, kp_votes, kp_rating) 上(这里的顺序很重要)，这将有助于排序
ps.:您可能需要帮助 MySQL 执行连接的顺序.添加索引后查看说明，如果它看起来不好，请尝试 STRAIGHT_JOIN (http://dev.mysql.com/doc/refman/5.5/en/join.html)
ps.: You might need to help MySQL in which order should it do the join. See the explain after adding the indexes and if it's not looking good try STRAIGHT_JOIN (http://dev.mysql.com/doc/refman/5.5/en/join.html)
这篇关于优化连接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
