文章
文章用户文档快讯圈子网址导航

超级全面的MySQL优化面试解析

释放双眼，带上耳机，听听看~！

作者：Anwen
juejin.im/post/5c6b9c09f265da2d8a55a855

本文概要

概述

为什么要优化

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

如何优化

  • 设计数据库时：数据库表、字段的设计，存储引擎
  • 利用好MySQL自身提供的功能，如索引等
  • 横向扩展：MySQL集群、负载均衡、读写分离
  • SQL语句的优化（收效甚微）

字段设计

原则：尽量使用整型表示字符串

存储IP

MySQL内部的枚举类型（单选）和集合（多选）类型

原则：定长和非定长数据类型的选择

金额

定点数decimal

小单位大数额避免出现小数

字符串存储

原则：尽可能选择小的数据类型和指定短的长度

原则：尽可能使用 not null

原则：字段注释要完整，见名知意

原则：单表字段不宜过多

原则：可以预留字段

关联表的设计

一对多

多对多

一对一

范式 Normal Format

第一范式1NF：字段原子性

第二范式：消除对主键的部分依赖

MySQL
教育大楼1525
周一
张三
Java
教育大楼1521
周三
李四
MySQL
教育大楼1521
周五
张三

第三范式：消除对主键的传递依赖

1001
周一
教育大楼1521
3546

3546
Java
张三

存储引擎选择

功能差异

InnoDB
DEFAULT
Supports transactions, row-level locking, and foreign keys
MyISAM
YES
MyISAM storage engine

存储差异

文件格式
数据和索引是分别存储的，数据.MYD，索引.MYI
数据和索引是集中存储的，.ibd
文件能否移动
能，一张表就对应.frm、MYD、MYI3个文件
否，因为关联的还有data下的其它文件
记录存储顺序
按记录插入顺序保存
按主键大小有序插入
空间碎片（删除记录并flush table 表名之后，表文件大小不变）
产生。定时整理：使用命令optimize table 表名实现
不产生
事务
不支持
支持
外键
不支持
支持
锁支持（锁是避免资源争用的一个机制，MySQL锁对用户几乎是透明的）
表级锁定
行级锁定、表级锁定，锁定力度小并发能力高

选择依据

索引

索引检索为什么快？

  • 关键字相对于数据本身，

数据量小

  • 关键字是

有序的，二分查找可快速确定位置

MySQL中索引类型

  • 普通索引：对关键字没有限制
  • 唯一索引：要求记录提供的关键字不能重复
  • 主键索引：要求关键字唯一且不为null

索引管理语法

查看索引

创建索引

创建表之后建立索引



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
1create TABLE user_index(

2   id int auto_increment primary key,

3   first_name varchar(16),

4   last_name VARCHAR(16),

5   id_card VARCHAR(18),

6   information text

7);

8

9-- 更改表结构

10alter table user_index

11-- 创建一个first_name和last_name的复合索引，并命名为name

12add key name (first_name,last_name),

13-- 创建一个id_card的唯一索引，默认以字段名作为索引名

14add UNIQUE KEY (id_card),

15-- 鸡肋，全文索引不支持中文

16add FULLTEXT KEY (information);

17

18

创建表时指定索引



1
2
3
4
5
6
7
8
9
10
11
12
1CREATE TABLE user_index2 (

2   id INT auto_increment PRIMARY KEY,

3   first_name VARCHAR (16),

4   last_name VARCHAR (16),

5   id_card VARCHAR (18),

6   information text,

7   KEY name (first_name, last_name),

8   FULLTEXT KEY (information),

9   UNIQUE KEY (id_card)

10);

11

12

删除索引



1
2
3
4
5
1alter table user_index drop KEY name;

2alter table user_index drop KEY id_card;

3alter table user_index drop KEY information;

4

5





1
2
3
4
5
6
1alter table user_index

2-- 重新定义字段

3MODIFY id int,

4drop PRIMARY KEY

5

6

执行计划explain



1
2
3
4
5
6
7
8
9
10
11
12
13
1CREATE TABLE innodb1 (

2   id INT auto_increment PRIMARY KEY,

3   first_name VARCHAR (16),

4   last_name VARCHAR (16),

5   id_card VARCHAR (18),

6   information text,

7   KEY name (first_name, last_name),

8   FULLTEXT KEY (information),

9   UNIQUE KEY (id_card)

10);

11insert into innodb1 (first_name,last_name,id_card,information) values ('张','三','1001','华山派');

12

13

索引使用场景（重点）

where



1
2
3
4
5
6
1-- 增加一个没有建立索引的字段

2alter table innodb1 add sex char(1);

3-- 按sex检索时可选的索引为null

4EXPLAIN SELECT * from innodb1 where sex='男';

5

6

order by

join

索引覆盖

