MySQL

Eric_go
182 阅读36分钟

mysql 体系结构

MYSQL体系结构.png

mysql 架构图

MYSQL架构图.png

  1. 应用程序(tomcat) 是支持,多客户端访问的.必然是多线程访问.

  2. 应用程序维护一个数据库连接池(线程池)来与mysql交互.

  3. mysql也维护一个线程池来连接应用(如果不使用线程池,而是使用单线程则每次只能连接一个客户端.上一次客户端未断开链接时下一个客户端不能链接)

    对于每一个连接，mysql 都有一个线程状态来标识，这个连接在做什么。

    show processlist; 查询线程状态，root 用户可以看到所有线程，其他用户只能看到自己权限内线程。

    • id 线程 id ，可以使用kill xx

    • user 启动这个线程的用户

    • Host 发送请求的客户端 IP 和 port 端口

    • db 当前命令在哪个库执行

    • Command 当前线程执行的命令

      • Create DB 正在创建数据库
      • Drop DB 正在删除数据库
      • Execute 正在执行 PreparedStatement
      • Close Stmt 正在关闭 PreparedStatement
      • Query 正在执行语句
      • Sleep 正在等待客户端发送语句
      • Quit 正在退出
      • Shutdown 正在关闭服务器

    • Time 线程处于当前状态的时长 单位秒

    • State 线程状态

      • Updating 正在搜索匹配记录，进行修改
      • Sleeping 正在等待客户端发送新请求
      • Starting 正在执行请求处理
      • Checking table 正在检查数据表
      • Closing table 正在将表中数据刷新到磁盘文件
      • Locked 被其他查询锁定记录
      • Sending Data 正在处理 select 查询，同时将结果发送给客户端

    • info 一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用show full

      processlist;

  4. 查询缓存，需要查询 SQL 与 缓存中的 SQL 完全一致（包括参数且不能存在不确定参数比如 now()）

    是否开启查询缓存

    ​ show variables like '%query_cache%'; 查询是否开启查询缓存，空间大小，限制等

    ​ show status like 'Qcache%'; 查看查询缓存更详细的参数，缓存空间，缓存块，缓存多少等

    不能使用查询缓存

    • 查询语句使用SQL_NO_CACHE
    • 查询的结果大于query_cache_limit设置
    • 查询中存在不确定参数比如 now()

  5. 当将需要执行的 SQL 语句发送到 mysql 之后,通过 SQL解析器(将SQL解析成为mysql程序能理解的信息),然后由 查询优化器(选择一个最优的查询路径) 然后由执行器调用合适的存储引擎开始真正的执行查询.

  6. InnoDB存储引擎执行流程:

    1. 将需要操作的数据从磁盘文件中查询出来并添加到缓冲池(Buffer Pool).如果缓存池存在则不会在查询磁盘.

    2. 将历史数据写入到undo日志文件.主要用于回滚.后续执行失败的情况.

    3. 更新缓存池数据

    4. 写入 redo log buffer. (此时也是在内存中)

    5. 将 redo log 写入 磁盘文件 (主要用于如果 mysql 宕机时候恢复数据.(缓冲池中的数据未写入到磁盘中时,宕机可以恢复数据))

      innodb_flush_log_at_trx_commit mysql 的这个配置来控制写入 redo log 的时机 默认 =1

      =0 事务提交时,不写入redo 磁盘文件,而是由 InnoDB存储引擎线程来定时写入磁盘. 性能最好,但是宕机会丢失数据

      =1 事务提交时,写入 redo 磁盘文件. 安全性最高.不会丢失数据.相对性能差

      =2 事务提交时,,不写入redo 磁盘文件,而是写入 os cache.然后再从os cache 写入 磁盘文件. 性能比1好,但是比0差.如果机器宕机则会丢失数据

    6. 写入 binlog 日志.

      sync_binlog控制binlog写入磁盘策略 默认=0

      =0时先写入 OS cache 然后写入磁盘

      =1直接写入磁盘

    7. redo log 与 bin log 全部写入成功后提交事务完成. (redo log 与 bin log 都是写入内存后即写入成功)

InnoDB

InnoDB存储引擎体系架构.png

后台线程

InnoDB 是一个多线程模式的存储引擎.由多个线程处理不同的功能.

Master Thread

