MySQL知识点汇总（二）

当并发较高或数据量比较大的时候，数据库的瓶颈就会成为制约应用的关键，解决方案有：
1.做mysql 主从机读写分离 （主节点master负责写，从节点slave负责读）。
2.对主机进行 双机热备 （当主节点宕机后，自动切换到另一备用节点上），对从节点做集群（读取数据分离到不同的节点上，以减小读数据库的压力）。

MySQL的高可扩展和高可用，常见问题
MySQL分区的工作原理：
从客户端来看，分区表是一个独立的逻辑表，但是MySQL底层会将其分成多个物理子表，每一个分区表都会使用一个独立的表文件。
创建表时使用partition by 子句定义每个分区存放的数据，执行查询时，优化器会根据分区定义进行过滤，只查询所需数据所在的分区。
分区的主要目的是将数据按照一个较粗的粒度分在不同的表中，这样可以将相关的数据存放在一起，而且如果想一次性删除整个分区的数据也很方便。

使用场景：
1、表非常大，无法全部存在内存，或者只在表的最后有热点数据，其他都是历史数据
2、分区表的数据更容易维护，可以对独立的分区进行独立的操作
3、分区表的数据可以分布在不同的机器上，从而高效使用资源
4、可以使用分区表来避免某些特殊的瓶颈
5、可以备份和恢复独立的分区

缺点：
1、一个表最多只能有1024个分区
2、5.1版本中，分区表表达式必须是整数
3、分区字段中如果有主键和唯一索引列，那么主键列和唯一列都必须包含进来
4、分区表中无法使用外键约束
5、需要对现有表的结构修改
6、所有分区都必须使用相同的存储引擎
7、分区函数中可以使用的函数和表达式会有一些限制
8、某些存储引擎不支持分区

分库分表的工作原理：
通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分。

整体缺点：有些分表的策略基于应用层的逻辑算法，一旦逻辑算法改变，整个分表逻辑都会改变，扩展性较差；
对于应用层来说，逻辑算法无疑增加开发成本。

适用场景:
1、单表记录条数达到百万千万级别时
2、解决表锁的问题

分表方式:
水平分割：表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。

使用场景
1、表中的数据本身就有独立性，列如表中分别记录各个地区的数据或者不同时期的数据，特别时有些数据常用，有些不常用
2、需要把数据存放在多个介质上
缺点：
1、给应用增加复杂度，通常查询时需要多个表名，查询所有数据都需要union操作
2、在许多数据库应用中，这种复杂性会超过它带来的优点，查询时会增加读一个索引层的磁盘次数。

垂直分表：把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。

使用场景：
1、如果一个表中某些列常用，而另外一些列不常用
2、可以使数据行变小，一个数据页能存储更多数据，查询时减少I/O次数
缺点：管理冗余列，查询所有数据需要join操作

MySQL主从复制解决的问题：
数据分布：随意停止或开始复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以使用更高版本的MySQL作为从库
（参考文章：https://blog.csdn.net/weixin_42666036/article/details/113312834）

MySQL故障问题
1.数据库主机LOAD飙高
(1)一般情况下导致MySQL服务器LOAD突然飙高，可能的五种情况如下：
– 全表扫描的SQL语句；
– SELECT操作语句的执行计划走错；
– 存在UPDATE/DELETE 语句没有索引可选择，而导致堵塞其他SQL语句的执行；
– 存在修改表结构或OPTIMIZE 语句执行；
– 大数据量的导入或导出，尤其数据库的逻辑备份操作；
– 业务量大到超过服务器处理能力
(2)要解决LOAD飙高，必须先找到造成飙高的真实原因，请登陆数据库服务器后，一般执行：

SHOW PROCESSLIST;

(3)若一直处在执行状态，且执行时间比较久，可通过分析SQL语句进行查询

EXPLAIN SQL语句

(4)检查io请求状态
通过SQL语句（SHOW PROCESSLIST;），查找造成服务器LOAD飙高的PID，特别是DELETE 或UPDATE等会堵住其他SQL语句的PID，然后进入MySQL命令行工具中，对一些SQL先记录下来，
必要时考虑执行：KILL SQL PID
2. 请求处理波动，应用大量请求超时失败

SHOW STATUS;   -- 状态分析
EXPLAIN SQL语句;  -- 分析slow query， 检查可疑SQL 
Kill SQL PID;   -- 如有长时间运行异常SQL,kill掉
另外，重点关注索引的使用问题

复制故障

复制中断的原因：
(1)备机无法连接到主服务器，可能是网络问题，也可能是主服务器的mysqld已停止；
(2)主键冲突；
(3)主从服务器数据不一致；
(4)其他原因；
一般情况下通过重启服务器即可，但解决主键冲突或者主从服务器数据不一致的情况时，则需进行以下操作：
(1)停止slave进程 STOP SLAVE;
(2)设置事务号，事务号从Retrieved_Gtid_Set获取 SET @@SESSION.GTID_NEXT= 'xxxxxxxxxxx'
(3)设置空事务 BEGIN; COMMIT;
(4)恢复事务号SET SESSION GTID_NEXT = AUTOMATIC;
(5)启动slave进程START SLAVE;

