一些关于mysql的笔记

隔离级别

1、并发问题

　　1.1脏读

　　　　即读取到了其他事务尚未commit到的数据。通过行锁解决。

　　1.2不可重复读

　　　　同一事务两次执行select的数据不一致。通过MVCC解决。

　　1.3幻读

　　　　同一事务两次执行count的结果不一致。通过next-key锁解决。

2、各种隔离级别和解决的问题

　　read uncommited 以上三种问题都未解决

　　read committed 只解决了脏读问题

　　repeatable read 解决了以上三种问题（版本是8.2）,不知道是不是版本的问题，实测是解决了。但网上一片声音说没解决。

　　serializable 串行化执行事务，效率太低了。MVCC+next-key。

 

　　临时表　

　　　　概念

　　　　内存表和临时表

　　　　前者是使用memory引擎时创建的表，表结构在磁盘，数据在内存 

　　　　特征：

　　　　1、创建语句是create temporary table ...

　　　　2、只对创建时的session线程可见

　　　　3、临时表可以与正常表同名，且如果是同名，show create 、增删改查则都是针对临时表。但show tables则没有临时表。

　　　　临时表很适合join的场景：1、不用创建之后无法自动删除的问题 2、多个session进行join优化时无需担心表重名而创建失败问题。

　　　　临时表的实现

　　　　实际是每一个session线程维护一个临时表链，操作表时先遍历临时表链，有的话就优先使用临时表。结束时则遍历整个链，然后drop。

 

　　内部临时表

　　　　使用临时表的时机

　　　　1、一边读数据，如果能直接得到结果，则不需要额外内存，否则就需要内部临时表

　　　　2、join_buffer是无序数组，sort_buffer是有序数组，临时表是二维表结构

　　　　3、执行逻辑需要二维表，则会考虑使用临时表。

　　memory引擎

　　　　数据组织形式：innodb是B+结构，数据是按主键顺序存储，数据有空洞也只能往前插，数据是放在主键索引上，其他索引保存主键ID，这种称之为索引组织表。memory是hash结构，数据按插入的顺序排序，数据找到空洞就能查，数据单独存放，索引上保存数据位置，这种称之为堆组织表。

　　　　二者的不同

　　　　1、前者数据总是有序存放的，后者是按写入顺序存放的。

　　　　2、数据有空洞，前者仍旧往前写新值，后者是找到空洞就能插入。

　　　　3、数据位置发生变化，前者只需要修改主键索引，后者是所有的索引（比如列索引）。

　　　　4、前者主键索引需走一次，其他索引需要走两次。后者是都一样。

　　　　5、前者支持变长数据类型，后者只能固定长度

　　　　6、前者的数据是在磁盘。后者是在内存，重启就不再存在了。

　　　　7、前者范围扫描只需遍历索引即可（如果有索引的话）。后者只能全表扫描，然后排序。

　　　　不建议用memory的原因：1、锁粒度 2、数据持久化

　　　　1、内存表的锁力度只能是表级别，在高并发场景下插入不适用。查询性能的话，innodb查询大多数走的是buffer_pool,性能也不会太差。

　　　　2、内存表的数据会在重启时丢失。

　　　　　　2.1 M-S架构下，如果备库升级，数据丢失，这时客户端传来一条update，备库会报找不到更新的行，导致主从同步停止。

　　　　　　2.2 双M架构下 备库binlog写入delete会导致主库表的数据被删除

　　　　适用场景：内存临时表

　　　　在优化join时采用内存临时表的话，1、线程专有，无并发问题 2、可以优化查询 3、临时表重启后也是需要删除数据的。4、不影响其他线程。

　　　　8.0版本貌似就加入了这个优化，extra 里显示join bufer(hash Join).

　　自增ID怎么不连续

　　　　自增键的保存策略：

　　　　　　MyISAM是将自增值保存在数据文件里

