我有一个查询在开发服务器上执行的时间比在生产服务器上执行的时间少(数据库是相同的)。 Prod 服务器效率更高(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 |
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+

这一个来自产品:

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

prod 服务器上的分析结果向我展示了类似的内容:

............................................
| 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 秒。 你能回答 2 个问题吗:

1. tmpfs 配置有什么问题？
2. 为什么服务器上的解释不同，我该如何优化这个查询？ (即使不使用 tmpfs；我发现如果删除最后一个“order by”，查询完成速度会更快)。

提前致谢。

最佳答案

Why explains are different on servers, how can I optimize this query? (even not using tmpfs; I figured out that if the last 'order by' removed, query completes much faster).

你说“数据库是一样的”，但从解释输出来看你大概是指“模式是一样的”。看起来生产模式中有更多的数据？ MySQL 根据数据量、索引大小等优化它处理查询的方式。这将解释(在最高级别)为什么您会看到如此显着的差异。

要查看的解释输出列是“行”。注意到这两个派生表在开发中是如何非常小的吗？看起来(您可以在 freenode IRC 上的 #mysql 中询问以确认)MySQL 正在为开发中的派生表创建索引，但选择不在生产中创建索引(可能是因为有更多的记录？)。

What's wrong with tmpfs configuration?

没有。 :) MySQL 在内存中创建临时表，直到其中的数据量达到一定大小(tmp_table_size)，然后才将临时数据写入磁盘。您可以相信 MySQL 可以做到这一点——您不需要创建在内存中创建临时文件系统并将 MySQL 指向那里的所有复杂性和开销...InnoDB 的key变量是 innodb_buffer_pool_size，我看不到你已经调整过了。

网上有很多文档，包括很多 (恕我直言) good stuff by Percona . (我不隶属于他们，但我曾与他们合作过；如果你能负担得起与他们签订的支持契约(Contract)，那就去做吧。他们真的很了解他们的东西。)

我绝对不是调优 MySQL 方面的专家，所以我不会评论您选择的选项，只是说我花了数周时间阅读和调优 - 只是为了让 Percona 团队看看看着它并说“那太好了，但是你错过了这个并且弄错了” - 结果有了显着的改进!

最后我要指出一些其他的东西——索引、模式和查询是主要的。你有两个子查询，我会尝试将它们分解出来看看是否首先有帮助。您需要 dev 中可用的代表性数据样本才能正确调整查询。 (我过去为此使用过只读复制服务器。)我不完全理解你的查询试图做什么，但看起来你可以将这些表加入并对整体结果进行分组。

如果我遗漏了明显的(可能!)——那么我会考虑在这些子查询中单独维护一个数据表。默认情况下，我一直使用 SP 来处理 INSERT，因为 DBA 指出您可以在以后以事务安全的方式更轻松地添加此类缓存逻辑。因此，当您插入 ci_* 表时，还要更新一个包含 COUNT() 数据的表(如果您不能分解出子查询)——所以一切都变得很好-索引的连接集。

关于mysql - 不同的sql在两台服务器上解释。 "Copying to tmp table"极慢，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/34313897/