概述
为什么要优化
- 系统的吞吐量瓶颈往往出现在数据库的访问速度上
- 随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
- 数据是存放在磁盘上的,读写速度无法和内存相比
如何优化
- 设计数据库时:数据库表、字段的设计,存储引擎
- 利用好MySQL自身提供的功能,如索引等
- 横向扩展:MySQL集群、负载均衡、读写分离
- SQL语句的优化
字段设计
- 尽量使用整型表示字符串,存储IP用整型
- 关联表代替enum
- 金额:使用decimal,对数据的精度要求较高。double较大数的存储会损失精度,占用固定空间。
- 尽可能选择小的数据类型和指定短的长度。
- 尽可能使用not null,处理高效,不用判断为null
- 字段注释要完整,见名知意
- 单表字段不宜过多(二三十个极限)
- 可以预留字段
关联表的设计
一对多:使用外键
多对多:单独新建一张表将多对多拆分成两个一对多
一对一:使用相同的主键或者增加一个外键字段(如商品的基本信息(item
)和商品的详细信息(item_intro
),通常使用相同的主键或者增加一个外键字段(item_id
))
存储引擎选择
早期问题:如何选择MyISAM和Innodb?
现在不存在这个问题了,Innodb不断完善,从各个方面赶超MyISAM,也是MySQL默认使用的。
存储引擎Storage engine:MySQL中的数据、索引以及其他对象是如何存储的,是一套文件系统的实现。
1.功能差异
show engines
Engine | Support | Comment |
---|
InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys |
MyISAM | YES | MyISAM storage engine |
2.存储差异
MyISAM | Innodb |
---|
文件格式 | 数据和索引是分别存储的,数据.MYD ,索引.MYI | 数据和索引是集中存储的,.ibd |
文件能否移动 | 能,一张表就对应.frm 、MYD 、MYI 3个文件 | 否,因为关联的还有data 下的其它文件 |
记录存储顺序 | 按记录插入顺序保存 | 按主键大小有序插入 |
空间碎片(删除记录并flush table 表名 之后,表文件大小不变) | 产生。定时整理:使用命令optimize table 表名 实现 | 不产生 |
事务 | 不支持 | 支持 |
外键 | 不支持 | 支持 |
锁支持(锁是避免资源争用的一个机制,MySQL锁对用户几乎是透明的) | 表级锁定 | 行级锁定、表级锁定,锁定力度小 |
3.选择依据
如果没有特别的需求,使用默认的Innodb
即可。
MyISAM:以读写插入为主的应用程序,比如博客系统、新闻门户网站。
Innodb:更新(删除)操作频率也高,或者要保证数据的完整性;并发量高,支持事务和外键保证数据完整性。比如OA自动化办公系统。
索引
建立索引
-
select,update,delete语句中的where从句中的列
-
包含在order by,group by,distinct中的字段
-
多表join的关联列
-
多个单列索引再多个查询条件时只会生效一个索引。多条件查询时最好建立组合索引
避免索引失效
集群
横向扩展:从根本上(单机的硬件处理能力有限)提升数据库性能 。由此而生的相关技术:==读写分离、负载均衡==
安装和配置主从复制
环境
Red Hat Enterprise Linux Server release 7.0 (Maipo)
(虚拟机)mysql5.7
(下载地址)
安装和配置
解压到对外提供的服务的目录(我自己专门创建了一个/export/server
来存放)
1 2 3 4 | <code class = "lang-shell" >tar xzvf mysql- 5.7 . 23 -linux-glibc2. 12 -x86_64.tar.gz -C /export/server cd /export/server mv mysql- 5.7 . 23 -linux-glibc2. 12 -x86_64 mysql </code> |
添加mysql
目录的所属组和所属者:
1 2 3 4 5 6 | <code class = "lang-shell" >groupadd mysql useradd -r -g mysql mysql cd /export/server chown -R mysql:mysql mysql/ chmod -R 755 mysql/ </code> |
创建mysql
数据存放目录(其中/export/data
是我创建专门用来为各种服务存放数据的目录)
1 2 | <code class = "lang-shell" >mkdir /export/data/mysql </code> |
初始化mysql
服务
1 2 3 | <code class = "lang-shell" >cd /export/server/mysql ./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize </code> |
如果成功会显示mysql
的root
账户的初始密码,记下来以备后续登录。如果报错缺少依赖,则使用yum instally
依次安装即可
配置my.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | <code class = "lang-shell" >vim /etc/my.cnf [mysqld] basedir=/export/server/mysql datadir=/export/data/mysql socket=/tmp/mysql.sock user=mysql server-id= 10 # 服务id,在集群时必须唯一,建议设置为IP的第四段 port= 3306 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-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.log pid-file=/export/data/mysql/mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d </code> |
将服务添加到开机自动启动
1 2 | <code class = "lang-shell" >cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld </code> |
启动服务
1 2 | <code class = "lang-shell" >service mysqld start </code> |
配置环境变量,在/etc/profile
中添加如下内容
1 2 3 4 5 6 | <code class = "lang-shell" ># mysql env MYSQL_HOME=/export/server/mysql MYSQL_PATH=$MYSQL_HOME/bin PATH=$PATH:$MYSQL_PATH export PATH </code> |
使配置即可生效
1 2 | <code class = "lang-shell" >source /etc/profile </code> |
使用root
登录
1 2 3 | <code class = "lang-shell" >mysql -uroot -p # 这里填写之前初始化服务时提供的密码 </code> |
登录上去之后,更改root
账户密码(我为了方便将密码改为root),否则操作数据库会报错
1 2 3 | <code class = "lang-mysql" >set password=password( 'root' ); flush privileges; </code> |
设置服务可被所有远程客户端访问
1 2 3 4 | <code class = "lang-mysql" >use mysql; update user set host= '%' where user= 'root' ; flush privileges; </code> |
这样就可以在宿主机使用navicat
远程连接虚拟机linux上的mysql了
配置主从节点
配置master
以linux
(192.168.10.10
)上的mysql
为master
,宿主机(192.168.10.1
)上的mysql
为slave
配置主从复制。
修改master
的my.cnf
如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | <code class = "lang-shell" >[mysqld] basedir=/export/server/mysql datadir=/export/data/mysql socket=/tmp/mysql.sock user=mysql server-id= 10 port= 3306 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-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 log-bin=mysql-bin # 开启二进制日志 expire-logs-days= 7 # 设置日志过期时间,避免占满磁盘 binlog-ignore-db=mysql # 不使用主从复制的数据库 binlog-ignore-db=information_schema binlog-ignore-db=performation_schema binlog-ignore-db=sys binlog- do -db=test #使用主从复制的数据库 [mysqld_safe] log-error=/export/data/mysql/error.log pid-file=/export/data/mysql/mysql.pid # # include all files from the config directory # !includedir /etc/my.cnf.d </code> |
重启master
1 2 | <code class = "lang-shell" >service mysqld restart </code> |
登录master
查看配置是否生效(ON
即为开启,默认为OFF
):
1 2 3 4 5 6 7 | <code class = "lang-mysql" >mysql> show variables like 'log_bin' ; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | +---------------+-------+ </code> |
在master
的数据库中建立备份账号:backup
为用户名,%
表示任何远程地址,用户back
可以使用密码1234
通过任何远程客户端连接master
1 2 | <code class = "lang-mysql" >grant replication slave on *.* to 'backup' @ '%' identified by '1234' </code> |
查看user
表可以看到我们刚创建的用户:
1 2 3 4 5 6 7 8 9 10 11 | <code class = "lang-sql" >mysql> use mysql mysql> select user,authentication_string,host from user; +---------------+-------------------------------------------+-----------+ | user | authentication_string | host | +---------------+-------------------------------------------+-----------+ | root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | % | | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost | | backup | *A4B6157319038724E3560894F7F932C8886EBFCF | % | +---------------+-------------------------------------------+-----------+ </code> |
新建test
数据库,创建一个article
表以备后续测试
1 2 3 4 5 6 7 | <code class = "lang-sql" >CREATE TABLE `article` ( `id` int ( 11 ) NOT NULL AUTO_INCREMENT, `title` varchar( 64 ) DEFAULT NULL, `content` text, PRIMARY KEY (`id`) ) CHARSET=utf8; </code> |
重启服务并刷新数据库状态到存储文件中(with read lock
表示在此过程中,客户端只能读数据,以便获得一个一致性的快照)
1 2 3 4 5 6 7 | <code class = "lang-shell" >[root @zhenganwen ~]# service mysqld restart Shutting down MySQL.... SUCCESS! Starting MySQL. SUCCESS! [root @zhenganwen mysql]# mysql -uroot -proot mysql> flush tables with read lock; Query OK, 0 rows affected ( 0.00 sec) </code> |
查看master
上当前的二进制日志和偏移量(记一下其中的File
和Position
)
1 2 3 4 5 6 7 8 9 | <code class = "lang-sql" >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,sys Executed_Gtid_Set: 1 row in set ( 0.00 sec) </code> |
File
表示实现复制功能的日志,即上图中的Binary log
;Position
则表示Binary log
日志文件的偏移量之后的都会同步到slave
中,那么在偏移量之前的则需要我们手动导入。
主服务器上面的任何修改都会保存在二进制日志Binary log里面,从服务器上面启动一个I/O thread(实际上就是一个主服务器的客户端进程),连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log里面。从服务器上面开启一个SQL thread定时检查Realy log,如果发现有更改立即把更改的内容在本机上面执行一遍。
如果一主多从的话,这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整,将二进制日志只给某一从,这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从,这样架构起来性能可能要好得多,而且数据之间的延时应该也稍微要好一些
手动导入,从master
中导出数据
1 2 | <code class = "lang-shell" >mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql </code> |
将test.sql
中的内容在slave
上执行一遍。
配置slave
修改slave
的my.ini
文件中的[mysqld]
部分
1 2 3 | <code class = "lang-ini" >log-bin=mysql server-id= 1 # 192.168 . 10.1 </code> |
保存修改后重启slave
,WIN+R
->services.msc
->MySQL5.7
->重新启动
登录slave
检查log_bin
是否以被开启:
1 2 | <code class = "lang-sql" >show VARIABLES like 'log_bin' ; </code> |
配置与master
的同步复制:
1 2 3 4 5 6 7 8 | <code class = "lang-sql" >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 ; </code> |
启用slave
节点并查看状态
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | <code class = "lang-sql" >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: 0 Master_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:\ProgramData\MySQL\MySQL Server 5.7 \Data\master.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) </code> |
注意查看第4、14、15三行,若与我一致,表示slave
配置成功
测试
关闭master
的读取锁定
1 2 3 | <code class = "lang-sql" >mysql> unlock tables; Query OK, 0 rows affected ( 0.00 sec) </code> |
向master
中插入一条数据
1 2 3 4 | <code class = "lang-sql" >mysql> use test mysql> insert into article (title,content) values ( 'mysql master and slave' , 'record the cluster building succeed!:)' ); Query OK, 1 row affected ( 0.00 sec) </code> |
查看slave
是否自动同步了数据
1 2 3 | <code class = "lang-sql" >mysql> insert into article (title,content) values ( 'mysql master and slave' , 'record the cluster building succeed!:)' ); Query OK, 1 row affected ( 0.00 sec) </code> |
至此,主从复制的配置成功!
读写分离
读写分离是依赖于主从复制,而主从复制又是为读写分离服务的。因为主从复制要求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
),那么就可以做到读写分离了。
项目结构
引入依赖
其中,为了方便访问数据库引入了mybatis
和druid
,实现数据源动态切换主要依赖spring-aop
和spring-aspects
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <code class = "lang-xml" ><dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version> 1.3 . 2 </version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version> 3.4 . 6 </version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version> 1.1 . 6 </version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version> 6.0 . 2 </version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version> 1.16 . 22 </version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version> 5.0 . 8 .RELEASE</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version> 4.12 </version> </dependency> </dependencies> </code> |
数据类
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code class = "lang-java" > package top.zhenganwen.mysqloptimize.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; <a href= "/profile/1954537" data-card-uid= "1954537" class = "" target= "_blank" style= "color: #25bb9b" data-card-index= "3" > @Data @AllArgsConstructor @NoArgsConstructor public class Article { private int id; private String title; private String content; } </a></code> |
spring配置文件
其中RoutingDataSourceImpl
是实现动态切换功能的核心类,稍后介绍。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | <code class = "lang-xml" ><?xml version= "1.0" encoding= "UTF-8" ?> <beans xmlns= "http://www.springframework.org/schema/beans" xmlns:xsi= "http://www.w3.org/2001/XMLSchema-instance" xmlns:context= "http://www.springframework.org/schema/context" xsi:schemaLocation= "http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd" > <context:property-placeholder location= "db.properties" ></context:property-placeholder> <context:component-scan base- package = "top.zhenganwen.mysqloptimize" /> <bean id= "slaveDataSource" class = "com.alibaba.druid.pool.DruidDataSource" > <property name= "driverClassName" value= "${db.driverClass}" /> <property name= "url" value= "${master.db.url}" ></property> <property name= "username" value= "${master.db.username}" ></property> <property name= "password" value= "${master.db.password}" ></property> </bean> <bean id= "masterDataSource" class = "com.alibaba.druid.pool.DruidDataSource" > <property name= "driverClassName" value= "${db.driverClass}" /> <property name= "url" value= "${slave.db.url}" ></property> <property name= "username" value= "${slave.db.username}" ></property> <property name= "password" value= "${slave.db.password}" ></property> </bean> <bean id= "dataSourceRouting" class = "top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl" > <property name= "defaultTargetDataSource" ref= "masterDataSource" ></property> <property name= "targetDataSources" > <map key-type= "java.lang.String" value-type= "javax.sql.DataSource" > <entry key= "read" value-ref= "slaveDataSource" /> <entry key= "write" value-ref= "masterDataSource" /> </map> </property> <property name= "methodType" > <map key-type= "java.lang.String" value-type= "java.lang.String" > <entry key= "read" value= "query,find,select,get,load," ></entry> <entry key= "write" value= "update,add,create,delete,remove,modify" /> </map> </property> </bean> <!-- Mybatis文件 --> <bean id= "sqlSessionFactory" class = "org.mybatis.spring.SqlSessionFactoryBean" > <property name= "configLocation" value= "classpath:mybatis-config.xml" /> <property name= "dataSource" ref= "dataSourceRouting" /> <property name= "mapperLocations" value= "mapper/*.xml" /> </bean> <bean class = "org.mybatis.spring.mapper.MapperScannerConfigurer" > <property name= "basePackage" value= "top.zhenganwen.mysqloptimize.mapper" /> <property name= "sqlSessionFactoryBeanName" value= "sqlSessionFactory" /> </bean> </beans> </code> |
dp.properties
1 2 3 4 5 6 7 8 9 10 | <code class = "lang-properties" >master.db.url=jdbc:mysql: //localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC master.db.username=root master.db.password=root slave.db.url=jdbc:mysql: //192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC slave.db.username=root slave.db.password=root db.driverClass=com.mysql.jdbc.Driver </code> |
mybatis-config.xml
1 2 3 4 5 6 7 8 9 10 | <code class = "lang-xml" ><?xml version= "1.0" encoding= "UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" > <configuration> <typeAliases> <typeAlias type= "top.zhenganwen.mysqloptimize.entity.Article" alias= "Article" /> </typeAliases> </configuration> </code> |
mapper接口和配置文件
ArticleMapper.java
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | <code class = "lang-java" > package top.zhenganwen.mysqloptimize.mapper; import org.springframework.stereotype.Repository; import top.zhenganwen.mysqloptimize.entity.Article; import java.util.List; @Repository public interface ArticleMapper { List<Article> findAll(); void add(Article article); void delete( int id); } </code> |
ArticleMapper.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code class = "lang-xml" ><?xml version= "1.0" encoding= "UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace= "top.zhenganwen.mysqloptimize.mapper.ArticleMapper" > <select id= "findAll" resultType= "Article" > select * from article </select> <insert id= "add" parameterType= "Article" > insert into article (title,content) values (#{title},#{content}) </insert> <delete id= "delete" parameterType= "int" > delete from article where id=#{id} </delete> </mapper> </code> |
核心类
RoutingDataSourceImpl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | <code class = "lang-java" > 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<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>(); /** * 由我们指定数据源的id,由Spring切换数据源 * * <a href="/profile/547241" data-card-uid="547241" class="" target="_blank" style="color: #25bb9b" data-card-index="4">@return */ </a><a href= "/profile/992988" data-card-uid= "992988" class = "" target= "_blank" style= "color: #25bb9b" data-card-index= "5" > @Override protected Object determineCurrentLookupKey() { System.out.println( "数据源为:" +DataSourceHandler.getDataSource()); return DataSourceHandler.getDataSource(); } public void setMethodType(Map<String, String> map) { for (String type : map.keySet()) { String methodPrefixList = map.get(type); if (methodPrefixList != null ) { METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split( "," ))); } } } } </a></code> |
它的主要功能是,本来我们只配置一个数据源,因此Spring
动态代理DAO接口时直接使用该数据源,现在我们有了读、写两个数据源,我们需要加入一些自己的逻辑来告诉调用哪个接口使用哪个数据源(读数据的接口使用slave
,写数据的接口使用master
。这个告诉Spring
该使用哪个数据源的类就是AbstractRoutingDataSource
,必须重写的方法determineCurrentLookupKey
返回数据源的标识,结合spring
配置文件(下段代码的5,6两行)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | <code class = "lang-xml" ><bean id= "dataSourceRouting" class = "top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl" > <property name= "defaultTargetDataSource" ref= "masterDataSource" ></property> <property name= "targetDataSources" > <map key-type= "java.lang.String" value-type= "javax.sql.DataSource" > <entry key= "read" value-ref= "slaveDataSource" /> <entry key= "write" value-ref= "masterDataSource" /> </map> </property> <property name= "methodType" > <map key-type= "java.lang.String" value-type= "java.lang.String" > <entry key= "read" value= "query,find,select,get,load," ></entry> <entry key= "write" value= "update,add,create,delete,remove,modify" /> </map> </property> </bean> </code> |
如果determineCurrentLookupKey
返回read
那么使用slaveDataSource
,如果返回write
就使用masterDataSource
。
DataSourceHandler
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <code class = "lang-java" > package top.zhenganwen.mysqloptimize.dataSource; /** * DataSourceHandler class * <p> * 将数据源与线程绑定,需要时根据线程获取 * * @author zhenganwen, blog:zhenganwen.top * @date 2018/12/29 */ public class DataSourceHandler { /** * 绑定的是read或write,表示使用读或写数据源 */ private static final ThreadLocal<String> holder = new ThreadLocal<String>(); public static void setDataSource(String dataSource) { System.out.println(Thread.currentThread().getName()+ "设置了数据源类型" ); holder.set(dataSource); } public static String getDataSource() { System.out.println(Thread.currentThread().getName()+ "获取了数据源类型" ); return holder.get(); } } </code> |
DataSourceAspect
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | <code class = "lang-java" > package top.zhenganwen.mysqloptimize.dataSource; import org.aspectj.lang.JoinPoint; import org.aspectj.lang.annotation.Aspect; import org.aspectj.lang.annotation.Before; import org.aspectj.lang.annotation.Pointcut; import org.springframework.context.annotation.EnableAspectJAutoProxy; import org.springframework.stereotype.Component; import java.util.List; import java.util.Set; import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP; /** * DataSourceAspect class * * 配置切面,根据方法前缀设置读、写数据源 * 项目启动时会加载该bean,并按照配置的切面(哪些切入点、如何增强)确定动态代理逻辑 * @author zhenganwen,blog:zhenganwen.top * @date 2018/12/29 */ <a href= "/profile/664319079" data-card-uid= "664319079" class = "" target= "_blank" style= "color: #25bb9b" data-card-index= "6" > @Component //声明这是一个切面,这样Spring才会做相应的配置,否则只会当做简单的bean注入 @Aspect @EnableAspectJAutoProxy public class DataSourceAspect { /** * 配置切入点:DAO包下的所有类的所有方法 */ @Pointcut ( "execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))" ) public void aspect() { } /** * 配置前置增强,对象是aspect()方法上配置的切入点 */ @Before ( "aspect()" ) public void before(JoinPoint point) { String className = point.getTarget().getClass().getName(); String invokedMethod = point.getSignature().getName(); System.out.println( "对 " +className+ "$" +invokedMethod+ " 做了前置增强,确定了要使用的数据源类型" ); Set<String> dataSourceType = METHOD_TYPE_MAP.keySet(); for (String type : dataSourceType) { List<String> prefixList = METHOD_TYPE_MAP.get(type); for (String prefix : prefixList) { if (invokedMethod.startsWith(prefix)) { DataSourceHandler.setDataSource(type); System.out.println( "数据源为:" +type); return ; } } } } } </a></code> |
测试读写分离
如何测试读是从slave
中读的呢?可以将写后复制到slave
中的数据更改,再读该数据就知道是从slave
中读了。==注意==,一但对slave
做了写操作就要重新手动将slave
与master
同步一下,否则主从复制就会失效。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 | <code class = "lang-java" > package top.zhenganwen.mysqloptimize.dataSource; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import top.zhenganwen.mysqloptimize.entity.Article; import top.zhenganwen.mysqloptimize.mapper.ArticleMapper; @RunWith (SpringJUnit4ClassRunner. class ) @ContextConfiguration (locations = "classpath:spring-mybatis.xml" ) public class RoutingDataSourceTest { @Autowired ArticleMapper articleMapper; @Test public void testRead() { System.out.println(articleMapper.findAll()); } @Test public void testAdd() { Article article = new Article( 0 , "我是新插入的文章" , "测试是否能够写到master并且复制到slave中" ); articleMapper.add(article); } @Test public void testDelete() { articleMapper.delete( 2 ); } } </code> |
负载均衡
负载均衡算法
- 轮询
- 加权轮询:按照处理能力来加权
- 负载分配:依据当前的空闲状态(但是测试每个节点的内存使用率、CPU利用率等,再做比较选出最闲的那个,效率太低)
高可用
在服务器架构时,为了保证服务器7x24不宕机在线状态,需要为每台单点服务器(由一台服务器提供服务的服务器,如写服务器、数据库中间件)提供冗余机。
对于写服务器来说,需要提供一台同样的写-冗余服务器,当写服务器健康时(写-冗余通过心跳检测),写-冗余作为一个从机的角色复制写服务器的内容与其做一个同步;当写服务器宕机时,写-冗余服务器便顶上来作为写服务器继续提供服务。对外界来说这个处理过程是透明的,即外界仅通过一个IP访问服务。
参考:https://www.nowcoder.com/discuss/150059?type=0&order=0&pos=13&page=0