　　　　　　innoDB是保存在内存，5.7及之前的版本是在重启后找到最大id，然后加一。8之后会有自增键有持久化的能力，重启时可以通过redolog恢复

　　　　自增键的修改机制：

　　　　　　1、如果ID是0、null、未指定，则将表当前的AUTO_INCREMENT作为ID

　　　　　　2、如果指定了，就用指定的值

　　　　自增键的生成算法：

　　　　　　从auto_increment_offset开始，以auto_increment_increment为步长，持续增加，直到找到第一个大于当前插入值的值最为新的自增值。ps:在双主结构，可能会让步长为2，一个为奇数，一个为偶数，避免主键值冲突。

　　　　不连续的原因：根本都是申请了未使用或者使用又回滚了

　　　　1、唯一键冲突，导致申请成功，但插入失败。则申请的ID空闲出来了。

　　　　2、事务回滚了

　　　　3、批量申请自增ID的策略导致，多申请了，但是没有全部使用。

　　　　为什么不能回滚？

　　　　原因是如果能回滚，那么会出现A申请了x,B申请了x+1,此时A又回滚了，C再申请了x，D申请了x+1报主键冲突插入失败。解决这个冲突有两个办法：1、申请时判断ID存不存在，成本太高。 2、申请锁的粒度放大到事务，锁力度过大，并发下降

 

　　　　innodb_autoinc_lock_mode

　　insert为什么会这么多锁？

　　　　insert...select需要注意select会在扫描到的记录和间隙加上读锁。如果对象是同一张表，那么可以引入临时表来优化。insert出现唯一键冲突会在冲突的唯一键上加共享next-ke lock,所以要尽快提交和回滚事务。　

　　　　分区表

　　　　　　分区是在引擎层进行分表的操作，对于innodb来说，多少个分区就表示有多少个.idb文件。分区的锁的范围会缩小，只会锁住对应分区的数据，其他分区则不影响。

　　　　　　每次mysql在第一次访问分区表时，会把所有的分区都访问一遍，myisam通常会导致打开的表数量过多而报错。

　　　　　　分区策略：

　　　　　　　　myisam是通用分区策略，访问分区是由server层去控制，innodb是本地分区策略，就在分区表打开超过innodb_open_files时会关闭之前打开的文件。所以不会抱打开文件过多的错误。

　　　　　　总结：

　　　　　　1、mysql第一次打开分区表会访问所有的分区表

　　　　　　2、所有分区共用MDL锁

　　　　　　3、获取MDL之后的执行会根据分区规则，只访问必要分区

　　　　　　优势是对业务透明，清理历史数据方便

　　　　

　　　　

 

 

优化篇

SQL的优化

　　1、join 的优化

　　　　1.1 尽量用小表做驱动表

　　　　1.2 尽量让被驱动表走索引，不触发BNJ算法。

　　　　1.3 出现BNJ，则可以选择用临时表，先插入被驱动表的数据，然后在join，将BNJ转化成INJ。

　　　　1.4 出现BNJ，还可以用hash join,将被驱动表的数据以hash的结构先放在应用端，然后取出被驱动表的数据，逐个比较结果。这个在8.0版本开始已经自动支持的。

　　2、group by 优化

　　　　2.1 没有排序则使用order by. null

　　　　2.2 尽量走上索引，尽量消除using temporary 和using filefort

　　　　2.3 数据量不大的话尽量使用内存临时表，可以适当调大tmp_table_size的参数

　　　　2.4 数据量过大使用SQL_BIG_RESULT这个提示。直接走排序算法

Server端的优化

　　1、mysql体系

　　　　mysql是有一组线程、一些内存块和若干服务线程组成。默认是7组线程：1个master、4个IO线程(读写+insert buffer+log)、一个锁线程、一个错误监控线程。命令是show enging innodb status.

　　2、内存优化

　　　　2.1myISAM 内存优化。

　　　　2.2Innodb DB的优化

　　　　　　　2.2.1缓存机制

