长文：一篇解决面试常问的MySQL性能优化下篇（建议收藏）！
2019-04-19 本文已影响14人
若丨寒
由于简书篇幅原因，此篇是上文长文：一篇解决面试常问的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
