由于一些性能问题,我一直在优化几个SQL查询并向某些表/列添加索引以加快速度。
一直在使用 PHP 中的 microtime()
运行一些时间测试(循环查询几百次并在每个循环中调用RESET QUERY CACHE
)。我对执行 3 件事的函数之一的结果感到有些困惑:
- 在
sessions
表中插入一行 (InnoDB)。 - 更新
users
表中的行 (InnoDB)。 - 将会话 ID 发送到远程服务器,远程服务器将会话 ID 插入到它自己的会话表 (MongoDB) 中。
30 毫秒和步骤 3. 7 - 20 毫秒
我尝试查找MySQL的一些预期查询时间,但没有找到任何有用的东西,所以我不知道会发生什么。话虽如此,这些查询时间似乎有些高,我肯定不希望 Web 请求比对本地数据库的 MySQL 查询更快地完成。
知道与 Web 请求相比,这些查询时间是否合理吗?
SQL/系统信息
两个服务器(远程服务器和带有MySQL数据库的服务器)都是虚拟服务器,运行在同一具有共享存储(多个SSD raid destup)的物理服务器上。远程服务器分配了一个 CPU 和 2 GB RAM,MySQL 服务器分配了 8 个 CPU 和 32 GB RAM。两台服务器位于同一 LAN 上。
sessions
插入查询:
INSERT INTO sessions (
session_id,
user_id,
application,
machine_id,
user_agent,
ip,
method,
created,
last_active,
expires
)
VALUES (
string, // session_id
int, // user_id
string, // application
string, // machine_id
string, // user_agent
string, // ip
string, // method
CURRENT_TIMESTAMP, // created
CURRENT_TIMESTAMP, // last_active
NULL / FROM_UNIXTIME([PHP timestamp]) // expires
)
sessions
表(包含 ~500'000 行);
+-------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+----------------+
| sessions_id | int(11) | NO | PRI | NULL | auto_increment |
| session_id | char(32) | NO | UNI | NULL | |
| user_id | int(11) | NO | MUL | NULL | |
| application | varchar(128) | NO | | NULL | |
| machine_id | varchar(36) | NO | | NULL | |
| user_agent | varchar(1024) | NO | | NULL | |
| ip | varchar(15) | NO | | NULL | |
| method | varchar(20) | NO | | NULL | |
| created | datetime | NO | | NULL | |
| last_active | datetime | NO | | NULL | |
| expires | datetime | YES | MUL | NULL | |
+-------------+---------------+------+-----+---------+----------------+
users
更新查询:
UPDATE users
SET last_active = string // For example '2016-01-01 00:00:00'
WHERE user_id = int
users
表(包含 ~200'000 行):
+------------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | NULL | |
| first_name | varchar(256) | NO | | NULL | |
| last_name | varchar(256) | NO | | NULL | |
| info | varchar(512) | NO | | NULL | |
| address1 | varchar(512) | NO | | NULL | |
| address2 | varchar(512) | NO | | NULL | |
| city | varchar(256) | NO | | NULL | |
| zip_code | varchar(128) | NO | | NULL | |
| state | varchar(256) | NO | | NULL | |
| country | varchar(128) | NO | | NULL | |
| locale | varchar(5) | NO | | NULL | |
| phone | varchar(128) | NO | | NULL | |
| email | varchar(256) | NO | MUL | NULL | |
| password | char(60) | NO | MUL | NULL | |
| permissions | bigint(20) unsigned | NO | | 0 | |
| created | datetime | YES | | NULL | |
| last_active | datetime | YES | | NULL | |
+------------------------+---------------------+------+-----+---------+----------------+
似乎问题只是我们的MySQL设置(它们都是默认的)。
我在users
更新查询上运行了MySQL配置文件,发现步骤query end
占用了执行查询所花费的大部分时间。
谷歌搜索导致我 https://stackoverflow.com/a/12446986/736247 - 而不是直接使用所有建议的值(不推荐,因为其中一些可能会对数据完整性产生不利影响),我找到了更多信息,包括Percona上的这个页面:https://www.percona.com/blog/2013/09/20/innodb-performance-optimization-basics-updated/。
InnoDB启动选项和系统变量也很有用:http://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html 也很有用。
我最终为以下设置设置了新值:
-
innodb_flush_log_at_trx_commit
-
innodb_flush_method
-
innodb_buffer_pool_size
-
innodb_buffer_pool_instances
-
innodb_log_file_size
这导致查询时间明显缩短(测量方式与我在问题中所做的相同):
- 在
sessions
表中插入一行:~8 毫秒(从 30-40 毫秒向下)。 - 更新
users
表中的行:~2.5 毫秒(从 20-30 毫秒下降)。