MySQL分区表

为什么要用分区表?为什么不是分库分表?

随着业务的发展,当然现在比较流行的微服务无非就是业务垂直拆分+功能水平拆分,应用加节点是比较简单的,但是每个业务的单库单表扛不住了;数据库分库分表相对来说更复杂一点,但是分区表可以继续支持业务发展两三年,人手有限的情况下,我觉得分布表更合适一点。架构的终极目标是用最小的人力成本来满足就构建维护系统的需求

分区表是一个独立的逻辑表,但是底层由多个物理子表组成。对于SQL层 来说是一个完全封装底层实现的黑盒子,对应用是透明的,但是从底层的

文件系统来看,每一个分区表都有一个使用#分隔命名的表文件。

MySQL实现分区表的方式——对底层表的封装。索引也是按照分区的子表定义的,而没有全局索引。MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无须扫描所有分区,只需要查找包含需要数据的分区就可以了。

  • 一个表最多只能有1024个分区(MySQL5.6之后支持8192个分区)。
  • 在MySQL 5.1中,分区表达式必须是整数,或者是返回整数的表达式。在MySQL 5.5中,某些场景中可以直接使用列来进行分区。
  • 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来。
  • 分区表中无法使用外键约束。
  • 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数 据,其他均是历史数据。
  • 分区表的数据更容易维护。例如,想批量删除大量数据可以使用清除整个 分区的方式。另外,还可以对一个独立分区进行优化、检查、修复等操作。
  • 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备。
  • 可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问 、ext3文件系统的inode锁竞争等。
  • 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效 果非常好。

ACID操作

SELECT查询 当查询一个分区表的时候,分区层先打开并锁住所有的底层表,优化器先判断是否可以过滤部分分区,然后再调用对应的存储引擎接口访问各个分区的数据。

INSERT操作 当写入一条记录时,分区层先打开并锁住所有的底层表,然后确定哪个分区接收这条记录,再将记录写入对应底层表。

DELETE操作 当删除一条记录时,分区层先打开并锁住所有的底层表,然后确定数据对应的分区,最后对相应底层表进行删除操作。

UPDATE操作 当更新一条记录时,分区层先打开并锁住所有的底层表,MySQL先确定需要更新的记录在哪个分区,然后取出数据并更新,再判断更新后的数据应该放在哪个分区,最后对底层表进行写入操作,并对原数据所在的底层表进行删除操作。

虽然每个操作都会“先打开并锁住所有的底层表”,但这并不是说分区表在处理过程中是锁住全表的。如果存储引擎能够自己实现行级锁,例如InnoDB,则会在分区层释放对应表锁。这个加锁和解锁过程与普通InnoDB上的查询类似

使用方法

MySQL支持多种分区表。我们看到最多的是根据范围进行分区,每个分区存储落在某个范围的记录,分区表达式可以是列,也可以是包含列的表达式。例如,下表就可以将每一年的销售额存放在不同的分区里:

    CREATE TABLE sales (
       order_date DATETIME NOT NULL,
       -- Other columns omitted
    ) ENGINE=InnoDB PARTITION BY RANGE(YEAR(order_date)) (
        PARTITION p_2010 VALUES LESS THAN (2010),
        PARTITION p_2011 VALUES LESS THAN (2011),
        PARTITION p_2012 VALUES LESS THAN (2012),
        PARTITION p_catchall VALUES LESS THAN MAXVALUE );

PARTITION分区子句中可以使用各种函数。但有一个要求,表达式返回的值要是一个确定的整数,且不能是一个常数。

-- 查看表信息

show table status WHERE NAME ='tableName';

-- 查看是否过滤分区

EXPLAIN PARTITIONS
SELECT * FROM 'tableName' WHERE xxx;

-- 查看表具有哪几个分区、分区的方法、分区中数据的记录数等信息

select 
  partition_name part,  
  partition_expression expr,  
  partition_description descr,  
  table_rows  
from information_schema.partitions  where 
  table_schema = schema()  
  and table_name='SHOP_TEST'; 

-- 删除分区:

alter table emp drop partition p1,p2;

-- 增加分区:

alter table emp add partition (partition p3 values less than (4000));
alter table empl add partition (partition p3 values in (40));

-- 分解分区:

-- Reorganizepartition关键字可以对表的部分分区或全部分区进行修改,并且不会丢失数据。分解前后分区的整体范围应该一致。

alter table te reorganize partition p1 into
(partition p1 values less than (100),
partition p3 values less than (1000));

-- 合并分区:

alter table te reorganize partition p1,p3 into
(partition p1 values less than (1000));

注意事项

  • 1、NULL值会使分区过滤无效
  • 2、分区列和索引列不匹配(没有完全过滤分区)
  • 3、选择分区的成本可能很高
  • 4、维护分区的成本可能很高s
  • 5、分区逻辑无法灵活自定义,查找分区不可控,影响高并发。
  • 6、不合理分区会降低性能,大部分应用于历史数据存储。

本文分享自微信公众号 - 只喝牛奶的杀手(killerhub)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-04-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ONLYOFFICE历史版本开发技术之四

    继昨天打包好onlyoffice5.3.4版本后,本想将engineercms的历史版本功能完善,也就是将历史版本的word和changes都从document...

    hotqin888
  • MySQL 主键索引在 RR 和 RC 隔离级别下的加锁情况总结

    我今天抽时间给大家总结一个 MySQL InnoDB 存储引擎各种不同 SQL 情况下,加行锁、间隙锁、next-key lock 做一个总结。如果有错误的地方...

    业余草
  • 在真实环境下测试ASR的必要性

    http://www.speechtechmag.com/Articles/News/Industry-Voices/Avoid-Being-Fooled-by...

    用户6026865
  • python 标准库 sqlite3 介绍(一)

    SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。它的功能特点有:

    用户6021899
  • 数据分析:数据采集是根基

    数据传输,指的是数据以何种方式流入到存储介质,比如日志是通过logstash还是filebeat采集到kafka的,前端的操作记录是通过http请求发送的

    kk大数据
  • ABAP CDS view redirect特性介绍

    Suppose we have a database table A, and then we create a CDS redirect view B for...

    Jerry Wang
  • 读《新一代银行IT架构》有感

    在规划整体架构之前,我们首先需了解业务,然后据此明确架构目标及科技发展战略。互联网银行在建设之初,就与传统银行存在诸多不同之处(如下表所示)。

    用户5548425
  • Pytorch数据读取详解

    数据库DataBase + 数据集DataSet + 采样器Sampler = 加载器Loader

    marsggbo
  • 2019 数据库流行趋势 --非国内

    其实从去年已经隐隐约约感觉到数据库的有变化,只是没有想到变得这么快。今年的中美贸易,实实在在的是给某些数据库重击,如果以前去某数据库还是喊喊,然后该用还用,今年...

    AustinDatabases
  • Postgresql Repmgr 级联复制 及 PostgreSQL 故障转移

    PostgreSQL 使用repmgr 进行主从数据的Clone是可以进行级联复制的,使用过MYSQL的同学可能会觉得,没有什么了不起,MYSQL 多少级的级联...

    AustinDatabases

扫码关注云+社区

领取腾讯云代金券