我在 stack overflow 上遇到过类似的问题，但它似乎与服务器/mysql 设置有关，而不是编码。
以下所有查询都会在我们的开发服务器上立即执行，但最多可能需要 2 分 20 秒。
查询执行时间似乎受到 LIKE 字符串的模糊性影响。如果它们与匹配项较少的国家/地区紧密匹配，则需要的时间会更少，而如果您使用诸如“ge”之类的词来表示德国，则执行时间会更长。但情况并不总是这样，有时它会非常不稳定。
发送数据似乎是罪魁祸首，但为什么会这样？这是什么意思？生产中的内存看起来也相当低（可用内存）？
生产：
Intel 四核 Xeon E3-1220 3.1GHz
4GB DDR3
2x 1TB SATA RAID1
网络速度 100Mb
Ubuntu
发展
英特尔酷睿 i3-2100，2C/4T，3.10GHz
500 GB SATA - 无 RAID
4GB DDR3
更新2：
mysqltuner 输出：
[产品]
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.61-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 103M (Tables: 180)
[--] Data in InnoDB tables: 491M (Tables: 19)
[!!] Total fragmented tables: 38
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 77d 4h 6m 1s (53M q [7.968 qps], 14M conn, TX: 87B, RX: 12B)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (12K/53M)
[OK] Highest usage of available connections: 22% (34/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/10.6M
[OK] Key buffer hit rate: 98.7% (162M cached / 2M reads)
[OK] Query cache efficiency: 20.7% (7M cached / 36M selects)
[!!] Query cache prunes per day: 3934
[OK] Sorts requiring temporary tables: 1% (3K temp sorts / 230K sorts)
[!!] Joins performed without indexes: 71068
[OK] Temporary tables created on disk: 24% (3M on disk / 13M total)
[OK] Thread cache hit rate: 99% (690 created / 14M connections)
[!!] Table cache hit rate: 0% (64 open / 85M opened)
[OK] Open file limit used: 12% (128/1K)
[OK] Table locks acquired immediately: 99% (16M immediate / 16M locks)
[!!] InnoDB data size / buffer pool: 491.9M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 64)
innodb_buffer_pool_size (>= 491M)
[开发]
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.62-0ubuntu0.11.10.1
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 185M (Tables: 632)
[--] Data in InnoDB tables: 967M (Tables: 38)
[!!] Total fragmented tables: 73
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 2h 26m 9s (5K q [0.058 qps], 1K conn, TX: 4M, RX: 1M)
[--] Reads / Writes: 99% / 1%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (11% of installed RAM)
[OK] Slow queries: 0% (0/5K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/18.6M
[OK] Key buffer hit rate: 99.9% (60K cached / 36 reads)
[OK] Query cache efficiency: 44.5% (1K cached / 2K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 44 sorts)
[OK] Temporary tables created on disk: 24% (162 on disk / 666 total)
[OK] Thread cache hit rate: 99% (2 created / 1K connections)
[!!] Table cache hit rate: 1% (64 open / 4K opened)
[OK] Open file limit used: 8% (88/1K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[!!] InnoDB data size / buffer pool: 967.7M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 64)
innodb_buffer_pool_size (>= 967M)
更新 1：
测试此处列出的查询时，通常不会发生超过一个其他查询，并且通常不会发生任何查询。
因为生产实际上正在处理 apache 请求，而开发收到的请求很少，因为只有我和另外 1 个人访问它——使用单台机器同时运行 apache 和 mysql 服务器会耗尽 4GB 的 RAM 吗？
生产：
sudo hdparm -tT /dev/sda
/dev/sda:
Timing cached reads: 24872 MB in 2.00 seconds = 12450.72 MB/sec
Timing buffered disk reads: 368 MB in 3.00 seconds = 122.49 MB/sec
sudo hdparm -tT /dev/sdb
/dev/sdb:
Timing cached reads: 24786 MB in 2.00 seconds = 12407.22 MB/sec
Timing buffered disk reads: 350 MB in 3.00 seconds = 116.53 MB/sec
Server version(mysql + ubuntu versions): 5.1.61-0ubuntu0.10.04.1
发展：
sudo hdparm -tT /dev/sda
/dev/sda:
Timing cached reads: 10632 MB in 2.00 seconds = 5319.40 MB/sec
Timing buffered disk reads: 400 MB in 3.01 seconds = 132.85 MB/sec
Server version(mysql + ubuntu versions): 5.1.62-0ubuntu0.11.10.1
原始数据：
这个查询不是所讨论的查询，但与之相关，所以我发布它。
SELECT
f.form_question_has_answer_id
FROM
form_question_has_answer f
INNER JOIN
project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id
INNER JOIN
company c ON p.project_company_has_user_company_id = c.company_id
INNER JOIN
project p2 ON p.project_company_has_user_project_id = p2.project_id
INNER JOIN
user u ON p.project_company_has_user_user_id = u.user_id
INNER JOIN
form f2 ON p.project_company_has_user_project_id = f2.form_project_id
WHERE
(f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174'
上述查询的解释计划是，在开发和生产上运行都会产生相同的计划。
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
| 1 | SIMPLE | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 1 | SIMPLE | f | ref | form_question_has_answer_form_id,form_question_has_answer_user_id | form_question_has_answer_form_id | 4 | const | 796 | Using where |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | new_klarents.f.form_question_has_answer_user_id | 1 | Using index |
| 1 | SIMPLE | p | ref | project_company_has_user_unique_key,project_company_has_user_user_id,project_company_has_user_company_id,project_company_has_user_project_id | project_company_has_user_user_id | 4 | new_klarents.f.form_question_has_answer_user_id | 1 | Using where |
| 1 | SIMPLE | f2 | ref | form_project_id | form_project_id | 4 | const | 15 | Using where |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | new_klarents.p.project_company_has_user_company_id | 1 | Using where |
+----+-------------+-------+--------+----------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+-------------+
此查询需要2分钟~20秒来执行。
服务器上实际运行的查询是这样的：
SELECT
COUNT(*) AS num_results
FROM (SELECT
f.form_question_has_answer_id
FROM
form_question_has_answer f
INNER JOIN
project_company_has_user p ON f.form_question_has_answer_user_id = p.project_company_has_user_user_id
INNER JOIN
company c ON p.project_company_has_user_company_id = c.company_id
INNER JOIN
project p2 ON p.project_company_has_user_project_id = p2.project_id
INNER JOIN
user u ON p.project_company_has_user_user_id = u.user_id
INNER JOIN
form f2 ON p.project_company_has_user_project_id = f2.form_project_id
WHERE
(f2.form_template_name = 'custom' AND p.project_company_has_user_garbage_collection = 0 AND p.project_company_has_user_project_id = '29') AND (LCASE(c.company_country) LIKE '%ge%' OR LCASE(c.company_country) LIKE '%abcde%') AND f.form_question_has_answer_form_id = '174'
GROUP BY
f.form_question_has_answer_id;) dctrn_count_query;
使用解释计划（在开发和生产上同样如此）：
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
| 2 | DERIVED | p2 | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
| 2 | DERIVED | f | ref | form_question_has_answer_form_id,form_question_has_answer_user_id | form_question_has_answer_form_id | 4 | | 797 | Using where |
| 2 | DERIVED | p | ref | project_company_has_user_unique_key,project_company_has_user_user_id,project_company_has_user_company_id,project_company_has_user_project_id,project_company_has_user_garbage_collection | project_company_has_user_user_id | 4 | new_klarents.f.form_question_has_answer_user_id | 1 | Using where |
| 2 | DERIVED | f2 | ref | form_project_id | form_project_id | 4 | | 15 | Using where |
| 2 | DERIVED | c | eq_ref | PRIMARY | PRIMARY | 4 | new_klarents.p.project_company_has_user_company_id | 1 | Using where |
| 2 | DERIVED | u | eq_ref | PRIMARY | PRIMARY | 4 | new_klarents.p.project_company_has_user_user_id | 1 | Using where; Using index |
+----+-------------+-------+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------+---------+----------------------------------------------------+------+------------------------------+
在生产服务器上我掌握的信息如下。
执行后：
+-------------+
| num_results |
+-------------+
| 3 |
+-------------+
1 row in set (2 min 14.28 sec)
显示简介：
+--------------------------------+------------+
| Status | Duration |
+--------------------------------+------------+
| starting | 0.000016 |
| checking query cache for query | 0.000057 |
| Opening tables | 0.004388 |
| System lock | 0.000003 |
| Table lock | 0.000036 |
| init | 0.000030 |
| optimizing | 0.000016 |
| statistics | 0.000111 |
| preparing | 0.000022 |
| executing | 0.000004 |
| Sorting result | 0.000002 |
| Sending data | 136.213836 |
| end | 0.000007 |
| query end | 0.000002 |
| freeing items | 0.004273 |
| storing result in query cache | 0.000010 |
| logging slow query | 0.000001 |
| logging slow query | 0.000002 |
| cleaning up | 0.000002 |
+--------------------------------+------------+
开发结果如下。
+-------------+
| num_results |
+-------------+
| 3 |
+-------------+
1 row in set (0.08 sec)
再次查看此查询的配置文件：
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000022 |
| checking query cache for query | 0.000148 |
| Opening tables | 0.000025 |
| System lock | 0.000008 |
| Table lock | 0.000101 |
| optimizing | 0.000035 |
| statistics | 0.001019 |
| preparing | 0.000047 |
| executing | 0.000008 |
| Sorting result | 0.000005 |
| Sending data | 0.086565 |
| init | 0.000015 |
| optimizing | 0.000006 |
| executing | 0.000020 |
| end | 0.000004 |
| query end | 0.000004 |
| freeing items | 0.000028 |
| storing result in query cache | 0.000005 |
| removing tmp table | 0.000008 |
| closing tables | 0.000008 |
| logging slow query | 0.000002 |
| cleaning up | 0.000005 |
+--------------------------------+----------+
如果我删除用户和/或项目内连接，查询将减少到 30 秒。
我掌握的最后一点信息是：
Mysqlserver和Apache在同一个盒子上，只有一个盒子用于生产。
从顶部开始的生产输出：之前和之后。
top - 15:43:25 up 78 days, 12:11, 4 users, load average: 1.42, 0.99, 0.78
Tasks: 162 total, 2 running, 160 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 50.4%sy, 0.0%ni, 49.5%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4037868k total, 3772580k used, 265288k free, 243704k buffers
Swap: 3905528k total, 265384k used, 3640144k free, 1207944k cached
top - 15:44:31 up 78 days, 12:13, 4 users, load average: 1.94, 1.23, 0.87
Tasks: 160 total, 2 running, 157 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.2%us, 50.6%sy, 0.0%ni, 49.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4037868k total, 3834300k used, 203568k free, 243736k buffers
Swap: 3905528k total, 265384k used, 3640144k free, 1207804k cached
但这并不能很好地代表生产的正常状态，因此这里是从今天执行查询之外获取的。
top - 11:04:58 up 79 days, 7:33, 4 users, load average: 0.39, 0.58, 0.76
Tasks: 156 total, 1 running, 155 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.3%us, 2.8%sy, 0.0%ni, 93.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4037868k total, 3676136k used, 361732k free, 271480k buffers
Swap: 3905528k total, 268736k used, 3636792k free, 1063432k cached
发展：这个在期间或之后都不会改变。
top - 15:47:07 up 110 days, 22:11, 7 users, load average: 0.17, 0.07, 0.06
Tasks: 210 total, 2 running, 208 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.2%sy, 0.0%ni, 99.7%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4111972k total, 1821100k used, 2290872k free, 238860k buffers
Swap: 4183036k total, 66472k used, 4116564k free, 921072k cached
答案1
差异可能在于：
- 在 PROD 服务器（Quad Xeon E3-1220）上，您有一个 RAID1 磁盘设置，这可能会减慢查询速度，因为它会写入 2 个磁盘并从 2 个磁盘读取http://en.wikipedia.org/wiki/RAID，这意味着提交时的性能会降低，而读取操作（选择）的性能会提高。根据您的应用程序，这可能是好事也可能是坏事……
- 两台服务器上的交换分区不同，并且使用的 RAM /交换使用情况似乎与 PROD / DEV 系统不同（即使它们具有相同数量的 RAM）。 我将使用 ps aux 检查正在运行的进程并比较列表，因为您将看到在 prod 上运行的进程更多。
- 请查看 mysql / prod 服务器上有多少个 query 的并发连接。
- 请查看 prod 和 dev 中的磁盘速度差异。
- 他们是否具有相同的 OS/mysql 版本，并且 innodb 是否在两种环境中作为其引擎运行？
答案2
| Sending data | 136.213836 |
看起来您可能遇到了接口饱和或网络问题/限制？
要运行的其他测试：
直觉高清测试（第二意见
hdparm -T）dd if=/dev/zero of=1G bs=1M count=1024
- 凭感觉进行网络测试
scp 1G [email protected]- 或者：使用类似
iftop
正如您在帖子中推测的那样：内存似乎不是问题 - 大部分内存用于缓存，并且没有迹象表明有大量交换。
答案3
肯定存在差异。您确定两台服务器上的数据完全相同吗？或者生产服务器上的某个表可能明显更大？
也许你搜索的方向不对。我会先运行mysqltuner在两个系统上。该脚本将为您提供有关如何调整设置的建议。如果两个系统上的设置完全相同，它应该会为您提供相同或相似的建议。
答案4
好的，在此问题出现后不久，我们的服务器就离线了，并且我们被告知磁盘阵列已损坏 - 更换了新的硬盘并重新安装了我们的 Web 应用程序 - 一切都恢复正常运行，性能与开发系统相似。