不同的sql在两台服务器上进行了解释。“复制到tmp表”非常慢

我有一个查询，在开发服务器上执行的时间少于在生产服务器上执行的时间（数据库是相同的）。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 |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+

生产服务器上的分析结果显示出类似以下内容：

............................................
| 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秒。您能否回答两个问题：

1. tmpfs配置有什么问题？
2. 为什么解释在服务器上不同，如何优化此查询？（即使不使用tmpfs；我发现如果删除了最后一个“ order by”，查询完成的速度也会大大提高）。

提前致谢。