我有两张表：user和post。
通过显示创建表语句：
CREATE TABLE `user` (
`user_id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET latin1 NOT NULL,
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8;
CREATE TABLE `post` (
`post_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`owner_id` bigint(20) NOT NULL,
`data` varchar(300) CHARACTER SET latin1 DEFAULT NULL,
PRIMARY KEY (`post_id`),
KEY `my_fk` (`owner_id`),
CONSTRAINT `my_fk` FOREIGN KEY (`owner_id`) REFERENCES `user` (`user_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1012919 DEFAULT CHARSET=utf8;
在执行带有ORDER BY语句的两个查询时，一切正常，但结果非常奇怪，ASC很慢，而DESC非常快。
SELECT sql_no_cache * FROM mydb.post where post_id > 900000 and owner_id = 20 order by post_id desc limit 10;
10 rows in set (0.00 sec)
SELECT sql_no_cache * FROM mydb.post where post_id > 900000 and owner_id = 20 order by post_id asc limit 10;
10 rows in set (0.15 sec)
然后我使用解释语句:
explain SELECT sql_no_cache * FROM mydb.post where post_id > 900000 and owner_id = 20 order by post_id desc limit 10;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| 1 | SIMPLE | post | ref | PRIMARY,my_fk | my_fk | 8 | const | 239434 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
1 row in set (0.01 sec)
explain SELECT sql_no_cache * FROM mydb.post where post_id > 900000 and owner_id = 20 order by post_id asc limit 10;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+------------------------------------+
| 1 | SIMPLE | post | ref | PRIMARY,my_fk | my_fk | 8 | const | 239434 | Using index condition; Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+------------------------------------+
1 row in set (0.00 sec)
我认为重点在于“使用索引条件”，但我不知道原因。如何改进我的数据库以获得更好的性能？
更新：
explain SELECT * FROM mydb.post where post_id < 600000 and owner_id = 20 order by post_id desc limit 10;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
| 1 | SIMPLE | post | ref | PRIMARY,my_fk | my_fk | 8 | const | 505440 | Using where |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+
explain SELECT * FROM mydb.post where post_id < 600000 and owner_id > 19 and owner_id < 21 order by post_id desc limit 10;
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+
| 1 | SIMPLE | post | range | PRIMARY,my_fk | PRIMARY | 4 | NULL | 505440 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+--------+-------------+