php - 如何将 Laravel SQl 查询时间保持在 5 秒以下

Question

我的 php 应用程序使用 laravel 4.1.31。对于数据库查询，它通过ajax使用knockout js库。当数据库变大时，ajax加载就成了问题，因为加载时间太长，有时会中途停止。我不熟悉这些库，所以我无法进行任何应用程序级别的优化。

我希望我仍然可以对数据库查询进行优化，并将每个查询控制在 5 秒以内。

以下是我可以分享的一些信息:

内存:2GB 1core Debian 9。单个 Innodb 数据库。

慢查询日志:

# Time: 190611  7:49:08
# User@Host: user[user] @ localhost []
# Thread_id: 690728  Schema: user  QC_hit: No
# Query_time: 9.343611  Lock_time: 0.000030  Rows_sent: 100  Rows_examined: 440481
# Rows_affected: 0
use user;
SET timestamp=1560239348;
select * from `titles` where `titles`.`type` = 'movie'
       order by `tmdb_rating` desc limit 100 offset 86500;

显示创建表标题；

CREATE TABLE `titles` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `type` enum('movie','series') COLLATE utf8_unicode_ci DEFAULT NULL,
 `imdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tmdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mc_user_score` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mc_critic_score` smallint(5) unsigned DEFAULT NULL,
 `mc_num_of_votes` int(10) unsigned DEFAULT NULL,
 `imdb_votes_num` bigint(20) unsigned DEFAULT NULL,
 `release_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `year` smallint(5) unsigned DEFAULT NULL,
 `plot` text COLLATE utf8_unicode_ci,
 `genre` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tagline` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `poster` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `awards` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `runtime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `trailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `budget` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `revenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `views` bigint(20) NOT NULL DEFAULT '1',
 `tmdb_popularity` float(50,2) unsigned DEFAULT NULL,
 `imdb_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tmdb_id` bigint(20) unsigned DEFAULT NULL,
 `season_number` tinyint(3) unsigned DEFAULT NULL,
 `fully_scraped` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `allow_update` tinyint(3) unsigned NOT NULL DEFAULT '1',
 `featured` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `now_playing` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL DEFAULT NULL,
 `temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `original_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `affiliate_link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `custom_field` text COLLATE utf8_unicode_ci,
 PRIMARY KEY (`id`),
 UNIQUE KEY `titles_imdb_id_unique` (`imdb_id`),
 UNIQUE KEY `titles_tmdb_id_type_unique` (`tmdb_id`,`type`),
 KEY `titles_mc_num_of_votes_index` (`mc_num_of_votes`),
 KEY `titles_created_at_index` (`created_at`),
 KEY `titles_release_date_index` (`release_date`),
 KEY `titles_title_index` (`title`),
 KEY `titles_mc_user_score_index` (`mc_user_score`),
 KEY `titles_tmdb_popularity_index` (`tmdb_popularity`),
 KEY `titles_temp_id_index` (`temp_id`),
 KEY `titles_tmdb_rating_index` (`tmdb_rating`)
) ENGINE=InnoDB AUTO_INCREMENT=18712721 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

解释 select * from titles where titles.type = 'movie' order by tmdb_rating desc limit 100 偏移量 86500;

+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
|    1 | SIMPLE      | titles | ALL  | NULL          | NULL | NULL    | NULL | 311859 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.01 sec)

my.cnf 设置:

default-storage-engine = InnoDB
symbolic-links=0
skip-external-locking
max_allowed_packet = 16M
table_open_cache = 5000
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 4
tmp_table_size = 256M
max_heap_table_size = 256M
performance_schema = ON
key_buffer_size = 140k
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256k
myisam_sort_buffer_size = 140k
join_buffer_size = 2M

innodb_file_per_table
innodb_buffer_pool_size = 512M
innodb_log_file_size = 200M
innodb_buffer_pool_instances = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 32M
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
sync_binlog = 1

max_connections=100
max_user_connections=100
wait_timeout=10
interactive_timeout=30
long_query_time=5
slow-query-log =1
slow-query-log-file = /var/log/mysql/mysql-slow.log

内存使用情况:

Private  +   Shared  =  RAM used       Program

224.0 KiB +  26.5 KiB = 250.5 KiB       agetty (2)
248.0 KiB +  41.0 KiB = 289.0 KiB       sftp-server
180.0 KiB + 124.0 KiB = 304.0 KiB       anvil
404.0 KiB +  31.5 KiB = 435.5 KiB       vsftpd
392.0 KiB + 104.5 KiB = 496.5 KiB       dovecot
564.0 KiB +  39.5 KiB = 603.5 KiB       memcached
484.0 KiB + 122.0 KiB = 606.0 KiB       log
828.0 KiB +  44.0 KiB = 872.0 KiB       systemd-udevd
968.0 KiB +  42.0 KiB =   1.0 MiB       ntpd
732.0 KiB + 307.0 KiB =   1.0 MiB       systemd-logind
684.0 KiB + 355.0 KiB =   1.0 MiB       sh (8)
340.0 KiB + 754.0 KiB =   1.1 MiB       vesta-nginx (2)
  1.0 MiB +  58.5 KiB =   1.1 MiB       dbus-daemon
880.0 KiB + 250.5 KiB =   1.1 MiB       auth
760.0 KiB + 496.0 KiB =   1.2 MiB       flock (6)
  1.2 MiB + 244.0 KiB =   1.4 MiB       config
  1.7 MiB + 205.5 KiB =   1.9 MiB       systemd-journald
  1.9 MiB +  47.0 KiB =   1.9 MiB       rsyslogd
  2.1 MiB + 103.0 KiB =   2.2 MiB       exim4
  2.5 MiB +  76.0 KiB =   2.6 MiB       bash
  2.5 MiB +   1.3 MiB =   3.8 MiB       cron (9)
  2.2 MiB +   2.1 MiB =   4.3 MiB       sshd (4)
  2.2 MiB +   2.1 MiB =   4.4 MiB       nginx (3)
500.0 KiB +   4.8 MiB =   5.2 MiB       vesta-php (3)
  4.3 MiB +   3.7 MiB =   7.9 MiB       systemd (5)
 15.2 MiB + 109.5 KiB =  15.3 MiB       fail2ban-server
 67.3 MiB +  15.4 MiB =  82.6 MiB       php7.0 (8)
100.9 MiB +  67.2 MiB = 168.1 MiB       apache2 (12)
758.7 MiB + 156.0 KiB = 758.9 MiB       mysqld

TOP 命令:

top - 14:10:43 up 7 days, 23:12,  1 user,  load average: 0.79, 0.91, 0.69
Tasks: 145 total,   4 running, 141 sleeping,   0 stopped,   0 zombie
%Cpu(s): 19.1 us,  6.0 sy,  0.0 ni, 71.9 id,  2.7 wa,  0.0 hi,  0.3 si,  0.0 st
KiB Mem :  2052588 total,   501496 free,  1085916 used,   465176 buff/cache
KiB Swap:        0 total,        0 free,        0 used.   735980 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND
23688 mysql     20   0 1211616 770288      0 S  7.3 37.5 543:04.35 mysqld
29889 admin     20   0  539584  53996  38624 R  3.7  2.6   0:00.74 apache2
27140 www-data  20   0  537900  64576  50864 S  3.0  3.1   0:10.99 apache2
29901 admin     20   0  539484  53356  38100 S  2.3  2.6   0:00.71 apache2
29972 admin     20   0  538404  46336  32220 S  2.3  2.3   0:00.24 apache2
29297 admin     20   0  539572  64000  48648 S  2.0  3.1   0:02.00 apache2
30084 www-data  20   0  536388  43876  31700 S  1.3  2.1   0:00.24 apache2
30063 admin     20   0  392344  34440  21948 S  1.0  1.7   0:00.54 php
30042 admin     20   0  311432  30956  21628 S  0.7  1.5   0:00.10 php
30347 root      20   0   44888   3616   3016 R  0.7  0.2   0:00.04 top
    7 root      20   0       0      0      0 R  0.3  0.0   8:52.43 rcu_sched
 4834 root      20   0       0      0      0 S  0.3  0.0   0:01.46 kworker/u2:2
23741 memcache  20   0  335680    424      0 S  0.3  0.0   2:41.11 memcached
29002 www-data  20   0  537740  70296  56792 S  0.3  3.4   0:03.03 apache2
29279 www-data  20   0  537504  59164  45840 S  0.3  2.9   0:01.75 apache2
29709 root      20   0       0      0      0 S  0.3  0.0   0:00.07 kworker/0:0
    1 root      20   0  204556   4732   3076 S  0.0  0.2   0:21.00 systemd
    2 root      20   0       0      0      0 S  0.0  0.0   0:00.01 kthreadd
    3 root      20   0       0      0      0 R  0.0  0.0   8:12.40 ksoftirqd/0

另一个慢查询示例:

# Query_time: 21.742335  Lock_time: 0.000042  Rows_sent: 0  Rows_examined: 354027
# Rows_affected: 0
SET timestamp=1560293586;
select * from `titles`
    where `titles`.`type` = 'movie'
      and ( select count(*)
            from `actors`
            inner join `actors_titles` on `actors`.`id` = `actors_titles`.`actor_id`
            where `actors_titles`.`title_id` = `titles`.`id`
              and `name` like 'teren' 
          ) >= 1
    order by `mc_num_of_votes` asc
    limit 100 offset 0;

Answer 1

呃:限制 100 偏移量 86500

不要那样做分页。而是“记住你离开的地方”。这样做的缺点是没有提供“跳转到第 864 页”，但是谁来做。谁会 Next-next-next... 865 次？

关于这个常见问题的进一步讨论:http://mysql.rjweb.org/doc.php/pagination

它更详细地讨论了“停止”的解决方案。

另一个潜在的问题:order by tmdb_rating desc -- 也许多个标题具有相同的“评级”？如果是这样，您希望以什么顺序列出标题？简单的答案是指定一些明确的(但有些武断的)顺序:order by tmdb_rating desc, id desc。

记住您在复合排序方式中离开的地方更复杂，但也是可能的。

其他说明:

• WHERE type=... ORDER BY rating 可以按此顺序从“复合”INDEX(type, rating) 中受益。

• 3 个唯一键听起来不对。

• 2 TEXT 列被提取会影响性能。除非您确实需要所有列，否则不要执行 SELECT *。

• tmp_table_size = 256M 和 max_heap_table_size = 256M 对于 2GB 的 RAM 来说是危险的高值。将它们缩小到仅占 RAM 的 1%。

• 代替 ( SELECT COUNT(*) FROM...) >=1，执行 EXISTS ( SELECT 1 FROM ...)