MySQL性能：多个表与单个表和分区上的索引

一尘不染

MySQL性能：多个表与单个表和分区上的索引

mysql

我想知道什么是更有效和更快的性能：
在一个大表或多个没有索引的小表上有索引？

由于这是一个非常抽象的问题，让我使其更加实用：
我有一张表，该表包含有关用户的统计信息（20,000个用户，总共约3000万行）。该表有10列，包括user_id，actions，timestamps等
最常见的应用是：通过插入数据user_id和user_ID的检索数据（SELECT报表从不包含多个user_id's）。

到目前为止，我已经INDEX打开了user_id，查询看起来像这样

SELECT * FROM statistics WHERE user_id = 1

现在，随着越来越多的行，表格变得越来越慢。INSERT声明放慢，因为INDEX变得越来越大；SELECT语句变慢了，因为要搜索的行更多。

现在，我想知道为什么不为每个用户提供一个统计表，而是将查询语法改为如下所示：

SELECT * FROM statistics_1

其中1代表user_id明显。
这样，INDEX就不需要了，每个表中的数据要少得多，因此INSERTand SELECT语句应该快得多。

现在我再问一个问题：
处理这么多的表（在我的情况下为20,000），而不是使用一个带有的表有现实的不利INDEX吗？
我的方法会加快速度，还是对表的查找最终会使速度降低，而不是速度降低？

阅读 463

2020-05-17

共1个答案

一尘不染

创建20,000个表是一个坏主意。您很快将需要40,000个表，然后更多。

我在《SQL反模式》一书中将此综合症称为
Metadata Tribbles
。您每次计划创建“每X表格”或“每X列”时都会看到这种情况。

当您有成千上万个表时，这确实会导致实际的性能问题。每个表都需要MySQL维护内部数据结构，文件描述符，数据字典等。

还有实际的操作后果。您是否真的要创建一个系统，该系统要求您每次注册新用户时都创建一个新表？

相反，我建议您使用MySQL分区。

这是对表进行分区的示例：

CREATE TABLE statistics (
  id INT AUTO_INCREMENT NOT NULL,
  user_id INT NOT NULL,
  PRIMARY KEY (id, user_id)
) PARTITION BY HASH(user_id) PARTITIONS 101;

这给您带来了定义一个逻辑表的好处，同时还可以将该表分为许多物理表，以便在查询分区键的特定值时可以更快地进行访问。

例如，当您像示例一样运行查询时，MySQL仅访问包含特定user_id的正确分区：

mysql> EXPLAIN PARTITIONS SELECT * FROM statistics WHERE user_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: statistics
   partitions: p1    <--- this shows it touches only one partition 
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
        Extra: Using where; Using index

分区的HASH方法意味着通过整数分区键的模数将行放置在分区中。这确实意味着许多user_id都映射到同一分区，但是每个分区平均只有平均行数的1 /
N（其中N是分区数）。而且您用固定数量的分区来定义表，因此不必在每次获得新用户时都对其进行扩展。

您可以选择多达1024个分区（在MySQL 5.6中为8192）的任意数量的分区，但是有些人报告说，当分区过高时会出现性能问题。

建议使用素数分区。如果您的user_id值遵循某种模式（例如仅使用偶数），则使用质数分区可以帮助更均匀地分配数据。

在评论中回答您的问题：

如何确定合理的分区数量？

对于HASH分区，如果您使用101个分区（如上例所示），则任何给定分区平均约占行的1％。您说统计信息表有3000万行，因此，如果使用此分区，则每个分区只有30万行。对于MySQL而言，这更容易阅读。您也可以（也应该）使用索引-
每个分区都有其自己的索引，并且其大小仅为整个未分区表的索引的1％。

因此，如何确定合理数量的分区的答案是：整个表有多大，并且您希望分区平均有多大？

分区数量不应该随着时间增长吗？如果是这样：我该如何自动化？

如果您使用HASH分区，则分区的数量并不一定需要增加。最终，您可能总共有300亿行，但是我发现，当您的数据量增长几个数量级时，无论如何都需要一种新的体系结构。如果您的数据变得如此之大，则可能需要在多个服务器上进行
分片以及将其分区到多个表中。

也就是说，您可以使用ALTER TABLE重新分区表：

ALTER TABLE statistics PARTITION BY HASH(user_id) PARTITIONS 401;

这必须重新组织表（就像大多数ALTER TABLE更改一样），因此希望它花一些时间。

您可能要监视分区中数据和索引的大小：

SELECT table_schema, table_name, table_rows, data_length, index_length
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE partition_method IS NOT NULL;

与任何表一样，您希望活动索引的总大小适合您的缓冲池，因为如果在SELECT查询期间MySQL必须在缓冲池中交换部分索引进出索引，则性能会受到影响。

如果使用RANGE或LIST分区，则添加，删除，合并和拆分分区更为常见。见 http://dev.mysql.com/doc/refman/5.6/en/partitioning-
management-range-
list.html

我鼓励您阅读有关分区的手册部分，并阅读以下演示文稿：使用MySQL
5.1分区提高性能。

2020-05-17