MySql之数据库性能优化策略

1.所谓的数据库性能调优，主要是指的我们如何让我们的SQL执行更快，比如查询更快；
  那么我们在进行数据库性能调优的时候，需要从SQL的整个执行的流程上去调整和优化；
  分析每个环节的消耗时间，去进行优化；

1.一般不建议，除非长时间没有重启过服务，比如几年

1.这里的连接问题，主要是连接可用的线程数量；
2.这个连接的配置，两个方面，考虑：服务端和客户端

1.服务端的连接数，通俗的理解实际上就是：我这台mysql服务器最多同时支持多少个连接的请求并发；

show variables like 'max_connections'; # 默认151个连接

1.并不是连接数量越多越好，这个跟服务器的性能以及服务器CPU核心数量有关系，

1.及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是 28800秒，8小时（8*3600=28800秒），我们可以把这个值调小。

show global variables like 'wait_timeout'; --及时释放不活动的连接，注意不要释放连接池还在使用的连接

1.所谓的连接池，就是我们在应用服务器在启动的时候，会初始化多少个连接到数据库(当然这里有好多配置：比如最多连接多少，最少连接数等等)

1.ORM：MyBatis自带了一个连接池
2.连接池的工具：
     Druid（阿里巴巴）
     Hikari(Spring Boot 2.x 版本默认的连接池)
     DBCP (以前比较经典的)
     C3P0 (以前比较经典的)

Druid 的默认最大连接池大小是 8。Hikari 的默认最大连接池大小是 10。

为什么有的情况下，减少连接数反而会提升吞吐量呢？为什么建议设置的连接池大小要跟CPU的核数相关呢？ 
每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多;

问题：CPU 是怎么同时执行远远超过它的核数大小的任务的？
时间片。上下文切换。 而 CPU 的核数是有限的，频繁的上下文切换会造成比较大的性能开销。

1.这里我们说的数据库优化中的缓存，主要是指：可以引入第三方的缓存服务，来缓解数据库的压力，变相的提升数据库的性能；

1.下面例子：在我们曾经做过的一个短信应用服务当中，由于短信的数量特别多，每天几万的短信发送量，
  我们的短信有一些是异步发送的(也就是有些短信实时性要求不是特别高，比如生日提醒短信等等)
2.业务流程：
  2.1.我们先接受业务方发送短信请求MQ，我们保存数据库SMS_SEND表，然后将sms_send表记录的主键保存到redis当中
  2.2.后面我们启动定时任务，去扫描redis当中的数据(我们2.1当中保存的主键)，然后通过主键再去查询sms_send表的具体短信内容，
      这样我们通过redis这一层的缓存操作，可以有效地减少数据库的压力；

1.我们这里之所以提起主从复制的策略，主要是基于可能单台的mysql的数据库已经无法满足应用的请求，我们通过数据库负载集群的方式
  来减少单台服务器的压力，从而有效地提升数据库的性能；
2.关于主从复制的一些原理，可以参考下面的地址学些了解

垂直分库的做法，把一个数据库按照业务拆分成不同的数据库：

水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据表。

1.NDB Cluster 是一种数据库集群负载的存储引擎

1.Galera 是针对MySql提供的一种多主同步复制的集群方案的插件;
2.Galera Cluster for MySQL is  a true Multi-Master Cluster based on synchronous replication. 
  It’s an easy-to-use, high-availability solution, which provides high system up-time, 
  no data loss and scalability for future growth.
  Galera Cluster for

MMM 和 MHA 都是对外提供一个虚拟 IP，并且监控主节点和从节点，当主节点发 生故障的时候，需要把一个从节点提升为主节点，
并且把从节点里面比主节点缺少的数 据补上，把 VIP 指向新的主节点。

MHA（Master HA）是一款开源的 MySQL 的高可用程序，它为 MySQL 主从复制架构提供了 automating master failover 功能。MHA 在监控到 master 节点故障时，会提升其中拥有最新数据的 slave 节点成为新的master 节点，在此期间，MHA 会通过于其它从节点获取额外信息来避免一致性方面的问题。MHA 还提供了 master 节点的在线切换功能，即按需切换 master/slave 节点。
　　MHA 是由日本人 yoshinorim（原就职于DeNA现就职于FaceBook）开发的比较成熟的 MySQL 高可用方案。MHA 能够在30秒内实现故障切换，并能在故障切换中，最大可能的保证数据一致性。目前淘宝也正在开发相似产品 TMHA， 目前已支持一主一从。

1.MySQL 5.7.17 版本推出的 InnoDB Cluster，也叫 MySQL Group Replicatioin （MGR），这个套件里面包括了mysql shell和mysql-route。

