php - MySQL 中较慢的 UPDATE 语句

Question

我最近将我的网站移到了另一台服务器上，当我运行一个包含大量 UPDATE 语句的脚本时，我看到的结果非常缓慢。

OLD_SERVER:Intel(R) Xeon(R) CPU E5-2650L 0 @ 1.80GHz，8 核和 1.500 MB RAM

服务器:Intel(R) Core(TM) i7-4770 CPU @ 3.40GHz 8 核和 32GB RAM + RAID 中的 2 SSD(比旧的好 10 倍)

** php脚本代码**

$startTime = microtime(true);  

// change this with a mysql query           
$update_result = mysql_query("
UPDATE some_table 
SET order_id = ".$random_order_id."
WHERE id = ".$row_get['id']."
");

// Your content to test
$endTime = microtime(true);  
$elapsed = $endTime - $startTime;
echo "Execution time : $elapsed seconds\n";

if(!$update_result)
{
    return array(
    "result" => false,
    "code" => 502,
    "reason" => "SQL Update error"
    );
}

** OLD_SERVER 脚本输出(非常快):**

306497 will have order_id = 49438<br/>
Execution time : 0.00071907043457031 seconds
306505 will have order_id = 113556<br/>
Execution time : 0.00055885314941406 seconds
306508 will have order_id = 295573<br/>
Execution time : 0.00074100494384766 seconds
306511 will have order_id = 206028<br/>
Execution time : 0.00042295455932617 seconds
306518 will have order_id = 241993<br/>
Execution time : 0.00048589706420898 seconds

iotop renders 10MB/sec

** NEW_SERVER 脚本输出(非常慢):**

10995 will have order_id = 94532<br/>
Execution time : 0.030339956283569 seconds
11021 will have order_id = 158848<br/>
Execution time : 0.060288906097412 seconds
11035 will have order_id = 288621<br/>
Execution time : 0.030526876449585 seconds
11059 will have order_id = 194945<br/>
Execution time : 0.031852960586548 seconds
11089 will have order_id = 176289<br/>
Execution time : 0.030807018280029 seconds
11102 will have order_id = 80207<br/>
Execution time : 0.059854984283447 seconds
11147 will have order_id = 33899<br/>
Execution time : 0.030609846115112 seconds
11392 will have order_id = 124314<br/>
Execution time : 0.031843900680542 seconds
11541 will have order_id = 249986<br/>

iotop renders 300KB/sec

**/etc/mysql/my.cnf **

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address       = 127.0.0.1
#bind-address       = 148.251.126.39
#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#

query_cache_limit   = 1M
query_cache_size        = 256M

# query_cache_size=0

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id      = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/


innodb_log_file_size = 1G
innodb_io_capacity = 20000
innodb_read_io_threads = 5000
innodb_write_io_threads = 5000

问题出在哪里？？我不明白。

谢谢。

Answer 1

请先检查索引，使用

SHOW INDEXES FROM some_table;

在两台服务器上，查看旧服务器的id字段是否有索引。很明显，您已经升级了服务器配置，所以问题一定出在复制上。