MySQL查询时间在两台不同的机器上差别很大

Question

这让我发疯。

我在两台不同的机器上有完全相同的数据库，一台是 Arch，一台是 Debian。我正在下表中运行查询:

describe wellness;
+--------------+-------------+------+-----+---------------------+----------------+
| Field        | Type        | Null | Key | Default             | Extra              |    
+--------------+-------------+------+-----+---------------------+----------------+
| wellness_id  | int(11)     | NO   | PRI | NULL                | auto_increment |
| people_id    | int(11)     | NO   | MUL | NULL                |                |
| time_checked | timestamp   | NO   | MUL | CURRENT_TIMESTAMP   |                |
| check_type   | varchar(1)  | NO   | MUL | NULL                |                |
| username     | varchar(16) | NO   | MUL | NULL                |                |
| return_date  | timestamp   | NO   | MUL | 0000-00-00 00:00:00 |                |
| seen_by      | varchar(16) | YES  | MUL | NULL                |                |
+--------------+-------------+------+-----+---------------------+----------------+
7 rows in set (0.00 sec)

和查询:

mysql> explain select * from wellness         where wellness_id in (                 select max(wellness_id) from wellness group by people_id) and time_checked < (now() - interval 48 hour);
+----+--------------------+----------+-------+------------------+---------------+---------+------+-------+-------------+
| id | select_type        | table    | type  | possible_keys    | key           | key_len | ref  | rows  | Extra       |
+----+--------------------+----------+-------+------------------+---------------+---------+------+-------+-------------+
|  1 | PRIMARY            | wellness | ALL   | time_checked_key | NULL          | NULL    | NULL | 62546 | Using where |
|  2 | DEPENDENT SUBQUERY | wellness | index | NULL             | people_id_key | 4       | NULL |   231 | Using index |
+----+--------------------+----------+-------+------------------+---------------+---------+------+-------+-------------+
2 rows in set (0.00 sec)

在我的 Debian 服务器上，我正在迁移使用此数据库的应用程序，查询需要 7 分钟才能运行。在我的 Arch 服务器上，只需不到一秒。奇怪的是，我的 Arch 盒子上的 EXPLAIN 是不同的，我首先从其中获取了 SQL 数据:

MariaDB [redacted]> explain select * from wellness         where wellness_id in (                 select max(wellness_id) from wellness group by people_id) and time_checked < (now() - interval 48 hour);
+------+--------------+-------------+--------+--------------------------+---------------+---------+------------------------------+------+--------------------------+
| id   | select_type  | table       | type   | possible_keys            | key           | key_len | ref                          | rows | Extra                    |
+------+--------------+-------------+--------+--------------------------+---------------+---------+------------------------------+------+--------------------------+
|    1 | PRIMARY      | <subquery2> | ALL    | distinct_key             | NULL          | NULL    | NULL                         |  221 |                          |
|    1 | PRIMARY      | wellness    | eq_ref | PRIMARY,time_checked_key | PRIMARY       | 4       | <subquery2>.max(wellness_id) |    1 | Using where              |
|    2 | MATERIALIZED | wellness    | range  | NULL                     | people_id_key | 4       | NULL                         |  221 | Using index for group-by |
+------+--------------+-------------+--------+--------------------------+---------------+---------+------------------------------+------+--------------------------+
3 rows in set (0.00 sec)

对于我需要调整什么才能使其正常工作有什么想法吗？据我所知，两台服务器上的 Apache 和 PHP 设置完全相同，因此我觉得这可能是数据库问题。

Answer 1

比较输出

show variables LIKE 'sql_mode';

并验证 my.cnf 中的设置。

此外，如果您从一台服务器转储数据库并将其导入另一台服务器，数据文件也不相同。行的内部顺序可以不同。

您还可以为您的表运行此命令以更新优化器统计信息

SELECT * FROM YOUR_TABLE PROCEDURE ANALYSE();