3MySQL数据库-------MySQL设计优化 -主从复制、数据库的分区-----全栈式开发37

• 是基于一定数据量的条件下才主从复制、分区
• 这方面的优化主要都是运维做的，所以全栈开发可以只做了解

一、主从复制

（一）主从复制介绍

复制的三步骤

• 1.master将改变记录到二进制日志。这些记录过程叫做二进制日志事件,binary log events
• 2.slave将master的binary log events拷贝到它的中继日志
• 3.slave重做中继日志relaylog中的事件,将改变应用到自己的数据库中。MySQL复制是异步的且串行的

复制的基本原则

• 1.每个slave只有一个master
• 2.每个slave只能有一个唯一的服务器ID
• 3.每个master可以有多个salve

作用

• 读写分离
• 防止主机崩溃，备份

缺点

• mysql是异步的串行的，所以会有一定的延迟，i/o线程操作一定会有延迟

（二） 一主一从常见配置

要求

• MySQL版本一致且后台服务可以运行
• 主从主机可以相互通信

步骤

• 1.设置配置文件，
• 2.修改过配置文件之后,要重启MySQL服务或flush privileges刷新权限
• 3.在终端分别开启主机、从机

  ## 主机配置文件my.ini
  server-id = 1       # [必须]主服务器唯一ID，主从不能重复，可以设置成端口号
  log-bin = 自己本地的路径/mysqlbin   # [必须]启用二进制日志。文件名称可以自己指定
  log-err = 自己本地的路径/mysqlerr   # [可选] 启用错误日志
  binlog-do-db =   # [可选] 指定需要复制的数据库
  
  ## 从机配置文件-mysqld.cnf   
  server-id = 1       # [必须]从服务器唯一ID，主从不能重复，可以设置成端口号
  port=            # [必须]主从不能重复
  replicate-do-db= # [可选]需要同步的数据库名称，与master上的配置保持一致。
  

  #--------------------------linux--------------------------#
  ## 主机
  vim /etc/my.cnf  ## 打开配置文件，设置server-id = 1 log-bin = 自己本地的路径/mysqlbin
  flush privileges;  ## 重启service mysql restart;
  service iptables stop ##主从都关闭防火墙
  grant replication slave on *.* to 'zhangsan'@'从机数据库IP' identified by '123456'; #主机上建立账户并授权slave
  show master status;  ## 记录下File和position的值
  role   ##查看是主机还是从机
  ## 从机
  cp /etc/my.cnf /etc/mysqld.cnf   
  vim /etc/mysqld.cnf    ## 打开配置文件，设置server-id、port
  flush privileges;  ## 重启service mysql restart;
  service iptables stop ##主从都关闭防火墙
  start slave;  ## 开始主从复制
  show slave status\G  ## 两个参数都是yes,则说明主从配置成功slave_io_running、slave_sql_running
  stop slave;  ## 停止slave的复制
  

二、数据库分区

什么是数据库分区

• 在逻辑上为一个表,在物理上存储在多个文件中
• 看上去是一张表，其实存在多个文件里

分区键

• 分区的规则
• 分区键用于根据某个区间值、特定值、或者HASH函数值执行数据的聚集,让数据根据规则分布在不同的分区中。

分区类型

• RANGE分区 – 比较常用
• LIST分区
• HASH分区

使用分区表的注意事项

• 结合业务场景选择分区键,避免跨分区查询
• 对分区表进行查询最好在where从句中包含分区键
• 具有主键或唯一索引的表，主键或唯一索引必须是分区键的一部分

分区常用的函数

• year(login_time) – 返回时间年份
• UNIX_TIMESTAMP(login_time) --返回时间戳

（一）RANGE分区

特点

• 根据分区键值的范围把数据行存储到表的不同分区中
• 多个分区的范围要连续,但是不能重叠
• 分区不包括上限,取不到上限值