MySQL实例假死故障：
1.确认mysql的运行状态SHOW PROCESSLIST;是否有大量请求在等待处理
2.检查mysql实例的进程的内存/交换分区状态
结合当前业务压力，检查内存消耗现状，消耗速度，是否开始使用交换分区,如内存消耗过快且业务压力不大，并发不高，转向对slow query的定位。
3.检查mysql实例的进程分区使用情况和IO状态
确认分区有足够空间，如异常，则进入对应目录，检查实例的数据和日志存放和增长情况，特别是对日志进行清理处理，或通过调整逻辑卷的大小解决问题。
4.检查mysqld的CPU使用情况
结合当前业务压力和连接数，确认mysql进程的CPU占用率是否异常，如当前业务压力不大，并发不高，转向对slow query的定位。
5.检查实例主机的网络通信
6.Slow query 分析
打印slow query日志，分析和定位造成故障的SQL, 必要时，kill 掉出现问题的SQL ID

应用报连接池满
(1) 检查io请求状态
(2)运行orzdba对照SQL执行数量(QPS-sel栏和TPS-iud栏)和逻辑读消耗(Hit%-lor栏)
(3) 转向slow query分析查看行扫描Query_time和Rows_examined栏，如无明显定位和结论，进入下一步全面分析
(4)使用pt-query-digest 全面分析slow query，着重分析索引的合法性
(5) 检查General log
(6) 检查Binlog, 对于DML操作, 通过mysqlbinlog工具解析binlog检查
(7) 使用Linux命令 tcpdump抓包分析，配合pt-query-digest做进一步分析排查

MySQL请求线程堵塞
(1)检查 processlist：processlist 主要查看 Time, State 栏的汇报值
(2)使用pstack 保存当前mysqld实例的栈调用，以备进一步分析
（参考文章：https://blog.csdn.net/weixin_38320674/article/details/106610727）

mysql问题排查及调优常用命令
0.常规后台进程报错日志log-error路径：

show variables like '%log_error%'; -- 文本文件，可以直接查看

1.查看数据库配置的最大连接数

show VARIABLES like 'max_connections'

2.SHOW PROCESSLIST查看当前数据库运行的所有线程、命令、状态、耗时、SQL

SHOW PROCESSLIST;  -- 只显示前100条
SHOW full PROCESSLIST;  -- 显示全量数据

3.查看当前运行的所有事务

select * from information_schema.INNODB_TRX

4.查看当前出现的锁

select * from information_schema.INNODB_LOCKS

5.查看锁等待的对应关系

select * from information_schema.INNODB_LOCK_WAITS

6.SHOW ENGINE INNODB STATUS 查看Innodb状态

SHOW ENGINE INNODB STATUS;  -- 该命令只能获取最近一次的死锁信息

7.SHOW STATUS LIKE 'innodb_row_lock_%'查询当前锁性能状态

SHOW STATUS LIKE 'innodb_row_lock_%

8.查看死锁

SELECT b.trx_state, e.state, e.time, d.state AS block_state, d.time AS block_time
, a.requesting_trx_id, a.requested_lock_id, b.trx_query, b.trx_mysql_thread_id, a.blocking_trx_id
, a.blocking_lock_id, c.trx_query AS block_trx_query, c.trx_mysql_thread_id AS block_trx_mysql_tread_id
FROM information_schema.INNODB_LOCK_WAITS a
LEFT JOIN information_schema.INNODB_TRX b ON a.requesting_trx_id = b.trx_id
LEFT JOIN information_schema.INNODB_TRX c ON a.blocking_trx_id = c.trx_id
LEFT JOIN information_schema.PROCESSLIST d ON c.trx_mysql_thread_id = d.id
LEFT JOIN information_schema.PROCESSLIST e ON b.trx_mysql_thread_id = e.id
ORDER BY a.requesting_trx_id;

慢查询相关

show variables like 'log_slow_queries';  -- 查看慢SQL是否启用
set global log_slow_queries = on;  -- 开启慢查询命令
show global  variables like 'long_query_time'; -- 查看慢查询参数，即设置超过多少秒的查询归为了慢查询
set global long_query_time =1; -- 设置超过1秒就会被认为慢查询
show variables like 'slow_query_log_file';  -- 查看慢查询存放日志
select * from mysql.slow_log; -- 查看慢查询
show global status like '%slow_queries%'; -- 查询有多少条慢查询记录

死锁问题排查思路
1.错误日志报警发现死锁异常
2.查看错误日志的堆栈信息
根据1，2步骤可以找到死锁异常时进行回滚事务的具体业务，也就能够找到该事务执行的 SQL 语句
3.查看 MySQL 死锁相关的日志
死锁日志的获取
发生死锁异常后，我们可以直接使用 show engine innodb status 命令获取死锁信息，但是该命令只能获取最近一次的死锁信息。所以，我们可以通过开启 InnoDB 的监控机制来获取实时的死锁信息，它会周期性（每隔 15 秒）打印 InnoDb 的运行状态到 mysqld 服务的错误日志文件中。
InnoDb 的监控较为重要的有标准监控（Standard InnoDB Monitor）和 锁监控（InnoDB Lock Monitor），通过对应的系统参数可以将其开启。

