解释查询返回不同的结果

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              |
+-------------------------+--------------------------+

当我对查询进行解释时，结果是不同的：

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？

就像玛丽亚不考虑索引或缓存一样？

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

谢谢

Answer 1

好的，

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