Mysql那些事

高性能Mysql读书笔记

索引基础

B-Tree索引（实现方式可能是B+Tree）

顺序存储 & 到根结点距离相同

数据存储在叶子节点，叶子节点之间有顺序链接的指针，方便范围查找

联合索引结构图

可以使用索引的查询类型

1. 全匹配
2. 最左前缀
3. 列前缀，即最左列的前缀匹配，like ‘xxx%’
4. 范围匹配
5. 精确匹配前面的列，范围匹配后面的列
6. 覆盖索引

hash索引

结构类似hashMap
value是指向行的指针

hash索引的限制

1. 无法用于排序
2. 不支持部分列匹配（因为是对所有列一起计算的hash）
3. 只支持等值比较查询，包括=, IN(), <=>(支持null的等于)，即不支持范围查询
4. hash冲突多时，会遍历指针，查询和修改效率下降

三星系统

1. 一星：将相关记录放到一起
2. 二星：索引中的数据顺序和查找中的排列顺序一致
3. 三星：索引中的列包含了查询的全部列

高性能索引

独立的列

索引列不能是表达式的一部分，否则不能使用索引

mysql > SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;// 不能使用索引

长字符列索引

1. 选择字符列的前一部分进行计算hash索引
2. Mysql不允许索引BLOB，TEXT或很长的VARCHAR的列，这种类型的列必须使用前缀索引
3. 前缀索引无法在order by 和 group by中使用

多列索引

最好建立多列的联合索引而不是多个列的索引

选择合适的索引列顺序

将选择性高的列放在前面，前面的条件应该能筛掉更多的无用数据

聚簇索引

数据行存在叶子结点的叫聚簇索引，并且相邻键值的数据行相邻。
InnoDB的主键列为聚簇索引，如果没有选择逐渐则选择一个唯一非空索引代替，如果没有唯一非空索引，会创建一个隐式主键来代替
叶子结点中还包含会滚指针和事物ID
主键和普通索引的区别，主键不支持null列

优点

1. 减少IO（相关数据在磁盘的一个数据页）
2. 访问快（索引和数据在一起）
3. 覆盖索引时直接用叶子节点的主键值

缺点

1. 如果数据加载完毕，没有优势
2. 影响插入速度
3. 页分裂
4. 稀疏或者页分裂严重时会全表扫描
5. 二级索引更大
6. 查询有回表

uuid做聚簇索引的缺点

1. 由于不连续可能要插入的页不在缓存中，导致大量随机IO
2. 由于不连续会导致大量的页分裂
3. 由于频繁的页分裂导致数据稀疏

顺序主键的缺点

1. 主键自增可能成为热点数据，导致间隙锁竞争
2. 自增主键变为热点数据AUTO_INCREMENT锁机制

覆盖索引

包含了所有要查询数据的索引
MYSQL只能使用B-Tree做覆盖索引
所有二级索引隐式的包含了主键，如果只查询主键和二级索引的列，可以走覆盖索引
Extra为Using index时表示使用了覆盖索引

使用索引做排序

type为index表示使用索引做排序

• 只有索引顺序和order by顺序一致时才能走索引排序，并且排序方向要一致
• 如果查询关联多张表则只有order by子句引用的字段全部为第一个表时才能走索引

冗余和重复索引

重复索引

相同列上创建了同一类型的索引（B-Tree或者hash）
比如，同一列上创建了主键索引、唯一索引和索引

冗余索引

创建了（A，B）索引，又创建了A索引，就叫冗余索引

SQL A
mysql > SELECT COUNT(*) FROM userinfo WHERE state_id=5;
SQL B
mysql > SELECT state_id, city, addreess FROM userinfo WHERE state_id=5;
索引情况A
state_id
索引情况B
(state_id, city, address)

当索引从A变为B时
SQL A性能下降，SQL B性能提升。这是因为原本只需要找到state_id对应的叶子结点数目（相邻）。现在需要找到多个叶子结点

• 从上面可以看出冗余索引有时是有必要的
• 需要注意隐式的ID索引，比如索引（A）相当于（A，ID）。修改索引（A）为（A，B）意味着变为（A，B，ID）对原来的SQL

