85.mysql常见的原理问题

1.一个SQL在运行时为何突然变慢 

(1)log buffer满了在清理。
(2)查询涉及刷脏页,整个数据库短暂停止写。
(3)update操作清空了缓存的内容。

2.主从延迟原因

(1)主库执行大事务
(2)网络延迟,日志较大
(3)主库多线程,从库单线程
(4)主库机器好,从库机器差
(5)主库发生锁冲突
(6)慢SQL语句过多
(7)主库读写压力大
(8)SLAVE太多

3.夯死问题如何分析

(1)检查系统资源:CPU,内存,磁盘 
(2)show processlist;查看进程相关信息
(3)日志查看:慢日志，错误日志，二进制日志
(4)锁冲突查询。
(5)参数配置检查
(6)版本过低.

4.SQL优化的基本思路

1.优化数据库的设计:合适的数据类型,合适的索引。
2.SQL优化,使用JOIN替代子查询,避免使用SELECT *,避免全表扫描。
3.优化数据库的参数:innodb_buffer_pool_size,innodb_log_file_size,
innodb_log_buffer_size,max_connection;
4.使用合适的存储引擎:INNODB,支持事务和行级锁,有好的并发性。MyISAM适用于读密集型场景。
5.分区和分表:减少单个表的数据量和索引的大小。
6.优化磁盘和文件系统，日志和数据存放在不同的物理磁盘,分离I/O,使用SSD提高写入性能。
磁盘IO分析,是否分析:RAID阵列卡开高速缓存功能,磁盘开预读功能及队列增加队列深度。
7.监控和调优:定期分析数据库的性能，并使用性能分析工具慢查询进行优化。
8.表的碎片清理。

5.索引失效的原因

1.查询未使用索引列
2.索引列上使用函数或运算符。
3.隐式类型转换，条件列和索引列类型不匹配。
4.字符串字段过长
5.多列联合索引顺序问题,与索引存储字段顺序不同。
6.数据量较小,无需使用索引。
7.索引统计信息不准确。
8.配置不当,innodb_buffer_pool_size或query_cache_size不合理。

6.一个UPDATE执行的过程

UPDATE MYTAB1 SET NAME = NAME || '1' WHERE ID = 1;
	UPDATE步骤:
	1.连接器:管理连接,权限验证。
	2.分析器:词法分析,语法分析。
	3.检查数据是否在内存中,如果数据在内存中,返回行数据。如果不在内存中,先从磁盘读取到内存。再返回数据行。
	4.更新数据并写入到内存中。
	5.更新的数据写入redo log;(prepare状态)
	6.更新的数据写入binlog;
	7.提交事务,commit状态。

7.数据写入redo 时断电，请问重启后数据怎么恢复

1)如果在断电时 redolog 还没有写入，那么重启后将没有任何的数据变化。
2)如果redolog 已经写入了但状态还在prepare时，binlog日志还没有写入，那么重启后会将事务进行回滚，数据也没有任何变化。
3)如果redolog 已经写入了，binlog也写入了，但redolog日志还没commit, 那么重启后会检查binlog是否完整，如果完整则提交事务，
数据发生变化，如果不完整则回滚事务，数据也不发生变化。

8.强制启动数据库的几个级别

		[mysqld]
		innodb_force_recovery = 1
		innodb_force_recovery 可以设置为 1-6，大的值包含前面所有小于它的值的影响。
		
1=(SRV_FORCE_IGNORE_CORRUPT): 忽略检查到的 corrupt 页。尽管检测到了损坏的 page 仍强制服务运行。
		一般设置为该值即可，然后 dump 出库表进行重建。
		
2=(SRV_FORCE_NO_BACKGROUND): 阻止主线程的运行，如主线程需要执行 full purge 操作，会导致 crash。 
		阻止 master thread 和任何 purge thread 运行。若 crash 发生在 purge 环节则使用该值。
		
3=(SRV_FORCE_NO_TRX_UNDO): 不执行事务回滚操作。
		
