MySql知识体系
2024-06-07 21:02:27 0 举报AI智能生成
为你推荐
查看更多
MySql知识体系
作者其他创作
大纲/内容
如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就对 id 值判断是否为 NULL，如果不为 NULL，就将 count 变量加 1
如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引，这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引
select count(id) from t_order; //id 为主键值
如果表里只有主键索引，没有二级索引时，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1
如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了
select count(1) 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 (*) from t_order
若 col_name 没有 加索引，则会采用全表扫描的方式来计数
select count (col_name) from t_order
在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端
1. select count(1) 与 select count(*)
常见问题
使用临时表对 semi-join 产生的结果集去重
DuplicateWeedout
只选用内部表的第 1 条与外表匹配的记录
FirstMatch
把 inner-table 数据基于索引进行分组，取每组第一条数据进行匹配。
LooseScan
将 inner-table 去重物化成临时表，遍历 outer-table，然后在物化表上去寻找匹配
Materializelookup
将 inner-table 去重物化成临时表，遍历物化表，然后在 outer-table 上寻找匹配
MaterializeScan
半连接优化Semi-join 策略
1. 在执行一个SQL查询很多数据前buffer pool 缓存页全部已用完。此时需要将大部分缓存页刷新到磁盘中就会导致查询很慢
2. 在执行更新语句时，redo log在磁盘上的所有文件都已写满，此时需要回到第一个redo log 文件覆盖写，此时同样会导致redo log 被刷入磁盘
NUMA 默认是使用 CPU 亲和的内存分配策略，即请求进程会从当前所处的 CPU 的 Node 请求分配内存。当某个需要消耗大量内存的进程耗尽了所处的 Node 的内存时，就会导致产生 swap，不会从远程 Node 分配内存，这就是 swap insanity 现象。MySQL 数据库是单进程多线程的架构，在开启的 NUMA 服务器中，内存被分配到各 NUMA Node 上，而 MySQL 进程只能消耗所在节点的内存。所以在开启 NUMA 的服务器上，某些特殊场景中容易出现系统拥有空闲内存但发生 SWAP 导致性能问题的情况。
3. 系统可能开启了 NUMA（Non Uniform Memory Access）
可能原因
1. 减少缓存页 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 减少刷新缓冲页的数量
2. 提升缓存页 flush 到磁盘的速度
分支主题
1. 硬件层面上关闭，通过BIOS关闭
For RHEL 6# vi /boot/grub/grub.confkernel /vmlinuz-2.6.39-400.215.10.EL ro root=/dev/VolGroup00/LogVol00 numa=off
For RHEL 7：# vi /etc/default/grubGRUB_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
2. 内核层面关闭，在 Linux Kernel 启动参数中加上 numa=off 后重启服务器
数据库层，在 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
3. 通过 MySQL参数来关闭
3.关闭 NUMA
解决方案
性能抖动
（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，这就是说每秒从冷数据区域进入热数据区域的缓存页的数量，以及每秒在冷数据区域里被访问了但是不能进入热数据区域的缓存页的数量
（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：现在正在读取磁盘页的数量
SHOW ENGINE INNODB STATUS
缓存页（每页默认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。
比如全表扫描会导致随机读
随机预读
预读机制
冷热数据比例是37 即innodb_old_blocks_pct=37，冷数据占比37%
基于冷热数据分离思想而设计
数据第一次被加载到缓存页时会先被放到冷数据区链表的头部
热数据更新：为减少数据移动带来的性能损耗，只有在热数据区域的后3/4部分的缓存页被访问时，才会将其移动到链表头部
在数据被加载到缓存页 innodb_old_blocks_time (默认1000)毫秒后，再次有数据访问请求时才将该缓存页放到热数据区链表的头部
预读机制和全表扫描加载近来的缓存页都会被放到冷数据区，如果此时缓存页不够时，就需要从冷数据区尾部的缓存页，将其刷入磁盘
LRU算法
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倍数
生产环境如何配置
show status like 'Innodb_buffer_pool_read_%';
innodb_buffer_pool_reads： 表示 InnoDB 缓冲池无法满足的请求数，需要从磁盘中读取
innodb_buffer_pool_read_requests： 表示从InnoDb缓冲池读取页的请求数
可根据Innodb缓存命中率情况做出调整，若命中率低于90%则需要增加Buffer Pool的内存
如何调整Buffer Pool大小
Buffer Pool (默认128M)
即存储系统每秒可以执行多少次磁盘读写操作。该指标对数据库 crud 操作的 QPS 影响非常大，几乎决定了每秒可执行 SQL 语句的个数，底层存储的 IOPS 越高，数据库的并发能力就越高。
IOPS
即每个随机读写操作的耗时
响应延迟
关键性能指标
随机读
1. 如果写入 redo log buffer 的日志已经占据了 redo log buffer 总容量的一半，即超过了 8MB ，此时就会把他们刷入到磁盘文件中
2. 一个事务提交的时候，必须把 redo log 所在的 redo log block 都刷入到磁盘文件里，只有这样，当事务提交之后，修改的数据才不会丢失，因为 redo log 里有重做日志，随时可以恢复事务做的修改
3. 后台线程每隔 1 秒定时刷新
4. MySQL 关闭的时候，redo log block 都会刷入到磁盘里
redo log block何时刷入磁盘
0：提交事务的时候，不会将内存中的 redo log 刷入磁盘。优势，纯内存操做速度快，缺点，redo 日志没有落地磁盘，若是提交事务的一瞬间，MySQL 宕机，那么若是是修改数据，内存数据没了，磁盘也没来的及更新，就丢失了本次修改操做
1：提交事务以前必定会将 redo log 刷入磁盘。优势，事务提交以前，事务操做 log 必定刷入磁盘，事务成功，磁盘必定有 redo 日志，若是事务提交成功，内存修改，磁盘尚未更新，彻底能够读取 redo 日志恢复数据。缺点，写磁盘确实会消耗不少性能，若是是高并发，大量写入，必定会影响写入性能，吞吐量和处理时间都会影响到。
2： 将 redo 日志刷入 OS cache，间隔可能一秒写入磁盘。方案鉴于一和二方案之间。优势，利用 OS cache 去缓存部分日志，能够提升吞吐量，间隔时间，异步刷入磁盘。缺点，提交事务以后，可能 redo 日志还在 cache 中。此时，日志存在丢失的风险。
innodb_flush_log_at_trx_commit
查看目录 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
redo log参数
redo log
对于数据需要频繁更新的场景，要实时更新，对于 MySQL 来说，只处理 IO，就能把性能耗尽。
如果说 redo log 写满之后，这时候系统的所有更新操作都会进行停止。redo log 是顺序写的，redo log 的大小是固定的，比如可以一组配置 4 个文件，每个文件大小是 1G，那么这个 redo log 可以记录 4G。write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。 checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。 write pos 和 checkpoint 中间空着的部分，就是可以写入的部分，如果 write pos 追上了 checkpoint ，代表写满了，就需要停下擦除一部分，把 checkpoint 推进一下
Redo 日志大小也是有限的，通过刷新策略，可以更有效的重复使用文件，不需要开辟新的空间。
当缓冲池不够用时，根据 LRU 算法会溢出最近最少使用的页，若此页为脏页，那么需要强制执行 Checkpoint，将脏页刷回磁盘
数据库宕机，崩溃恢复期间，需要从上次的检查点进行恢复，使得效率提升，以便缩短数据库的恢复时间
物理备份日志点。
作用
发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb_fast_shutdown=1。但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint 进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘
Sharp Checkpoint (全量检查点)
对于 Master Thread 中发生的 Checkpoint，差不多以每秒或每十秒的速度从buffer pool 的脏页列表中刷新一定比例的页回磁盘。这个过程是异步的，即此时 InnoDB 存储引擎可以进行其他的操作，用户查询线程不会阻塞
Master Thread 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
FLUSH_LRU_LIST Checkpoint
重做日志文件不可用的情况，这时需要强制将一些页刷新回磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的 LSN 记为 redo_lsn，将已经刷新回磁盘最新页的 LSN 记为 checkpoint lsn，则可定义: checkpoint_age = redo_lsn - checkpoint_lsn
async_water_mark = 75% * total_redo_log_file_sizesync_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 中，故不会阻塞用户查询线程
Async/Sync Flush Checkpoint
innodb_max_dirty_pages_pct
innodb_adaptive_flushing 这三个参数是为了解决 SSD 等大容量存储设备的出现而导致 innoDB 不能很好利用这类设备性能的困境而出现的
Dirty Page too much Checkpoint
Fuzzy Checkpoint (模糊检查点)
分类
checkpoint
默认值是 200，该定义了 InnoDB 后台线程刷新脏页时的 iops （自适应刷新独立于该参数）。如果是 SSD，建议设置此值一万以上
如果该参数值设置的太低，那么 InnoDB 会认为 磁盘 io 很慢，刷脏页也会很慢，这会导致 redo log 很快就满
innodb_io_capacity
相关参数
innodb_adaptive_flushing 默认开启innodb_adaptive_flushing_lwm = 10%
此功能在 InnoDB Plugin 引入的。InnoDB 刷新脏页的规则是，当超过 innodb_max_dirty_pages_pct 设定的值后，或者当重做日志 ib_logfile 文件写满了以后，或者是机器空闲的时候，这三种情况下才会把 InnoDB_Buffer_Pool 的脏页刷入磁盘。
自适应刷新
刷脏机制
sync 函数只是将所有修改过的块缓冲区排入写队列，无需等待写磁盘操作便返回。系统守护进程 (update) 会周期性 (30 秒) 调用 sync 函数来刷新内存缓冲区。
fsync 函数只对由文件描述符 filedes 指定的单一文件起作用，需要等待写磁盘操作结束后才返回，确保修改过的数据被写入到磁盘。fsync 不但更新数据，还更新指定文件元数据 (如修改时间和访问时间)。
fdatasync 函数与 fsync 函数类似，但 fdatasync 函数只更新数据，而不更新指定文件的属性
前置知识："延迟写" 减少了磁盘读写次数，但是却降低了文件内容的更新速度，使得欲写到文件中的数据在一段时间内并没有写到磁盘上。当系统发生故障时，这种延迟可能造成文件更新内容的丢失。为了保证磁盘上实际文件系统与缓冲区高速缓存中内容的一致性，UNIX 系统提供了 sync、fsync 和 fdatasync 三个函数
由于 fsync 函数除需要更新数据外，还需要更新文件元数据 (metadata，包括文件大小 / 访问时间 / 修改时间等)，因此 fsync 函数需要两次写，而 fdatasync 函数值需要一次写
innodb_flush_method
其他重要参数
InnoDB 内部机制
异步复制
5.5版本之后引入
非默认，即主库写入日志到 binlog，等待 binlog 复制到从库，接着主库提交自己的本地事务，等待从库返回主库一个成功的响应，然后主库返回提交事务成功的响应给客户端。
AFTER_COMMIT
另外一种即在 MySQL 5.7 默认的方式，主库把日志写入 binlog，并且复制给从库，然后开始等待从库的响应，从库返回说成功给主库了，主库再提交事务，接着返回提交事务成功的响应给客户端。
install plugin rpl_semi_sync_master soname 'semisync_master.so';set global rpl_semi_sync_master_enabled=on;show plugins;
安装插件
stop slave io_thread; start slave io_thread;
重启从库的IO线程
开启操作
半同步复制
全同步复制
在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表里的时间戳。对比两边的时间戳就可以指定主从同步落后的时间
查看延迟时间
解决方式
主从延迟如何处理
复制
将单表数据按照一定规则拆分为多个物理数据文件，如按范围，哈希散列等规则拆分，侧重读写性能
分区
将单个大表拆分为多个子表，侧重并发性能
分表
将不同的表分到不同的库中
分库
分库+分表
分片
数据拆分
精确做法，判断主从二进制文件的偏移量
简单做法，通过查看从节点中的Seconds_Behind_Master查看
主从延迟监控
主从数据一致性检测
TPS/QPS
Innodb线程堵塞
监控
1. 分库，将一个主库分为多个主库，每个主库的写并发压力减少了几倍，此时主从延时可以忽略不计
2. 打开MySQL支持的并行复制，多个库并行复制。若某个库的写并发很高，单库达到2k/s，则并行复制还是无意义
3. 重写代码，插入数据后立马查询可能查不到
主从同步延时
1. 若业务可接受则可以忽略
2. 强制读主，高可用主库， 用缓存提高性能
3. 在缓存中记录哪些记录发生过写请求，来路由读主还是读从
主从不一致如何处理（存在时延）
主从同步
读写分离
双节点主从 + keepalived/heartbeat
多节点主从+MHA/MMM
多节点主从+etcd/zookeeper
主从模式
模式
高可用
当一个事务同时更新多个表并且使用了不同的顺序，可能会导致死锁的发生
例如，事务 A 首先更新表 X，然后获取锁，并在未释放锁的情况下尝试更新表 Y；而事务 B 首先更新表 Y，然后获取锁，并在未释放锁的情况下尝试更新表 X。这种情况下，两个事务会相互等待对方的锁释放，从而形成死锁。
1. 事务中对资源的访问顺序不一致
2. 并发修改同一条记录
如果在事务中执行了一条不满足条件的语句，执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞
SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
3. 索引不当
当多个事务按照不同的顺序访问相同的数据行，并且使用了不同的索引时，可能会发生死锁. 例如，事务A 按照索引 1 的顺序访问数据行，事务B 按照索引 2 的顺序访问同一组数据行，这样两个事务之间就会产生死锁。select ... from table for update
4. 索引顺序不一致
当一个事务内部开启了另一个事务，并在内层事务中更新了某个表，而外层事务也需要更新该表的同一行记录时，就有可能发生死锁。因为外层事务需要等待内层事务释放锁，而内层事务需要等待外层事务释放锁。
5. 事务嵌套
死锁原因及解决方案
1. 将IP地址转为整型存储，INET_ATON
会自动删除末尾的空格
char
nchar
varchar
nvarchar
占用8字节，与时区无关，可精确到毫秒
datetime
占用4字节，精确到秒，采用整型存储，依赖系统时区
timestamp
占用3字节，date类型用于保存 1000-01-01 到 9999-12-31 之间的日期
可直接使用日期函数进行日期计算
date
底层存储的是整型值
select col+1 from enum_test; 其中col表示枚举列名
enum
int (x) 中的 x 代表是其宽度，其宽度值并不会影响存储的范围，而只是根据定义的宽度进行了显示的格式化而已
当定义一个无符号 int 类型，且设置了 ZEROFILL 属性时，如果数值的长度没有达到显示的宽度时，MySQL 会自动的在数值前面补零直至达到定义的宽度
int
数据类型优化
oltp_read_write
oltp_read_only
oltp_write_only
oltp_delete
oltp_update_index
oltp_update_non_index
oltp_insert
测试类型
安装
sysbench
top
CPU监控
IO吞吐量
dsk/total -
随机IO每秒执行多少次
--io/total-
每秒钟网卡接收及发送的速率
-net/total-
字段分析
dstat
磁盘IO
压测工具
undo log
原子性（A）
一致性（C）
gap-lock
record-lock
next-key lock
锁
mvcc
隔离性（I）
持久性（D）
事务
读未提交（Read UnCommit）
读已提交（Read Commited）
可重复读（Repetable Read）
串行化（Serialazable）
隔离级别
共享锁
排他锁
共享锁之间不互斥，简记为：读读可以并行排他锁与任何锁互斥，简记为：写读，写写不可以并行
1. 事务提交的数据先写到redo日志里（此时变成了顺序写），再定期将数据刷到磁盘上，即随机写变为顺序写
2. 若数据库崩溃，还没来得及刷盘的数据，在数据库重启后，会重做redo日志里的内容，以保证已提交事务对数据产生的影响都刷到磁盘上。
redo日志
用于保障已提交事务的ACID特性
对insert操作，undo日志记录新数据的PK(ROW_ID)，回滚时直接删除
对于delete/update操作，undo日志记录旧数据row，回滚时直接恢复；
当事务回滚时，或者数据库崩溃时，可使用undo日志来恢复数据
用于保障未提交事务不会对数据库的ACID特性产生影响
undo日志
回滚端 rollback segment
DB_TRX_ID，6字节，记录每一行最近一次修改它的事务ID
DB_ROLL_PTR，7字节，记录指向回滚段undo日志的指针
DB_ROW_ID，6字节，单调递增的行ID
行数据的三个内部属性
事务链表中保存的都是还未提交的事务，事务一旦被提交，则会被从事务链表中摘除。
trx_sys 事务链
表示该SQL启动时，当前事务链表中最大的事务id编号，也就是最近创建的除自身以外最大事务编号；
low_trx_id
表示该SQL启动时，当前事务链表中最小的事务id编号，也就是当前系统中创建最早但还未提交的事务；
up_trx_id
表示所有事务链表中事务的id集合
trx_ids
ReadView
概要
1. 若某条数据的事务id小于最小事务id，说明修改该行的事务在当前事务执行前就已经提交完成，故对当前事务可见
2. 若某条数据的事务id大于最大事务id，说明修改该行记录的事务在当前事务之后，所以对于当前事务来说是不可见的。
1. 对于RC级别，对于事务执行过程中，已经提交的事务的数据，对当前事务是可见的
2. 对于RR隔离级别来说，事务启动时，已经开始的事务链表中的事务的所有修改都是不可见的
3. 对于事务id在最大事务id与最小事务id之间的数据需要根据隔离级别进行判断
每次SQL启动都会构造一个ReadView，该结构包含当前事务链的最大事务id及最小事务id，某条数据对事务是否可见就以此结构来判断
通俗理解
数据多版本
InnoDB为何能够做到这么高的并发
并发控制
记录锁 Record Locks
插入意向锁
间隙锁 Gap Locks
临键锁 Next-Key Locks
自增锁 Auto-inc Locks
共享/排它锁(Shared and Exclusive Locks)
事务要获得某些行的S锁，必须先获得表的IS锁
事务要获得某些行的X锁，必须先获得表的IX锁
意向锁协议(intention locking protocol)
意向锁(Intention Locks)
表锁
对一张表进行 CRUD 操作时，加的是 MDL 读锁
对一张表做结构变更操作的时候，加的是 MDL 写锁
当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。
反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）
元数据集锁
在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」
在使用 InnoDB 引擎的表里对某些记录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」
意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突
意向锁的目的是为了快速判断表里是否有记录被加锁
意向锁
AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放
AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞
在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增
一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁
当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
性能最高的方式，但是当搭配 binlog 的日志格式是 statement 一起使用的时候，在「主从复制的场景」中会发生数据不一致的问题
当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
普通 insert 语句，自增锁在申请之后就马上释放；
类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；
当 innodb_autoinc_lock_mode = 1：
innodb_autoinc_lock_mode 用来控制选择用 AUTO-INC 锁，还是轻量级的锁
AUTO-INC 锁
表级锁
Record Lock
Gap Lock
Next-key Lock
行级锁
锁类型
锁定索引记录+间隔，防止幻读
InnoDB
不支持外键
MyISAM
类型
不支持事务
故在插入及更新时需要锁住整个表
不支持行级锁和外键
适合读操作远多于写操作
引擎自身保存了表的行数，故不需要全表扫描
索引同样使用B+树
索引文件和数据文件分离
支持全文索引
MyIASM
InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
数据文件本身就是索引文件
不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大
不支持全文索引
对比
存储引擎
七种连接
定义
优势
劣势
单值索引
唯一索引
覆盖索引
复合索引
相对于整列建立索引，前缀索引仅仅是选择该列的部分字符作为索引，减少索引的字符可以节约索引空间，从而提高索引效率，但这样也会降低索引的选择性
索引的选择性，它是指不重复的索引值（也称为基数 cardinality) 和数据表的记录总数的比值，范围从 1/(数据表记录总数) 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。选择性为 1 的索引叫唯一索引，这是最好的索引选择性，性能也是最好的。建立合理前缀索引的诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。前缀应该足够长，以使得前缀索引的选择性接近于索引的整个列。换句话说，前缀的基数应该接近于完整列的基数
查看完整列索引选择性
找出合适的前缀索引
创建前缀索引
创建表及数据
案例
mysql 无法使用其前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描
缺点
前缀索引
B树索引
Hash索引
全文索引
R-Tree 索引
索引结构
对于分组、排序、范围查找的查询哈希型的索引，时间复杂度会退化为O(n)，而树型的“有序”特性，依然能够保持O(log(n)) 的高效率
叶子节点，非叶子节点，都存储数据
中序遍历，可以获得所有节点
B树
非叶子节点不再存储数据，数据只存储在同一层的叶子节点上
叶子之间，增加了链表，获取所有节点，不再需要中序遍历
B+树
1. 范围查找，定位min与max之后，中间叶子节点，就是结果集，不用中序回溯
2. 叶子节点存储实际记录行，记录行相对比较紧密的存储，适合大数据量磁盘存储；非叶子节点存储记录的PK，用于查询加速，适合内存存储；
3. 非叶子节点，不存储实际记录，而只存储记录的KEY的话，那么在相同内存的情况下，B+树能够存储更多索引；
MySql 5.6之前未作优化，故count(*)会引起全表扫描
MySql 5.6之后都会用成本最小的辅助索引来查询
1. count(1) 与 count(*)对比
即从磁盘把数据加载到内存的成本，默认情况下，读取数据页的 IO 成本是 1，MySQL 是以页的形式读取数据的，即当用到某个数据时，并不会只读取这个数据，而会把这个数据相邻的数据也一起读到内存中，这就是有名的程序局部性原理，所以 MySQL 每次会读取一整页，一页的成本就是 1。所以 IO 的成本主要和页的大小有关
IO成本
将数据读入内存后，还要检测数据是否满足条件和排序等 CPU 操作的成本，显然它与行数有关，默认情况下，检测记录的成本是 0.2。
CPU成本
组成
索引执行成本
mysql 查询优化器
CPU
IO
常见瓶颈
表的读取顺序
数据读取操作的操作类型
可能用到哪些索引\t
实际用到哪些索引
表之间的引用
每张表有多少行被优化器查询
能做什么
id相同
id 不同
ID相同又不同
id
Simple
Primary
SubQuery
Derived
Union
Union result
select_type
table
system
const
equ_ref
ref
range
index
all
type
possible_keys
key
key_len
rows
Using filesort
Using temporary
Using index
经典的索引下推，虽然命中了索引，但并不是严格匹配，需要使用索引进行扫描对比，最后再进行回表
Using index condition
using where
using join buffer
impossible where
select table optimized away
distinct
Extra
列名解析
explain
性能分析
主键自动创建唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系创建索引
频繁更新的字段不合适建索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或分组的字段
哪些情况需要创建索引
永远是小表驱动大表
双路排序
单路排序
若排序的字段不在索引列上，则mysql会启用两种filesort算法
如果查询的列字段大于 max_length_for_sort_data 变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存
增大sort_buffer_size参数
增大 max_length_for_sort_data 参数
优化策略
order by
group by
查询优化
开启 set global slow_query_log=1;
阀值 long_query_time
s 按照何种方式排序
c 访问次数
l 锁定时间
r 返回记录
t 查询时间
al 平均锁定时间
ar 平均返回记录数
at 平均查询时间
g 后面搭配一个正则表达式
日志分析工具 mysqldumpslow
慢查询日志
1. 查看数据库是否支持
2. 运行 sql
3. show profiles; 查看执行的语句
截图
分析步骤
converting heap to myisam 表示查询结果太大，内存不够用改用磁盘
creating tmp table
copying to tmp table on disk 把内存中的临时表复制到磁盘
locked
需要注意的结论
show profile
dept表
emp表
订单表
建表
大数据插入注意事项
随机字符串函数
随机部门编号
随机状态数字
随机指定位数数字字符串
随机用户ID
创建函数
部门表dept
员工表emp
创建存储过程
执行存储过程
批量插入数据脚本
查询截取分析
从非主键索引回到主键索引的过程
回表
如 count(*)，由于主键索引存放的是整行的数据，而辅助索引仅存放的是主键，故辅助索引可以存放更多节点，做统计时效率更快
方便统计
即先在索引中查看所有条件匹配的数据再回表查询，而不是逐个回表查询
根据前面说的“最左前缀原则”，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录（即记录ID3），接下来是怎么处理的呢？当然就是从ID3开始，逐个回表，到主键索引上找出相应的记录，再比对age和ismale这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表字数
select * from tuser where name like '张 %' and age=10 and ismale=1
索引下推（Index Condition Pushdown，ICP）
概念
MySQL 会根据执行成本来决定走索引还是全表扫描
1. NOT IN 是否走索引
QA
索引
获取大数据表的总行数
优化案例
减少数据库连接次数
无需重新编译，而SQL需要经过解析编译再执行
提高代码安全，减少SQL注入
优点
移植性较差
开发调试复杂
SQL本身是一种结构化查询语言，但不是面向对象的的，本质上还是过程化的语言，面对复杂的业务逻辑，过程化的处理会很吃力
存储过程
MySql
0 条评论
回复 删除
下一页