语法细节（要点）

字段要独立出现



1
2
3
4
1select * from user where id = 20-1;

2select * from user where id+1 = 20;

3

4

like查询，不能以通配符开头



1
2
3
1select * from article where title like '%mysql%';

2

3





1
2
3
1select * from article where title like 'mysql%';

2

3

复合索引只对第一个字段有效



1
2
3
1alter table person add index(first_name,last_name);

2

3

or，两边条件都有索引可用

状态值，不容易使用到索引

如何创建索引

  • 建立基础索引：在where、order by、join字段上建立索引。

  • 优化，组合索引：基于业务逻辑

  • 如果条件经常性出现在一起，那么可以考虑将多字段索引升级为

复合索引
* 如果通过增加个别字段的索引，就可以出现
索引覆盖，那么可以考虑为该字段建立索引
* 查询时，不常用到的索引，应该删除掉

前缀索引

索引的存储结构

BTree

B+Tree聚簇结构

哈希索引

查询缓存

在配置文件中开启缓存

  • 0：不开启
  • 1：开启，默认缓存所有，需要在SQL语句中增加select sql-no-cache提示来放弃缓存
  • 2：开启，默认都不缓存，需要在SQL语句中增加select sql-cache来主动缓存（

常用）



1
2
3
4
1show variables like 'query_cache_type';

2query_cache_type   DEMAND

3

4

在客户端设置缓存大小



1
2
3
4
5
6
7
8
1show variables like 'query_cache_size';

2query_cache_size   0

3

4set global query_cache_size=64*1024*1024;

5show variables like 'query_cache_size';

6query_cache_size   67108864

7

8

将查询结果缓存

重置缓存

缓存失效问题（大问题）

注意事项

  1. 应用程序，不应该关心query cache的使用情况。可以尝试使用，但不能由query cache决定业务逻辑，因为query cache由DBA来管理。
  2. 缓存是以SQL语句为key存储的，因此即使SQL语句功能相同，但如果多了一个空格或者大小写有差异都会导致匹配不到缓存。

分区



1
2
3
4
5
6
7
1create table article(

2   id int auto_increment PRIMARY KEY,

3   title varchar(64),

4   content text

5)PARTITION by HASH(id) PARTITIONS 10

6

7

MySQL提供的分区算法

hash(field)

key(field)



1
2
3
4
5
6
7
8
1create table article_key(

2   id int auto_increment,

3   title varchar(64),

4   content text,

5   PRIMARY KEY (id,title)  -- 要求分区依据字段必须是主键的一部分

6)PARTITION by KEY(title) PARTITIONS 10

7

8

range算法



1
2
3
4
5
6
7
8
9
10
11
12
13
14
1create table article_range(

2   id int auto_increment,

3   title varchar(64),

4   content text,

5   created_time int,   -- 发布时间到1970-1-1的毫秒数

6   PRIMARY KEY (id,created_time)   -- 要求分区依据字段必须是主键的一部分

7)charset=utf8

8PARTITION BY RANGE(created_time)(

9   PARTITION p201808 VALUES less than (1535731199),    -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')

10  PARTITION p201809 VALUES less than (1538323199),    -- 2018-9-30 23:59:59

11  PARTITION p201810 VALUES less than (1541001599) -- 2018-10-31 23:59:59

12);

13

14





1
2
3
4
1insert into article_range values(null,'MySQL优化','内容示例',1535731180);

2flush tables;  -- 使操作立即刷新到磁盘文件

3

4

list算法



1
2
3
4
5
6
7
8
9
10
11
12
13
1create table article_list(

2   id int auto_increment,

3   title varchar(64),

4   content text,

5   status TINYINT(1),  -- 文章状态：0-草稿，1-完成但未发布，2-已发布

6   PRIMARY KEY (id,status) -- 要求分区依据字段必须是主键的一部分

7)charset=utf8

8PARTITION BY list(status)(

9   PARTITION writing values in(0,1),   -- 未发布的放在一个分区   

10  PARTITION published values in (2)   -- 已发布的放在一个分区

11);

12

13





1
2
3
4
1insert into article_list values(null,'mysql优化','内容示例',0);

2flush tables;

3

4

分区管理语法

range/list

增加分区



1
2
3
4
5
6
1alter table article_range add partition(

2   partition p201811 values less than (1543593599) -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')

3   -- more

4);

5

6

删除分区



1
2
3
1alter table article_range drop PARTITION p201808

2

3

key/hash

新增分区



1
2
3
1alter table article_key add partition partitions 4

2

3

销毁分区



1
2
3
1alter table article_key coalesce partition 6

2

3

分区的使用

水平分割和垂直分割

分表原因

  • 为数据库减压
  • 分区算法局限
  • 数据库支持不完善（5.1之后mysql才支持分区操作）

