我有一个查询，它在开发服务器上的执行时间比在生产服务器上的时间少(数据库是一样的(。生产服务器的效率要高得多(64GB 内存、12 个内核等(。
下面是查询：
SELECT `u`.`id`,
`u`.`user_login`,
`u`.`last_name`,
`u`.`first_name`,
`r`.`referrals`,
`pr`.`worker`,
`rep`.`repurchase`
FROM `ci_users` `u`
LEFT JOIN
(SELECT `referrer_id`,
COUNT(user_id) referrals
FROM ci_referrers
GROUP BY referrer_id) AS `r` ON `r`.`referrer_id` = `u`.`id`
LEFT JOIN
(SELECT `user_id`,
`expire`,
SUM(`quantity`) worker
FROM ci_product_11111111111111111
GROUP BY `user_id`) AS `pr` ON `pr`.`user_id` = `u`.`id`
AND (`pr`.`expire` > '2015-12-10 09:23:45'
OR `pr`.`expire` IS NULL)
LEFT JOIN `ci_settings` `rep` ON `u`.`id` = `rep`.`id`
ORDER BY `id` ASC LIMIT 100,
150;
在开发服务器上具有以下解释结果：
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
| 1 | PRIMARY | u | index | NULL | PRIMARY | 4 | NULL | 1 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | dev1.u.id | 10 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key1> | <auto_key1> | 5 | dev1.u.id | 15 | Using where |
| 1 | PRIMARY | rep | eq_ref | PRIMARY | PRIMARY | 4 | dev1.u.id | 1 | NULL |
| 3 | DERIVED | ci_product_11111111111111111 | ALL | NULL | NULL | NULL | NULL | 30296 | Using temporary; Using filesort |
| 2 | DERIVED | ci_referrers | ALL | NULL | NULL | NULL | NULL | 11503 | Using temporary; Using filesort |
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
而这个来自 prod：
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
| 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 10990 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2628 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 8830 | |
| 1 | PRIMARY | rep | eq_ref | PRIMARY | PRIMARY | 4 | prod123.u.id | 1 | |
| 3 | DERIVED | ci_product_11111111111111111 | ALL | NULL | NULL | NULL | NULL | 28427 | Using temporary; Using filesort |
| 2 | DERIVED | ci_referrers | ALL | NULL | NULL | NULL | NULL | 11837 | Using temporary; Using filesort |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
生产服务器上的分析结果向我显示了类似的东西：
............................................
| statistics | 0.000030 |
| preparing | 0.000026 |
| Creating tmp table | 0.000037 |
| executing | 0.000008 |
| Copying to tmp table | 5.170296 |
| Sorting result | 0.001223 |
| Sending data | 0.000133 |
| Waiting for query cache lock | 0.000005 |
............................................
在谷歌上搜索了一段时间后，我决定将临时表移动到RAM中：
/etc/fstab：
tmpfs /var/tmpfs tmpfs rw,uid=110,gid=115,size=16G,nr_inodes=10k,mode=0700 0 0
目录规则：
drwxrwxrwt 2 mysql mysql 40 Dec 15 13:57 tmpfs
etc/mysql/my.cnf(玩了很多值(：
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmpfs
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16000M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 150
myisam-recover = BACKUP
tmp_table_size = 512M
max_heap_table_size = 1024M
max_connections = 100000
table_cache = 1024
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
query_cache_limit = 1000M
query_cache_size = 10000M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
而且它不起作用。执行时间保持不变，大约 5 秒。你能回答两个问题吗：
- tmpfs 配置有什么问题？
- 为什么服务器上的解释不同，如何优化此查询？(即使不使用TMPFS;我发现，如果删除最后一个"排序依据"，查询完成速度要快得多(。
提前谢谢。
为什么服务器上的解释不同，如何优化此查询？ (即使不使用TMPFS;我发现，如果最后一个"排序方式" 删除，查询完成速度要快得多(。
您说"数据库是相同的"，但从解释输出来看，您可能意味着"模式是相同的"。 看起来生产架构中有更多的数据？ MySQL 根据数据量、索引大小等优化处理查询的方式。 这将解释(在最高级别(为什么你会看到如此巨大的差异。
要查看的说明输出列是"行"。 注意到两个派生表在 dev 中是如何非常小的吗？ 看起来(你可以在 freenode IRC 上#mysql询问以确认(MySQL 正在为 dev 中的派生表创建索引，但选择不在生产中创建索引(可能是因为有更多的记录？
tmpfs 配置有什么问题？
无。 :) MySQL在内存中创建临时表，直到其中的数据量达到一定的大小(tmp_table_size(，然后才将临时数据写入磁盘。 您可以信任MySQL来执行此操作 - 您无需创建在内存中创建临时文件系统并将MySQL指向那里的所有复杂性和开销... InnoDB 的关键变量是 innodb_buffer_pool_size ，我看不到你已经调整了。
网上有很多文档，包括Percona的许多(恕我直言(好东西。 (我不隶属于他们，但我与他们合作过;如果你能负担得起与他们签订的支持合同 - 那就去做吧。 他们真的知道他们的东西。
我绝对不是调优MySQL的专家，所以我不会评论你选择的选项，只是说我已经花了几周的时间阅读和调优 - 只是为了让Percona团队看看它并说"这很好，但你错过了这个，弄错了" - 结果有了明显的改进！
最后，我会指出其他一些事情 - 索引，模式和查询是主要的。 您有两个子查询，我会尝试将它们分解出来，看看是否有帮助。 需要 dev 中提供的代表性数据示例才能正确优化查询。 (我过去曾为此使用只读复制服务器。 我不完全了解您的查询试图做什么，但看起来您可以将这些表加入并对整体结果进行分组。
如果我错过了明显的(可能！( - 那么我会考虑单独维护这些子查询中的数据表。 默认情况下，我一直使用 SP 来处理INSERT，因为 DBA 指出，您可以在以后以事务安全的方式更轻松地添加此类缓存逻辑。 因此，当您插入到ci_*表中时，还要更新COUNT()数据的表(如果您无法分解出子查询( - 因此所有内容都成为一组索引良好的连接。
解释表明，在 prod 上，查询不使用 u、derived1、derived2 表上的索引，而在 dev 上则使用索引。因此，在生产中扫描的行号明显更高。2 个派生表上的索引名称表明这些是由 mysql 动态创建的，利用了 mysql v5.6.5 中可用的物化派生表优化策略。由于生产服务器的解释中不存在此类优化，因此生产服务器可能具有较早的 mysql 版本。
正如注释中@Satevg提供的，开发和生产环境具有以下 mysql 版本：
开发：debian 7，Mysql 5.6.28。产品：debian 8，Mysql 5.5.44
mysql 版本中的这种细微差异可以解释速度差异，因为开发服务器可以利用具体化优化策略，而 prod - 仅 v5.5 - 不能。