WHERE A = 5 ORDER BY ID

有影响

索引和锁

WHERE id < 5 AND id <> 1

上面sql会获取1-4的排他锁

优化排序

延迟关联

SELECT <cols> FROM profiles INNER JOIN (
        SELECT <primary key cols> FROM profiles
        WHERE x.sex='M' OEDER BY rating LIMIT 100000, 10
) AS x USING(<primary key cols> )
先通过覆盖索引查到所有主键，再一起回表
否则会查到id之后逐个回表

MySQL技术内幕读书笔记

Cardinality

用于表示索引中不重复记录数量的预估值

SHOW INDEX FROM table

insert buffer（是物理页）

对非唯一索引的插入具有随机性，会降低性能，引入insert buffer，先插入到insert buffer在定时刷
如果数据插入量大恢复的时候会耗时很长

double write（from）

背景

页断裂
即在Mysql写入磁盘时没有将整个页刷新到磁盘上导致的数据不一致
DB block > OS block >= IO block > 磁盘 sector，而且他们之间保持了整数倍的关系。
由于任何DB page的写入，最终都会转为sector的写入，如果在写磁盘的过程中，出现异常重启，就可能会发生一个DB页只写了部分sector到磁盘，进而出现页断裂的情况。

主流数据异常恢复流程
第一阶段重做redo日志，恢复数据页和undo页到异常crash时的状态；
第二阶段，根据undo页的内容，回滚没有提交事务的修改。
通过两个阶段保证了数据库的一致性。

发生页断裂后，对于利用纯物理日志实现redo的数据库不受影响，因为每一条redo日志完全不依赖物理页的状态，并且是幂等的(执行一次与N次，结果是一样的)。另外要说明一点，redo日志的页大小一般设计为512个字节，因此redo日志页本身不会发生页断裂。而逻辑物理日志则不行，比如修改页头信息，页内记录数加1，slot信息修改等都依赖于页面处于一个一致状态，否则就无法正确重做redo。而mysql正是采用这种日志类型，所以发生页面断裂时，异常恢复就会出现问题，需要借助于double write技术来辅助处理。

技术介绍

The doublewrite buffer is a storage area where InnoDB writes pages flushed from the buffer pool before writing the pages to their proper positions in the InnoDB data files. If there is an operating system, storage subsystem, or unexpected mysqld process exit in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
Although data is written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the doublewrite buffer in a large sequential chunk, with a single fsync() call to the operating system (except in the case that innodb_flush_method is set to O_DIRECT_NO_FSYNC).
Prior to MySQL 8.0.20, the doublewrite buffer storage area is located in the InnoDB system tablespace. As of MySQL 8.0.20, the doublewrite buffer storage area is located in doublewrite files.
摘自mysql官方文档

简单翻一下，
doublewrite buffer是一块存储区域，当InnoDB把数据写入到合适位置之前会先写入这个区域。如果在写入页过程中出现操作系统异常、存储子系统异常、mysql进程异常，在崩溃恢复过程中
doublewrite buffer可以提供一个完整数据copy
虽然doublewrite buffer 是写两次，但是从IO负载和IO操作上看，并没有耗费双倍的性能。写入是调用一次 fsync() 写入到一个连续的块中（顺序写，这也是kafka快的原因）
在MySQL 8.0.20,之前，存放在系统表空间，之后存放在doublewrite文件中。

自增长与锁

nnodb_autoinc_lock_mode	说明
0	AUTO-INC Locking
1	知道行数的用内存互斥量，不知道的批量的插入用AUTO-INC Locking，可以用sbr
2	全内存互斥量，会出现主键不连续 & 不能用sbr

事务的实现

redo log 保证原子性和持久性
undo log 保证一致性
锁 & MVCC保证隔离性

redo && undo && binlog

binlog
在服务层记录数据的变化，是基于事件记录的。只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。

（binlog格式）sbr(statement based replication) vs rbr(row based replication)

https://dev.mysql.com/doc/refman/8.0/en/replication-sbr-rbr.html