id重复的解决方案

  • 借用第三方应用如memcache、redis的id自增器
  • 单独建一张只包含id一个字段的表，每次自增该字段作为数据记录的id

集群

安装和配置主从复制

环境

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

安装和配置



1
2
3
4
5
1tar xzvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /export/server

2cd /export/server

3mv mysql-5.7.23-linux-glibc2.12-x86_64 mysql

4

5





1
2
3
4
5
6
7
1groupadd mysql

2useradd -r -g mysql mysql

3cd /export/server

4chown -R mysql:mysql mysql/

5chmod -R 755 mysql/

6

7





1
2
3
1mkdir /export/data/mysql

2

3





1
2
3
4
1cd /export/server/mysql

2./bin/mysqld --basedir=/export/server/mysql --datadir=/export/data/mysql --user=mysql --pid-file=/export/data/mysql/mysql.pid --initialize

3

4





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
1vim /etc/my.cnf

2

3[mysqld]

4basedir=/export/server/mysql

5datadir=/export/data/mysql

6socket=/tmp/mysql.sock

7user=mysql

8server-id=10 # 服务id，在集群时必须唯一，建议设置为IP的第四段

9port=3306

10# Disabling symbolic-links is recommended to prevent assorted security risks

11symbolic-links=0

12# Settings user and group are ignored when systemd is used.

13# If you need to run mysqld under a different user or group,

14# customize your systemd unit file for mariadb according to the

15# instructions in http://fedoraproject.org/wiki/Systemd

16

17[mysqld_safe]

18log-error=/export/data/mysql/error.log

19pid-file=/export/data/mysql/mysql.pid

20

21#

22# include all files from the config directory

23#

24!includedir /etc/my.cnf.d

25

26





1
2
3
1cp /export/server/mysql/support-files/mysql.server /etc/init.d/mysqld

2

3





1
2
3
1service mysqld start

2

3





1
2
3
4
5
6
7
1# mysql env

2MYSQL_HOME=/export/server/mysql

3MYSQL_PATH=$MYSQL_HOME/bin

4PATH=$PATH:$MYSQL_PATH

5export PATH

6

7





1
2
3
1source /etc/profile

2

3





1
2
3
4
1mysql -uroot -p

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

3

4





1
2
3
4
1set password=password('root');

2flush privileges;

3

4





1
2
3
4
5
1use mysql;

2update user set host='%' where user='root';

3flush privileges;

4

5

配置主从节点

配置master



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
1[mysqld]

2basedir=/export/server/mysql

3datadir=/export/data/mysql

4socket=/tmp/mysql.sock

5user=mysql

6server-id=10

7port=3306

8# Disabling symbolic-links is recommended to prevent assorted security risks

9symbolic-links=0

10# Settings user and group are ignored when systemd is used.

11# If you need to run mysqld under a different user or group,

12# customize your systemd unit file for mariadb according to the

13# instructions in http://fedoraproject.org/wiki/Systemd

14

15log-bin=mysql-bin # 开启二进制日志

16expire-logs-days=7  # 设置日志过期时间，避免占满磁盘

17binlog-ignore-db=mysql    # 不使用主从复制的数据库

18binlog-ignore-db=information_schema

19binlog-ignore-db=performation_schema

20binlog-ignore-db=sys

21binlog-do-db=test #使用主从复制的数据库

22

23[mysqld_safe]

24log-error=/export/data/mysql/error.log

25pid-file=/export/data/mysql/mysql.pid

26

27#

28# include all files from the config directory

29#

30!includedir /etc/my.cnf.d

31

32





1
2
3
1service mysqld restart

2

3





1
2
3
4
5
6
7
8
1mysql> show variables like 'log_bin';

2+---------------+-------+

3| Variable_name | Value |

4+---------------+-------+

5| log_bin       | ON    |

6+---------------+-------+

7

8





1
2
3
1grant replication slave on *.* to 'backup'@'%' identified by '1234'

2

3





1
2
3
4
5
6
7
8
9
10
11
12
1mysql> use mysql

2mysql> select user,authentication_string,host from user;

3+---------------+-------------------------------------------+-----------+

4| user          | authentication_string                     | host      |

5+---------------+-------------------------------------------+-----------+

6| root          | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B | %         |

7| mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

8| mysql.sys     | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | localhost |

9| backup        | *A4B6157319038724E3560894F7F932C8886EBFCF | %         |

10+---------------+-------------------------------------------+-----------+

11

12





1
2
3
4
5
6
7
8
1CREATE TABLE `article` (

2  `id` int(11) NOT NULL AUTO_INCREMENT,

3  `title` varchar(64) DEFAULT NULL,

4  `content` text,

5  PRIMARY KEY (`id`)

6) CHARSET=utf8;

7

8





