MySQL45讲笔记

MySQL45

https://funnylog.gitee.io/mysql45/

原系列目录：

01 讲基础架构：一条SQL查询语句是如何执行的

主要介绍
MySQL分为Server层和存储引擎层两部分:

  • Server层包括连接器、查询缓存、分析器、优化器、执行器等
  • 存储引擎层负责数据的存储和提取。

00 开篇词讲这一次，让我们一起来搞懂MySQL

  1. 01 讲基础架构：一条SQL查询语句是如何执行的
    1. 主要讲组件分层，每个组件的大致作用（连接器、查询缓存、分析器、优化器、执行器等）
  2. 02 讲日志系统：一条SQL更新语句是如何执行的
    1. 结合组件，引申到binglog和redolog的配合使用，以及两阶段提交
  3. 03 讲事务隔离：为什么你改了我还看不见
    1. 事务隔离级别
    2. 介绍了【可重复读】的实现，MVVC等
    3. 事务启动方式
  4. 04 讲深入浅出索引(上)
    1. 主键索引和普通索引的数据结构推演，不同数据结构的优劣
      1. 符合磁盘读写特性，减少IO次数
    2. 长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
  5. 05 讲深入浅出索引(下)
    1. 索引深入概念，覆盖索引、前缀索引、索引下推
    2. 索引重建的优化作用：删除记录后，索引不一定删除。（索引文件大小有可能大于表文件） alter table T engine=InnoDB
  6. 06 讲全局锁和表锁：给表加个字段怎么有这么多阻碍
    1. 引入数据库备份的例子，先备份余额表还是课程表
    2. 表级锁，MDL机制，举例ddl加字段和同时读取场景
  7. 07 讲行锁功过：怎么减少行锁对性能的影响
    1. MySQL的行锁，涉及了两阶段锁协议、死锁和死锁检测这两大部分内容。
    2. 以电影院售票的例子，针对[这种热点行更新导致的性能问题]给出3个解决方案：1、临时关闭死锁检测 2、控制并发度 3、拆分热点行和业务逻辑兼容
  8. 08 讲事务到底是隔离的还是不隔离的
  9. 09 讲普通索引和唯一索引，应该怎么选择
    1. 查询过程，性能几乎无差别（由于引擎是按页读取，普通索引多做一次内存判断，损耗极小）
    2. redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗
    3. 唯一索引的缺点：大量插入数据慢、内存命中率低
  10. 10 讲MySQL为什么有时候会选错索引
    1. InnoDB Persistent Statistics https://blog.csdn.net/cuiwangxie1183/article/details/100483099
    2. 索引统计的更新机制，并提到了优化器存在选错索引的可能性。
  11. 11 讲怎么给字符串字段加索引
  12. 12 讲为什么我的MySQL会“抖”一下
    1. 用孔乙己酒馆粉板和账本的例子，解释了redolog、磁盘以及何时flush，并说明了脏页、干净页
    2. 重温redolog上的checkpoint、write-pos机制
    3. InnoDB刷脏页的控制策略
    4. 引申再讲了redo-log在ACID特性中D的支持
  13. 13 讲为什么表数据删掉一半，表文件大小不变
    1. 数据删除流程，每个page删除后，会留下数据空洞，
    2. alter table A engine=InnoDB命令重建表，原理
    3. 其他重建表方式的比较 [analyze table t] [optimize table t 等于recreate+analyze]
  14. 14 讲count这么慢，我该怎么办
    1. 罗列各种count方案的优劣，缓存方案、table信息方案
    2. 提出汇总表的方案
    3. 按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)
  15. 15 讲答疑文章（一）：日志和索引相关问题
    1. 重新讲了两阶段提交中，redo-log和binlog操作时，系统崩溃的恢复过程。
  16. 16 讲“orderby”是怎么工作的
  17. 17 讲如何正确地显示随机消息
    1. 介绍了MySQL对临时表排序的执行过程， 内存临时表(Using temporary)和磁盘临时表(Using filesort)之间的区别
  18. 18 讲为什么这些SQL语句逻辑相同，性能却差异巨大
    1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
    2. 隐式类型转换、连表时字符集未对齐，都可能导致放弃走索引
  19. 19 讲为什么我只查一行的语句，也执行这么慢
    1. 等MDL锁、等flush、等行锁
  20. 20 讲幻读是什么，幻读有什么问题
    1. 介绍了间隙锁和next-key lock的概念（间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间）
    2. 跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。
  21. 21 讲为什么我只改一行的语句，锁这么多
    1. next-key lock详细举例
    2. 可重复读的几个要点
  22. 22 讲MySQL有哪些“饮鸩止渴”提高性能的方法
    1. 在专栏的第2篇和第15篇文章中，我和你分析了，如果redo log和binlog是完整的，MySQL是如何保证crash-safe的。今天这篇文章，我着重和你介绍的是MySQL是“怎么保证redo log和binlog是完整的”。
  23. 23 讲MySQL是怎么保证数据不丢的
    1. 第2篇和第15篇文章中分析了，如果redo log和binlog是完整的，MySQL是如何保证crash-safe的
    2. 介绍的是MySQL是“怎么保证redo log和binlog是完整的”
  24. 24 讲MySQL是怎么保证主备一致的
    1. 介绍了MySQL不同格式binlog的优缺点，和设计者的思考
  25. 25 讲MySQL是怎么保证高可用的
    1. 讨论了几种会导致主备延迟的情况（主备机器性能差别、备库的读压力大、大事务（如一次性大量删除））
    2. 分析了可靠性优先和可用性优先策略的区别（用自增主键列的例子举例，证明主备切换可能导致数据错乱）
  26. 26 讲备库为什么会延迟好几个小时
    1. 介绍了MySQL的各种多线程复制策略
  27. 27 讲主库出问题了，从库怎么办
    1. 介绍了一主多从的主备切换流程
      1. 介绍了GTID的基本概念和用法
  28. 28 讲读写分离有哪些坑
    1. 两种架构 一主一备的双M架构 一主多从架构
  29. 29 讲如何判断一个数据库是不是出问题了
    1. 监控命令 select 1
    2. 优先考虑update系统表，然后再配合增加检测performance_schema的信息
  30. 30 讲答疑文章（二）：用动态的观点看加锁
  31. 31 讲误删数据后除了跑路，还能怎么办
  32. 32 讲为什么还有kill不掉的语句
  33. 33 讲我查这么多数据，会不会把数据库内存打爆
    1. 关键点，边读边发，不全进内存
  34. 34 讲到底可不可以使用join
  35. 35 讲join语句怎么优化
  36. 36 讲为什么临时表可以重名
  37. 37 讲什么时候会使用内部临时表
  38. 38 讲都说InnoDB好，那还要不要使用Memory引擎
  39. 39 讲自增主键为什么不是连续的
    1. 原因1 唯一键冲突，申请的自增值被丢弃
    2. 原因2 跳过默认自增，指定了数值
    3. 其他，mysql自身策略
  40. 40 讲insert语句的锁为什么这么多
  41. 41 讲怎么最快地复制一张表
  42. 42 讲grant之后要跟着flushprivileges吗
  43. 43 讲要不要使用分区表
  44. 44 讲答疑文章（三）：说一说这些好问题
  45. 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;