主要负责将缓存池中的数据异步刷新到磁盘,保证数据的一致性.

  1. 每秒操作

    • 日志缓冲刷新到磁盘,即使这个事务还没有提交(总是). 每秒一定会操作,这个是大事务依然能够很快提交的保障.

    • 合并插入缓存(可能)

      并不是每秒发生, 判断 I/O 次数是否小于5次,如果小于5次则代表压力很小可以执行.

    • 最多刷新 100 个 InnoDB 缓冲池中的脏页到磁盘(可能)

      判断 缓冲池 中的脏页是否大于配置(innodb_max_dirty_page_pct 默认 = 90 )的比例.如果大于则执行.

      刷新100个脏页为 innodb 版本为 1.0.x 之前的版本

      SSD磁盘能力很强.每秒足够写入更多的脏页.但是 1.0.x 之前的版本每次就是写入100个.限制了磁盘的能力

      之后的版本,增加了 innodb_io_capacity 配置用来表示磁盘吞吐能力默认为 200

      合并插入缓冲时,合并插入缓冲的量是 innodb_io_capacity 的 5%

      刷入磁盘脏页的数量等于 innodb_io_capacity

      修改参数innodb_max_dirty_page_pct 默认值为 75%.之前的90%太大了不是很合理.通过测试 75%比较合理

      增加参数 innodb_adaptive_flushing自适应刷新

      之前只有超过innodb_max_dirty_page_pct 才会刷新.修改为即使小于也有可能刷新.改为根据产生 redolog 的速度来刷新

    • 如果当前没有用户活动,则切换到 background loop (可能)

  2. 每十秒操作

    • 刷新 100 个脏页到磁盘 (可能)

      判断 I/O 操作次数是否小于 200次.小于则刷新 100 个脏页到磁盘

    • 合并至多5个插入缓冲(总是)

    • 将日志缓冲刷入磁盘(总是)

    • 删除无用的undo页(总是)

    • 刷新 100 个或者 10个脏页到磁盘 (总是)

      判断 缓冲池 中的脏页是否大于70% 如果大于则 刷新 100个脏页如果小于则刷新10个脏页

  3. background loop

后台循环,若当前没用用户活动( 数据库空闲时 )或者数据库 shutdown时.就会切换到这个循环

  • 删除无用的undo页(总是)
  • 合并 20 个 插入缓冲 (总是)
  • 跳回主循环 (总是)
  • 不断刷新 100 个脏页,直到没有脏页(可能)

1.2.x 版本将每十秒的操作部分移出了 master thread 增加了一个新的线程处理,减轻了负担

如果所有事件都处理完成,且数据库空闲则 master thread 挂起

默认存储引擎为 InnoDB 但数据库中没有一个 innodb 存储引擎的表, master thread 挂起

缓存池 Buffer Pool

MYSQL Buffer Pool.png

  1. InnoDB 1.0.x 版本之后是支持允许设置多个 Buffer Pool 实例存在的.(每个数据页根据哈希值平均分配到不同的实例中,区分为不同的内存块可有效减少公共资源的竞争,提高并发)
# 用于设置 buffer_pool 个数
innodb_buffer_pool_instances=1

  1. 配置文件可以设置 Buffer Pool 大小

    [server]
# 设置buffer_pool 大小为 2G (2147483648字节)
innodb_buffer_pool_size=2147483648

  2. free 链表用来解决buffer_pool 中的那个缓存页是空闲的.

    mysql 运行过程中是不停的从磁盘文件中读取数据页到缓存页中进行操作的(CRUD).所以此时是需要知道哪个缓存页是空闲的以方便后续的数据页写入缓存页中.如果某一个缓存页已经使用了.然后又读取了其他的数据页到该缓存页上时就会出现数据错误.

  3. flush 链表用来解决更新后的数据写回到磁盘文件.

  4. lru 链表(最近最少使用)用来解决脏页的淘汰. 内存是有限的.所以空闲缓存页一定是有用完的那一刻的.如果所有缓存页用完了.则通过lru链表来淘汰一些缓存页.

    • 简单lru链表

      1. 当从磁盘文件中读取数据页到缓存页中时,就将该页放入LRU链表头部.
2. 当使用本身就已经再buffer_pool中的缓存页时,则将该页放入LRU头部.
3. 需要淘汰一些缓存页时,直接从LRU链表尾部开始淘汰即可.尾部的数据一定是最近最少使用的数据.

mysql 预读机制会导致大量的无效缓冲页放入 LRU 链表头部.从而导致淘汰的缓存页数据其实是热点数据.从而导致性能下降.
预读机制就是: 在读取数据页1时,捎带手的把数据页2读取出来.并且放入了LRU头部.但是 数据页2 并不会有人访问,但是它依然不会被淘汰从而浪费内存.

触发预读机制:
1. innodb_read_ahead_threshold=56 该参数是用来控制预读机制的.默认设置是 56. 如果顺序访问了一个区内的多个数据页,超过的设置的值则会发生预读机制,会把下一个相邻的区的所有数据页加载到 buffer_pool 中
2. innodb_random_read_ahead=OFF.该参数是默认关闭的 如果buffer_pool中缓存了一个区内的13个连续的数据页,则会直接缓存整个区内的所有数据页到 buffer_pool 中. 
3. 全表扫描 `select * from table`

    • 冷热数据分离