4=(SRV_FORCE_NO_IBUF_MERGE): 不执行插入缓冲的合并操作。如果可能导致崩溃则不要做这些操作。
		不要进行统计操作。该值可能永久损坏数据文件。若使用了该值，则将来要删除和重建辅助索引。
		
5=(SRV_FORCE_NO_UNDO_LOG_SCAN): 不查看重做日志，InnoDB 存储引擎会将未提交的事务视为已提交。
		此时 InnoDB 甚至把未完成的事务按照提交处理。该值可能永久性的损坏数据文件。
		
6=(SRV_FORCE_NO_LOG_REDO): 不执行前滚的操作。恢复时不做 redo log roll-forward。
		使数据库页处于废止状态，继而可能引起 B 树或者其他数据库结构更多的损坏。

9.MYSQL5.7和MYSQL8.0的新特性

MySQL5.7新特性: 
1) 随机 root 密码
		MySQL 5.7 数据库初始化完成后，会自动生成一个 root@localhost 用户，root 用户的密码不为空，而是随机产生一个密码。
2) 自定义 test 数据库
		MySQL 5.7 默认安装完成后没有 test 数据库。用户可以自行创建 test 数据库并对其进行权限控制。
3) 默认 SSL 加密
		MySQL 5.7 采用了更加简单的 SSL 安全访问机制，默认连接使用 SSL 的加密方式。
4) 密码过期策略
		MySQL 5.7 支持用户设置密码过期策略，要求用户在一定时间过后必须修改密码。
5) 用户锁
		MySQL 5.7 为管理员提供了暂时禁用某个用户的功能，使被锁定的用户无法访问和使用数据库。
6) 全面支持JSON
		随着非结构化数据存储需求的持续增长，各种非结构化数据存储的数据库应运而生（如 MongoDB），各大关系型数据库也不甘示弱，
纷纷提供对 JSON 的支持，以应对非结构化数据库的挑战。
MySQL 5.7 也提供了对 JSON 的支持，在服务器端提供了一组便于操作 JSON 的函数。存储的方法是将 JSON 编码成 BLOB 后再由存储引擎进行处理。这样，MySQL 就同时拥有了关系型数据库和非关系型数据库的优点，并且可以提供完整的事务支持。
7) 支持两类生成列（generated column）
生成列是通过数据库中的其他列计算得到的一列。当为生成列创建索引时，可以便捷地加快查询速度。
MySQL 5.7 支持虚拟生成列和存储生成列。虚拟生成列仅将数据保存在表的元数据中，作为缺省的生成列类型；存储生成列则是将数据永久保存在磁盘上，需要更多的磁盘空间。
8) 引入系统库（sys schema）
系统库中包含一系列视图、函数和存储过程，通过多线程、多进程、组合事务提交和基于行的优化方式将复制功能提高 5 倍以上，用户向外扩充其跨商品系统的工作负载时，得以大幅提升复制的效能和效率。
		



MySQL8.0的新特性
		1.隐藏索引 
		2.参数设置持久化
		3.默认字符集改为utf8mb4,包含表情符。
		4.WITH AS 通用表表达式。
		5.窗口函数。 
		
1.隐藏索引
		隐藏索引的特性对于性能调试非常有用。在 8.0 中，索引可以被“隐藏”和“显示”。当一个索引隐藏时，它不会被查询优化器所使用。
		也就是说，我们可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了。隐藏一个索引的语法是：
ALTER TABLE t ALTER INDEX i INVISIBLE;
恢复显示该索引的语法是：
ALTER TABLE t ALTER INDEX i VISIBLE;
当一个索引被隐藏时，我们可以从 show index 命令的输出中看到，该索引的 Visible 属性值为 NO。
注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的，这个特性本身是专门为优化调试使用。
如果你长期隐藏一个索引，那还不如干脆删掉，因为毕竟索引的存在会影响插入、更新和删除的性能。
		
