mysql 总结笔记

                       

                                       

一.一条sql是如何运行得
mysql结构：
主要由server层和引擎两个部分组成，server层由连接器，查询缓存，分析器，优化器
执行器组成，引擎有innoDb，memory，myISAM等引擎
连接器主要与客户端建立连接，获取权限，使用show processlist显示当前有多少个连接。
command这一列表示连接状态。如果客户端连接一直没有使用，一段时间后将会被干掉，由wait_timeout指定，
默认8小时。
mysql拿到查询请求后，会先请求查询缓存，查询缓存以key-value形式（key为sql，value为结果）保存了
所有sql得查询结果。查询缓存失效频繁，只要有对一个表的更新，表上所有查询缓存都会失效，建议关闭查询缓存，使用
query_cache_type关闭查询缓存。
分析器校验sql语句得语法，识别表名，列名等。
优化器决定索引得使用，join时决定表得执行顺序
执行器执行器会先校验有无查询权限，有权限则调用引擎接口取数据。慢查询日志中row_examined字段代表
查询扫描得行数（不一定准确）

 

二.更新语句如何执行
write-ahead-log（wal）: 先写日志，再写磁盘

 

redo log：
innodb引擎特有，redo log保证mysql得crash-safe，异常重启记录不会丢，redo log大小固定，由四个文件构成，
write pos记录当前位置，check point记录要擦除得位置，从头到尾循环写到开头，write pos追上
check point时得先擦除日志，持久化到磁盘。

 

binlog：
一种归档日志，在server层，所有引擎都可使用，没有crash-safe能力，采用追加写入方式，
通过binlog_format参数设置binlog格式，有row，statement，mix三种格式。

 

为什么要有binlog日志？
binlog是server层得所有引擎共用，redo log是引擎层得，innodb引擎特有。

 

sql执行时得事务流程：
两阶段提交：先写入redo log，处于prepare阶段，再写binlog，最后再提交事务，处于commit
阶段，事务完成。
如果先些完redo log再写binlog或者先写完binlog再写red log，都会发生主从不一致得问题。两阶段提交
使得两个日志得提交不会产生中间状态，使得redo log和binlog保持一致。

 

优化建议：
innodb_flush_log_at_try_commit参数设置成1，每次事务都持久化到磁盘，sync_binlog也设置成1，
保证mysql重启binlog不会丢失。

 

三.事务隔离
实现：
每条更新记录都会生成一条回滚操作，生成一个read-view，记录上得最新值，
通过回滚操作，都可以得到前一个状态得值。

 

优化建议：
1.auto_commit=0手动长时间不提交（如果是长连接）可能导致长事务，尽量不要使用长事务，
长事务会导致undo log被保留很长时间，占用大量存储空间，所以auto_commit尽量设置为1，让事务自动提交，
使用commit workand chian语法，提交事务时自动开启下一个事务。—长事务还会导致MDL读锁一直不释放
2.通过set MAX_EXECUTION_TIME控制语句最长执行时间，防止某些事务因为意外执行太长时间。
3.可以在information_schema库得innodb_trx中查询长事务，监控该表，设置长事务预警值超过就报警。
4.innodb_undo_tablespaces设置成2或更大值，设置后清理回滚段更方便。为什么？。

 

四.索引上
常见索引类型：
hash表：区间查询很慢，新增很快（联想ArrayList和linkedList），只适合等值查询场景
hash冲突：拉链法解决

 

有序数组：范围查询和等值查询性能好，使用二分法查找，更新麻烦。

 

树：二叉树每个节点左儿子小于父节点，父节点小于右节点，树深度过高，不适合磁盘读写，
mysql采用B+树，是一种N叉树，树深较小，读写性能好，

 

mysql中的索引：
主键索引：以id为树节点，节点上挂行数据。
普通索引：叶子节点挂id值，也称二级索引

 

索引维护：
mysql数据以数据页存储，插入数据要维护索引，插入一页里中间值需要逻辑上移动后面的值，这时如果数据页满了要申请新的数据页（页分裂），
页分裂会影响会影响空间利用率，当两个相邻页中由于删除了数据，利用率很低之后，会将数据页合并。

 

建索引策略：
a字段较大，b字段较小情况下，已经有（a,b）联合索引，单独用到b字段查询是否有必要建立b字段索引和a字段索引，此时已经
没必要建立a索引了，如果通过调整联合索引顺序可以避免少创建一个索引，那么优先采用，如果
必须要建立让b条件走索引，那么可以建立（b，a）索引和较小的b索引。

 

索引下推：
在索引遍历过程中，先找出符合索引字段的记录的id，减少了回表次数。

 

索引重建：
使用alter table T engine=innoDB。

 

优化：尽量使用自增主键，可以防止页分裂。

 

六.全局锁和表锁
全局锁：全局锁可用作逻辑备份
1.使用Flush tables with read lock（FTWRD），让整个库处于只读状态，增删改，事务提交，数据定一
等语句将被阻塞。如果客户端发生异常，会自动释放全局锁，
2.使用mysqldump工具，使用参数-single-transation时，导数据之前启动一个事务，开启一致性视图，但是myISAM
不支持事务的引擎不能使用。
3.使用set global readonly = true，这个逻辑一般会用来判断这个库是主库还是从库，而且如果
客户端发生异常，将导致整个库一致处于不可写状态。

 

