`

MySQL 分区表举例

阅读更多
关于MySQL分区表的概念百度上一大堆,这里给出几个例子供参考,基本上一看就懂的。酷
Range类型
须要提供一个数字列作为判断依据
ALTER TABLE log_regist PARTITION BY RANGE (UNIX_TIMESTAMP(event_at))(
PARTITION P201702 VALUES LESS THAN (1485878400) ENGINE = InnoDB,
PARTITION P201703 VALUES LESS THAN (1488297600) ENGINE = InnoDB,
PARTITION P201704 VALUES LESS THAN (1490976000) ENGINE = InnoDB,
PARTITION P201705 VALUES LESS THAN (1493568000) ENGINE = InnoDB,
PARTITION P201706 VALUES LESS THAN (1496246400) ENGINE = InnoDB
)
注意红字必须是线性值,数字、时间戳都成(date需要使用 to_days(DATE)这种形式,5.5以后支持),核心思想就是比较指定列是否在某个区间(分区)内。问题就是分区随着时间推移会不断增加,所以一定要及时增加新的分区否则无法写入数据。另外,参照列必须列为复合主键(比如 PRIMARY KEY (`id`,`create_at`) )。
Hash类型
利用Hash函数获取某列的hash值,然后提供一个正数的分区数量。
ALTER TABLE log_order_finished PARTITION BY HASH(id) PARTITIONS 12;
核心思想就是根据一个HASH值判断指定的分区,与Range不同,这里直接给定了分区数量。话说MySQL分区数量上限是1024,超过了会有奇迹发生,具体参考MySQL5手册或百度。
List类型
List类型必须明确指定范围值,其不同于Range对连续的序列进行判断
假定有20个音像店,分布在4个有经销权的地区,如下表所示:
地区 商店ID 号
北区 3, 5, 6, 9, 17
东区 1, 2, 10, 11, 19, 20
西区 4, 12, 13, 14, 18
中心区 7, 8, 15, 16
要按照属于同一个地区商店的行保存在同一个分区中的方式来分割表,可以使用下面的“CREATE TABLE”语句:
CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id)
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);
这段照搬了手册,我不用这种分区方式的理由是不可约定列的内容范围。
Key类型
它们唯一的区别在于使用的关键字是KEY而不是HASH,并且KEY分区只采用一个或多个列名的一个列表
通过线性KEY分割一个表也是可能的。下面是一个简单的例子:
CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR KEY (col1)
PARTITIONS 3;
这段很诡异,手册上说明了是基于MySQL内部的hash方法,类似于password()函数。
删除分区
ALTER TABLE tr DROP PARTITION p2;
增加分区
ALTER TABLE members ADD PARTITION (PARTITION p3 VALUES LESS THAN (1960));
for ($i = 1; $i <= 36; $i++) {
$ts = mktime(0, 0, 0, $i + 1, 1);
$p = 'P' . date('Ym', $ts);
$d = date('Y-m-01', $ts);
echo "PARTITION $p VALUES LESS THAN ($ts) ENGINE = InnoDB,", '<br/>';
}
另外,为了方便的生成SQL语句,可以参考以下PHP代码:
function makePartition(){
        echo "ALTER TABLE log_mobi_regist PARTITION BY RANGE (UNIX_TIMESTAMP(event_at)) \r\n<br/>";
        $partitions = [];

        for ($i = 1; $i <= 36; $i++) {
            $ts = mktime(0, 0, 0, $i + 1, 1);
            $p  = 'P' . date('Ym', $ts);
            $d  = date('Y-m-01', $ts);
            $partitions[] = "PARTITION $p VALUES LESS THAN ($ts) ENGINE = InnoDB\r\n".'<br/>';
        }
        echo '(', join(',', $partitions), ')';
}
 此段是我常用于生成日志SQL的代码,仅供参考。
另外可以看看这边分区性能测评的文章会有帮助:http://blog.csdn.net/jhq0113/article/details/44593511
吐槽一句,这个编辑器越来越烂了
1
0
分享到:
评论

相关推荐

    MySQL优化介绍和举例

    4. 分区和分表:对于大数据量的表,可以使用分区或分表策略,提高查询效率。例如,按时间或地理位置进行分区。 二、查询优化 1. 使用EXPLAIN分析查询:通过EXPLAIN查看查询执行计划,了解索引使用情况,调整SQL...

    mysql面试题 92问

    - 何时应该考虑分区表,以及它对查询性能的影响? 8. **触发器和存储过程** - 什么是触发器?在什么情况下会使用它? - 存储过程的优点和使用场景是什么? 9. **备份与恢复** - 介绍几种常见的MySQL备份方法,...

    MySQL面试题资源整理

    - 何时应该考虑分区表,以及不同类型的分区策略(例如:Range,List,Hash)? - 缓存机制如何工作?MySQL的Query Cache和InnoDB Buffer Pool有什么区别? 7. **安全性** - 用户权限管理:GRANT和REVOKE命令的...

    MySql中文帮助手册

    9. **性能优化**:手册会讲解如何通过查询优化、索引设计、分区和复制等手段提高MySQL的性能。 10. **安全性**:MySQL提供了多种用户管理、权限控制和加密功能。手册会阐述如何设置用户账户、授权和撤销权限,以及...

    MySQL最佳优化完美攻略

    - **扩展技术**: 探讨MySQL的各种扩展技术, 如分区、复制等。 #### 21. MySQL何时使用索引 - **索引策略**: 了解MySQL在哪些情况下会选择使用索引。 #### 22. MySQL何时不使用索引 - **索引限制**: 理解MySQL不...

    mysql面试40道.zip

    20. **分区表的维护**:如何添加和删除分区,以及分区的优化策略。 以上知识点只是MySQL面试中的一部分,实际面试可能还会涵盖更多高级话题,如SQL性能优化、集群和分布式数据库等。准备面试时,除了理解这些概念,...

    mysql面试题汇总一共100+道经典mysql面试题

    MySQL是世界上最受欢迎的关系型数据库管理系统之一,广泛应用于各种规模的企业和项目中。为了在面试中脱颖而出,理解并掌握MySQL的核心概念和技术至关重要。以下是一些基于MySQL的经典面试题和相关知识点的详细解析...

    mysql一些经典面试题

    分区是将大表分成多个物理部分,每个部分有自己的索引,提高查询效率。分表是将一个大表分成多个逻辑上独立的小表,可以是垂直分割(按列分)或水平分割(按行分)。 10. **如何解释外键约束?** 外键是一种约束...

    MySQL运维-故障处理手册

    - 考虑使用分区表或者复制方案来降低单个表的负载。 3. **正常状态:** - **含义:**表示线程处于正常的等待状态,通常不需要特别处理。 **查看锁阻塞视图:** 1. **schema_table_lock_waits:** - **作用:**...

    云数据库十大经典案例.rar

    阿里可能采用了水平分区(Partitioning)和垂直分区(Sharding)策略,将大表分成多个小表,分散存储,从而降低单个表的数据量,提升查询速度。 2. 主从复制与读写分离:在高并发场景下,为了缓解主库压力,通常会...

    100 透彻研究通过explain命令得到的SQL执行计划(1).pdf

    分区表有助于提高大型数据库的管理和查询效率。 5. type:描述了MySQL优化器决定如何访问表中的数据。常见的类型有const(表示通过索引一次就找到数据)、ref(使用索引查找数据)、range(索引范围查找)、index...

    数据库性能优化培训PPT学习教案.pptx

    举例中,对`comment_video`表进行全表扫描获取平均值时,时间消耗显著,而对`comment_star`表执行相同操作则更快,因为返回的数据量更小。 - 通过索引扫描的性能取决于返回数据的大小。如果查询结果仅包含几行,...

    Java面试所需八股文

    使用分区表处理大数据量。 #### 2\. MySQL主键索引与二级索引的区别 - **主键索引**:用于唯一标识表中的每一行记录。MySQL中,主键索引通常采用B树结构,可以加快查询速度。 - **二级索引**:除了主键索引外的...

    2018 PHP 最新面试题

    8. **MySQL大表优化** - 使用分区技术,如范围分区、列表分区等。 - 建立合理的索引策略,避免不必要的全表扫描。 - 调整MySQL配置参数,优化缓存机制。 - 定期清理旧数据或归档数据。 9. **MySQL慢查询问题** ...

    学生宿舍管理系统数据库设计案例

    虽然内容未直接提及物理设计,但这是数据库设计的最后一步,涉及数据在硬盘上的存储方式、索引构建、分区策略等,以确保高效的数据存取。 5. 实施与维护: 系统开发完成后,会进行部署、测试和持续的维护,以适应...

    Hadoop和HIVE面试题

    3. 元数据:Hive使用元数据来描述数据,如表结构、字段类型、分区信息等,这些信息存储在元数据存储中,通常是MySQL或Derby数据库。 4. 索引:虽然Hive不支持传统意义上的数据库索引,但可以通过创建外部表和视图来...

    Hive教程.pdf

    - 利用分区表减少扫描的数据量。 - 利用桶表提高数据处理效率。 - 加速JOIN操作。 - 启用mapjoin优化。 - 优化GROUP BY操作。 - 优化ORDER BY操作。 - 在Map端进行聚合。 - **Hive架构层面优化**: - 避免...

    Ubuntu权威指南(2/2)

    2.4.5 磁盘分区 40 2.4.6 网络 40 2.4.7 连接到服务器 41 2.4.8 搜索文件 41 2.4.9 最近的文档 42 2.5 系统菜单 42 2.5.1 首选项 42 2.5.2 系统管理 45 2.5.3 锁住屏幕 51 2.5.4 注销 51 2.5.5 关机 51 2.6 使用移动...

    Ubuntu权威指南(1/2)

    2.4.5 磁盘分区 40 2.4.6 网络 40 2.4.7 连接到服务器 41 2.4.8 搜索文件 41 2.4.9 最近的文档 42 2.5 系统菜单 42 2.5.1 首选项 42 2.5.2 系统管理 45 2.5.3 锁住屏幕 51 2.5.4 注销 51 2.5.5 关机 51 2.6 使用移动...

Global site tag (gtag.js) - Google Analytics