MySQL45讲总结

参数和命令

事务

• set autocommit=1

  • 可以预防不知情的情况下产生长事务
  • 没有手动start transaction开启事务，mysql默认也会将用户的操作当做事务即时提交

• set max_execution_time

  • 设置语句执行最长时间

• innodb_undo_tablespaces

• select * from information_schema.innodb_trx;

  • 查询当前执行中的事务

索引

• change buffer

  • 前身是insert buffer

  • merge

    • 把change buffer应用到旧的数据页，得到新的数据页的过程称为merge

    • 3种触发时机

      • 访问这个数据页
      • 后台线程，定期触发merge
      • 数据库正常关闭，执行merge

  • 好处

    • 减少读磁盘，避免占用内存，提高内存利用率

  • 局限性

    • 唯一索引的更新不能使用

  • 大小

    • innodb_change_buffer_max_size（该值为buffer pool的百分比）

• 优化器选错索引几种方式

  • force index
  • 删掉一些索引
  • 新建更合适的索引
  • 子主题 4

• 基数：show index from table_name里面有个cardinality ，基数越大，索引区分度越好

  • 索引基数统计是采样统计，Innodb默认选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。
  • 而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计

• innodb_stats_persistent 索引统计的方式

  • on：表示统计信息会持久化存储，默认N是20，M是10
  • off：表示统计信息只会存储在内容中，N是8，M是16

慢查询

• long_query_time

analyze table t 重新统计索引信息

线程

• innodb_thread_concurrency

  • 控制InnoDB的并发线程上限。
  • 就是说，一旦并发线程数达到这个值，InnoDB在接收到新请求的时候，就会进入等待状态，直到有线程退出。

kill

• kill query + 线程id

  • 终止这个线程中正在执行的语句
  • 场景：锁等待、死锁

  • 实现流程：

    • 把session B的运行状态改成THD::KILL_QUERY(将变量killed赋值为THD::KILL_QUERY)；

    • 给session B的执行线程发一个信号。

      • 发一个信号的目的，就是让session B退出等待，来处理THD::KILL_QUERY状态。

• kill connection + 线程id

  • 这里connection可缺省，表示断开这个线程的连接，当然如果这个线程有语句正在执行，也是要先停止正在执行的语句的

• kill 失效

  • 线程没有执行到判断线程状态的逻辑

  • 终止逻辑耗时较长

    • 超大事务执行期间被kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长。

    • 大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待IO资源，导致耗时较长

    • DDL命令执行到最后阶段，如果被kill，需要删除中间过程的临时文件，也可能受IO资源影响耗时
      3 问题1 Ctrl+c，是不是可以直接终止线程呢？
      答案是，不可以；这里有一个误解，其实在客户端的操作只能操作到客户端的线程，客户端和服务端只能通过网络交互，是不可能直接操作服务端线程的

  • 而由于MySQL是停等协议，所以这个线程执行的语句还没有返回的时候，再往这个连接里面继续发命令也是没有用的。实际上，执行Ctrl+C的时候，是MySQL客户端另外启动一个连接，然后发送一个kill query 命令。

net_buffer

• 由参数net_buffer_length控制默认16kb

join_buffer

• 由参数join_buffer_size设定的，默认值是256kb

技巧部分

回表

页分裂

• 子主题 1

自增主键好处

• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

重建索引

• analyze table t

  • 发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理

身份证号建立索引

• reverse 倒序存储身份证：缺点多一次cpu计算，依然是前缀索引，会增加扫描行数
• hash: crc32，缺点冲突

隐式类型转换

• 规则：mysql在字符串与数字比较的时候，把字符串转为数字
• 隐式转换相当于把字段上加上函数，所以使用不了索引

• 常见的有类型转换、字符集转换

  • 解决方案：把值转为相同类型，不要转字段 如 a=cast(b as unsigned int)

随机显示3条信息

• 内存临时表

  • order by rand() 不可取，读取行数太多
  • 使用rowid方法
  • tmp_table_size这个配置限制了内存临时表的大小，默认值是16M,超过了就会变成磁盘临时表

• 磁盘临时表

  • internal_tmp_disk_storage_engine默认引擎是innodb

• 解决方案

  • select count(*) into @C from t;
    set @Y1 = floor(@C * rand());
    set @Y2 = floor(@C * rand());
    set @Y3 = floor(@C * rand());
    select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
    select * from t limit @Y2，1；
    select * from t limit @Y3，1；

QPS 突增的问题

• 确定，业务方很快会回滚，如果有白名单，去掉白名单里面值
• 新功能 单独数据库、把用户删掉
• 与主功能耦合，只能修改压力大的语句为 select 1

MySQL IO瓶颈

