Mysql
2021-05-07 16:57:56 8 举报AI智能生成
Mysql
作者其他创作
大纲/内容
基础知识
mysql驱动
在底层跟数据库建立网络连接
数据库连接池
会维持多个数据库连接，执行完sql语句后，不销毁这个数据库连接，而是放回池子里，后续可以再使用
解决了多个线程并发使用多个数据库连接去执行sql语句的问题，也避免了数据库连接使用完之后就销毁的问题
常见的有DBCP，C3P0，Druid
Sql语句执行流程
1,网络连接由线程来处理，从连接池里获取连接后，线程监听和请求
2，sql接口：负责处理接收到的sql语句
3，查询解析器：让mysql看懂sql语句
4，查询优化器：选择最优的查询路径，生成执行计划
5，执行器：根据优化器生成的执行计划去调用存储引擎的接口
6，调用存储引擎接口，执行sql语句，访问内存以及磁盘上的数据
存储引擎架构
update users set name ='XXX' where id =10
1，buffer pool缓冲池，执行语句时，判断数据是否在缓冲池里，若没有，从磁盘读取放入缓冲池
2，更新时，会把更新前的值写入undo日志，便于回滚
3，更新buffer pool 中的缓存数据,这条数据对于磁盘中的数据，这条就是脏数据了
4，把对内存的修改写入一个redo Log Buffer缓冲区，存放redo日志，用来宕机恢复数据
提交事务前宕机，内存中buffer pool 数据丢失，redo日志也丢失，此时报数据库异常
提交事务时，redo日志写入磁盘。假如宕机，redo日志去恢复修改数据。
三个日志刷盘策略： innodb_flush_log_at_trx_commit:0 不写入磁盘。
innodb_flush_log_at_trx_commit:1 一定会写入磁盘。通常建议设置为1
innodb_flush_log_at_trx_commit:2 先写入os cache，1秒后再写入磁盘。
innodb_flush_log_at_trx_commit:1 一定会写入磁盘。通常建议设置为1
innodb_flush_log_at_trx_commit:2 先写入os cache，1秒后再写入磁盘。
5，提交事务时，binlog归档日志写入磁盘
binlog不是innodb存储引擎特有的日志文件，是属于mysql server自己的日志文件
刷盘策略：
sync_binlog:0，默认，先写os cache
sync_binlog:1，强制写磁盘
sync_binlog:0，默认，先写os cache
sync_binlog:1，强制写磁盘
binlog文件名称、位置写入redo log日志文件，同时redo日志写一个commit标记，这才表示事务最终完成
redo log的commit标记事务才算提交成功，redo log和binlog完全是一致的
6，io线程随机把buffer pool修改后的脏数据刷回磁盘
在把脏数据写入磁盘之前，mysql宕机也没关系，因为重启后，根据redolog会恢复之前提交事务做过的修改到内存里去
生产环境配置
经验而言，java应用系统常选用机器大致是2核4G，4核8G，数据库最低也要在8核16G，正常16核32G，因为数据库需要执行大量的IO操作，这是比较慢的
16核32G每秒抗个两三千问题不大，但是如果达到每秒上万请求，那么数据库的cpu，磁盘，io，内存的负载瞬间飙升，可能扛不住
最好采用SSD固态硬盘，因为需要大量的读写磁盘文件，所以固态硬盘性能更高一些
对数据库做一个压测，再考虑java系统能承载的并发数
QPS：代表数据库每秒能处理多少请求。TPS代表数据库每秒能处理多少事务
吞吐量：代表机器的磁盘存储每秒能读写多少字节
io相关的压测指标
数据的延迟写入，磁盘的读写延迟
cpu负载
网络负载
内存负载
压测工具sysbench
Buffer Pool
假如对磁盘文件读写，每次请求可能需要几百毫秒进行处理，相当的慢，所以实际上增删改查都是基于buffer pool、进行的
默认：128M,生产上，假如是16核32G的机器，实际会配置2G，修改my.ini，innodb_buffer_pool_size=2147483648
buffer pool中存放一个个的数据页，叫作缓存页
缓存页和磁盘中的数据页都是16KB,更新一行数据时，数据库会找到这条数据所在的数据页，加载到buffer pool里去
缓存页和磁盘中的数据页都是16KB,更新一行数据时，数据库会找到这条数据所在的数据页，加载到buffer pool里去
初始化：数据库启动时，分配一块内存区域，申请内存之后，buffer pool就会按照16K数据页加上每个数据页800字节的描述信息划分一个个的缓存页和描述数据，此时缓存页都是空的
free链表
用链表存储空闲的缓存页的描述数据
哪些缓存页是空闲的？每个空闲的缓存页的描述数据组成一个双向链表，作用是用来保存空闲缓存的描述数据
- 由一个free双向链表，缓存页的描述数据分别有两个指针，free-pre，free-next链接成一个链表，free链表并不会申请独立的空间。
2,当磁盘数据读取到buffer pool时，找到free链表拿到空闲的缓存页，把数据写入缓存。再去除free链表上的空闲节点
怎么知道数据页有没有被缓存？
数据库有一个哈希表数据结构，他会用表空间号+数据页号作为key，缓存页地址作为value，当用表空间号+数据页号去buffer pool查询，如果有，说明已经缓存了
flush双向链表
用链表存储脏数据描述数据
如果内存中的数据和数据库的数据不一样，就称为脏数据，还没有被刷到磁盘上的更新过的数据
哪些缓存页是已经更新了的“脏数据”？由一个flush双向链表记录，然后刷到磁盘中
LRU链表
当BufferPool内存空间不够使用时，会淘汰最近最少使用的数据刷入到磁盘中去
淘汰算法：缓存命中率，引入一个LRU链表，当free链表没有空闲缓存页时，把最近最少使用的缓存页数据淘汰及刷入到磁盘中，释放空闲缓存页
LRU链表怎么工作？
被加载进来的缓存页顺序插入LRU链表头部（链表头插法），如果BufferPool不够用了，mysql就会将LRU链表的末尾节点刷入到磁盘
预读机制：会把相邻的数据页缓存到buffer pool
某条数据可能在数据页的头部，也可能在数据页的尾部，所以mysql为了提高效率会将某个数据也的相邻数据页也加载到bufferpool
预读机制优点：为了提高性能，减少IO操作
缺点：在全表扫描时，会把频繁访问的数据刷到磁盘里去，但仅使用一次。却将经常命中的缓存页直接挤到了LRU链表的尾部
为了解决预加载带来的缺点，推出了冷热分离的LRU链表
冷热数据分离，第一次加载会放到冷数据链表头
innodb_old_blocks_time=1000毫秒，即在1S后（不是1S内）再次访问，才会把数据放入热数据链的表头，解决了全表扫描的问题
之所以要在一定时间之后，是防止某条数据刚被加载到缓存池，紧接着又被访问，但是之后就不使用了，又出现上面的缺点
之所以要在一定时间之后，是防止某条数据刚被加载到缓存池，紧接着又被访问，但是之后就不使用了，又出现上面的缺点
什么时候淘汰冷数据，假如缓存不足，在1S后然后没再次访问的数据，就在冷数据连尾部删除。
热数据区域优化：热数据区域的后3/4部分被再次访问，才移动到表头位置，为了减少链表的移动
LRU刷入磁盘
数据库后台有一个线程定时的把冷数据连尾部的几个缓存页刷到磁盘
刷入磁盘后，缓存页加入到free链表，从flush链表中移除，从LRU链表中移除
这个后台线程，同时会在繁忙的时候把flush链表中的缓存页刷到磁盘中。
LRU算法简单算法
详情加算法画图文件（LRU算法）
也可以直接继承LinkedHashMap来实现
定义一个int CACHE_SIZE
最近访问的放在头，最老访问的就在尾
插入头部，判断是否大于CACHE_SIZE，如果大于，删除尾部
假如再次访问节点，刷新节点在链表中的位置，移到头部。
运转整体机制
一边不停的加载数据到缓存页里去，不停的查询和修改缓存数据，然后free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的增加和移动
另一边，后台线程不停的把lru链表的冷数据区域的缓存页以及flush链表的缓存页，输入磁盘来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。
生产环境配置
设置多个buffer pool优化并发能力
因为每个bufferPool虽然在内存里运行，但每个bufferPool内部是串行执行的
buffer pool不支持运行期间动态调整大小
数据库可以由多个buffer pool组成，一个buffer pool由多个chunk组成，一个chunk默认是128M
buffer pool设置为服务器的内存50%-60%左右，32G内存就设置为16G
buffer pool总值=（chunk*buffer pool数量）*倍数，必须是倍数，多少倍都行。
buffer pool总值=（chunk*buffer pool数量）*倍数，必须是倍数，多少倍都行。
表空间
对应磁盘文件，是一个个的 "表名.ibd"
表空间：包含多组数据区
一组数据区：256个数据区
一个数据区：64个数据页，1MB
数据页：16KB。包含多个数据行
一组数据区：256个数据区
一个数据区：64个数据页，1MB
数据页：16KB。包含多个数据行
执行crud时，说白了就是从磁盘上的表空间的数据文件里，加载一些数据页到buffer pool的缓存里去使用
redo log
redo log 日志是 偏物理层面的日志，也叫 重做日志。而 binlog 是归档日志
redo log：本质就是 表空间+数据页号+偏移量+修改几个字节的值+具体的值
redo log：本质就是 表空间+数据页号+偏移量+修改几个字节的值+具体的值
redo log block：512字节，存放多个单行日志
redo log group：一个事务是一组日志，一个redo log group可能存放在多个block里面，一个block也可能存放多个group
redo log buffer：16MB，里面是block。
redo log group：一个事务是一组日志，一个redo log group可能存放在多个block里面，一个block也可能存放多个group
redo log buffer：16MB，里面是block。
何时刷入磁盘
1，如果写入redo log buffer的日志占据了buffer容量的一半，也就是超过8M,一般出现在高并发的情况下
2，事务提交的时候。
3，后台线程定时刷新，每隔1秒
4，mysql关闭的时候。redo log block都会刷入磁盘
undo 日志文件
如果我们执行一个更新语句，在没有提交事务之前，我们都是可以对数据进行回滚的
undo 日志文件就是保证我们可以回滚数据的一个组件
举例：
如果我们要把 id = 10 的数据的 name 从 张三 改为 李四
第一步是把数据加载到 Buffer Pool 里
第二步 就要把 id = 10 ，name = 张三 的这条原始数据，写到undo日志文件
如果数据回滚，就会从 undo 日志文件中读取原始数据恢复
undo 日志文件就是保证我们可以回滚数据的一个组件
举例：
如果我们要把 id = 10 的数据的 name 从 张三 改为 李四
第一步是把数据加载到 Buffer Pool 里
第二步 就要把 id = 10 ，name = 张三 的这条原始数据，写到undo日志文件
如果数据回滚，就会从 undo 日志文件中读取原始数据恢复
事务，多线程
sql标准中四种事务隔离级别
read uncommitted 读未提交，会避免脏写
read committed 读已提交，会避免脏写，脏读
repeatable read 可重复读，会避免脏写，脏读，不可重复读，保证事务内，读到的值都是同一个
serializable 串行化，不允许多个事务并发执行。这种级别一般除非脑子换了，否则不可能设置
read uncommitted 读未提交，会避免脏写
read committed 读已提交，会避免脏写，脏读
repeatable read 可重复读，会避免脏写，脏读，不可重复读，保证事务内，读到的值都是同一个
serializable 串行化，不允许多个事务并发执行。这种级别一般除非脑子换了，否则不可能设置
脏写：一个事务（回滚）修改了 另外一个事务未提交的修改过的数据
举例：原来有一行数据是 NULL
事务 A 把 NULL 改成了值 A,但是它并没有提交
事务 B 做的也是 update 操作，把值 A 改成了值 B
事务 A 回滚了,事务 B 明明正常写了一行数据，但是写完之后发现值变了
mysql隔离级别
默认的是repeatable read，但是mysql的RR级别，依托MVCC机制，可以避免幻读。
事务时并发处理的，并且避免了幻读，所以就用默认的就特别好。一般情况下不用改。除非你一定要在你的事务执行期间多次查询时必须要查到其他事务已经提交的最新值，那么就设置成Isolation.READ_COMMITTED
默认的是repeatable read，但是mysql的RR级别，依托MVCC机制，可以避免幻读。
事务时并发处理的，并且避免了幻读，所以就用默认的就特别好。一般情况下不用改。除非你一定要在你的事务执行期间多次查询时必须要查到其他事务已经提交的最新值，那么就设置成Isolation.READ_COMMITTED
MVCC机制：多版本并发控制机制
undo log多版本链条+ReadView机制
ReadView：基于undo log版本链条实现的一套试图机制，ReadView一旦生成就不会改变
RC隔离级别：的关键点在于，事务里每次查询都生成新的ReadView
RR隔离级别：的关键点在于，事务里每次查询都是同一个ReadView
基本要素ACID
原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账
持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚
spring的事务
详见分布式事务process脑图
锁机制
共享锁也叫S锁：查询的时候加，与共享锁是不互斥的。与独占锁互斥。
select * from table lock in share，加了之后其他事务想更新时更新不了的，因为另外的事务拿不到独占锁。
select * from table lock in share，加了之后其他事务想更新时更新不了的，因为另外的事务拿不到独占锁。
独占锁也叫X锁：更新的时候加，都互斥
查询时，加独占锁：select * from table for update
查询时，加独占锁：select * from table for update
锁粒度
1、 行锁：锁定一行记录
2、 间隙锁：锁定一个范围，但是不包含记录本身
3、Next-Key Lock：Record Lock和Gap Lock的组合，锁定一个区间以及对应的记录
4、 表锁：锁定整张表
2、 间隙锁：锁定一个范围，但是不包含记录本身
3、Next-Key Lock：Record Lock和Gap Lock的组合，锁定一个区间以及对应的记录
4、 表锁：锁定整张表
索引
数据库索引的位置
默认是这里 （数据库安装目录\data\某个数据库 ）
SHOW VARIABLES LIKE '%datadir%'
数据库文件都有后缀
例如：
数据文件：. myd
索引文件：. MYI
表定义文件：. frm
例如：
数据文件：. myd
索引文件：. MYI
表定义文件：. frm
存储结构：
数据页之间：双向链表
数据页内部数据行：单向链表
数据页之间：双向链表
数据页内部数据行：单向链表
数据页分裂：假如主键不是自增的，要把主键较大和较小的在不同数据页进行切换位置，为了保证下一个数据页的主键值比上一个数据页的主键值都小
主键索引：把每个数据页的页号和最小主键组成一个索引目录
索引的存储物理结构，跟数据页一样的结构，组成一颗B+树
假如有联合索引INDEX_KEY（ class_name,student_name,subject_name）
最左侧列匹配：
用到索引：select * from student_score where class_name='' and student_name = '';
用不到索引：select * from student_score where subject_name='';
最左前缀匹配规则：
用到索引：select * from student_score where class_name like '1%';
用不到索引：select * from student_score where class_name like '%班'; 因为不知道最左前缀是什么
范围查找规则：
用到索引：select * from student_score where class_name > '1班' and class_name <'5班';
用不到索引：select * from student_score where class_name > '1班' and class_name <'5班' and student_name>''; 范围查询，只有对联合索引最左侧的列起作用
等职匹配 +范围匹配规则、
select * from student_score where class_name > '1班' and student_name <'' and subject_name <'';
class_name 会基于索引，student_name会基于索引，但是subject_name不能用索引，因为class_name和student_name是按照顺序存储的
排序：order by
index(xx1,xx2,xx3)
select * from table order by xx1,xx2,xx3
select * from table order by xx1 desc,xx2 desc,xx3 desc
联合索引的情况下，要么都加，要么都不加，如果按倒序，也都加desc。
order by字段不在联合索引里，或者order by用了复杂的函数，都不会使用索引
分组：group by
通常而言：group by后的字段，按照联合索引的最左侧的字段开始，按顺序排列开来。
最左侧列匹配：
用到索引：select * from student_score where class_name='' and student_name = '';
用不到索引：select * from student_score where subject_name='';
最左前缀匹配规则：
用到索引：select * from student_score where class_name like '1%';
用不到索引：select * from student_score where class_name like '%班'; 因为不知道最左前缀是什么
范围查找规则：
用到索引：select * from student_score where class_name > '1班' and class_name <'5班';
用不到索引：select * from student_score where class_name > '1班' and class_name <'5班' and student_name>''; 范围查询，只有对联合索引最左侧的列起作用
等职匹配 +范围匹配规则、
select * from student_score where class_name > '1班' and student_name <'' and subject_name <'';
class_name 会基于索引，student_name会基于索引，但是subject_name不能用索引，因为class_name和student_name是按照顺序存储的
排序：order by
index(xx1,xx2,xx3)
select * from table order by xx1,xx2,xx3
select * from table order by xx1 desc,xx2 desc,xx3 desc
联合索引的情况下，要么都加，要么都不加，如果按倒序，也都加desc。
order by字段不在联合索引里，或者order by用了复杂的函数，都不会使用索引
分组：group by
通常而言：group by后的字段，按照联合索引的最左侧的字段开始，按顺序排列开来。
更新语句：三大核心问题
1，索引不能太多，更新的时候维护很多的索引树
2，可能会涉及到锁等待，死锁问题
3，可能涉及到mysql连接池，写redo log文件
1，索引不能太多，更新的时候维护很多的索引树
2，可能会涉及到锁等待，死锁问题
3，可能涉及到mysql连接池，写redo log文件
索引涉及考虑的因素：
第一条:where,order by,group by 都要用到索引，并且遵循最左前缀原则
第二条：基数，尽量使用基数大的做索引，例如，性别就不适合做索引
第三条：索引不要太多，尽量用两三个联合索引覆盖掉表的全部查询。
第四条：主键一定要自增的，别用uuid之类的。
第五条：范围查询，一个字段一旦做范围查询用到索引，接下来的条件都不能用索引了，这就是规则
第一条:where,order by,group by 都要用到索引，并且遵循最左前缀原则
第二条：基数，尽量使用基数大的做索引，例如，性别就不适合做索引
第三条：索引不要太多，尽量用两三个联合索引覆盖掉表的全部查询。
第四条：主键一定要自增的，别用uuid之类的。
第五条：范围查询，一个字段一旦做范围查询用到索引，接下来的条件都不能用索引了，这就是规则
为什么说B+树比B树更适合数据库索引？
1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
PS：我在知乎上看到有人是这样说的,我感觉说的也挺有道理的：
他们认为数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。
1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。
2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
3、由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。
PS：我在知乎上看到有人是这样说的,我感觉说的也挺有道理的：
他们认为数据库索引采用B+树的主要原因是：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。
在InnoDB中一个3层B+树最多大概可以存放多少行数数据？？
一个索引页的大小为16384B,也就是16kb
假如一行数据是1KB，也就是说一页可以存放16行数据。然后因为非叶子节点的结构是：“页指针+键值”，我们假设主键ID为bigint类型，长度为8字节（byte），而指针大小在InnoDB源码中设置为6字节（byte），这样一共14字节（byte），因为一个页可以存放16k个byte，所以一个页可以存放的指针个数为16384/14=1170个。因此一个两层的B+树可以存放的数据行的个数为：1170*16=18720（行）
根页（page:3）可以存放1170个指针，然后第二层的每个页（page:4,5,6,7）也可以存放1170个指针。这样一共可以存放1170*1170个指针，所以一共可以存放1170*1170*16=21902400行记录。也就是说一个三层的B+树就可以存放千万级别的数据
执行计划
运行sql的成本
1，从磁盘把数据读出来，从磁盘数据读到内存就是IO成本，而且是一页一页读，读一页成本约定为1
2，数据运算，验证是否符合搜索条件、排序，都是消耗cpu资源的，属于cpu成本，约定读取和检测一条数据成本是0.2
全表扫描成本计算
show table status like "表名"
拿到rows和data_length（聚簇索引的字节数）
data_length/1024就是kb为单位的大小，在除以16KB==页数
总成本=IO成本（页数*1）+cpu成本（rows*0.2）
执行计划优化
select * from t1 join t2 on t1.x1=t2.x1 and t1.id=1
mysql自动优化为：
select t1表中id=1的那行数据的各个字段的常量值，t2.* from t1 join t2 on t1表里x1字段的常量值=t2.x1
子查询优化
select * from t1 where x1 in (select x2 from t2 where x3=xxx)
运行时，并不是，t1全表扫描，判断每条数据是否在子查询的结果集里
而是，全表扫描物化表去t1通过索引查询
semi join（半连接）
对于t1表而言，只要在t2表里有符合t1.x1=t2.x2和t2.x3=xxx条件的数据就可以
执行计划个数
举例：explain selelct * from t1 where x1 in (select x1 from t2 where x1='xxx' union select x1 from t1 where x1='xxx');
执行计划会出现4个，1,select_type：primary 2,dependent subquery 3,dependent union 4,union result(合并)
执行计划会出现4个，1,select_type：primary 2,dependent subquery 3,dependent union 4,union result(合并)
explain 参数说明
id:每个select对应一个id。复杂的查询会拆分出多个select（比如子查询），也就包含多条执行计划，与表没关系，id相同代表同一个select语句
possible_keys：结合type使用，确定具体的索引可供选择
key：在possible_keys：里实际选择的那个索引
key_len：索引用到的字段的最大长度
ref：与索引列等职匹配
rows：大概会读取多少条数据
filtered：经过搜索条件过滤后筛选出来的数据占表里数据的百分比
possible_keys：结合type使用，确定具体的索引可供选择
key：在possible_keys：里实际选择的那个索引
key_len：索引用到的字段的最大长度
ref：与索引列等职匹配
rows：大概会读取多少条数据
filtered：经过搜索条件过滤后筛选出来的数据占表里数据的百分比
select_type：查询类型
simple普通查询
primary代表主查询
subquery子查询
union合并查询
derived 子查询衍生物化临时表
type：索引访问方式
const
通过聚簇索引或者二级索引（唯一索引）+聚簇索引回表查询，常量级
ref（二级索引等值匹配）
普通二级索引，不是唯一索引，也很快
rang
范围查询
index（扫描二级索引叶子节点每条数据）
有联合索引（x1,x2,x3）
select x1,x2,x3 from table where x2=? ，针对只遍历二级索引，无需回表聚簇索引
all
ref_or_null
const后面加类似name is null
extra说明
信息说明:比如查询过程中使用了临时表
Using index仅仅用到了一个二级索引
Using index condition用到了一个二级索引同时，加了条件过滤
Using where没用到索引或者用了索引，where里好几个条件
Using join buffer 多表关联，关联条件并不是索引，内部使用一种内存技术提升性能
Using filesort在order by时没用直接利用索引排序，需把所有数据放入临时磁盘文件。
Using temproxy在group by，union，distinct时如果没法直接利用索引，就会基于临时表来完成，涉及磁盘操作，性能极低。
SQL调优案例
1，千万级用户表
查询最近登录的用户，发送推销短信
select id,name from users where id in (select user_id from users_extent_info where last_login_time<xxxxxx_)
问题：
在测试环境单表5万条数据跑执行计划，即使是5万条数据，跑了10多秒
原因1，子查询的4561条数据物化成了一个临时表，临时表会落磁盘
原因2，针对users表做了个全表扫描5万条数据，扫描的过程中，每一条数据跟一个没有索引的临时表进行了join操作（相当于全表扫描）
为什么会做join操作？
mysql生成计划时，自动把普通的in子句，优化成了基于semi join进行in+子查询的操作
semi join(半连接)：
表示每条数据，不需要跟临时表所有记录join上，只要找到匹配的就返回了。mysql自己做的优化
半连接导致了大量的无索引的全表扫描
解决：
关掉半连接set optimizer_switch='semijoin=off'
提升几十倍，编程100ms，反而他自己的半连接优化导致问题，正常的方式基于主键索引去执行，性能提高
生产不能关闭半连接：
这样并没有在进行semi jion优化，而是正常的用了子查询，主查询也是基于索引去执行的，这样性能就一下提升到几百毫秒了
想办法避免他全表扫描，一定要让他用索引，用索引才是王道，是最重要的
2，几千万商品系统大量慢查询
问题：
大量的慢查询导致每个数据库连接执行一个慢查询要耗费很久，同时连接变多，报警数据的连接突然暴增
接着数据库的连接全部打满，导致数据库没法处理新的查询，查询发送到数据库直接阻塞后超时了，商品系统濒临崩溃
大量慢查询耗尽数据库的连接资源，用户也就没法查询和筛选电商网站的商品了
一个查询要执行几十秒的sql
监控情况：
每分钟慢查询超过了10W+
由于连接资源被占用，大量的查询变成了慢查询
功能
用户在电商网站上根据商品的品类以及子类进行筛选
商品表大致有几千万数据
select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx
索引也有:
index_category(category,sub_category)
分析：
按理说，用上索引的话，会很快，绝对不会超过1S
可见这个sql语句是没有用到索引，explain 查看执行计划
结果没用索引，select_type是primary，走的是主键索引，而且extra显示：Using where
本质上，主键聚簇索引上进行扫描，一遍扫描，一遍还用where条件的两个字段去筛选，必要耗时长
为什么mysql默认会选择对主键聚簇索引进行扫描
1，首先接近亿级数据量的大表，二级索引也是比较大的
2，mysql觉得从二级索引查到符合where条件的数据，接着还得回表，回表之前，还得做完order 和limit的操作，查出来数据太多，mysql判定这一方式不好
3，mysql认为按照顺序扫描聚簇索引，拿到10条符合where条件的数据的速度应该很快，可能比使用二级索引
本来走聚簇索引，是很快会拿到数据的，但是，导致查不到任何商品数据，等于接近上亿的商品表进行了全表扫描，正因如此，出现几十秒的慢查询
解决办法：
select * from products force index(index_category),强制使用索引
明显提升，控制在100ms以内
3,十亿级商品评论表
前提
评论数据，做了分库分表，单表数据量控制在百万级别
每个商品的评论都放在一个库的一张表里，确保用户在分页查询评论时，一般直接从一个库的一张表里获取数据
有些热门商品评论可达几十万条，某些用户就喜欢一页一页翻，有时候还会分页跳转，这就涉及深分页问题
sql语句
select * from comments where product_id='xx' and is_good_comment='1' (表示只看好评)order by id desc limit 100000,20
分析
正常情况下，肯定会走索引（index_product_id）,但是is_good_comment并没有索引，所以只能很尴尬额回表
也就是说每一条评论都会回表，取出来is_good_comment字段，做对比
导致几十万的评论回表操作，虽然根据id在聚簇索引里快速查询，但是架不住每条都回表
并且还得基于临时磁盘文件进行倒序排序，有的耗时很久，还得进行limit
导致问题
几十万次回表查询，还有几十万条数据的磁盘文件排序，sql每次基本要跑1-2s
解决问题
这个思路，反而要跟第二个案例反过来，这个案例就不应该走索引，再回表了
这个案例，因为product_id和is_good_comment不是联合索引，所以会出现大量的回表操作，耗时极高
改造sql：
select * from comments a ,(select id from comments where product_id='xx' and is_good_comment='1' order by id desc limit 100000,20) b where a.id = b.id
子查询反而会使用primary聚簇索引，根据id排序进行扫描，扫描时，只要有100000+20条符合条件的数据，此时就可以根据limit 提取5001页的20条数据
接着对着20条数据回表，按照id去聚簇索引里查找一下完成数据
优化后，减少几百毫秒
结论，根据product_id的二级索引查找，反而出现10几万次回表查询，所以二级索引查找方式不适合
4，千万级数据删除，导致慢查询
问题分析：
查询sql本身不会发生慢查询问题
是生产服务器负载太高导致
比如磁盘io负载特别高，也就是每秒执行大量的高负载随机io，还有就是cpu、负载过高也会导致慢查询
应该排查一下服务器的负载，尤其看磁盘，网络和cpu的负载
比如灌入大量数据，最好在凌晨低峰期灌入，别影响线上系统运行
结果发现负载都没问题，用sql调优利器，profiling
通过profiling工具，发现他的sending Data耗时严重，几乎1S时间，占据了sql执行耗时的99%
sending Data：
为你的select语句把数据读出来，同时发送给客户端
用一个命令：
show engine innodb status
大量的事务，mvcc在构建dudo多版本快照链条，此时history list length值会很高，是一些长事务运行时间长导致
发现长事务是一下清理了上千万的数据，居然开了一个事务，导致事务一直在运行
查询的时候，可能会把上千万标记为已删除的数据都扫描一遍，因为readview机制数据没有真正删除，只是标记为删除
查询的时候会根据readview判断哪些数据是你可见的，以及可见的数据版本是哪个版本。
所以会读到所有标记为删除的数据就会继续扫描下去，出现千万级数据的扫描，造成慢查询
解决问题
直接kill掉正在删除的千万级数据的长事务，大量的数据清理应该全部放在凌晨执行，所以查询很少
死锁
产生原因：两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象，那么对应的解决死锁问题的关键就是：让不同的session加锁有次序
当对存在的行进行锁的时候(主键)，mysql就只有行锁。
当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）
锁住的范围为：
(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
当对未存在的行进行锁的时候(即使条件为主键)，mysql是会锁住一段范围（有gap锁）
锁住的范围为：
(无穷小或小于表中锁住id的最大值，无穷大或大于表中锁住id的最小值)
死锁成因
1，不同表相同记录行锁冲突，事务A和事务B操作两张表，但是顺序颠倒
2，相同表不同的记录行锁冲突，jobA处理的的id列表为[1,2,3,4]，而jobB处理的id列表为[8,9,10,4,2]，这样就造成了死锁。因为2在jobB里面是放在后面的
3，不同索引锁冲突，这种情况比较隐晦，事务A在执行时，除了在二级索引加锁外，还会在聚簇索引上加锁，在聚簇索引上加锁的顺序是[1,4,2,3,5]，而事务B执行时，只在聚簇索引上加锁，加锁顺序是[1,2,3,4,5]，这样就造成了死锁的可能性。因为A二级索引已经加锁，正接着给聚簇索引加锁时，已经被事务B占用。
update masg set message =' 订单' where token>'aaa'
delete from msg where id>1;
4,gap锁（间隙锁）：innodb在RR级别下,为了解决幻读问题，innodb引入了gap锁,
在事务A执行：update msg set message=‘订单’ where token=‘asd’;
innodb首先会和RC级别一样，给索引上的记录添加上X锁（排它锁），此外，还在非唯一索引’asd’与相邻两个索引的区间加上锁。
这样，当事务B在执行insert into msg values (null,‘asd',’hello’); commit;时，
会首先检查这个区间是否被锁上，如果被锁上，则不能立即执行，需要等待该gap锁被释放。这样就能避免幻读问题。
在事务A执行：update msg set message=‘订单’ where token=‘asd’;
innodb首先会和RC级别一样，给索引上的记录添加上X锁（排它锁），此外，还在非唯一索引’asd’与相邻两个索引的区间加上锁。
这样，当事务B在执行insert into msg values (null,‘asd',’hello’); commit;时，
会首先检查这个区间是否被锁上，如果被锁上，则不能立即执行，需要等待该gap锁被释放。这样就能避免幻读问题。
如何尽可能避免死锁
1）以固定的顺序访问表和行。比如对第2节两个job批量更新的情形，简单方法是对id列表先排序，后执行，这样就避免了交叉等待锁的情形；又比如对于3.1节的情形，将两个事务的sql顺序调整为一致，也能避免死锁。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
2）大事务拆小。大事务更倾向于死锁，如果业务允许，将大事务拆小。
3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁概率。
4）降低隔离级别。如果业务允许，将隔离级别调低也是较好的选择，比如将隔离级别从RR调整为RC，可以避免掉很多因为gap锁造成的死锁。
5）为表添加合理的索引。可以看到如果不走索引将会为表的每一行记录添加上锁，死锁的概率大大增大。
七种类型的锁
为何InnoDB能够支撑如此之高的并发
提高并发的演进思路
（1）普通锁，本质是串行执行；
（2）读写锁，可以实现读读并发；
（3）数据多版本，可以实现读写并发；
（2）读写锁，可以实现读读并发；
（3）数据多版本，可以实现读写并发；
redo日志
将修改行为先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上
redo日志用于保障，已提交事务的ACID特性
undo日志
数据库事务未提交时，会将事务修改数据的镜像（即修改前的旧版本）存放到undo日志里，当事务回滚时，或者数据库奔溃时，可以利用undo日志，即旧版本数据，撤销未提交事务对数据库产生的影响
undo日志用于保障，未提交事务不会对数据库的ACID特性产生影响
快照读（Snapshot Read），这种一致性不加锁的读
事务读取到的数据，要么是事务开始前就已经存在的数据，要么是事务自身插入或者修改的数据
InnoDB之所以并发高，快照读不加锁
InnoDB所有普通select都是快照读
（1）自增锁（Auto-inc Locks）
一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列
如果一个事务正在往表中插入记录，所有其他事务的插入必须等待
案例分析
（1）事务A先执行insert，会得到一条(4, xxx)的记录
（2）事务B后执行insert，假设不会被阻塞，那会得到一条(5, ooo)的记录
（3）事务A继续insert：会得到一条(6, xxoo)的记录
（4）事务A再select：select * from t where id>3;
得到的结果是：
4, xxx
6, xxoo
4, xxx
6, xxoo
不可能查询到5的记录，在RR的隔离级别下，不可能读取到还未提交事务生成的数据
假设不是自增列
数据表中有数据：
10, shenjian
20, zhangsan
30, lisi
10, shenjian
20, zhangsan
30, lisi
事务A先执行，未执行
insert into t values(11, xxx);
insert into t values(11, xxx);
事务B后执行
insert into t values(12, ooo);
insert into t values(12, ooo);
这里，便不再使用自增锁
使用的是插入意向锁
并不会阻塞事务B
如果插入位置冲突，多个事务会阻塞，以保证数据一致性
（2）共享/排它锁(Shared and Exclusive Locks)
标准的行级锁
（1）事务拿到某一行记录的共享S锁，才可以读取这一行
（2）事务拿到某一行记录的排它X锁，才可以修改或者删除这一行
（1）多个事务可以拿到一把S锁，读读可以并行
（2）而只有一个事务可以拿到X锁，写写/读写必须互斥
共享/排它锁的潜在问题是，不能充分的并行，解决思路是数据多版本
（3）意向锁(Intention Locks)
意向锁，是一个表级别的锁，弱锁，仅仅表明意向
意向锁分为：
意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
意向锁之间并不相互互斥，而是可以并行
会与共享锁/排它锁互斥
事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁
事务要获得某些行的X锁，必须先获得表的IX锁
（4）插入意向锁(Insert Intention Locks)
插入意向锁，是间隙锁(Gap Locks)的一种
对已有数据行的修改与删除，必须加强互斥锁X锁
那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？
多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此，以提高插入并发
（5）记录锁(Record Locks)
封锁索引记录，对索引记录实施互斥，以保证数据一致性
select * from t where id=1 for update;
阻止其他事务插入，更新，删除id=1的这一行
（6）间隙锁(Gap Locks)
索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围
表中有四条记录：
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B
select * from t where id between 8 and 15 for update;
会封锁区间，以阻止其他事务比如id=10的记录插入
主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”
如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效
（7）临键锁(Next-key Locks)
记录锁与间隙锁的组合
封锁范围，既包含索引记录，又包含索引区间
临键锁会封锁索引记录本身，以及索引记录之前的区间。
在RR下有效，防止幻读；
如果一个会话占有了索引记录R的共享/排他锁，其他会话不能立刻在R之前的区间插入新的索引记录
子主题
小结
（1）InnoDB使用共享锁，可以提高读读并发；
（2）为了保证数据强一致，InnoDB使用强互斥锁，保证同一行记录修改与删除的串行性；
（3）InnoDB使用插入意向锁，可以提高插入并发；
InnoDB的细粒度锁，是实现在索引记录上的，如果查询没有命中索引，也将退化为表锁
InnoDB的索引有两类索引，聚集索引(Clustered Index)与普通索引(Secondary Index)
InnoDB的每一个表都会有聚集索引
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个非空unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
（2）如果表没有定义PK，则第一个非空unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；
0 条评论
下一页
