mysql - 通过大量连接提高 MySQL 查询速度

Question

我在 MySQL 中有一个使用多个连接的查询，它目前运行缓慢 - 平均需要大约 35 秒才能运行。

查询是:

SELECT t.id,
       CASE t.emp_accepted
           WHEN '1' THEN 'No'
           WHEN '0' THEN 'Yes'
       END AS accepted,
       e.department,
       e.works_id,
       e.first_name,
       e.sur_name,
       e.job_title,
       e.job_status,
       e.site_id,
       e.manager,
       d1.department_name AS dept_name,
       d2.department_name AS sub_dept_name,
       temp_hours_worked.hours AS hours,
       s.office_name AS site_name,
       CONCAT(e2.first_name, ' ', e2.sur_name) AS manager_name,
       CONCAT(e3.first_name, ' ', e3.sur_name) AS validated_by
FROM time t
LEFT JOIN employee e
    ON t.employee_id = e.employee_id
LEFT JOIN departments d1
    ON e.department = d1.id
LEFT JOIN departments d2
    ON e.sub_department = d2.id
LEFT JOIN site s
    ON e.site_id = s.id
LEFT JOIN employee e2
    ON e.manager = e2.id
LEFT JOIN employee e3
    ON t.manager_id = e3.id
LEFT JOIN temp_hours_worked
    ON temp_hours_worked.week_beginning = t.week_beginning
        AND temp_hours_worked.employee_id = t.employee_id
        AND temp_hours_worked.company_id=?
WHERE t.company_id = ?;

解释:

+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+
| id | select_type | table             | type   | possible_keys | key         | key_len | ref                                     | rows | Extra |
+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+
|  1 | SIMPLE      | t                 | ref    | company_id    | company_id  | 4       | const                                   | 5566 |       |
|  1 | SIMPLE      | e                 | ref    | employee_id   | employee_id | 4       | DBNAME.t.employee_id                    |    1 |       |
|  1 | SIMPLE      | d1                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.department                     |    1 |       |
|  1 | SIMPLE      | d2                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.sub_department                 |    1 |       |
|  1 | SIMPLE      | s                 | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.site_id                        |    1 |       |
|  1 | SIMPLE      | e2                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.e.manager                        |    1 |       |
|  1 | SIMPLE      | e3                | eq_ref | PRIMARY       | PRIMARY     | 4       | DBNAME.t.manager_id                     |    1 |       |
|  1 | SIMPLE      | temp_hours_worked | ref    | company_id    | company_id  | 4       | const                                   | 5566 |       |
+----+-------------+-------------------+--------+---------------+-------------+---------+-----------------------------------------+------+-------+

MySQL 版本是 5.5.31，在 Centos 6.5 64 位上运行，服务器是 8 核，4GB 内存和 SSD 磁盘。盒子上的平均负载是:

load average: 0.24, 0.29, 0.29

空闲内存显示为:

             total       used       free     shared    buffers     cached
Mem:          3880       3067        813          0        177       1065
-/+ buffers/cache:       1825       2055
Swap:         1023          0       1023

磁盘空间还可以:

Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       45G   11G   32G  26% /
tmpfs           1.9G     0  1.9G   0% /dev/shm
/usr/tmpDSK    1008M   51M  907M   6% /tmp

hdparm -Tt/dev/xvda1 的输出

 Timing cached reads:   12538 MB in  1.99 seconds = 6297.76 MB/sec
 Timing buffered disk reads: 826 MB in  3.00 seconds = 275.27 MB/sec

我的.cnf:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/mysql.sock

[mysqld]


local-infile=0

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/mysql.sock
pid_file                       = /var/lib/mysql/mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
#sql_mode                      = NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
#innodb_strict_mode            = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0

max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 4096

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1456M

# LOGGING #
log_error                      = /var/lib/mysql/mysql-error.log
#log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/mysql-slow.log

被查询/连接的列都是必需的，不能删除，我意识到很多列上没有索引，但因为它们只是单行，我不确定这是否重要 - 还有什么我可以做的吗如何加快查询速度？

Answer 1

这个查询在那些硬件规范上永远不会那么慢。 explain 输出表明所有连接的字段都使用了非常优化的索引，并且只扫描了 5566 行。唯一的索引改进可能是 temp_hours_worked 字段 week_beginning, employee_id, company_id 的组合索引，但这永远不会有太大区别。根据解释输出，甚至没有任何文件排序或临时表。

我怀疑你要么遇到了锁定问题(你显示的负载很低，但没有说明有多少同时查询在这些相同的表上运行)或者你的 MySQL 的配置令人难以置信地不足(使用默认 tiny.config 设置或类似的)。

要检查的事情:

• 使用 hdparm -Tt/dev/sdX 测试驱动器性能 - SSD 磁盘或 RAID 阵列可能已损坏
• 检查您的性能设置。不要犹豫，将所有 buffer 设置放在 my.cnf 中，至少是当前值的两倍，这样你就有 RAM 了。一些可能需要非常高的设置。类似 MySQLTuner 的脚本可能对此有所帮助。

同时检查该问题是否可在另一台服务器上重现。

增加 MySQL 缓冲区值的一个良好开端是将此位添加到您的 my.cnf:

key_buffer = 768M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
query_cache_size = 128M
thread_concurrency = 16
table_open_cache = 2048
tmp_table_size = 64M
max_heap_table_size = 64M

您可以在 phpMyAdmin(服务器 -> 变量)中查看当前值。