mysql5.7中实现分区表及分区where in查询示例及分区分表对比浅析

数据库  /  管理员 发布于 2年前   841

mysql5.7中单表性能跟分区性能那个好？

这个问题的答案取决于具体的应用场景和数据量大小。

对于小数据量的单表查询，单表性能可能更好，因为不需要进行分区操作，查询速度更快。

对于大数据量的查询，分区性能可能更好，因为可以将数据分散存储在不同的分区中，减少查询时需要扫描的数据量，从而提高查询效率。此外，使用分区还可以更容易地进行数据的维护、备份和恢复操作。因此，在选择单表性能和分区性能之间，需要根据具体情况进行权衡和选择。

有几种方式，下面用range分区示例

1.创建分区表

DROP TABLE
IF
EXISTS `fenqubiao`;
CREATE TABLE `fenqubiao` ( 
`id` INT, `name` VARCHAR ( 10 ), 
`age` INT ( 3 ) 
) PARTITION BY RANGE ( id ) 
( 
PARTITION f1 VALUES less than ( 10 ), 
PARTITION f2 VALUES less than ( 20 ), 
PARTITION f3 VALUES less than MAXVALUE 
);

插入测试数据：

INSERT INTO fenqubiao ( 
id, NAME,age )
VALUES
( 1, "aaa" ,18),
( 10, "bbb",20),
( 15, "ccc",30),
( 50, "ddd" ,60);

查看所以分区表命令

SELECT
partition_name,
table_rows 
FROM
information_schema.PARTITIONS 
WHERE
table_name = 'fenqubiao';

查询其中f1分表

select * from fenqubiao partition (f1);

分区后怎么实现where in

我这里以分区表子查询的方式：

将where in子句中的值列表拆分成多个子查询，每个子查询查询一个分区，然后将结果合并。

例如：

SELECT * FROM fenqubiao WHERE id IN (
    SELECT id FROMfenqubiao PARTITION ( f1 ) WHERE age = 18 
    UNION ALL
    SELECT id FROM fenqubiao PARTITION ( f3 ) WHERE age = 60 
);

ps:

还有一种分区表联合查询这里就不介绍了

对了也可以对分区总表进行查询，不过要加索引不然会影响性能

要注意的是，对于查询条件中没有包括分区键的查询操作，MySQL仍然需要扫描所有的分区，这样会降低查询效率。

因此，在设计分区表时需要考虑分区键的选择和查询条件的优化，以最大限度地提高查询效率。

如果查询条件包括分区键，则可以直接查询对应的分区，这样可以减少扫描的数据量，提高查询效率。