MySQL分表和分区的具体实现方法

垂直分表

垂直分表就是一个包含有很多列的表拆分成多个表,比如表A包含20个字段,现在拆分成表A1和A2,两个表各十个字段(具体如何拆根据业务来选择)。

优势:在高并发的情境下,可以减少表锁和行锁的次数。

劣势:在数据记录非常大的情况下,读写速度还是会遇到瓶颈。

水平分表

假如某个网站,它的数据库的某个表已经达到了上亿条记录,那么此时如果通过select去查询,在没有索引的情况下,他的查询会非常慢,那么就可以通过hash算法将这个表分成10个子表(此时每个表的 的数据量只有1000万条了)。

同时生成一个总表,记录各个子表的信息,假如查询一条id=100的记录,它不再需要全表扫描,而是通过总表找到该记录在哪个对应的子表上,然后再去相应的表做检索,这样就降低了IO压力。

劣势:会给前端程序应用程序的SQL代码的维护带来很大的麻烦,这时候可以使用MySQL的Merge存储引擎实现分表。

---------------------------------------我是蛋疼的分割线----------------------------------------------------

用Merge存储引擎分表,对应用程序的SQL语句来说是透明的,不需要修改任何代码。

CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
CREATE TABLE total (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

在创建total表的时候可能报错:

Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

实际上merge存储引擎是一个虚拟的表,对应的实际表必须是myisam类型的表,如果你的mysql是5.1以上版本,默认数据库使用的事InnoDB存储引擎的,所以在创建total时,t1和t2表必须是myisam存储引擎的。

如果需要定期增加分表,只需要修改merge表的union即可。

CREATE TABLE t3( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20));
ALTER TABLE total UNION=(t1,t2,t3)

横向分区

举例说明:假如有100W条数据,分成十份,前10W条数据放到第一个分区,第二个10W条数据放到第二个分区,依此类推。取出一条数据的时候,这条数据包含了表结构中的所有字段,横向分区并没有改变表的结构。

纵向分区

举例说明:在设计用户表的时候,开始的时候没有考虑好,而把个人的所有信息都放到了一张表里面去,这个表里面就会有比较大的字段,如个人简介,而这些简介呢,也许不会有好多人去看,所以等到有人要看的时候,再去查找,分表的时候,可以把这样的大字段,分开来

完整的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件。

时间: 2019-06-26

什么是分表和分区 MySql数据库分区和分表方法

1.为什么要分表和分区 日常开发中我们经常会遇到大表的情况,所谓的大表是指存储了百万级乃至千万级条记录的表.这样的表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,如果涉及联合查询的情况,性能会更加糟糕.分表和表分区的目的就是减少数据库的负担,提高数据库的效率,通常点来讲就是提高表的增删改查效率. 2.什么是分表和分区 2.1 分表 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表,我们可以称为子表,每个表都对应三个文件,MYD数据文件,.MYI索引文件,.frm表结构

MyBatis实现Mysql数据库分库分表操作和总结(推荐)

前言 作为一个数据库,作为数据库中的一张表,随着用户的增多随着时间的推移,总有一天,数据量会大到一个难以处理的地步.这时仅仅一张表的数据就已经超过了千万,无论是查询还是修改,对于它的操作都会很耗时,这时就需要进行数据库切分的操作了. MyBatis实现分表最简单步骤 既然文章的标题都这么写了,不如直接上干货来的比较实际,我们就先来看看如何实现最简单的分表. 1.我们模拟用户表数据量超过千万(虽然实际不太可能) 2.用户表原来的名字叫做user_tab,我们切分为user_tab_0和user_t

MySQL分区表的局限和限制详解

禁止构建 分区表达式不支持以下几种构建: 存储过程,存储函数,UDFS或者插件 声明变量或者用户变量 可以参考分区不支持的SQL函数 算术和逻辑运算符 分区表达式支持+,-,*算术运算,但是不支持DIV和/运算(还存在,可以查看Bug #30188, Bug #33182).但是,结果必须是整形或者NULL(线性分区键除外,想了解更多信息,可以查看分区类型). 分区表达式不支持位运算:|,&,^,<<,>>,~ . HANDLER语句 在MySQL 5.7.1之前的分区表不

mysql的3种分表方案

一.先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了.分表的目的就在于此,减小数据库的负担,缩短查询时间. 根据个人经验,mysql执行一个sql的过程如下:1.接收到sql; 2.把sql放到排队队列中;3.执行sql; 4.返回执行结果.在这个执行过程中最花时间在什么地方呢?第一,是排队等待的时间,第二,sql的执行时间.其实这二个是一回事,等待的同时,肯定有sql在执行.所以我们要缩短sql的执行时间. mysql中有一种机

mysql分表和分区的区别浅析

一.什么是mysql分表和分区 什么是分表,从表面意思上看呢,就是把一张表分成N多个小表 什么是分区,分区呢就是把一张表的数据分成N多个区块,这些区块可以在同一个磁盘上,也可以在不同的磁盘上 二.mysql分表和分区有什么区别呢 1.实现方式上 a)mysql的分表是真正的分表,一张表分成很多表后,每一个小表都是完正的一张表,都对应三个文件,一个.MYD数据文件,.MYI索引文件,.frm表结构文件. 复制代码 代码如下: [root@BlackGhost test]# ls |grep use

