MySQL:将大表拆分成小表的最快方法

Question

我有一个非常大的表，其中有近3亿条记录。因为select查询对我来说太慢了，所以我想把它拆分为大约800个小表。

数据集如下所示：

XXXXXX column2 column3 column4 ...
XXXXXX column2 column3 column4 ...
XXXXXX column2 column3 column4 ...
YYYYYY column2 column3 column4 ...
YYYYYY column2 column3 column4 ...

我想根据第一列的值来拆分表(例如，使用XXXXXX将记录拆分到表XXXXXX中)，最快的方法是什么？

注意:我已经为它添加了10个分区，但它并没有很好地加速它。

Answer 1

分区在两种情况下作为一种性能策略工作：

1. 该表的主查询最终执行表或索引扫描，并且位于具有足够资源和适当配置的系统上，以执行高级别的并行。因此，如果所有分区都在同一物理驱动器上，这不会给您带来太多好处，您的I/O绑定程度与最初一样高。但是，如果您在一个16核系统上，每个分区位于物理上不同的磁盘上？分区可能会在系统performance.
2. The分区规则中产生惊人的改进，分区规则使用的索引通常用于对该表的最常用查询。如果您打算通过该方法获得性能，则应该对经常用于过滤或约束结果集的索引值进行分区。最常见的候选是事务日期，因为报告通常是按日历日期范围进行的。然后，查询优化器可以使用分区规则将操作限制在单个(较小)分区内，或者并行运行两个或多个分区扫描(受上述相同限制的约束)。

我假设拆分这个表的主要原因是为了性能。但是800个分区呢？如果您所追求的是性能改进，那么这可能是一个错误的方法。企业数据库在缓存中保留尽可能多的顶级表索引，以获得良好的性能。在一个五级b-tree中，对于一个中等使用率的表，在第一次访问之后，前三级很可能一直保存在缓存中(这是一种可能的配置，适用于具有整数主键的300M行表)。通过将表拆分为800个部分，这意味着将有800个数据结构(除了表数据本身之外)进行缓存。如果您的访问或多或少是按主键均匀分布的，那么在一个分区上进行搜索最终会将其他分区逐出缓存，从而最终损害整体性能。

然而，如果您决心这样做，将一个表划分为N个部分的最简单方法是根据您想要的针对主键的分区数量的模数对其进行分区(在本例中为primary_key % 800)。较新版本的MySQL也支持散列分区，这使得划分成任意数量的集合变得非常直接：

PARTITION BY HASH(some_column_value) PARTITIONS number_of_partitions

如果您希望将数据放入800个实际的表中，则必须使用编辑器魔术，或者使用脚本语言，并在SQL中完成：

CREATE TABLE table1 LIKE MasterTable
CREATE TABLE table2 LIKE MasterTable
CREATE TABLE table3 LIKE MasterTable
..
INSERT INTO table1 SELECT * FROM MasterTable WHERE id MOD 800 = 0
INSERT INTO table2 SELECT * FROM MasterTable WHERE id MOD 800 = 1
INSERT INTO table3 SELECT * FROM MasterTable WHERE id MOD 800 = 2

您可以使用您最喜欢的编程语言使用动态SQL在循环中完成此操作:这可能是最容易呈现的。

Answer 2

感谢每一位启发我使用这个存储过程的人！我很高兴与你分享它：

​

    DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `split_tablebyrowscnt` (IN `tableName` VARCHAR(40), IN `step` INT)  BEGIN 
    SET @table := tableName; 
    SET @liminf := 0; 
    SET @limsup := step;

    SET @sql_2 = CONCAT('SELECT COUNT(*) INTO @rwcnt FROM ',@table,';'); 
    PREPARE stmt from @sql_2; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;

    WHILE @liminf<@rwcnt DO

    SET @sql_1 = CONCAT('SELECT CAST(',@limsup,' as char(10)) INTO @limsup_str;'); 
    PREPARE stmt from @sql_1; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt; 

    SET @sql_loop =CONCAT('CREATE TABLE ',@table,'_',@limsup_str,' SELECT * FROM(SELECT @rownum:=@rownum+1 rownum,d.* FROM (',@table,' d, (SELECT @rownum:=0) r))t 
    WHERE ( rownum >?) AND (rownum <= ?);'); 
    PREPARE stmt from @sql_loop; 
    EXECUTE stmt USING @liminf,@limsup; 
    DEALLOCATE PREPARE stmt;  

    SET @sql_drop = CONCAT('ALTER TABLE ',@table,'_',@limsup_str,' DROP COLUMN rownum;'); 
    PREPARE stmt from @sql_drop; 
    EXECUTE stmt; 
    DEALLOCATE PREPARE stmt;

    SET @liminf = @liminf + step;
    SET @limsup = @limsup + step;
    END WHILE ;
    END$$

DELIMITER ;

要执行该过程:调用split_tablebyrowscnt('myTable',100)