2.设置持久化
MySQL 的设置可以在运行时通过 SET GLOBAL 命令来更改，但是这种更改只会临时生效，到下次启动时数据库又会从配置文件中读取。
ySQL 8 新增了 SET PERSIST 命令，例如：
SET PERSIST max_connections = 500;
MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖缺省的配置文件。
		
3.UTF-8 编码
从 MySQL 8 开始，数据库的缺省编码将改为 utf8mb4，这个编码包含了所有 emoji 字符。
多少年来我们使用 MySQL 都要在编码方面小心翼翼，生怕忘了将缺省的 latin 改掉而出现乱码问题。从此以后就不用担心了。
		
4.通用表表达式（Common Table Expressions）
		复杂的查询会使用嵌入式表，例如：
		SELECT t1.*, t2.* FROM (SELECT col1 FROM table1) t1,(SELECT col2 FROM table2) t2;
		而有了 CTE，我们可以这样写：
		WITH t1 AS (SELECT col1 FROM table1),
			t2 AS (SELECT col2 FROM table2)
		SELECT t1.*, t2.* 
		FROM t1, t2;
		这样看上去层次和区域都更加分明，改起来也更清晰的知道要改哪一部分。

5.窗口函数（Window Functions）
		MySQL 被吐槽最多的特性之一就是缺少 rank() 函数，当需要在查询当中实现排名时，必须手写 @ 变量。
		但是从 8.0 开始，MySQL 新增了一个叫窗口函数的概念，它可以用来实现若干新的查询方式。
		窗口函数有点像是 SUM()、COUNT() 那样的集合函数，但它并不会将多行查询结果合并为一行，
		而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUP BY 的。

10.MYSQL的多线程复制

什么是MySQL的多线程复制。
		(1)Mysql 5.6 引入了 "多线程复制:" 这个新功能.
		(2)先看下现在 slave 的并发类型，通过变量 slave_parallel_type 的值来获得，这个变量用来决定如何使用多线程复制
		默认是 datebase,每个线程只能处理一个数据库,DATABASE表示并行的基础是以数据库为基础，一个数据库开启一个线程
		但是实际是一个数据库很多的写入，如果按照DATABASE来sql回放发挥不出并行的性能
		set global slave_parallel_type='logical_clock';  --配置成基于逻辑时钟的方式
		set global slave_parallel_workers=4; 
		slave_parallel_workers 原先的sql线程会变成为协调线程，重建16个worker(官方建议16个)，不能设置1，设置1还不如原先的性能（不设置sql的并行复制）
		
		根据实际生产调整具体的worker是多少，官方测试是16
		因为一旦走了并行，机制就变化了，通过协调线程来做转发，性能一部分用在了转发上面
		master_info_repository 和 relay_log_repository
		启动并行复制后，每次复制的时候，主从的信息会保留在数据目录/usr/local/mysql/data 下面的 master.info 和relay-log.info
		激活并行复制后，文件变更速度比原先快得多，频繁的刷文件，这样的话，性能会下降，配合并行复制 mts，官方建议开启两个参数选项
		set global master_info_repository=TABLE
		set global relay_log_repository=TABLE
		信息保存方式2种，一种是文件，一种是表（放在数据库中），由数据库系统做刷盘动作，而不是直接写入文件保存，这样开启并行复制的时候性能能提升
		master.info 和 relay-log.info 不存在了，变成数据库表了

11.MYSQL主从复制的原理

