mysql分区和分表

mysql分区和分表？

分区

1）为什么要分区？

如果一张表的数据量太大的话，索引和数据空间会很大，查找数据就会变的很慢。这个时候我们可以利用mysql的分区功能

2）分区的具体实现：

分为range分区，list，hash，key

List分区实现：

1. mysql> CREATE TABLE IF NOT EXISTS `list_part` ( 
2.  ->   `id` int(11) NOT NULL  COMMENT '用户ID', 
3.  ->   `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省', 
4.  ->   `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称', 
5.  ->   `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男，1为女'  
6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8  
7.  -> PARTITION BY LIST (province_id) (  
8.  ->     PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),  
9.  ->     PARTITION p1 VALUES IN (9,10,11,12,16,21),  
10.  ->     PARTITION p2 VALUES IN (13,14,15,19),  
11.  ->     PARTITION p3 VALUES IN (17,18,20,22,23,24)  
12.  -> ); 

 

Hash分区的实现

1. mysql> CREATE TABLE IF NOT EXISTS `hash_part` (  
2.  ->   `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',  
3.  ->   `comment` varchar(1000) NOT NULL DEFAULT '' COMMENT '评论',  
4.  ->   `ip` varchar(25) NOT NULL DEFAULT '' COMMENT '来源IP',  
5.  ->   PRIMARY KEY (`id`)  
6.  -> ) ENGINE=INNODB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1  
7.  -> PARTITION BY HASH(id)  
8.  -> PARTITIONS 3;  

 

 

对分区的管理

#删除分区

alter table list_part drop partition p2; 

查询

select count(*) from aa; 

3）分区内部实现与原理

分区表是一个独立的逻辑表，但是底层MySQL将其分成了多个物理子表，这对用户来说是透明的，每一个分区表都会使用一个独立的表文件。
MySQL内部将表分成多个物理字表，但是客户端并无感知，仍然认为操作的是一个表

1. 分区的优缺点

1，分区可以分在多个磁盘，存储更大一点

2，根据查找条件，也就是where后面的条件，查找只查找相应的分区不用全部查找了

3，进行大数据搜索时可以进行并行处理。

4，跨多个磁盘来分散数据查询，来获得更大的查询吞吐量

分表

通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分

适用场景
1、单表记录条数达到百万或千万级别时
2、解决表锁的问题

分表的两种方式：

1）垂直分表

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中

适用场景

1、如果一个表中某些列常用，另外一些列不常用

2、可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数

实例：

有一张成绩表, 里面有学生id,学生姓名,学生题目, 学生答案, 但是你会发现sql语句是

select * from tt where id ="8"; 这时候查全表, 会有题目 和 答案, 这个表就比较大, 这时候把题目,和答案分出去, 留一张信息表就可以了, 查询的时候直接查询信息表, 而不用查询题目和答案, 

 

2）水平分表

水平分表：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询次数

适用场景：

1、表中的数据本身就有独立性，例如表中分表记录各个地区的数据或者不同时期的数据，特别是有些数据常用，有些不常用。

2、需要把数据存放在多个介质上。

实例：

订单行表，有1000万个订单行分到100个表中，此时可以对订单行号对101做取模操作， 余数就是这条数据判定的表!!!

借鉴：https://blog.csdn.net/weixin_38399962/article/details/80108079