mysql性能调优

定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中
获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个
查询结果只通过辅助索引就能拿到结果，不需要回表。即通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

如果查询的结果集在主键索引(聚簇索引)和辅助索引中都有，优先走二级索引，因为辅助索引通常更小

MySQL 从你输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，比如关键字错误或缺少等

"table_scan": {     --全表扫描情况
                    "rows": 10123,    --扫描行数
                    "cost": 2054.7    --查询成本
              }

"range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "a < name"      --索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,    --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,       --是否使用覆盖索引
                        "rows": 5061,              --索引扫描行数
                        "cost": 6074.2,            --索引使用成本
                        "chosen": false,           --是否选择该索引
                        "cause": "cost"
                      }
                    ]

"considered_access_paths": [   --最终选择的访问路径
                    {
                      "rows_to_scan": 10123,
                      "access_type": "scan",     --访问类型：为scan，全表扫描
                      "resulting_rows": 10123,
                      "cost": 2052.6,
                      "chosen": true,            --确定选择
                      "use_tmp_table": true
                    }
                  ]

mysql> EXPLAIN select * from employees limit 90000,5;
优化：mysql> EXPLAIN select * from employees where id > 90000 limit 5;

mysql> EXPLAIN select * from employees ORDER BY name limit 90000,5;
优化：mysql> EXPLAIN select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

一次一行循环地从驱动表中读取行，在这行数据中取到关联字段，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表的结果合集。

mysql>EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ

适合被驱动表关联字段有索引的情况

把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比

mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法

适合被驱动表关联字段没有索引的情况

join操作时尽量选择NLJ算法，驱动表过滤的条件也尽量要走索引，避免全表扫描

select * from t2 straight_join t1 on t2.a = t1.a;  指定mysql选 t2 表作为驱动表

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤完成之后，数据量小的那个表，就是“小表”

可重复读的隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）

串行模式下innodb的查询也会被加上行锁

如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁

用版本对比来实现(如果更新数据时，发现数据的版本不一致，就不更新重新获取数据)

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景

sql语句

锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)

读锁会阻塞写，但是不会阻塞读

写锁则会把读和写都阻塞

mysql自己加的锁，用于提高加锁的效率

Dynamic 和 Compressed 行格式和Compact 行格式挺像，只不过在处理行溢出数据时有所不同

Compressed 行格式和 Dynamic 不同的一点是，Compressed 行格式会采用压缩算法对页面进行压缩，以节省空间

变长字段长度列表

NULL值列表

记录头信息

自定义的列的值，不为null的时候，记录的真实数据就存在这里

隐藏列

实际存储

一个 VARCHAR(M)类型的列就最多可以存储 65532 个字节，1页的大小位16KB，也就是16384字节

定义：如果我们定义一个表，表中只有一个VARCHAR(60000)字段，然后让字段中插入60000个字符，一页就放不了一条记录，这就叫数据溢出

如何处理数据溢出

1.File Header：38个字节，文件头部，页的一些通用信息

2.Page Header：56个字节，页面头部，数据页(index页)专有的信息

3.Infimum+supremun：26个字节，最小记录和最大记录，两个虚拟行记录

4.User records：大小不确定(看存储的数据)，用户记录，实际存储的行记录内容

5.Free space：大小不确定，空间空间，页中尚未使用的剩余空间

6.Page directory：大小不确定，页面目录，页中某些记录对应的位置，地址值

7. File Trailer：8个字节，文件尾部，校验当前页是否完整

问题

优点

type：该条 redo 日志的类型，redo 日志设计大约有 53 种不同的类型日志

space ID：表空间 ID

page number：页号

data：该条 redo 日志的具体内容

每用一次自增1，每当变量为256倍数时，将值更新到系统表空间的 Max Row ID 属性中

当系统启动时，会将系统表空间的 Max Row ID 属性加载到内存中，将该值加上256 之后赋值给全局变量

针对某一棵 B+树来说，既可能更新叶子节点页面，也可能更新非叶子节点页面，也可能创建新的页面(叶子节点分裂、非叶子节点添加索引)

File Header、Page Header、Page Directory等等也会更新

方法1：在每个修改的地方都记录一条 redo 日志。缺点：被修改的地方太多，redo日志太多

方法2：将整个页面的第一个被修改的字节到最后一个修改的字节之间所有的数据当成是一条物理 redo 日志中的具体数据。缺点：会将很多没有修改的数据页加入到redo日志中，很浪费

方法3：增加很多种redo日志类型来记录

既包含物理层面的意思，也包含逻辑层面的意思

物理层面看，这些日志都指明了对哪个表空间的哪个页进行了修改

逻辑层面看，在系统崩溃重启时，需要调用一些事先准备好的函数，执行完这些函数后才可以将页面恢复成系统崩溃前的样子

一个 redo 日志类型而只是把在本页面中变动（比如插入、修改）一条记录所有必备的要素记了下来，之后系统崩溃重启时，服务器会调用相关向某个页面变动（比如插入、修改）一条记录的那个函数，而 redo 日志中的那些数据就可以被当成是调用这个函数所需的参数，在调用完该函数后，页面中的相关值也就都被恢复到系统崩溃前的样子了。这就是所谓的逻辑日志的意思