MySQL 表数量多的场景优化

随着数据量的增长,MySQL 单表数据越来越大,单个机器的磁盘空间不足以存储那么大的数据,此时通常会采用分库分表技术,将单表数据按某种规则分割到多个表中。分库分表可能导致的一个结果是库里同时存在很多表,少则上千,多则上百万,处理这样庞大数量的表,其开销将成为一个新的性能影响因素。

1. MySQL 如何打开和关闭表

当执行 mysqladmin status 命令,通常能看到如下输出:

Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12

Open tables 值为 12,这个值可能会让你感到疑惑,因为实际上库中表的数量可能远小于 12。

1.1 表缓存与文件描述符

MySQL 是多线程的,可能有许多客户同时对某个特定表进行查询,因此一个表在多个客户查询时会处于不同的状态,为了使问题简单化,查询同一个表的多个会话会独立地打开表,这会导致使用更多的内存,但通常也会提高性能。对于 MyISAM 表,每个会话打开表的数据文件都需要一个额外的文件描述符,相比之下,索引文件描述符可以在所有会话之间共享。

MySQL 系统变量 table_open_cache 和 max_connections 能够影响服务器上能够打开的文件的最大数量,如果增加这两个变量的值,可能会达到操作系统对每个进程施加的文件描述符限制,当然,很多操作系统允许增加该限制值的上限,在表数量较大的场景下,应当根据操作系统文档,适当增加文件描述符数量上限。

table_open_cache 值与 max_connections 相关,例如 200 并发的连接,将 table_open_cache 设置为 200*N 是比较合适的,其中 N 为 执行的 SQL 中 join 语句涉及的表的数量。当然也必须为临时表和临时文件保留额外的一定数量的文件描述符。

确保操作系统能够处理 table_open_cache 设置的文件描述符的数量,如果 table_open_cache 设置的太大,MySQL 可能会用尽文件描述符,并出现拒绝连接或无法执行查询等现象。

另外值得注意的是 MyISAM 表打时,需要 2 个文件描述符,对于一个分区的 MyISAM 表,其中每个分区都需要 2 个文件描述符,当打开一个 MyISAM 分区表时,不管有没有指定分区,分区表中的所有分区都会被打开,因此其占用的文件描述符将非常巨大。这种场景应当增加 MySQL 可用的文件描述符数量,参数为 open_files_limit。

1.2 表缓存淘汰策略

MySQL 内部表的缓存数量保持在 table_open_cache 级别,MySQL 在启动时自动调整缓存大小,在 MySQL 启动时就应当显式设置 table_open_cache 参数值,在缓存用满时,MySQL 可能会临时打开更多的表来执行查询。

在以下场景,MySQL 关闭一个未使用的表并将其从表缓存中移除。

  • 当缓存已满且线程试图打开不在缓存中的表时。
  • 当缓存的表数量超过 table_open_cache,并且缓存中的表不再被任何线程使用时。
  • 当执行 FLUSH TABLES 命令时。

当表缓存填满时,MySQL 使用以下策略来定位要移除的缓存项:

  • 当前不使用的表被释放,从最近最少使用的表开始。
  • 如果一个新表必须被打开,但是表缓存已满,并且没有表能够被释放,此时表缓存容量能够被临时扩展。临时扩容后,如果某个表进入未使用状态,那么该表将被释放并从表缓存中移除。

MyISAM 表每个并发访问都会打开一次表,这意味着两个线程访问同一张表,那么这张表将会被打开 2 次,或者一个线程在一个查询中访问同一张表 2 次,比如该表和自己做 join 操作,那么该表也会被打开 2 次。每个并发访问,都会在表缓存中占用一个缓存记录。MyISAM 表的第一次打开会占用 2 个文件描述符,一个用于数据文件,一个用于索引文件,此后,对于该表的每一个并发使用,都会占用一个文件描述符,用于数据文件,索引文件在所有并发线程中共享。

当使用 HANDLER tbl_name OPEN 语句打开一个表时,一个专门的表对象被创建,这个表对象不与其他线程分享,除非调用 HANDLER tbl_name CLOSE 或者 线程结束,否则该对象一直处于打开状态。该对象关闭后,如果表缓存没有满,那么该对象将会进入到表缓存中。

1.3 如何设置合适的表缓存大小

如何判断 table_open_cache 设置的太小,可以通过检查状态变量 Opened_tables 来判断,该变量表示 MySQL 启动以来打开表操作的数量。如果这个值很大,并且在没有执行 flush tables 语句的情况下,快速增长,那么就要考虑增加 table_open_cache 值。

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

二、在一个库中创建许多表的弊端

如果在一个数据库中,创建大量的 MyISAM 表,创建表、打开表、关闭表都将变得很慢,如果使用 select 语句查询多张不同的表,当表缓存满了时,查询语句执行会有一些额外的开销,因为对于每一个必须要打开的表,都必须关闭另一个表,当然可以通过增加 table_open_cache 值来减小这种开销。


以上内容翻译自官方文档,同样适用于 InnoDB 表。

https://dev.mysql.com/doc/refman/5.7/en/optimize-multi-tables.html

文章评论

0条评论