MYSQL数据库分区、分表、分库和读写分离使用场景和注意事项

最新推荐文章于 2024-05-17 12:00:00 发布

「已注销」

最新推荐文章于 2024-05-17 12:00:00 发布

阅读量1.2k

点赞数

分类专栏： Go语言笔记

Go语言笔记专栏收录该内容

13 篇文章 0 订阅

订阅专栏

https://www.cnblogs.com/bluebluesky/articles/6413831.html

其实MYSQL 数据库的优化流程大致如下：
分区 -> 分表 -> 库垂直拆分 -> 库水平拆分 ->库读写分离
注意：现在尽量使用InnoDB代替MyISAM，使用表级锁的代价是很大的。

简介

一般当我们的数据表存的数据越来越大，已经影响到了查询等操作，而且旧的数据访问比较少，这时候一般会使用分区来分散数据到不同的磁盘，分散I/O负担，查询数据时也只需要查询指定的分区即可。

但是每个表的分区不能超过1024，而实际情况下分区达到100以后基本就影响性能了，此时需要进一步对表进行划分，分表不影响单个表的分区，二者应该一起使用。

然而当单个数据库的请求并发实在多到影响性能时，这时候就要采用分库、读写分离的手段了，然而这时候可能就需要一些中间件来简化操作。

分区

mysql支持的分区类型包括Range、List、Hash、Key，其中Range比较常用：

RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。

分区的限制：

主键或者唯一索引必须包含分区字段，如primary key (id,username),不过innoDB的大组建性能不好。
很多时候，使用分区就不要在使用主键了，否则可能影响性能。
每个表最多1024个分区，而且多分区会大量消耗内存。一般小于100个为好
分区的表不支持外键，相关的逻辑约束需要使用程序来实现。
分区后，可能会造成索引失效，需要验证分区可行性。

create table user(
    id int not null auto_increment,
    username varchar(10),
    primary key(id)
)engine = innodb charset=utf8
partition by range (id)(
    partition user_1 values less than (10),
    partition user_2 values less than (20)
);

建立后添加分区：
maxvalue 表示最大值   这样大于等于20的id 都出存储在user_3分区
alter table user add partition(
    partition user_3 values less than maxvalue
);
删除分区
alter  table user drop partition user_3;

由此可见，mysql通过分区把数据保存到不同的文件里，同时索引也是分区的。相对于未分区的表来说，分区后单独的数据库文件索引文件的大小都明显降低，效率则明显的提示了。可以插入一条数据然后分析查询语句验证一下：

insert into user values(null,'测试');
explain partitions select * from user where id =1;

可以将这些分区所在的物理磁盘分开完全独立，可以提高磁盘IO吞吐量。

CREATE TABLE users (  
       id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
       usersname VARCHAR(30) NOT NULL DEFAULT '',  
       email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY RANGE (id) (  
       PARTITION p0 VALUES LESS THAN (3000000)  
       DATA DIRECTORY = '/data0/data'  
       INDEX DIRECTORY = '/data0/index',  
  
       PARTITION p1 VALUES LESS THAN (6000000)  
       DATA DIRECTORY = '/data1/data'  
       INDEX DIRECTORY = '/data1/index',  
  
       PARTITION p2 VALUES LESS THAN (9000000)  
       DATA DIRECTORY = '/data2/data'  
       INDEX DIRECTORY = '/data2/index',  
  
       PARTITION p3 VALUES LESS THAN MAXVALUE     
       DATA DIRECTORY = '/data3/data'   
       INDEX DIRECTORY = '/data3/index'  
);