　　　　　　　InnoDB缓存池是由free list、flush list和LRU list组成。free是空闲的缓存块列表，flush是需要刷新到磁盘的缓存块。LRU是正在使用的缓存块，是buffer pool的核心。整个LRU分为young list和old list。新缓存块会先放在old的头部。经过一定的时间（innodb_old_blocks_time）才会到young的头部。优先淘汰old的尾部（也就是整个LRU的尾部）。

　　　　　　　2.2.2 innodb_buffer_pool_size的设置

　　　　　　命中率过低，则调大innodb_buffer_pool_size；命令：

mysqladmin -s /tmp/mysql.socket |grep -i innodb_buffer_pool

实测没啥用。。。。命中率的计算公式（1-innodb_buffer_pool_reads/innodb_buffer_pool_read_request）*100。命中率低了，需要调大innodb_buffer_pool_size；

　　　　　　　2.2.3调整old sublist大小和innodb_old_blocks_time

　　　　　　如果没有大表的扫描，可以适当加大innodb_old_block_pct的值或者减少innodb_old_blocks_time。本质都是增大old区或者快速将old区的数据移到young区。有大表可以临时加大old区，之后再调回正常的。

　　　　　　　2.2.4调整缓存池的数量

　　　　　　整个innodb buffer pool会被切割成很多个buffer pool.如果并发很多，需要将innodb_buffer_pool_instances调大，增加buffer pool的个数，减少竞争区。

　　　　　　　2.2.5 控制刷页，延长数据的缓存时间

　　　　　　如果可用的缓存页被用完了，那么后台线程会刷新缓存到磁盘。主要是受两个参数的影响，innodb_max_dirty_page_pct，这是缓存池的脏页比例，默认是75.还有一个是innodb_io_capactity这是每次刷新时要刷新的数据页个数。如果转速较低的磁盘，可以减少innodb_io_capactity个数。如果等待新页很多，那就要减小innodb_max_dirty_page_pct。

　　　　　　　2.2.6 双写

　　　　　　mysql的数据页通常是16k，操作系统是4k。无法保证完整的一个mysql数据页写入的原子性.所以是1、先把数据页写入2M的double write buffer 2、DWB分两次1M写入系统表空间(顺序写，速度快) 3、DWB再写数据到数据表里。然后记录redo file.然后再把数据页写入到磁盘中。如果中途出错了，那么可以从double write buffer 恢复数据。

　　　　2.3 排序缓存的设置

　　　　通过调大sort_buffer_size来优化order by 或group 语句的性能。调大join_buffer_size来改善join的性能。这两个配置都是针对客户端连接线程的，如果链接线程很多，这两buffer又很大，就很容易造成内存浪费，可以设置小的全局配置，然后每个连接单独设置大的buffer。

　　3、InnoDb log 的机制及优化

　　　　3.1、InnoDB重做日志

　　　　　　redo log 是保证事务的一个重要机制。工作原理图如下：

 

 

 　　　　　一个update语句的执行过程如下：

　　　　　　1、将要更新的数据从磁盘移至buffer_pool，并加独占锁

　　　　　　2、将undo log 写到undo的表空间

　　　　　　3、在buffer_pool更新数据。

　　　　　　4、将数据页的改动记录到redo_log_buffer

　　　　　　5、事务提交。根据innodb_flush_log_at_trx_commit的值，做不同反应。然后释放独占锁。

　　　　　　　　0 一秒后 再将redo_log_buffer 写入redo_log_file.如果此时mysql宕机，则会丢失一秒的redo_log。mysql进程和系统挂了都会丢失一秒数据。

　　　　　　　　1 立马将redo_log_buffer写入redo_log_file。这是默认的，最安全的选项。但性能最差。

　　　　　　　　2 将redo_log_buffer写入OS cache，由操作系统决定何时写入磁盘。这个如果mysql进程挂了，只要系统还在则不会丢失。较1要更安全。

　　　　　　6、后台择机将数据页刷入磁盘。