sbr

传输的是SQL statement无法应对动态值

rbr

传输的是行的改变（When using row-based logging, the source writes events to the binary log that indicate how individual table rows are changed.）

redo
物理日志记录页的物理修改操作，用于事务数据落盘前掉电的恢复，连续写
undo
逻辑日志，根据每行记录进行记录，用于事务回滚

面试题补充

为什么选择B+树，不选择红黑树

B+树的出度为N，红黑树为2，意味着，B+树的高度要小于红黑树，在搜索时候，每次查找树的节点意味着一次随机磁盘访问，随机磁盘访问由于机器的原因速度很慢。
PS.B+树叶非叶子节点不存储数据，可以增加存储节点的数量，也有利于降低随机磁盘访问的次数

一致性hash

一致性hash
简单来说就是算一个hash值，这个hash值会在环上分布，设置的服务节点也在环上分布，每个请求由顺时针离自己最近的服务节点服务

分布式ID生成算法

雪花算法
0 - 41位时间戳 - 5位数据中心标识 - 5位机器标识 - 12位序列号

查询唯一索引列的时候会锁降级

即从Gap lock降级为Record Lock

Explain相关

Explain、Explain Extended、Explain Partitions

• EXPLAIN EXTENDED相比EXPLAIN命令，会额外显示一个filtered字段。这个字段会指示出表的条件所过滤的表中行数的百分比。除此之外，在执行EXPLAIN EXTENDED命令之后，使用SHOW WARNINGS语句可以查看额外的语句信息。
• 可以显示查询使用的分区

Explain的一些限制

1. 不会告诉触发器，存储过程和UDF的影响
2. 不支持存储过程
3. 不会反应MySQL的特定优化
4. 不会反应所有信息
5. 不区分相同名字的事物，内存排序和临时文件排序都是filesort，磁盘和内存的临时表都是using temporary
6. 早期不考虑limit
7. 5.6之前，explain 后面的语句，有如果包含自查询，会将子查询查询出来并放入临时表
8. 重新非SELECT查询时，要注意防止使用覆盖索引，因为更新不会走覆盖索引

Explain中的列

id	SELECT识别符。这是SELECT的查询序列号
select_type	SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)
table	输出的行所引用的表
type	联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
possible_keys	指出MySQL能使用哪个索引在该表中找到行
key	显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
key_len	显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。
ref	显示使用哪个列或常数与key一起从表中选择行。
rows	显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。
filtered	显示了通过条件过滤出的行数的百分比估计值。
Extra	该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。 Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

filesort 优化

1. 加大max_length_for_sort_data参数的设置
   在MySQL中，决定使用老的双路排序算法还是改进版单路排序算法是通过参数max_length_for_ sort_data来决定的。当所有返回字段的最大长度小于这个参数值时，MySQL就会选择改进后的单路排序算法，反之，则选择老式的双路排序算法。所以，如果有充足的内存让MySQL存放需要返回的非排序字段，就可以加大这个参数的值来让MySQL选择使用改进版的排序算法。
2. 去掉不必要的返回字段或列长度尽量小一些
   对于内存不是非常充裕的情况，不能强行增大配置项max_length_for_sort_data，否则可能会造成MySQL不得不将数据分成很多段，然后进行排序，这样可能会得不偿失。此时可以选择去掉不必要的返回字段或者将列长度尽可能设置小一些，让返回结果长度适应max_length_for_sort_data参数的限制。
3. 增大sort_buffer_size参数设置
   增大sort_buffer_size并不是为了让MySQL选择改进版的单路排序算法，而是为了让MySQL尽量减少在排序过程中对需要排序的数据进行分段，因为分段会造成MySQL不得不使用临时表来进行交换排序。
4. 增加read_rnd_buffer_size大小，可以一次性多读到内存中
   该变量可以被任何存储引擎使用，当从一个已经排序的键值表中读取行时，会先从该缓冲区中获取而不再从磁盘上获取。默认为256K。
5. 改变tmpdir，使其指向多个物理盘（不是分区）的目录。