MySQL表分区的选择与实践小结

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 当面对百万或千万级数据量的表,即使有索引,查询速度也可能较慢。此时,可以采取分库、分表或分区策略来提升性能。分库分表涉及创建新数据库或表,可能需更改逻辑代码;

在一些系统中有时某张表会出现百万或者千万的数据量,尽管其中使用了索引,查询速度也不一定会很快。这时候可能就需要通过分库,分表,分区来解决这些性能瓶颈。


一. 选择合适的解决方法


1. 分库分表。


      分库分表从名字上就明白是需要创建额外的新数据库或新表,可以建在其他的机器上也可以是和当前数据库同一台机器。在优化查询上可能需要买新机器或者要修改逻辑代码了。比如一张user表,当数据超过10W,就可以创建新的user表,假设是user2。通过接收的UID,和分组10W(假定),取模就是对应的user表名的索引数字。


2. 分区。


      分区这里是指表分区,mysql数据库管理系统提供的表功能,分区后逻辑上是同一张表,物理上数据存储是分开的。能否优化查询还取决于在查询中是否使用到了分区字段,这个就和索引的使用有点类似,但是好处就是这个不像分库分表,可以几乎不修改业务逻辑代码就可以提升速度。下面再总结一下mysql数据保存格式和innodb,myisam。


2.1. innodb存储引擎。


      innodb,支持事务处理,外来键,在查询方面要慢于myisam。对并发友好,支持行锁和表锁,行锁的形成要看查询条件。有共享空间结构和独立空间结构,保存的格式有frm和ibddata1(共享结构),ibd(独立结构)。


2.1.1.共享空间结构。


      共享结构为innodb默认的结构,除了frm保存innodb表结构外,整个数据库所有表的索引和数据源都保存在ibdata中。可以通过在mysql-ini中添加 innodb_file_per_table=1设置为独立空间结构。


2.1.2.独立空间结构。


    独立空间结构就是每个对应的表保存对应的数据源和索引在一个后缀为ibd的文件中,表结构同样也保存在frm中。


2.2. myisam存储引擎


      myisam是mysql默认存储引擎,不支持事务,但是会对I/O进行平均分配,相较于innodb查询速度要快,对并发不友好,支持表锁。格式frm同样也是表结构,myd为表的数据源,myi表的索引储存(所以一张表的索引不是越多越好,因为在添加和修改数据时也需要对索引库进行修改和添加)

 

二. 表分区的几种分区类型


1. RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。


2. LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。


3. HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。


4. KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

 

三. 表分区的创建


   par格式为保存的是分区的规则,ibd就是分别为每一块分区后的数据源,以下为innodb分区,myisam的是有多个myd文件同时也存在par。


 

1. 新建表时添加分区。


    比如以下创建一张employees 的表,并创建了list类型的4个分区,以store_id 字段为分区字段。


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)
);

 

2. 存在的表添加分区


   将原来的数据以当前的分区规则对原来数据进行规整,以下是对ztest表添加range类型的3个分区,以id字段为分区字段。


alter table rm_ztest partition by RANGE (id)   
(
  PARTITION p0 VALUES LESS THAN (948),  
  PARTITION p1 VALUES LESS THAN (960),
  PARTITION p3 VALUES LESS THAN MAXVALUE
)


3. navicat for mysql工具添加或创建。


    平时我使用上面的工具比较多,所以他也有一个添加分区的功能。"新建表"或者是"设计表",点击"选项",“分割区”就可以进入分区的创建了。


 

四. 分区查询的实验结果


   我就拿了一张几百万数据的表备份了副本,其中一张创建了分区,并使用id分区字段进行查询。


       从上面两张截图可以看出使用了分区的查询速度要比未使用分区快差不多1倍,但是如果不使用id为查询条件或没有使用到,速度二者是一样的,甚至有时分区过的还要慢于未分区的,所以在使用上还需结合当前业务做合理的选择。

explain partitions select * from table_name ……       可以查看当前查询是否使用了分区,分区使用的是哪几个等等信息。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL
mysql join 实践
mysql join 实践
25 0
|
1月前
|
SQL 关系型数据库 MySQL
mysql百万数据实践-索引
mysql百万数据实践-索引
42 0
|
1月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版并行查询技术探索与实践
PolarDB MySQL版并行查询技术探索与实践 PolarDB MySQL版在企业级查询加速特性上进行了深度技术探索,其中并行查询作为其重要组成部分,已经在线稳定运行多年,持续演进。本文将详细介绍并行查询的背景、挑战、方案、特性以及实践。
233 2
|
5天前
|
SQL 关系型数据库 MySQL
从理论到实践,Mysql查询优化剖析(联表查询)
从理论到实践,Mysql查询优化剖析(联表查询)
14 0
|
1月前
|
SQL 关系型数据库 MySQL
Sqoop【付诸实践 01】Sqoop1最新版 MySQL与HDFS\Hive\HBase 核心导入导出案例分享+多个WRAN及Exception问题处理(一篇即可学会在日常工作中使用Sqoop)
【2月更文挑战第9天】Sqoop【付诸实践 01】Sqoop1最新版 MySQL与HDFS\Hive\HBase 核心导入导出案例分享+多个WRAN及Exception问题处理(一篇即可学会在日常工作中使用Sqoop)
118 7
|
2天前
|
监控 安全 关系型数据库
精通MySQL:数据库核心技术与应用实践
h3> 一、引言 MySQL作为开源关系型数据库管理系统的佼佼者,凭借其出色的性能、灵活性和稳定性,成为许多企业和开发者的首选
|
2天前
|
存储 关系型数据库 MySQL
精通MySQL:核心功能与实践应用
h3> 第一章:MySQL入门 1.1 MySQL概述 简要介绍MySQL的历史、特点以及在数据库领域的应用
|
1月前
|
SQL 存储 关系型数据库
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(下)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
168 2
|
1月前
|
SQL 关系型数据库 MySQL
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!(上)
必知的 MySQL 索引失效场景【包括实践验证】,别再踩坑了!
74 2
|
1月前
|
存储 关系型数据库 MySQL
MySQL中最左匹配相关实践总结
MySQL中最左匹配相关实践总结
37 0

相关产品

  • 云数据库 RDS MySQL 版