　　　　LSN:Log Sequeue Number 日志序列号，实际是已写入日志的总大小。新LSN = 旧LSN + 写入日志的大小。比如日志文件是600M，此时的LSN是1G，现在要将512字节的数据写入redo log file.实际写入过程如下：

　　　　1、1G对600M取余，得出400M

　　　　2、在redo log 400M的位置写入512字节日志内容

　　　　3、LSN变成1000000512.

　　　　3.2 设置log file size.

　　　　每个日志文件写满后，innodb就切换另一个日志文件，切换会触发数据库检查点，将导致小批量刷新缓存脏页。每半小时写满一个日志文件比较合适。可以通过计算每半小时的日志量来设置innodb_log_file_size.

　　　　3.3调整innodb_log_buffer_size.

　　　　如果遇到大量update、insert、delete的事务，则可以适当调大该参数，提高事务的处理性能。

　　4、调整并发相关参数

　　　　4.1 max_connections

　　　　　　如果connection_errors_max_connections不为零且在快速增长，则说明很多因最大连接数限制而链接失败。这时可以调大一些。

　　　　4.2 back_log 

　　　　　　这个参数控制mysql坚挺TCP端口积压请求栈的大小。需要短时处理大量链接请求，则可以增大该值。

　　　　4.3调整table_open_cache

　　　　　　表缓存 = max_connection * N(执行线程需要打开的最大个数)

　　　　4.4调整thread_cache_size

　　　　　　这个是mysql缓存客户服务线程的个数。如果threads_created/connections 越接近1则表明新开的线程和总链接数占比越高。这时是可以加大thread_cache_size的值。

　　　　4.5 innodb_lock_wait_timeout

　　　　　　等待锁的时间设置，默认是50ms.需要快速交互的可以适当调小。对于后台批处理的操作则可以调大。

　　　　
redo log

redo log分为两部分：redo log buffe和redo log file。前者是内存中的，后者是磁盘中的。

 

redo log 和bin log

redo log是innodb引擎特有的，是在数据写入磁盘前先执行的。也就是先写redo log再写磁盘。redo log 是记录某个数据的物理修改。

bin log是server层实现的。是记录某一行做了什么修改（逻辑修改）

两阶段提交

将新数据写入内存，然后写入redo log buffer。之后再写bin log.写完后,事务提交，此时必须将redo log buffer 写入到磁盘中redo log file.也即先写redo log buffer,再写bin log ,最后写redo log file.

 

 

参考博客：https://www.cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html

 

关于join 

生长上对于join，一般是两个问题：

1、能不能用join

2、两张大小不同的表，用哪张做为驱动表。

 

实例：

建立两张相同的表t1,t2。都有索引a。

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;

drop procedure idata;
delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  while(i<=1000)do
    insert into t2 values(i, i, i);
    set i=i+1;
  end while;
end;;
delimiter ;
call idata();

create table t1 like t2;
insert into t1 (select * from t2 where id<=100)

现在看一下join语句：

select * from t1 straight_join t2 on (t1.a=t2.a);

这个语句的执行过程如下

1、从t1全表扫描，取出一条数据d1

2、从t2索引a扫描，取出一条索引，再通过索引回表找到一条数据d2。拼接成最终的一条数据

3、重复1、2步骤直到取完。

这里是100次全量扫描+100次索引扫描（200次树索引）=200次

 

如果拆开来执行

select * from t1
for(i<length)
    select * from t2 where a=$R.a；

执行过程如下：

1、全表扫描取出t1 100行

2、循环100次,执行select * from t2 where a=$R.a；

全量扫描100+100次索引扫描（200次树索引）=200次

两种扫描次数一样，但是前者是一次连接，一个语句。而后者是执行了101次语句，极端情况就是101次连接。

假设被驱动表的行数为M，驱动表是N，那么近似的复杂度是N+N * 2 * log2M(两次索引查)。

显然N的影响最大。也就是驱动表的行数影响是最大的