1
2
3
4
5
6
7
8
1[root@zhenganwen ~]# service mysqld restart

2Shutting down MySQL.... SUCCESS! 

3Starting MySQL. SUCCESS! 

4[root@zhenganwen mysql]# mysql -uroot -proot

5mysql> flush tables with read lock;

6Query OK, 0 rows affected (0.00 sec)

7

8





1
2
3
4
5
6
7
8
9
10
1mysql> show master status \G

2*************************** 1. row ***************************

3             File: mysql-bin.000002

4         Position: 154

5     Binlog_Do_DB: test

6 Binlog_Ignore_DB: mysql,information_schema,performation_schema,sys

7Executed_Gtid_Set: 

81 row in set (0.00 sec)

9

10





1
2
3
1mysqldump -uroot -proot -hlocalhost test > /export/data/test.sql

2

3

配置slave



1
2
3
4
1log-bin=mysql

2server-id=1 #192.168.10.1

3

4





1
2
3
1show VARIABLES like 'log_bin';

2

3





1
2
3
4
5
6
7
8
9
1stop slave; 

2change master to

3    master_host='192.168.10.10', -- master的IP

4    master_user='backup',            -- 之前在master上创建的用户

5    master_password='1234',

6    master_log_file='mysql-bin.000002', -- master上 show master status \G 提供的信息

7    master_log_pos=154;

8

9





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
1mysql> start slave;

2mysql> show slave status \G

3*************************** 1. row ***************************

4               Slave_IO_State: Waiting for master to send event

5                  Master_Host: 192.168.10.10

6                  Master_User: backup

7                  Master_Port: 3306

8                Connect_Retry: 60

9              Master_Log_File: mysql-bin.000002

10          Read_Master_Log_Pos: 154

11               Relay_Log_File: DESKTOP-KUBSPE0-relay-bin.000002

12                Relay_Log_Pos: 320

13        Relay_Master_Log_File: mysql-bin.000002

14             Slave_IO_Running: Yes

15            Slave_SQL_Running: Yes

16              Replicate_Do_DB:

17          Replicate_Ignore_DB:

18           Replicate_Do_Table:

19       Replicate_Ignore_Table:

20      Replicate_Wild_Do_Table:

21  Replicate_Wild_Ignore_Table:

22                   Last_Errno: 0

23                   Last_Error:

24                 Skip_Counter: 0

25          Exec_Master_Log_Pos: 154

26              Relay_Log_Space: 537

27              Until_Condition: None

28               Until_Log_File:

29                Until_Log_Pos: 0

30           Master_SSL_Allowed: No

31           Master_SSL_CA_File:

32           Master_SSL_CA_Path:

33              Master_SSL_Cert:

34            Master_SSL_Cipher:

35               Master_SSL_Key:

36        Seconds_Behind_Master: 0

37Master_SSL_Verify_Server_Cert: No

38                Last_IO_Errno: 0

39                Last_IO_Error:

40               Last_SQL_Errno: 0

41               Last_SQL_Error:

42  Replicate_Ignore_Server_Ids:

43             Master_Server_Id: 10

44                  Master_UUID: f68774b7-0b28-11e9-a925-000c290abe05

45             Master_Info_File: C:\ProgramData\MySQL\MySQL Server 5.7\Data\master.info

46                    SQL_Delay: 0

47          SQL_Remaining_Delay: NULL

48      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

49           Master_Retry_Count: 86400

50                  Master_Bind:

51      Last_IO_Error_Timestamp:

52     Last_SQL_Error_Timestamp:

53               Master_SSL_Crl:

54           Master_SSL_Crlpath:

55           Retrieved_Gtid_Set:

56            Executed_Gtid_Set:

57                Auto_Position: 0

58         Replicate_Rewrite_DB:

59                 Channel_Name:

60           Master_TLS_Version:

611 row in set (0.00 sec)

62

63

测试



1
2
3
4
1mysql> unlock tables;

2Query OK, 0 rows affected (0.00 sec)

3

4





1
2
3
4
5
1mysql> use test

2mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');

3Query OK, 1 row affected (0.00 sec)

4

5





1
2
3
4
1mysql> insert into article (title,content) values ('mysql master and slave','record the cluster building succeed!:)');

2Query OK, 1 row affected (0.00 sec)

3

4

读写分离

方案一、定义两种连接

方案二、使用Spring AOP

项目结构

引入依赖



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
1<dependencies>

2    <dependency>

3        <groupId>org.mybatis</groupId>

4        <artifactId>mybatis-spring</artifactId>

5        <version>1.3.2</version>

6    </dependency>

7    <dependency>

8        <groupId>org.mybatis</groupId>

9        <artifactId>mybatis</artifactId>

10        <version>3.4.6</version>

11    </dependency>

12    <dependency>

