如何在5秒内保持Laravel SQl查询时间

已采纳

My php application uses laravel 4.1.31. For database queries, it uses knockout js library through ajax. When the database size grows larger, the ajax loading becomes a problem because it takes too long and sometimes stops halfway. I'm not familiar with the libraries so I can't do any application level optimization.

I hope that I can still do optimization on the database query and keep each query under 5 seconds.

Here are some of information I can share:

RAM: 2GB 1core Debian 9. Single Innodb database.

The Slow query log:

# 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;

SHOW CREATE TABLE titles;

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

EXPLAIN select * from titles where titles.type = 'movie' order by tmdb_rating desc limit 100 offset 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 Settings:

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

Memory Usage:

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 Command:

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

Another Slow Query Example:

# 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;

点赞
写回答
关注问题
收藏
复制链接分享
邀请回答

1条回答

采纳
码龄粉丝数

被点赞

被采纳

采纳率

私信
duanjian5059 2年前
Ugh: limit 100 offset 86500

Don't do pagination that way. Instead "remember where you left off". This has the drawback of no providing "jump to page 864", but who does that. And who does Next-next-next... 865 times?

Further discussion about this common problem: http://mysql.rjweb.org/doc.php/pagination

And it discusses the "left off" solution in more detail.

Another potential problem: order by tmdb_rating desc -- Perhaps multiple titles have the same "rating"? If so, in what order do you want the titles listed? The simple answer is to specify some unambiguous (but somewhat arbitrary) order: order by tmdb_rating desc, id desc.

Remembering where you left off in a compound order-by is more complex, but possible.

Other notes:
- WHERE type=... ORDER BY rating can benefit from the "composite" INDEX(type, rating), in this order.
- 3 unique keys sounds wrong.
- 2 TEXT columns being fetched hurts the performance. Don't do SELECT * unless you really need all the columns.
- tmp_table_size = 256M and max_heap_table_size = 256M are dangerously high for a tiny 2GB of RAM. Shrink them to only 1% of RAM.
- Instead of ( SELECT COUNT(*) FROM...) >=1, do EXISTS ( SELECT 1 FROM ...)
点赞评论复制链接分享

提交

为你推荐

如何在5秒内保持Laravel SQl查询时间
- php
- mysql
- 1个回答