MySql知识体系思维导图模板_ProcessOn思维导图、流程图

常见问题

1. select count(1) 与 select count(*)

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端

select count(id) from t_order; //id 为主键值

如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就对 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1

如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引，这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引

select count(1) from t_order;

如果表里只有主键索引，没有二级索引时，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1

如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了

select count (*) from t_order

count(*) 其实等于 count (0)，也就是说，当你使用 count (*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count (*) 执行过程跟 count (1) 执行过程基本一样的，性能没有什么差异

在 MySQL 5.7 的官方手册中有这么一句话： InnoDB handles SELECT COUNT(\*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

select count (col_name) from t_order

若 col_name 没有加索引，则会采用全表扫描的方式来计数

半连接优化Semi-join 策略

DuplicateWeedout

使用临时表对 semi-join 产生的结果集去重

FirstMatch

只选用内部表的第 1 条与外表匹配的记录

LooseScan

把 inner-table 数据基于索引进行分组，取每组第一条数据进行匹配。

Materializelookup

将 inner-table 去重物化成临时表，遍历 outer-table，然后在物化表上去寻找匹配

MaterializeScan

将 inner-table 去重物化成临时表，遍历物化表，然后在 outer-table 上寻找匹配

性能抖动

可能原因

1. 在执行一个SQL查询很多数据前buffer pool 缓存页全部已用完。此时需要将大部分缓存页刷新到磁盘中就会导致查询很慢

2. 在执行更新语句时，redo log在磁盘上的所有文件都已写满，此时需要回到第一个redo log 文件覆盖写，此时同样会导致redo log 被刷入磁盘

3. 系统可能开启了 NUMA（Non Uniform Memory Access）

NUMA 默认是使用 CPU 亲和的内存分配策略，即请求进程会从当前所处的 CPU 的 Node 请求分配内存。当某个需要消耗大量内存的进程耗尽了所处的 Node 的内存时，就会导致产生 swap，不会从远程 Node 分配内存，这就是 swap insanity 现象。 MySQL 数据库是单进程多线程的架构，在开启的 NUMA 服务器中，内存被分配到各 NUMA Node 上，而 MySQL 进程只能消耗所在节点的内存。所以在开启 NUMA 的服务器上，某些特殊场景中容易出现系统拥有空闲内存但发生 SWAP 导致性能问题的情况。

解决方案

1. 减少缓存页 flush 到磁盘的频率

2. 提升缓存页 flush 到磁盘的速度

1. 采用SSD固态硬盘，随机IO性能很高

2. innodb_io_capacity：数据库采用多大的 IO 速率把缓存页 flush 到磁盘，即刷盘时每秒执行多少次随机IO（默认200）。可根据机器的平均随机IO速率来决定，相关工具有 fio。实际刷盘时还需要由另外一个因素决定，即脏页的比例 innodb_max_dirty_pages_pct 控制，默认75%，一般该参数无需调整

3. innodb_flush_neighbors：在 flush 缓存页到磁盘的时候，可能会控制把缓存页临近的其他缓存页也刷到磁盘。所以可以将其设置为0 减少刷新缓冲页的数量

3.关闭 NUMA

分支主题

1. 硬件层面上关闭，通过BIOS关闭

2. 内核层面关闭，在 Linux Kernel 启动参数中加上 numa=off 后重启服务器

For RHEL 6 # vi /boot/grub/grub.conf kernel /vmlinuz-2.6.39-400.215.10.EL ro root=/dev/VolGroup00/LogVol00 numa=off

For RHEL 7： # vi /etc/default/grub GRUB_CMDLINE_LINUX="rd.lvm.lv=rhel_vm-210/root rd.lvm.lv=rhel_vm-210/swap vconsole.font=latarcyrheb-sun16 crashkernel=auto vconsole.keymap=us rhgb quiet numa=off

RHEL7/CentOS7 必须要重建 GRUB 配置文件才能生效：# grub2-mkconfig -o /etc/grub2.cfg

3. 通过 MySQL参数来关闭

数据库层，在 mysqld_safe 脚本中加上 “numactl –interleave all” 来启动 mysqld： # numactl --interleave=all ./bin/mysqld_safe --defaults-file=/etc/my.cnf &

MySQL 在 5.6.27、 5.7.9 引入了 innodb_numa_interleave 参数，MySQL 自身解决了内存分类策略的问题，需要服务器支持 numa

SHOW ENGINE INNODB STATUS

（1）Total memory allocated，这就是说 buffer pool 最终的总大小是多少

（2）Buffer pool size，这就是说 buffer pool 一共能容纳多少个缓存页

（3）Free buffers，这就是说 free 链表中一共有多少个空闲的缓存页是可用的

（4）Database pages 和 Old database pages，就是说 lru 链表中一共有多少个缓存页，以及冷数据区域里的缓存页数量

（5）Modified db pages，这就是 flush 链表中的缓存页数量

（6）Pending reads 和 Pending writes，等待从磁盘上加载进缓存页的数量，还有就是即将从 lru 链表中刷入磁盘的数量、即将从 flush 链表中刷入磁盘的数量

（7）Pages made young 和 not young，这就是说已经 lru 冷数据区域里访问之后转移到热数据区域的缓存页的数量，以及在 lru 冷数据区域里 1s 内被访问了没进入热数据区域的缓存页的数量

（8）youngs/s 和 not youngs/s，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量

（9）Pages read xxxx, created xxx, written xxx，xx reads/s, xx creates/s, 1xx writes/s，这里就是说已经读取、创建和写入了多少个缓存页，以及每秒钟读取、创建和写入的缓存页数量

（10）Buffer pool hit rate xxx / 1000，这就是说每 1000 次访问，有多少次是直接命中了 buffer pool 里的缓存的

（11）young-making rate xxx / 1000 not xx / 1000，每 1000 次访问，有多少次访问让缓存页从冷数据区域移动到了热数据区域，以及没移动的缓存页数量

（12）LRU len：这就是 lru 链表里的缓存页的数量

（13）I/O sum：最近 50s 读取磁盘页的总数

（14）I/O cur：现在正在读取磁盘页的数量

InnoDB 内部机制

Buffer Pool (默认128M)

数据结构

缓存页（每页默认16k）

描述数据块

数据页所属的表空间、数据页的编号、这个缓存页在 Buffer Pool 中的地址等

空闲的描述数据块组成free链表，修改过的缓存页所对应的描述数据块组成flush链表

预读机制

线性预读

如果一个区中的被顺序读取的 page 超过或者等于 innodb_read_ahead_threshold 该参数变量时，Innodb 将会异步的将下一个区读取到 buffer pool 中，innodb_read_ahead_threshold默认为56（可设置 0-64）

随机预读

表示当同一个区中的一些 page 在 buffer pool 中发现时，Innodb 会将该区中的剩余 page 一并读到 buffer pool 中。由于随机预读方式给 innodb code 带来了一些不必要的复杂性，同时在性能也存在不稳定性，在 5.5 中已经将这种预读方式废弃，默认是 OFF。若要启用此功能，即将配置变量设置 innodb_random_read_ahead 为 ON。

比如全表扫描会导致随机读

LRU算法

基于冷热数据分离思想而设计

冷热数据比例是37 即innodb_old_blocks_pct=37，冷数据占比37%

数据第一次被加载到缓存页时会先被放到冷数据区链表的头部

在数据被加载到缓存页 innodb_old_blocks_time (默认1000)毫秒后，再次有数据访问请求时才将该缓存页放到热数据区链表的头部

热数据更新：为减少数据移动带来的性能损耗，只有在热数据区域的后3/4部分的缓存页被访问时，才会将其移动到链表头部

预读机制和全表扫描加载近来的缓存页都会被放到冷数据区，如果此时缓存页不够时，就需要从冷数据区尾部的缓存页，将其刷入磁盘

参数

innodb_buffer_pool_size = 8589934592，buffer pool 总内存大小8G

innodb_buffer_pool_instances = 4，buffer pool 实例个数，若总内存为8G则每个buffer pool大小为2G，可以根据CPU核心数设置用以提高并发能力

生产环境如何配置

1. buffer pool大小一般设置为机器内存的50%~60%

2. buffer pool size 等于（chunk大小 * buffer pool 数量）的2倍数

如何调整Buffer Pool大小

可根据Innodb缓存命中率情况做出调整，若命中率低于90%则需要增加Buffer Pool的内存

show status like 'Innodb_buffer_pool_read_%';

innodb_buffer_pool_reads：表示 InnoDB 缓冲池无法满足的请求数，需要从磁盘中读取

innodb_buffer_pool_read_requests：表示从InnoDb缓冲池读取页的请求数

随机读

关键性能指标

IOPS

即存储系统每秒可以执行多少次磁盘读写操作。该指标对数据库 crud 操作的 QPS 影响非常大，几乎决定了每秒可执行 SQL 语句的个数，底层存储的 IOPS 越高，数据库的并发能力就越高。

响应延迟

即每个随机读写操作的耗时

redo log

redo log block何时刷入磁盘

1. 如果写入 redo log buffer 的日志已经占据了 redo log buffer 总容量的一半，即超过了 8MB ，此时就会把他们刷入到磁盘文件中

2. 一个事务提交的时候，必须把 redo log 所在的 redo log block 都刷入到磁盘文件里，只有这样，当事务提交之后，修改的数据才不会丢失，因为 redo log 里有重做日志，随时可以恢复事务做的修改

3. 后台线程每隔 1 秒定时刷新

4. MySQL 关闭的时候，redo log block 都会刷入到磁盘里

innodb_flush_log_at_trx_commit

0：提交事务的时候，不会将内存中的 redo log 刷入磁盘。优势，纯内存操做速度快，缺点，redo 日志没有落地磁盘，若是提交事务的一瞬间，MySQL 宕机，那么若是是修改数据，内存数据没了，磁盘也没来的及更新，就丢失了本次修改操做

1：提交事务以前必定会将 redo log 刷入磁盘。优势，事务提交以前，事务操做 log 必定刷入磁盘，事务成功，磁盘必定有 redo 日志，若是事务提交成功，内存修改，磁盘尚未更新，彻底能够读取 redo 日志恢复数据。缺点，写磁盘确实会消耗不少性能，若是是高并发，大量写入，必定会影响写入性能，吞吐量和处理时间都会影响到。

2：将 redo 日志刷入 OS cache，间隔可能一秒写入磁盘。方案鉴于一和二方案之间。优势，利用 OS cache 去缓存部分日志，能够提升吞吐量，间隔时间，异步刷入磁盘。缺点，提交事务以后，可能 redo 日志还在 cache 中。此时，日志存在丢失的风险。

redo log参数

查看目录 show variables like 'datadir'

设置目录对应参数为 innodb_log_group_home_dir

redo log文件默认大小为 48M ，设置参数为 innodb_log_file_size

innodb_log_files_in_group 用于指定日志文件的数量，默认为2. 故默认有两个日志文件，即 ib_logfile0 及 ib_logfile1

checkpoint

作用

对于数据需要频繁更新的场景，要实时更新，对于 MySQL 来说，只处理 IO，就能把性能耗尽。

Redo 日志大小也是有限的，通过刷新策略，可以更有效的重复使用文件，不需要开辟新的空间。

如果说 redo log 写满之后，这时候系统的所有更新操作都会进行停止。redo log 是顺序写的，redo log 的大小是固定的，比如可以一组配置 4 个文件，每个文件大小是 1G，那么这个 redo log 可以记录 4G。write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。 write pos 和 checkpoint 中间空着的部分，就是可以写入的部分，如果 write pos 追上了 checkpoint ，代表写满了，就需要停下擦除一部分，把 checkpoint 推进一下

当缓冲池不够用时，根据 LRU 算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行 Checkpoint，将脏页刷回磁盘

数据库宕机，崩溃恢复期间，需要从上次的检查点进行恢复，使得效率提升，以便缩短数据库的恢复时间

物理备份日志点。

分类

Sharp Checkpoint (全量检查点)

发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1。但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint 进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘

Fuzzy Checkpoint (模糊检查点)

Master Thread Checkpoint

对于 Master Thread 中发生的 Checkpoint，差不多以每秒或每十秒的速度从buffer pool 的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时 InnoDB 存储引擎可以进行其他的操作，用户查询线程不会阻塞

FLUSH_LRU_LIST Checkpoint

FLUSH_LRU_LIST Checkpoint 是因为 InnoDB 存储引擎需要保证 LRU 列表中需要有差不多 100 个空闲页可供使用。在 InnoDB1.1.x 版本之前，需要检查 LRU 列表中是否有足够的可用空间操作发生在用户查询线程中，显然这会阻塞用户的查询操作。倘若没有 100 个可用空闲页，那么 InnoDB 存储引擎会将 LRU 列表尾端的页移除。如果这些页中有脏页，那么需要进行 Checkpoint，而这些页是来自 LRU 列表的，因此称为 FLUSH_LRU_LIST Checkpoint。

而从 MySQL 5.6 版本，也就是 InnoDB 1.2.x 版本开始，这个检查被放在了一个单独的 Page Cleaner 线程中进行，并且用户可以通过参数 innodb_lru_scan_depth 控制 LRU 列表中可用页的数量，该值默认为 1024

Async/Sync Flush Checkpoint

重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的 LSN 记为 redo_lsn，将已经刷新回磁盘最新页的 LSN 记为 checkpoint lsn，则可定义: checkpoint_age = redo_lsn - checkpoint_lsn

async_water_mark = 75% * total_redo_log_file_size sync_water_mark = 90% * total_redo_log_file_size 若每个重做日志文件的大小为 1GB，并且定义了两个重做日志文件，则重做日志文件的总大小为 2GB。async_water_mark=1.5GB，sync_water_mark=1.8GB

原理

checkpoint_age<async_water_mark 时，不需要刷新任何脏页到磁盘

async_water_mark < checkpoint age < sync_water_mark 时触发 Async Flush，从 Flush 列表中刷新足够的脏页回磁盘，使得刷新后满足 checkpoint age < async_water_ mark

checkpoint_age>sync_water_mark 这种情况一般很少发生，除非设置的重做日志文件太小，并且在进行类似 LOAD DATA 的 BULK INSERT 操作。此时触发 Sync Flush 操作，从 Flush 列表中刷新足够的脏页回磁盘，使得刷新后满足 checkpoint_age<async_water_mark

Async/Sync Flush Checkpoint 是为了保证redo log的循环使用的可用性

在 InnoDB 1.2.x 版本之前，Async Flush Checkpoint 会阻塞用户查询线程，而 Sync Flush Checkpoint 会阻塞所有的用户查询线程，并且等待脏页刷新完成。从 InnoDB 1.2.x 版本开始也就是 MySQL 5.6 版本，这部分的刷新操作同样放入到了单独的 Page Cleaner Thread 中，故不会阻塞用户查询线程

Dirty Page too much Checkpoint

innodb_max_dirty_pages_pct

innodb_adaptive_flushing 这三个参数是为了解决 SSD 等大容量存储设备的出现而导致 innoDB 不能很好利用这类设备性能的困境而出现的

刷脏机制

相关参数

innodb_io_capacity

默认值是 200，该定义了 InnoDB 后台线程刷新脏页时的 iops （自适应刷新独立于该参数）。如果是 SSD，建议设置此值一万以上

如果该参数值设置的太低，那么 InnoDB 会认为磁盘 io 很慢，刷脏页也会很慢，这会导致 redo log 很快就满

自适应刷新

innodb_adaptive_flushing 默认开启 innodb_adaptive_flushing_lwm = 10%

此功能在 InnoDB Plugin 引入的。InnoDB 刷新脏页的规则是，当超过 innodb_max_dirty_pages_pct 设定的值后，或者当重做日志 ib_logfile 文件写满了以后，或者是机器空闲的时候，这三种情况下才会把 InnoDB_Buffer_Pool 的脏页刷入磁盘。

当写操作很频繁的时候，重做日志 ib_logfile 切换的次数就会很频繁，每当一个写满后，就会进行大批量将脏页刷入磁盘，会对系统的整体性能造成不小的影响。为了避免过大的磁盘 IO,innodb_adaptive_flushing 自适应刷新，会根据重做日志 ib_logfile 生成的速度和刷新频率来将脏页刷入磁盘，这样重做日志 ib_logfile 还没有写满时，也可以刷新一定的量

其他重要参数

innodb_flush_method

前置知识："延迟写" 减少了磁盘读写次数，但是却降低了文件内容的更新速度，使得欲写到文件中的数据在一段时间内并没有写到磁盘上。当系统发生故障时，这种延迟可能造成文件更新内容的丢失。为了保证磁盘上实际文件系统与缓冲区高速缓存中内容的一致性，UNIX 系统提供了 sync、fsync 和 fdatasync 三个函数

sync 函数只是将所有修改过的块缓冲区排入写队列，无需等待写磁盘操作便返回。系统守护进程 (update) 会周期性 (30 秒) 调用 sync 函数来刷新内存缓冲区。

fsync 函数只对由文件描述符 filedes 指定的单一文件起作用，需要等待写磁盘操作结束后才返回，确保修改过的数据被写入到磁盘。fsync 不但更新数据，还更新指定文件元数据 (如修改时间和访问时间)。

fdatasync 函数与 fsync 函数类似，但 fdatasync 函数只更新数据，而不更新指定文件的属性

复制

异步复制

半同步复制

5.5版本之后引入

分类

AFTER_COMMIT

非默认，即主库写入日志到 binlog，等待 binlog 复制到从库，接着主库提交自己的本地事务，等待从库返回主库一个成功的响应，然后主库返回提交事务成功的响应给客户端。

另外一种即在 MySQL 5.7 默认的方式，主库把日志写入 binlog，并且复制给从库，然后开始等待从库的响应，从库返回说成功给主库了，主库再提交事务，接着返回提交事务成功的响应给客户端。

开启操作

安装插件

install plugin rpl_semi_sync_master soname 'semisync_master.so'; set global rpl_semi_sync_master_enabled=on; show plugins;

重启从库的IO线程

stop slave io_thread; start slave io_thread;

全同步复制

复制架构衍生史

在2000年，MySQL 3.23.15版本引入了Replication。这个时候的Replicaton的实现涉及到两个线程，一个在Master，一个在Slave。Slave的I/O和SQL功能是作为一个线程，从Master获取到event后直接apply，没有relay log。这种方式使得读取event的速度会被Slave replay速度拖慢，当主备存在较大延迟时候，会导致大量binary log没有备份到Slave端。

在2002年，MySQL 4.0.2版本将Slave端event读取和执行独立成两个线程(IO线程和SQL线程)，同时引入了relay log。IO线程读取event后写入relay log，SQL线程从relay log中读取event然后执行。这样即使SQL线程执行慢，Master的binary log也会尽可能的同步到Slave。当Master宕机，切换到Slave，不会出现大量数据丢失。

在2010年MySQL 5.5版本引入了半同步复制，主库在应答客户端提交的事务前需要保证至少一个从库接收并写到relay log中。

在2016年，MySQL在5.7.17中引入了一个全新的技术，称之为InnoDB Group Replication。目前官方MySQL 5.7.17基于Group replication的全同步技术已经问世，全同步技术带来了更多的数据一致性保障。基于Paxos的分布式一致性算法实现，由若干个节点共同组成一个复制组，一个事务的提交，必须经过组内大多数节点（N / 2 + 1）决议并通过，才能得以提交。

主从延迟如何处理

查看延迟时间

使用工具percona-toolkit工具集里的 pt-heartbeat. 它会在主库创建一个heartbeat表，定时线程更新该表的时间戳字段，从库线程负责检查从库同步过来的heartbeat表里的时间戳。对比两边的时间戳就可以指定主从同步落后的时间

解决方式

让从库使用多线程并行复制数据，即设置 slave_parallel_workers > 0 , 然后将 slave_parallel_type设置为LOGICAL_CLOCK

数据拆分

分区

将单表数据按照一定规则拆分为多个物理数据文件，如按范围，哈希散列等规则拆分，侧重读写性能

分表

将单个大表拆分为多个子表，侧重并发性能

分库

将不同的表分到不同的库中

分片

分库+分表

监控

主从延迟监控

精确做法，判断主从二进制文件的偏移量

简单做法，通过查看从节点中的Seconds_Behind_Master查看

主从数据一致性检测

TPS/QPS

Innodb线程堵塞

主从同步延时

1. 分库，将一个主库分为多个主库，每个主库的写并发压力减少了几倍，此时主从延时可以忽略不计

2. 打开MySQL支持的并行复制，多个库并行复制。若某个库的写并发很高，单库达到2k/s，则并行复制还是无意义

3. 重写代码，插入数据后立马查询可能查不到

高可用

主从同步

主从不一致如何处理（存在时延）

1. 若业务可接受则可以忽略

2. 强制读主，高可用主库，用缓存提高性能

3. 在缓存中记录哪些记录发生过写请求，来路由读主还是读从

读写分离

模式

主从模式

双节点主从 + keepalived/heartbeat

多节点主从+MHA/MMM

多节点主从+etcd/zookeeper

死锁原因及解决方案

1. 事务中对资源的访问顺序不一致

当一个事务同时更新多个表并且使用了不同的顺序，可能会导致死锁的发生

例如，事务 A 首先更新表 X，然后获取锁，并在未释放锁的情况下尝试更新表 Y；而事务 B 首先更新表 Y，然后获取锁，并在未释放锁的情况下尝试更新表 X。这种情况下，两个事务会相互等待对方的锁释放，从而形成死锁。

2. 并发修改同一条记录

3. 索引不当

如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。

4. 索引顺序不一致

当多个事务按照不同的顺序访问相同的数据行，并且使用了不同的索引时，可能会发生死锁. 例如，事务A 按照索引 1 的顺序访问数据行，事务B 按照索引 2 的顺序访问同一组数据行，这样两个事务之间就会产生死锁。select ... from table for update

5. 事务嵌套

当一个事务内部开启了另一个事务，并在内层事务中更新了某个表，而外层事务也需要更新该表的同一行记录时，就有可能发生死锁。因为外层事务需要等待内层事务释放锁，而内层事务需要等待外层事务释放锁。

数据类型优化

1. 将IP地址转为整型存储，INET_ATON

char

会自动删除末尾的空格

: 固定长度的非 Unicode 字符数据，最大长度为 8,000 个字符

nchar

固定长度的 Unicode 数据，最大长度为 4,000 个字符

varchar

可变长度的非 Unicode 数据，最长为 8,000 个字符

nvarchar

可变长度 Unicode 数据，其最大长度为 4,000 字符

datetime

占用8字节，与时区无关，可精确到毫秒

timestamp

占用4字节，精确到秒，采用整型存储，依赖系统时区

date

占用3字节，date类型用于保存 1000-01-01 到 9999-12-31 之间的日期

可直接使用日期函数进行日期计算

enum

底层存储的是整型值

select col+1 from enum_test; 其中col表示枚举列名

int

int (x) 中的 x 代表是其宽度，其宽度值并不会影响存储的范围，而只是根据定义的宽度进行了显示的格式化而已

当定义一个无符号 int 类型，且设置了 ZEROFILL 属性时，如果数值的长度没有达到显示的宽度时，MySQL 会自动的在数值前面补零直至达到定义的宽度

压测工具

sysbench

测试类型

oltp_read_write

oltp_read_only

oltp_write_only

oltp_delete

oltp_update_index

oltp_update_non_index

oltp_insert

安装

CPU监控

top

磁盘IO

dstat

分支主题

字段分析

dsk/total -

IO吞吐量

--io/total-

随机IO每秒执行多少次

-net/total-

每秒钟网卡接收及发送的速率

事务

原子性（A）

undo log

一致性（C）

隔离性（I）

锁

gap-lock

record-lock

next-key lock

mvcc

持久性（D）

redo log

隔离级别

读未提交（Read UnCommit）

读已提交（Read Commited）

可重复读（Repetable Read）

串行化（Serialazable）

存储引擎

类型

InnoDB

并发控制

锁

共享锁

排他锁

数据多版本

redo日志

1. 事务提交的数据先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，即随机写变为顺序写

2. 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。

undo日志

当事务回滚时，或者数据库崩溃时，可使用undo日志来恢复数据

对insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除

对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；

回滚端 rollback segment

行数据的三个内部属性

DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID

DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针

DB_ROW_ID，6字节，单调递增的行ID

trx_sys 事务链

事务链表中保存的都是还未提交的事务，事务一旦被提交，则会被从事务链表中摘除。

ReadView

low_trx_id

表示该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号；

up_trx_id

表示该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务；

trx_ids

表示所有事务链表中事务的id集合

在RC隔离级别下，每个快照读都会生成并获取最新的Read View，而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个ReadView

通俗理解

每次SQL启动都会构造一个ReadView，该结构包含当前事务链的最大事务id及最小事务id，某条数据对事务是否可见就以此结构来判断

1. 若某条数据的事务id小于最小事务id，说明修改该行的事务在当前事务执行前就已经提交完成，故对当前事务可见

2. 若某条数据的事务id大于最大事务id，说明修改该行记录的事务在当前事务之后，所以对于当前事务来说是不可见的。

3. 对于事务id在最大事务id与最小事务id之间的数据需要根据隔离级别进行判断

1. 对于RC级别，对于事务执行过程中，已经提交的事务的数据，对当前事务是可见的

2. 对于RR隔离级别来说，事务启动时，已经开始的事务链表中的事务的所有修改都是不可见的

InnoDB为何能够做到这么高的并发

锁类型

记录锁 Record Locks

间隙锁 Gap Locks

插入意向锁

临键锁 Next-Key Locks

自增锁 Auto-inc Locks

共享/排它锁(Shared and Exclusive Locks)

意向锁(Intention Locks)

分类

意向排它锁(intention exclusive lock, IX)

意向共享锁(intention shared lock, IS)

意向锁协议(intention locking protocol)

事务要获得某些行的S锁，必须先获得表的IS锁

事务要获得某些行的X锁，必须先获得表的IX锁

插入意向锁

表级锁

表锁

元数据集锁

对一张表进行 CRUD 操作时，加的是 MDL 读锁

对一张表做结构变更操作的时候，加的是 MDL 写锁

当有线程在执行 select 语句（加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（释放 MDL 读锁）。

反之，当有线程对表结构进行变更（加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（释放 MDL 写锁）

意向锁

在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」

在使用 InnoDB 引擎的表里对某些记录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突

意向锁的目的是为了快速判断表里是否有记录被加锁

AUTO-INC 锁

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放

AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞

在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁

innodb_autoinc_lock_mode 用来控制选择用 AUTO-INC 锁，还是轻量级的锁

当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；

当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题

当 innodb_autoinc_lock_mode = 1：

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

行级锁

Record Lock

Gap Lock

Next-key Lock

MyISAM

不支持外键

对比

MyIASM

不支持事务

不支持行级锁和外键

故在插入及更新时需要锁住整个表

适合读操作远多于写操作

引擎自身保存了表的行数，故不需要全表扫描

索引同样使用B+树

索引文件和数据文件分离

支持全文索引

InnoDB

数据文件本身就是索引文件

InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大

不支持全文索引

七种连接

索引

定义

优势

劣势

分类

单值索引

唯一索引

复合索引

覆盖索引

前缀索引

相对于整列建立索引，前缀索引仅仅是选择该列的部分字符作为索引，减少索引的字符可以节约索引空间，从而提高索引效率，但这样也会降低索引的选择性

索引的选择性，它是指不重复的索引值（也称为基数 cardinality) 和数据表的记录总数的比值，范围从 1/(数据表记录总数) 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。选择性为 1 的索引叫唯一索引，这是最好的索引选择性，性能也是最好的。建立合理前缀索引的诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的基数应该接近于完整列的基数

案例

创建表及数据

查看完整列索引选择性

找出合适的前缀索引

创建前缀索引

缺点

mysql 无法使用其前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描

索引结构

B树索引

Hash索引

全文索引

R-Tree 索引

数据结构

B树

叶子节点，非叶子节点，都存储数据

中序遍历，可以获得所有节点

B+树

非叶子节点不再存储数据，数据只存储在同一层的叶子节点上

叶子之间，增加了链表，获取所有节点，不再需要中序遍历

性能分析

mysql 查询优化器

案例

1. count(1) 与 count(*)对比

MySql 5.6之前未作优化，故count(*)会引起全表扫描

MySql 5.6之后都会用成本最小的辅助索引来查询

索引执行成本

组成

IO成本

即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关

CPU成本

将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。

常见瓶颈

CPU

IO

explain

能做什么

表的读取顺序

数据读取操作的操作类型

可能用到哪些索引

实际用到哪些索引

表之间的引用

每张表有多少行被优化器查询

列名解析

id

id相同

id 不同

ID相同又不同

select_type

Simple

Primary

SubQuery

Derived

Union

Union result

table

type

system

const

equ_ref

ref

range

index

all

possible_keys

key

key_len

ref

rows

Extra

Using filesort

Using temporary

Using index

Using index condition

经典的索引下推，虽然命中了索引，但并不是严格匹配，需要使用索引进行扫描对比，最后再进行回表

using where

using join buffer

impossible where

select table optimized away

distinct

哪些情况需要创建索引

主键自动创建唯一索引

频繁作为查询条件的字段应该创建索引

查询中与其他表关联的字段，外键关系创建索引

频繁更新的字段不合适建索引

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

查询中统计或分组的字段

查询截取分析

查询优化

永远是小表驱动大表

order by

若排序的字段不在索引列上，则mysql会启用两种filesort算法

双路排序

单路排序

优化策略

增大sort_buffer_size参数

增大 max_length_for_sort_data 参数

group by

慢查询日志

开启 set global slow_query_log=1;

阀值 long_query_time

日志分析工具 mysqldumpslow