由于简书篇幅原因, 此篇是上文长文: 一篇解决面试常问的 MySQL 性能优化上篇(建议收藏)!, 如看到这篇文章的朋友可以先从上文看起, 文章有些长希望这篇文章能帮助到那些有需要的朋友!
集群
横向扩展: 从根本上 (单机的硬件处理能力有限) 提升数据库性能 . 由此而生的相关技术:== 读写分离, 负载均衡 ==
安装和配置主从复制
环境
- 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/server
- cd /export/server
- mv MySQL-5.7.23-Linux-glibc2.12-x86_64 MySQL
添加 MySQL 目录的所属组和所属者:
- groupadd MySQL
- useradd -r -g MySQL MySQL
- cd /export/server
- chown -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/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
将服务添加到开机自动启动
cp /export/server/MySQL/support-files/MySQL.server /etc/init.d/mysqld
启动服务
service mysqld start
配置环境变量, 在 / etc/profile 中添加如下内容
- # MySQL env
- MYSQL_HOME=/export/server/MySQL
- MYSQL_PATH=$MYSQL_HOME/bin
- PATH=$PATH:$MYSQL_PATH
- export 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/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
重启 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 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 | % |
- +---------------+-------------------------------------------+-----------+
新建 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 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)
查看 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,sys
- Executed_Gtid_Set:
- 1 row in set (0.00 sec)
File 表示实现复制功能的日志, 即上图中的 Binary log;Position 则表示 Binary log 日志文件的偏移量之后的都会同步到 slave 中, 那么在偏移量之前的则需要我们手动导入.
主服务器上面的任何修改都会保存在二进制日志 Binary log 里面, 从服务器上面启动一个 I/O thread(实际上就是一个主服务器的客户端进程), 连接到主服务器上面请求读取二进制日志, 然后把读取到的二进制日志写到本地的一个 Realy log 里面. 从服务器上面开启一个 SQL thread 定时检查 Realy log, 如果发现有更改立即把更改的内容在本机上面执行一遍.
如果一主多从的话, 这时主库既要负责写又要负责为几个从库提供二进制日志. 此时可以稍做调整, 将二进制日志只给某一从, 这一从再开启二进制日志并将自己的二进制日志再发给其它从. 或者是干脆这个从不记录只负责将二进制日志转发给其它从, 这样架构起来性能可能要好得多, 而且数据之间的延时应该也稍微要好一些
手动导入, 从 master 中导出数据
mysqldump -uroot -proot -hlocalhost test> /export/data/test.sql
将 test.sql 中的内容在 slave 上执行一遍.
配置 slave
修改 slave 的 my.INI 文件中的 [mysqld] 部分
- log-bin=MySQL
- server-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: 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)
注意查看第 4,14,15 三行, 若与我一致, 表示 slave 配置成功
测试
关闭 master 的读取锁定
- MySQL> unlock tables;
- Query OK, 0 rows affected (0.00 sec)
向 master 中插入一条数据
- 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)
查看 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)
至此, 主从复制的配置成功!:)
读写分离
读写分离是依赖于主从复制, 而主从复制又是为读写分离服务的. 因为主从复制要求 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
- <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>
数据类
- package top.zhenganwen.mysqloptimize.entity;
- import lombok.AllArgsConstructor;
- import lombok.Data;
- import lombok.NoArgsConstructor;
- @Data
- @AllArgsConstructor
- @NoArgsConstructor
- public class Article {
- private int id;
- private String title;
- private String content;
- }
spring 配置文件
其中 RoutingDataSourceImpl 是实现动态切换功能的核心类, 稍后介绍.
- <?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>
- dp.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
- mybatis-config.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>
mapper 接口和配置文件
ArticleMapper.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); }
ArticleMapper.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>
核心类
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<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>(); /** * 由我们指定数据源的 id, 由 Spring 切换数据源 * * @return */ @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(","))); } } } }
它的主要功能是, 本来我们只配置一个数据源, 因此 Spring 动态代理 DAO 接口时直接使用该数据源, 现在我们有了读, 写两个数据源, 我们需要加入一些自己的逻辑来告诉调用哪个接口使用哪个数据源(读数据的接口使用 slave, 写数据的接口使用 master. 这个告诉 Spring 该使用哪个数据源的类就是 AbstractRoutingDataSource, 必须重写的方法 determineCurrentLookupKey 返回数据源的标识, 结合 spring 配置文件(下段代码的 5,6 两行)
<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>
如果 determineCurrentLookupKey 返回 read 那么使用 slaveDataSource, 如果返回 write 就使用 masterDataSource.
DataSourceHandler 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(); } } DataSourceAspect 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 */ @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; } } } } }
测试读写分离
如何测试读是从 slave 中读的呢? 可以将写后复制到 slave 中的数据更改, 再读该数据就知道是从 slave 中读了.== 注意 ==, 一但对 slave 做了写操作就要重新手动将 slave 与 master 同步一下, 否则主从复制就会失效.
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); } }
负载均衡
负载均衡算法
轮询
加权轮询: 按照处理能力来加权
负载分配: 依据当前的空闲状态(但是测试每个节点的内存使用率, CPU 利用率等, 再做比较选出最闲的那个, 效率太低)
高可用
在服务器架构时, 为了保证服务器 7x24 不宕机在线状态, 需要为每台单点服务器 (由一台服务器提供服务的服务器, 如写服务器, 数据库中间件) 提供冗余机.
对于写服务器来说, 需要提供一台同样的写 - 冗余服务器, 当写服务器健康时(写 - 冗余通过心跳检测), 写 - 冗余作为一个从机的角色复制写服务器的内容与其做一个同步; 当写服务器宕机时, 写 - 冗余服务器便顶上来作为写服务器继续提供服务. 对外界来说这个处理过程是透明的, 即外界仅通过一个 IP 访问服务.
典型 SQL
线上 DDL
DDL(Database Definition Language)是指数据库表结构的定义 (create table) 和维护 (alter table) 的语言. 在线上执行 DDL, 在低于 MySQL5.6 版本时会导致全表被独占锁定, 此时表处于维护, 不可操作状态, 这会导致该期间对该表的所有访问无法响应. 但是在 MySQL5.6 之后, 支持 Online DDL, 大大缩短了锁定时间.
优化技巧是采用的维护表结构的 DDL(比如增加一列, 或者增加一个索引), 是 ==copy== 策略. 思路: 创建一个满足新结构的新表, 将旧表数据 == 逐条 == 导入 (复制) 到新表中, 以保证 == 一次性锁定的内容少 ==(锁定的是正在导入的数据), 同时旧表上可以执行其他任务. 导入的过程中, 将对旧表的所有操作以日志的形式记录下来, 导入完毕后, 将更新日志在新表上再执行一遍(确保一致性). 最后, 新表替换旧表(在应用程序中完成, 或者是数据库的 rename, 视图完成).
但随着 MySQL 的升级, 这个问题几乎淡化了.
数据库导入语句
在恢复数据时, 可能会导入大量的数据. 此时为了快速导入, 需要掌握一些技巧:
导入时 == 先禁用索引和约束 ==:
alter table table-name disable keys
待数据导入完成之后, 再开启索引和约束, 一次性创建索引
alter table table-name enable keys
数据库如果使用的引擎是 Innodb, 那么它 == 默认会给每条写指令加上事务 ==(这也会消耗一定的时间), 因此建议先手动开启事务, 再执行一定量的批量导入, 最后手动提交事务.
如果批量导入的 SQL 指令格式相同只是数据不同, 那么你应该先 prepare== 预编译 == 一下, 这样也能节省很多重复编译的时间.
limit offset,rows
尽量保证不要出现大的 offset, 比如 limit 10000,10 相当于对已查询出来的行数弃掉前 10000 行后再取 10 行, 完全可以加一些条件过滤一下(完成筛选), 而不应该使用 limit 跳过已查询到的数据. 这是一个 ==offset 做无用功 == 的问题. 对应实际工程中, 要避免出现大页码的情况, 尽量引导用户做条件过滤.
select * 要少用
即尽量选择自己需要的字段 select, 但这个影响不是很大, 因为网络传输多了几十上百字节也没多少延时, 并且现在流行的 ORM 框架都是用的 select *, 只是我们在设计表的时候注意将大数据量的字段分离, 比如商品详情可以单独抽离出一张商品详情表, 这样在查看商品简略页面时的加载速度就不会有影响了.
order by rand()不要用
它的逻辑就是随机排序(为每条数据生成一个随机数, 然后根据随机数大小进行排序). 如 select * from student order by rand() limit 5 的执行效率就很低, 因为它为表中的每条数据都生成随机数并进行排序, 而我们只要前 5 条.
解决思路: 在应用程序中, 将随机的主键生成好, 去数据库中利用主键检索.
单表和多表查询
多表查询: join, 子查询都是涉及到多表的查询. 如果你使用 explain 分析执行计划你会发现多表查询也是一个表一个表的处理, 最后合并结果. 因此可以说单表查询将计算压力放在了应用程序上, 而多表查询将计算压力放在了数据库上.
现在有 ORM 框架帮我们解决了单表查询带来的对象映射问题(查询单表时, 如果发现有外键自动再去查询关联表, 是一个表一个表查的).
count(*)
在 MyISAM 存储引擎中, 会自动记录表的行数, 因此使用 count(*)能够快速返回. 而 Innodb 内部没有这样一个计数器, 需要我们手动统计记录数量, 解决思路就是单独使用一张表:
id | table | count |
---|---|---|
1 | student | 100 |
limit 1
如果可以确定仅仅检索一条, 建议加上 limit 1, 其实 ORM 框架帮我们做到了这一点(查询单条的操作都会自动加上 limit 1).
慢查询日志
用于记录执行时间超过某个临界值的 SQL 日志, 用于快速定位慢查询, 为我们的优化做参考.
开启慢查询日志
配置项: slow_query_log
可以使用 show variables like 'slov_query_log'查看是否开启, 如果状态值为 OFF, 可以使用 set GLOBAL slow_query_log = on 来开启, 它会在 datadir 下产生一个 xxx-slow.log 的文件.
设置临界时间
配置项: long_query_time
查看: show VARIABLES like 'long_query_time', 单位秒
设置: set long_query_time=0.5
实操时应该从长时间设置到短的时间, 即将最慢的 SQL 优化掉
查看日志
一旦 SQL 超过了我们设置的临界时间就会被记录到 xxx-slow.log 中
profile 信息
配置项: profiling
开启 profile
set profiling=on
开启后, 所有的 SQL 执行的详细信息都会被自动记录下来
MySQL> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | OFF | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) MySQL> set profiling=on; Query OK, 0 rows affected, 1 warning (0.00 sec)
查看 profile 信息
show profiles MySQL> show variables like 'profiling'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | profiling | ON | +---------------+-------+ 1 row in set, 1 warning (0.00 sec) MySQL> insert into article values (null,'test profile',':)'); Query OK, 1 row affected (0.15 sec) MySQL> show profiles; +----------+------------+-------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------+ | 1 | 0.00086150 | show variables like 'profiling' | | 2 | 0.15027550 | insert into article values (null,'test profile',':)') | +----------+------------+-------------------------------------------------------+
通过 Query_ID 查看某条 SQL 所有详细步骤的时间
show profile for query Query_ID
上面 show profiles 的结果中, 每个 SQL 有一个 Query_ID, 可以通过它查看执行该 SQL 经过了哪些步骤, 各消耗了多场时间
典型的服务器配置
以下的配置全都取决于实际的运行环境
max_connections, 最大客户端连接数
MySQL> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+
table_open_cache, 表文件句柄缓存(表数据是存储在磁盘上的, 缓存磁盘文件的句柄方便打开文件读取数据)
MySQL> show variables like 'table_open_cache'; +------------------+-------+ | Variable_name | Value | +------------------+-------+ | table_open_cache | 2000 | +------------------+-------+
key_buffer_size, 索引缓存大小(将从磁盘上读取的索引缓存到内存, 可以设置大一些, 有利于快速检索)
MySQL> show variables like 'key_buffer_size'; +-----------------+---------+ | Variable_name | Value | +-----------------+---------+ | key_buffer_size | 8388608 | +-----------------+---------+
innodb_buffer_pool_size,Innodb 存储引擎缓存池大小(对于 Innodb 来说最重要的一个配置, 如果所有的表用的都是 Innodb, 那么甚至建议将该值设置到物理内存的 80%,Innodb 的很多性能提升如索引都是依靠这个)
MySQL> show variables like 'innodb_buffer_pool_size'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_buffer_pool_size | 8388608 | +-------------------------+---------+ innodb_file_per_table(innodb 中, 表数据存放在. ibd 文件中, 如果将该配置项设置为 ON, 那么一个表对应一个 ibd 文件, 否则所有 innodb 共享表空间)
压测工具 mysqlslap
安装 MySQL 时附带了一个压力测试工具 mysqlslap(位于 bin 目录下)
自动生成 sql 测试
C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 1.219 seconds Minimum number of seconds to run all queries: 1.219 seconds Maximum number of seconds to run all queries: 1.219 seconds Number of clients running queries: 1 Average number of queries per client: 0
并发测试
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 3.578 seconds Minimum number of seconds to run all queries: 3.578 seconds Maximum number of seconds to run all queries: 3.578 seconds Number of clients running queries: 100 Average number of queries per client: 0 C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 5.718 seconds Minimum number of seconds to run all queries: 5.718 seconds Maximum number of seconds to run all queries: 5.718 seconds Number of clients running queries: 150 Average number of queries per client: 0
多轮测试
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 5.398 seconds Minimum number of seconds to run all queries: 4.313 seconds Maximum number of seconds to run all queries: 6.265 seconds Number of clients running queries: 150 Average number of queries per client: 0
存储引擎测试
C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine innodb Average number of seconds to run all queries: 5.911 seconds Minimum number of seconds to run all queries: 5.485 seconds Maximum number of seconds to run all queries: 6.703 seconds Number of clients running queries: 150 Average number of queries per client: 0 C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Running for engine myisam Average number of seconds to run all queries: 53.104 seconds Minimum number of seconds to run all queries: 46.843 seconds Maximum number of seconds to run all queries: 60.781 seconds Number of clients running queries: 150 Average number of queries per client: 0
来源: http://www.jianshu.com/p/098ff5e94902