join/order-by子句会导致查询速度变慢

Question

有一个小型博客网站，主机提供商运行Server version: 5.5.5-10.3.34-MariaDB-cll-lve MariaDB Server。最近几周，我注意到获取帖子的速度较慢，现在查询需要6秒。

有问题的查询：

SELECT * FROM POSTS
JOIN POST_CATEGORIES on POST_CATEGORIES.CATEG_ID = POSTS.POST_CATEGORY
LEFT JOIN USERS on POSTS.POST_USERNAME = USERS.USER_USERNAME
WHERE POST_STATUS = 'ENA'
AND POST_IS_PUBLIC = 'Y'
ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
LIMIT 0, 12;

如果我去掉order by，查询时间会下降到0.04秒，但显然这是正确排序所必需的。如果我删除USERS的左联接，也会产生同样的效果，但我想把重点放在order by子句上。

表行：

POSTS: 62K
POST_CATEGORIES: 20
USERS: 180

有趣的发现：

表上已经有索引(主/唯一/外键(，但相关列上没有，为了简单起见，我不在这里添加它们。

但我确实尝试添加了索引：

INDEX `POSTS_OPTIMIZING_INDEX` (`POST_CREATION_DATE`, `POST_LIKES`, `POST_DISLIKES`) USING BTREE,

和FORCE INDEX (POSTS_OPTIMIZING_INDEX)，看到了一些有趣的东西：

• 在MariaDB上，实际上没有什么区别
• 在我家的mysql社区服务器Server version: 5.7.20 MySQL Community Server (GPL)上，查询持续时间从6秒降到了0.12秒，这是可以接受的

有关于优化SQL或调优mariadb的建议吗？

编辑

表格定义：

帖子：

CREATE TABLE `POSTS` (
`POST_ID` int(11) NOT NULL AUTO_INCREMENT,
`POST_URL_ID` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_TYPE` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_CATEGORY` int(3) DEFAULT NULL,
`POST_IS_PUBLIC` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_IS_STICKY` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_FREE_TEXT` varchar(5000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_IMAGE_FILE` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_VIDEO_URL` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_IS_FROM_USER` varchar(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_USERNAME` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_CREATION_DATE` timestamp NOT NULL DEFAULT current_timestamp(),
`POST_STATUS` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ENA',
`POST_LIKES` int(5) DEFAULT 0,
`POST_DISLIKES` int(5) DEFAULT 0,
`POST_ADDITIONAL_INFO` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_FB_OBJ_ID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_FB_OBJ_TYPE` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_FB_IMG_LAST_CHECKED` timestamp NULL DEFAULT NULL,
`POST_FB_IMG_LAST_CHECK_RESULT` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`POST_TWITTER_OBJ_ID` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`POST_ID`),
UNIQUE KEY `POST_URL_ID` (`POST_URL_ID`),
KEY `FK_POSTS_POST_CATEGORIES` (`POST_CATEGORY`),
CONSTRAINT `FK_POSTS_POST_CATEGORIES` FOREIGN KEY (`POST_CATEGORY`) REFERENCES `POST_CATEGORIES` (`CATEG_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=62529 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

POST_CATEGORIES:

CREATE TABLE `POST_CATEGORIES` (
`CATEG_ID` int(11) NOT NULL AUTO_INCREMENT,
`CATEG_URL_NAME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
`CATEG_NAME` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`CATEG_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

用户：

CREATE TABLE `USERS` (
`USER_ID` int(11) NOT NULL AUTO_INCREMENT,
`USER_MAIL_ADDRESS` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_USERNAME` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_PASSWORD` varchar(60) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_CREATION_DATE` timestamp NULL DEFAULT current_timestamp(),
`USER_LAST_LOGIN_DATE` timestamp NULL DEFAULT NULL,
`USER_FROM_FB` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_FB_MAIL_ADDRESS` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_FB_FULL_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_SPECIAL` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_ACCT_STATUS` varchar(3) COLLATE utf8_unicode_ci DEFAULT 'ENA',
`USER_POSTS_COUNT` int(5) DEFAULT 0,
`USER_NAME` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_QUOTE` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_ABOUT_MYSELF` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_WEBSITE` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_FB` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_TWITTER` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_LOCATION` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_BIRTHDAY` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_PROFILE_PIC` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
`USER_PROFILE_PIC_FROM_FB` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`USER_ID`),
UNIQUE KEY `USER_USERNAME` (`USER_USERNAME`)
) ENGINE=InnoDB AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

EXPLAIN:

mysql> EXPLAIN
-> SELECT * FROM POSTS
-> JOIN POST_CATEGORIES on POST_CATEGORIES.CATEG_ID = POSTS.POST_CATEGORY
-> LEFT JOIN USERS on POSTS.POST_USERNAME = USERS.USER_USERNAME
-> WHERE POST_STATUS = 'ENA'
-> AND POST_IS_PUBLIC = 'Y'
-> ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
-> LIMIT 0, 12;
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
| id   | select_type | table           | type   | possible_keys            | key     | key_len | ref                            | rows  | Extra                                           |
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
|    1 | SIMPLE      | POSTS           | ALL    | FK_POSTS_POST_CATEGORIES | NULL    | NULL    | NULL                           | 61407 | Using where; Using temporary; Using filesort    |
|    1 | SIMPLE      | POST_CATEGORIES | eq_ref | PRIMARY                  | PRIMARY | 4       | MY_DB.POSTS.POST_CATEGORY |     1 |                                                 |
|    1 | SIMPLE      | USERS           | ALL    | NULL                     | NULL    | NULL    | NULL                           |   178 | Using where; Using join buffer (flat, BNL join) |
+------+-------------+-----------------+--------+--------------------------+---------+---------+--------------------------------+-------+-------------------------------------------------+
3 rows in set (0.03 sec)
mysql>  

并删除2个连接：

mysql> EXPLAIN
-> SELECT * FROM POSTS
-> WHERE POST_STATUS = 'ENA'
-> AND POST_IS_PUBLIC = 'Y'
-> ORDER BY POST_IS_STICKY DESC, (TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now())-(POST_LIKES-POST_DISLIKES)*3600) ASC
-> LIMIT 0, 12;
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
|    1 | SIMPLE      | POSTS | ALL  | NULL          | NULL | NULL    | NULL | 61407 | Using where; Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.02 sec)
mysql> 

TIA-

Answer 1

"爆炸内爆；综合征重写查询以将12行先，然后JOIN分配给其他表。

SELECT ...
FROM ( SELECT user_id,
is_sticky,
(TIMESTAMPDIFF(SECOND, POST_CREATION_DATE, now()) -
(POST_LIKES-POST_DISLIKES)*3600)  AS  metric
FROM POSTS
WHERE  POST_STATUS = 'ENA'
AND  POST_IS_PUBLIC = 'Y'
ORDER BY
POST_IS_STICKY DESC, metric ASC
LIMIT  0, 12
) AS x
JOIN POSTS AS p USING(user_id)
JOIN  POST_CATEGORIES AS pc  ON pc.CATEG_ID = p.POST_CATEGORY
LEFT JOIN  USERS AS u  ON p.POST_USERNAME = u.USER_USERNAME
ORDER BY x.POST_IS_STICKY DESC, x.metric ASC

用您真正需要的列替换概要。

是的，POSTS被第二次查看，但仅针对12行，并由PK进行查看。类似地，其他行仅被查看12次。

是的，需要重复ORDER BY。如果JOIN创建额外的行(例如，多个类别(，则LIMIT可能需要重复。

考虑使用

SELECT ...
( SELECT GROUP_CONCAT(CATEG_NAME) FROM POST_CATEGORIES
WHERE categ_id = p.post_categories ) AS Categories,
...

而不是JOINing到POST_CATEGORIES，尤其是在有多个类别的情况下。

此可能有助于提高性能，但前提是它具有足够的选择性：

INDEX(post_tatus, post_is_public)