MySQL45
https://funnylog.gitee.io/mysql45/
原系列目录：
01 讲基础架构：一条SQL查询语句是如何执行的
主要介绍
MySQL分为Server层和存储引擎层两部分:
- Server层包括连接器、查询缓存、分析器、优化器、执行器等
- 存储引擎层负责数据的存储和提取。
00 开篇词讲这一次，让我们一起来搞懂MySQL
- 01 讲基础架构：一条SQL查询语句是如何执行的
- 主要讲组件分层，每个组件的大致作用（连接器、查询缓存、分析器、优化器、执行器等）
- 02 讲日志系统：一条SQL更新语句是如何执行的
- 结合组件，引申到binglog和redolog的配合使用，以及两阶段提交
- 03 讲事务隔离：为什么你改了我还看不见
- 事务隔离级别
- 介绍了【可重复读】的实现，MVVC等
- 事务启动方式
- 04 讲深入浅出索引(上)
- 主键索引和普通索引的数据结构推演，不同数据结构的优劣
- 符合磁盘读写特性，减少IO次数
- 长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
- 主键索引和普通索引的数据结构推演，不同数据结构的优劣
- 05 讲深入浅出索引(下)
- 索引深入概念，覆盖索引、前缀索引、索引下推
- 索引重建的优化作用：删除记录后，索引不一定删除。（索引文件大小有可能大于表文件） alter table T engine=InnoDB
- 06 讲全局锁和表锁：给表加个字段怎么有这么多阻碍
- 引入数据库备份的例子，先备份余额表还是课程表
- 表级锁，MDL机制，举例ddl加字段和同时读取场景
- 07 讲行锁功过：怎么减少行锁对性能的影响
- MySQL的行锁，涉及了两阶段锁协议、死锁和死锁检测这两大部分内容。
- 以电影院售票的例子，针对[这种热点行更新导致的性能问题]给出3个解决方案：1、临时关闭死锁检测 2、控制并发度 3、拆分热点行和业务逻辑兼容
- 08 讲事务到底是隔离的还是不隔离的
- 09 讲普通索引和唯一索引，应该怎么选择
- 查询过程，性能几乎无差别（由于引擎是按页读取，普通索引多做一次内存判断，损耗极小）
- redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗
- 唯一索引的缺点：大量插入数据慢、内存命中率低
- 10 讲MySQL为什么有时候会选错索引
- InnoDB Persistent Statistics https://blog.csdn.net/cuiwangxie1183/article/details/100483099
- 索引统计的更新机制，并提到了优化器存在选错索引的可能性。
- 11 讲怎么给字符串字段加索引
- 12 讲为什么我的MySQL会“抖”一下
- 用孔乙己酒馆粉板和账本的例子，解释了redolog、磁盘以及何时flush，并说明了脏页、干净页
- 重温redolog上的checkpoint、write-pos机制
- InnoDB刷脏页的控制策略
- 引申再讲了redo-log在ACID特性中D的支持
- 13 讲为什么表数据删掉一半，表文件大小不变
- 数据删除流程，每个page删除后，会留下数据空洞，
- alter table A engine=InnoDB命令重建表，原理
- 其他重建表方式的比较 [analyze table t] [optimize table t 等于recreate+analyze]
- 14 讲count这么慢，我该怎么办
- 罗列各种count方案的优劣，缓存方案、table信息方案
- 提出汇总表的方案
- 按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)
- 15 讲答疑文章（一）：日志和索引相关问题
- 重新讲了两阶段提交中，redo-log和binlog操作时，系统崩溃的恢复过程。
- 16 讲“orderby”是怎么工作的
- 17 讲如何正确地显示随机消息
- 介绍了MySQL对临时表排序的执行过程， 内存临时表(Using temporary)和磁盘临时表(Using filesort)之间的区别
- 18 讲为什么这些SQL语句逻辑相同，性能却差异巨大
- 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
- 隐式类型转换、连表时字符集未对齐，都可能导致放弃走索引
- 19 讲为什么我只查一行的语句，也执行这么慢
- 等MDL锁、等flush、等行锁
- 20 讲幻读是什么，幻读有什么问题
- 介绍了间隙锁和next-key lock的概念（间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间）
- 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
- 21 讲为什么我只改一行的语句，锁这么多
- next-key lock详细举例
- 可重复读的几个要点
- 22 讲MySQL有哪些“饮鸩止渴”提高性能的方法
- 在专栏的第2篇和第15篇文章中，我和你分析了，如果redo log和binlog是完整的，MySQL是如何保证crash-safe的。今天这篇文章，我着重和你介绍的是MySQL是“怎么保证redo log和binlog是完整的”。
- 23 讲MySQL是怎么保证数据不丢的
- 第2篇和第15篇文章中分析了，如果redo log和binlog是完整的，MySQL是如何保证crash-safe的
- 介绍的是MySQL是“怎么保证redo log和binlog是完整的”
- 24 讲MySQL是怎么保证主备一致的
- 介绍了MySQL不同格式binlog的优缺点，和设计者的思考
- 25 讲MySQL是怎么保证高可用的
- 讨论了几种会导致主备延迟的情况（主备机器性能差别、备库的读压力大、大事务（如一次性大量删除））
- 分析了可靠性优先和可用性优先策略的区别（用自增主键列的例子举例，证明主备切换可能导致数据错乱）
- 26 讲备库为什么会延迟好几个小时
- 介绍了MySQL的各种多线程复制策略
- 27 讲主库出问题了，从库怎么办
- 介绍了一主多从的主备切换流程
- 介绍了GTID的基本概念和用法
- 介绍了一主多从的主备切换流程
- 28 讲读写分离有哪些坑
- 两种架构 一主一备的双M架构 一主多从架构
- 29 讲如何判断一个数据库是不是出问题了
- 监控命令 select 1
- 优先考虑update系统表，然后再配合增加检测performance_schema的信息
- 30 讲答疑文章（二）：用动态的观点看加锁
- 31 讲误删数据后除了跑路，还能怎么办
- 32 讲为什么还有kill不掉的语句
- 33 讲我查这么多数据，会不会把数据库内存打爆
- 关键点，边读边发，不全进内存
- 34 讲到底可不可以使用join
- 35 讲join语句怎么优化
- 36 讲为什么临时表可以重名
- 37 讲什么时候会使用内部临时表
- 38 讲都说InnoDB好，那还要不要使用Memory引擎
- 39 讲自增主键为什么不是连续的
- 原因1 唯一键冲突，申请的自增值被丢弃
- 原因2 跳过默认自增，指定了数值
- 其他，mysql自身策略
- 40 讲insert语句的锁为什么这么多
- 41 讲怎么最快地复制一张表
- 42 讲grant之后要跟着flushprivileges吗
- 43 讲要不要使用分区表
- 44 讲答疑文章（三）：说一说这些好问题
- 45 直播回顾讲林晓斌：我的MySQL心路历程
https://zhuanlan.zhihu.com/p/379092178 mysql 一棵 B+ 树能存多少条数据？ （扇区、块、页）
https://www.jianshu.com/p/d1c42fe4c4ed 两阶段锁协议（两阶段加锁对性能的影响，下面两种不同的扣减库存的方案:）
MySQL5.7 原文档
8.2.1.3 Index Merge Optimization 对索引合并使用做了说明
https://dev.mysql.com/doc/refman/5.7/en/ 用chrome 右键翻译成中文
mariadb直到10.4版本才有Optimizer Trace， 之前的版本执行'SET optimizer_trace='enabled=on'; '会返回错误
https://mariadb.com/resources/blog/optimizer-trace-in-mariadb-server-10-4/
One of the new features in MariaDB Server 10.4 is the Optimizer Trace. It provides diagnostics about the optimizer: you can switch the tracing on, run a statement, and then examine the trace to see how the optimizer processed the statement and arrived at the query plan.
INSERT INTO innodb_lock_waits (wait_started, wait_age, wait_age_secs, locked_table, locked_index, locked_type, waiting_trx_id, waiting_trx_started, waiting_trx_age, waiting_trx_rows_locked, waiting_trx_rows_modified, waiting_pid, waiting_query, waiting_lock_id, waiting_lock_mode, blocking_trx_id, blocking_pid, blocking_query, blocking_lock_id, blocking_lock_mode, blocking_trx_started, blocking_trx_age, blocking_trx_rows_locked, blocking_trx_rows_modified, sql_kill_blocking_query, sql_kill_blocking_connection) VALUES('2022-09-09 10:53:02', '00:00:28', 28, 'my_batch_demo.t', 'PRIMARY', 'RECORD', '5535', '2022-09-09 10:53:02', '00:00:28', 1, 0, 29, 'insert into t values(8,8,8)', '5535:30:3:4', 'X,GAP', '5534', 28, '', '5534:30:3:4', 'X,GAP', '2022-09-09 10:52:34', '00:00:56', 1, 0, 'KILL QUERY 28', 'KILL 28');
其他常用
InnoDB Persistent Statistics innodb持续分析
重新计算的操作有：
1.重启
2.访问表
3.表中数据改变（1/16 以上的DML）
4.show table status 及 show index for table
5.analyze table
6.and so on
为了解决这个问题，在mysql 5.6 时，加入了持续优化统计，不再自动重新统计，持续统计数据是作为系统表存储在innodb_table_stats和innodb_index_stats中的，在上次的分享中也有提到过。
如何进行持续优化统计：
mysql>show variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
1、对于所有innodb表，可以设置全局参数
全局参数：
innodb_stats_persistent 是否开启统计
innodb_stats_auto_recalc 自动重新统计
innodb_stats_persistent_sample_pages 随机取样页数
innodb_stats_on_metadata 该参数主要为元数据索引统计分析，如查询information_schema中的某些表，还有show table status 也会造成innodb 随机提取数据，很容易导致查询性能大幅抖动，在5.6之后的版本该参数已经很鸡肋了，不开启完全不影响数据统计的准确性。
2、单表
(1) stats_persistent 对于innodb表是否保证持续统计
ALTER TABLE table_name stats_persistent=1
默认是由innodb_stats_persistent选项决定的
(2) stats_auto_recalc 对于innodb表是否自动计算持续统计
默认是由innodb_stats_auto_recalc 选项决定的，为1 时，
当有10%的数据发生改变时，就重新计算,按照我的测试大概超过10%
(3) stats_sample_pages 指定随机索引页的数量
show variables like 'transaction_isolation'; 查看事务隔离级别
/* 查询长事务（超过60秒） */
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
查询结果 横向转纵向
select * from t where id = 1 \G;
使用show table status 查看表信息 （含行数、表数据大小、索引大小等）
show table status from db_name like 'esf_seller_history'\G;
查询结果解释
- Name 表名称
- Engine 表的存储引擎
- Version 版本
- Row_format 行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。
- Rows 表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。
- Avg_row_length 平均每行包括的字节数
- Data_length 整个表的数据量(单位：字节)
- Max_data_length 表可以容纳的最大数据量
- Index_length 索引占用磁盘的空间大小
- Data_free 对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。
- Auto_increment 下一个Auto_increment的值
- Create_time 表的创建时间
- Update_time 表的最近更新时间
- Check_time 使用 check table 或myisamchk工具检查表的最近时间
- Collation 表的默认字符集和字符排序规则
- Checksum 如果启用，则对整个表的内容计算时的校验和
- Create_options 指表创建时的其他所有选项
- Comment 包含了其他额外信息，对于MyISAM引擎，包含了注释徐标新，如果表使用的是innodb引擎 ，将现实表的剩余空间。如果是一个视图，注释里面包含了VIEW字样。
查看表索引
show index from tm_test_key_len ;
重新组织索引
analyze table tm_test_key_len;
重建索引
查看SQL执行计划 optimizer_trace
explain select xxx;
进阶-查看优化器决策过程 Optimizer Trace
/* 打开optimizer_trace，只对本线程有效 /
SET optimizer_trace='enabled=on';
/ 执行语句 /
select * from tt order by xx limit 10;
/ 查看 OPTIMIZER_TRACE 输出 /
SELECT * FROM information_schema.OPTIMIZER_TRACE;
/ 关闭optimizer_trace，只对本线程有效 */
SET optimizer_trace='enabled=off';
查看innodb配置
mysql>show variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name | Value |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
+--------------------------------------+-------------+
1、对于所有innodb表，可以设置全局参数
全局参数：
innodb_stats_persistent 是否开启统计
innodb_stats_auto_recalc 自动重新统计
innodb_stats_persistent_sample_pages 随机取样页数
innodb_stats_on_metadata 该参数主要为元数据索引统计分析，
MySQL的优化器是通过innodb收集到的数据来选择最优的执行计划，但因为这些数据会随着某些操作而重新计算，造成执行计划会多次变化，出现不精确和不稳定的问题。
这些导致重新计算的操作有：
1.重启
2.访问表
3.表中数据改变（1/16 以上的DML）
4.show table status 及 show index for table
5.analyze table
6.其他
为了解决这个问题，在mysql 5.6 时，加入了持续优化统计，不再自动重新统计，持续统计数据是作为系统表存储在innodb_table_stats和innodb_index_stats中的，
动态信息查看（排查锁表、长查询等问题）
show processlist 查看语句进程
sys.schema_table_lock_waits 表的锁等待信息（MySQL启动时需要设置performance_schema=on，相比于设置为off会有10%左右的性能损失)
select * from sys.schema_table_lock_waits
select * from information_schema.processlist
innodb行锁等待情况
select * from t sys.innodb_lock_waits
查询慢 看慢查询日志
# Time: 2022-09-08T09:00:48.011620Z
# User@Host: root[root] @ ipxxxxx Id: 23
# Query_time: 4.315941 Lock_time: 0.000146 Rows_sent: 8785 Rows_examined: 1500000
use my_batch_demo;
SET timestamp=1662627648;
/* ApplicationName=DBeaver 7.0.5 - SQLEditor <local本地验证.sql> */ SELECT * from TM_ACCOUNT where LAST_MODIFY_DT >= '2100-09-11' and org_first_id > 1 and org_second_id = 20 limit 10000000;
MySQL Explain详解
在日常工作中，我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
-- 实际SQL，查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明
下面对这些字段出现的可能进行解释：
一、 id
SELECT识别符。这是SELECT的查询序列号
我的理解是SQL执行的顺序的标识，SQL从大到小的执行
-
id相同时，执行顺序由上至下
-
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
-
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行
-- 查看在研发部并且名字以Jef开头的员工，经典查询
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研发部';
二、select_type
示查询中每个select子句的类型
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
三、table
显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
四、type
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index: Full Index Scan，index与ALL区别为index类型只遍历索引树
range:只检索给定范围的行，使用一个索引来选择行
ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
五、possible_keys
指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
六、Key
key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好
八、ref
列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
九、rows
估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
十、Extra
该列包含MySQL解决查询的详细信息,有以下几种情况：
Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
-- 测试Extra的filesort
explain select * from emp order by name;
Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
No tables used：Query语句中使用from dual 或不含任何from子句
-- explain select now() from dual;
总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。
通过收集统计信息不可能存在结果
标签：stats,笔记,查询,索引,innodb,MySQL,MySQL45,table From： https://www.cnblogs.com/starmoon1994/p/16881194.html