表锁：

 

1. 语法是lock tables 。。。 read/write，会限制别的线程也会限制自己。比如线程1执行
   lock tables t1 read，t2 write，t1加读锁，则线程1和其他线程都只能读t1，t2加写锁，其他线程不能读写t2，
   线程1可以读写t2.
   2.元数据锁（metedata lock，MDL）：为了防止查询时多出或少列，对一个表增删改查时加
   MDL读锁，更改表结构是加MDL写锁。MDL读锁之间不互斥，两个写锁，读写锁之间互斥。
   MDL锁在语句执行完不释放，要等到事务结束后才释放，所以再加字段时先看下是否有长事务在
   执行。
   七.行锁
   行锁就是对数据行加锁，innodb事务中，行锁是在需要的时候才加上的，但要在事务结束才释放，这就是两阶段锁协议。如果说事务中需要锁多行，
   那么把最容易影响并发度的往后放，比如一条插入语句和一条更新语句，如果把更新语句放前面，那么一开始就加了行锁，直到事务提交时才释放，如
   果放后面，那么可以延迟加锁的时机，尽可能行锁的时间。

 

死锁
两个线程相互等待对方的锁会形成死锁，

 

死锁解决：
1.设置innodb_lock_wait_timeout，直接进入等待，直到超时。默认50s，

 

2.设置innodb_deadlock_detect=on，开启主动死锁检测，默认开启，每个线程获取锁时都要判断自己所依赖的线程是否被别人锁住，
会性能损耗，所以需要控制并发度，考虑中间件实现或者修改mysql源码。

 

八.事务隔离还是不隔离

 

例子：可重复读级别下，id=1行的k初始值为1，事务A由于一致性读，B和C事务都是在它启动之后才提交的，
所以A事务看不见这两个更新，如果看见了那就是幻读了，而对于B事务来说，C事务是在B事务启动之后才提交的，B事务的更新语句，
实际上是一个当前读，它是能看到C事务的更新的，所以B事务最后的查询结果是3，而如果B事务看不到C事务的更新，那C事务的更新就白白更新了，出现了数据错误。

 

                     事务A:                      事务B                                               事务C                                                

↓时间    start transation with consistent | 
                                                                                                 | 
                                           |    start transation with consistent                 | 
                                           |                                                     | update t set k=k+1 where id=1;

|                                                     |       
                                           | update t set k=k+1 where id=1;                      |

| select * from t where id=1;                         |

select * from t where id=1;       |                                                     |
         commit;                           |                                                     |
                                           | commit;                                             |

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

 

所以mysql的数据是有多个版本的，每个版本有自己的row_try_id,每个事务有自己的一致性视图。

 

九.普通索引和唯一索引的选择
普通索引和唯一索引在查询性能上相差不大，性能差异主要体现在更新时。
更新一条数据时， 如果内存中有就直接更新，如果没有innoDb会将这些操作缓存到changeBuff中，
（）changeBuff是poolBuff的一部分），这样就不用加载数据页了，mysql后台线程定期将changeBuff merge入磁盘。
唯一索引因为要判断数据是否重复，必须将数据页全部读入内存，再更新内存，而普通索引只需将操作缓存在changeBUff中即可，
可见在更新数据是唯一索引的性能差。要尽可能多得让更新操作缓存在changeBuffer中，提升更新性能。

 

changeBuffer通过innodb_change_buffer_max_size来动态设置，代表占用poolBuffer的比例。

 

优化建议：
1.如果你的查询后面马上跟着要对这条数据进行查询，那么changeBUffer作用就发挥不出来了，这时候
应该关掉changeBuffer。

 

2.历史数据的库将changeBuffer尽量开大，在机械硬盘的情况下，提升写入性能。

 

3.如果业务可以接受的情况下或者业务可以保证不会插入重复数据尽量选择普通索引。

 

十.mysql如何选择索引
选择索引的条件：
1.扫描行数越少，消耗的cpu资源越少，也就越快

 

2.列区分度：使用show index from t查看列的基数（不同值的个数），基数越接近总行数，性能越好，
mysql采用采样的方式获取表的基数M，当更新数据数大于1/M是触发索引统计，所以在一边删除历史数据一边插入数据
的业务场景下会发生重新索引统计，进而导致选错索引问题

 

3.回表代价：mysql如果认为直接扫描数据行更快，则不会走索引。

 

4.是否排序：如select * from t where a between 1 and 100 and b between 1 and 10000 order by b；
这时候虽然b扫描行数较多，但是按照b排序，b本身就是索引，天然有序能省去排序，如果选择a，在a索引筛选出
数据后回表查出的数据还要按照b字段排序，mysql认为这样代价更大，于是选择了索引b。

 

解决mysql索引错误选择：
1.analyze table t可以用来重新统计索引信息。
2.sql中加上force index(a)，不灵活，迁移数据库类型时可能语法不兼容。

 

十一.给字符串加索引

 

前缀索引：
过程：先搜索索引，找到满足条件的字段值得id，回表再判断一次该字段。
好处和问题：空间占用小，定义好长度，既能节省存储索引的空间，又能
减少扫描行数，但是扫描行数会增加，还有可能减小列的区分度，继续增加扫描行数，
必须回表所以无法使用覆盖索引的优化。

 