MySQL主从复制的原理是什么
		(1) Master的更新事件(update、insert、delete,DDL)会按照顺序写入bin-log中。当Slave连接到Master的后,Master机器会为Slave开启
		binlog dump线程,该线程会去读取bin-log日志
		(2) Slave连接到Master后,Slave库有一个I/O线程 通过请求binlog dump thread读取bin-log日志,然后写入从库的relay log日志中。
		(3) Slave还有一个 SQL线程,实时监控 relay-log日志内容是否有更新,解析文件中的SQL语句,在Slave数据库中去执行。
		Slave 执行SQL<--SQLThread解析SQL<--relay-log <--(Slave)SlaveIOThread<--(Master)binlogDumpThread<--binlog<--Master
		

        Master-->binlog-->binlogDumpThread-->(Slave)SlaveIOThread-->relay-log-->SQLThread解析SQL-->Slave执行SQL;
		(1)开启binlog二进制日志
		(2)主库将DML/DDL 写入binlog;
		(3)Slave连接主库后,主库生成binlogDumpThread线程, Master binlogDumpThread 读取binlog 发送给Slave IOThread
		(4)Slave IOThread将接收的binlog写入relaylog;

12.MYSQL MHA的原理

(1)解决主服务器的高可用。
			MHA（master high availability）是mysql高可用方面相对成熟的解决方案，是非常好的故障切换和主从提升的高可用
			软件，它由日本人开发。在mysql故障切换过程中，MHA能做到0~30秒之内自动完成数据库的故障切换操作。并且在故障切换
			中，MHA能在最大程度上保证数据的一致性。
			
			MHA有两部分组成：MHA manager(管理节点)和MHA node(数据节点)
			MHA manager可以单独部署在一台机器上管理多个master-slave集群，也可以部署在一台slave节点上。
			MHA NODE运行在每台mysql服务器上，MHA manager会定时探测集群中的master节点。
			当master出现故障时，它可以自动将新数据的slave提升为新的master,然后将所有的其它slave重新指向新的master,
			这个故障转移过程对应用程序完全透明。
		(2)MHA Manager;
			1.master自动切换及故障转移命令运行。
			2.其它的帮助脚本运行：手动切换master;master/slave状态检测。
		(3)MHA node:
			1.复制主节点的binlog数据。
			2.对比从节点的中继日志文件。
			3.无需停止从节点的SQL线程，定时删除中继日志。

		(4)MYSQL MHA工作原理。
			如何通过mha manager管理多组主从复制，可以将mha工作原理总结为如下：
			1.从宕机奔溃的master保存二进制日志事件（binlog events）
			2.识别含有最新更新的slave;
			3.应用差异的中继日志（relay log）到其它的slave;
			4.应用从master保存的二进制日志事件（binlog events）
			5.提升一个slave为新的master;
			6.使其它的slave连接到新的master进行复制。
		(5)MHA优缺点
			优点：双主热备模式，读写分离，slave集群可线性扩展。
			缺点：读写分离需要在程序端解决，master大批量写操作时会产生主从延时。
		(6)mysql mha工具优点：
			由perl语言开发的开源工具。
			master自动监控和故障转移。
			master crash不会导致主从数据不一致性。
			可以支持基于GTID的复制模式：mysql5.7版本。
			MHA在进行故障转移时更不易产生数据丢失。
			同一个节点可以监控多个集群。
			MHA加强了数据的安全性。
		(7)mysql MHA工具缺点。
			需要编写脚本或利用第三方工具来实现VIP的配置。
			MHA启动后只会对主进行监控。
			需要基于SSH免认证配置，存在一定的安全隐患。
			没有提供从服务器的读负载均衡功能。

		(8)mysql mha 工具组件：
			mha软件由两部分组成，manager工具包和Node工具包，具体的说明如下。
			1).manager工具。
			masterha_check_ssh  检查mha的ssh配置。
			masterha_check_repl 检查mysql复制。
			masterha_manager 启动mha 
			masterha_check_status:检测当前mha运行状态。
			masterha_master_monitor:监测master是否宕机。
			masterha_master_switch:控制故障转移：自动或手动。
			masterha_conf_host: 添加或删除配置的server信息。
			
			2).NoDe 工具：这些工具通常由mhamanager的脚本触发，无需人工操作。
			save_binary_logs: 保存和复制master的二进制日志。
			apply_diff_relay_logs:识别差异的中继日志事件并应用于其它slave;
			filter_mysqlbinlog;去除不必要的rollback事件（Mha已不再使用这个工具）
			purge_relay_logs清楚中继日志：不会阻塞sql线程。
			
			3).自定义扩展。
			secondary_check_script；通过多条网络路由检测master的可用性。
			master_ip_failover_script:更新application使用的masterip(需要修改)
			shutdown_script:强制关闭master节点。
			report_script:发送报告。
			init_conf_load_script:加载初始化配置参数。
			master_ip_online_change:更新master节点IP地址。