MySql分表、分库、分片和分区知识点介绍

一.前言 数据库的数据量达到一定程度之后,为避免带来系统性能上的瓶颈.需要进行数据的处理,采用的手段是分区.分片.分库.分表. 二.分片(类似分库) 分片是把数据库横向扩展(Scale Out)到多个物理节点上的一种有效的方式,其主要目的是为突破单节点数据库服务器的 I/O 能力限制,解决数据库扩展性问题.Shard这个词的意思是"碎片".如果将一个数据库当作一块大玻璃,将这块玻璃打碎,那么每一小块都称为数据库的碎片(DatabaseShard).将整个数据库打碎的过程就叫做分片,可以

mysql分表程序改动方法

mysql分表 程序如何改动 1.实现分表的原理如下 2.做mysql集群利用mysql cluster ,mysql proxy,mysql replication,drdb的命令如下 3.出现大数据量并且访问频繁的表-->将其分为若干个表.方法代码如下 4.创建表的方法代码 5.实现拆成二张表user1和user2,每张表25W条数据的方法代码.插入的数据方法代码 6.实现更新的方法代码

MySQL 分表优化试验代码

这里的分表逻辑是根据t_group表的user_name组的个数来分的.因为这种情况单独user_name字段上的索引就属于烂索引.起不了啥名明显的效果. 1.试验PROCEDURE.DELIMITER $$DROP PROCEDURE `t_girl`.`sp_split_table`$$CREATE  PROCEDURE `t_girl`.`sp_split_table`()BEGIN  declare done int default 0;  declare v_user_name var

mysql分表分库的应用场景和设计方式

很多朋友在论坛和留言区域问mysql在什么情况下才需要进行分库分表,以及采用何种设计方式才是最优的选择,根据这些问题,小编为大家整理了关于MySQL分库分表的应用场景和最优的设计方式举例. 一. 分表 场景:对于大型的互联网应用来说,数据库单表的记录行数可能达到千万级甚至是亿级,并且数据库面临着极高的并发访问.采用Master-Slave复制模式的MySQL架构, 只能够对数据库的读进行扩展,而对数据库的写入操作还是集中在Master上,并且单个Master挂载的Slave也不可能无限制多,Sl

MySQL与SQL Server的一些区别浅析

1.mysql支持enum,和set类型,sql server不支持 2.mysql不支持nchar,nvarchar,ntext类型 3.mysql的递增语句是AUTO_INCREMENT,而mssql是identity(1,1) 4.mssql默认到处表创建语句的默认值表示是((0)),而在mysql里面是不允许带两括号的 5.mysql需要为表指定存储类型 6.mssql识别符是[],[type]表示他区别于关键字,但是mysql却是 `,也就是按键1左边的那个符号 7.mssql支持ge

PHP操作mysql数据库分表的方法

一般来说,当我们的数据库的数据超过了100w记录的时候就应该考虑分表或者分区了,这次我来详细说说分表的一些方法.首先,我们需要想好到底分多少个 表,前提当然是满足应用.这里我使用了一个比较简单的分表方法,就是根据自增id的尾数来分,也就是说分0-9一共10个表,其取值也很好做,就是对10 进行取模.另外,还可以根据某一字段的md5值取其中几位进行分表,这样的话,可以分的表就很多了. 好了,先来创建表吧,代码如下: CREATE TABLE `ttlsa_com`.`article_0` ( `i

Mysql数据表分区技术PARTITION浅析

在这一章节里, 我们来了解下 Mysql 中的分区技术 (RANGE, LIST, HASH)   Mysql 的分区技术与水平分表有点类似, 但是它是在逻辑层进行的水平分表, 对于应用而言它还是一张表, 换句话说: 分区不是实际真正的对一张表进行拆分,分区之后表还是一个表,它是把存储文件进行拆分. 在 Mysql 5.1(后) 有了几种分区类型:   RANGE分区: 基于属于一个给定连续区间的列值, 把多行分配给分区 LIST分区: 类似于按 RANGE 分区, 区别在于 LIST 分区是基