mysql - 解释查询返回不同结果

Question

两台服务器上的数据相似。

服务器A:

mysql>  SHOW VARIABLES LIKE "%version%";
+-------------------------+-------------------------+
| Variable_name           | Value                   |
+-------------------------+-------------------------+
| innodb_version          | 5.7.26                  |
| protocol_version        | 10                      |
| slave_type_conversions  |                         |
| tls_version             | TLSv1,TLSv1.1           |
| version                 | 5.7.26-0ubuntu0.16.04.1 |
| version_comment         | (Ubuntu)                |
| version_compile_machine | x86_64                  |
| version_compile_os      | Linux                   |
+-------------------------+-------------------------+

服务器B

MariaDB> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------------+
| Variable_name           | Value                    |
+-------------------------+--------------------------+
| innodb_version          | 5.6.42-84.2              |
| protocol_version        | 10                       |
| slave_type_conversions  |                          |
| version                 | 10.1.38-MariaDB-0+deb9u1 |
| version_comment         | Debian 9.8               |
| version_compile_machine | x86_64                   |
| version_compile_os      | debian-linux-gnu         |
| version_malloc_library  | system jemalloc          |
| version_ssl_library     | YaSSL 2.4.4              |
| wsrep_patch_version     | wsrep_25.24              |
+-------------------------+--------------------------+

当我对查询进行 EXPLAIN 时，结果有所不同:

Server A return

+----+-------------+-------+------------+--------+----------------------+----------+---------+--------------------+------+----------+-----------------+
| id | select_type | table | partitions | type   | possible_keys        | key      | key_len | ref                | rows | filtered | Extra           |
+----+-------------+-------+------------+--------+----------------------+----------+---------+--------------------+------+----------+-----------------+
|  1 | SIMPLE      | f1_   | NULL       | index  | IDX_90291C2D2D710CF2 | date_idx | 5       | NULL               |   40 |   100.00 | Using temporary |
|  1 | SIMPLE      | u0_   | NULL       | eq_ref | PRIMARY,closed_idx   | PRIMARY  | 4       | smailsf.f1_.poster |    1 |    50.00 | Using where     |
+----+-------------+-------+------------+--------+----------------------+----------+---------+--------------------+------+----------+-----------------+

Server B return

+------+-------------+-------+--------+----------------------+---------+---------+------------------+--------+---------------------------------+
| id   | select_type | table | type   | possible_keys        | key     | key_len | ref              | rows   | Extra                           |
+------+-------------+-------+--------+----------------------+---------+---------+------------------+--------+---------------------------------+
|    1 | SIMPLE      | f1_   | ALL    | IDX_90291C2D2D710CF2 | NULL    | NULL    | NULL             | 657064 | Using temporary; Using filesort |
|    1 | SIMPLE      | u0_   | eq_ref | PRIMARY,closed_idx   | PRIMARY | 4       | smail.f1_.poster |      1 | Using where                     |
+------+-------------+-------+--------+----------------------+---------+---------+------------------+--------+---------------------------------+

为什么同一个查询，在服务器 A 上的行数是 40，而服务器 B 上的行数是 657064 ？

这就像 Maria 不考虑索引或缓存？

什么样的配置选项会影响此行为？

谢谢

Answer 1

好的，

innodb_buffer_pool_size 设置是我的解决方案；)谢谢。