Mysql优化

最新推荐文章于 2021-10-24 09:10:22 发布

ningmengbaby

最新推荐文章于 2021-10-24 09:10:22 发布

阅读量140

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/ningmengbaby/article/details/102248359

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

概述

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度无法和内存相比

如何优化

设计数据库时：数据库表、字段的设计，存储引擎
利用好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的关联列
多个单列索引再多个查询条件时只会生效一个索引。多条件查询时最好建立组合索引

避免索引失效

最左前缀法则
不在索引上做任何操作，函数，类型转换（自动or手动）
尽量使用覆盖索引，索引列和查询列一致
like已%开头会索引失效
- 如果非要加在左边，用覆盖索引解决
字符串不加单引号索引失效，会自动类型转换
or,is null,!=会导致索引失效

集群

横向扩展：从根本上（单机的硬件处理能力有限）提升数据库性能。由此而生的相关技术：==读写分离、负载均衡==

安装和配置主从复制

环境

Red Hat Enterprise Linux Server release 7.0 (Maipo)（虚拟机）
mysql5.7（下载地址）

安装和配置

解压到对外提供的服务的目录（我自己专门创建了一个/export/server来存放）

<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目录的所属组和所属者：

<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服务

<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

<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中添加如下内容

<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登录

<code class="lang-shell">mysql -uroot -p

# 这里填写之前初始化服务时提供的密码

</code>

登录上去之后，更改root账户密码（我为了方便将密码改为root），否则操作数据库会报错

<code class="lang-mysql">set password=password('root');

flush privileges;

</code>

设置服务可被所有远程客户端访问

<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如下

<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>`

<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表可以看到我们刚创建的用户：

<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表以备后续测试

<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表示在此过程中，客户端只能读数据，以便获得一个一致性的快照）

<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）

<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]部分

<code class="lang-ini">log-bin=mysql

server-id=1 #192.168.10.1

</code>

保存修改后重启slave，WIN+R->services.msc->MySQL5.7->重新启动

1 2	`<code` `class="lang-sql">show VARIABLES like` `'log_bin';` `</code>`

配置与master的同步复制：

<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节点并查看状态

<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的读取锁定

<code class="lang-sql">mysql> unlock tables;

Query OK, 0 rows affected (0.00 sec)

</code>

向master中插入一条数据

<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是否自动同步了数据

<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

<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>

数据类

<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是实现动态切换功能的核心类，稍后介绍。

<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>

<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

<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

<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

<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

<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

<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两行）

<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

<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

<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同步一下，否则主从复制就会失效。

<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

ningmengbaby

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql优化

概述为什么要优化系统的吞吐量瓶颈往往出现在数据库的访问速度上随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢数据是存放在磁盘上的，读写速度无法和内存相比如何优化设计数据库时：数据库表、字段的设计，存储引擎利用好MySQL自身提供的功能，如索引等横向扩展：MySQL集群、负载均衡、读写分离 SQL语句的优化字段设计尽量使用整型表示字符串，存储...
复制链接

扫一扫

专栏目录