例子：身份证号前6位都一样如果使用前缀索引，那么列的区分度会很低，那么注意到身份证号
的后6位是区分度很大的，可以使用倒序存储方式，如：select field1，field2 from t where id_card = reverse(‘input_id’);
或者建一个hash字段存储id_card的hash值，这两种方式共同的问题是都不支持范围查询

 

十二.Mysql为什么会抖一下
刷脏页：
1.redo log写满了，此时拒绝所有更新请求
2.查询请求太多，直接从bufferPol中内存页没有数据，需要从磁盘读取，此时要申请新的内存数据页
，然后bufferPool内存不足了，需要淘汰内存页，如果淘汰的是脏页，需将脏页flush到磁盘，会影响性能。
3.mysql正常关闭时会把所有脏页都flush到磁盘
4.mysql在空闲状态下也要见缝插针的刷脏页。

 

1和2需要尽量避免，所以需要正确的设置innodb_io_capacity变量，需要知道磁盘的iops（磁盘的最大io能力，可以通过fio工具来测试）,
尽量不要接近75%。

 

ps：刷脏页时如果邻近的数据页也是脏页，就会一起被刷掉，innodb_flush_neighbors=1时生效，为0时只刷自己的。
mysql8.0后，这个配置已经默认是0了。

 

十三.删除数据，表文件大小不变
建议表文件放置单独一个文件xx.idb，即设置参数innodb_file_per_table=on,这样删除表时直接drop table，即可回收表空间，
innodb_file_per_table=off时表数据存放在系统共享空间。

 

对于删除数据行，mysql采用的是标记删除的方式，不会回收表空间，比如pageA中id=4到id=7之间的数据id=5被删除，
该位置标记为可复用，插入数据id=6数据时，会复用这个位置，而如果是插入id=10，则复用不了，因为数据不在4-7之间；
如果整个页都被delete了，则整个页被标记可复用，插入的id无限制；如果相邻的两个页利用率都低，mysql会合并页到一页，
另一页标记为可复用。
插入也会造成空洞，也即页分裂，当向一页插入数据时是按序存入的，如果当前页满，则会被插入下一页。

 

所以增删改查都是会造成空洞，解决空洞使用alter table t engine = innodb重建表,mysql会新建临时表，
然后自动完成转存数据，交换表名，删除旧表。转存数据会比较耗时，整个ddl过程不是online ddl,期间的任何
旧表数据写入会丢失。
mysql5.6后，这个操作支持online ddl，这个过程中允许日志写入和旧表的增删改查。

 

十四.count()这么慢
count()实现方式，myISAM中存储了每张表总数，所以直接返回即可，而innodb需要需要一行行读取数据累计和。而innodb
不存储总行数是因为MVCC多版本并发中，每一行记录都要判断是否对当前事务可见，所以只能一行行读，使用show table statue
也可以统计行数，但是基于采样统计的，不是有一定误差。
count(*)，count(1)返回满足条件的总行数，不会判断是否为null，
count(字段)返回满足条件的字段中，字段不为null的数据，count(id)同样是此逻辑。

 

十五.事务和日志解答
在redo log prepare之后mysql崩溃，事务会回滚，这时候binlog还没写入，并不会产生数据一致性问题；
在写完binlog还未commit时mysql崩溃，这时如果binlog存在并且完整，则事务直接commit，否则回滚。此时mysql
需要判断binlog是否完整，如果是row模式下binlog最后会有一个xid event，statement下会有commit;redo log和
binlog之间存储的每个操作日志通过xid关联。

 

十六.order by工作原理
explain中的extra有using filesort代表需要排序，mysql为线程分配一个sort bffer用于排序，

 

全字段排序：
先初始化sort buffer，放入查询字段，从索引中找到满足条件的id（如果用不到索引，直接扫描主键索引），回表查询数据行，放入sort buffer，
最后对索引字段做快速排序，返回客户端。其中按索引字段排序需要看sort buffer的大小（sort_buffer_size），
如果要排序的数据量小于sort_buffer_size，则使用内存排序，如果大于则会使用磁盘排序，磁盘排序使用归并排序法，
将排序数据分成12份，分别排序后再合并成一个大文件。

 

row_id排序：
如果单行太大，超过max_length_for_sort_data的值，mysql会使用row_id排序，
与全字段排序相比多了一次回表操作，sort_buffer中只放入id和排序字段，拍好序后再回表查询其他字段。

 

可以使用覆盖索引，优化，做到既不用临时表排序，也不需要回表。

 

十八.sql逻辑相同，性能却差很大
隐式的类型转换：
比如select “9” > 8,mysql会将字符串转为数字，所以使用不到索引。

 

隐式编码转换：
两个表的编码不一样也会导致编码转换而不走索引

 

对于给字段加表达式时，可以尝试将表达式优化到等式右边，避免直接对字段加表达式。

 

十九.为什么只查一行，也会很慢

 

1.长时间不返回
大概率表被锁住了，执行show processlist,state列如果有waiting for table
metedata lock，说明有线程持有MDL锁，这时候可以在mysql启动时指定performance_schema=on，通过查询
sys.schema_table_lock_waits这张表，可以找出造成阻塞的processId，使用kill 命令断开。

 

2.等flush
show processlist,state列如果有waiting for table flush，flush执行都很快，所以大概率是
其他长事务把flush堵住，flush又堵住了当前查询。

 

