mysql

事务四大特性：原子性，一致性，隔离性，持久性

隔离级别

可通过show variables like 'transaction_isolation' 查看数据库使用的隔离级别，5.6和5.6以前用tx_isolation， 如果是oracle（默认隔离级别read committed）迁移过来的数据库，需要将mysql设置为读提交

• 读未提交（read uncommitted）,会出现脏读
• 读提交（read committed）会出现不可重复读，用到视图
• 可重复读（repeatable read）会出现幻读，用到视图
• 串行化（serializable）对同一行记录加锁，写加写锁，读加读锁，独写锁互斥

全局锁和表锁

全局锁（Flush tables with read lock (FTWRL)）：对整个数据库实例加锁，让数据库处于只读状态，DML语句和DDL语句都会被阻塞

场景：做全库逻辑备（把整个库的每张表都select出来存成文本）主要用于不支持事务的引擎（MyISAM）做备份（事务引擎InnoDB可在不可重复读，会构建一个前后一致性视图，所以备份时不需要全局加锁，使用官方自带的备份工具mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。适用于所有表都是使用事务引擎的库）

为什么备份要加锁？：不加锁的话，备份系统备份的得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

数据库是主从架构

• 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果你在从库，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

既然要全库只读，为什么不使用 set global readonly=true 的方式呢？

       确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：

•   一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。
• 二是，在异常处理机制上有差异。 如果客户端发生异常断开，FTWRL 会释放全局锁， 而将整个库设置为 readonly，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

表级锁

1. 一种是表锁：lock tables ...read/write。与 全局读锁（FTWRL）类似，可以用unlock tables主动释放锁，限定其他线程的操作，同时也限定了本线程的操作 。

• 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。
• 同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

1. 另一类表级锁是MDL（metadata lock）mysql5.5引入，不需要显示使用

当对一个表做增删改查（DML）操作的时候，MDL加读锁，做表结构修改时（DDL），MDL加写锁

• 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
• 如果有一个写锁被阻塞（还未获得写锁，写锁前面有加读/写锁的事务正在执行），该写锁后面的所有读锁也会被阻塞，相当于一个加锁队列按先后顺序执行（防止写锁饥饿而导致ddl语句一直被后面的dml语句抛在后面而长时间得不到执行） 这里可能会导致一个问题，如果某个表上的查询语句频繁，而且客户端有重试机制，超时后会再起一个新session再请求的话，这个库的线程很快就会爆满

行锁

行锁是innodb引擎层自己实现的。myisam不支持行锁，所以在并发方面需要借助表锁来实现

select * from t lock in share mode:读锁（DQL没有加读锁）

select * from t for update 写锁（语境同DML语句）

无论是行读锁还是行写锁，都是当前读（当前系统已提交的数据可见）

行锁导致的问题

死锁和死锁检测：当两个session分别持有一把行锁，并需要获取对方的行锁时，会出现死锁。

解决死锁策略

1. 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置（innodb中默认是50秒）。当出现死锁后，超过超时时间会超时退出，释放对应行锁。无法正确预估超时时间，一般不采用
2. 另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on（默认），表示开启这个逻辑。但是如果大量的事务都要更新同一行的场景，会触发很多的死锁检测，大量消耗cpu，可以从业务上将账户一行拆分成多行，比如一个账户值记录，分成10行账户记录值总和，增加账户值的时候，随机拿其中一条记录来加值，减少冲突概率

注意点

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

一致性视图（rr级别，一致性视图的生成时间是 第一次执行一致性读的时候）

间隙锁加锁过程

如果select xxx ...update 使用了覆盖索引，则不会再主键索引上加锁

如果是等值查询，唯一索引，next-key-lock（间隙锁加行锁，前开后闭）退化为行锁

如果是等值查询，非唯一索引，net-key-lock（间隙锁加行锁，前开后闭）退化为间隙锁

MySQL 中关于gap lock / next-key lock 的一个问题 | HelloWood

实验案例1-表级锁MDL长事务导致的mysql服务线程和内存打满

1.sessionA执行长事务MDL加读锁语句

 2.sessionB执行一个ddl语句（加MDL写锁）

    alter table T add b int; 被阻塞

3.开启一个窗口sessionC查看当前mysql服务开启的线程数，和使用top查看mysql服务的内存占用情况

 4.同时开启多个session， 执行select * from T 语句，查看sessionC窗口的线程数，和内存占用情况

所以再执行ddl语句时，需要注意有没有长事务的存在。事务不提交会一直占用MDL锁，可以查看information_schema 库的 innodb_trx 表中的正在执行的事务，如果刚好有长事务存在，要考虑先暂停ddl，或者kill掉这个长事务

order by工作原理

16 | “order by”是怎么工作的？-极客时间

排序字段没有索引， 文件排序（Using filesort）：sort_buffer(在server层)内存够用（能存下整个结果集），否者借助文件排序（多个小文件归并排序）

    全字段排序 :保存行的所有字段（全字段排序减少回表，优先被选择）

    rowId排序:保存需要排序的字段和主键id，排好序后需要根据主键回表拿出行的其他字段

排序字段有索引，索引排序（Using index condition）: 索引是有序的，覆盖索引（Using index）更好（联合索引中有结果集的所有字段）

     

     

     

幻读产生的原因： 行锁只能所有现有的行， 但插入数据是插入未来的行，innodb为了解决幻题，引入了间隙锁（gap lock）

    

     

连接mysql ：mysql -h localhost -u root -p

create database test；创建数据库test

show database:查看已有的数据库

use test：切换到test库

        information_schema库的innodb_trx表 可以查询执行中的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G  （sql后面加\G可以将结果集转向纵向便于可读）
#查看当前持续时间超过60的事务

show variables like 'transaction_isolation' 查看数据库的隔离级别