Skip to content

mysql.md

Latest commit

 

History

History
328 lines (191 loc) · 14 KB

mysql.md

File metadata and controls

328 lines (191 loc) · 14 KB

MySQL

command

# cli连接
mysql -uroot -p123456 -h localhost -p 3306

# 查询连接信息
show processlist;

执行过程

image-20240902165940904

查询缓存

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空

默认不会使用查询缓存，但可以手动指定，从8.0之后没有该功能

select SQL_CACHE * from T where ID=10；

binlog 和redo log

binlog：Server层记录所有操作。追加写入，满了就产生新文件

redolog：innoDB特有，预写日志（WAL, write ahead log）,内存更新后，磁盘落地之前。固定文件大小，满了就清除一段

两阶段提交：redo log更新后 变成prepare 状态，等待binlog 写完成，然后redo log变成commit状态

隔离性与隔离级别

隔离性

ACID, Isolation:隔离性

-- 查看事务隔离级别
show variables like 'transaction_isolation';
-- 关闭事务自动提交
set autocommit=0

begin/start transation;-- 开启事务
commit;  -- 提交事务
commit work and chain;  -- 提交事务并开启下一个事务
rollback; -- 回滚事务

-- 查询查询事务时常
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

原因：当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

事务隔离级别

SQL 标准的事务隔离级别包括：

  • 读未提交是指： 一个事务还没提交时，它做的变更就能被别的事务看到。

  • 读提交：一个事务提交之后，它做的变更才会被其他事务看到。

  • 可重复读：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

  • 串行化： 顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

事务隔离的实现：回滚日志

事务并发的时候，每一条update都会有一天回滚日志，当一个事务创建的时候，读取当前的回滚日志，执行一遍就得到第一个事务看见的数据了

image-20240902175117837

避免长事务[面试]

从应用开发端来看：

  1. set autocommit=1。
  2. 确认是否有不必要的只读事务。这种只读事务可以去掉。
  3. 业务连接数据库的时候，根据业务本身的预估，通过 SET MAX_EXECUTION_TIME 命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

从数据库端来看：

  1. 监控 information_schema.Innodb_trx 表，设置长事务阈值，超过就报警 / 或者 kill；
  2. 在业务功能测试阶段要求输出所有的 general_log，分析日志行为提前发现问题；
  3. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

索引

常见模型： 哈希表， 有序数组，搜索树

哈希表：只适合等值查询的场景

有序数组：等值查询和范围查询都可以，但是不适合数据更新和插入，只适合静态存储引擎

n叉搜索树：层数高会增加寻址成本，所以需要树高不能高

mysql使用B+树不使用B树：

  1. B+树非叶子节点不存数据，B树非叶子节点存放数据，B树查询效率不稳定，结果可能在非叶子节点
  2. 而且B+树使用双向链表串联所有叶子节点。区间查询效率更高，B树需要中序遍历才能完成范围查找。
  3. B+树更矮更胖，高度小，io少

InnoDB索引模型

InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。

根据叶子节点的内容，索引类型分为主键索引和非主键索引。

如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

索引维护

B+ 树为了维护索引有序性，在插入新值和删除的时候需要做必要的维护。分裂或者合并，会消耗性能。为了避免这种情况可以采用自增主键:NOT NULL PRIMARY KEY AUTO_INCREMENT。

每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

由于每个非主键索引的叶子节点上都是主键的值，所以主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择。

适合业务字段做主键的场景

  • 只有一个索引；

  • 该索引必须是唯一索引。

这就是典型的 KV 场景。

覆盖索引

覆盖索引就是在这次的查询中，所要的数据已经在这棵索引树的叶子结点上了

select * from T where k between 3 and 5

select ID from T where k between 3 and 5

最左原则

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

  1. 如果有索引(a,b), 那么单独查询a,也会走索引
  2. 如果有索引a，a like 'aaa%'会走索引

索引下推

image-20240904175728553

只有一个联合索引(name, age), 当查询这个sql的时候，联合索引只能根据最左原则找到所有姓张的

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

通过索引下推，可以减少回表次数

image-20240905143145837

重建索引

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

-- 这个用来重建索引
alter table T engine=InnoDB

alter table T drop primary key;
alter table T add primary key(id);

alter table T drop index k;
alter table T add index(k);

主键索引

值唯一且不允许为空值

唯一索引

值唯一，可以有空值

alter table add unique index_name(`filed_name`)

普通索引

最基本的索引，没有限制

alter table add index index_name(`filed_name`)

索引选择[面试]

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。

**当需要更新(UPDATE)一个数据页时，**如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

也清楚了 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这时，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

这时。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

Change Buffer 和 Redo log

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。小结

-- 执行一条sql语句的流程如下图
insert into t(id,k) values(id1,k1),(id2,k2);

  1. Page 1 在内存中，直接更新内存；

  2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息

  3. 将上述两个动作记入 redo log 中（图中 3 和 4）。

    系统表空间(ibdata1)：存放权限，索引，表结构，数据字典等

image-20240911161832907

锁

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类

全局锁

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是

Flush tables with read lock (FTWRL);

-- 这个也可以用来设置全局只读，但是更建议用FTWRL，
-- 1.客户端执行FTWRL异常中断后会恢复正常更新状态
-- 2.readonly常用来区分主库还是从库
set global readonly=true

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。在备份过程中整个库完全处于只读状态。也可以用官方自带mysqldump备份工具。使用 single-transaction参数，只会开启一个事务也能拿到全局一致的数据

表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁

可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放，一般只有在数据库引擎不支持行级锁的时候才用到。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

-- client的线程A执行了这个语句，那么其他线程不能写t1，读写t2
-- 线程A只能读t1，不能写，能读写t2，不能访问其他的表
lock tables t1 read, t2 write;

元数据锁

MDL 不需要显式使用，在访问一个表的时候会被自动加上。MDL 的作用是，保证读写的正确性(MDL作用是防止DDL和DML并发的冲突)

事务中的 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。所以一个表变更语句有可能导致整个库挂掉。

解决方法：避免长事务，在alter table语句中设置超时时间

image-20240905165125698

行级锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。能减少可能冲突的锁的持有时间

死锁和死锁检测

当出现死锁以后，有两种策略：

  • 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

  • 一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

image-20240905174148444

热点更新问题：

假设有 1000 个并发线程要同时更新同一行，虽然最终检测可能没有死锁，但是死锁检测操作就是 100 万这个量级的。CPU利用率很高但是实际没执行多少事务

  1. 确定不会产生死锁时，可以临时关闭死锁检测

  2. 控制并发度，需要做在服务端，如果有中间件做在中间件里面

  3. 将逻辑上的一行改成多行，访问时随机选中一行，可以减少冲突。需要针对业务场景特殊处理

    以影院账户为例，可以考虑放在多条记录上，比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。