3.等行锁
更新语句事务一直不提交，导致当前更新被锁住，在mysql5.7中使用innodb_lock_waits查找占有
锁的线程kill之。

 

4。查询慢
设置long_query_time的值为0，

 

二十.幻读及其解决
在可重复读级别下，mysql使用间隙所来解决幻读问题，间隙所之间不会冲突，因为间隙所的目的就是
要禁止在间隙插入，间隙所和行锁合成next-key-lock，间隙锁是影响性能的。

 

在读提交下，会存在幻读，所以也就没有间隙锁。

 

二十一.加锁规则
可重复读下next-key-lock加锁规则：
1.索引上的等值查询，给唯一索引加锁时，next-key-lock退化为行锁。
2.索引上的等值查询，向右遍历时且最后一个值不满足等值查询时，next-key lock退化为间隙锁。
3.只有访问到的才加锁。
bug：唯一索引上的范围查询，会访问到不满足条件的第一个值为止。

 

锁是加在索引上的。

 

二十二.mysql饮鸩止渴的提升性能的方法
1.短连接
提高max_connections的值，但是连接要经里tcp连接，权限校验等，是比较重的操作，
提高连接数可能导致系统的负载更重。第一种方法可以考虑断开事务外空闲太久的线程，先用show
processlist 查看会话状态，找到处于sleeping的连接，innodb下查看information_schema
库的innodb_trx表查询具体的事务状态（字段为trx_state），事务外的可以用kill connection杀掉；
第二种方法是重启mysql开启 -skip-grant-tables，跳过连接的权限校验，这样做有安全风险，这时如果mysql版本为8.0，mysql为了安全
会加上–skip-network，此时，数据库只能被本地客户端连接。

 

2.慢查询
（1）索引没涉及好：
古老的方案是：在备库设置sql_log_bin=off关闭写binlog，然后备库上加索引，然后
主备切换，切换后在备库关掉binlog写。
更好的方案是gh-ost。

 

（2）语句没写好
mysql选错索引可以用force index(x)强制走索引，或者用mysql5.7提供的sql重写功能，
执行insert into query_rewrite_rules(pattern,replace,pattern_db_name) v; call query_rewrite.flush_rewrite_rules();

 

上线前有必要做sql分析，在测试环境打开慢查询日志，设置long_query_time=0,留意慢查询日志中的rows_exmained是否与
预期相符。

 

二十三.mysql是怎么保证数据不丢失的

 

1.binlog写入流程
binlog是写写入binlog cache，每个线程都有自己的binlog cache，当事务提交时，会把binlog cache
写入文件系统的page cache中，并清空当前线程的binlog cache（称为write），如果binlog cache超过了参数binlog_cache_size
的大小，就会调用fsync暂存到磁盘（称为fsync）。
sync_binlog=0时只write不fsync，为1时每次事务提交都会write+fsync，为n时代表n次事务提交才fsync（mysql异常时可能会丢失n次事务）

 

2.redo log写入机制
redo log写入由参数innodb_flush_log_at_try_commit,
innodb_flush_log_at_try_commit=0，每次只写redo log buffer；
innodb_flush_log_at_try_commit=1，每次直接持久化到磁盘。
innodb_flush_log_at_try_commit=2，只写入page cache

 

三种情况会将未提交事务的redo log写入磁盘或文件系统page cache
（1）innodb有后台线程，每秒将redo log buffer写入redo log，再刷盘，这时会将没有提交的事务
也持久化
（2）redo log buffer占用空间即将达到innodb_log_buffer_size时，innob主动写盘，
因为事务没提交，没有调用fsync，只会存在page cache中。
（3）innodb_flush_log_at_try_commit=1时，事务并行时后一个已提交事务将前一个未提交事务的redo log buffer刷盘。

 

所以双1配置需要等待一个binlog 刷盘，一次redo log刷盘。

 

疑问？未提交的redo log被刷盘会不会导致脏数据？？

 

并发场景下mysql提升tps的group commit机制
mysql会尽量延迟binlog和redo log fsync的时间，减少磁盘刷盘次数，
可以设置binlog_group_commit_delay（表示延迟多少微秒才fsync）和
binlog_group_commit_no_delay_count（表示积累多少次才fsync），这两个满足一个即可
执行fsync，io压力大的系统可以适当提高这两个参数的值。

 

二十四.mysql如何保持主备一致

 

mysql主从
主库负责读写，备库负责读，备库执行主库的binlog，保持主备一致，建议设置备库
为只读，这样可以防止主备切换时的双写造成主从不一致，还有通过read only可以用来判断节点的主从
状态。
主备切换流程
1.备库执行change master命令，设置主库的ip，端口，用户名，密码，以及开始执行binlog
的位置和日志偏移量。
2.备库执行start slave命令，备库启动一个io_thread用于维持和主库的长链接,一个sql_thread
用于接收中继日志relay log
3.主库校验备库传过来的用户名等，校验成功后从本地读取binlog传给备库
4.备库拿到binlog后，写到本地文件，sql_thread执行中继日志中的命令。

 

binlog的三种格式，由参数binlog_format控制：
statement：记录sql原文，前后会有begin和commit,statement下回可能导致主备执行时选的索引不一样；
row：前后同样有begin和commit，没有sql原文，替换成了Table_map(用于说明是哪张表)和delete_rows(用于定义删除行为，删除时有),
最后会有xid用于和redo log日志关联事务。借助mysqlbinlog 工具还能看到server_id和start position。
row格式很占用空间。但是能确保主备数据一致。
mixed：结合statement和row格式的一种混合格式。

 

双M模式下的循环复制通过设置每个节点不同的server_id来解决，每个节点传给对方时会把server_id传
过去，对方执行后又传回来了，这是判断下server_id是自己就不用执行。

 

疑问？备库不用开启binlog？

 

主库生成的binlog还没来得及发给从库，会导致数据一致性问题，采用semi-sync解决（只能解决一注一从方式）。

 

二十五.mysql怎么保证高可用

 

mysql主库写入binlog，日志中记录写入时间，传送到备库，备库接收binlog日志到执行完成这个事
务的时间减去主库传送binlog日志中的时间戳之差，称为主从延迟。在备库执行show slave status，
会显示seconds behind master表示延迟了多少秒。即使主库的时间有误，备库也会判断主库的当前时间，如果
有差值，则会减去这个差值。
主库延迟的根源是备库执行中继日志的速度赶不上主库传送的速度，可能是备库的机器性能比主库差，
或者备库查询请求太多，压力过大，抑或是大事务执行时间长，导致延迟。

 

主从切换的策略：
1.可靠性优先
在双M下，在备库执行show slave status，判断seconds_behind_master,如果小于某个值，比如5秒，
则进行切换，否则重试，把主库设置成read only，备库判断seconds_behind_master，直到为0为止，
之后把备库设置成可读写，即read only设置为false。
设置主库read only后整个mysql集群不可写状态。

 

尽量使用可靠性优先策略，并且binlog_format设置成row

 

二十六.备库为什么会延迟好几个小时

 

如果主库生成日志的速度远大于备库执行中继日志的速度，就会导致从库延迟越来越长，永远追不上主库。

 

主备并行复制能力：
mysql5.6以后支持并行复制，即前面的sql_thread变成了coordinator，只负责读取中继日志和
分发到各个worker，worker是多个线程，由slave_parallel_workers决定（建议设置为8-16比较好），

 

coordinator在分配时要保证同一个事务的sql放到同一个worker。更新同一行的两个事务必须放到同一个worker。

 

分发策略：
按库分发，hash库名到一个worker 中，MySQL 5.6 版本的并行复制策略。
按表分发，需将相同表hash到一个worker 中。
按行分发，按“库名 + 表名 + 唯一索引 a 的名字 +a 的值”hash到一个worker 中。

 

MariaDB 利用了redo log 组提交 (group commit)特性，因为能在一组中提交，一定不会修改同一行。
MySQL 5.7 并行复制策略由参数 slave-parallel-type 来控制，配置成DATABASE使用5.6版本的策略，
LOGICAL_CLOCK使用MariaDB 的策略，但进行了优化（针对两阶段提交）。MySQL 5.7.22 新增了一
个并行复制策略，基于 WRITESET 的并行复制

 

二十七.主库出问题了，从库怎么办

 

基于位点的主备切换
当我们把节点 B 设置成节点 A’的从库的时候，需要执行一条change master 命令：
CHANGE MASTER TO
//主库A’的信息
MASTER_HOST=$hostnameMASTERPORT=hos{t}_{n}ameMASTE{R}_{P}ORT=hostnameMASTERPORT=$port
MASTER_USER=$usernameMASTERPASSWORD=use{r}_{n}ameMASTE{R}_{P}ASSWORD=usernameMASTERPASSWORD=$password
//同步位点
MASTER_LOG_FILE=$masterlognameMASTERLOGPOS=maste{r}_{l}o{g}_{n}ameMASTE{R}_{L}O{G}_{P}OS=masterlognameMASTERLOGPOS=$master_log_pos

 

最后两个参数表示要从主库的 master_log_name 文件的 master_log_name 文件的
master_log_pos 这个位置的日志继续同步。而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量。
同步位点很难取到精确位置，因为不能丢数据，需要需要找一个“稍微靠前”的位点，然后判断跳过已经执行过的事务，但是主库异常前的那条语句
发送给从节点后，从节点执行了一次，并记录了同步位点，当从节点执行change master时，又执行了一遍同步位点时那条语句，导致sql错误或脏数据，
针对这个问题采用主动跳过事务（设置set global sql_slave_skip_counter=1;start slave;）或者 设置 slave_skip_errors 参数，直接设置跳过指定错误。
“1032”删除找不到行，“1062”主键冲突。同步完成后，稳定一段时间，去掉该设置 。

 

GTID
MySQL 5.6 版本引入了 GTID，每个mysql实例都维护了一个gtid集合，存储已经执行过的事务
GTID启动加上参数 gtid_mode=on 和 enforce_gtid_consistency=on。
GTID 的全称是 Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。它由两部分组成，格式是：
//server_uuid实例第一次启动时生成，全局唯一
//gno一个整数，初始值是 1，每次提交事务的时候分配给这个事务，并+1
//mysql文档中叫GTID=source_id:transaction_id
GTID=server_uuid:gno

 

如果从库中已经存在了某事务，使用以下方式跳过。前三句执行了一个空事务，并把GTID加到了从库的集合中。

 

