MySQL 进阶 原理 优化
2020-06-18 18:27:54 11 举报AI智能生成
登录查看完整内容
MySQL实战，MySQL知识进阶，包括CRUD的执行流程。事务隔离级别， B+Tree剖析。
作者其他创作
大纲/内容
MySQL进阶
字段定义中数字的意义
数值
可分为两类：整数，浮点数或小数
允许指定数值的正负(UNSIGNED)或者用零填补(ZEROFILL)
示例：int(6)
示例中的6 表示\"宽度指示器\
mysql默认是有符号的，需要单独使用一位来保存符号，使用了修饰符设置了zerofill，那就自动变为无符号字段了 unisgned
浮点数定义是需要定义两个数字， 一个显示宽度指示器，一个是小数点指示器
字符串
varchar : 可变成字符串，定义时指定的就是最大的字符数， 按照实际的值需要的长度来存储，不会被填充其他字符
MySQL查询执行流程
示例语句
执行流程
2. 从索引city中找到第一个满足city='杭州' 条件的主键id
4. 从索引city中取出下一条满足记录的主键id
5. 重复3-4 步骤直到不满足查询条件为止
6. 对sort_buffer 中数据按照字段name做快速排序
7. 按照排序加过取出前1000行返回给客户端
参数设置
其中按照name做排序可以在内存中完成， 如果sort_buffer_size设置的较小，则会使用磁盘临时文件进行辅助排序
开启优化器的执行跟踪
服务端处理查询流程
MySQL处理查询是“边读边发”，查询结果是分段发送给客户端的，流程如下：
1. 获取一行，写到net_buffer中，这块内存的大小是由参数net_buffer_length定义， 默认：16k
2. 重复获取行，直到net_buffer写满，调用网络接口发出去
查询中的JOIN
join 优化器可能使用选择自然为比较合适的驱动表； straight_join则是让优化器按照我们的指定的方式去join
案例SQL: select * from t1 straight_join t2 on (t1.a=t2.a); t2.a上有条索引
上诉查询SQL流程
1. 从表t1中读取一行数据R
2. 从数据行R中， 取出a字段到表t2里去查找，会使用到t2表的a字段对应的索引
3. 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分
4. 重复执行步骤 1-3， 直到把表t1 的末尾循环结束
join_buffer作用
表关联查询的时候，执行计划中有时会看到 join buffer， 表示会把分段读取的驱动表数据放在join_buffer中，然后根据join条件关联下张被驱动表作为结果集的一部分返回。由参数join_buffer_size 设定，默认： 256k
结论
Index Nested Loop Join（NLJ）: 顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，使用的on条件在被驱动表选择走索引，匹配成功则跟驱动表组成一行作为结果集的一部分返回，重复上面的操作知道循环结束
Block Nested-Loop Join(BNL): 先把驱动表的数据读取出来放在线程内存的join_buffer中，把被驱动表的每一行数据取出跟join_buffer中的数据做对比， 满足join条件的作为结果的一部分返回
1. 使用join语句，比直接强行拆成多个单标执行SQL语句的性能要好
2. 如果使用join语句的话， 需要让小表做驱动表，当然被驱动表需要能走索引
3. 如果使用Index Nested-Loop Join算法， 也就是可以用上被驱动表上的索引， 则join没有问题
4. 如果使用 Block Nested-Loop Join （BNL）算法， 扫描行数过多， 特别是大表上的join操作，这样可能要扫描的被驱动表多次，会占用大量的系统资源。主要关注 执行计划中是否有 “Block Nested Loop”字样
优化JOIN
MRR优化
大多数的数据都是按照主键递增的顺序插入得到的，所以可以认为，按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读， 能够提升读性能。
实例SQL: select * from t1 where a>=1 and a<=100;
优化后的流程
1. 根据索引a，定位到满足条件的行记录，把id的值放入read_rnd_buffer中
2. 将read_rnd_buffer 中的id进行递增排序
3. 排序后的id数组， 一次到主键id索引中查询记录， 并作为结果返回
BKA优化
对于Index Nested-Loop Join的一个优化， 在从驱动表获取a值再到t2使用a索引一条条查找数据中间， 添加了一层buffer， 即 驱动表查出去的a数据放在join_buffer中，便于后面t2的使用
图示
group by的优化
尽量让group by 过程用上索引， 确认explain中没有Using temporary 和 Using filesort
如果 group by 统计的数据量不大，尽量只使用内存表；也可以通过适当调大tmp_table_size参数，来避免使用磁盘临时表
如果数据量太大， 在使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by 的结果
案例分析
查询长时间不返回
示例语句： mysql> select * from t where id=1;
一般地这种情况大概率是表t被锁，可以使用show processlist 查看当前语句执行的情况
三种可能性
等待MDL锁
等待flush
等待行锁
查询慢
SQL没写好
示例： select * from t where id + 1 = 10000
错误的书写导致索引没有发挥作用
索引没有建好
例如一个联合索引，但是查询的语句中字段顺序出现跟索引不对应等
MySQL选择索引错误
MySQL索引统计存在偏差，导致选择了错误的索引
查询数据mvcc版本
示例语句： select * from t where id=1 lock in share mode
带有lock in share mode 的sql是当前读。如果有大量的写操作，此时直接返回的是MVCC中可见的最新值。而不加这个语句的sql是一致性读， 需要通过undo log 回滚到对应的位置，此时就会增加查询的负担
MySQL更新语句流程
update语句的执行流程
update T set C=C+1 where ID=2;
更新语句中的索引的更新
change buffer
唯一索引字段的更新
普通索引字段的更新
总结
MySQL删除数据的奥秘
表的数据存储方式
innodb_file_per_table
OFF: 表的数据使用共享表空间， 跟数据字典放在一起
ON： 每个InnDB表数据存在一个以 .idb为后缀的文件中， Mysql5.6.6开始的默认值
数据“空洞”
当执行delete语句时，Inndb做标记删除；如果后续插入数据时，定位到这个数据页上有可能继续复用这个位置
同理，如果一个数据页上的数据都被删除了， 那么这个数据页也是可以被复用的
Inndb的标记删除，是不会主动回收表空间的，这些没有被复用的空间就造成了“空洞”
插入数据的时候，如果是随机插入，就有可能形成数据页分裂，就会造成数据页的末尾形成空洞
更新索引上的值可以理解为删除一个旧值， 插入一个新值。 同样会造成空洞
总结： 一个数据表经过大量的增删改后，都是有可能造成空洞的， 通过重建表可进行回收
重建表
MySQL5.6的Online DDL
1. 建立临时文件，扫描旧表中主键的所有的数据页
3.生成临时文件的过程中， 把所有对旧表记录的操作记录在一个日志文件中（row log）
4. 临时文件生成后， 把日志文件的操作应用到临时文件，得到一个路基数据上与旧表相同的数据文件
5. 用临时文件替换旧表的数据文件
三种方式
MySQL5.6开始 alter table t engine = InnoDB
analyze table t ； 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据， 这个过程中添加了DML读锁
optimize table t ; 相当于上面两步的聚合
注意
重建表后，InnDB不会把整个表填满， 每个页都会留1/16的空间给后续的更新使用；当用户插入数据命中这个页时，会使用掉一部分预留空间， 当下次再重建表时就会继续按照1/16的规则进行
count(id) count(*) count(cel) 你真的懂吗
数据库设计法则
在保证逻辑正确都前提下， 尽量减少扫描的数据量， 减少磁盘IO
对于InnoDB来说，主键索引上保存的是数据， 而二级索引上保存的是主键值。 对于count(*)这种操作，优化器会找到最小的那棵树进行遍历， 因为遍历哪棵树得到的逻辑都结果一致
count()含义
count()是一个聚合函数， 对于返回的结果集，逐行进行判断，count中参数不是null， 则累加1， 否则不加。最后返回累加值
性能上的差别
原则
1. server层要取哪个字段就返回哪个
2. InnoDB只给必要的值
3. 目前优化器只针对count(*)做了优化，定义为取行数
count(*)
这是例外，MySQL专门做了优化，不会把全部字段读取出来， 直接按照行累加
count(1)
InnoDB遍历整张表，但不取值。server层对于返回的每行添加一个“1”列， 判定不会为null，按照行累加即可
count(主键id)
InnoDB遍历整张表， 把每行的id取出，返回我server层，server层拿到id后，判定id不会为null，按照行累加即可
count(字段)
若字段定义允许null，取出字段判断不为null，进行累加
按照效率排序：count(字段)<count(主键id)<count(1)≈count(*)
自增ID的使用
保存位置
MyISAM引擎的自增值保存在数据文件中
InnoDB引擎的自增值保存在内存中
<= 5.7， 自增值不会自动持久化，每次服务重启，第一次打开表的时候，会去找找表的max(id) ， 然后再这个基础上加1 作为表的自增值
8.0 开始， 把自增值的变更记录在了redo log 中， 重启的时候依靠 redo log 恢复重启之前的值
修改机制
2. 如果插入数据时id字段指定了具体的值， 就直接使用语句里指定的值
3. 指定的id值小于当前自增值时，那么这个表的自增值不变
4. 如果插入的id值， 就需要把当前自增值修改为新的自增值
注： 在插入一行新数据行前，首先对自增值进行修改，拿到最新的自增值给到新的数据行 那么在 插入出现异常（如：唯一键冲突，事务回滚）就会导致 自增值“空洞”
自增值的算法
从auto_increment_offset 开始，以auto_increment_increment为步长， 持续叠加
自增值为何不能回退
避免导致事务回滚还需要回退自增值，从而增加设计复杂度以及系统性能降低。所以自增值在事务之外，所以自增值可能会不连续
自增id用完怎么办
int类型的id，无符号整形是4个字节，上限就是2的32次方-1
表定义的自增值达到上限后，再申请下一个ID时，得到的值不变。insert就会出现主键冲突
临时表的意义
内存表： 指的是使用Memory引擎的表(engine=memory)，数据保存在内存中，系统重启数据会被清空，表结构还存在
应用: 大表被分库之后， 可以利用临时表进行汇总做逻辑操作，表最终会随着session的关闭而释放
临时表可以重名机制
临时表物理存放机制
create temporary 会创建一个frm文件保存表结构，这个frm文件存放在临时文件目录下， 名字是\"#sql{进程id}_{线程id}_序列号\"
临时表内存区别机制
每张表都对应一个table_def_key
普通表是由 \"库名+表名\" 得到的
临时表是由\"库名+表名+server_id+thread_id\"
两个不同的session创建的同名临时表，在物理上和逻辑内存中都是不同的，所以可以重名并存
临时表和主备
当前的binlog_format=row时，跟临时表相关的语句，就不会记录到binlog中，因为对表的操作都转化成了全量的数据模式
binlog_format=statment/mixed ， binlog中才会记录临时表相关的操作，创建临时表的语句会传到备库进行执行，并带上主库的thread_id， 在主库session关闭的时候，会自动写入一个 drop temporary table 传给备库执行
数据表的复制
insert ... select
insert select 是很常见的两个表之间复制数据的方法， 在RR的隔离级别下， 这个语句会给select的表里扫描到的的记录和间隙加读锁
若insert 和select的对象是同一个表， 则有可能造成循环写入， 此时可以使用临时表来优化
insert如果出现了唯一键的冲突，则会在唯一值上加共享的next-key lock (S 锁)，此时需要快速提交或者回滚事务，避免加锁时间过长
mysqldump 方法
-single-transaction: 导入数据的时候不要对表 db1.t加表锁
-add-locks= 0 表示输出的文件中，不需要增加“LOCK TABLES t WRITE”
-no-create-info 不需要导出表结构
-where: 按照条件查询需要导出的数据
-skip-extended-insert: 生成的文件中一条INSERT语句只插入一行数据
在客户端执行命令导入
source 是个客户端命令，流程如下：
1、打开文件，默认以分号为结尾读取每条SQL语句
2. 把SQL语句发送到服务端进行执行
使用CSV文件
这个语句会把文件保存在服务端，原则上一个数据行对应文本中的一行，into outfile 需要指定一个不存在的文件名，不然会因文件同名而报错
导入CSV文件
流程
1. 打开CSV文件，按照格式读取数据
2. 启动一个事务
3. 判定每行字段数与新表是否相同，不相同则报错回滚事务； 相同则构造一行，调用InnoDB引擎接口， 写入到表中
4. 重复步骤3， 知道吧CSV文件写完。最终提交事物
物理复制法
MySQL5.6 开始引入了可传输表空间，可以通过导出+导入表空间的方式 实现物理表都复制
例如： 复制一个跟t相同的表r
4. 在db1目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd ;
6. 执行 alter table r import tablesspace ; 此时r.ibd 文件作为表r的新的表空间，复制成功
MySQL主备方案
同步过程
master接收到客户端的更新请求后， 执行内部事务的更新逻辑， 同时写binlog; slave跟master之间维持一个长连接。master内部有个线程专门用于服务这个长连接
流程图
过程
1. slave上通过change master 命令，指定同步的服务器端口 ， binlog文件的偏移量
3. master校验用户名密码，开始按照slave传过来的位置， 从本地读取binlog发送给slave
4. slave拿到binlog后，写到本地文件， 称之为 中继日志（relay log）
5. sql_thread 读取中继日志， 解析出日志中的明了，并执行
主备延迟
主备机器性能不对称， 从库要比主库性能差很多
备库负荷高，影响了同步的速度，造成延迟
大事务造成延迟
比如 一次性删除很多的数据，这就是典型的大事务场景
大表的DDL
备库的并行复制能力
复制分发策略
复制分发执行的原则
1. 不能造成更新覆盖， 要求更新同一行的两个事物， 分发到同一个worker中
2. 同一个事物不能被拆开，必须放到同一个woker中
MySQL5.5 并行复制策略
按表分发策略
如果两个事物更新不同的表， 他们就可以并行。
按行分发策略
如果两个事物没有更新相同的行， 他们在备库上可以并行执行。这个模式要求binlog格式是：row
相对的， 按行并发执行策略在决定的个线程分发的时候， 需要消耗更多的计算资源
约束条件
表必须有主键
不能有外键，级联更新的行不会记录在binlog中，所以冲突检测就不准确
问题
操作很多行和大事务时，系统负载很高
实现这个策略时，需要设置一个行数阈值。达到这个阈值则退化为单线程模式
两种策略都没有被合并到官方
MySQL5.6并行复制策略
按照库并行处理
MySQL5.7 并行复制策略
利用组提交优化复制策略
特性
1. 能够在同一组里提交的事务， 一定不会修改同一行
2. 主库上可以并行执行的事务，备库上可以可以并行执行
3. 在备库上， 同时处于prepare状态的事务， 在备库执行时可以并行
4. 处于prepare状态的事务与处于commit状态的事务之间，在备库执行时也可以并行
实现
2. commit_id 直接写到binlog中
3. 传到备库应用的时候， 相同的commit_id的事务可以被分发到多个worker中执行
4. 这一组全部执行完毕之后， coordinator再去取下一批
MySQL5.7.22 的并行复制策略
基于WRITESET并行复制
WRITESET， 对于事务设计更新的每一行， 计算出这一行的hash值， 组成集合writeset， 如果两个事物没有操作相同的行， 就可以并行
主备切换
如何判定主库挂了
并发连接
使用show processlist 的结果中， 指得是并发连接， 当前正在执行的语句才是并发线程
并发线程， 参数: innodb_thread_concurrency
控制InnoDB并发线程的上限，默认为0 表示不限制。 一旦并发线程数达到这个值， InnoDB在接受到新请求时，就会进入等待状态直到线程退出。设置过大会导致线程上下文的切换。 一般建议设置为 64 - 128 之间
线程进入锁等待后，并发线程的计数会减一
查表判断
为了检测InnoDB并发线程过多导致系统不可用，一般地可以在系统库mysql中创建一个表，插入一条数据， 定期执行 select * from mysql.health_check;
更新判断
常见做法是放一个timestamp字段，表示最后一次执行的更新时间。 update mysql.health_check set t_modified = now();
内部统计
MySQL5.6添加了performance_schema库， 其中file_summary_by_event_name 表统计每次IO请求的时间
打开 redo log 的时间监控
例： 检查单次IO请求时间查过200毫秒
数据统计完毕之后把统计信息清空
truncate table performance_schema.file_summary_by_event_name;
基于位点的主备切换
当我们把节点 B 设置成节点 A’的从库的时候可以通过命令进行
其中有个重要的点，在于确定MASTER_LOG_FILE 和 MASTER_LOGPOS，这个表示同步的主库文件的偏移量
按照 mysqlbinlog File --stop-datetime=T --start-datetime=T 大致确定一个相对较前的点，按照这个偏移量进行同步操作
同步过程中出现类似主键冲突，则停止同步，一般由两种做法
1.跳过一个事务，直到不会出现错误位置
set global sql_slave_skip_counter=1; start slave;
2. 通过设置参数slave_skip_errors 直接跳过错误
基于位点进行主备切换， 两种方式的操作比较复杂， 而且容易出错。 MySQL5.6 引入了GTID
GTID
概念
GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是： GTID= server_uuid:gno
server_uuid: 每个MySQL实例的全局唯一标记
gno是一个整数， 初始值为1， 每次提交事务时， 分配这个事务并加1
开启
在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on
生成方式
2. 如果gtid_next 是一个指定GTID的值， 例如 set gitd_next = 'current_gtid'。 如果 current_gtid已经存在于本实例的GTID集合中， 则接下来执行这个事务会被直接忽略； 否则 就分配给当前事务
一个current_gtid只能给一个事务使用， 这个事务提交之后，如果要执行下一个事务就要重新按照上诉两种生产方式重新set新的gtid
在 GTID 模式下，备库 B 要设置为新主库 A’的从库；master_auto_position=1 就表示这个主备关系使用的是 GTID 协议
主备延迟解决方案
1. 强制走主库方案
对于刚提交的事务，需要立即获取最新的结果，则需要强制走主库
对于实时性要求不高的数据，即使从从库中读取，稍有延迟也不要紧。根据业务而定
2. Sleep方案
主库更新之后，读取从库先sleep一下。 类似先执行一条sleep(1) 命令。这个前提假设是在1s之内完成同步。 这个方案具有不确定性
3. 判定主备此刻是否存在延迟
1. show slave status; 结果中的seconds_behind_master 如果是0， 表示主从此时无延迟
2. 通过对比位点确保主备无延迟
Master_Log_File 和 Read_Master_Log_Pos，表示的是读到的主库的最新位点；
Relay_Master_Log_File 和 Exec_Master_Log_Pos，表示的是备库执行的最新位点。
以上两组值完全相同，表示日志已经同步完成
3. 对比GTID集合确保主备无延迟
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。
Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；
Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。
以上两个集合相同，则表示日志已经同步完成
4. 配合 semi-sync
半同步复制
1. 事务提交时，主库把binlog发给主库
2. 从库收到binlog之后，发回给主库ack，表示已经收到
3. 主库收到ack以后，才能返回给客户端“事务完成”的确认
5. 等待主库位点
参数file和 pos指定的是主库上的文件和位置
timeout可选， 设置为正整数， 表示这个函数最多等待的秒数
返回值 >=0 ，则可以在这个从库上进行查询操作
6. GTID
2. 超时返回 1
删库不跑路
1. delete误删除行
使用Flashback工具通过闪回恢复数据行
原理是通过binlog回放，前提是确保binlong_format_row 和binlog_row_image=FULL
2. truncat/drop 误删库表
这种情况下binlog只是记录了truncate/drop语句，此时需要最近全量备份+增量日志的方式恢复数据
MySQL5.6 版本引入延迟复制备库
是一种特殊的备库， 通过CHANGE MASTER TO MASTER_DELAY = N 命令，指定备库持续主库有N秒的延迟
设置一个合理的值， 在做了误删除之后， 立即在这个延迟备库上执行stop slave . 然后再恢复出需要的数据
3. rm删除物理文件
只要数据库的集群正常，仅仅是删除了其中一个节点的数据，MySQL集群就能正常工作
4. 防患于未然
账号分离
开发同学 只给DML权限，不给truncate/drop权限，需要DDL的话通过DBA操作
即是团队成员，日常操作使用只读账号，必要时才使用更新权限的账号
操作规范
删除之前先修改表名， 确保对业务无影响再删除这张表
改表名，要求给表加固定的后缀，删除表必须通过DBA执行，而且只能删除有这个后缀的表
充分的数据备份
异地延迟节点备份， 数据物理文件备份
MySQL基本架构
服务层
组成部分
连接器
查询缓存
分析器
词法分析
语法分析
优化器
执行器
执行引擎层
客户端
架构示意图
重要的日志模块
重做日志 [redo log]
由innoDB引擎实现
每个文件默认1G，一组4个文件，从文件头部开始写，到末尾再从头循环
InnoDB把内存中变更的数据页flush到磁盘中
脏页
InnoDB刷新脏页到磁盘的策略
参数 innodb_io_capacity 限制磁盘的负载能力. 这个值可以设置为磁盘的IOPS
IOPS通过fio工具获取: fio-direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
innodb_flush_neighbors默认为1，表示当前页刷新到磁盘时，如果旁边的数据页也是脏页，则把旁边的脏页也一起刷掉，这个逻辑有顺延效果
归档日志 [binlog]
Server层实现
可通过binlog恢复被误修改的数据， 同时也是作为主从复制的重要文件
机制
一个事务的binlog不能被拆开，不管这个事务多大，都会被一次性写入
参数binlog_cache_size 可以控制每一个线程内的binlog chache 的内存的大小，超过了这个规定， 就要暂存到磁盘
参数sync_binlog 控制刷盘时机
1， 默认值， 每次事务提交都执行fsync
常见的会设置为100~1000中的一个数值， 对应的风险是： 如果主机发生异常重启，会丢失最近N个事务的binlog日志
图示： write 过程是把日志写入到文件系统的page cache中； fsync 是数据持久化到磁盘的过程
三种格式
Row level: 保存每行被修改的细节，任何情况下都可以进行数据恢复，加快从库重放日志的效率， 保证从库的数据一致性
Statement level: 每条修改数据的sql会被记录下来，可能这条语句影响多行数据。对于Row模式下就是多行binlog
Mixed level : 混合模式，上面两种level的结合
作用
主从复制，master端开启binlog， master把二进制日志传递给salve并回放
数据恢复，通过mysqlbinlog 工具可以恢复数据
增量备份
回滚日志 [undo log]
分类
insert undo log
update undo log
用途
两阶段性提交
分析两种crash场景
1. 在“写入redolog”后“写binlog”前 发生crash
此时binlog还没有写，redolog也还没有提交，所以在恢复的时候， 这个事物会回滚
2. 在“写binlog” 后， redlog 还没有commit 前发生crash
如果redolog事物是完整的，同时已经有了commit标识， 则直接提交； 如果redolog事务只有完整的prepare， binlog完整则提交事物，如果binlog不完整则回滚事务
binlog完整性判定规则
statement格式的binlog， 最后都有COMMIT
row格式的binlog， 最后会有一个XID event
MySQL5.6.2之后， 引入了binlog-checksum参数可以验证binlog的正确性
事务原理
ACID
原子性(Atomicity)
一致性(Consistency)
隔离性(Isolcation)
持久性(Durability)
所有提交的数据都要写入磁盘固化下来
隔离级别
读未提交
读提交 RC
可重复度 RR
串行(xing)化
MVCC
多版本并发控制
隐藏列
data_trx_id: 最近修改该行数据的事务ID
InnoDB中数据行的结构
整个MVCC的关键就是通过这两个隐藏列来实现的
事务链表
事务链表的结构
两种读形式
快照读
当前读
ReadView
其实就是一种数据结构
trx_ids: 当前系统活跃(未提交)的事务版本号集合
cerator_trx_id: 创建当前ReadView的事务的版本号
可见性规则
事务启动的时机
MySQL的锁
全局锁
表级锁
表锁
元数据锁 MDL(metadata lock)
对表的操作包括两种
元数据锁之间的互斥关系
alter等待时间设置语法: alter table t_test wait N add name varchar(32) not null default '';
行级锁
针对数据表中行记录的锁
子主题
死锁和死锁检测
死锁图示
出现死锁后有两种策略
解决热点行的更新导致性能问题
控制并发度
间隙所(Gap Lock)
所有的数据行上都加了行数，但是无法阻止新数据的插入，也就是会出现\"幻读\
跟 Gap Lock 存在冲突关系的是“往这个间隙插入记录”这个操作；而间隙锁之间不存在冲突
间隙锁的引入，可能会导致通用的语句锁住更大范围，进而影响了并发度
加锁规则
原则2. 查找过程中访问到的对象才会加锁
优化1. 索引上的等值查询，该唯一索引枷锁的时候， next-key lock 退化为行锁
优化2. 索引上的等值查询， 向右遍历时且最后一个值不满足等值条件的时候， next-key lock 退化为间隙锁
优化3. 唯一索引上的范围查找会访问到不满足条件的第一个值为止
MySQL索引解密
根据数据结构划分
hash索引
B+Tree索引
根据索引字段个数划分
单值索引
索引创建时仅包含一个字段
复合索引
根据是否在是主键上的索引划分
主键索引
二级索引
根据数据组织结构划分
聚簇索引
非聚簇索引
其他分类
唯一索引
全文索引
用来支持文本的搜索
学术意义上的B+Tree
根节点至少一个元素
每个节点允许保存多个key
所有的非叶子节点key在叶子节点都有冗余
学术上的B+Tree
mysql的B+Tree索引
补充
本质
perv指针 指向上一页的地址; next指向下一页的地址
回表
覆盖索引
最左匹配原则
使用like
多字段的联合索引
索引下推
字符串上的索引
前缀索引
alter table t_test add index index_t_test(email); 索引中会记录整个字符串
mysql 如何选择索引
优化器的逻辑
索引的基数
mysql采样统计法
innodb_stats_persistent 参数可以设置索引的统计方式
如何引导mysql使用正确的索引
1. 使用 force index(index_name) 强行选择一个索引
被索引的字段上添加函数，将不会使用该索引
例如：mysql> select count(*) from tradelog where month(t_modified)=7; 全表扫描
对于索引字段做函数操作，可能会破坏索引的有序性，因此优化器就决定放弃走索引的查找功能。直接进行了全表扫描
隐式类型转换，导致索引失效
例如：mysql> select * from tradelog where tradeid=110717; 字段tradeid类型是字符串
字符串和数字进行比较，会隐式的将字符串转化为数字，导致触发上一个规则
隐式字符编码转换
两张不同字符集的表进行关联，分别是utf8mb4 和 utf8 ， 当两个类型的字符串做比较的时候， 会先把utf8字符串转换为utf8mb4字符集
MySQL的分区表
分区表的创建
分区表的实现时是在引擎层， 这个表包含一个 .frm 文件和 4 个 . ibd文件，每个分区都对应一个 .ibd文件； 对于引擎层是4个表。 对于Server层来说是 1个表。
分区策略
MyISAMySQL使用的通用分区策略，每次访问都由server层控制， 性能问题比较严重。8.0已经丢弃了这种策略
MySQL5.7.9开始， InnoDB引擎引入了本地分区策略， 这个策略是在InnoDB内部自己管理打开分区的行为
应用场景
显而易见的优势是对于业务透明， 相对于用户分表来说， 使用分区表的业务代码更加简洁
一个业务数据跑的时间足够长，往往回产生大量的历史数据。此时就可以按照时间进行分区；如果每个用户生产的数据较大，也可以按照用户ID进行分区
分区方式
范围分区 range
hash分区
list分区
优化
1. 分区并不是越细越好， 单表控制在千万以下就可以
2. 分区也不要提前创建太多，例如按照月份的分区，每年创建好第二年的分区即可
0 条评论
回复 删除
下一页
