我有以下查询,大约需要 5 秒才能完成,以前是 0.5 秒,然后是 1 秒、2 秒,现在我们是 5 秒,尽管数据库中的数据量没有改变!该查询进行选择以获取一部分数据来处理和更新某些列(不添加新行),通常它有一个“限制500”,但“限制1”和“限制500”的查询时间是相同的,所以为了简单起见,我将这篇文章更改为“限制 1”。
我在同一服务器上制作了生产表的副本,以便能够在不损害生产数据的情况下运行各种测试和更改,也有一些数据被混淆,但所有查询及其输出都是真实的
表“test”有 3400 万行,大小 11.3GB,其中索引为 3.3GB。服务器是 Freebsd 13.2,mysql 8.0.35,配备相当不错的 Samsung nvme 驱动器、64GB 内存和 innodb_buffer_pool_size = 42G。服务器不进行交换并且有近 4GB 的可用内存。有相当多的磁盘 IO 负载来自对同一服务器上托管的其他数据库的查询(可能主要是二进制日志,因为这是复制主机),但是如果我禁用所有其他查询并仅运行有问题的查询,则会缩短执行时间是完全一样的。
慢速查询:
SELECT t_channel_id,login FROM test WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+--------------+---------------+
| t_channel_id | login |
+--------------+---------------+
| 933271419 | sunday1funday |
+--------------+---------------+
1 row in set (4.43 sec)
索引正确使用并覆盖所有 WHERE 列,如果我从查询中删除列videos_scanned_lock,则时间为 0.35 秒(仍然很慢,但快得多),如果我从查询中删除仅列 t_channel_id 时间为 0 秒
我有一个理论,为什么一开始时间要快得多,现在大约是 5 秒。我们现在正在处理大约第 3000 万行记录(共 34 行),一开始选定的行来自表的开头,现在就像“几乎必须首先扫描整个表才能获得结果”。但是3400万条记录对于mysql来说很多吗?我想不是,整个表小于 innodb 缓冲区大小的 1/4。作为这个理论的证明,在上一段中我写道,如果我从查询中删除列 t_channel_id,时间为 0 秒 - 这是因为如果我删除此列,则会显示从表开头开始的结果。第二个证明是查询给出了一定的结果,如果我将此结果的 t_channel_id 值更改为更小的数字,则查询时间将会短得多(因为 ID 较低,会更早找到它)。我很确定我回答了自己的问题,但为什么会发生这种情况?我相信某些东西一定配置错误,但我找不到它是什么,我希望在这里找到答案。任何改善这种情况的建议都值得赞赏:)
下面我提供调试数据:show create table、profiler、describeanalysis和my.cnf
SHOW CREATE TABLE test;
CREATE TABLE `test` (
`channel_id` bigint NOT NULL AUTO_INCREMENT,
`t_channel_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_bin DEFAULT NULL,
`login` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`display_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`videos_scanned` datetime DEFAULT NULL,
`videos_scanned_lock` int NOT NULL DEFAULT '0',
PRIMARY KEY (`channel_id`) USING BTREE,
UNIQUE KEY `login` (`login`),
KEY `t_channel_id_2` (`t_channel_id`,`videos_scanned`,`videos_scanned_lock`)
) ENGINE=InnoDB AUTO_INCREMENT=34626837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
describe SELECT t_channel_id,login FROM test WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+----------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+----------+----------+-----------------------+
| 1 | SIMPLE | test | NULL | range | t_channel_id_2 | t_channel_id_2 | 1023 | NULL | 16992061 | 1.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+----------+----------+-----------------------+
describe analyze SELECT t_channel_id,login FROM test WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit
1;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Limit: 1 row(s) (cost=7.65e+6 rows=1) (actual time=4407..4407 rows=1 loops=1)
-> Index range scan on test using t_channel_id_2 over (NULL < t_channel_id), with index condition: ((test.videos_scanned_lock = 0) and (test.t_channel_id is not null) and (test.videos_scanned is null)) (cost=7.65e+6 rows=17e+6) (actual time=4407..4407 rows=1 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (4.41 sec)
SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SELECT t_channel_id,login FROM test WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+--------------+---------------+
| t_channel_id | login |
+--------------+---------------+
| 933271419 | sunday1funday |
+--------------+---------------+
1 row in set (4.43 sec)
show profile cpu;
+--------------------------------+----------+----------+------------+
| Status | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting | 0.000142 | 0.000372 | 0.000036 |
| Executing hook on transaction | 0.000063 | 0.000048 | 0.000004 |
| starting | 0.000027 | 0.000022 | 0.000002 |
| checking permissions | 0.000028 | 0.000025 | 0.000003 |
| checking permissions | 0.000018 | 0.000016 | 0.000001 |
| Opening tables | 0.000098 | 0.000094 | 0.000009 |
| init | 0.000025 | 0.000019 | 0.000002 |
| System lock | 0.000026 | 0.000024 | 0.000002 |
| optimizing | 0.000091 | 0.000084 | 0.000008 |
| statistics | 0.000284 | 0.000269 | 0.000026 |
| preparing | 0.000126 | 0.000119 | 0.000011 |
| executing | 4.426675 | 6.156588 | 0.156978 |
| end | 0.000016 | 0.000005 | 0.000001 |
| query end | 0.000004 | 0.000003 | 0.000000 |
| waiting for handler commit | 0.000005 | 0.000005 | 0.000001 |
| closing tables | 0.000006 | 0.000006 | 0.000000 |
| freeing items | 0.000010 | 0.000014 | 0.000002 |
| logging slow query | 0.000027 | 0.000020 | 0.000001 |
| cleaning up | 0.000008 | 0.000007 | 0.000001 |
+--------------------------------+----------+----------+------------+
19 rows in set, 1 warning (0.00 sec)
[mysqld]
user = mysql
port = 3306
socket = /tmp/mysql.sock
bind-address = 0.0.0.0
datadir = /var/db/mysql
log-output = FILE
relay-log-recovery = 1
slow-query-log = 1
server-id = 29
sync_binlog = 1
sync_relay_log = 1
binlog_cache_size = 16M
binlog_expire_logs_seconds = 43200
default_password_lifetime = 0
enforce-gtid-consistency = 1
gtid-mode = On
safe-user-create = 1
lower_case_table_names = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options = BACKUP,FORCE
table_open_cache = 16384
table_definition_cache = 8192
net_retry_count = 16384
key_buffer_size = 256M
max_allowed_packet = 64M
long_query_time = 0.2
innodb_buffer_pool_size = 42G
innodb_data_home_dir = /var/db/mysql
innodb_log_group_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:128M:autoextend
innodb_temp_data_file_path = ibtmp1:128M:autoextend
innodb_flush_method = O_DSYNC
innodb_log_buffer_size = 32M
innodb_write_io_threads = 12
innodb_read_io_threads = 12
innodb_autoinc_lock_mode = 2
innodb_redo_log_capacity = 8192M
log_error = /var/db/mysql/mysql-error.log
slow_query_log_file = /var/db/mysql/mysql-slow.log
skip_name_resolve
mysqlx = 0
tls_version = TLSv1.2
innodb_flush_log_at_trx_commit = 0
replica_parallel_workers = 1
binlog_group_commit_sync_delay = 10000
编辑 Ergest:
...
KEY `channel_id` (`videos_scanned_lock`,`channel_id`,`t_channel_id`) USING BTREE
SELECT t_channel_id,login FROM test force index (channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+--------------+----------------+
| t_channel_id | login |
+--------------+----------------+
| 921437844 | marryme2 |
+--------------+----------------+
1 row in set (50.80 sec)
describe SELECT t_channel_id,login FROM test force index (channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+
| 1 | SIMPLE | test | NULL | ref | channel_id | channel_id | 4 | const | 68378 | 9.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)
describe format=json SELECT t_channel_id,login FROM test force index (channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
....
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "2094461.53"
},
"table": {
"table_name": "test",
"access_type": "ref",
"possible_keys": [
"t_channel_id_2",
"channel_id"
],
"key": "channel_id",
"used_key_parts": [
"videos_scanned_lock"
],
"key_length": "4",
"ref": [
"const"
],
"rows_examined_per_scan": 16992061,
"rows_produced_per_join": 1529285,
"filtered": "9.00",
"index_condition": "(`db`.`test`.`t_channel_id` is not null)",
"cost_info": {
"read_cost": "395255.43",
"eval_cost": "152928.54",
"prefix_cost": "2094461.53",
"data_read_per_join": "4G"
},
"used_columns": [
"t_channel_id",
"login",
"videos_scanned",
"videos_scanned_lock"
],
"attached_condition": "(`db`.`test`.`videos_scanned` is null)"
}
}
} |
对于@里克·詹姆斯
它根本不起作用
show create table test;
....
KEY `t_channel_id` (`t_channel_id`,`login`,`videos_scanned`,`videos_scanned_lock`)
SELECT t_channel_id,login FROM test force index (t_channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_loc
k=0 limit 1;
+--------------+----------------+
| t_channel_id | login |
+--------------+----------------+
| 921437844 | blahblahcar |
+--------------+----------------+
1 row in set (9.99 sec)
describe SELECT t_channel_id,login FROM test force index (t_channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | range | t_channel_id | t_channel_id | 1023 | NULL | 16992061 | 0.02 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+----------+----------+--------------------------+
explain format=json SELECT t_channel_id,login FROM test force index (t_channel_id) WHERE t_channel_id IS NOT NULL AND videos_scanned IS NULL AND videos_scanned_lock=0 limit 1;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4712106.82"
},
"table": {
"table_name": "test",
"access_type": "range",
"possible_keys": [
"t_channel_id"
],
"key": "t_channel_id",
"used_key_parts": [
"t_channel_id"
],
"key_length": "1023",
"rows_examined_per_scan": 16992061,
"rows_produced_per_join": 3906,
"filtered": "0.02",
"using_index": true,
"cost_info": {
"read_cost": "4711716.19",
"eval_cost": "390.62",
"prefix_cost": "4712106.82",
"data_read_per_join": "11M"
},
"used_columns": [
"t_channel_id",
"login",
"videos_scanned",
"videos_scanned_lock"
],
"attached_condition": "((`db`.`test`.`videos_scanned_lock` = 0) and (`db`.`test`.`t_channel_id` is not null) and (`db`.`test`.`videos_scanned` is null))"
}
}
PROFILE
很少有任何用处。这个综合指数应该有帮助
简短的解释(针对您的查询):
=
IS NOT NULL
)详细解释:请参阅我的Index Cookbook
(这里没什么令人兴奋的......)
观察结果:
更重要的问题:
建议配置更改:
寻找
SHOW ENGINE=INNODB STATUS
“最新的僵局”。详细信息和其他观察结果:
( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) = (0 + 0 + 25119175 + 81458209 + 8195499 + 80652334) / 400622 = 487 /sec
——眼压?-- 如果硬件可以处理它,请将 innodb_io_capacity (现在为 200)设置为大约这个值。( ( Key_reads + Key_writes + Innodb_pages_read + Innodb_pages_written + Innodb_dblwr_writes + Innodb_buffer_pool_pages_flushed ) / innodb_io_capacity / Uptime ) = ( 0 + 0 + 25119175 + 81458209 + 8195499 + 80652334 ) / 200 / 400622 = 243.9%
-- 这可能是一个衡量 innodb_io_capacity 设置合理的指标。-- 如果硬件可以处理,则增加 innodb_io_capacity (现在为 200)。( table_open_cache ) = 16,384
-- 要缓存的表描述符的数量 -- 通常几百个就足够了。( binlog_cache_size * max_connections ) = (16M * 151) / 65536M = 3.7%
-- RAM 用于缓存传输至二进制日志的事务。-- 减少 binlog_cache_size (现在为 16777216)和/或 max_connections (现在为 151)( innodb_buffer_pool_size / innodb_buffer_pool_instances ) = 43008M / 8 = 5376MB
-- 每个 buffer_pool 实例的大小。-- 实例至少应为 1GB。在非常大的RAM中,有16个实例。( innodb_lru_scan_depth * innodb_page_cleaners ) = 1,024 * 4 = 4,096
-- 页面清理器每秒的工作量。--“InnoDB:page_cleaner:1000ms预期循环花费了...”可以通过降低lru_scan_深度来修复:考虑1000 / innodb_page_cleaners(现在为4)。还要检查是否有交换。( innodb_lru_scan_depth ) = 1,024
-- innodb_lru_scan_depth 是一个命名非常糟糕的变量。更好的名称是 innodb_free_page_target_per_buffer_pool。它是 InnoDB 试图在每个缓冲池实例中保持空闲的页面数量,以加速读取和页面创建操作。--“InnoDB:page_cleaner:1000ms预期循环花费了...”可以通过降低lru_scan_深度来修复( innodb_io_capacity ) = 200
-- 刷新时,使用这么多 IOP。-- 读取可能缓慢或尖刻。如果使用 SSD 驱动器,请使用 2000。( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10
-- 容量:max/plain -- 建议 2。最大值应大约等于 I/O 子系统可以处理的 IOP。(如果驱动器类型未知,2000/200 可能是合理的一对。)( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((19069505 + 80652334) ) / 400622 = 248 /sec
-- InnoDB I/O( Innodb_buffer_pool_pages_flushed ) = 80652334 / 400622 = 201 /sec
-- 写入(刷新)( Innodb_os_log_written ) = 417798258688 / 400622 = 1042873 /sec
-- 这是 InnoDB 繁忙程度的一个指标。-- InnoDB 非常繁忙。( innodb_log_buffer_size / innodb_log_file_size ) = 32M / 48M = 66.7%
-- 缓冲区位于 RAM 中;文件在磁盘上。-- buffer_size 应该更小和/或 file_size 应该更大。( Innodb_log_writes ) = 318280758 / 400622 = 794 /sec
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 417,798,258,688 / (400622 / 3600) / 2 / 48M = 37.3
-- Ratio -- (see minutes)( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 400,622 / 60 * 48M / 417798258688 = 0.804
-- Minutes between InnoDB log rotations Beginning with 5.6.8, innodb_log_file_size can be changed dynamically; I don't know about MariaDB. Be sure to also change my.cnf -- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 50331648). (Cannot change in AWS.)( innodb_flush_method ) = innodb_flush_method = O_DSYNC
-- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT( Innodb_row_lock_time_max ) = 50,981
-- Max time to lock a row (millisec) -- Possibly conflicting queries; possibly table scans.( Innodb_row_lock_waits ) = 1993787 / 400622 = 5 /sec
-- How often there is a delay in getting a row lock. -- May be caused by complex queries that could be optimized.( Innodb_dblwr_writes ) = 8195499 / 400622 = 20 /sec
-- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them. -- (Symptom of other issues)( ( Innodb_pages_read + Innodb_pages_written ) / Uptime / innodb_io_capacity ) = ( 25119175 + 81458209 ) / 400622 / 200 = 133.0%
-- If > 100%, need more io_capacity. -- Increase innodb_io_capacity (now 200) if the drives can handle it.( innodb_io_capacity ) = 200
-- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor. Limits write IO requests per second (IOPS). -- For starters: HDD: 200; SSD: 2000.( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON
-- Whether to use the adapative hash (AHI). -- ON for mostly readonly; OFF for DDL-heavy( Innodb_row_lock_current_waits ) = 590
-- The number of row locks currently being waited for by operations on InnoDB tables. Zero is pretty normal. -- Something big is going on?( innodb_flush_log_at_trx_commit ) = 0
-- 1 = secure; 2 = faster -- (You decide) Use 1, along with sync_binlog (now 1)=1 for the greatest level of fault tolerance. 0 is best for speed. 2 is a compromise between 0 and 1.( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = ON
-- Usually should be ON. -- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890). 10.5.0 decided to default OFF.( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF
-- Whether to log all Deadlocks. -- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.( bulk_insert_buffer_size ) = 8M / 65536M = 0.01%
-- Buffer for multi-row INSERTs and LOAD DATA -- Too small could hinder such operations.( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (218991721 + 3627939 + 0 + 45 + 74927620 + 0) / 400622 = 742 /sec
-- writes/sec -- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives( log_slow_slave_statements ) = log_slow_slave_statements = OFF
-- (5.6.11, 5.7.1) By default, replicated statements won't show up in the slowlog; this causes them to show. -- It can be helpful in the slowlog to see writes that could be interfering with Replica reads.( Aborted_connects / Connections ) = 20,179 / 54134 = 37.3%
-- Perhaps a hacker is trying to break in? (Attempts to connect)Abnormally small:
Abnormally large:
Abnormal strings: