mysql
2021-03-20 14:23:12 2 举报AI智能生成
登录查看完整内容
MySQL是一个开源的关系型数据库管理系统，由瑞典的MySQL AB公司开发，现在属于Oracle公司。它使用结构化查询语言（SQL）作为访问和管理数据的主要方式。MySQL被广泛应用于各种类型的应用中，包括Web应用、服务器、嵌入式系统等。 MySQL具有高性能、稳定性强、易于使用和扩展等优点。它支持多种操作系统，如Windows、Linux、MacOS等。此外，MySQL还提供了丰富的功能，如事务处理、存储过程、触发器、视图等，以满足不同应用场景的需求。 总之，MySQL是一个功能强大、灵活且广泛使用的数据库管理系统，为开发者和企业提供了一个可靠、高效的数据存储和访问解决方案。
作者其他创作
大纲/内容
mysqlbug师姐整理
存储
Page结构
数据最小加载单位
页头
记录页面的控制信息，共56字节。包括页面的左右兄弟页指针、页面空间使用情况等
虚记录
最大虚记录：比页中最大主键还大
最小虚记录：比页中最小主键还小
所以能知道一个页的主键 范围
记录堆
行记录存储区。分为有效记录和已删除记录
自由空间链表
记录堆中删除数据后，地址用一个链表记录。这个链表就是自由空间链表
未分配空间
页面还未使用的存储空间
slot区
查找记录用的，是一个连续空间，相当于跳表。参考后面的页内查询
页尾
页面的最后一部分，共占8字节。主要存储页面的校验信息
页内记录维护
顺序保证
物理有序
物理地址是连续的，方便查找（如二分查找），但是插入时，如果不是顺序插入，则会不停移动数据
逻辑有序
用链表连接，没有数据移动，插入很快，但是查找很慢，不能进行偏移查找，只能遍历
物理有序太多限制，选择逻辑有序，现在需要优化查询
插入策略
可以先自由空间链表找位置插入
也可以从未分配空间插入
在加载page到内存中时，如果page中删到只余下一条记录，也会整个page加载到内存中，造成内存空洞所以插入时最好先插入到已删除的位置里
页内查询
遍历
二分查找
二分查找slot，然后再遍历子链表【类似跳表结构】
内存管理
预分配一块内存
内存不能现用现分配，分配内存耗时间
数据加载单位：页
一般数据前后挨着的数据也是大概率也要访问的，所以一次加载一页，以减少磁盘IO提高性能
内外存交换
内存没有磁盘大，内存满了需要移除一些数据，修改数据时要同步到磁盘里
名词解释
buffer pool
预分配的内存池
Page
buffer pool的最小单位
free list
空闲页组成的链表
flush list
脏页链表
page hash 表
维护内存page和文件page的映射关系
LRU
内存淘汰算法，最近最少使用原则
内存页管理
页面映射
页面数据管理
空闲页
没有数据的页，可以专门用一个链表记录哪些是空闲页
数据页
正常的数据页，也叫干净页
脏页
页内数据与磁盘内存不一致
上面几种页都可以用一个专门的数据结构来记录管理，比如链表
数据淘汰
内存页都被使用
需要加载新数据
页面加载
磁盘数据加载到内存：从磁盘加载到内存时，先尝试放进一个free page里，然后这个free page写了数据后链接到LRU old头，然后把映射关系写入到page hash中
没有空闲页后查找内存顺序
free list > LRU淘汰 > LRU flush【从LRU尾巴开始找脏页，将其刷盘淘汰直接放到free list】
页面淘汰
LRU尾部淘汰
Flush LRU淘汰
位置移动
old到new
innodb_old_blocks_time：old区存活时间大于此值时，有机会进入new区
new到old
LRU_new的操作
该操作是某个节点往表头移动
表头变动要加锁，需要减少移动次数
移动时机：当前淘汰页次数 - 上次移动header时的淘汰次数 > 1/4的LRU_new
缓冲池
存在位置
减少磁盘IO
大小设置
当分配给buffer pool的总内存小于1G时，则最多只能有一个buffer pool实例
当分配内存大于1G时，则可以自定义设置成多个buffer pool实例，推荐设置总大小为总内存的50%-60%
innodb_buffer_pool_size = 8589934592 // 分配8G内存
innodb_buffer_pool_instances = 4 // 设置4个buffer pool实例
预读
当顺序访问的数据页超过了innodb_read_ahead_threshold的阈值时，就会触发预读，即把下一个相邻区中的所有数据页都加载到内存中
当检测到已经在buffer pool 中有连续数据达到innodb_random_read_ahead的设置值后，就会去把剩余的数据页也加载到buffer pool里
预读失效：一次读取两数据页时，只有访问了一个数据页，另一个数据页没有人访问，这个没人访问的内存页可能会把历史的数据淘汰掉
缓冲池污染
SQL中执行了太多的查询，把之前的数据都淘汰了，查其他数据就会变得很慢
缓冲空间管理
使用LRU淘汰内存
least recently used
冷热数据分离
将数据分成两个LRU 链表，一部分为热数据区域5/8，一部分为冷数据区域3/8。由innodb_old_blocks_pct参数决定 ，默认37，即冷数据区占37%
当数据页第一次被加载到缓冲池中的时候，先将其放到冷数据区域的链表头部，1s后（由 innodb_old_blocks_time 参数控制） 该缓存页被访问了再将其移至热数据区域的链表头部
热数据区的后 3/4 部分被访问后才将其移动到链表头部去，对于前 1/4 部分的缓存页被访问了不会进行移动
优化
SQL语句优化
ON或USING的关联顺序中第二个表的关联列有索引
insert or update优化：批量插入、顺序插入
优化max和min：用order by 和limit优化
关联查询代替子查询，这个也不是绝对的
分页Limit offset：索引覆盖做“延迟关联\"
用UNION替换OR (仅适用于索引列)
or会导致索引失效
5.1之后，如果or的两列都是过引，则可以分别扫描再求并集
不在索引列上作计算
SQL参数数据类型与数据库一致
如在字符 类型上加引号，不然会因为类型转换导致索引失效
数据库类型是字符串，参数是数字，则要把数据的转成数字，索引失效数据库类型是数字，参数是字符串的，则把参数转成数字，还是会走索引
不使用前导模糊
索引列不允许为空时，索引列是否为空判断会导致索引失效
允许为空时，没事儿
不关心顺序时，尽量用order by NULL 避免group by的默认排序（从而避免了排序）
group by和order by，联合索引顺序不要打乱
组合索引中，等值查询的列放在前面，范围查询的列放在后面
排序分页重复问题
按某个字段排序时，由于该字段有一样的值，则在返回时发现一样时会随机返回，这样就可能不同页返回同样的数据
在排序字段后，再加一个条件列，比如id，相当于如果排序字段相同，则按id字段排序
执行计划（explain）
id
表明了执行查询的顺序。id相同，从上往下顺序执行，它们可认为是一组。id不同时(子查询)，id越大，执行优先级越高。id值如果为NULL则最后执行
select_type
simple
简单查询，不包含union和子查询
primary
查询包含子部分，最外层查询则被标记为主查询
union
union result
从UNION表获取结果的SELECT
subquery
SELECT或WHERE列表中包含了子查询
derived
FROM子查询被标记为DERIVED(衍生)MySQLfont color=\"#c41230\
table
涉及到哪些表。<derived6>指的是使用的衍生表，后面的数字表示的是explain的id号
type
访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system
整张表仅有一行，这是const类型的特例
const
通过索引一次就找到了，一般是主键索引和唯一索引
eq_ref
关联查询时右表主键上有唯一索引
ref
使用了非唯一性索引或者使用了唯一性索引的非唯一性前缀
fulltext
ref_or_null
index_merge
unique_subquery
index_subquery
range
利用索引范围查询，一般是有between、where子句里带有>的查询、in
index
索引物理文件全扫描
ALL
全表扫描，还要从磁盘读取数据
key
实际使用了哪个索引。如果该索引没有出现在possible_key中，则可能是用了索引覆盖
key_len
索引可能的最大长度
计算规则
字符类型 +
tinyint和char+1，smallint和varchar+2，int和float+4、long和bigint+8、datetime类型在5.6中字段长度是5个字节
varchar+2：有一个字节来记录这种变长的数据的长度到底是多少
本身长度 * 字符集 +
utf8占用3个字节，gbk占用2个字节，latin1占用1个字节
是否为null
为null+1(用于判断是否为空)，不为null+0
ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
filtered
返回结果的行占需要读到的行(rows列的值)的百分比
rows
可能要查询的行数
extra
Using filesort
文件排序：无法利用索引排序导致的
Using temporary
排序时用到了临时表，一般是group by 和order by
Using index
是否使用了索引覆盖
Using where
Using join buffer
使用了连接缓存
Impossible where
where条件是false，查不回结果如：where id=10 and id=12
Using index condition
利用了5.6版本以后的索引下推：先把索引过滤了，再回表，减少回表次数
比using where;using index效率低，where索引过滤的数据更多
锁
表锁
意向锁
意向共享锁/意向排他锁：用来说明接下来事务会加哪种锁，意向锁之间不冲突
自增锁
插入到有自增序列的表中时的锁：会有阻塞，所以最好批量生成主键
行锁
行锁分类
共享锁（读锁）
select 语句才有共享锁，sql语句最后加 lock in share mode
排他锁（写锁）
insert ，delete ， update (idu)默认加排他锁；select 语句需要用 for update加排他锁
注意：行锁作用在索引上，所以必须有索引，否则锁表
行锁3种实现方式
记录锁（record lock）：总是锁定索引记录。如果表没索引，就去锁定隐式的“聚集索引” 只能锁住行，所以在锁定范围时，先锁住行，再锁范围
间隙锁（gap-lock）：
间隙锁之间不冲突。唯一目的是防止其他事务插入间隙。只锁定一个范围【锁左不锁右】，不包含记录本身【加的排他锁】
关闭：innodb_locks_unsafe_for_binlog置为1
邻键锁（next-key lock)：(行锁的默认算法)
记录与间隙锁的组合，InnoDB对行的查询时用的锁，该锁会对相邻值的间隙也加上索引
降级情况
当查询未命中任何记录时，会降级为间隙锁
当使用主键或者唯一索引命中了一条记录时，会降级为记录锁
RR时用邻键锁防止幻读
锁非聚集索引时，也会锁相应的聚集索引；给聚集索引加锁时，也会给对应的非聚集索引加锁。注意：范围查询时，加锁顺序是一条数据加完锁之后，才会给下一条数据加锁
死锁
原因：两个事务同时分别对两个表或两行加锁的顺序不一致导致的
解决：回滚较小的那个事务
优化：尽可能相同顺序访问、尝试升级锁粒度
查看：innodb_lock_waits 和innodb_trx两张表里的信息
排查：找到两个事物ID，然后分析两个事务里的SQL
锁优化
尽量控制事务的大小，减少锁定的资源量、锁定时间
尽可能让所有检索都通过索引来完成
尽量使用较低级别的事务隔离
加锁分析
RC隔离级别，非唯一索引
加的记录锁，有幻读问题
RR隔离级别，非唯一索引
加上邻键锁，不止锁住了记录，也锁住了区间，解决了幻读问题
如delete user where name=peter，name没有索引时就会锁表，这时会一条一条的加锁，然后过滤，统一释放不需要加锁的数据行
RC隔离级别下，全表扫描加锁是先加锁，不符合条件的再释放掉
RR隔离级别下，全表扫描加锁时，还要对间隙加锁，然后才释放不符合条件的
MVCC
MVCC解决多版本并发控制问题【读写并发】，可重复读时读的是历史版本（undo log实现），每个版本里有事务ID和回滚指针（指向上一个版本）
MVCC只在RC和RR两个隔离级别有效
ReadView：创建快照读这一刻，包含该条数据的所有活跃事务ID的列表
只有事务ID小于ReadView的最小事务ID（说明被提交了），才可以被读到
RC在每一次进行普通SELECT操作前都会生成一个ReadView
RR只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都用这个ReadView
参考：https://mp.weixin.qq.com/s/SCW_3AypO-rSolMcjCxVtA
快照读
在RR时普通查询就是快照读
读的是历史版本
当前读
insert语句、update语句、delete语句、显示加锁的select语句【都加排他锁】
读的是当前版本，且会将返回的数据加锁，保证别的事务不会并发操作该记录
事务
事务特性
原子性（ atomicity）
一致性（consistency）
由AID保证
隔离性（isolation）
持久性（durability）
事务隔离级别
读未提交（READ UNCOMMITED）
可能产生脏读
读提交 （READ COMMITED）
可能产生不可重复：针对同一条数据不可重复读【锁行解决】
RC隔离级除了外键约束和唯一性约束会加间隙锁，其余都是加记录锁所以在RC下，是没有临键锁的
可重复读（REPEATABLE READ）默认
可能产生幻读：针对范围【锁表可解决】
幻读：事务在插入时，明明已经检查过如某个主键不存在记录时，后面再操作却发现这些数据已经存在了
mysql通过邻键锁，解决了幻读的问题【只解决了在两次当前读里的幻读】
但是，当一个事务A查询后，另外一个事务B修改了数据提交后，事务A再作更新，此条数据readview里的事务ID变成了A的ID那么事务A就能读到事务B的提交，所以这种幻读没解决
当事务A读到目前主键是3，本应该插入4了，但是由于事务B此时插入了主键为4的记录，当事务A插入时就发现主键冲突了。这种幻读也没解决
可串行化（SERIALIZABLE）
三大范式
解决问题
减少数据冗余、优化存储、增删改快
缺点
关联表太多，查询慢；难以索引优化
反范式
允许少量字段冗余，提升查询性能
慢查询
相关命令
slow_query_log
启动停止技术慢查询日志
slow_query_log_file
指定慢查询日 志得存储路径及文件（默认和数据文件放一起）
long_query_time
指定记录慢查询日 志 SQL 执行时间得伐值（单位： 秒， 默认 10 秒）
log_queries_not_using_indexes
是否记录未使用索引 的 SQL
log_output
分析工具
MysqlDumpSlow
pt_query_digest
定义
内存数据页和磁盘数据页不一致时，那么称这个内存数据页为脏页
产生原因
数据插入时，为了效率会优先插入顺序写的redo log日志中，后面才会做随机写的操作来写入磁盘
redo log还未同步到磁盘时，就会产生脏页
注：redo log日志也是写到磁盘，因为是顺序读写所以快
影响
在缓存淘汰时，如果淘汰的是脏页，则需要将脏页的数据刷到磁盘中，这个过程较慢
redo log满了之后刷盘比较慢
当顺序访问的数据页超过了innodb_read_ahead_threshold的阈值时，就会触发预读，即把下一个相邻区中的所有数据页都加载到会存中
将数据分成两个LRU 链表，一部分为热数据区域，一部分为冷数据区域。由innodb_old_blocks_pct参数决定 ，默认37，即冷数据区占37%
主从延迟
主从复制原理
在主从同步的过程中，主库会将所有的操作事件记录在 binlog 中，从库通过开启一个 I/O 线程保持与主库的通信，并在一定时间间隔内探测 binlog 日志文件是否发生改变。如果 binlog 日志发生了变化，主库生成一个 binlog dump 线程向从库 I/O 线程传送 binlog。从库上的 I/O 线程将 binlog 复制到自己的 relay log 中。最终由从库中的 SQL 线程读取 relay log 中的事件重放到从库上
主从延迟原因
从库重放到磁盘时已经过了好一会儿了
主库并发太高
从库的查询有锁等待
解决办法
5.6后从库的重放时，是多线程了
降低并发，如加分布式锁防止大量访问
实时性要求高的，直接读主库
各种日志
更新过程
binlog是server层产生的
undo log
通过在提交事务前备份历史数据到磁盘的方式实现事务的原子性和MVCC，也实现了持久化。缺点是多写了一份数据到磁盘
它是回滚日志，该日志性能较低，于是有了下面的redolog
作用
回滚日志、保证事务原子性、实现数据多版本MVCC，
delete undo log：用于回滚、提交即清理
update undo log：用于回滚，同时实现快照读，不能随便删除
清理：比活跃事务表readview的最小事务ID还小的事务提交 的就可以删除
redo log
redo log又称重做日志文件，记录的是每个页的修改，用于记录事务操作的变化
redo log 是 InnoDB 引擎特有的，以512字节为单位块存储
它是物理日志，记录的是数据页的物理修改，它是末尾追加顺序写，很快且是持久化的
数据库掉电，InnoDB存储引擎会使用redo log恢复到掉电前的时刻，以此来保证数据的完整性
把数据页从磁盘到缓冲池来修改后同步回磁盘是随机IO比较慢，如 果服务 宕机则数据丢失；如果在同步回磁盘前先以顺序 IO同步到redolog中，如果发生宕机，则可以从redolog恢复
记录页的修改，事务状态为prepare。用于异常时恢复事务
记录commit，事务状态为commit状态
循环写文件
write pos ：记录写入位置
check point：事务提交点，即刷盘位置
check point 和 write pos之间的是待落盘数据
刷盘时机
默认1。一般我们用2就行，先写到系统的buffer中，只要系统未挂就行
binlog
记录了所有增、删、改操作，二进制形式；主同步给从的时候就用的该日志
只在事务提交完成后进行一次写入
binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
binlog有两种模式，statement 格式是记sql语句， row格式是记录行的内容，记两条，更新前和更新后都有
redo和undo 区别
undolog记录的是修改的各历史版本（MVCC就要用到)
redolog记录的是事务操作后的最新数据结果，作用是持久化事务，在mysql故障时，如果事务有未完成的，重启后可通过redolog重新完成事务
表设计实践
Ø 数据库字符集使用utf8mb4; Ø VARCHAR按实际需要分配长度；Ø 文本字段建议使用VARCHAR；Ø 时间字段建议使用long； Ø bool字段建议使用tinyint； Ø 枚举字段建议使用tinyint； Ø 交易金额建议使用long；
0 条评论
回复 删除
下一页
