架构

al475-2epfc

每一个连接，在单独的线程中执行，进行验证后，先判断如果在查询缓存中，直接返回结果，否则通过解析器解析优化，然后发送存储引擎查询数据返回。

性能分析与优化

读锁共享，写锁互斥；

表锁，开销小，并发低；

行锁，开销大，并发高；

死锁，互相等待对方占有的资源

事务特性，原子性、一致性、隔离性、持久性

隔离级别，读未提交（脏读）、读已提交（不可重复读）、可重复读（幻读），串行化

多版本控制协议(MVCC)，系统中记录最新版本号，开启新事务会递增系统新版本号并作为此事务的版本号，事务中修改了数据，就在数据隐藏列记录事务版本号，查询时只记录小于等于此事务版本号的数据，实现了读不用加锁

慢日志

配置

1
1、 慢日志
2

3
#查看是否开启
4
show variables like '%slow_query_log%';
5

6
#开启
7
set global slow_query_log = 1;
8

9
#时间阈值
10
show variables like '%long_query_time%'
11

12
#设置
13
set global long_query_time = 1;
14

15
#重新打开链接，测试
16
SELECT sleep(4);
17

18
#查看慢日志条数
19
show global status like '%slow_queries%';

永久生效，vim /etc/mysql/my.cnf

1
slow_query_log =1
2

3
slow_query_log_file=/tmp/mysql_slow.log

慢日志分析

1
mysqldumpslow [option] logfile
2

3
  -s ORDER  排序方式
4

5
        c: 访问计数
6
        l: 锁定时间
7
        r: 返回记录
8
        t: 查询时间
9
        al:平均锁定时间
10
        ar:平均返回记录数
11
        at:平均查询时间
12

13
  -r           倒序排
14
  -t NUM       只返回前几条
15
  -g PATTERN   正则表达式

用 show profile 进行 sql 分析

show profile 用于查询 sql 执行的资源消耗，默认关闭，只记录最近 15 条

配置

1
#查询是否开启
2
show variables like '%profiling%';
3

4
#开启
5
set profiling = on;
6

7
#查看记录列表
8
show profiles

使用

1
show profile cpu, block io for query 5;

1
Show profile后面的一些参数：
2

3
All：显示所有的开销信息
4

5
Block io：显示块IO相关开销
6

7
Context switches： 上下文切换相关开销
8

9
Cpu：显示cpu相关开销
10

11
Memory：显示内存相关开销
12

13
Source：显示和source_function,source_file,source_line相关的开销信息

explain 执行计划

官方解释： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

id 查询序列号，倒序执行，从上至下
select_type 查询类型
- simple 简单查询，不包含子查询和 union
- primary 包含子查询的，最外层查询
- subquery select 或 where 里面包含子查询
- derived 在 from 列表中包含的子查询
- union 并集的第二个 select 查询
table 指定数据来源
partitions 匹配的分区
type 表的连接类型，性能由高到低排列
- system 表只有一行记录，相当于系统表
- const 通过索引查找，只匹配一行
- eq_ref 索引扫描，表记录一对一关联
- ref 索引扫描，表关联
- range 索引范围查询
- index 只遍历索引树
- ALL 全表扫描
possible_keys 使用的索引
key 实际使用的索引
key_len 索引中使用的字节数
ref 显示该表的索引字段，关联了哪张表
rows 扫描的行数
filtered 结果返回的行数占读取的行数
extra 额外信息
- using filesort 文件排序
- using temporary 使用临时表
- using index 使用了覆盖索引
- using where 使用了 where 子句
- using join buffer 使用连接缓冲

使用合适的数据类型

整数类型

使用占用空间更小的类型性能会更好
有符号和无符号占用空间相同，表示范围不同，性能相同
为整数指定宽度没有意义

实数类型

float 和 double 基于操作系统的浮点数近似计算
Decimal 是 mysql 实现的小数精确计算，需要更多的开销，比 float 和 double 性能差
大量财务数据，精确到最小计量单位，采用 bigint 存储

字符串类型

Varchar 存储变长字符串
char 存储定长字符串，存储时会剔除末尾空格
BLOB 存储二进制；text 存储变长大字符串有字符集

日期和时间类型

Datetime 精度秒，从 1001 年到 9999 年
timestamp 精度秒，记录 19700101 以来的秒数，最大到 2028 年，效率比 datetime 高

索引

最左前缀匹配，不能跳过索引列

索引列参与计算，无法走索引

尽量选择区分度高的列建立索引