1.高可用 HA 方案需要解决的问题都是当一个master节点宕机的时候，如何提升一个数据最新的slave成为master。
  如果同时运行多个 master，又必须要解决 master之间数据复制，以及对于客户端来说连接路由的问题。 
2.不同的方案，实施难度不一样，运维管理的成本也不一样。

1.优化器就是对我们的 SQL 语句进行分析，生成执行计划。 问题：在我们做项目的时候，有时会收到 DBA 的邮件，
  里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？ 
2.我们的服务层每天执行了这么多 SQL 语句，它怎么知道哪些 SQL 语句比较慢呢？ 第一步，我们要把SQL执行情况记录下来。

因为开启慢查询日志是有代价的（跟 bin log、optimizer-trace 一样），所以它默 认是关闭的：

show variables like 'slow_query%';

set @@global.slow_query_log=1; -- 1 开启，0

show variables like '%long_query%';

-- mysql默认的慢查询时间是10秒，另开一个窗口后才会查到最新值
set @@global.long_query_time=3;

set @@global.slow_query_log=1; -- 1 开启，0

set @@global.long_query_time=3;  -- 设置超过3秒 我们认为是慢SQL

slow_query_log = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

select SLEEP(10)

sh-3.2# tail -f localhost-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.27 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument




# Time: 2020-02-06T05:04:47.210966Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    21
# Query_time: 10.002061  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use gp-mysql;
SET timestamp=1580965487;
select SLEEP(10);

1.MySQL提供了mysqldumpslow的工具，在MySQL的bin目录下。我们可以使用这个工具去统计mysql的慢SQL，进行分析

mysqldumpslow -s t -t 5 -g 'select' /usr/local/mysql/data/localhost-slow.log # 后面是你的日志文件目录

sh-3.2# 
sh-3.2# mysqldumpslow -s t -t 5 -g 'select' /usr/local/mysql/data/localhost-slow.log 

Reading mysql slow query log from /usr/local/mysql/data/localhost-slow.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  use gp-mysql;
  SET timestamp=N;
  select SLEEP(N)

Died at /usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 1.
sh-3.2#

SHOW PROFILE 是谷歌高级架构师 Jeremy Cole 贡献给 MySQL 社区的，可以查看SQL 语句执行的时候使用的资源，比如 CPU、IO 的消耗情况。 
在SQL中输入help profile 可以得到详细的帮助信息。

select @@profiling; #1

set @@profiling=1;# 如果需要设置，可以使用这个命令

show profiles;

show profile

show profile for query 1;

show processlist

select * from information_schema.processlist;

SHOW STATUS 用于查看 MySQL 服务器运行状态（重启后会清空），有session和global 两种作用域，
   格式：参数-值。 
   可以用 like 带通配符过滤。

SHOW GLOBAL STATUS LIKE 'com_select'; --

show engine 用来显示存储引擎的当前运行信息，
     包括事务持有的表锁、行锁信息；
        事务的锁等待情况；
        线程信号量等待；
        文件IO请求；
        buffer pool统计信息。

show engine innodb status;

show engines;

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

select version();
show variables like '%engine%';

DROP TABLE IF EXISTS course;

CREATE TABLE `course` (
  `cid` INT(3) DEFAULT NULL,
  `cname` VARCHAR(20) DEFAULT NULL,
  `tid` INT(3) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

DROP TABLE IF EXISTS teacher;

CREATE TABLE `teacher` (
  `tid` INT(3) DEFAULT NULL,
  `tname` VARCHAR(20) DEFAULT NULL,
  `tcid` INT(3) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

DROP TABLE IF EXISTS teacher_contact;

CREATE TABLE `teacher_contact` (
  `tcid` INT(3) DEFAULT NULL,
  `phone` VARCHAR(200) DEFAULT NULL
) ENGINE = INNODB CHARSET = utf8mb4;

INSERT INTO `course`
VALUES ('1', 'mysql', '1');

INSERT INTO `course`
VALUES ('2', 'jvm', '1');

INSERT INTO `course`
VALUES ('3', 'juc', '2');

INSERT INTO `course`
VALUES ('4', 'spring', '3');

INSERT INTO `teacher`
VALUES ('1', 'qingshan', '1');

INSERT INTO `teacher`
VALUES ('2', 'jack', '2');

INSERT INTO `teacher`
VALUES ('3', 'mic', '3');

INSERT INTO `teacher_contact`
VALUES ('1', '13688888888');

INSERT INTO `teacher_contact`
VALUES ('2', '18166669999');

INSERT INTO `teacher_contact`
VALUES ('3', '17722225555');

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 = 'mysql'
  )
);