冷热数据分离LRU.png 冷数据占比,冷数据移动规则设置

```toml
 # 控制 LRU 冷热数据比例.默认37,冷数据占数据的 37%
  innodb_old_blocks_pct=37
 # 数据页被加载到缓存页后多长时间访问才会放入热数据区头部. 默认 1000毫秒
 innodb_old_blocks_time=1000
```

1.  第一读取磁盘数据页时会将数据读取到冷数据区域头部.

2.  冷数据区域数据页在被加载超过1s之后访问,则会被移动到热数据区域头部.

3.  热数据区域数据不会频繁移动,只有访问热数据区域后3/4的数据时候才会将缓存页移动到热数据区域头部.

> 该方案解决了数据预读机制以及全表扫描机制.
>
> 1.  预读以及全表扫描来的数据,会进入冷数据区域.而不是进入热数据区域.除非 1s 之后又访问到了改缓存页.

6. 缓存页刷入磁盘 1. 后台线程定时将 flush链表,以及LRU链表冷数据刷入磁盘数据页. 并放入 free链表中. 2. 如果free链表中没有空闲的缓存页时,会直接写入 LRU 链表冷数据区域尾部的缓存页中.

Buffer Pool 性能优化

  1. 设置 buffer pool 个数来优化数据的并发性能.

    配置文件

    [server]
# 设置buffer_pool 大小为 8G 
# 如果设置的小于 1G 则buffer_pool 个数最大只有 1 个
innodb_buffer_pool_size = 8589934592
# 用于设置 buffer_pool 个数
innodb_buffer_pool_instances=4
# 
innodb_buffer_pool_chunk_size=128M

    在多个线程同时操作数据库时就是多个线程访问 buffer pool , 所以并发的访问时候一定是会加锁的.多个线程排队处理.

    一切都是基于内存的,而且所有的链表操作都是指针,所以性能还是很好的.

    但是如果访问的数据大多数在缓存页中没有则会进行磁盘读取,此时会发生磁盘IO,大量的磁盘IO性能极低.此时就会出现大量的排队的线程.从而降低整个数据库的性能

    设置多个 buffer pool 则可以分散多个线程,从而实现同时处理多个请求.

    生产环境数据库 buffer pool 设置

    1. buffer pool 大小一般设置为机器大小的 50%到60%
    2. buffer pool总大小=(chunk大小 * buffer pool数量)的2倍数

InnoDB 状态

SHOW ENGINE INNODB STATUS;

************************************=
2021-09-10 11:28:29 0x7fbbacd38700 INNODB MONITOR OUTPUT
************************************=
Per second averages calculated from the last 40 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 1 srv_active, 0 srv_shutdown, 19554 srv_idle
srv_master_thread log flush and writes: 19555
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 4
OS WAIT ARRAY INFO: signal count 4
RW-shared spins 0, rounds 6, OS waits 3
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 6.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 7427
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421919597786976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
629 OS file reads, 55 OS file writes, 9 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 13616285
Log flushed up to   13616285
Pages flushed up to 13616285
Last checkpoint at  13616276
0 pending log flushes, 0 pending chkp writes
12 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 103406
Buffer pool size   8191
Free buffers       7756
Database pages     435
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 401, created 34, written 36
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 435, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1, Main thread ID=140444323751680, state: sleeping
Number of rows inserted 0, updated 0, deleted 0, read 8
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
****************************

物理文件存储

表空间

表空间是一个 InnoDB 存储引擎逻辑结构的最高层.所有的数据都存放在表空间中. 默认情况下 InnoDB存储引擎有一个共享表空间 ibdata1.

如果用户启用了参数 innodb_file_per_table 则每张表内的数据单独放到一个表空间中.

即使是开启了这个参数每个表间仅仅保存,数据,索引和插入缓冲 Bitmap页. 其他数据依然存放与 共享表空间.

表空间.png

数据页格式

数据页结构.png

一行数据的存储

一行记录的存储.png

行溢出

如果一行数据无法存储在一个数据页内则需要溢出这个数据页,把数据溢出到其他的数据页.

真实数据除了会保存数据之外,还会存储一个20字节的指针,指向下一个数据页

行数据溢出.png

redo log

保证事务提交之后,修改的数据不丢失.

  1. redo log 占用空间非常小
  2. redo log 是顺序写入磁盘

redo log 日志格式

redolog 格式.png

redo log 类型非常多也非常复杂,下方列举的是几种简单格式.

  1. MLOG_1BYTE

    修改1个字节的值

  2. MLOG_2BYTE

    修改2个字节的值

  3. MLOG_4BYTE

    修改4个字节的值

  4. MLOG_8BYTE

    修改8个字节的值

  5. MLOG_WRITE_STRING

    修改大量的值

偏移量: 表示修改内容位置. 例如: 在 xx 位置修改了 1 个字节