扩展

主从复制

读多写少，访问性能低时采用，读写分离，主从复制

配置

准备多台 MySQL 服务器

在每台服务器上创建复制账号，并赋予权限

1
create user 'sla'@'%' identified with 'mysql_native_password' by '123456';
2
grant replication slave on *.* to 'sla'@'%';

在选定的主库上，配置二进制日志和唯一的服务器 ID，通过show master status命令检查配置

1
 server-id    = 1
2
 log_bin      = /var/log/mysql/mysql-bin.log
3
# binlog_expire_logs_seconds  = 2592000
4
max_binlog_size   = 100M
5
# binlog_do_db    = include_database_name
6
 binlog_ignore_db  = mysql
7
 binlog_ignore_db       = sys
8

9
log-slave-updates = 1

在备库上，配置二进制日志和唯一的服务器 ID，运行change master to，通过show slave status检查备库状态

1
 server-id    = 2
2
 log_bin      = /var/log/mysql/mysql-bin.log
3
# binlog_expire_logs_seconds  = 2592000
4
max_binlog_size   = 100M
5
# binlog_do_db    = include_database_name
6
 binlog_ignore_db  = mysql
7
 binlog_ignore_db       = sys
8
log-slave-updates = 1

1
change master to master_host='172.17.0.2',master_user='sla',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=0;

在备库上，执行start slave 开始复制

复制模式
- 基于语句模式，在从库上执行主库上的 SQL 语句
- 基于行模式，记录修改后的数据
- 混合模式

读写分离

分表

垂直分表

表中的字段太多，访问频率不同，将不经常访问的字段分离出来作为扩展表
水平分表

某个表数据量太大，按照主键或时间 range、hash 分到相同结构的多张表

分库

垂直分库

按功能，将不同功能相关的表放到不同的库里
水平分库

将表中数据分离到不同的库，每个库里表结构相同，数据不同

sharding-jdbc 框架分库分表配置

官网地址：https://shardingsphere.apache.org/

依赖配置

1
        <dependency>
2
            <groupId>org.springframework.boot</groupId>
3
            <artifactId>spring-boot-starter</artifactId>
4
            <version>${spring-boot.version}</version>
5
        </dependency>
6
        <dependency>
7
            <groupId>org.springframework.boot</groupId>
8
            <artifactId>spring-boot-starter-jdbc</artifactId>
9
            <version>${spring-boot.version}</version>
10
        </dependency>
11
        <dependency>
12
            <groupId>org.apache.shardingsphere</groupId>
13
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
14
            <version>4.1.1</version>
15
        </dependency>
16
        <dependency>
17
            <groupId>mysql</groupId>
18
            <artifactId>mysql-connector-java</artifactId>
19
            <version>8.0.22</version>
20
        </dependency>
21
        <!--日志-->
22
        <dependency>
23
            <groupId>org.springframework.boot</groupId>
24
            <artifactId>spring-boot-starter-logging</artifactId>
25
            <version>${spring-boot.version}</version>
26
        </dependency>
27

28
        <!--测试-->
29
        <dependency>
30
            <groupId>org.springframework.boot</groupId>
31
            <artifactId>spring-boot-starter-test</artifactId>
32
            <version>${spring-boot.version}</version>
33
            <exclusions>
34
                <exclusion>
35
                    <artifactId>junit</artifactId>
36
                    <groupId>junit</groupId>
37
                </exclusion>
38
            </exclusions>
39
        </dependency>
40

41
        <dependency>
42
            <groupId>junit</groupId>
43
            <artifactId>junit</artifactId>
44
            <version>4.12</version>
45
            <scope>test</scope>
46
        </dependency>

初始化表结构

1
create database sample_1;
2

3
create table sample_1.torder_1(
4
    id bigint not null auto_increment primary key ,
5
    user_id bigint not null ,
6
    create_time datetime default current_timestamp()
7
) comment '订单';
8

9
create table sample_1.torder_2(
10
    id bigint not null auto_increment primary key ,
11
    user_id bigint not null ,
12
    create_time datetime default current_timestamp()
13
) comment '订单';
14

15

16
create database sample_2;
17

18
create table sample_2.torder_1(
19
    id bigint not null auto_increment primary key ,
20
    user_id bigint not null ,
21
    create_time datetime default current_timestamp()
22
) comment '订单';
23

24
create table sample_2.torder_2(
25
    id bigint not null auto_increment primary key ,
26
    user_id bigint not null ,
27
    create_time datetime default current_timestamp()
28
) comment '订单';