基于 GTID 的主备切换

 

CHANGE MASTER TO
MASTER_HOST=$hostnameMASTERPORT=hos{t}_{n}ameMASTE{R}_{P}ORT=hostnameMASTERPORT=$port
MASTER_USER=$usernameMASTERPASSWORD=use{r}_{n}ameMASTE{R}_{P}ASSWORD=usernameMASTERPASSWORD=$password

 

//使用GTID协议
master_auto_position=1
从库会把自己的GTID集合传给切换的主库，主库会计算差集，然后把不同的同步给从库。现在备库执行
start slave的逻辑为：
1.从库设置新主库为a’
2.备库b把Set_b 发给新主库a’ ，
3.新主库a’算出set_a与set_b的差集，即存在于set_a但不存在于set_b中的日志，判断
a‘本地是否包含差集中的所有事务，如果不包含，说明a’已经删除了这些binlog，返回错误，反之
则发给b执行。之后就从这个事务开始按顺序发给b执行。

 

二十八.读写分离有哪些坑

 

过期读解决方案：
1.对于必须读到最新数据的强制走主库，其他的走从库
2.select sleep(1)让查询线程休眠一秒。用户体验不好，如果查询不足1秒，也要等1秒。

 

判断主备无延迟方案
判断show slave status 结果里的 seconds_behind_master 参数的值是否等于0，但该值精度为秒。
对比位点确保主备无延迟，Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和
Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。
对比 GTID 集合确保主备无延迟，Retrieved_Gtid_Set、Executed_Gtid_Set是否相同。
上述方案，只会判断从库已经收到的事务，是否执行完，但对于一些主库已执行，但从库还没收到的情况，这时
Retrieved_Gtid_Set、Executed_Gtid_Set是相同的，但是主库穿过来的那个事务在新从库还是看不到还是存在主备延迟。

 

配合semi-sync
从库执行主库的binlog后，返回ack确认，主库收到ack才返回客户端确认事务完成。
在一主多从下，如果查询请求落到了没有收到最新binlog日志的从库上，还是会有过期读问题。
还有如果在业务更新的高峰期，主库的位点或者 GTID 集合更新很快，那么上面的两个位点等值判断就会一直不成立，
很可能出现从库上迟迟无法响应查询请求的情况。

 

等主库同步位点
select master_pos_wait(file, pos[, timeout]);
它是在从库执行的；参数 file 和 pos 指的是主库上的文件名和位置；timeout 可选，设置为正整数 N 表示这个函数最多等待N 秒。

 

这个会返回一个正整数 M，表示从命令开始执行，到应用完 file 和 pos 表示的 binlog 位置，执行了多少事务。除了正常返回之外，还会返回：

 

1.如果执行期间，备库同步线程发生异常，则返回 NULL；
2.如果等待超过 N 秒，就返回 -1；
3.如果刚开始执行的时候，就发现已经执行过这个位置了，则返回 0。

 

所以可以这么判断：
1.trx1 事务更新完成后，马上执行 show master status 得到当前主库执行到的File 和 Position；
2.选定一个从库执行查询语句；
3.在从库上执行 select master_pos_wait(File, Position, 1)；
4.如果返回值是 >=0 的正整数，则在这个从库执行查询语句；
5.否则，到主库执行查询语句。
所以可能存在将流量打到主库的情况，所以需要做好主库限流策略。

 

GTID 方案
select wait_for_executed_gtid_set(gtid_set, 1);
等待，直到这个库执行的事务中包含传入的 gtid_set，返回 0；超时返回 1。

 

MySQL 5.7.6 版本开始，允许在执行完更新类事务后，把这个事务的 GTID 返回给客户端，这样等 GTID 的方案就可以减少一次查询。

 

1.trx1 事务更新完成后，从返回包直接获取这个事务的 GTID，记为 gtid1；
2.选定一个从库执行查询语句；
3.在从库上执行 select wait_for_executed_gtid_set(gtid1, 1)；
4.如果返回值是 0，则在这个从库执行查询语句；
5.否则，到主库执行查询语句。
同样请求可能打到主库，注意限流。

 

二十九.如何判断一个数据库是不是出问题了？

 

select 1 判断
当前并发查询数超过innodb_thread_concurrency时， select 1会返回，但执行查询命令时会等待。
该参数默认值是0，表示不限制并发查询数，建议把 innodb_thread_concurrency 设置为 64~128 之间的值。不是并发连接数。

 

查表判断
在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：
但有其他一个问题，更新事务要写 binlog，binlog 所在磁盘的空间占用率达到 100%，那么所有的更新
语句和事务提交的 commit 语句就都会被堵住。但是，系统这时候还是可以正常读数据的。

 

更新判断
常见做法是放一个 timestamp 字段，用来表示最后一次执行检测的时间。但备库不能写同一行，
所以需要使用多行，id为server_id。但有可能，机器的I/O已经100%，但刚好健康检查的sql拿到了资源，成功返回了。

 

内部统计
关于磁盘利用率100%的问题。
MySQL 5.6 版本以后提供的 performance_schema 库，就在 file_summary_by_event_name 表里统计了每次 IO 请求的时间。
老师比较倾向的方案，是优先考虑 update 系统表，，然后再配合增加检测 performance_schema的信息。

 

三十.动态看待加锁

 