查询结果解释

  1. Name 表名称
  2. Engine 表的存储引擎
  3. Version 版本
  4. Row_format 行格式。对于MyISAM引擎，这可能是Dynamic，Fixed或Compressed。动态行的行长度可变，例如Varchar或Blob类型字段。固定行是指行长度不变，例如Char和Integer类型字段。
  5. Rows 表中的行数。对于非事务性表，这个值是精确的，对于事务性引擎，这个值通常是估算的。
  6. Avg_row_length 平均每行包括的字节数
  7. Data_length 整个表的数据量(单位：字节)
  8. Max_data_length 表可以容纳的最大数据量
  9. Index_length 索引占用磁盘的空间大小
  10. Data_free 对于MyISAM引擎，标识已分配，但现在未使用的空间，并且包含了已被删除行的空间。
  11. Auto_increment 下一个Auto_increment的值
  12. Create_time 表的创建时间
  13. Update_time 表的最近更新时间
  14. Check_time 使用 check table 或myisamchk工具检查表的最近时间
  15. Collation 表的默认字符集和字符排序规则
  16. Checksum 如果启用，则对整个表的内容计算时的校验和
  17. Create_options 指表创建时的其他所有选项
  18. 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从大到小的执行

  1. id相同时，执行顺序由上至下

  2. 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

  3. 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后查看执行计划。

通过收集统计信息不可能存在结果

posted @ 2022-11-21 11:56  starmoon1900  阅读(539)  评论(0)  编辑  收藏  举报