小结：

在可以使用被驱动表的索引下

1、join要比分开查性能高

2、驱动表要选择小表

以上的算法也叫“Index Nested-Loop Join”，简称NLJ。

 

如果不能使用被驱动表的索引是什么情况呢？

那就是被驱动表不再走索引而是走全量了。

扫描行就变成100*1000 = 100000了。

如果双方的表各是100万，那么扫描行就是10000亿了。

实际的处理是

1、将t1的数据读入join_buffer

2、全量扫描t2，将t2的每一行跟join_buffer中进行比对，满足则组合成结果返回。

假设小表行数为N，大表为M。

1、两表都需要全表扫描M+N

2、内存中判断次数是M*N

在join_buffer放的下的情况下，选择大表小表做驱动表M*N都不变，耗时一样。

 

如果t1过大，join_buffer放不下，则分块（Block）放入。然后分部分进行。

假设驱动表为N，需要k段才能完成，被驱动表的数据行是M。

那么k=λN 。λ是（0，1）。λ由join_buffer决定，join_buffer越大，λ越小。

整个过程可以表示成：

1、扫描行数是N+kM = N+λNM （磁盘）

2、判断次数是N*M（内存）

N和M相比，N的影响更大。所以选择小表作为驱动表。

当然，λ才是影响最大的，因为扫描是在磁盘进行的。所以λ越小越好。如果join很慢，可以试试调大join_buffer.

 

综上可以回答这两个问题了。

1、能不能用？

　　如果是能用被驱动表上的索引时，没问题，性能还更好。

　　如果不能使用，那么join还是不要的好。

　　可以通过Extra中是否出现Block Nested Loop，出现了，说明没有用被驱动表上的索引。实测mysql 8.0.22版本是出现“Using join buffer (hash join)”字样。

2、用大表还是小表做驱动表？

　　对于NLJ的情况，用小表。

　　对于BNLJ，分两种：

　　　　如果join_buffer能放得下，都一样。

　　　　如果join_buffer放不下，则使用小表。

查询那么多数据，会不会把数据库server端内存打爆。

这个问题弄懂了server发送数据的流程

1、server获取一行，放到net_buffer，直到满了。net_buffer大小由net_buffer_length决定.

2、调用网络接口socket发送出去

3、如果发送成功（实际是成功写入socket send buffer），则继续1、2步骤。

4、如果网络接口返回EAGAIN或WSAEWOULDBLOCK，表明socket send buffer已经满了。此时等待，直到socket send buffer可写，再继续发送。

也就是mysql是边读边发送的，如果客户端接收慢了，server就发送不出去，那么就会造成长事务。

 

客户端如果使用-quick参数则是mysql_use_result模式，客户端是读一行处理一行，一旦处理很慢就会出翔上面的情况。一般建议不用。通常建议使用mysql_store_result接口，也就是客户端先保存在内存里，再处理。

如果太多sending to client的线程，则可以加大net_buffer_length.

Sending Data是当mysql进入执行阶段后的一个状态。执行完成再置为空字符。

 

关于buffer pool 的LRU算法

数据结构是用链表实现的。

每次被访问则将数据页放入到表头，如果链表满了，此时不能新增一个数据页，那只能淘汰表尾的一个数据页。

这种设计会有一个问题，如果一个大量冷数据的查询过来，那么每个数据页的产生都会淘汰链表上的数据，此时命中率就会非常低。

innodb 作出进一步的优化。

将链表分成5:3的young和old区。每个新数据的产生都会放入old区的表头。对于old区的数据页，如果innodb_old_blocks_time毫秒后（说明LUR链表还没满，或者满了，但是该数据页被频繁访问），这个数据页还在链表上，那就把这个数据页放到LRU的表头。

这里思想类似Java中的young区和old区的概念，只是二者的概念互换。一个新的数据页只有在old区存活足够多的时间才能放到young区里。

这个的改进就是避免了大量冷数据查询影响其他正常查询。