修改长度: MLOG_WRITE_STRING 该redo log 时候存在, 表示 在 偏移量 XX 位置修改了 长度 XX

redo log 写入方式

redolog写入磁盘方式.png

  1. 一条条的redo log 并非直接写入了磁盘中,而是交由 内存中的 redo log buffer 写入 redo log block

  2. redo buffer 是在 MySQL 启动时,向操作系统申请的一块连续的内存.然后划分为多个 redo log block.

    # 设置 redo log buffer 大小.默认 16M
# redo log buffer 越大磁盘IO越少
innodb_log_buffer_size = 16777216

  3. redo log block 占内存 512 字节. 分为三个 部分 header body trailer.

    header 又分为四个部分

    1. 包括4个字节的block no，就是块唯一编号；
    2. 2个字节的data length，就是block里写入了多少字节数据；
    3. 2个字节的first record group。这个是说每个事务都会有多个redo log，是一个redo log group，即一组redo log。那么在这个block里的第一组redo log的偏移量，就是这2个字节存储的；
    4. 4个字节的checkpoint on

  4. MySQL 每次执行一个 事务时,可能有很多个 增删改查.所以会有多个 redo log .这些 redo log 就是一组. 每组 redo log 并非时一条一条的写入 redo log block 而是事务提交之后,整组 redo log 写入 redo log block

  5. 一组组的 redo log 并非完整的存储与同一个 redo log block. 而是取决与 这组 redo log 的大小. 如果 redo log 很小,可能多组 redo log 存储与同一个 redo log block .如果太大,可能一组 redo log 存储与多个 redo log bolck

  6. redo log block 刷入磁盘的时机有四种:

  7. redo log block 已经占满了 redo log buffer 总量的一半儿以上.

  8. innodb_flush_log_at_trx_commit=1 设置每次提交事务时,都将 redo log 所在的 redo log block 刷入磁盘.

  9. 后台主线程每秒都会将 redo log block 刷入磁盘.

  10. mysql 关闭时

  11. redo log 文件设置

    # 查询 redo log 日志目录
show variables like 'datadir';

    # 设置  redo log 日志目录
innodb_log_group_home_dir = xxx
# 设置 redo log 日志文件大小: 默认 48M
innodb_log_file_size = 50331648
# 设置 redo log 日志文件数量 默认 2个
innodb_log_files_in_group = 2

undo log

用于事务回滚

undolog日志格式.png

事务

脏写

脏写.png

两个事务对同一个数据进行更新,第一个更新的时候在更新之后又回滚了事务.导致事务2更新的数据也丢失了.这个就是脏写.

脏读

脏读.png

两个事务一个更新一个读取,更新事务回滚之前与之后,读取事务读取到的内容不一致.

不可重复读

不可重复读.png

我们假定: 只有事务提交之后的数据内容才能被其他事务读取到.这种方式可以避免 脏读

事务A 在业务中,要多次查询一行数据,并根据该数据进行业务处理,但是如果事务A未提交时,其他事务对这行数据进行了更新并且提交了事务.此时 事务A 读取到的内容就发生了变化,此时事务A对值A就是不可重复读的

幻读

幻读.png

SQL标准事务隔离级别

  1. read uncommitted 读未提交

    不允许发生脏写,但是可能发生脏读,不可重复读,幻读.

  2. read committed 读已提交

    不允许发生 脏读,脏写. 可能发生 不可重复读,幻读.

  3. repeatable read 可重复读

    不允许发生 脏读,脏写,不可重复读. 可能发生幻读.

  4. serializable 串行化

    不允许并发执行. 多个事务串行执行.自然也就没有 脏写,脏读,不可重复读,幻读的问题.

MySQL事务隔离级别

MySQL 事务隔离默认是 RR(repeatable read 可重复读) MySQL的RR隔离级别是不允许:脏读,脏写,不可重复读,幻读

MVCC多版本并发控制机制

undo log版本链

undo log 版本链.png

事务的更新会在 undo log 上使用 roll_pointer 形成一条回滚链

ReadView

ReadView 主要包含四个内容

  1. m_ids 生成 ReadView 时,MySQL中活跃的读写事务id列表
  2. min_trx_id 生成 ReadView 时最小的事务id,也就是m_ids 最小值
  3. max_trx_id mysql 下一个要生成的事务id 最大事务id
  4. creator_trx_id 生成ReadView的事务id