13.MYSQL异步复制，同步复制，半同步复制的区别

(1)异步复制
		MySQL主从同步 默认是异步复制的。就是上面三步中,只有第一步是同步的(也就是Mater写入bin log日志),
		就是主库写入binlog日志后即可成功返回客户端，无须等待binlog
		日志传递给从库的过程。Master 不关心 Slave 的数据有没有写入成功。因此如果Master和Slave之间有网络延迟，
		就会造成暂时的数据不一致的现象；如果Master出故障，而数据
		还没有复制过去，则会造成数据丢失；但也有好处，效率较其他两种复制方式最高。
		
		(2)同步复制
		对于同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到所有Slave节点（如果有多个Slave）
		返回数据复制成功的信息给Master。这种复制方式最安全，但是同时，效率也是最差的。
		
		(3)半同步复制
		对于半同步复制而言，Master主机将事件发送给Slave主机后会触发一个等待，直到其中
		一个Slave节点（如果有多个Slave）返回数据复制成功的信息给Master。由此增强了
		数据的一致性，但是因为Master主机的确认开销，会损耗一部分的性能；另外，
		半同步复制除了不需要等待所有Slave主机确认事件的接收外，半同步数据复制并不要求那些事件
		完全地执行，因此，仍有可能看到在Slave主机上数据复制延迟的发生，如果因为网络延迟等原因
		造成Slave迟迟没有返回复制成功的信息，超过了Master设置的超时时长，半同步
		复制就降级为异步复制方式，而后继续数据复制。

在TDSQL里面使用MAR强同步复制方案来解决异步复制，同步复制，半同步复制的问题。

14.MYSQL锁的分类

MySQL锁:
1.操作类型分类:共享锁(S锁),排他锁(X锁)
2.锁的粒度分类:全局锁
			   表锁:表级别的S锁、X锁,意向锁,自增锁,元数据锁。
			   行锁:记录锁,间隙锁,临键锁,插入意向锁
3.锁的态度分类:悲观锁,乐观锁。

1)共享锁（S锁）：也称读锁，允许事务对某些数据进行读取。多个事务的读操作不会相互影响，也不会相互阻塞。
select .. lock in share mode; 
select .. for share nowait; --nowait:表示不等待直接报错。
select .. for share skip locked; --表示立即返回,但返回的结果不包含被锁定的行。
LOCK TABLES t READ：--对表t加表级别的共享锁

2)排他锁（X锁）：也称写锁，允许事务对某些数据进行删除或更新。如果当前操作还没完成，
其他事务的S和X锁是会被阻塞的，确保在多个事务中，对同一资源，只有一个事务能写入，
并防止其他用户读取正在写入的资源
SELECT ... FOR UPDATE; --排他锁
LOCK TABLES t WRITE：--对表t加表级别的排他锁
3)意向共享锁（IS）：事务有意向对表中的某些行加共享锁（S锁），会自动加上意向共享锁		
-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;	
4)意向排他锁（IX）：事务有意向对表中的某些行加排他锁（X锁），会自动加上意向排它锁
-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;
由于InnoDB存储引擎支持的是行级别的锁，因此意向锁不会阻塞除全表扫描以外的任何请求，
它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

5)自增锁
表中有自增列时，插入记录会使用到自增锁，一个事务持有自增锁时，
其他事务的插入语句会被阻塞。了解即可。

6)元数据锁
在对某个表执行一些诸如ALTER TABLE 、DROP TABLE这类的DDL语句时，
其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞。
同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，
在其他事务中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之
为元数据锁（英文名： Metadata Locks ，简称 MDL）结构来实现的。