• 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count参数，减少binlog的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。
• 将sync_binlog 设置为大于1的值（比较常见是100\~1000）。这样做的风险是，主机掉电时会丢binlog日志
• 将innodb_flush_log_at_trx_commit设置为2。这样做的风险是，主机掉电的时候会丢数据。

MySQL 高可用

• 主备延迟

  • show slave status

    • seconds_behind_master 显示落后时间，单位秒

    • 即便主备设置的时间不一致，也不会造成影响，会自动扣掉时间，因为备库会获取主库的时间，不一致的时候，会自动减掉差值

      • 如备库时间为38而 主库时间为35那么差值就是3，执行时间A-主库写入时间B-3 就是落后的时间

  • 延迟原因

    • 主备物理机性能差

    • 备库压力大，不加限制的读，cpu飙升，影响同步速度

      • 解决方案：binlog 输送到外部系统

    • 大事务：因为主库上必须等事务执行完成才会写入binlog，再传给备库

      • 产生大事务的原因

        • delete 删除数据太多
        • 大表的DDL

    • 备库的并行复制

      • 把原来的单线程换成多个工作线程
      • slave_parallel_workers 决定工作线程的个数

  • 对应策略

    • 可靠性优先

      • 始终判断seconds_behind_master 小于x，直到满足条件，否则一直判断，然后主库只读，再判断sbm（seconds_behind_master）=0,把备库 改为可写
      • 都改为只读的时候，数据库有段时间不可写

    • 可用性优先

      • 直接切备库，没有不可用时间，会出现数据不一致

• 主备切换

  • 基于位点的主备切换

    • 从主库的master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量

    • 问题：跳过一个事务、跳过指定错误

      • 1062错误是插入数据时唯一键冲突；
        1032错误是删除数据时找不到行。
      • slave_skip_errors
      • set global sql_slave_skip_counter = 1; 跳过事务

  • 基于GTID的主备切换

    • Global Transaction Identifier，也就是全局事务ID

      *

    • 组成：GTID=source_id\:transaction_id

    • 启动：gtid_mode=on和enforce_gtid_consistency=on就可以了。

    • 生成方式

      • gtid_next=automatic，代表使用默认值

读写分离

• 目的：分摊主库的压力
• 实现方式：客户端主动做负载均衡，把数据库连接信息放在客户端的链接查询，在mysql与客户端之间有个中间代理层

• 主从延迟

  • 强制走主库方案

    • 特殊场景下直接走主库

  • sleep方案

  • 判断主备无延迟方案

    • show slave status的结果里 seconds_behind_master参数的值，先判断是否等于0 等于0再执行查询请求

    • 对比点位确保主备无延迟

      • Master_Log_File和Read_Master_Log_Pos，表示的是读到的主库的最新位点
      • Relay_Master_Log_File和Exec_Master_Log_Pos，表示的是备库执行的最新位点。如果Master_Log_File和Relay_Master_Log_File、Read_Master_Log_Pos和Exec_Master_Log_Pos这两组值完全相同，就表示接收到的日志已经同步完成

    • 对比GTID集合确保主备无延迟

      • Auto_Position=1 ，表示这对主备关系使用了GTID协议
      • Retrieved_Gtid_Set，是备库收到的所有日志的GTID集合
      • Executed_Gtid_Set，是备库所有已经执行完成的GTID集合。
        如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
      • 特点：对比位点和对比GTID这两种方法，都要比判断seconds_behind_master是否为0更准确

  • 配合semi-sync的方案

    • 半同步复制，semi-sync replication

      • 事务提交的时候，主库把binlog发给从库；

从库收到binlog以后，发回给主库一个ack，表示收到了；

主库收到这个ack以后，才能给客户端返回“事务完成”的确认。

也就是说，如果启用了semi-sync，就表示所有给客户端发送过确认的事务，都确保了备库已经收到了这个日志。

- 等主库位点方案

    - select master_pos_wait(file, pos[, timeout]);
    - 这条命令的逻辑如下：

它是在从库执行的；

参数file和pos指的是主库上的文件名和位置；

timeout可选，设置为正整数N表示这个函数最多等待N秒。

- 等GTID方案

    - select wait_for_executed_gtid_set(gtid_set, 1);
    - 等待，直到这个库执行的事务中包含传入的gtid_set，返回0，

超时返回1

判断数据库是不是出问题

• select 1？

  • 只能说明这个库的进程还在，并不能说明主库没问题

• 查表判断

  • 建立一张表，只有一行数据，定期执行

• 更新判断

  • 常见做法是放一个timestamp字段，用来表示最后一次执行检测的时间。
  • 缺点：主备之间，都用相同的更新命令就可能出现行冲突，也就是可能会导致主备同步停止。所以，现在看来mysql.health_check 这个表就不能只有一行数据了。

为了让主备之间的更新不产生冲突，我们可以在mysql.health_check表上存入多行数据，并用A、B的server_id做主键。