版本是否可见

  1. 如果被访问版本的 trx_id 属性值,与 ReadView 中的 creator_trx_id 相同. 表示当前事务在访问它自己修改过的记录.所以该版本对当前事务时可见.

  2. 如果被访问版本的 trx_id 属性值小于 ReadView 中的 min_trx_id 值, 表示该版本事务在当前事务生成 ReadView 之前已经提交,所以该版本对当前事务可见.

  3. 如果被访问版本的 trx_id 属性值大于或等于 ReadView 中的 max_trx_id值,表示该版本的事务在当前事务生成 ReadView 之后开启的,所以该版本对当前事务不可见.

  4. 如果被访问版本的 trx_id 属性值在 ReadView 中的 min_trx_id与 max_trx_id 之间,那就需要判断一下 trx_id 属性值是否在 m_ids列表中,

    ​ 如果在则说明创建 ReadView 时生成该版本的事务还活跃,所以该版本对当前事务不可见.

    ​ 如果不在则说明创建 ReadView 时生成该版本的事务已经提交,所以该版本对当前事务可见.

ReadView流程解析

ReadView001.png

访问版本 trx_id= 32 小于 ReadView 中的 min_trx_id. 说明创建 ReadView时, trx_id=32 的事务已经提交了.所以该版本的数据对事务A是可见的.

ReadView002.png

ReadView003.png

事务A 再次查询数据发现. 此时的 trx_id=59,存在与 min_trx_id与max_trx_id之间且在 m_ids中所以.trx_id=59这条事务是与事务A创建 ReadView时就活跃的.所以该数据对事务A时

不可见的.事务A顺着roll_pointer找到了 trx_id=32< min_trx_id=45的undolog.说明此版本数据事务A可以访问.

ReadView004.png

事务A修改了数据为A.roll_pointer指向了上次的数据生成的undolog. 事务A查询数据时,发现 trx_id=45与 creator_id=45相等.所以 事务A在访问 本身修改的数据.所以 可见

ReadView 实现 Read Committed 隔离级别(RC(避免脏读,脏写))

事务的隔离级别设置为 RC 级别之后,每次查询都会重新生成一个 ReadView.

Read Committed 隔离级别.png

每次查询的时候重新生成 ReadView,所以生成的ReadView中一定不包含活跃的事务.所以只要是提交的事务数据都是可见的.

ReadView 实现 Repeatable Read 隔离级别(RR 避免脏读,脏写,不可重复读,幻读)

事务开始查询时候创建 ReadView.之后该事务再次查询则不会创建新的 ReadView,那么在该事务创建时,活跃的事务即使是提交了.该事务依然认为仍然活跃着.从而保证不去读取它提交的数据.

具体流程图可参考: ReadView流程解析

锁

ReadView undolog 版本链解决了读的问题(脏读,不可重复读,幻读).

独占锁/排他锁 (Exclude Lock) 行锁

锁.png

任何一个事务要更新一行数据之前必定要先加独占锁,后来的事务是创建独占锁后会等待.知道前面的事务更新完成来唤醒.才会执行更新.

共享锁(Share Lock)行锁

# 查询一行数据的时候加共享锁
select * from table lock in share mode;

如果加了共享锁.则后续的更新操作就不能再加独占锁.只有共享锁结束后才能执行更新.

很少用.一般都使用 redis/zookeeper 分布式锁.

锁类型独占锁共享锁
独占锁互斥互斥
共享锁互斥不互斥

独占锁与其他锁都互斥.

加锁后为啥读没问题呢?

因为读取这行数据的时候使用的是 MVCC 多版本控制机制.并不加锁.

分布式锁

# 独占锁
# 加锁后不允许,添加任何其他锁.(可以查询因为查询不加锁)
select * from table for update;
# 共享锁
# 加锁后可以继续加共享锁,但不能加独占锁
select * from table lock in share mode;

可以实现分布式锁但是不建议

  1. 加锁隐藏在SQL中不好维护.
  2. 对数据库的性能有问题.

索引

二分查找

二分查找.png

package main

import (
	"fmt"
	"sort"
)

func main(){
	array := []int{4,3,2,1,10,11,5,6}
	//array := []int{1,2,3,4,5,6,10,11} 
	fmt.Println("find: ",binarySearch(array,2))
}

// binarySearch 二分查找
// array 查询数组
// findValue 查询值
func binarySearch(array []int,findValue int) int{
	// 对int 切片进行排序
	sort.Sort(sort.IntSlice(array))
	
	low := 0
	height := len(array) -1
	
	for low <= height {
		mid := low + ((height - low)/2)
		fmt.Println("mid = ", mid)
		minValue := array[mid]
		fmt.Println("minValue = ", minValue)

		if minValue ** findValue {
			return minValue
		}else if minValue > findValue{
			height = mid - 1
		}else if minValue < findValue{
			low = mid + 1
		}

	}

	return -9999
}

索引.png

B+树