项目配置application-share.properties

1
logging.config=classpath:logback/logback.xml
2

3

4
# 一个实体类对应两张表，覆盖
5
spring.main.allow-bean-definition-overriding=true
6

7
# 配置真实数据源
8
spring.shardingsphere.datasource.names=sample1,sample2
9

10
# 配置第 1 个数据源
11
spring.shardingsphere.datasource.sample1.type=com.zaxxer.hikari.HikariDataSource
12
spring.shardingsphere.datasource.sample1.driver-class-name=com.mysql.cj.jdbc.Driver
13
spring.shardingsphere.datasource.sample1.jdbc-url=jdbc:mysql://localhost:3306/sample_1
14
spring.shardingsphere.datasource.sample1.username=root
15
spring.shardingsphere.datasource.sample1.password=root
16

17
# 配置第 2 个数据源
18
spring.shardingsphere.datasource.sample2.type=com.zaxxer.hikari.HikariDataSource
19
spring.shardingsphere.datasource.sample2.driver-class-name=com.mysql.cj.jdbc.Driver
20
spring.shardingsphere.datasource.sample2.jdbc-url=jdbc:mysql://localhost:3306/sample_2
21
spring.shardingsphere.datasource.sample2.username=root
22
spring.shardingsphere.datasource.sample2.password=root
23

24
# 配置 torder 表规则
25
spring.shardingsphere.sharding.tables.torder.actual-data-nodes=sample$->{1..2}.torder_$->{1..2}
26

27
# 主键生成策略
28
spring.shardingsphere.sharding.tables.torder.key-generator.column=id
29
spring.shardingsphere.sharding.tables.torder.key-generator.type=SNOWFLAKE
30

31
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.sharding-column= user_id
32
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.algorithm-expression= sample${user_id % 2+1}
33

34
# 配置分表策略
35
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.sharding-column=id
36
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.algorithm-expression=torder_${id % 2+1}
37

38
# 日志打印sql
39
spring.shardingsphere.props.sql.show=true

测试代码

1
@RunWith(SpringRunner.class)
2
@SpringBootTest(classes = MainApp.class)
3
public class MainAppTest {
4

5
    @Autowired
6
    TorderMapper torderMapper;
7

8
    @Autowired
9
    NamedParameterJdbcTemplate jdbcTemplate;
10

11
    @Test
12
    public void test() throws Exception{
13
        System.out.println("sssss");
14

15
       for (int i=2;i<10;i++){
16
           Torder torder = new Torder();
17
           torder.setUserId(new Long(i));
18
           torder.setCreateTime(new Date());
19
           torderMapper.insert(torder);
20
       }
21

22
    }
23

24
    @Test
25
    public void testss() throws Exception{
26
        System.out.println("sssss");
27

28
        String s = "insert into torder(user_id, create_time) values (:userId,:createTime)";
29
        for (int i=2;i<10;i++){
30
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
31
            mapSqlParameterSource.addValue("userId",new Long(i));
32
            mapSqlParameterSource.addValue("createTime",new Date());
33
            jdbcTemplate.update(s,mapSqlParameterSource);
34
        }
35

36
    }
37

38
}

效果

sharding-jdbc 框架读写分离配置

项目配置application-rw.properties

1
# 一个实体类对应两张表，覆盖
2
spring.main.allow-bean-definition-overriding=true
3

4
# 配置真实数据源
5
spring.shardingsphere.datasource.names=sample1,sample2,sample1s1,sample2s1
6

7
# 配置第 1 个数据源
8
spring.shardingsphere.datasource.sample1.type=com.zaxxer.hikari.HikariDataSource
9
spring.shardingsphere.datasource.sample1.driver-class-name=com.mysql.cj.jdbc.Driver
10
spring.shardingsphere.datasource.sample1.jdbc-url=jdbc:mysql://localhost:3306/sample_1
11
spring.shardingsphere.datasource.sample1.username=root
12
spring.shardingsphere.datasource.sample1.password=root
13

14
# 配置第 1 个slave数据源
15
spring.shardingsphere.datasource.sample1s1.type=com.zaxxer.hikari.HikariDataSource
16
spring.shardingsphere.datasource.sample1s1.driver-class-name=com.mysql.cj.jdbc.Driver
17
spring.shardingsphere.datasource.sample1s1.jdbc-url=jdbc:mysql://localhost:13306/sample_1
18
spring.shardingsphere.datasource.sample1s1.username=root
19
spring.shardingsphere.datasource.sample1s1.password=root
20