• 内部统计

  • 5.6版本之后

    • performance_schema库，表 file_summary_by_event_name表里统计了每次IO请求的时间
    • select * from performance_scheme.file_summary_by_event_name='wait/io/file/innodb/innodb_log_file
    • 打开会有10%左右的性能损耗

    • 打开redo log的时间监控

      • update setup_instruments set ENABLED='YES', Timed='YES' where name like '%wait/io/file/innodb/innodb_log_file%';

      • 检测：select event_name,MAX_TIMER_WAIT FROM performance_schema.file_summary_by_event_name where event_name in ('wait/io/file/innodb/innodb_log_file','wait/io/file/sql/binlog') and MAX_TIMER_WAIT>200*1000000000;

        • 单次IO请求时间超过200毫秒属于异常，

误删数据-提桶跑路

• 场景

  • 1 使用delete语句误删除数据行

    • 方案：用flashback工具通过闪回把数据恢复，Flashback恢复数据的原理，是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保binlog_format=row 和 binlog_row_image=FULL。

  • 2 使用drop table 或者 truncate table 语句误删数据表

    • 没法用flashback 恢复，因为binlog语句只有一个truncate/drop ，即便配置了binlog_format=row
    • 解决方案：使用全量备份，加增量日志的方式

  • 3 使用drop database 语句误删除库

  • 4 使用rm命令误删整个MySQL实例

    • 分布式集群不怕删除单节点的数据
    • HA会选出新主库，保证运行正常
    • 跨机房备份，跨城市保存

• 延迟复制备库

  • 通过 CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库持续保持跟主库有N秒的延迟。

• 预防措施

  • 上线SQL审计
  • sql_safe_updates参数设置为on，忘记在delete或者update语句中写where条件，或者where条件里面没有包含索引字段的话，这条语句的执行就会报错。
  • 账号分离，避免写错命令，只给业务DML的权限，而不给truncate、drop 权限
  • 制定规范：删除表之前，先对表改名，观察一段时间，确保对业务没有影响，然后再删除

连表查询JOIN

• straight join 让mysql 按照指定方式join 前面是驱动表，后面是被驱动表
• 如果使用join语句，在可以使用被驱动表的索引的情况下，需要让小表做驱动表

• 算法

  • Index Nested-Loop Join

    • 可以用上被驱动表上的索引

  • Simple Nested-Loop Join

    • 类似于笛卡尔积
    • mysql 并未使用这种算法

  • Block Netsted-Loop Join

    • 时间复杂度跟SNLJ 一样
    • 如果被驱动表上没有可用索引，最差情况下也是两个表的笛卡尔积，但是这个笛卡尔积的操作是在内存中完成的

    • 怎么选择驱动表

      • join buffer 里面如果放不下，就会分段来放，先读取表的一部分，假设100行数据，前50行就放满了，那先读这50行，然后去链表，接着再剩下的50行，再去连表，这就是block的由来

      • 假设，驱动表的数据行数是N，需要分K段才能完成算法流程，被驱动表的数据行数是M。

      • 注意，这里的K不是常数，N越大K就会越大，因此把K表示为λ*N，显然λ的取值范围是(0,1)。

      • 扩展：在N+λNM这个式子里，λ才是影响扫描行数的关键因素，这个值越小越好。
        join_buffer_size越大，一次可以放入的行越多，分成的段数也就越少，对被驱动表的全表扫描次数就越少。

这就是为什么，你可能会看到一些建议告诉你，如果你的join语句很慢，就把join_buffer_size改大。

    - 影响

        - 可能会多次扫描被驱动表，占用磁盘IO资源
        - 判断join条件需要执行M*N次对比（M、N分别是两张表的行数），如果是大表就会占用非常多的CPU资源
        - 可能会导致Buffer Pool的热数据被淘汰，影响内存命中率。

- Batched Key Access

    - 版本限制5.6之后
    - BKA的算法是对NLJ的优化
    - 启用方式：set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'; 因为BKA以来MRR算法 所以要同时启用
    - 思路都是让join语句能够用上被驱动表上的索引来触发BKA算法，提升查询西能

• Multi-Range Read 优化

  • 原理：顺序读盘,MRR能够提升性能的核心在于，这条查询语句在索引a上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势

  • 回表

    • InnoDB在普通索引a上查到主键id的值后，再根据一个个主键id的值到主键索引上去查整行数据的过程
    • 回表过程是一行行的查数据，主键索引是一个颗b+树，每次只能根据一个主键查到一行数据。

  • 设计思路

    • 根据索引a，定位到满足条件的记录,将id 值放入 read_rnd_buffer
    • 将read_rnd_buffer中的id进行递增排序
    • 排序后的id数组，依次到主键id索引中查记录，并作为结果返回
    • read_rnd_buffer_size 参数控制 read_rnd_buffer

  • 使用方法：set opimizer_switch='mrr_cost_based=off'