结构

  1. 数据页的分裂

    当插入第一条数据时候,数据页只有一个.随着数据量的增长,第一个数据页满了之后会插入到第二个数据页.此时会发生数据的挪移.将数据根据 id大小进行排序顺序存储于两个数据页.

    二分查找.需要对数据排序

    数据页是个链表.

  2. 数据的查找

    假如只有一个数据页.根据主键id查询则可以使用二分查找快速定位到数据行所在位置.

  3. 索引的建立

    当数据页变多.单纯使用二分查找已经不能满足需求.此时则在数据页的基础上生成一个索引页

    索引页包含最小id以及数据页号.

    此时可以根据查找id与最小id对比找到数据页,然后查询到数据行.

  4. B+树的建立

    随着数据页的增多导致一个索引页无法存储大量的数据时,此时则再次生成一个索引页用来保存索引页信息.生成的索引页就是根索引页.

    根索引页内包含最小id以及索引页号.

    此时可以根据查找id与最小id对比找到索引页.然后再索引页内继续对比找到数据页.然后查询到数据行. 这样随着数据量的增多,索引页一层层的增加,从而形成一颗树,就是B+树

    根据id查询数据时,会从最顶层的索引页使用二分查找一层一层的定位.最终定位到数据页.然后在数据页内的目录里使用二分查找找到对应的数据行.

总结

前面的讲解其实是倒推的B+树.

其实在创建表的时候 innodb 就已经创建了聚簇索引(主键),真实的创建聚簇索引的过程:

  1. 创建表时候就会创建聚簇索引根节点.
  2. 开始插入数据最早是直接插入到这个根节点.并添加页目录.
  3. 根节点空间用完之后继续插入数据,会将根节点数据复制到一个新的页中(比如页1),然后对页1进行页分裂,然后根节点就会升级为根索引.
  4. 持续插入数据则会继续页分裂和创建新的索引页.

聚簇索引

如索引图示: 所有的索引页 + 所有的数据页组成的B+树就是聚簇索引.

innodb的主键采用聚簇索引

二级索引

二级索引结构和聚簇索引基本是一致的.

区别: 例如上图中如果对 name 字段创建二级索引.索引页内存存储的则是最小name,主键id和页号然后根据name排序.

查询方式:

​ 根据name查询数据时,从二级索引根索引页开始使用二分查找一层一层的定位,直到定位到最终数据的主键id.

​ 然后根据主键id再次到聚簇索引查询到数据行(这个过程叫回表)

联合索引

多列的索引就是联合索引.比如使用name和age创建索引.

索引查询