建立分区表

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(login_id)(
partition p0 values less than(10000),   # 实际范围0-9999
partition p1 values less than(20000),   # 实际范围10000-19999
partition p2 values less than(30000)， -- 如果不设置maxvalue，超过了插入的数据插不进去
partition p3 values less than maxvalue   -- # 存储大于30000的数据
);，

insert into login_log_range values(1,'2020-8-10 22:30:00',123)

explain select * from login_log_range;-- 扫描了3个分区
explain select * from login_log_range where login_id int=1;  只扫描了分区p0

- alter table login_log_range drop partitions p3; --删除分区

RANGE分区使用场景

• 分区键为日期或是时间类型
• 经常运行包含分区键的查询，MySQL可以很快的确定只有某一个或某些分区需要扫描,例如检索商品login_id小于10000的记录数,MySQL只需要扫描p0分区即可
• 定期按分区范围清理历史数据，比如可以直接删除某分区

（二）HASH分区

HASH分区的特点

• 根据MOD(分区键,分区值)的值把数据行存储到表的不同分区内
• 数据可以平均的分布在各个分区中
• HASH分区的键值必须是一个INT类型的值,或是通过函数可以转为INT类型

建立分区表

create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(login_id) partitions 4;

-- 或者这样建
create table `login_log`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb default charset=utf8 partition by hash(UNIX_TIMESTAMP(login_time)) partitions 4; ##将时间戳转换为整数，作为hash的分界线UNIX_TIMESTAMP(login_time)：

说明

• 建立好后数据插入的时候会自动平均分配到每个分区里面，按顺序一个一个存储
• 建立分区的时候不需要指定范围，根据login_id自动分配
• 这个就不能按时间清理数据，因为是平均分的，不能删分区来解决这个问题

（三）LIST分区

LIST分区特点

• 按分区键取值的列表进行分区
• 同范围分区一样,各分区的列表值不能重复
• 每一行数据必须能找到对应的分区列表,否则数据插入失败

-- 建立LIST分区
create table `login_log_list`(
    login_id int(10) unsigned not null comment '登录用户ID',
    login_time timestamp not null default CURRENT_TIMESTAMP,
    login_ip int(10) unsigned not null comment '登录ip',
    login_type int(10) not null
)engine=innodb 
partition by list(login_type)(
partition p0 values in(1,3,5,7,9),    -- 不能重复
partition p1 values in(2,4,6,8)   
);

说明

• 插入的数据必须注意login_type必须在分区中的存在
• (1,3,5,7,9)(2,4,6,8) 分区类型不能太多，这里是登陆方式
• 分区建不能太多超1000

（四）数据归档

说明

• 把要删除的分区数据移动到另一个表里
• 数据归档的表一定是非分区表，表结构要一样，5.7以上才有的方法
• 原表的数据会删掉

作用

• 删掉原表数据某分区不用数据，并另存到另一张表里，从而减少原表读写压力

create table `login_log_range`(
    login_id int(10) unsigned not null comment '登录用户id',
    login_time datetime  not null default current_timestamp,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb 
partition by range(year(login_time))(
partition p0 values less than(2015),   
partition p1 values less than(2016),   
partition p2 values less than(2017)   
);
-- 插入数据
insert into login_log_range values
(1,'2015-01-25',1),
(2,'2015-07-25',2),
(3,'2015-06-25',3),
(4,'2016-03-25',2),
(5,'2016-02-25',1);

-- 查询表
select table_name,partition_name,partition_description,table_rows from 
information_schema.`partitions` where table_name = 'login_log_range'
-- 修改分区-添加分区
alter table login_log_range add partition (partition p4 values less than(2018))
-- 分区删除
alter table login_log_range drop partition p0;
-- 数据归档-把要删除的分区数据移动到另一个表里，数据归档的表一定是非分区表，表结构要一样，5.7以上
create table `data_login_log_range`(
    login_id int(10) unsigned not null comment '登录用户id',
    login_time datetime  not null default current_timestamp,
    login_ip int(10) unsigned not null comment '登录ip'
)engine=innodb ;

-- 原表的数据会删掉
alter table login_log_range exchange partition p1 with  table data_login_log_range;