查询比较慢，即使只差一条

• 场景

  • 等MDL锁

    • 排查手段：show processlist ；select blocking_pid from sys.shcema_table_lock_waits
    • 现象：Waiting for table metadata lock
    • 解决：kill 掉 连接

  • 等flush

    • 排查手段：show processlist ；
    • 现象：state 列 显示 Waiting for table flush 在Info 列有 哪个正在flush table t kill掉这个连接
    • flush tables t with read lock;

flush tables with read lock; 一般这两种sql 会引起这个情况

- 等行锁

    - 排查手段：select * from t sys.innodb_lock_waits where locked_table=`'test'.'t'
    - 解决：kill pid（这个pid 就是查出来的），连接断开会自动回滚正在执行的线程

- 查询慢

    - 没有使用到索引，全表扫描
    - 一致性读，需要从事务里慢慢查找
    - 解决：使用当前读，例如 lock in share model ，for update

事务

start transaction with consistent snapshot

• 立马启动一个事务

begin/start transaction

• 命令并不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句（第一个快照读语句），事务才真正启动。

transaction_id

• 唯一事务id，事务开始时候申请，严格递增

概念

• 当前读：更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。除了update语句外，select语句如果加锁，也是当前读
• 一致性读：同一个事务，不论在什么时候读取同一条信息结果都是一致的

binlog

• 事务执行过程中，先把日志写到binlog cache
  事务提交的时候，再把binlog cache写到binlog文件中

• binlog_cache_size

  • 单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

• write 把cache 写进文件系统的page_cache,并没有把数据持久化到磁盘，fsync 将数据持久化到磁盘

  • sync_binlog=0的时候，表示每次提交事务都只write，不fsync
  • sync_binlog=1的时候，表示每次提交事务都会执行fsync；
  • sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync
  • 建议：设置为100\~1000中的某个数值
  • 风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志

• 三种格式

  • row：比较占用空间
  • statement：可能造成主备不一致，写binlog耗费IO资源，影响执行速度；可以用来恢复数据
  • mixed：MySQL 自己判断

redo log

• 先写入到redo log buffer

• innodb_flush_log_at_trx_commit

  • 设置为0的时候，表示每次事务提交时都只是把redo log留在redo log buffer中;

  • 设置为1的时候，表示每次事务提交时都将redo log直接持久化到磁盘；

  • 设置为2的时候，表示每次事务提交时都只是把redo log写到page cache。InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘。

• 双1配置

  • 指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare 阶段），一次是binlog

• 组提交

  • LSN 日志逻辑序列号log sequence number
  • 第一个加入组的是leader
  • 组员越多 节约IOPS的效果就好

  • 提升效果

    • binlog_group_commit_sync_delay参数，表示延迟多少微秒后才调用fsync;
    • binlog_group_commit_sync_no_delay_count参数，表示累积多少次以后才调用fsync。
    • 二者是或的关系

SQL优化

order by

• sort buffer

  • explain分析的时候 Extra这一列 Using filesort,表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序称为sot_buffer

  • sort_buffer_size

    • 排序所需内存大于此值，利用磁盘排序
    • 小于此值在内存中排序

  • 如何确定用了哪种排序？

    • 查看OPTIMIZER_TRACE结果，SET optimizer_trace='enabled=on';SELECT * FROM information_schema.OPTIMIZER_TRACE\G
    • 结果中number_of_tmp_files，排序过程中使用的临时文件数，为0代表在内存中排序，否则就是在临时文件中排序

  • max_length_for_sort_data

    • 专门控制用于排序的行数据的长度的一个参数，超过此值就会更换算法

• 排序算法

  • packed_additional_fields, 全字段排序，排序过程对字符串做了“紧凑”处理,按照实际长度来分配空间的
    缺点：数据字段多，内存里能够同时放下的行数很少，要分成很多个临时文件，排序性能差
    优点：减少磁盘访问量
  • rowid，当单行的长度超过max_length_for_sort_data值就用rowid算法
    缺点：排序之后，再次根据id读表，
  • 5.6开始 引入优先队列算法

索引设计差

• 工具：gh-ost

• 古老魔法：alter +不写日志 set sql_log_bin=off

• query_rewrite :可以把输入的一种语句改写成另外一种模式。

  • insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select from t where id + 1 = ?", "select from t where id = ? - 1", "db1");
    这里为什么是flush 开头的
    call query_rewrite.flush_rewrite_rules();

• force_index

索引

覆盖索引

最左前缀原则

• 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小

联合索引

• 考虑空间大小，如果需要同时维护（name，age），（age），name字段比age大，就维护一个age索引

索引下推

• 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。没有索引下推出现之前，只能一个个回表，到主键索引上找出数据行，再对比字段值。
• 5.6版本开始出现

普通索引

唯一索引

前缀索引

• 定义字符串的一部分作为索引

• 如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

• 优缺点

  • 优点：占用空间小
  • 缺点：增加额外的扫描记录数，不能利用覆盖索引了

• 区分度

  • 区分度越高越好。因为区分度越高，意味着重复的键值越少
  • 计算区分度：select count(distinct email) as L from SUser;
  • select
    count(distinct left(email,4)）as L4,
    count(distinct left(email,5)）as L5,
    count(distinct left(email,6)）as L6,
    count(distinct left(email,7)）as L7,
    from SUser;

概念

flush

• 把内存里的数据写入磁盘的过程叫flush

• 触发时机

  • Innodb redo log 快满了，系统推进checkpoint，就会对应范围的脏页flush进磁盘

    • 这种情况尽量避免

  • 内存不足时可能会淘汰脏页，也会flush

  • MySQL 认为系统很闲的时候

    • 系统没什么压力

  • MySQL 正常关闭的时候

脏页

• 当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”

  • 脏页控制策略：innodb_io_capacity ,它能告诉Innodb 磁盘的刷盘能力，默认是200；所以建议设置为IOPS

  • 刷盘速度两个因素：脏页比例，redo log写盘速度

    • 脏页比例上限：innodb_max_dirty_pages_pct 默认值 75%

    • 脏页比例计算方法：Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total

      • select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
        select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
        select @a/@b;

  • 刷盘的“连坐”机制

    • 在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
    • 参数innodb_flush_neighbors=1 就连坐，等于0就只管自己的。机械硬盘建议1，减少随机IO，固态硬盘建议0，更快完成刷盘，减少语句响应时间

并法连接数和并发查询数

• show processlist 显示的是并发连接数

  • 影响：占据内存

• 当前正在执行的语句，才是我们所说的并发查询

  • 进入锁等待，并发线程计数会减一

客户端的误解

• 误解1：如果库里的表特别多，连接就会很慢

  • 每个客户端在和服务端建立连接的时候，需要做的事情就是TCP握手、用户校验、获取权限。但这几个操作，显然跟库里面表的个数无关

  • 为什么会感觉很慢？

    • 原因

      • 当使用默认参数连接的时候，MySQL客户端会提供一个本地库名和表名补全的功能。为了实现这个功能，客户端在连接成功后，需要多做一些操作：

执行show databases；

切到db1库，执行show tables；

把这两个命令的结果用于构建一个本地的哈希表。
- 最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。
也就是说，我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢

    - 解决方案

        - 跳过补全功能

            - 增加-quick 或者-q 参数

                - 作用

                    - 跳过补全
                    - 不用申请本地缓存
                    - 不记录执行命令记录到本地的命令历史文件

            - 增加-A参数
            - 自动补全的效果就是，你在输入库名或者表名的时候，输入前缀，可以使用Tab键自动补全表名或者显示提示。

        - 实现原理

            - MySQL客户端发送请求后，接收服务端返回结果的方式有两种

                - 一种是本地缓存，也就是在本地开一片内存，先把结果存起来。如果你用API开发，对应的就是mysql_store_result 方法。
                - 一种是不缓存，读一个处理一个。如果你用API开发，对应的就是mysql_use_result方法。

                - MySQL客户端默认采用第一种方式，而如果加上–quick参数，就会使用第二种不缓存的方式。

                - 采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。

结果集存储到哪？

• net_buffer

• 假设表200G大小，select * from t 全表扫描，实际上并不需要保存一个完整的结果集

  • 流程

    • 获取一行，写到net_buffer中，重复获取行，直到net_buffer写满，调用网络接口发出去。

    • 如果发送成功，就清空net_buffer，然后继续取下一行，并写入net_buffer。

    • 如果发送函数返回EAGAIN或WSAEWOULDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待。直到网络栈重新可写，再继续发送

  • 关键点

    • 默认定义/proc/sys/net/core/wmem_default的socket send buffer 也不可能达到200GB，如果它被写满了，就会暂停读取数据的流程

  • 问题：引起长事务

    • MySQL是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致MySQL服务端由于结果发不出去，这个事务的执行时间变长

  • 现象

    • 用show processlist 查看会看到 sending client data

    • 注意sending data和sending client data 是两个状态，

      • 区别

        • sending data 仅代表mysql 进入sql 执行阶段
        • sending client data 当一个线程处于“等待客户端接收结果”的状态，才会显示"Sending to client"

buffer pool 简称BP

• 作用

  • 加速查询

• 指标

  • 内存命中率：show engine innodb status Buffer pool hit rate 990/1000

• 参数

  • innodb_buffer_pool_size

    • 一般建议设置成可用物理内存的60%\~80%。

• 内存管理方法

  • LRU（最近最少使用）

    • 底层数据结构是链表
    • 有young 区和old 区，占比为5:3
    • 对于old 区的数据页，每次访问都会做个判断，如果数据页在链表中的时间超过指定时间，就把它移动到链表头部，
    • innodb_old_blocks_time 来控制 指定时间，默认是1000毫秒即1秒

临时表

• 误区

  • 临时表就是内存表

• 内存表

  • 指的是使用Memory引擎的表，建表语法是create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在

• 临时表

  • 可以使用各种引擎类型 。如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上的。当然，临时表也可以使用Memory引擎

  • 特性

    • 建表语法是create temporary table

    • 一个临时表只能被创建它的session访问，对其他线程不可见

    • 临时表可以与普通表同名

      • 普通表table_def_key的值由库名+表名
      • 临时表table_def_key由table_def_key=库名+表名+server_id+thread_id

    • session A内有同名的临时表和普通表的时候，show create语句，以及增删改查语句访问的是临时表

    • show tables命令不显示临时表

    • 每个线程都维护了自己的临时表链表，每次session 内操作表，先遍历链表，检查是否有这个名字的临时表，有就先操作临时表，如果没有操作普通表，对链表的每个临时表 执行 drop temporary table+表名操作

    • binlog_format=statment或者mixed的时候，binlog中才会记录临时表的操作

    • 如果数据库掉电，重启以后MySQL会扫描临时目录，把表都删掉；

  • 应用

    • 分布式系统的跨库查询

  • tmp_table_size 控制内存临时表的大小 默认是16MB

  • 内存临时表的放不下的时候，就把临时表转成磁盘临时表，默认引擎是InnoDB

  • 场景：union 语句，group by 无序排序且没有加order by null，group by 没有使用到索引

groub by

• 执行流程

  • 创建内存临时表，依次取出数据，记录临时表，如果有x，就将x的值加1，再根据排序字段排序，如果没有排序，加上order by null

• 优化方法

  • 索引 5.7版本 generated column 机制

    • alter table t1 add column z int generated always as(id % 100), add index(z);

  • group by语句中加入SQL_BIG_RESULT

    • 就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。
    • join_buffer 是无序数组，sort_buffer 是有序数组，临时表是二维表的结构

Memory 引擎

• InnoDB表的数据就放在主键索引树上（B+树）

  • 索引组织表（index organizied table）

• Memory引擎的数据和索引是分开的，数据部分以数组的方式单独存放，而主键id索引里，存的是每个数据的位置，主键的id是hash索引，可以索引上的key并不是有序的

  • 堆组织表（heap organizied table）

• 跟InnoDB的区别

  • InnoDB表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的
  • 当数据文件有空洞的时候，InnoDB表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值
  • 数据位置发生变化的时候，InnoDB表只需要修改主键索引，而内存表需要修改所有索引
  • InnoDB表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的
  • InnoDB支持变长数据类型，不同记录的长度可能不同；内存表不支持Blob 和 Text字段，并且即使定义了varchar(N)，实际也当作char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同

• 索引

  • 它支持hash索引和b-tree索引
  • 内存表快 Memory 支持hash索引，且数据都在内存

• 缺点

  • 不支持行锁，只支持表锁
  • 数据库重启的时候，所有内存表的都会被清空
  • 临时表数据只有2000行

自增主键

• 保存的位置

  • 表的结构定义存放在后缀名为.frm 文件中，但是并不会保存自动值

  • 不同引擎保存策略不同

    • MyISAM 引擎的自增值保存在数据文件中

    • InnoDB 引擎的自增值，其实是保存在了内存里

      • 8.0 自增持久化，将自增值的变更记录放在了redo log
      • 5.7及之前的版本，自增只保存在内存里，并没有持久化，每次重启之后，第一次打开表的时候获取最大值+1,作为这个表当前的自增值

    • 自增值修改机制

      • 如果插入的id为0 null，或未指定，就把表的当前的auto_increment值插入
      • 指定了就使用指定的值，如果自增值是Y，插入是X，X < Y 那么这个表的自增值不变，如果x>= Y就需要把当前的自增值修改为新的自增值

      • 修改时机

        • 没有指定id
        • 子主题 2

    • 新的自增生成算法

      • 从auto_increment_offset开始，以auto_increment_increment为步长，持续叠加，直到找到第一个大于X的值，作为新的自增值。
      • 其中，auto_increment_offset 和 auto_increment_increment是两个系统参数，分别用来表示自增的初始值和步长，默认值都是1

• 自增锁

  • 5.0版本是语句级别的
  • 5.1.22版本引入了一个新的策略，新增参数innodb_autoinc_lock_mode=1（默认是1），为0表示用5.0的版本，这个参数的值被设置为2时，所有的申请自增主键的动作都是申请后就释放锁

• 不连续的原因

  • 唯一键冲突
  • 事务回滚

  • 申请的策略

    • 批量插入的语句，第一次申请自增id，会分配1个；

1个用完以后，这个语句第二次申请自增id，会分配2个；

2个用完以后，还是这个语句，第三次申请自增id，会分配4个；依此类推，同一个语句去申请自增id，每次申请到的自增id个数都是上一次的两倍。

表分区

• 对于引擎层来说是多个表，对于server层来说是1个表

• 分区表间隙锁只在一个表上

• 优缺点：

  • server层认为都是一张，因此所有分区共用同一个MDL锁
  • 打开表行为，每当第一次访问一个分区表的时候，MySQL需要把所有的分区都访问一遍。如果系统参数 open_files_limit 使用默认值1024，超过这个数值会报错的

  • 优点

    • 分区表可以很方便的清理历史数据 alter table t drop partition
    • 业务代码更简洁

• 分区策略

  • myisam

    • 通用分区策略（generic partition）

      • 每次访问分区都由server层控制

  • mysql 5.7.9

    • Innodb 本地分区策略（native partitioning）

      • 在InnoDB内部自己管理打开分区的行为。

  • mysql 5.7.17

    • 将myISAM分区表标记为即将弃用deprecated

  • mysql 8.0 不允许创建myisam分区表

• 应用场景

幻读

• 定义：幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
• 现象：数据不一致
• 解决方案：间隙锁
• 出现的前提：事务隔离级别为RR

连接

断开连接

• 规则：优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接
• 从服务端断开连接使用的是kill connection + id的命令

短连接：短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

• 短连接风暴

  • max_connections

    • 只要连接着就占用一个计数

  • wait_timeout

    • 一个线程空闲wait_timeout这么多秒之后，就会被MySQL直接断开连接

长连接：数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接

锁

自增锁（AUto-inc locks）

• auto-inc锁是当使用含有auto_increment列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时在表级别加一个auto-inc锁，然后为每条待插入记录的auto_increment修饰的列分配递增的值，在语句执行结束后，再把auto-inc锁释放掉
• innodb_autoinc_lock_mode
• 作用：保证一个语句中分配的递增值是连续的
• 缺点：每条语句都要对这个表锁进行竞争，这样的并发性低下

• 模式

  • innodb_autoinc_lock_mode = 0(“传统”锁定模式)

    • 所有insert 语句 开始时候都会获取一个表锁autoinc_lock，获取到

  • innodb_autoinc_lock_mode = 1(“连续”锁定模式)

    • 针对bulk insert，自增锁会被一直持有直到语句执行结束才会被释放。

  • innodb_autoinc_lock_mode = 2(“交错”锁定模式)

    • simple insert语句能保证ID是连续的，但是bulk insert的ID则可能有空洞。

主从复制的同一张表下的同一行id有可能不一样。

• Insert 语句有三种类型，simple insert ，bulk insert ，mixed insert

临键锁（Next-keys locks）

• 前开后闭的区间(0,5] 不包括5
• 实际上它是行锁+间隙锁实现的

间隙锁（Gap locks）

死锁(deadlock)

• 当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁

• 解决策略：

  • 等，直到超时innodb_lock_wait_timeout=50s 默认
  • 起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
  • 直接kill掉 connection

记录锁（Record locks）

乐观锁

• 其实主要就是一种思想，因为乐观锁的操作过程中其实没有没有任何锁的参与，乐观锁只是和悲观锁相对，严格的说乐观锁不能称之为锁
• 实现方式 数据库表的version，增加时间戳类型的字段

悲观锁

• 行锁、表锁、读锁、写锁

根据不同引擎

• innodb

  • 自增锁（Auto-inc locks）

    • 门针对事务插入 AUTO_INCREMENT类型的列

  • 共享锁 允许持有该锁的事务读取行

  • 排他锁 允许持有该锁的事务更新或删除行。

  • 意向锁（Intention locks）InnoDB用意向锁来实现多粒度级别的锁

    • 意向共享锁 （intention shared lock, IS）表示事务打算在表中的各个行上设置共享锁

      • select column from table ...lock in share model

    • 意向排他锁 （intention exclusive lock, IX）表示事务打算在表中的各个行上设置排他锁

      • select column from table ... for update

    • 意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，

    • 意向锁是一种不与行级别锁冲突的表级锁。

    • 意向锁不会与行级别的锁互斥，会与表级别的S和X互斥

  • 记录锁（Record Locks）记录锁是对索引记录的锁定

    • 没有索引也能锁定索引记录，innodb 有个隐藏的聚簇索引可用来做记录锁

  • 间隙锁（Gap locks）

    • 它封锁索引记录中的间隔
    • RR情况下才有效
    • 关闭可能遇到幻读

  • 插入意向锁 insert intention locks

    • 插入意向锁是一种Gap锁，不是意向锁，在insert操作时产生
    • 跟普通的区别，普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据

插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据
- 插入意向锁的作用是为了提高并发插入的性能

- 临键锁 （Next-key locks）

    - Next-Key锁是索引记录上的记录锁和索引记录之前的区间上的区间锁的组合。
    - （1,5] 类似这种前开后闭的区间（数学上称之为 半开区间）

锁粒度

• 表锁

  • 表锁

    • lock tables t read 加S锁，lock tables t write

    • 自增锁

    • 意向锁（Intention lock）

      • 允许行锁和表锁共同存在

    • 元数据锁（MDL）

  • 元数据锁 meta data lock MDL

    • MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性，你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
    • 等事务提交后再释放

• 行锁

  • myisam 不支持行锁
  • 行锁就是针对数据表中行记录的锁
  • 可以是一行也可以是多行

  • 范围

    • 记录锁（单行的），间隙锁（多行的），临键锁，插入意向锁

• 全局锁

  • 全局锁就是对整个数据库实例加锁

    • Flush tables with read lock (FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令

加锁规则

• 原则1：加锁的基本单位是next-key lock。希望你还记得，next-key lock是前开后闭区间。

原则2：查找过程中访问到的对象才会加锁。
优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁。

优化2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock退化为间隙锁。

一个bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

DCL\&DDL

database controll language

grant：用来给用户赋权

• 创建用户：create user 'ua'@'%' identified by 'pa'

  • 在磁盘上往mysql.user表里插入一行
  • 内存里，往数组acl_users里插入一个acl_user对象，这个对象的access字段的值为0

• 全局权限：grant all privileges on . to 'ua'@'%' with grant option;

  • 磁盘上，将mysql.user表里，用户’ua’@’%'这一行的所有表示权限的字段的值都修改为‘Y’；
  • 内存里，从数组acl_users中找到这个用户对应的对象，将access值（权限位）修改为二进制的“全1“
  • grant执行之后，新的客户端使用用户名ua登录，mysql会为新🔗维护一个线程对象，然后从acl_users数组查询到用户的权限，并将权限值拷贝到这个线程对象中，之后在这个连接中执行的全局权限判断，都使用线程对象内部保存的前线位
  • 注意：已经存在的连接全局判断不受grant影响

• 回收权限 revoke all privileges on . from 'ua'@'%'

  • 磁盘上，将mysql.user表里，用户'ua'@'%' 这一行的所有标识权限的字段的值都修改为’N‘
  • 内存里，从数组acl_users中找到这个用户对应的对象，将access的值修改为0

DB权限

• rant all privileges on db1.* to 'ua'@'%' with grant option;

  • 磁盘上，往mysql.db表中插入了一行记录，所有权限位字段设置为“Y”；
  • 内存里，增加一个对象到数组acl_dbs中，这个对象的权限位为“全1”。

• 每次判断一个用户对一个数据库读写权限的时候，都需要遍历一次acl_dbs数组，根据user，host和db找到匹配对象，然后根据对象权限位来判断，也就是说grant 修改db权限的时候，是同时对磁盘和内存生效的

  • 特例：如果当前会话已经处于一个db里面，之前use这个裤的时候拿到的库权限会保存在会话变量重

表权限和列权限

• 表权限定义存放在表mysql.tables_priv中
• 列权限定义存放在表mysql.columns_priv中。这两类权限
• 这两类权限，组合起来存放在内存的hash结构column_priv_hash中。

• 赋权指令

  • grant all privileges on db1.t1 to 'ua'@'%' with grant option;
  • GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;
  • 同时修改数据表和内存中的hash，会立马影响到已经存在的连接

结论

• flush privileges

  • flush privileges命令会清空acl_users数组，然后从mysql.user表中读取数据重新加载，重新构造一个acl_users数组。也就是说，以数据表中的数据为准，会将全局权限内存数组重新加载一遍

• 正常情况下，grant命令之后，没有必要跟着执行flush privileges命令

• 当数据表中的权限数据跟内存中的权限数据不一致的时候，flush privileges语句可以用来重建内存数据，达到一致状态。

  • 这种一般是去mysql.user表执行了dml操作，这种是不规范的写法

数据库架构

客户端

服务层

• 连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接

  • 查询缓存：只要表上有更新，这个表所有的查询都会被清空

    • query_cache_type 设置为demand
    • 8.0 版本已经移除了查询缓存
    • 显示指定：select SQL_CACHE * from t

  • 分析器:分析每个字符串分别是什么

    • 优化器：决定索引的使用，关联表的顺序

      • 执行器：检查执行权限，去调用不同的引擎接口，慢日志里面有rows_examined,表示语句执行过程中扫描了多少行

存储引擎层