数据库
2023-05-09 15:04:12 0 举报
AI智能生成
数据库脑图
作者其他创作
大纲/内容
交叉连接,计算笛卡儿积
CROSS JOIN
内连接,返回满足条件的记录
INNER JOIN
返回左表所有行,右表不存在补NULL
LEFT
返回右表所有行,左边不存在补NULL
RIGHT
返回左表和右表的并集,不存在一边补NULL
FULL
OUTER JOIN
自连接,将表查询时候命名不同的别名。
SELF JOIN
JOIN
SQL
tidb
同一时间只占用一个 CPU,只能有一个指令在运行,并行读写是不存在的。
单线程
缓存、限流、分布式锁、分布式session
string类型是二进制安全的。意思是redis的string可以包含任何数据。比如jpg图片或者序列化的对象string类型是Redis最基本的数据类型,一个键最大能存储512MB。
String(字符串)
用户信息
时间轴、简单队列
List类型是按照插入顺序排序的字符串链表(所以它这里的list指的相当于java中的linkesdlist)
List类型
赞、踩
Set类型看作为没有排序的字符集合。如果多次添加相同元素,Set中将仅保留该元素的一份拷贝。
set类型
排行榜
Sorted-Sets中的每一个成员都会有一个分数(score)与之关联
Sorted-Sets类型
位图
HLL
GEO(地理信息)
链表
字典
跳跃表
设计
跳跃列表(skipList)
底层结构
数据结构
xadd mystream * name zhangsan age 18;
添加消息
xgroup create mystream cg1 $
创建消费组
xreadgroup group cg1 godconsumer streams mystream >
消费消息
xack mystream cg1 1636612497298-0
ACK
XPENDING mystream cg1
查询未消费消息
消费组,使用 XGROUP CREATE 命令创建,一个消费组有多个消费者(Consumer)。
Consumer Group
游标,每个消费组会有个游标 last_delivered_id,任意一个消费者读取了消息都会使游标 last_delivered_id 往前移动。
last_delivered_id
消费者(Consumer)的状态变量,作用是维护消费者的未确认的 id。 pending_ids 记录了当前已经被客户端读取的消息,但是还没有 ack (Acknowledge character:确认字符)。
pending_ids
stream
处理客户端请求时,不会阻塞主线程;Redis 单纯执行(大多数指令)一个指令不到 1 微秒[4],如此,单核 CPU 一秒就能处理 1 百万个指令(大概对应着几十万个请求吧),用不着实现多线程(网络才是瓶颈)。
多路 I/O 复用机制
使用操作系统提供的虚拟内存来存储数据
这段时间,redis 是无法处理请求的。
RDB 全量持久化
AOF 增量持久化
持久化
纯文本格式(redis文本协议的流水记录),加载的过程相当于历史重放
aof(日志文件)
二进制格式,直接进行加载,所以一般情况下rdb数据加载会比aof加载快
rdb(快照文件)
replication(主从复制)
数据迁移方式
First In First Out,先进先出。
FIFO 淘汰最早数据
Least Recently Used,最近最少使用。
LRU 剔除最近最少使用
Least Frequently Used,最不经常使用。
LFU 剔除最近使用频率最低
noeviction:返回错误当内存限制达到并且客户端尝试执行会让更多内存被使用的命令(大部分的写入指令,但DEL和几个例外)
allkeys-lru: 尝试回收最少使用的键(LRU),使得新添加的数据有空间存放。
allkeys-random: 回收随机的键使得新添加的数据有空间存放。
volatile-random: 回收随机的键使得新添加的数据有空间存放,但仅限于在过期集合的键。
指令
淘汰策略
redis默认是每隔 100ms 就随机抽取一些设置了过期时间的key,检查其是否过期,如果过期就删除。注意这里是随机抽取的。为什么要随机呢?你想一想假如 redis 存了几十万个 key ,每隔100ms就遍历所有的设置过期时间的 key 的话,就会给 CPU 带来很大的负载!
定期删除
定期删除可能会导致很多过期 key 到了时间并没有被删除掉。所以就有了惰性删除。假如你的过期 key,靠定期删除没有被删除掉,还停留在内存里,除非你的系统去查一下那个 key,才会被redis给删除掉。这就是所谓的惰性删除,也是够懒的哈
惰性删除
删除策略
Sentine l哨兵
高可用
异步补偿
数据不一致
跳表
事务提供了一种将多个命令请求打包
批量操作在发送 EXEC 命令前被放入队列缓存
Lua脚本
不保证原子性
没有隔离级别
且没有回滚
multi : 标记一个事务块的开始( queued )
exec : 执行所有事务块的命令 ( 一旦执行exec后,之前加的监控锁都会被取消掉 )
discard : 取消事务,放弃事务块中的所有命令
unwatch : 取消watch对所有key的监控
命令
事务
多套环境
redlock 红锁
redis
Tair
neo4j
nosql
Binary log
解释器
全表扫描(顺序扫描(sequential scan))
索引扫描
优化器
服务层
核心服务
引擎事务日志
存储引擎
系统文件层
逻辑架构
主键ID
事务ID
回滚指针
系统内部字段
trx_sys->mysql_trx_list 包含了所有用户线程的事务对象,即使是未开启的事务对象,只要还没被回收到trx_pool中,都被放在该链表上。当session断开时,事务对象从链表上摘取,并被回收到trx_pool中,以待重用。
trx_sys->rw_trx_list 读写事务链表,当开启一个读写事务,或者事务模式转换成读写模式时,会将当前事务加入到读写事务链表中,链表上的事务是按照trx_t::id有序的;在事务提交阶段将其从读写事务链表上移除。
trx_sys->serialisation_list 序列化事务链表,在事务提交阶段,需要先将事务的undo状态设置为完成,在这之前,获得一个全局序列号trx->no,从trx_sys->max_trx_id中分配,并将当前事务加入到该链表中。随后更新undo等一系列操作后,因此进入提交阶段的事务并不是trx->id有序的,而是根据trx->no排序。当完成undo更新等操作后,再将事务对象同时从serialisation_list和rw_trx_list上移除。
trx_sys->rw_trx_ids 记录了当前活跃的读写事务ID集合,主要用于构建ReadView时快速拷贝一个快照
trx_sys->rw_trx_set 这是的映射集合,根据trx_id排序,用于通过trx_id快速获得对应的事务对象。一个主要的用途就是用于隐式锁转换,需要为记录中的事务id所对应的事务对象创建记录锁,通过该集合可以快速获得事务对象
集合
事务链表和集合
trx_sys
在Innodb中,每次开启一个事务时,都会为该session分配一个事务对象。而为了对全局所有的事务进行控制和协调,有一个全局对象trx_sys,对trx_sys相关成员的操作需要trx_sys->mutex锁。
Innodb使用一种称做ReadView(视图)的对象来判断事务的可见性(也就是ACID中的隔离性)
对于普通的读写事务,总是为其指定一个回滚段(默认128个回滚段)。而对于只读事务,如果使用到了InnoDB临时表,则为其分配(1~32)号回滚段。(回滚段指定参阅函数trx_assign_rseg_low)
当为事务指定了回滚段后,后续在事务需要写undo页时,就从该回滚段上分别分配两个slot,一个用于update_undo,一个用于insert_undo。分别处理的原因是事务提交后,update_undo需要purge线程来进行回收,而insert_undo则可以直接被重利用。
事务回滚段
Innodb的多版本数据使用UNDO来维护的,例如聚集索引记录(1) =>(2)=>(3),从1更新成2,再更新成3,就会产生两条undo记录。
原理
事务锁 维护在不同的Isolation level下数据库的Atomicity和Consistency两大基本特性。
一类是mutex,实现内存结构的串行化访问
一类是rw lock,实现读写阻塞,读读并发的访问的读写锁
内存锁 为了维护内存结构的一致性,比如Dictionary cache、sync array、trx system等结构。 InnoDB并没有直接使用glibc提供的库,而是自己封装了两类:
undo log
Atomicity(原子性)
保证数据页的准确性
doublewrite buffer
保证恢复时能够将所有的变更apply到数据
崩溃恢复时存在还未提交的事务,那么根据XA规则提交或者回滚事务
crash recovery
Consistency (一致性)
事务A读到了事务B还没有提交的数据
READ-UNCOMMITTED 在该隔离级别下会读到未提交事务所产生的数据更改,这意味着可以读到脏数据,实际上你可以从函数row_search_mvcc中发现,当从btree读到一条记录后,如果隔离级别设置成READ-UNCOMMITTED,根本不会去检查可见性或是查看老版本。这意味着,即使在同一条SQL中,也可能读到不一致的数据。
脏读
读未提交(READ UNCOMMITED)
在一个事务里面读取了两次某个数据,读出来的数据不一致
每次select都生成一个快照读
READ-COMMITTED 在该隔离级别下,可以在SQL级别做到一致性读,当事务中的SQL执行完成时,ReadView被立刻释放了,在执行下一条SQL时再重建ReadView。这意味着如果两次查询之间有别的事务提交了,是可以读到不一致的数据的。
不可重复读
读已提交(READ COMMITTED)
事务后第一个select语句才是快照读的地方
并发的事务中有事务发生了插入、删除操作
在一个事务里面的操作中发现了未被操作的数据
行锁只能锁住行,即使把所有的行记录都上锁,也阻止不了新插入的记录。
幻读(当前读)
REPEATABLE-READ 可重复读和READ-COMMITTED的不同之处在于,当第一次创建ReadView后(例如事务内执行的第一条SEELCT语句),这个视图就会一直维持到事务结束。也就是说,在事务执行期间的可见性判断不会发生变化,从而实现了事务内的可重复读。
可重复读(REPEATABLE READ)
序列化(SERIALIZABLE)
Isolation(隔离性)
WAL(Write-Ahead Logging)的原则
Redo log和数据页都做了checksum校验
为了解决半写的问题,即写一半数据页时实例crash,这时候数据页是损坏的。InnoDB使用double write buffer来解决这个问题,在写数据页到用户表空间之前,总是先持久化到double write buffer,这样即使没有完整写页,我们也可以从double write buffer中将其恢复出来。你可以通过innodb_doublewrite选项来开启或者关闭该特性。
Durability(持久性)
事务特性
只读事务
读写事务
读写事务并不意味着一定在引擎层就被认定为读写事务了,5.7版本InnoDB里总是默认一个事务开启时的状态为只读的。举个简单的例子,如果你事务的第一条SQL是只读查询,那么在InnoDB层,它的事务状态就是只读的,如果第二条SQL是更新操作,就将事务转换成读写模式。
BEGIN
当Server层接受到任何数据更改的SQL时,都会直接拒绝请求,返回错误码ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION,不会进入引擎层。
这个选项可以强约束一个事务为只读的,而只读事务在引擎层可以走优化过的逻辑,相比读写事务的开销更小,例如不用分配事务id、不用分配回滚段、不用维护到全局事务链表中。
START TRANSACTION READ ONLY
START TRANSACTION READ WRITE
innobase_start_trx_and_assign_read_view
这里会进入InnoDB层,调用函数innobase_start_trx_and_assign_read_view,注意只有你的隔离级别设置成REPEATABLE READ(可重复读)时,才会显式开启一个Read View
START TRANSACTION WITH CONSISTENT SNAPSHOT
无需显式开启事务,如果你执行多条SQL但不显式的调用COMMIT(或者执行会引起隐式提交的SQL)进行提交,事务将一直存在
AUTOCOMMIT = 0
事务开启
事务类型
回滚的方式是提取undo日志,做逆向操作
InnoDB的undo是单独写在表空间中的,本质上和普通的数据页是一样的。如果在事务回滚时,undo页已经被从内存淘汰,回滚操作(特别是大事务变更回滚)就可能伴随大量的磁盘IO。因此InnoDB的回滚效率非常低
事务回滚
Server层的MDL锁模块,维持了一个事务过程中所有涉及到的表级锁对象。通过MDL锁,可以堵塞DDL,避免DDL和DML记录binlog乱序
InnoDB的trx_sys子系统,维持了所有的事务状态,包括活跃事务、非活跃事务对象、读写事务链表、负责分配事务id、回滚段、Readview等信息,是事务系统的总控模块;
InnoDB的lock_sys子系统,维护事务锁信息,用于对修改数据操作做并发控制,保证了在一个事务中被修改的记录,不可以被另外一个事务修改;
InnoDB的log_sys子系统,负责事务redo日志管理模块;
InnoDB的purge_sys子系统,则主要用于在事务提交后,进行垃圾回收,以及数据页的无效数据清理。
在InnoDB中一直维持了一个不断递增的整数,存储在trx_sys->max_trx_id中;每次开启一个新的读写事务时,都将该ID分配给事务,同时递增全局计数。事务ID可以看做一个事务的唯一标识。
事务管理执行
回滚段undo是实现InnoDB MVCC的根基。每次修改聚集索引页上的记录时,变更之前的记录都会写到undo日志中。回滚段指针包括undo log所在的回滚段ID、日志所在的page no、以及page内的偏移量,可以据此找到最近一次修改之前的undo记录,而每条Undo记录又能再次找到之前的变更。
undo日志
ReadView::id 创建该视图的事务ID;
ReadView::m_ids 创建ReadView时,活跃的读写事务ID数组,有序存储;
ReadView::m_low_limit_id 设置为当前最大事务ID;
ReadView::m_up_limit_id m_ids集合中的最小值,如果m_ids集合为空,表示当前没有活跃读写事务,则设置为当前最大事务ID。
Readview
快照读
加排他锁
间隙锁
1.对主键或唯一索引,如果当前读时,where条件全部精确命中(=或者in),这种场景本身就不会出现幻读,所以只会加行记录锁。
2.没有索引的列,当前读操作时,会加全表gap锁,生产环境要注意
3.非唯一索引列,如果where条件部分命中(>、<、like等)或者全未命中,则会加附近Gap间隙锁。
update ... (更新操作)
delete ... (删除操作)
insert ... (插入操作)
select ... lock in share mode (共享读锁)
select ... for update (写锁)
场景
当前读
聚集索引
二级索引
可见性判断
多版本并发控制MVCC
GTID
数据库事务
LOCK_X(排他锁)
LOCK_S(共享锁)
意向共享锁( Intent share lock,简称IS锁)
意向排它锁( Intent Exclusive lock,简称IX锁)
表锁
锁带上这个 FLAG 时,表示这个锁对象只是单纯的锁在记录上,不会锁记录之前的 GAP。在 RC 隔离级别下一般加的都是该类型的记录锁(但唯一二级索引上的 duplicate key 检查除外,总是加 LOCK_ORDINARY 类型的锁)。
LOCK_REC_NOT_GAP
记录锁(RK)
表示只锁住一段范围,不锁记录本身,通常表示两个索引记录之间,或者索引上的第一条记录之前,或者最后一条记录之后的锁。可以理解为一种区间锁,一般在RR隔离级别下会使用到GAP锁。
间隙锁(GK) LOCK_GAP
类型
而NEXT-KEY LOCK正是为了解决RR隔离级别下的幻读问题。所谓幻读就是一个事务内执行相同的查询,会看到不同的行记录。在RR隔离级别下这是不允许的。
LOCK_ORDINARY(Next-Key Lock)
插入意向锁(IK) LOCK_INSERT_INTENTION(插入意向锁)
行锁
InnoDB 通常对插入操作无需加锁,而是通过一种“隐式锁”的方式来解决冲突。聚集索引记录中存储了事务id,如果另外有个session查询到了这条记录,会去判断该记录对应的事务id是否属于一个活跃的事务,并协助这个事务创建一个记录锁,然后将自己置于等待队列中。该设计的思路是基于大多数情况下新插入的记录不会立刻被别的线程并发修改,而创建锁的开销是比较昂贵的,涉及到全局资源的竞争。
隐式锁
锁
16k
Row size too large (> 8126).
每个page至少要包含两个记录
最小存储单元
页
自增ID可以保证每次插入时B+索引是从右边扩展的,可以避免B+树和频繁合并和分裂(对比使用UUID)。如果使用字符串主键和随机主键,会使得数据随机插入,效率比较差。
推荐自增 id 做主键
6字节
指针
768字节的时候,剩余部分会保存在另外的页面(off-page)
64K
如果我们想在创建的表的时候,保证创建的表中的text字段都能安全的达到64k上限(而不是等插入的时候才发现),那么需要将默认为OFF的innodb_strict_mode设置为ON,这样在建表时会先做判断
text
一般情况下,当我们需要读入一个Page时,首先根据space id 和page no找到对应的buffer pool instance。然后查询page hash,如果page hash中没有,则表示需要从磁盘读取。在读盘前首先我们需要为即将读入内存的数据页分配一个空闲的block。当free list上存在空闲的block时,可以直接从free list上摘取;
流程
我们知道InnoDB使用buffer pool来缓存从磁盘读取到内存的数据页。buffer pool通常由数个内存块加上一组控制结构体对象组成
buffer pool
对于 VARCHAR、VARBINARY、BLOB、TEXT 这类可变长字段,如果数据长度过长,会将其单独存储在主索引记录之外的 LOB page 上(从主索引所属的 tablespace 上分配)
InnoDB 将一个 LOB 字段直接存储在多个 LOB page 中,这些 LOB page 组成一个单向链表
随机访问 LOB 中的数据是非常低效的
8.0前
LOB index 来索引 LOB page
在 LOB 字段更新了部分数据后,user tablespace 中还是只有一个 LOB,更新操作只会修改部分 LOB data page,并且 LOB 中同时存在被修改 LOB data page 的多个版本
MVCC
8.0
LOB
存储
记录sql
优点:不需要记录每一行的变化,减少了binlog日志量,节约了IO,提高性能。(相比row能节约多少性能与日志量,这个取决于应用的SQL情况,正常同一条记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量,但是考虑到如果带条件的update操作,以及整表删除,alter表等操作,
Statement
不记录sql语句上下文相关信息,仅保存哪条记录被修改。
ROW格式会产生大量日志,因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况,其所产生的日志量会增加多少,以及带来的IO性能问题。)
Row
是以上两种level的混合使用,一般的语句修改使用statment格式保存binlog
有数据一致性问题走Row
Mixed
格式
【Log_name】:日志名称
【pos】上次操作点的序号
TABLE_MAP_EVENT
代表binlog日志的第一条,且只会在第一次出现
FORMAT_DESCRIPTION_EVENT
实测 没有 换文件 也会有该事件
当MySQL切换至新的binlog文件的时候,MySQL会在旧的binlog文件中写入一个ROTATE_EVENT,表示新的binlog文件的文件名
ROTATE_EVENT
记录更新操作的语句
QUERY_EVENT
在开启ROW模式记录的binlog文件中,WRITE_ROWS_EVENT记录了插入的行记录。
WRITE_ROWS_EVENT
在开启ROW模式记录的binlog文件,UPDATE_ROWS_EVENT记录了更新的行记录。
UPDATE_ROWS_EVENT
在开启ROW模式记录的binlog文件,DELETE_ROWS_EVENT记录了删除的行记录。
DELETE_ROWS_EVENT
ROWS_EVENT
当事务提交时,无论哪种模式都会记录。
XID_EVENT
在启用GTID模式(主从复制模式)后,MySQL将为每个事务都分配了个GTID。
GTID_LOG_EVENT
开启GTID模式(主从复制)后,每个binlog开头都会有一个PREVIOUS_GTIDS_LOG_EVENT(在后简称PE)事件,它的值是上一个binlog的PE+GTID信息。
PREVIOUS_GTIDS_LOG_EVENT
当MySQL数据库停止时,会在当前的binlog末尾添加一个STOP_EVENT事件表示数据库停止。
STOP_EVENT
【event_type】事件类型
show binlog events;
查看
# at 523 #210405 21:08:18 server id 1 end_log_pos 639 Query thread_id=13 exec_time=0 error_code=0 SET TIMESTAMP=1617628098/*!*/; update user set username='zhangqian' where uid = '15' /*!*/;
at后面的数字代表在binlog日志文件的第几个字节开始(at 523 )
postion(描述)
即第二行的(#210405 21:08:18)
timestamp(事件发生的时间戳)
(1),代表执行的主机编号
server id(服务器标识)
结束的字节位置639
end_log_pos(结束字节数)
事件类型。
Query(类型)
处理的线程编号(13)
thread_id
执行花费的时间
exec_time
错误码
error_code
SET TIMESTAMP=1617628098/*!*/;代表执行的时间戳
update user set username='zhangqian' where uid = '15' 代表执行的语句,遇到下一个#at 则为下一个binlog日志事件。
日志解析
将会清空00000X之前的所有日志文件.
purge master logs to "binlog_name.00000X"
(2)使用命令
(3) 使用--expire_logs_days=N选项指定过了多少天日志自动过期清空。
删除binlog
binlog
512字节
不管是log buffer中还是os buffer中以及redo log file on disk中,都是这样以512字节的块存储的
12字节
日志头
492字节
日志体
8字节
日志尾
组成
redo log block
redo
旧版本数据读取
purge时扫描undo log record
崩溃恢复时扫描undo log record
大事务回滚
作用
undo log segment
rollback segment称为回滚段,每个回滚段中有1024个undo log segment。
回滚
undo
备库通过二进制日志重放主库提交的事务,而主库binlog写入在commit之前,如果写完binlog主库crash,再次启动时会回滚事务。但此时从库已经执行,则会造成主备数据不一致
–自动为每个事务分配一个唯一的ID(XID)–COMMIT会被自动的分成Prepare和Commit两个阶段。– Binlog会被当做事务协调者(Transaction Coordinator),Binlog Event会被当做协调者日志。
若关闭了binlog,且存在不止一种事务引擎时,则XA控制对象为tc_log_mmap;
若打开binlog,且使用了事务引擎,则XA控制对象为mysql_bin_log;
其他情况,使用tc_log_dummy,这种场景下就没有什么XA可言了,无需任何协调者来进行XA。
隐式XA
显式XA
XA规范主要定义了(全局)事务管理器(TM)和(局 部)资源管理器(RM)之间的接口
两种XA事务方式
XA事务(内部分布式事物)
崩溃提交或回退
binlog 与 事务日志一致性
prepare_commit_mutex锁以串行的方式来保证MySQL数据库上层二进制日志和Innodb存储引擎层的事务提交顺序一致,然后会导致组提交(group commit)特性无法生效
5.6前
引入队列机制保证Innodb commit顺序与binlog落盘顺序一致,并将事务分组,组内的binlog刷盘动作交给一个事务进行,实现组提交目的。在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中,队列中的第一个事务称为leader,其他事务称为follow,leader控制着follow的行为。
BLGC(Binary Log Group Commit),并把事务提交过程分成三个阶段,Flush stage、Sync stage、Commit stage。
5.6
binlog与 redolog的顺序性
事务日志
日志
刷新binlog_cache中的信息到磁盘由os决定。
0
每N次事务提交刷新binlog_cache中的信息到磁盘。
n
sync_binlog
它控制了重做日志(redo log)的写盘和落盘策略
事务安全
事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中
1
OS crash可能丢失
每次提交都仅写入到os buffer,然后是每秒调用fsync()将os buffer中的日志写入到log file on disk
2
mysqld 进程crash可能丢失
事务提交时不会将log buffer中日志写入到os buffer,而是每秒写入os buffer并调用fsync()写入到log file on disk中
innodb_flush_log_at_trx_commit
“双1”,是保证CrashSafe的根本。
参数
InnoDB
MyISAM
InnoDB支持事务,MyISAM不支持
InnoDB支持外键,而MyISAM不支持
InnoDB是聚集索引,使用B+Tree作为索引结构,数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构),必须要有主键,通过主键索引效率很高。但是辅助索引需要两次查询,先查询到主键,然后再通过主键查询到数据。因此,主键不应该过大,因为主键太大,其他索引也都会很大。
MyISAM是非聚集索引,也是使用B+Tree作为索引结构,索引和数据文件是分离的,索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键),而Myisam可以没有
InnoDB的B+树主键索引的叶子节点就是数据文件,辅助索引的叶子节点是主键的值;而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。
因为InnoDB的事务特性
InnoDB不保存表的具体行数,执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快(注意不能加有任何WHERE条件);
InnoDB的行锁是实现在索引上的,而不是锁在物理行记录上。潜台词是,如果访问没有命中索引,也无法使用行锁,将要退化为表锁。
InnoDB支持表、行(默认)级锁,而MyISAM支持表级锁
区别
引擎
select
from
Simple Nested-Loop Join
驱动表会根据关联字段的索引进行查找,当在索引上找到了符合的值,再回表进行查询,也就是只有当匹配到索引以后才会进行回表。至于驱动表的选择,MySQL优化器一般情况下是会选择记录数少的作为驱动表,但是当SQL特别复杂的时候不排除会出现错误选择。在索引嵌套链接的方式下,如果非驱动表的关联键是主键的话,这样来说性能就会非常的高,如果不是主键的话,关联起来如果返回的行数很多的话,效率就会特别的低,因为要多次的回表操作。先关联索引,然后根据二级索引的主键ID进行回表的操作。这样来说的话性能相对就会很差。
Index Nested-Loop Join
最先介绍的Simple Nested-Loop Join算法,而是会优先使用Block Nested-Loop Join的算法。Block Nested-Loop Join对比Simple Nested-Loop Join多了一个中间处理的过程,也就是join buffer,使用join buffer将驱动表的查询JOIN相关列都给缓冲到了JOIN BUFFER当中,然后批量与非驱动表进行比较,这也来实现的话,可以将多次比较合并到一次,降低了非驱动表的访问频率。也就是只需要访问一次S表。这样来说的话,就不会出现多次访问非驱动表的情况了,也只有这种情况下才会访问join buffer。在MySQL当中,我们可以通过参数join_buffer_size来设置join buffer的值,然后再进行操作。默认情况下join_buffer_size=256K,在查找的时候MySQL会将所有的需要的列缓存到join buffer当中,包括select的列,而不是仅仅只缓存关联列。在一个有N个JOIN关联的SQL当中会在执行时候分配N-1个join buffer。
Block Nested-Loop Join
Nested-Loop Join(嵌套循环链接),不像其他商业数据库可以支持哈希链接和合并连接
Sort Merge Join
Hash Join
join
where
order by
limit
GROUP BY 条件字段需要符合最左前缀索引;
在使用GROUP BY 的同时,只能使用 MAX 和 MIN 这两个聚合函数;
如果引用到了该索引中 GROUP BY 条件之外的字段条件的时候,必须以常量形式存在;
使用松散(Loose)索引扫描实现 GROUP BY
使用紧凑(Tight)索引扫描实现 GROUP BY
使用临时表实现 GROUP BY
group by
having
CTE的主要思想就先生成临时结果集,以方便后面的使用;与临时表不同的是这个结果集的作用域不是当前session而是当前语句,对!不是 session级是语句级别的
MySQL8的新特性CTE
with
parttion by
并发死锁
Replace into
sql
指定表中的一列或者几列的组合的值在表中不能出现空值和重复值
主键约束
某一列或多个列不能有相同的两行或者两行以上的数据存在,和主键约束不同,唯一约束允许为NULL,只是只能有一行
唯一约束
非空约束
外键约束
约束
可以为NULL
唯一索引
索引
Sending data
Writing to net
state
show processlist
修改配置
用户量小
修改数据库连接池check连接时间
用户量大
8小时问题
wait_timeout
select * from information_schema.INNODB_TRX
查看事务
状态检查
•表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
•尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
•使用枚举或整数代替字符串类型
•尽量使用TIMESTAMP而非DATETIME
•单表不要有太多字段,建议在20以内
•用整型来存IP
•应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
•字符字段只建前缀索引
•字符字段最好不要做主键
•不用外键,由程序保证约束
•尽量不用UNIQUE,由程序保证约束
•使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引
选择数据结构
当where条件和order by同时出现时,如果where中字段A使用了索引,而order by的字段是B,查看执行计划时,就会出现filesort文件排序
建立一个包含 WHERE 和 ORDER BY 条件的混合索引
优化
速度2倍
parttion
窗口函数
隐藏索引
降序索引
在复杂的查询中使用嵌入式表时,使用 CTE 使得查询语句更清晰。
通用表表达式(Common Table Expressions CTE)
InnoDB 现在支持表 DDL 的原子性
可靠性
8.X新特性
分页对sql要求高
流式查询
创建影子表、执行alert
拷贝数据、追平binlog
ghost方案
基于触发器
percona
percona方案
DDL锁表(ALERT语句)
生产问题处理
mysql
数据库
0 条评论
回复 删除
下一页