三十三.查很多数据会不会把mysql内存打爆

 

mysql边读边发
执行查询语句时，重复获取行写入net_buffer(默认16k)，net_buffer写满就调用网络接口发出去，
发送成功后清空net_buffer，再重复获取行数据。net_buffer是mysql server层的。
发出去的数据存在socket send buffer中，如果socket send buffer满了，就等待直到socket send buffer
重写可写。
show processlist命令中state显示sending to client就表示socket send buffer写满了，显示sending
data其实是正在执行的意思，可能处于任意阶段，比如锁等待等。

 

全表扫描对innodb的影响
mysql的innodb层使用buffer_pool来管理内存数据页，采用lru（less recently use）算法，但是，如果对全表扫描，
就会导致内存数据页一直读磁盘，然后淘汰最近最久不用的数据页，内存命中率很低（show engine innodb status查询），
查询变慢。
buffer_pool由参数innodb_buffer_pool_size设置，建议设置成物理内存的60%-80%
mysql对lru算法进行了优化，将buffer_pool分成了两份，链表头部5/8是young区，链表尾部是old区，
新申请的内存页都是放在old区，每次访问到old区数据页，判断其存在时间如果超过1秒，则将其移动至
young区，否则保持不变，所以如果做全表扫描淘汰在old区就可以完成，young就是访问比较频繁的数据了。

 

三十四.到底能不能使用join

 

NLJ算法（Index Nested-Loop Join）
如果被驱动表上有索引，采用的是NLJ算法，遍历驱动表取出每行数据，去被驱动表中
查找符合条件的数据，这个过程对驱动表做了全表扫描，对被驱动表走索引树搜索，当然如果还要查询其他字段，
需要回表。

 

Simple Nestd-loop Join
如果被驱动表没有使用索引，则每次取到驱动表的数据，都要去遍历被驱动表主键索引查找符合
关联字段的数据，会很笨重，所以mysql使用的是Block Nested-Loop Join算法。

 

BNJ（Block Nested-Loop Join）算法
如果被驱动表没有使用索引，取到驱动表的数据所有数据放入join_buffer中，也是遍历被驱动表主键索引，
被驱动表每行数据与join_buffer中驱动表的数据判断关联条件，这样就只用扫描一次被驱动表了，
explain中extra显示using join buffer nested loop,虽然判断次数和Simple Nestd-loop Join算法一样，
但是是内存操作，会很快。
如果join buffer放不下所有驱动表的数据，就将驱动表分为几段，依序放入join buffer，block就是分块的
意思。由此可得尽量使用NLJ算法，用上被驱动表的索引，如果用BNJ算法，应该用小表做驱动表。
小表指的是where筛选之后数量比较少的查询结果的表。

 

三十五.join语句怎么优化
MRR(Multi-Range Read)优化
MRR优化了回表操作，先根据普通索引定位到符合条件的数据，放入read_rnd_buffer中，根据id进行排序，
直到read_rnd_buffer放满后，去主键索引中顺序查找数据，然后清空 ead_rnd_buffer，如此循环。
设置参数optimizer_swith=“mrr_cost_based=off”,explain中extra显示using MRR代表开启MRR优化。
MRR优化将磁盘随机读转化成了顺序读，要多值查询时才能体现这个优化的价值。

 

BKA算法（Mysql5.6的Batched Key Access）
mysql使用BKA算法对NLJ算法进行优化，在使用NLJ算法时在驱动表中就不止取一行数据，而是取多行数据，
放入join_buffer中，如果join_buffer放不下时，会分段执行。
这个算法依赖MRR优化，需要开启MRR，通过设置参数set optimizer_switch=“mrr=on,mrr_cost_based=off,batched_key_access=on”

 

BNL算法性能问题
BNL算法中频繁对被驱动表扫描，如果是一个冷表，不仅io的压力大，还会破坏buffer_pool的内存淘汰机制，
多次扫描导致LRU链表头部总是放入冷表数据，如果冷表很大，还会导致，old区的数据页不到1秒就被淘汰，
无法进入young区，young区也就没有了淘汰机制，进而降低了内存命中率和复用率。
针对以上问题可以适当增大join_buffer的大小，减少被驱动表的扫描。

 

BNL算法的优化的思路就是BNL算法的优化一般考虑在被驱动表上加索引，如果没可能建索引，可以考虑临时表，
在临时表对应字段上建索引。

 

hashJoin
将驱动表全部存入hash数组，用被驱动表的关联字段去hash表中查询，这样速度也会很快，可以在
业务端进行。
mysql join优化：
a left join b on a.f1 = b.f1,如果a.f1上没有索引，而b.f1上有索引，即使是a left join b，
原意是想让a做驱动表，b做被驱动表，因为b.f1上有索引，mysql优化器会将a做为被驱动表。

 

四十一.怎么最快地复制一张表？
如果可以控制对源表的扫描行数和加锁范围很小的话，我们简单地使用 insert … select 语句即可实现。
mysqldump -h$host-Phost-Phost-P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where=“a>900” --result-file=/client_tmp/t.sql

 

1. –single-transaction 的作用是，在导出数据的时候不需要对表 db1.t 加表锁，而是使用
   START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
