mysql-optimize查询(limit，offset，join)>2500万行

2j4z5cfb  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(290)

我想从过去拍卖的1000个最受欢迎的拍品中选出100个（随机）。我有一个mysql数据库，所有的表都使用innodb。
硬件：intel i7 6700，32 gb ram，ssd（不知道iops有多少）。
关于my.cnf的一些信息：

innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

# mysqltuner

query_cache_type    = 1
join_buffer_size    = 256K
tmp_table_size      = 32M
max_heap_table_size = 32M
innodb_buffer_pool_size = 11G

mysql调谐器输出：

>>  MySQLTuner 1.6.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.7.24-0ubuntu0.16.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in InnoDB tables: 28G (Tables: 456)
[!!] Total fragmented tables: 18

-------- Security Recommendations  -------------------------------------------
[OK] There is no anonymous account in all database users
[OK] All database users have passwords assigned
[--] There is 605 basic passwords in the list.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18d 5h 43m 18s (80M q [51.384 qps], 827K conn, TX: 539B, RX: 28B)
[--] Reads / Writes: 58% / 42%
[--] Binary logging is disabled
[--] Total buffers: 11.3G global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 11.3G (36.31% of installed RAM)
[OK] Maximum possible memory usage: 11.5G (36.76% of installed RAM)
[OK] Slow queries: 0% (0/80M)
[OK] Highest usage of available connections: 11% (17/151)
[OK] Aborted connections: 0.00%  (11/827974)
[OK] Query cache efficiency: 74.7% (50M cached / 67M selects)
[!!] Query cache prunes per day: 650750
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 744K sorts)
[!!] Temporary tables created on disk: 26% (160K on disk / 593K total)
[OK] Thread cache hit rate: 99% (70 created / 827K connections)
[!!] Table cache hit rate: 0% (416 open / 169K opened)
[OK] Open file limit used: 2% (21/1K)
[OK] Table locks acquired immediately: 100% (624 immediate / 624 locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (3M used / 16M cache)
[OK] Key buffer size / total MyISAM indexes: 16.0M/43.0K
[!!] Read Key buffer hit rate: 77.3% (172 cached / 39 reads)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[!!] InnoDB buffer pool / data size: 11.0G/28.3G
[!!] InnoDB buffer pool instances: 8
[OK] InnoDB Used buffer: 98.87% (712671 used/ 720852 total)
[OK] InnoDB Read buffer efficiency: 99.77% (20288463287 hits/ 20335997890 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 733034 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64:
    Beware that open_files_limit (1024) variable
    should be greater than table_open_cache ( 431)
Variables to adjust:
    query_cache_size (> 16M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)
    table_open_cache (> 431)
    innodb_buffer_pool_size (>= 28G) if possible.
    innodb_buffer_pool_instances(=11)

我用随机化的方法处理随机部分 OFFSET 在php中： $offsetRand = rand(0, 1000-100); 然后将随机化的偏移量附加到sql查询。
这将导致以下查询：

SELECT l.id FROM Auction a 
INNER JOIN Lot l ON a.id = l.auction_id 
WHERE a.startDate < "2018-11-09" 
ORDER BY l.views DESC LIMIT 100 OFFSET 543
``` `Explain` 查询结果：

+----+-------------+-------+------------+-------+-------------------------------+-----------------------+---------+--------------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-------------------------------+-----------------------+---------+--------------------+-------+----------+-----------------------------------------------------------+
| 1 | SIMPLE | a | NULL | range | PRIMARY,auction_startDate_idx | auction_startDate_idx | 5 | NULL | 33864 | 100.00 | Using where; Using index; Using temporary; Using filesort |
| 1 | SIMPLE | l | NULL | ref | IDX_33CC6FFB57B8F0DE | IDX_33CC6FFB57B8F0DE | 5 | lotsearch_new.a.id | 320 | 100.00 | NULL |
+----+-------------+-------+------------+-------+-------------------------------+-----------------------+---------+--------------------+-------+----------+-----------------------------------------------------------+

表 `Auction` ：70000行
表 `Lot` ：2700万行
指数 `Lot` 表格：

+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lot | 0 | PRIMARY | 1 | id | A | 23945704 | NULL | NULL | | BTREE | | |
| lot | 0 | UNIQ_33CC6FFB989D9B62 | 1 | slug | A | 23945900 | NULL | NULL | YES | BTREE | | |
| lot | 1 | IDX_33CC6FFB57B8F0DE | 1 | auction_id | A | 74748 | NULL | NULL | YES | BTREE | | |
| lot | 1 | lot_visible_idx | 1 | visible | A | 1 | NULL | NULL | | BTREE | | |
| lot | 1 | lot_hammerPrice_idx | 1 | hammerPrice | A | 59499 | NULL | NULL | YES | BTREE | | |
| lot | 1 | lot_views_idx | 1 | views | A | 3440 | NULL | NULL | YES | BTREE | | |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

指数 `Auction` 表格：

+---------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| auction | 0 | PRIMARY | 1 | id | A | 67729 | NULL | NULL | | BTREE | | |
| auction | 0 | UNIQ_1159CC0FED9CD316 | 1 | md5Image_id | A | 57263 | NULL | NULL | YES | BTREE | | |
| auction | 1 | IDX_1159CC0F38248176 | 1 | currency_id | A | 24 | NULL | NULL | YES | BTREE | | |
| auction | 1 | IDX_1159CC0F47EE7BD5 | 1 | auctionhouse_id | A | 752 | NULL | NULL | YES | BTREE | | |
| auction | 1 | IDX_1159CC0F8BAC62AF | 1 | city_id | A | 100 | NULL | NULL | YES | BTREE | | |
| auction | 1 | auction_visible_idx | 1 | visible | A | 1 | NULL | NULL | | BTREE | | |
| auction | 1 | auction_startDate_idx | 1 | startDate | A | 8810 | NULL | NULL | | BTREE | | |
+---------+------------+-----------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

表 `Lot` :

CREATE TABLE Lot (
id int(11) NOT NULL AUTO_INCREMENT,
auction_id int(11) DEFAULT NULL,
title varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
description longtext COLLATE utf8mb4_unicode_ci,
visible tinyint(1) NOT NULL DEFAULT '1',
url varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
number varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
created datetime NOT NULL,
views int(11) DEFAULT '0',
startPrice bigint(20) NOT NULL DEFAULT '0',
endPrice bigint(20) DEFAULT NULL,
hammerPrice bigint(20) DEFAULT NULL,
trained tinyint(1) NOT NULL DEFAULT '0',
classifiedByCategory tinyint(1) NOT NULL DEFAULT '0',
goldStandard tinyint(1) NOT NULL DEFAULT '0',
slug varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY UNIQ_33CC6FFB989D9B62 (slug),
KEY IDX_33CC6FFB57B8F0DE (auction_id),
KEY lot_visible_idx (visible),
KEY lot_hammerPrice_idx (hammerPrice),
KEY lot_views_idx (views),
KEY id (id,auction_id,views),
CONSTRAINT FK_33CC6FFB57B8F0DE FOREIGN KEY (auction_id) REFERENCES Auction (id) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=39363610 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

桌面拍卖：

CREATE TABLE Auction (
id int(11) NOT NULL AUTO_INCREMENT,
currency_id int(11) DEFAULT NULL,
auctionhouse_id int(11) DEFAULT NULL,
title varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
startDate datetime NOT NULL,
endDate datetime DEFAULT NULL,
created datetime NOT NULL,
visible tinyint(1) NOT NULL,
url varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
priceType smallint(6) NOT NULL COMMENT '0: Aufrufpreis | 1: Schätzpreis | 2: Limitpreis; Standard: 0',
identifier varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
address varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
viewing longtext COLLATE utf8mb4_unicode_ci,
useLocalImages tinyint(1) NOT NULL DEFAULT '0',
md5Image_id int(11) DEFAULT NULL,
city_id int(11) DEFAULT NULL,
importedViaApi tinyint(1) NOT NULL DEFAULT '0',
salecode varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY UNIQ_1159CC0FED9CD316 (md5Image_id),
KEY IDX_1159CC0F38248176 (currency_id),
KEY IDX_1159CC0F47EE7BD5 (auctionhouse_id),
KEY IDX_1159CC0F8BAC62AF (city_id),
KEY auction_visible_idx (visible),
KEY auction_startDate_idx (startDate),
CONSTRAINT FK_1159CC0F38248176 FOREIGN KEY (currency_id) REFERENCES Currency (id),
CONSTRAINT FK_1159CC0F47EE7BD5 FOREIGN KEY (auctionhouse_id) REFERENCES Auctionhouse (id) ON DELETE CASCADE,
CONSTRAINT FK_1159CC0F8BAC62AF FOREIGN KEY (city_id) REFERENCES City (id),
CONSTRAINT FK_1159CC0FED9CD316 FOREIGN KEY (md5Image_id) REFERENCES AuctionMd5Image (id) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=116337 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

fk约束： `Lot.auction_id` <=>  `Auction.id` （待优化查询只需要fk）
此查询未在合理时间（<500ms）内完成，它将运行分钟。如果我执行 `SELECT id FROM Auction WHERE startDate < :date;` 查询在80毫秒内完成。如果我执行 `SELECT id FROM Lot ORDER BY views DESC LIMIT 100 OFFSET 50` 查询也会在20毫秒内完成，所以连接操作似乎需要很长时间。
那么，为什么与连接结合在一起时会这么慢呢？我怎样才能加快查询速度？
sqlmysqlquery-optimizationsqlperformance

1条答案

按热度按时间
mzillmmw

mzillmmw1#

SELECT *
    FROM (
        SELECT ...
            LIMIT 1000
         ) AS x
    ORDER BY RAND()
    LIMIT 100

子查询将首先获得1000个“最多浏览量”。外部查询将从1000中随机选取100。
外部查询对于您来说“足够快”，所以我们需要关注内部查询，因为它似乎需要两个表之间的混乱连接？你提到了“最受欢迎”，然后你提到了一些关于“早于：日期”。请更新您的问题一个有效的查询，将找到1000。或许我们可以帮你优化一下。
请提供 SHOW CREATE TABLE 对于表。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com