mysql

2021-03-20 14:23:12   2  举报





AI智能生成

MySQL是一个开源的关系型数据库管理系统，由瑞典的MySQL AB公司开发，现在属于Oracle公司。它使用结构化查询语言（SQL）作为访问和管理数据的主要方式。MySQL被广泛应用于各种类型的应用中，包括Web应用、服务器、嵌入式系统等。 MySQL具有高性能、稳定性强、易于使用和扩展等优点。它支持多种操作系统，如Windows、Linux、MacOS等。此外，MySQL还提供了丰富的功能，如事务处理、存储过程、触发器、视图等，以满足不同应用场景的需求。总之，MySQL是一个功能强大、灵活且广泛使用的数据库管理系统，为开发者和企业提供了一个可靠、高效的数据存储和访问解决方案。

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值如果为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