高性能Mysql读书笔记
索引基础
B-Tree索引(实现方式可能是B+Tree)
顺序存储 & 到根结点距离相同
数据存储在叶子节点,叶子节点之间有顺序链接的指针,方便范围查找
联合索引结构图
可以使用索引的查询类型
- 全匹配
- 最左前缀
- 列前缀,即最左列的前缀匹配,like ‘xxx%’
- 范围匹配
- 精确匹配前面的列,范围匹配后面的列
- 覆盖索引
hash索引
结构类似hashMap
value是指向行的指针
hash索引的限制
- 无法用于排序
- 不支持部分列匹配(因为是对所有列一起计算的hash)
- 只支持等值比较查询,包括=, IN(), <=>(支持null的等于),即不支持范围查询
- hash冲突多时,会遍历指针,查询和修改效率下降
三星系统
- 一星:将相关记录放到一起
- 二星:索引中的数据顺序和查找中的排列顺序一致
- 三星:索引中的列包含了查询的全部列
高性能索引
独立的列
索引列不能是表达式的一部分,否则不能使用索引
mysql > SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;// 不能使用索引
长字符列索引
- 选择字符列的前一部分进行计算hash索引
- Mysql不允许索引BLOB,TEXT或很长的VARCHAR的列,这种类型的列必须使用前缀索引
- 前缀索引无法在order by 和 group by中使用
多列索引
最好建立多列的联合索引而不是多个列的索引
选择合适的索引列顺序
将选择性高的列放在前面,前面的条件应该能筛掉更多的无用数据
聚簇索引
数据行存在叶子结点的叫聚簇索引,并且相邻键值的数据行相邻。
InnoDB的主键列为聚簇索引,如果没有选择逐渐则选择一个唯一非空索引代替,如果没有唯一非空索引,会创建一个隐式主键来代替
叶子结点中还包含会滚指针和事物ID
主键和普通索引的区别,主键不支持null列
优点
- 减少IO(相关数据在磁盘的一个数据页)
- 访问快(索引和数据在一起)
- 覆盖索引时直接用叶子节点的主键值
缺点
- 如果数据加载完毕,没有优势
- 影响插入速度
- 页分裂
- 稀疏或者页分裂严重时会全表扫描
- 二级索引更大
- 查询有回表
uuid做聚簇索引的缺点
- 由于不连续可能要插入的页不在缓存中,导致大量随机IO
- 由于不连续会导致大量的页分裂
- 由于频繁的页分裂导致数据稀疏
顺序主键的缺点
- 主键自增可能成为热点数据,导致间隙锁竞争
- 自增主键变为热点数据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的一些限制
- 不会告诉触发器,存储过程和UDF的影响
- 不支持存储过程
- 不会反应MySQL的特定优化
- 不会反应所有信息
- 不区分相同名字的事物,内存排序和临时文件排序都是filesort,磁盘和内存的临时表都是using temporary
- 早期不考虑limit
- 5.6之前,explain 后面的语句,有如果包含自查询,会将子查询查询出来并放入临时表
- 重新非SELECT查询时,要注意防止使用覆盖索引,因为更新不会走覆盖索引
Explain中的列
id | SELECT识别符。这是SELECT的查询序列号 |
select_type | SELECT类型,可以为以下任何一种:
|
table | 输出的行所引用的表 |
type | 联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:
|
possible_keys | 指出MySQL能使用哪个索引在该表中找到行 |
key | 显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 |
key_len | 显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。 |
ref | 显示使用哪个列或常数与key一起从表中选择行。 |
rows | 显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 |
filtered | 显示了通过条件过滤出的行数的百分比估计值。 |
Extra | 该列包含MySQL解决查询的详细信息
|
filesort 优化
- 加大max_length_for_sort_data参数的设置
在MySQL中,决定使用老的双路排序算法还是改进版单路排序算法是通过参数max_length_for_ sort_data来决定的。当所有返回字段的最大长度小于这个参数值时,MySQL就会选择改进后的单路排序算法,反之,则选择老式的双路排序算法。所以,如果有充足的内存让MySQL存放需要返回的非排序字段,就可以加大这个参数的值来让MySQL选择使用改进版的排序算法。 - 去掉不必要的返回字段或列长度尽量小一些
对于内存不是非常充裕的情况,不能强行增大配置项max_length_for_sort_data,否则可能会造成MySQL不得不将数据分成很多段,然后进行排序,这样可能会得不偿失。此时可以选择去掉不必要的返回字段或者将列长度尽可能设置小一些,让返回结果长度适应max_length_for_sort_data参数的限制。 - 增大sort_buffer_size参数设置
增大sort_buffer_size并不是为了让MySQL选择改进版的单路排序算法,而是为了让MySQL尽量减少在排序过程中对需要排序的数据进行分段,因为分段会造成MySQL不得不使用临时表来进行交换排序。 - 增加read_rnd_buffer_size大小,可以一次性多读到内存中
该变量可以被任何存储引擎使用,当从一个已经排序的键值表中读取行时,会先从该缓冲区中获取而不再从磁盘上获取。默认为256K。 - 改变tmpdir,使其指向多个物理盘(不是分区)的目录。