-- 查看元数据锁
select OBJECT_TYPE,OBJECT_SCHEMA,OBJECT_NAME,LOCK_TYPE, LOCK_DURATION 
from performance_schema.metadata_locks;

7)行锁1:记录锁:记录锁就是行级别的X锁和S锁，仅仅锁住一行记录，
分S型记录锁和X型记录锁，和前面的规则一样，官方的类型名称为： LOCK_REC_NOT_GAP

8)行锁2:间隙锁（Gap Locks）
MySQL在REPEATABLE READ隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC方案解决，也可以采用加锁方案解决。
官方的类型名称为： LOCK_GAP。
加锁方式有点尴尬，幻影记录还未出现，给谁加锁呢？InnoDB提出了一种称之为Gap Locks的锁

9)行锁3:临键锁（Next-Key Locks）
有时候我们既想锁住某条记录 ，又想阻止其他事务在该记录前边的间隙插入新记录，
所以InnoDB就提出了一种称之为Next-Key Locks的锁 。临键锁是在存储引擎InnoDB、
事务级别在可重复读 的情况下使用的数据库锁， InnoDB默认的锁就是Next-Key locks。
官方的类型名称为： LOCK_ORDINARY
临键锁 = 记录锁 + 间隙锁
-- 给id小于等于8的所有记录加上临键锁
select * from student where id<=8 for update;
-- 给id为[6,8]的记录加上临键锁
select * from student where id<=8 and id>6 for update;

10)行锁4:插入意向锁（Insert Intention Locks）
一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了间隙锁，
如果有的话，插入操作需要等待，直到有间隙锁的那个事务提交。但是InnoDB规定事务在
等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，
但是现在在等待。InnoDB就把这种类型的锁命名为插入意向锁 。插入意向锁是一种Gap锁，
不是意向锁，在insert操作时产生。
插入意向锁是在插入一条记录行前，由INSERT操作产生的一种间隙锁 。 
事实上插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。
每个层级的锁数量是有限制的，因为锁会占用内存空间， 锁空间的大小是有限的。
当某个层级的锁数量超过了这个层级的阈值时，就会进行锁升级。


11)全局锁
全局锁就是对整个数据库实例加锁。当你需要让整个库处于只读状态的时候，可以使用这个命令，主要是做全库逻辑备份。
备份时应该锁定整个库，保证数据的完整性。
--加全局锁的命令:
FLUSH tables with read lock;
-- 解锁
unlock tables;

12)悲观锁（Pessimistic Locking）
假设最坏的情况，每次操作数据都会加上锁，如行锁、表锁等，都是在做操作之前先上锁，
当其他线程想要访问数据时，都需要阻塞挂起。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现
使用悲观锁来解决问题：当查询库存时就把数据给锁定，保证同时只能有一个事务查询到库存，
其他事务必须等他将库存减去后才能查询到库存

13)乐观锁（Optimistic Locking）
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，
它不采用数据库自身的锁机制，而是通过程序来实现。
在程序上，我们可以采用版本号机制或者CAS机制实现。乐观锁适用于多读和冲突不激烈的应用类型，
这样可以提高吞吐量。在Java中通过CAS实现的。
乐观锁机制
在表中增加一个版本字段version，对数据进行更新时会执行
UPDATE ... SET version=version+1 WHERE version=xx。
如果已经有事务对这条数据进行了更新，则不会成功
 
14)死锁
当出现死锁以后，有两种策略：
直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
将参数innodb_deadlock_detect设置为on ，表示开启这个逻辑。

15.总结

MYSQL的原理深入，建立在不断的进行阅读，处理问题，深入理解每个问题背后的底层逻辑是什么。大多数时候我们不会去想底层的原理，但是在遇到故障时，有时候需要理解底层的原理，才能清晰定位问题的原因。所以理解MySQL底层的原理，对于故障处理及问题排除有很好的指导左右。