21

22
# 配置第 2 个数据源
23
spring.shardingsphere.datasource.sample2.type=com.zaxxer.hikari.HikariDataSource
24
spring.shardingsphere.datasource.sample2.driver-class-name=com.mysql.cj.jdbc.Driver
25
spring.shardingsphere.datasource.sample2.jdbc-url=jdbc:mysql://localhost:3306/sample_2
26
spring.shardingsphere.datasource.sample2.username=root
27
spring.shardingsphere.datasource.sample2.password=root
28

29
# 配置第 2 个slave数据源
30
spring.shardingsphere.datasource.sample2s1.type=com.zaxxer.hikari.HikariDataSource
31
spring.shardingsphere.datasource.sample2s1.driver-class-name=com.mysql.cj.jdbc.Driver
32
spring.shardingsphere.datasource.sample2s1.jdbc-url=jdbc:mysql://localhost:13306/sample_2
33
spring.shardingsphere.datasource.sample2s1.username=root
34
spring.shardingsphere.datasource.sample2s1.password=root
35

36
## 主从读写分离
37
spring.shardingsphere.sharding.master-slave-rules.ds1.master-data-source-name=sample1
38
spring.shardingsphere.sharding.master-slave-rules.ds1.slave-data-source-names[0]=sample1s1
39
spring.shardingsphere.sharding.master-slave-rules.ds1.load-balance-algorithm-type=ROUND_ROBIN
40

41
spring.shardingsphere.sharding.master-slave-rules.ds2.master-data-source-name=sample2
42
spring.shardingsphere.sharding.master-slave-rules.ds2.slave-data-source-names[0]=sample2s1
43
spring.shardingsphere.sharding.master-slave-rules.ds2.load-balance-algorithm-type=ROUND_ROBIN
44

45

46
# 配置 torder 表规则
47
spring.shardingsphere.sharding.tables.torder.actual-data-nodes=ds$->{1..2}.torder_$->{1..2}
48

49
# 主键生成策略
50
spring.shardingsphere.sharding.tables.torder.key-generator.column=id
51
spring.shardingsphere.sharding.tables.torder.key-generator.type=SNOWFLAKE
52

53
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.sharding-column= user_id
54
spring.shardingsphere.sharding.tables.torder.database-strategy.inline.algorithm-expression= ds$->{user_id % 2+1}
55

56
# 配置分表策略
57
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.sharding-column=id
58
spring.shardingsphere.sharding.tables.torder.table-strategy.inline.algorithm-expression=torder_$->{id % 2+1}
59

60
# 日志打印sql
61
spring.shardingsphere.props.sql.show=true

测试代码

1
@RunWith(SpringRunner.class)
2
@SpringBootTest(classes = MainApp.class)
3
public class MainAppTest {
4

5
    @Autowired
6
    NamedParameterJdbcTemplate jdbcTemplate;
7

8
    @Test
9
    public void testss() throws Exception{
10
        System.out.println("sssss");
11

12
        String s = "insert into torder(user_id, create_time) values (:userId,:createTime)";
13
        for (int i=33;i<36;i++){
14
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
15
            mapSqlParameterSource.addValue("userId",new Long(i));
16
            mapSqlParameterSource.addValue("createTime",new Date());
17
            jdbcTemplate.update(s,mapSqlParameterSource);
18
        }
19

20
        String s2 = "select * from torder where user_id = :userId ";
21
        for (int i=33;i<36;i++){
22
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
23
            mapSqlParameterSource.addValue("userId",new Long(i));
24
            jdbcTemplate.queryForList(s2,mapSqlParameterSource);
25
        }
26
    }
27

28
}

结果

备份与恢复

逻辑备份

1
mysqldump -uroot -proot --all-databases >/tmp/all.sql
2
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql
3
mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

参考https://www.cnblogs.com/chenmh/p/5300370.html

物理备份

安装percona-xtrabackup-80

1
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
2

3
dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
4

5
percona-release enable-only tools release
6

7
apt-get update
8

9
apt-get install percona-xtrabackup-80
10

11
apt-get install qpress

执行备份恢复

1
#备份
2

3
xtrabackup --backup --target-dir=/data/backups/full/ -utest -pA@test.com
4

5
#回滚没有提交的日志，准备恢复
6

7
xtrabackup --prepare --target-dir=/data/backups/full/
8

9
#数据恢复
10

11
xtrabackup --copy-back --target-dir=/data/backups/full/