-- 开启标准监控
set GLOBAL innodb_status_output=ON;
-- 关闭标准监控
set GLOBAL innodb_status_output=OFF;
-- 开启锁监控
set GLOBAL innodb_status_output_locks=ON;
-- 关闭锁监控
set GLOBAL innodb_status_output_locks=OFF;

另外，MySQL 提供了一个系统参数 innodb_print_all_deadlocks 专门用于记录死锁日志，当发生死锁时，死锁日志会记录到 MySQL 的错误日志文件中。

`set` `GLOBAL` `innodb_print_all_deadlocks=``ON``;`

4.根据 binlog 查看死锁相关事务的执行内容
通过死锁日志，我们可以找到最终获得锁事务最后执行的 SQL，但是如果该事务执行了多条 SQL，这些信息就可能不够用的啦，我们需要完整的了解该事务所有执行的 SQL语句。这时，我们就需要从 binlog 日志中获取。
binlog 日志会完整记录事务执行的所有 SQL，借助它，我们就能找到最终获取锁事务所执行的全部 SQL。然后再进行具体的锁冲突分析。
开启binlog方式
1.先看binlog是否开启

show variables like 'log_%';

若binlog未开启，则需配置文件，开启Binlog，重启mysql服务
我们可以使用 MySQL 的命令行工具 Mysqlbinlog 远程获取线上数据库的 binlog 日志

Mysqlbinlog -hlocalhost -u root -p --read-from-remote-server mysql-bin.000001 --base64-output=decode-rows -v

5.根据上述信息找出两个相互死锁的事务执行的 SQL 操作，分析推断死锁原因
6.修改业务代码
（参考文章：https://blog.csdn.net/jy02268879/article/details/108753273）

使用 explain 查询和分析 SQL 的执行计划
explain 返回结果中各列的含义
table：显示这一行的数据是关于哪张表的
type：显示连接使用了哪种类型，从最好到最差的连接类型为 const、eq_reg、ref、range、index 和 all
possible_keys：显示可能应用在这张表中的索引。显示为空，则没有可能的索引
key：实际使用的索引。如果为 NULL，则没有使用索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了。如果可能的话，是一个常数
rows：MySQL认为必须检查的用来返回请求数据的行数
extra：显示 MySQL 在查询过程中的一些详细信息。若是 Using filesort 或者 Using temporary，就需要进行查询优化了

基于zookeeper的高可用：
zooKeeper是一个典型的发布/订阅模式的分布式数据管理与协调框架，通过zookeeper中丰富的数据节点类型进行交叉使用，配合watcher事件通知机制，可以方便地构建一系列分布式应用涉及的核心功能，比如：数据发布/订阅，负载均衡，分布式协调/通知，集群管理，Master选举，分布式锁和分布式队列等。
zookeeper如何解决HA自身可用性问题呢？。
架构图如下：

图中每个MySQL节点上面部署了一个HA client，用于实时向zookeeper汇报本地节点的心跳状态，比如主库crash，通过修改zookeeper(以下简称zk)上的节点信息，来通知HA。HA节点在zk上注册监听事件，当zk节点发生变化时会自动让HA感知，HA节点可以部署一个或多个，主要用于容灾。HA节点之间通过zookeeper服务来实现数据的一致性，通过分布式锁保证多个HA节点不会同时对一个主从节点进行切换。HA本身是无状态的，所有MySQL节点状态信息全部保存在zookeeper服务器上，切换时，HA会对MySQL节点进行复检，然后切换。

引入zookeeper后的切换流程：
a.HA client 检测到 Master 异常，进行一系列判断，最后确定 Master 宕掉；
b.HA client 删除 Master在zk上的节点信息；
c.由于监听机制，HA会感知到有节点被删除；
d.HA对MySQL节点进行复检，比如建立连接，更新心跳表等
e.确认异常后，则进行切换。

保证HA自身的高可用
(1).如果HA-client本身挂了，MySQL节点正常？
HA-Client管理的MySQL节点无法与zookeeper保持心跳，zk服务将节点删除，HA会感知到这种变化，准备尝试一次切换，切换前，会进行复检，复检时发现MySQL节点是OK的，则不会切换。
(2).MySQL节点与zookeeper的网络断了，那么表现如何？
由于HA-Client与节点在同一台主机，因此HA-client无法再定时向zk汇报心跳，zk会将对应的MySQL节点信息删除，HA尝试复检，依然失败，则进行切换。
(3).HA挂了，表现如何？
由于HA无状态，并且有多个副本，因此一个HA挂了，不会对整个系统造成影响。

优点：
1.保证了整个系统的高可用
2. 主从的强一致依赖于MySQL本身，比如半同步，或者外围工具的回补策略，类似MHA。
3. 扩展性非常好，可以管理大规模集群。
缺点：
1.引入zk，整个系统变得复杂。
（参考文章：https://blog.csdn.net/dhfzhishi/article/details/82561939）