2. –add-locks 设置为 0，表示在输出的文件结果里，不增加" LOCK TABLES t WRITE;"
3. –no-create-info 的意思是，不需要导出表结构；
4. –set-gtid-purged=off 表示的是，不输出跟 GTID 相关的信息；
5. –result-file 指定了输出文件的路径，其中 client 表示生成的文件是在客户端机器上
   的。

 

导出 CSV 文件
//导出
select * from db1.t where a>900 into outfile ‘/server_tmp/t.csv’;
//导入
load data infile ‘/server_tmp/t.csv’ into table db2.t;

 

物理拷贝方法
在 MySQL 5.6 版本引入了可传输表空间(transportable tablespace) 的方法，可以通过导出 + 导入表空间的方式，实现物理拷贝表的功能。
假设我们现在的目标是在 db1 库下，复制一个跟表 t 相同的表 r：

 

1.执行 create table r like t，创建一个相同表结构的空表；
2.执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；
3.执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；
4.在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令；
5.执行 unlock tables，这时候 t.cfg 文件会被删除；
6.执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，
由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

 

四十五.自增id用完怎么办？

 

表定义的id自增值id
表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。
这个情况下会报主键冲突了，可以采用长整型作为id，

 

InnoDB 系统自增 row_id
如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，
长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，
所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的
row_id，然后把 dict_sys.row_id 的值加 1。如果到达上限后，再有插入数据的行为要来申请 row_id，
拿到以后再取最后 6 个字节的话就是 0，然后继续循环。所以会导致覆盖数据。

 

Xid
redo log 和 binlog 相配合的时候，它们有一个共同的字段叫作 Xid。它在 MySQL 中是用来对应事务的。
MySQL 内部维护了一个全局变量 global_query_id，每次执行语句的时候将它赋值给 Query_id，然后给这个变量加 1。
如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。
而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，
不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，
同一个 binlog 文件里，Xid 一定是唯一的。不过 global_query_id 达到上限后，会继续从 0 开始计数，
由于 global_query_id 为8个字节，所以一般不会出现到达上限的情况。

 

max_trx_id
Xid 是由 server 层维护的。InnoDB 内部使用 Xid ，就是为了能够在 InnoDB 事务和 server
之间做关联。但是，InnoDB 自己的 trx_id，是另外维护的。InnoDB 内部维护了一个 max_trx_id
全局变量，每次需要申请一个新的 trx_id 时，就获得 max_trx_id 的当前值，然后并将 max_trx_id 加 1。
InnoDB 数据可见性的核心思想是：每一行数据都记录了更新它的 trx_id，当一个事务读到一行数据的时候，
判断这个数据是否可见的方法，就是通过事务的一致性视图与这行数据的 trx_id 做对比。
对于正在执行的事务，你可以从 information_schema.innodb_trx 表中看到事务的 trx_id。
但是对于只读事务，InnoDB 并不会分配 trx_id。

 

max_trx_id 会持久化存储，重启也不会重置为 0，那么从理论上讲，只要一个 MySQL 服务跑得足够久，就可能到达上限，
然后从 0 开始的情况。然后就会导致脏读。但只存在理论上，如果一个 MySQL 实例的 TPS 是每秒 50 万，持续这个压力的话，
在 17.8 年后，就会出现这个情况。

 

事务
当两个有事务注解的方法相互调用时，
1.传播级别
REQUEST_NEW,REQUESTED,NESTED：
REQUIRED_NEW是来一个线程就开启一个事务，判断当前线程是否在事务中，在就挂起当前事务，
执行新事务。
REQUIRED是先判断当前线程是否在事务中，是就加入，不是就创建事务执行。
NESTED和REQUESTED类似，是基于save point实现的。
SUPPORT，NOT_SUPPORT:
SUPPORT翻译为（不）支援其他事务，本身不创建事务，所以来一个线程判断当前线程是否在事务中
，级别为SUPPORT时加入事务，级别为NOT_SUPPORT时，抛出异常。
MANDATORY,NERVER：
MANDATORY:强制性的支持事务，当前线程无事务抛异常。
NERVER:永远不使用事务，当前线程有事务抛异常。

 

事务不生效：
1.mysql不是innodb引擎。
2.没有指定transationManage，或者一个事务涉及多个数据库。
3.aop使用jdk动态代理，内部对象方法互相调用不会被spring aop拦截

 

挂起事务：
spring将当前事务名，隔离级别，事务信息等保存在一个变量中，将和事务相关的threadlocal
变量设置为从未开启事务一样，挂起后当前线程就像是无事务状态。
流程：
1.获取事务属性（@transational注解中的配置）
2.加载配置中的transationManager
3.获取收集事务信息Transation
4.执行目标方法
5.出现异常，尝试处理
6.清理事务相关信息
7.提交事务

 

doBegin方法：
设置自动提交为false，用threadlocal存储每个数据库连接。

 

关键对象：
PlatformTransationManager：保存当前数据源dataSource，提供提交，
回滚，挂起，获取事务信息接口。

 

TransationInfo：存储关键对象TransationStatus，代表当前运行哪个事务。
提供方法：isNewTranstion（）判断是否新的事务，hasSavepoint（）判断当前

 
 

 
 
1
2

 

事务是否有保存点；setRollbackOnly（）和isRollbackOnly（）当子事务回滚时，并不会
回滚事务，而是对子事务设置savePoint；isCOmpleted（）事务是否完成。