**MySQL**
的性能优化,大部分情况下都是想减少查询所消耗的时间;而一个查询是由很多个环节组成,那么就需要从每个环节消耗时间进行入手。**Mysql: error 1040: Too many connections**
** **的错误。一般情况下可以从两个方面去解决连接数不够的问题: MySQL 8.0.11
**默认连接数为 **200**
),当有多个应用或很多请求同时访问数据库且连接数不够时,可以修改配置参数增加可用的连接数或及时释放不活动的连接,交互式和非交互式的客户端的默认超时时间都是 **28800m (8h)**
,可以把该值调小。-- 查看最大连接数,默认为 200
show variables like 'max_connections';
-- 修改最大连接数
set GLOBAL max_connections = 200;
-- 查看默认的超时时间,默认为 28800 秒 / 8 小时
show global variables like 'wait_timeout';
-- 修改超时时间
set GLOBAL wait_timeout = 28800;
**SQL**
语句都创建一个新的连接时可以引入连接池来实现连接的重用;现在市面上有 **ORM**
层面(**MyBatis**
自带的连接池)、专用的连接池工具(阿里的 **Druid**
、**Spring Boot 2.x**
默认自带的 **Hikari**
、以及 **DBCP**
和 **C3P0**
)。当客户端修改为从连接池获取连接后,在连接池中会维护一定数量的连接,其他客户端排队等待获取连接对数据库进行操作。**Druid**
的连接池默认大小是 **8**
,**Hikari**
的连接池默认大小是 **10**
。**PostgreSQL**
给出的数据库建议设置的连接池大小公式为 (机器核数 * 2) + 1。对于减少连接数会提高吞吐量和设置连接池的大小和 **CPU**
核数相关的原因是因为每一个连接,服务端都需要创建一个线程去处理它,连接数越多,那么服务端创建的线程数量就会越多。**Redis**
);运行一个独立的服务器是属于架构层面的优化。**master**
,复制的节点被称为 **slave**
。**slave**
自身也可以作为其他节点的数据来源—级联复制。**MySQL**
中的主从复制在做更新操作时会记录 **binlog**
,它是一种逻辑日志。有了该日志文件后会从服务器获取主服务器的 **binlog**
文件,然后解析里面的 **SQL**
语句,在服务器上执行一遍,从而保持主从数据一致。这其中的 **I/O**
线程主要是连接到 **master**
去获取 **binlog**
并解析。在 **Master**
节点上有一个 **log dump**
线程,主要是用于发送 **binlog**
给 **slave**
的。另外 **Slave**
的 **SQL**
线程是用来读取 **relay log**
并把数据写入到数据库的。**master**
节点,而读的请求可以分摊到 **slave**
节点上—读写分离,读写分离可以在一定程度上减低数据库服务器的访问压力,但会出现主从数据的一致性问题。然后把数据写入了 **master**
后,马上到 **slave**
中查询的话数据可能还未从 **Master**
服务器同步到 **Slave**
服务器上。**MySQL**
中 **slave**
的 **SQL**
线程是单线程的,**Master**
可以支持 **SQL**
语句的并行执行,配置了多少的最大连接数就是最多同时多少个 **SQL**
并行地执行。而 **Slave**
的 **SQL**
只能单线程地排队执行;当在主库并发量很多的情况下进行数据同步,就会出现一定的延迟。从库不能并行执行的原因是因为当用户增加一条数据并对其做修改操作,然后删除时,在从库上的顺序不能出现变化。**MySQL**
默认是异步复制;对于 **Master**
写入到 **binlog**
且事务结束,就返回给客户端了。对于 **Slave**
接收到 **binlog**
就结束了;**Master**
并不关心 **Slave**
的数据是否写入成功。如果要减少延迟就可以等待从库中的全部事务执行完毕才返回客户端,这样的方式称之为全同步复制;只有在从库写完数据时,主库才会返回给客户端。这种方式可以保证读之前数据已经同步成功,但它的问题是事务执行的时间会变长后,导致 **Master**
节点的性能下降。**binlog**
并写到 **relay log**
中才返回给客户端。**Master**
不会等待很长的时间,但返回给客户端时,数据即将写入成功,因为它只是剩下最后读取 **relay log**
并写入从库。**mysql/plugin**
目录下的一个插件并启动。对于异步复制,半步同步复制提高了数据的安全性,但也造成了一定程度的延迟;它需要等待一个 **Slave**
写入 **relay log**
在这里多了一个网络交互的过程,所以半同步复制最好在低延时的网络中使用。当要减少主从同步的延迟,减少 **SQL**
执行造成的等待时间,就可以使用 **MySQL 5.6**
版本中支持的多库并行复制,该方式是在 **Slave**
上让多个 **SQL**
语句可以并行执行,而不是排队执行。-- Master 执行
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 启动
SET GLOBAL rpl_semi_sync_master_enabled = 1;
-- 查看是否启动成功
SHOW variables LIKE '%semi_sync%';
-- Slave 执行
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
-- 启动
SET global rpl_semi_sync_master_enabled = 1;
-- 查看是否启动成功
SHOW global variables LIKE '%semi_sync%';
**SQL**
语句且各自操作自己的数据库,这样就不会产生并发的问题,并且对执行的顺序也没有要求。当使用该方式时,数据库的从库中的 **SQL**
线程就可以并发执行。但在大部分情况下都是单库多表的情况,在一个数据库中可以实现并行复制主要是因为数据库本身就是支持多个事务同时操作的情况。它们之间是互不干扰的,即便操作不同的表、行的情况下,也不会存在资源的竞争和数据的干扰。那在主库上能并行执行的事务,是否在从库上也能并行执行操作呢?所以就引入了异步复制之 **GTID**
复制。**GTID**
复制指把那些在主库上并行执行的事务分为一个组并给他们设定编号,这组事务在从库上也可以并行地执行,这个号就称为 **GTID(Global Transaction Identifiers)**
。如果要使用 **GTID**
复制就可以通过修改配置参数打开它,默认是关闭的。所以说无论对 **master | slave**
的连接方式进行优化,还是让从库可以并行执行 **SQL**
,都是从数据库的层面去解决主从复制延迟的问题。-- 查看 GTID 是否开启
show global variables like 'gtid_mode';
**Master**
节点或单张表存储的数据过大时(亿级别数据),单表的查询性能还是会下降,这时就需要进一步对单台数据库节点的数据分型拆分,这就是分库分表。在分库分表中存在两种: 高可用
**HA**
方案需要解决的问题是当一个**Master**
节点宕机时提升一个数据最新的**Slave**
成为**Master**
节点;如果同时运行多个**Master**
节点又必须要解决**Master**
之间数据复制和对客户端连接路由的问题就有不同的解决方案(难度不同、运维管理成本不同)。
**HAProxy + keepalived**
的方案是基于主从复制的;**NDB Cluster**
:基于 **NDB Cluster**
存储引擎的 **MySQL Cluster**
**Calera**
:一种多主同步复制集群方案;**MHA/MMM**
:**MMM [Master-Master replication manager for MySQL]**
是一种多主的高可用架构,它和 **MHA [MySQL Master High Available]**
都是对外提供一个虚拟 **IP**
并监控 **Master & Slave**
。当主节点发送故障时,就需要把一个从节点升级为主节点,并把从节点里面比主节点缺少的数据补上后就把虚拟 **IP**
指向一个新的主节点。**MGR**
:在 **MySQL 5.7.17**
版本中推出了 **InnoDB Cluster -- MySQL Group Replication -- MSG**
,该套件包括了 **mysql shell & mysql-route**
。**SQL**
语句,那么就需要记录执行比较慢的 **SQL**
语句;而优化器就是对执行的 **SQL**
语句进行分析,生成执行计划。当需要查看执行比较慢的 **SQL**
语句时就需要打开慢日志开关,它默认是关闭的,因为开启慢查询日志和 **bin log & optimizer-trace**
都是有代价的。-- 查看慢日志信息,默认是 OFF
SHOW VARIABLES LIKE '%slow_query%'
-- 控制执行多长时间的 SQL 记录到慢日志中,默认 10s
SHOW VARIABLES LIKE '%long_query%';
-- 动态开启慢查询日志 1 开启,0 关闭(重启后失效)
SET @@global.slow_query_log = 1;
-- mysql 默认记录到慢日志时间是 10s,修改时间
SET @@global.long_query_time = 3;
**MySQL**
提供的工具 **mysqldumpslow**
进行分析;-- 模拟慢查询
select sleep(10);
SELECT * FROM `user` WHERE username = 'John_687134';
-- 使用 mysqldumpslow 工具进行分析查询时最慢的 5 条 SQL 语句
mysqldumpslow -s t -t 5 -g 'select' /var/lib/mysql/localhost-slow.log
Count
:代表这条 **SQL**
语句执行了多少次;**Time**
:代表执行的时间(括号中代表累计时间);**Lock**
代表锁定时间(括号中代表累计时间);**Rows**
代表返回的记录数(括号中代表累计记录数)。**SHOW PROFILE**
可以查看 **SQL**
语句执行时使用的资源(**CPU、I/O**
等情况);-- 查看是否开启,在 MySQL 8.0.19 中默认是开启的
select @@profiling;
-- 如果没有开启就通过下面语句进行开启
set @@profiling=1;
-- 查看 profile 统计
show profiles;
-- 查看最后一个 SQL 的执行详细信息,找出耗时比较多的环节
show profile;
-- 根据 id 查看执行详细信息
show profile for query 1;
**show profile**
外要对当前数据库中执行的慢的 SQL
进行分析,可以通过查看运行线程状态和服务器运行信息、存储引擎信息来分析。使用 **show processlist**
命令显示运行线程,可以根据 **id**
号 **kill**
线程,也可以查表。select * from information_schema.processlist;
列 | 含义 |
---|---|
ID | 线程的唯一标志,可以根据它 **kill** 线程 |
**USER** | 启动这个线程的用户,普通用户只能看到自己的线程 |
HOST | 哪个 **IP** 端口发起的连接 |
**DB** | 操作的数据库 |
**COMMAND** | 线程的命令 |
**TIME** | 操作持续时间 |
**STATE** | 线程状态 |
**INFO** | **SQL** 语句的前 **100** 个字符 |
**SHOW STATUS**
命令用于查看 **MySQL**
服务器运行状态-- 查看 select 次数
SHOW GLOBAL STATUS LIKE 'com_select';
**[SHOW ENGIN](https://dev.mysql.com/doc/refman/5.7/en/show-engine.html)**
命令用于查看 **MySQL**
存储引擎的当前运行信息(事务持有的表锁、行锁、事务的锁等待状况、线程信号量等待、文件 **I/O**
请求以及 **buffer pool**
统计信息)show engine innodb status;
-- 查看存储引擎输出是否开启,默认关闭
show variables like 'innodb_status_output%';
-- 开启输出
SET GLOBAL innodb_status_output=ON;
SET GLOBAL innodb_status_output_locks=ON;
MySQL
中提供了一个执行计划工具,通过该工具可以模拟优化执行 **SQL**
查询语句的过程得到 **MySQL**
是怎么对一条 **SQL**
语句的过程并且可以对其进行分析。在 **MySQL 5.6.3**
之前只能分析 **SELECT**
语句,之后就全部都能分析。-- 创建课程表
CREATE TABLE `course` (
`cid` int(3) DEFAULT NULL,
`cname` varchar(20) DEFAULT NULL,
`tid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入课程信息
INSERT INTO `course` VALUES ('1', 'java', '1');
INSERT INTO `course` VALUES ('2', 'c++', '1');
INSERT INTO `course` VALUES ('3', 'python', '2');
INSERT INTO `course` VALUES ('4', 'go', '3');
-- 创建老师表
CREATE TABLE `teacher` (
`tid` int(3) DEFAULT NULL,
`tname` varchar(20) DEFAULT NULL,
`tcid` int(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入老师基本信息
INSERT INTO `teacher` VALUES ('1', 'Sky', '1');
INSERT INTO `teacher` VALUES ('2', 'Sara', '2');
INSERT INTO `teacher` VALUES ('3', 'John', '3');
-- 创建老师联系信息
CREATE TABLE `teacher_contact` (
`tcid` int(3) DEFAULT NULL,
`phone` varchar(200) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入老师联系信息
INSERT INTO `teacher_contact` VALUES ('1', '13688888888');
INSERT INTO `teacher_contact` VALUES ('2', '18166669999');
INSERT INTO `teacher_contact` VALUES ('3', '17722225555');
id
值不相同的情况**id**
值最大的,以从下往上进行查询 course → teacher → teacher_contact
,子查询就是只要拿到内层的结果后才能进行外层查询。-- 查询 java 课程的老师手机号
EXPLAIN SELECT
tc.phone
FROM
teacher_contact tc
WHERE
tcid = (
SELECT
tcid
FROM
teacher t
WHERE
t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'java' ));
id
值相同的情况teacher → course → teacher_contact
-- 查询课程 ID 为 2,或者联系表 ID 为 3 的老师
EXPLAIN SELECT
t.tname,
c.cname,
tc.phone
FROM
teacher t,
course c,
teacher_contact tc
WHERE
t.tid = c.tid
AND t.tcid = tc.tcid
AND ( c.cid = 2 OR tc.tcid = 3 );
teacher_contact → teacher → course
INSERT INTO `teacher` VALUES (4, 'Ken', 4);
INSERT INTO `teacher` VALUES (5, 'Tim', 5);
INSERT INTO `teacher` VALUES (6, 'Mellisa', 6);
**a、b、c**
三张表,分别有 **2、3、4**
条数据;当三张表做联合查询时且查询顺序为 **a → b → c**
,那么其笛卡尔积是 **2 * 3 * 4 = 6 * 4 = 24**
。如果查询顺序是 **c → b → a**
,它的笛卡尔积为 **4 * 3 * 2 = 12 * 2 = 24**
。因为 **MySQL**
需要把查询的结果(中间结果和最终结果都保存到内存中),所以 **MySQL**
会优先选择中间结果数据量比较小的顺序进行查询,所以最终联表查询的顺序是 **a → b → c**
,所以这是为什么 **teacher**
表插入数据之后查询顺序会发生变化。**id**
不同时先大后小,否则从上往下。EXPLAIN SELECT * FROM teacher;
**SQL**
语句中最外面的一层查询就是主查询-- 查询 java 课程的老师手机号
EXPLAIN SELECT
tc.phone
FROM
teacher_contact tc
WHERE
tcid = (
SELECT
tcid
FROM
teacher t
WHERE
t.tid = ( SELECT c.tid FROM course c WHERE c.cname = 'java' ));
**table(UNION)**
,然后再执行左边的 **table**
,类型是 **DERIVED**
-- 查询 ID 为 1 或 2 的老师的课程
EXPLAIN SELECT
cr.cname
FROM
( SELECT * FROM course WHERE tid = 1 UNION SELECT * FROM course WHERE tid = 2 ) cr;
**<union2, 3>**
表示 **id = 2 & id = 3**
存在联合查询**system > const > eq_ref > ref > range > index > all**
,其他类型 **fulltext 、ref_or_null 、index_merger 、unique_subquery、index_subquery**
;在所有的连接类型中越往下越差,除了常见的类型中的 **all**
都能访问到索引。** system
**system**
是 **const**
中的一种特例,只有一行满足条件**** const
-- 创建表
CREATE TABLE test (
id INT ( 3 ) PRIMARY KEY,
content VARCHAR ( 20 )
);
-- 插入一条数据
INSERT INTO test VALUES ( 1, 'a' );
-- 根据 id 查询查看执行计划
EXPLAIN SELECT * FROM test WHERE id = 1;
** eq_ref
**join**
查询,表示对于前表的每一个结果都只能够匹配到后表的一行结果(唯一索引的查询)-- 为 teacher_contact 的 tcid 创建一个主键
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
-- 为 teacher 中的 tcid 创建一个普通主键
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
EXPLAIN SELECT
t.tcid
FROM
teacher t,
teacher_contact tc
WHERE
t.tcid = tc.tcid;
ref **
-- 查看 teacher 表中的普通索引 tcid 查询的执行计划
EXPLAIN SELECT * FROM teacher WHERE tcid = 3;
range **
**where**
后面是 **between and | < | > | >= | <= | in**
时,type
是 **range**
。不走索引-- 对 tid 加索引,否则为全表扫描 all
ALTER TABLE teacher ADD INDEX idx_tid (tid);
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
** **index **
EXPLAIN SELECT tid FROM teacher;
all
EXPLAIN SELECT * FROM teacher
NULL
EXPLAIN select 1 from dual where 1=1;
**NULL**
就表示没有使用到索引,**possible_keys**
可能有一个或多个,可能会用到索引;当 possible_key = NULL
时 **ken**
有可能有值。**byte**
),与索引的类型和长度相关。**MySQL**
中认为能扫描多少行才能返回数据的一个预估值。**Server**
层过滤后剩下多少满足查询的记录数量的一个百分比。**using index**
:用到了覆盖索引,不用回表EXPLAIN SELECT username FROM `user` WHERE username = 'John_21';
**using where**
:使用了 **where**
过滤,表示存储引擎返回的记录并不是所有满足查询条件,需要在 **Server**
层进行过滤EXPLAIN SELECT * FROM `user` WHERE password = 'b9aae9537ca945ae9382525efd73bed0';
**using index condition**
:索引下推**using filesort**
:不能使用索引来排序,用到了额外的排序,复合索引的前提下需要优化EXPLAIN select * from user where name ='John' order by id;
**using temporary**
:用到了临时表-- distinct 非索引列
EXPLAIN SELECT DISTINCT(tid) FROM teacher;
-- group by 非索引列
EXPLAIN SELECT tname FROM teacher GROUP BY tname;
-- 使用 join 时 group 任意列
EXPLAIN SELECT t.tid FROM teacher t JOIN course c ON t.tid = c.tid GROUP BY t.tid;
**SQL**
查询语句的过程得到 **MySQL**
是怎么处理一条 **SQL**
语句的并可以对其进行语句或表的性能瓶颈。**SQL**
语句有多个关联和子查询且比较复杂时,就需要分析 **SQL**
语句是否有其他方式编写对其优化。-- 大偏移量的 limit, 预估扫描行数 995354
EXPLAIN select * FROM user LIMIT 900000, 10;
-- 改成先过滤 ID,再 limit 10
EXPLAIN SELECT * FROM user WHERE id >= 900000 AND id < 900010 LIMIT 10;
**MyISAM**
:对查询和插入操作比较多业务表;**Memory**
:临时数据;**InnoDB**
:常规并发更新多的表**varchar**
更节省空间,它需要一个字节来记录长度;但是固定长度就使用 **char**
。**NOT NULL**
并提供默认值或使用特殊值、空串代替 **NULL**
,**NULL**
类型的存储、优化、使用会存在问题;**NAS | SSO | fastDFS**
上,在数据库中只是存储它的路径,在应用中配置其服务器地址。