13        <groupId>org.springframework</groupId>

14        <artifactId>spring-core</artifactId>

15        <version>5.0.8.RELEASE</version>

16    </dependency>

17    <dependency>

18        <groupId>org.springframework</groupId>

19        <artifactId>spring-aop</artifactId>

20        <version>5.0.8.RELEASE</version>

21    </dependency>

22    <dependency>

23        <groupId>org.springframework</groupId>

24        <artifactId>spring-jdbc</artifactId>

25        <version>5.0.8.RELEASE</version>

26    </dependency>

27    <dependency>

28        <groupId>com.alibaba</groupId>

29        <artifactId>druid</artifactId>

30        <version>1.1.6</version>

31    </dependency>

32    <dependency>

33        <groupId>mysql</groupId>

34        <artifactId>mysql-connector-java</artifactId>

35        <version>6.0.2</version>

36    </dependency>

37    <dependency>

38        <groupId>org.springframework</groupId>

39        <artifactId>spring-context</artifactId>

40        <version>5.0.8.RELEASE</version>

41    </dependency>

42

43    <dependency>

44        <groupId>org.springframework</groupId>

45        <artifactId>spring-aspects</artifactId>

46        <version>5.0.8.RELEASE</version>

47    </dependency>

48

49    <dependency>

50        <groupId>org.projectlombok</groupId>

51        <artifactId>lombok</artifactId>

52        <version>1.16.22</version>

53    </dependency>

54    <dependency>

55        <groupId>org.springframework</groupId>

56        <artifactId>spring-test</artifactId>

57        <version>5.0.8.RELEASE</version>

58    </dependency>

59    <dependency>

60        <groupId>junit</groupId>

61        <artifactId>junit</artifactId>

62        <version>4.12</version>

63    </dependency>

64

65</dependencies>

66

67

数据类



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1package top.zhenganwen.mysqloptimize.entity;

2

3import lombok.AllArgsConstructor;

4import lombok.Data;

5import lombok.NoArgsConstructor;

6

7@Data

8@AllArgsConstructor

9@NoArgsConstructor

10public class Article {

11

12    private int id;

13    private String title;

14    private String content;

15}

16

17

spring配置文件



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
1<?xml version="1.0" encoding="UTF-8"?>

2<beans xmlns="http://www.springframework.org/schema/beans"

3       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

4       xmlns:context="http://www.springframework.org/schema/context"

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

6

7    <context:property-placeholder location="db.properties"></context:property-placeholder>

8

9    <context:component-scan base-package="top.zhenganwen.mysqloptimize"/>

10

11    <bean id="slaveDataSource" class="com.alibaba.druid.pool.DruidDataSource">

12        <property name="driverClassName" value="${db.driverClass}"/>

13        <property name="url" value="${master.db.url}"></property>

14        <property name="username" value="${master.db.username}"></property>

15        <property name="password" value="${master.db.password}"></property>

16    </bean>

17

18    <bean id="masterDataSource" class="com.alibaba.druid.pool.DruidDataSource">

19        <property name="driverClassName" value="${db.driverClass}"/>

20        <property name="url" value="${slave.db.url}"></property>

21        <property name="username" value="${slave.db.username}"></property>

22        <property name="password" value="${slave.db.password}"></property>

23    </bean>

24

25    <bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">

26        <property name="defaultTargetDataSource" ref="masterDataSource"></property>

27        <property name="targetDataSources">

28            <map key-type="java.lang.String" value-type="javax.sql.DataSource">

29                <entry key="read" value-ref="slaveDataSource"/>

30                <entry key="write" value-ref="masterDataSource"/>

31            </map>

32        </property>

33        <property name="methodType">

34            <map key-type="java.lang.String" value-type="java.lang.String">

35                <entry key="read" value="query,find,select,get,load,"></entry>

36                <entry key="write" value="update,add,create,delete,remove,modify"/>

37            </map>

38        </property>

39    </bean>

40

41    <!-- Mybatis文件 -->

42    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">

43        <property name="configLocation" value="classpath:mybatis-config.xml" />

44        <property name="dataSource" ref="dataSourceRouting" />

45        <property name="mapperLocations" value="mapper/*.xml"/>

46    </bean>

47

48    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">

49        <property name="basePackage" value="top.zhenganwen.mysqloptimize.mapper" />

50        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />

51    </bean>

52</beans>

53

54





1
2
3
4
5
6
7
8
9
10
11
1master.db.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

2master.db.username=root

3master.db.password=root

4

5slave.db.url=jdbc:mysql://192.168.10.10:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC

6slave.db.username=root

7slave.db.password=root

8

9db.driverClass=com.mysql.jdbc.Driver

10

11





1
2
3
4
5
6
7
8
9
10
11
1<?xml version="1.0" encoding="UTF-8"?>

