mysql - 解释查询返回不同的结果
问题描述
两台服务器上的数据相似。
服务器甲:
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 ?
就像玛丽亚不考虑索引或缓存一样?
什么样的配置选项会影响这种行为?
谢谢,
解决方案
好的,
innodb_buffer_pool_size 设置是我的解决方案;)谢谢。
推荐阅读
- json - 将谷歌云存储中的 json 文件加载到大查询表中
- python - 从 MongoDB Atlas 获取数据时出现“ObjectId' object is not iterable”错误
- mysql - 没有Count,查询有两个以上前置条件的患者,Patientinformation和Preconditions是我的表,PatientID是共享键
- python - Scrapy不将值保存到项目
- azure-devops - Scrum 是否应该在一个 sprint 中交付功能?
- reactjs - react-multi-select-component 在 IE 11 中未按预期工作
- c++ - 一个关于原子操作的问题及其 c++ 说明
- serverless-framework - '无服务器调用 -f hello' 给出 KeyError
- php - $script_filename 导致 nginx 配置错误
- php - 如何在 laravel 中从 PDF 上传生成缩略图