接上文
分区
一般情况下我们创建的表对应一组存储文件,使用MyISAM存储引擎时是一个.MYI和.MYD文件,使用Innodb存储引擎时是一个.ibd和.frm(表结构)文件。
当数据量较大时(一般千万条记录级别以上),MySQL的性能就会开始下降,这时我们就需要将数据分散到多组存储文件,保证其单个文件的执行效率。
最常见的分区方案是按id分区,如下将id的哈希值对10取模将数据均匀分散到10个.ibd存储文件中:
create table article( id int auto_increment PRIMARY KEY, title varchar(64), content text)PARTITION by HASH(id) PARTITIONS 10
查看data目录:
![d9f5bb0af4a99b4b7780aabdd1d6a8d6.png](https://i-blog.csdnimg.cn/blog_migrate/b799c8a5bb0a6c63e0c60fa2a36e5039.jpeg)
服务端的表分区对于客户端是透明的,客户端还是照常插入数据,但服务端会按照分区算法分散存储数据。
MySQL提供的分区算法
分区依据的字段必须是主键的一部分,分区是为了快速定位数据,因此该字段的搜索频次较高应作为强检索字段,否则依照该字段分区毫无意义
hash(field)
相同的输入得到相同的输出。输出的结果跟输入是否具有规律无关。仅适用于整型字段
key(field)
和hash(field)的性质一样,只不过key是处理字符串的,比hash()多了一步从字符串中计算出一个整型在做取模操作。
create table article_key( id int auto_increment, title varchar(64), content text, PRIMARY KEY (id,title) -- 要求分区依据字段必须是主键的一部分)PARTITION by KEY(title) PARTITIONS 10
range算法
是一种条件分区算法,按照数据大小范围分区(将数据使用某种条件,分散到不同的分区中)。
如下,按文章的发布时间将数据按照2018年8月、9月、10月分区存放:
create table article_range( id int auto_increment, title varchar(64), content text, created_time int, -- 发布时间到1970-1-1的毫秒数 PRIMARY KEY (id,created_time) -- 要求分区依据字段必须是主键的一部分)charset=utf8PARTITION BY RANGE(created_time)( PARTITION p201808 VALUES less than (1535731199), -- select UNIX_TIMESTAMP('2018-8-31 23:59:59') PARTITION p201809 VALUES less than (1538323199), -- 2018-9-30 23:59:59 PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59);
![38968197a239e7143685f5041fbe3492.png](https://i-blog.csdnimg.cn/blog_migrate/8c8a82748c2fd3f12bb4679403af5cbb.jpeg)
注意:条件运算符只能使用less than,这以为着较小的范围要放在前面,比如上述p201808,p201819,p201810分区的定义顺序依照created_time数值范围从小到大,不能颠倒。
insert into article_range values(null,'MySQL优化','内容示例',1535731180);flush tables; -- 使操作立即刷新到磁盘文件
![487d8084a25bb5459ce5110d2214689f.png](https://i-blog.csdnimg.cn/blog_migrate/f6e90b8fec3c0ae8d1c30c77fe7725a9.jpeg)
由于插入的文章的发布时间1535731180小于1535731199(2018-8-31 23:59:59),因此被存储到p201808分区中,这种算法的存储到哪个分区取决于数据状况。
list算法
也是一种条件分区,按照列表值分区(in (值列表))。
create table article_list( id int auto_increment, title varchar(64), content text, status TINYINT(1), -- 文章状态:0-草稿,1-完成但未发布,2-已发布 PRIMARY KEY (id,status) -- 要求分区依据字段必须是主键的一部分)charset=utf8PARTITION BY list(status)( PARTITION writing values in(0,1), -- 未发布的放在一个分区 PARTITION published values in (2) -- 已发布的放在一个分区);insert into article_list values(null,'mysql优化','内容示例',0);flush tables;
![f5efcd249192171890dd59051222f62e.png](https://i-blog.csdnimg.cn/blog_migrate/afd59a4d056720468eaeac3737fc666d.jpeg)
分区管理语法
range/list
增加分区
前文中我们尝试使用range对文章按照月份归档,随着时间的增加,我们需要增加一个月份:
alter table article_range add partition( partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59') -- more);
![e2087e3457aa43638ee5bb236daebb32.png](https://i-blog.csdnimg.cn/blog_migrate/f65a959c967bbfae12b8514592b6a4a8.jpeg)
删除分区
alter table article_range drop PARTITION p201808
注意:删除分区后,分区中原有的数据也会随之删除!
key/hash
新增分区
alter table article_key add partition partitions 4
![6e0161344a64a6f3f03320ffcc868331.png](https://i-blog.csdnimg.cn/blog_migrate/78f82f5e5a491c6967f7293007188f99.jpeg)
销毁分区
alter table article_key coalesce partition 6
key/hash分区的管理不会删除数据,但是每一次调整(新增或销毁分区)都会将所有的数据重写分配到新的分区上。效率极低,最好在设计阶段就考虑好分区策略。
分区的使用
当数据表中的数据量很大时,分区带来的效率提升才会显现出来。
只有检索字段为分区字段时,分区带来的效率提升才会比较明显。因此,分区字段的选择很重要,并且业务逻辑要尽可能地根据分区字段做相应调整(尽量使用分区字段作为查询条件)。
水平分割和垂直分割
水平分割:通过建立结构相同的几张表分别存储数据
垂直分割:将经常一起使用的字段放在一个单独的表中,分割后的表记录之间是一一对应关系。
分表原因
- 为数据库减压
- 分区算法局限
- 数据库支持不完善(5.1之后mysql才支持分区操作)
id重复的解决方案
- 借用第三方应用如memcache、redis的id自增器
- 单独建一张只包含id一个字段的表,每次自增该字段作为数据记录的id
集群
横向扩展:从根本上(单机的硬件处理能力有限)提升数据库性能 。由此而生的相关技术:读写分离、负载均衡
安装和配置主从复制
环境
- Red Hat Enterprise Linux Server release 7.0 (Maipo)(虚拟机)
- mysql5.7
安装和配置
解压到对外提供的服务的目录(我自己专门创建了一个/export/server来存放)
tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/servercd /export/servermv mysql-5.7.23-linux-glibc2.12-x86_64 mysql
添加mysql目录的所属组和所属者:
groupadd mysqluseradd -r -g mysql mysqlcd /export/serverchown -R mysql:mysql mysql/chmod -R 755 mysql/
创建mysql数据存放目录(其中/export/data是我创建专门用来为各种服务存放数据的目录)
mkdir /export/data/mysql
初始化mysql服务
cd /export/server/mysql./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize
如果成功会显示mysql的root账户的初始密码,记下来以备后续登录。如果报错缺少依赖,则使用yum instally依次安装即可
配置my.cnf
vim /etc/my.cnf[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10 # 服务id,在集群时必须唯一,建议设置为IP的第四段port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemd[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d
将服务添加到开机自动启动
cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld
启动服务
service mysqld start
配置环境变量,在/etc/profile中添加如下内容
# mysql envMYSQL_HOME=/export/server/mysqlMYSQL_PATH=$MYSQL_HOME/binPATH=$PATH:$MYSQL_PATHexport PATH
使配置即可生效
source /etc/profile
使用root登录
mysql -uroot -p# 这里填写之前初始化服务时提供的密码
登录上去之后,更改root账户密码(我为了方便将密码改为root),否则操作数据库会报错
set password=password('root');flush privileges;
设置服务可被所有远程客户端访问
use mysql;update user set host='%' where user='root';flush privileges;
这样就可以在宿主机使用navicat远程连接虚拟机linux上的mysql了
配置主从节点
配置master
以linux(192.168.10.10)上的mysql为master,宿主机(192.168.10.1)上的mysql为slave配置主从复制。(可以参考:数据库从主备到主主的高可用方案)
修改master的my.cnf如下
[mysqld]basedir=/export/server/mysqldatadir=/export/data/mysqlsocket=/tmp/mysql.sockuser=mysqlserver-id=10port=3306# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0# Settings user and group are ignored when systemd is used.# If you need to run mysqld under a different user or group,# customize your systemd unit file for mariadb according to the# instructions in http://fedoraproject.org/wiki/Systemdlog-bin=mysql-bin # 开启二进制日志expire-logs-days=7 # 设置日志过期时间,避免占满磁盘binlog-ignore-db=mysql # 不使用主从复制的数据库binlog-ignore-db=information_schemabinlog-ignore-db=performation_schemabinlog-ignore-db=sysbinlog-do-db=test #使用主从复制的数据库[mysqld_safe]log-error=/export/data/mysql/error.logpid-file=/export/data/mysql/mysql.pid## include all files from the config directory#!includedir /etc/my.cnf.d
重启master
service mysqld restart
登录master查看配置是否生效(ON即为开启,默认为OFF):
mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin | ON |+---------------+-------+
在master的数据库中建立备份账号:backup为用户名,%表示任何远程地址,用户back可以使用密码1234通过任何远程客户端连接master
grant replication slave on *.* to 'backup'@'%' identified by '1234'
查看user表可以看到我们刚创建的用户:
mysql> use mysqlmysql> select user,authentication_string,host from user;+---------------+-------------------------------------------+-----------+| user | authentication_string | host |+---------------+-------------------------------------------+-----------+| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % || mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost || backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % |+---------------+-------------------------------------------+-----------+
新建test数据库,创建一个article表以备后续测试
CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(64) DEFAULT NULL, `content` text, PRIMARY KEY (`id`)) CHARSET=utf8;
重启服务并刷新数据库状态到存储文件中(with read lock表示在此过程中,客户端只能读数据,以便获得一个一致性的快照)
[root@zhenganwen ~]# service mysqld restartShutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root@zhenganwen mysql]# mysql -uroot -prootmysql> flush tables with read lock;Query OK, 0 rows affected (0.00 sec)
查看master上当前的二进制日志和偏移量(记一下其中的File和Position)
mysql> show master status G*************************** 1. row *************************** File: mysql-bin.000002 Position: 154 Binlog_Do_DB: test Binlog_Ignore_DB: mysql,information_schema,performation_schema,sysExecuted_Gtid_Set: 1 row in set (0.00 sec)
![0617caac940fb65ae119944eb53c8f01.png](https://i-blog.csdnimg.cn/blog_migrate/d756693e0570208bbf5d9ab491b43a8d.jpeg)
File表示实现复制功能的日志,即上图中的Binary log;Position则表示Binary log日志文件的偏移量之后的都会同步到slave中,那么在偏移量之前的则需要我们手动导入。
主服务器上面的任何修改都会保存在二进制日志Binary log里面,从服务器上面启动一个I/O thread(实际上就是一个主服务器的客户端进程),连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log里面。从服务器上面开启一个SQL thread定时检查Realy log,如果发现有更改立即把更改的内容在本机上面执行一遍。
如果一主多从的话,这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整,将二进制日志只给某一从,这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从,这样架构起来性能可能要好得多,而且数据之间的延时应该也稍微要好一些
![a9aaaa00479478e7f1fa0ab2c0a739e8.png](https://i-blog.csdnimg.cn/blog_migrate/28ea98b267d0681d25263ae98148a16c.jpeg)
手动导入,从master中导出数据
mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql
将test.sql中的内容在slave上执行一遍。
配置slave
修改slave的my.ini文件中的[mysqld]部分
log-bin=mysqlserver-id=1 #192.168.10.1
保存修改后重启slave,WIN+R->services.msc->MySQL5.7->重新启动
登录slave检查log_bin是否以被开启:
show VARIABLES like 'log_bin';
配置与master的同步复制:
stop slave; change master to master_host='192.168.10.10', -- master的IP master_user='backup', -- 之前在master上创建的用户 master_password='1234', master_log_file='mysql-bin.000002', -- master上 show master status G 提供的信息 master_log_pos=154;
启用slave节点并查看状态
mysql> start slave;mysql> show slave status G*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.10 Master_User: backup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 154 Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 154 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10 Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05 Master_Info_File: C:ProgramDataMySQLMySQL Server 5.7Datamaster.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)
注意查看第4、14、15三行,若与我一致,表示slave配置成功
测试
关闭master的读取锁定
mysql> unlock tables;Query OK, 0 rows affected (0.00 sec)
向master中插入一条数据
mysql> use testmysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)
查看slave是否自动同步了数据
mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');Query OK, 1 row affected (0.00 sec)
至此,主从复制的配置成功!:)
https://blog.csdn.net/xlgen157387/article/details/52452394
读写分离
读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。因为主从复制要求slave不能写只能读(如果对slave执行写操作,那么show slave status将会呈现Slave_SQL_Running=NO,此时你需要按照前面提到的手动同步一下slave)。
方案一、定义两种连接
就像我们在学JDBC时定义的DataBase一样,我们可以抽取出ReadDataBase,WriteDataBase implements DataBase,但是这种方式无法利用优秀的线程池技术如DruidDataSource帮我们管理连接,也无法利用Spring AOP让连接对DAO层透明。
方案二、使用Spring AOP
如果能够使用Spring AOP解决数据源切换的问题,那么就可以和Mybatis、Druid整合到一起了。
我们在整合Spring1和Mybatis时,我们只需写DAO接口和对应的SQL语句,那么DAO实例是由谁创建的呢?实际上就是Spring帮我们创建的,它通过我们注入的数据源,帮我们完成从中获取数据库连接、使用连接执行 SQL 语句的过程以及最后归还连接给数据源的过程。
如果我们能在调用DAO接口时根据接口方法命名规范(增addXXX/createXXX、删deleteXX/removeXXX、改updateXXXX、查selectXX/findXXX/getXX/queryXXX)动态地选择数据源(读数据源对应连接master而写数据源对应连接slave),那么就可以做到读写分离了。(可以参考:Spring 动态切换、添加数据源实现以及源码浅析)
项目结构
![b3b9f3e19c14389fcef186bea0197762.png](https://i-blog.csdnimg.cn/blog_migrate/2037d2a6042be19eb68422c434f1fcf4.jpeg)
引入依赖
其中,为了方便访问数据库引入了mybatis和druid,实现数据源动态切换主要依赖spring-aop和spring-aspects
org.mybatis mybatis-spring 1.3.2org.mybatis mybatis 3.4.6org.springframework spring-core 5.0.8.RELEASEorg.springframework spring-aop 5.0.8.RELEASEorg.springframework spring-jdbc 5.0.8.RELEASEcom.alibaba druid 1.1.6mysql mysql-connector-java 6.0.2org.springframework spring-context 5.0.8.RELEASEorg.springframework spring-aspects 5.0.8.RELEASEorg.projectlombok lombok 1.16.22org.springframework spring-test 5.0.8.RELEASEjunit junit 4.12
数据类
package top.zhenganwen.mysqloptimize.entity;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;@Data@AllArgsConstructor@NoArgsConstructorpublic class Article { private int id; private String title; private String content;}
spring配置文件
其中RoutingDataSourceImpl是实现动态切换功能的核心类,稍后介绍。
<?xml version="1.0" encoding="UTF-8"?>
dp.properties
master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTCmaster.db.username=rootmaster.db.password=rootslave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTCslave.db.username=rootslave.db.password=rootdb.driverClass=com.mysql.jdbc.Driver
mybatis-config.xml
<?xml version="1.0" encoding="UTF-8"?>
mapper接口和配置文件
ArticleMapper.java
package top.zhenganwen.mysqloptimize.mapper;import org.springframework.stereotype.Repository;import top.zhenganwen.mysqloptimize.entity.Article;import java.util.List;@Repositorypublic interface ArticleMapper { List findAll(); void add(Article article); void delete(int id);}
ArticleMapper.xml
<?xml version="1.0" encoding="UTF-8" ?> select * from article insert into article (title,content) values (#{title},#{content}) delete from article where id=#{id}
核心类
RoutingDataSourceImpl
package top.zhenganwen.mysqloptimize.dataSource;import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;import java.util.*;/** * RoutingDataSourceImpl class * 数据源路由 * * @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */public class RoutingDataSourceImpl extends AbstractRoutingDataSource { /** * key为read或write * value为DAO方法的前缀 * 什么前缀开头的方法使用读数据员,什么开头的方法使用写数据源 */ public static final Map> METHOD_TYPE_MAP = new HashMap>(); /** * 由我们指定数据源的id,由Spring切换数据源 * * @return */ @Override protected Object determineCurrentLookupKey() { System.out.println("数据源为:"+DataSourceHandler.getDataSource()); return DataSourceHandler.getDataSource(); } public void setMethodType(Map map) { for (String type : map.keySet()) { String methodPrefixList = map.get(type); if (methodPrefixList != null) { METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(