全值匹配规则

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`), # 主键 聚簇索引
  KEY `name_age` (`name`,`age`) # 二级索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 此处有很多数据.因为三条数据其实只有一个根页
INSERT INTO `user` (`name`,`age`) VALUES('小明',10);
INSERT INTO `user` (`name`,`age`) VALUES('小红',11);
INSERT INTO `user` (`name`,`age`) VALUES('小白',12);

SELECT * FROM `user` WHERE `name`='小明' AND age=10;

联合索引是根据name,age这两个字段.所以索引页里面存储的是这两个字段的值的最小值进行的排序.

查询语句中WHERE之后的条件与联合索引字段一致且顺序一致(顺序不一致mysql也会调整顺序去查询),从根索引页使用二分查找一层层的匹配然后找到主键id,然后回表找到数据行.

像 WEHRE 之后的条件字段与索引中字段完全一致,且用等于号做等值匹配就是全值匹配规则.

最左侧列匹配规则

# 使用左侧字段查询也是可以使用索引的
SELECT * FROM `user` WHERE `name`='小明';

最左侧前缀匹配规则

SELECT * FROM `user` WHERE `name` like '小明%';

范围查找规则

where语句里如果有范围查询,只有对联合查询最左侧列进行范围查询才能用到索引

SELECT * FROM `user` WHERE `name`<'小明' AND age<11;

等值匹配+范围匹配规则

SELECT * FROM `user` WHERE `name`='小明' AND age<11;

排序(order by)和分组(group by)使用索引

联合索引使用的是name和age字段创建的.所以默认就是根据这两个字段进行的升序排序.

所以在排序和分组时候默认就是使用这两个字段升序.

  1. 所以排序尽量使用索引字段进行排序.
  2. 排序时要不所有字段升序,要不所有字段降序.不要一个字段升序一个字段降序.

覆盖索引

覆盖索引并不是一个新的索引类型是一个查询方式

# 假如 x,xx,xxx 三个字段是做的联合索引
# 这条SQL会先使用 x,xx,xxx.三个字段查询 联合索引找到所有id,再回表查询出每个id的数据行.(其实MySQL会直接扫描全表,这样还不用走索引.)
select * from table order by x,xx,xxx;

# 这条SQL就没办法直接扫描全表了.肯定是先联合索引再回表聚簇索引查询十条记录出来.
select * from table order by x,xx,xxx limit 10;

# 这条SQL仅仅需要走一次联合索引即可.
# 因为联合索引内包含了所有需要查询的字段.不需要再回表.这种查询方式就是覆盖索引.大概意思就是设计的时候如果常常查询的字段可以冗余到索引中.
select x,xx,xxx from table order by x,xx,xxx limit 10;

设计索引

  1. 功能开发完成后根据业务逻辑中的数据查询.where条件 order by 条件 group by 条件进行设计索引.

  2. 选择基数大的字段做索引.(基数大的意思就是字段的值比较多的)状态[0,1]这种就俩值的字段不适合做索引

  3. 字段类型比较小的字段作为索引,字段小占用磁盘空间少.检索快.简介这类大字符串就不适合做索引

    如果一个VARCHAR(255)这种大字符串做索引可以考虑放入每个字段的前20个字符, KEY my_index(name(20),age) 此时就是放入 name字段的前20个字符和age一起做为索引.

    这种字符串索引前缀做索引的方案.对于排序(order by)分组(group by)来说不能使用.

  4. 索引别太多.太多会影响插入和更新性能.

  5. 查询时候尽量简单查询.(函数,计算都不能使用索引)

  6. 存在等值匹配与范围匹配时,范围匹配字段尽量靠右

  7. 建议主键使用自增.可以避免数据页的频繁分裂(排序迁移).增加插入性能.

执行计划

单表查询方式

const

# id 为主键
select * from table where id = x;
# name 为唯一索引
# 此处查询需要回表
select * from table where name = 'xx';

根据主键查询或者根据唯一索引进行等值匹配查询时候.单表访问方法就是const

ref

# sex 为普通索引
# 此处查询需要回表
select * from table where sex=x;

普通索引列进行等值匹配.单表访问方法为 ref.

ref_or_null

# sex 为普通索引
# 此处查询需要回表
select * from table where sex=x or sex is null;

普通索引列进行等值匹配时该列可能为NULL.单表访问方法为 ref_or_null

range

# age 为普通索引
# 此处查询需要回表
select * from table where age > 18 and age < 45;

单表范围查询使用索引

index

假设有一表联合索引是KEY(x1,x2,x3) 查询语句为

select x1,x2,x3 from table where x2='xxx';

该 SQL 无法使用索引。但是需要查询的字段在联合索引中全部都有。所以扫描联合索引就可以。不需要全表扫描。

all

全表扫描.不用索引.

多表查询方式

select * from t1,t2 where t1.x1 = xxx and t1.x2 = t2.x2 and t2.x3=xx;

这条 SQL 执行过程大概是这样的:

  1. 首先根据 t1.x1 = xxx 这个筛选条件从 t1 表中查询出一批数据 ( 访问方式取决与 t1 表的索引和主键 )
  2. 遍历这一批数据,使用其 x2 的值与 t2.x3 = xx 组成筛选条件从 t2 表筛选出数据来.

先从 t1 表查询数据,这个表叫驱动表.然后关联 t2 查询数据,t2表为 被驱动表

执行计划优化

执行成本

  1. IO成本.将数据从磁盘读取到内存.

    mysql 读取数据是一页一页的读取.约定读取一页数据的成本为 1.0

    IO成本 = 数据页数量 * 1.0 + 微调值

  2. CPU成本.读取到内存的数据进行运算(是否符合检索条件,排序,分组,函数等)

    一般约定读取和检测一条数据的成为为 0.2

    CPU成本 = 行记录数 * 0.2 + 微调值

计算成本

  • 计算全表扫描成本

    1. 执行show table status like '表明';查询表统计信息
      1. rows 表内记录数
      2. data_length 表的聚簇索引字节数大小
    2. data_length / 1024 / 16kb 就能得到 数据页数量 (16kb为数据页大小)
    3. 全表扫描成本 = IO成本 + CPU成本

    例如: 表 t1 数据页有 100 个,记录2万条. 表 t1 的全表扫描成本 = 100 + 20000 * 0.2 = 4100

  • 计算索引成本

    1. 二级索引里根据条件查询的IO成本一般看查询条件涉及到几个范围.

      比如说name值在25-100，250-350两个区间，那么就是两个范围，否则name=xx就仅仅是一个范围区间. 一般粗暴的认为一个范围就是一个数据页(不考虑数据页溢出情况)

      IO成本 = 数据页数量 * 1.0 + 微调值

    2. CPU成本 = 估算二级索引查询结果数 * 0.2 + 微调值

    3. 回表IO成本.此时粗暴的认为为每条估算查询结果都要回表查询一个数据页.所以回表IO成本 = 估算二级索引查询结果数 * 1.0 + 微调值

    4. 回表最多查询到估算二级索引查询结果数,然后判断是否符合其他条件.此时CPU成本 = 估算二级索引查询结果数 * 0.2 + 微调值

    5. 最终结果为以上四个步骤结果之和.

查询优化(重写SQL)

有时候mysql会觉得你写的复杂SQL效率不高.帮你优化.

  • 常量替换.使 SQL 语义更加清晰

    比如: i = 5 and j > i 会改写成 i =5 and j > 5等.

    还有一些没有意义的比如 a=a and b=b 这些会直接删除.

  • select * from t1 where x1 in (select x2 from t2 where t2.x3=xxx) 子查询 in 优化

    先执行select x2 from t2 where t2.x3=xxx.然后将结果写入到临时表也叫物化表

    这个物化表一般使用memory存储引擎通过内存存放.如果数量太大则会使用B+数聚簇索引存放到磁盘.(有索引不管怎么样都有索引)

    然后根据物化表的数据量与 t1 表进行对比.哪个表数据量小全量扫描哪个.

主从架构

主从复制架构

主从复制架构.png

实现高可用.如果主数据库宕机.可以切换到从节点

实现主从复制的基本原理

  1. 从库会有 IO 线程与主库建立TCP连接,向主库发送请求要求主库传输 binlog 日志.
  2. 主库 IO dump 线程,负责通过TCP连接将 binglog 日志传输到从库
  3. 从库的 IO线程会将 binlog日志写入自己本地的 relay 日志中去
  4. 从库的 SQL线程会读取 relay日志,进行日志重做,将主库的所有 SQL(增,删,改) 在从库执行一遍.还原数据.

主从读写分离

单台机器的能力有限.不可能扛得住非常大的请求.所以此时可以做读写分离架构.该架构实在主从复制架构的基础上演进来的.

主服务器只处理写库请求.读库请求交给从库去完成.这样就可以将压力分散到多台服务器从而分担压力.增加数据库的吞吐性.

搭建主从复制架构

  1. 两台机器并且全部安装mysql. 主库必须打开 binlog

  2. 主库创建用于主从复制账号

    create user 'backup_user'@'192.168.31.%' identified by 'backup_123';
grant replication slave on *.* to 'backup_user'@'192.168.31.%';
flush privileges;

  3. 主库全量数据备份.系统维护.此时不能任何人操作数据库.

    /usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot --master-data=2 -A > backup.sql
# mysqldump 在mysql 安装目录下
# --master-data=2 备份SQL文件里,要记录下此时主库的binlog日志和position号.用于主从复制

  4. 将备份文件传输到从服务器.并在从库执行备份文件.

  5. 从库执行指定主库进行复制

    CHANGE MASTER TO MASTER_HOST='192.168.31.229', MASTER_USER='backup_user',MASTER_PASSWORD='backup_123',MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=1689;

# MASTER_LOG_FILE='mysql-bin.000015',MASTER_LOG_POS=1689; 备份文件中可以查看.

  6. 从库执行命令.开启主从复制

    start slave # 开启主从复制
show slave status # 查看主从复制状态. 主要看 Slave_IO_Running 和 Slave_SQL_Running 都是YES

最简单的异步复制.从库异步拉取主库binlog.

问题:

  • 数据延迟.会出现短暂不一致.主库刚刚插入的数据可能短暂时间内从库无法查询到
  • 数据丢失.主库写入数据成功后不会管从库是否接收到binlog.如果从库还没拉取最新的binlog 主库就宕机了.此时主库刚刚插入的数据从库是拿不到的.

解决数据丢失

默认的主从复制建构存在数据丢失

  1. AFTER_COMMIT : 主库写入 binlog 等待binlog 复制到从库了.主库就会提交自己的本地事务.接着等待从库返回给自己成功响应. 然后主库返回提交事务成功的响应给客户端.
  2. mysql5.7默认方式: 主库写入 binlog , 并且复制给从库,然后等待从库的响应,从库返回成功后,主库再提交事务.接着返回提交事务成功的响应给客户端.
搭建半同步主从复制架构

在异步主从复制架构的基础上

# 主库 安装半同步复制插件.
install plugin rpl_semi_sync_master soname 'semisync_master.so';
set global rpl_semi_sync_master_enabled=on;
show plugins;

# 从库 安装半同步复制插件.
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
set global rpl_semi_sync_slave_enabled=on;
show plugins;

# 重新启动从库IO线程
stop slave io_thread; 
start slave;

# 主库检查一下半同步复制是否正常运行
# Rpl_semi_sync_master_status的状态是ON
show global status like '%semi%';

解决数据延迟

percona-toolkit工具集里的pt-heartbeat工具，他会在主库里创建一个heartbeat表，然后会有一个线程定时更新这个表里的时间戳字段，从库上就有一个monitor线程会负责检查从库同步过来的heartbeat表里的时间戳。时间戳一对比就知道延迟多久了.

mysql5.7 支持并行复制

slave_parallel_workers>0，
slave_parallel_type = LOGICAL_CLOCK
avatar
文章
阅读
粉丝