2<!DOCTYPE configuration

3        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

4        "http://mybatis.org/dtd/mybatis-3-config.dtd">

5<configuration>

6    <typeAliases>

7        <typeAlias type="top.zhenganwen.mysqloptimize.entity.Article" alias="Article"/>

8    </typeAliases>

9</configuration>

10

11

mapper接口和配置文件



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1package top.zhenganwen.mysqloptimize.mapper;

2

3import org.springframework.stereotype.Repository;

4import top.zhenganwen.mysqloptimize.entity.Article;

5

6import java.util.List;

7

8@Repository

9public interface ArticleMapper {

10

11    List<Article> findAll();

12

13    void add(Article article);

14

15    void delete(int id);

16

17}

18

19





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1<?xml version="1.0" encoding="UTF-8" ?>

2<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >

3<mapper namespace="top.zhenganwen.mysqloptimize.mapper.ArticleMapper">

4    <select id="findAll" resultType="Article">

5        select * from article

6    </select>

7

8    <insert id="add" parameterType="Article">

9        insert into article (title,content) values (#{title},#{content})

10    </insert>

11

12    <delete id="delete" parameterType="int">

13        delete from article where id=#{id}

14    </delete>

15</mapper>

16

17

核心类

RoutingDataSourceImpl



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
1package top.zhenganwen.mysqloptimize.dataSource;

2

3import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

4

5import java.util.*;

6

7/**

8 * RoutingDataSourceImpl class

9 * 数据源路由

10 *

11 * @author zhenganwen, blog:zhenganwen.top

12 * @date 2018/12/29

13 */

14public class RoutingDataSourceImpl extends AbstractRoutingDataSource {

15

16    /**

17     * key为read或write

18     * value为DAO方法的前缀

19     * 什么前缀开头的方法使用读数据员，什么开头的方法使用写数据源

20     */

21    public static final Map<String, List<String>> METHOD_TYPE_MAP = new HashMap<String, List<String>>();

22

23    /**

24     * 由我们指定数据源的id，由Spring切换数据源

25     *

26     * @return

27     */

28    @Override

29    protected Object determineCurrentLookupKey() {

30        System.out.println("数据源为："+DataSourceHandler.getDataSource());

31        return DataSourceHandler.getDataSource();

32    }

33

34    public void setMethodType(Map<String, String> map) {

35        for (String type : map.keySet()) {

36            String methodPrefixList = map.get(type);

37            if (methodPrefixList != null) {

38                METHOD_TYPE_MAP.put(type, Arrays.asList(methodPrefixList.split(",")));

39            }

40        }

41    }

42}

43

44





1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1<bean id="dataSourceRouting" class="top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl">

2    <property name="defaultTargetDataSource" ref="masterDataSource"></property>

3    <property name="targetDataSources">

4        <map key-type="java.lang.String" value-type="javax.sql.DataSource">

5            <entry key="read" value-ref="slaveDataSource"/>

6            <entry key="write" value-ref="masterDataSource"/>

7        </map>

8    </property>

9    <property name="methodType">

10        <map key-type="java.lang.String" value-type="java.lang.String">

11            <entry key="read" value="query,find,select,get,load,"></entry>

12            <entry key="write" value="update,add,create,delete,remove,modify"/>

13        </map>

14    </property>

15</bean>

16

17

DataSourceHandler



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
1package top.zhenganwen.mysqloptimize.dataSource;

2

3/**

4 * DataSourceHandler class

5 * <p>

6 * 将数据源与线程绑定，需要时根据线程获取

7 *

8 * @author zhenganwen, blog:zhenganwen.top

9 * @date 2018/12/29

10 */

11public class DataSourceHandler {

12

13    /**

14     * 绑定的是read或write，表示使用读或写数据源

15     */

16    private static final ThreadLocal<String> holder = new ThreadLocal<String>();

17

18    public static void setDataSource(String dataSource) {

19        System.out.println(Thread.currentThread().getName()+"设置了数据源类型");

20        holder.set(dataSource);

21    }

22

23    public static String getDataSource() {

24        System.out.println(Thread.currentThread().getName()+"获取了数据源类型");

25        return holder.get();

26    }

27}

28

29

DataSourceAspect



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
1package top.zhenganwen.mysqloptimize.dataSource;

2

3import org.aspectj.lang.JoinPoint;

4import org.aspectj.lang.annotation.Aspect;

5import org.aspectj.lang.annotation.Before;

6import org.aspectj.lang.annotation.Pointcut;

7import org.springframework.context.annotation.EnableAspectJAutoProxy;

8import org.springframework.stereotype.Component;

9

10import java.util.List;

11import java.util.Set;

12

13import static top.zhenganwen.mysqloptimize.dataSource.RoutingDataSourceImpl.METHOD_TYPE_MAP;

14

15/**

16 * DataSourceAspect class

17 *

18 * 配置切面，根据方法前缀设置读、写数据源

19 * 项目启动时会加载该bean，并按照配置的切面（哪些切入点、如何增强）确定动态代理逻辑

20 * @author zhenganwen,blog:zhenganwen.top

21 * @date 2018/12/29

22 */

23@Component

24//声明这是一个切面，这样Spring才会做相应的配置，否则只会当做简单的bean注入

25@Aspect

26@EnableAspectJAutoProxy

27public class DataSourceAspect {

28

29    /**

30     * 配置切入点：DAO包下的所有类的所有方法

31     */

32    @Pointcut("execution(* top.zhenganwen.mysqloptimize.mapper.*.*(..))")

33    public void aspect() {

34

35    }

36

37    /**

38     * 配置前置增强，对象是aspect()方法上配置的切入点

39     */

40    @Before("aspect()")

41    public void before(JoinPoint point) {

42        String className = point.getTarget().getClass().getName();

43        String invokedMethod = point.getSignature().getName();

44        System.out.println("对 "+className+"$"+invokedMethod+" 做了前置增强，确定了要使用的数据源类型");

45

46        Set<String> dataSourceType = METHOD_TYPE_MAP.keySet();

47        for (String type : dataSourceType) {

48            List<String> prefixList = METHOD_TYPE_MAP.get(type);

49            for (String prefix : prefixList) {

50                if (invokedMethod.startsWith(prefix)) {

51                    DataSourceHandler.setDataSource(type);

52                    System.out.println("数据源为："+type);

53                    return;

54                }

55            }

56        }

57    }

58}

59

60

测试读写分离



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
1package top.zhenganwen.mysqloptimize.dataSource;

2

3import org.junit.Test;

4import org.junit.runner.RunWith;

5import org.springframework.beans.factory.annotation.Autowired;

6import org.springframework.test.context.ContextConfiguration;

7import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

8import top.zhenganwen.mysqloptimize.entity.Article;

9import top.zhenganwen.mysqloptimize.mapper.ArticleMapper;

10

11@RunWith(SpringJUnit4ClassRunner.class)

12@ContextConfiguration(locations = "classpath:spring-mybatis.xml")

13public class RoutingDataSourceTest {

14

15    @Autowired

16    ArticleMapper articleMapper;

17

18    @Test

19    public void testRead() {

20        System.out.println(articleMapper.findAll());

21    }

22

23    @Test

24    public void testAdd() {

25        Article article = new Article(0, "我是新插入的文章", "测试是否能够写到master并且复制到slave中");

26        articleMapper.add(article);

27    }

28

29    @Test

30    public void testDelete() {

31        articleMapper.delete(2);

32    }

33}

34

35

负载均衡

负载均衡算法

  • 轮询
  • 加权轮询：按照处理能力来加权
  • 负载分配：依据当前的空闲状态（但是测试每个节点的内存使用率、CPU利用率等，再做比较选出最闲的那个，效率太低）

高可用

典型SQL

线上DDL

数据库导入语句

  1. 导入时

先禁用索引和约束：



1
2
3
1alter table table-name disable keys

2

3





1
2
3
1alter table table-name enable keys

2

3
  1. 数据库如果使用的引擎是Innodb，那么它

默认会给每条写指令加上事务（这也会消耗一定的时间），因此建议先手动开启事务，再执行一定量的批量导入，最后手动提交事务。

  1. 如果批量导入的SQL指令格式相同只是数据不同，那么你应该先prepare

预编译一下，这样也能节省很多重复编译的时间。

limit offset,rows

select * 要少用

order by rand()不要用

单表和多表查询

count(*)

1
student
100

limit 1

慢查询日志

开启慢查询日志

设置临界时间

查看日志

profile信息

开启profile



1
2
3
4
5
6
7
8
9
10
11
12
1mysql> show variables like 'profiling';

2+---------------+-------+

3| Variable_name | Value |

4+---------------+-------+

5| profiling     | OFF   |

6+---------------+-------+

71 row in set, 1 warning (0.00 sec)

8

9mysql> set profiling=on;

10Query OK, 0 rows affected, 1 warning (0.00 sec)

11

12

查看profile信息



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
1mysql> show variables like 'profiling';

2+---------------+-------+

3| Variable_name | Value |

4+---------------+-------+

5| profiling     | ON    |

6+---------------+-------+

71 row in set, 1 warning (0.00 sec)

8

9mysql> insert into article values (null,'test profile',':)');

10Query OK, 1 row affected (0.15 sec)

11

12mysql> show profiles;

13+----------+------------+-------------------------------------------------------+

14| Query_ID | Duration   | Query                                                 |

15+----------+------------+-------------------------------------------------------+

16|        1 | 0.00086150 | show variables like 'profiling'                       |

17|        2 | 0.15027550 | insert into article values (null,'test profile',':)') |

18+----------+------------+-------------------------------------------------------+

19

20

通过Query_ID查看某条SQL所有详细步骤的时间

典型的服务器配置



1
2
3
4
5
6
7
8
1mysql> show variables like 'max_connections';

2+-----------------+-------+

3| Variable_name   | Value |

4+-----------------+-------+

5| max_connections | 151   |

6+-----------------+-------+

7

8





1
2
3
4
5
6
7
8
1mysql> show variables like 'table_open_cache';

2+------------------+-------+

3| Variable_name    | Value |

4+------------------+-------+

5| table_open_cache | 2000  |

6+------------------+-------+

7

8





1
2
3
4
5
6
7
8
1mysql> show variables like 'key_buffer_size';

2+-----------------+---------+

3| Variable_name   | Value   |

4+-----------------+---------+

5| key_buffer_size | 8388608 |

6+-----------------+---------+

7

8





1
2
3
4
5
6
7
8
1mysql> show variables like 'innodb_buffer_pool_size';

2+-------------------------+---------+

3| Variable_name           | Value   |

4+-------------------------+---------+

5| innodb_buffer_pool_size | 8388608 |

6+-------------------------+---------+

7

8

压测工具mysqlslap

自动生成sql测试



1
2
3
4
5
6
7
8
9
10
1C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot

2mysqlslap: [Warning] Using a password on the command line interface can be insecure.

3Benchmark

4        Average number of seconds to run all queries: 1.219 seconds

5        Minimum number of seconds to run all queries: 1.219 seconds

6        Maximum number of seconds to run all queries: 1.219 seconds

7        Number of clients running queries: 1

8        Average number of queries per client: 0

9

10

并发测试



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
1C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot

2mysqlslap: [Warning] Using a password on the command line interface can be insecure.

3Benchmark

4        Average number of seconds to run all queries: 3.578 seconds

5        Minimum number of seconds to run all queries: 3.578 seconds

6        Maximum number of seconds to run all queries: 3.578 seconds

7        Number of clients running queries: 100

8        Average number of queries per client: 0

9        

10C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot

11mysqlslap: [Warning] Using a password on the command line interface can be insecure.

12Benchmark

13        Average number of seconds to run all queries: 5.718 seconds

14        Minimum number of seconds to run all queries: 5.718 seconds

15        Maximum number of seconds to run all queries: 5.718 seconds

16        Number of clients running queries: 150

17        Average number of queries per client: 0

18

19

多轮测试



1
2
3
4
5
6
7
8
9
10
1C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=10 -uroot -proot

2mysqlslap: [Warning] Using a password on the command line interface can be insecure.

3Benchmark

4        Average number of seconds to run all queries: 5.398 seconds

5        Minimum number of seconds to run all queries: 4.313 seconds

6        Maximum number of seconds to run all queries: 6.265 seconds

7        Number of clients running queries: 150

8        Average number of queries per client: 0

9

10

存储引擎测试



1
2
3
4
5
6
7
8
9
10
11
1C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot

2mysqlslap: [Warning] Using a password on the command line interface can be insecure.

3Benchmark

4        Running for engine innodb

5        Average number of seconds to run all queries: 5.911 seconds

6        Minimum number of seconds to run all queries: 5.485 seconds

7        Maximum number of seconds to run all queries: 6.703 seconds

8        Number of clients running queries: 150

9        Average number of queries per client: 0

10

11





1
2
3
4
5
6
7
8
9
10
11
1C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot

2mysqlslap: [Warning] Using a password on the command line interface can be insecure.

3Benchmark

4        Running for engine myisam

5        Average number of seconds to run all queries: 53.104 seconds

6        Minimum number of seconds to run all queries: 46.843 seconds

7        Maximum number of seconds to run all queries: 60.781 seconds

8        Number of clients running queries: 150

9        Average number of queries per client: 0

10

11

Related posts:

  1. Kubernetes入门
  2. Nginx反向代理 实现Web负载均衡
  3. Nginx+Tomcat+Memcached实现负载均衡和Session共享
  4. 负载均衡器技术Nginx和F5的优缺点对比

给TA打赏
共{{data.count}}人
人已打赏
[db:标签]linuxmysql命令安装工具指令数据服务器系统解决方案负载均衡
安全运维

OpenSSH-8.7p1离线升级修复安全漏洞

2021-10-23 10:13:25

安全运维

设计模式的设计原则

2021-12-12 17:36:11

猜你喜欢

Kubernetes入门

029
❯

解锁会员权限

开通会员

解锁海量优质VIP资源

立刻开通
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索

  • 扫码打开当前页

返回顶部