MYSQL优化笔记

mysql8去掉了缓存，因为命中率太低了

group by

“Using filesort”，代表查询中有排序操作;

mysql5.7 group by 存在索引隐式排序，输出结果是排好序的

image-20210913164350650

mysql8 去掉了索引隐式排序，输出结果不是排好序的

image-20210913164524289

preview

开启慢sql日志

in是否走索引

Mysql中in到底走不走索引？

in通常是走索引的，当in后面的数据在数据表中超过30%（上面的例子的匹配数据大约6000/16000 = 37.5%）的匹配时，会走全表扫描，即不走索引，因此in走不走索引和后面的数据有关系。

复合索引

1
2
3
abc组成复合索引
select * from myTest where a=3 and b>7 and c=3;   b范围值，断点，阻塞了c的索引
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

关于Filtered思考

猜一下下面两个执行计划，哪个执行的速度快

image-20210915105648825

image-20210915105716719

sql执行过程

image-20210917124742511

image-20210917131933892

image-20210917132055995

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

需求：检索出表中“名字第一个字是张，而且年龄是10岁的所有男孩”;

1
select * from tuser where name like '张%' and age=10 and ismale=1;

  • 无索引下推的执行过程

    img

  • 有索引下推的执行过程

    img

普通索引与唯一索引

  • 针对查询来说，普通索引与唯一索引的性能差距微乎其微，可以忽略不计

  • 对于更新操作来说，如果要更新的数据不在内存中

    • 普通索引的操作是直接更新记录在change buffer中，语句执行结束
    • 唯一索引的操作是==需要将数据页读入到内存(所以说唯一索引是用不到change buffer的)，判断是否有冲突==，插入值，语句执行结束

    假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用

常用命令

1
2
3
4
5
6
7
8
# 查询事务隔离级别
show variables like 'transaction_isolation';
# 查询binlog文件列表
show binary logs;
# 查询当前正在使用的binlog文件
show master status;
# 查询binlog文件内容
show binlog events in 'binlog_name';

主从同步

image-20210920103933336

主从架构

image-20210920133348526

一主一从

从节点做热备，当主节点挂掉后，从节点升级为新的主节点；==一主一从并不是为了提高性能，而是为了保证服务的高可用==

一主多从

通常一个主节点，2到4个从节点，过多的从节点会给数据同步带来压力，导致性能下降；

如果是4个从节点，3个从节点作为数据同步，1个从节点专门用来处理耗时较长的查询或者专门用于开发人员处理线上问题

MYISAM

.myi 存储索引

.myd 存储数据

.frm 存储表结构数据

MYISAM数据与索引分开存储，查询的时候先根据索引查询数据文件地址，再根据地址去获取相应数据

INNODB

.ibd 存储索引和数据

.frm 存储表结构数据

主从复制

image-20210920225014283

==主从复制的过程中，最耗时的操作就是SQL Thread向数据库中写的过程，因为是随机IO；其他的过程都是顺序IO；为了解决随机IO的问题，mysql使用的是MTS，并制定了规则来保证数据的原子性==

MTS(multi-thread slave)

规则：

  1. 更新同一行的多个事务，必须要分发到同一个worker中执行
  2. 同一个事务不能被拆开执行，必须放在同一个worker中执行
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 从库SQL线程并行执行的粒度
show variables like '%paralle%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| innodb_parallel_read_threads | 4        |
| slave_parallel_type          | DATABASE |
| slave_parallel_workers       | 0        |
+------------------------------+----------+

# 设置线程个数
set global slave_parallel_workers =4;

# 设置并行执行粒度，database：库级别或者logical_check：行级别
set global slave_parallel_type='logical_check';

# 开启slave
start slave
# 查看线程数
show full processlist;
  • GTID 全局事务id

组提交

组提交（group commit）是mysql处理日志的一种优化方式，主要为了解决写日志时频繁刷磁盘的问题。目前已经支持bin log和redo log 的组提交

RC解决脏读问题，是当前读

RR解决不可重复读，是快照读

  • 当前读：读取的是最新数据
  • 快照读：读取的是历史数据

MVCC

  • readview：当进行快照读的时候会生成一个事务id的列表，来保存不同的信息，通过这些信息来做可见性判断
    • list: 生成readview的时候活跃的id
    • up_limit_id: 当前活跃id的最小值
    • lower_limit_id：尚未分配的下一个事务id

RC：每次快照读的时候生成新的read view

RR：第一次生成快照读的时候生成read view，后续不会变image-20210921003456750

历史版本保存在undolog中

Innodb存储引擎每次在进行数据插入的时候，数据必须要跟某一个索引列绑定在一起，这个索引列选择的顺序：主键->唯一键->自动生成一个6字节的rowid

脏读：一个事务读取到了另一个事务没有提交的数据

幻读：如果所有的读取都是快照读，那么不会产生幻读问题，如果有快照读也有当前读才会产生幻读问题。

数据结构

  • BST(Binary Search Trees) 二叉搜索树

    • 左右子树高度差没有限制，当插入递增数据时，数据结构变成了链表，搜索效率低

  • AVL Trees(Balanced binary search trees) 平衡二叉搜索树

    • 左右子树高度差不能大于一
    • 当左右子树高度差大于一时，会进行数据旋转（为了提升查询性能，造成数据插入性能下降）

  • Red-Black Trees 红黑树

    • 最长子树不能超过最短子树的两倍，对查询与插入性能做了一个平衡

  • B Tree

    image-20210924105433583

  • B+ Tree

    image-20210924105213979

B树与B+树的区别

  1. B+树叶子节点会将所有非叶子节点的树进行冗余，在mysql将所有的数据存储在叶子节点，非叶子节点只存索引

页分裂与页合并

主键索引用自增的好处是减少页分裂与页合并

http://example.com/2022/05/16/DB/MYSQL优化笔记/
作者
UncleBryan
发布于
2022年5月16日
许可协议