优化join:join_buffer_size调大。

join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。

如果join_buffer放不下表的所有数据，策略很简单，就是分段放。

优化join:在被驱动表上建索引:BNL转BKA

优化join:临时表存储被驱动表符合条件的数据

用临时表的大致思路是：

把被驱动表表t2中满足条件的数据放在临时表tmp_t中；
为了让join使用BKA算法，给临时表tmp_t的字段b加上索引；
让表t1和tmp_t做join操作。

此时，对应的SQL语句的写法如下：

create temporary table temp_t(id int primary key, a int, b int, index(b))engine=innodb;
insert into temp_t select * from t2 where b>=1 and b<=2000;
select * from t1 join temp_t on (t1.b=temp_t.b);

优化join:代码使用hash map优化

实现流程大致如下：

select * from t1;取得表t1的全部1000行数据，在业务端存入一个hash结构，比如C++里的set、PHP的dict这样的数据结构。
select * from t2 where b>=1 and b<=2000; 获取表t2中满足条件的2000行数据。
把这2000行数据，一行一行地取到业务端，到hash结构的数据表中寻找匹配的数据。满足匹配的条件的这行数据，就作为结果集的一行。

注意该方案对内存要求可能比较大!

范围查询 Multi-Range Read优化(MRR)

假设，我执行这个语句：

select * from t1 where a>=1 and a<=100;
即id:951 - 1000

主键索引是一棵B+树，在这棵树上，每次只能根据一个主键id查到一行数据。因此，回表肯定是一行行搜索主键索引的，基本流程如图1所示。

如果随着a的值递增顺序查询的话，id的值就变成随机的，那么就会出现随机访问，性能相对较差。虽然“按行查”这个机制不能改，但是调整查询的顺序，还是能够加速的。

因为大多数的数据都是按照主键递增顺序插入得到的，所以我们可以认为，如果按照主键的递增顺序查询的话，对磁盘的读比较接近顺序读，能够提升读性能。

这就是MRR优化的设计思路。此时，语句的执行流程变成了这样：

1. 根据索引a，定位到满足条件的记录，将id值放入read_rnd_buffer中;
2. 将read_rnd_buffer中的id进行递增排序；
3. 排序后的id数组，依次到主键id索引中查记录，并作为结果返回。

这里，read_rnd_buffer的大小是由read_rnd_buffer_size参数控制的。如果步骤1中，read_rnd_buffer放满了，就会先执行完步骤2和3，然后清空read_rnd_buffer。之后继续找索引a的下个记录，并继续循环。

另外需要说明的是，如果你想要稳定地使用MRR优化的话，需要设置set optimizer_switch="mrr_cost_based=off"。（官方文档的说法，是现在的优化器策略，判断消耗的时候，会更倾向于不使用MRR，把mrr_cost_based设置为off，就是固定使用MRR了。）

优化join:NLJ转BKA

如果要使用BKA优化算法的话，你需要在执行SQL语句之前，先设置

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

其中，前两个参数的作用是要启用MRR。这么做的原因是，BKA算法的优化要依赖于MRR。

优化group by

如果对group by语句的结果没有排序要求，要在语句后面加 order by null；
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；(Mysql的另外一个参数max_heap_table_size比tmp_table_size小时，则系统会把max_heap_table_size的值作为最大的内存临时表的上限，大于这个时，改写硬盘，实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。)。我们生产max_heap_table_size是16777216即16M，tmp_table_size是2097152
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法得到group by的结果。

优化orderBy

增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

减少select 后面的查询的字段。禁止使用select *

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

MySQL支持二种方式的排序，FileSort和Index，Index效率较高，FileSort方式效率较低

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

设置磁盘io容量,全力刷脏页

innodb_io_capacity会告诉InnoDB你的磁盘能力。这个值我建议你设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

其实，因为没能正确地设置innodb_io_capacity参数，而导致的性能问题也比比皆是。之前，就曾有其他公司的开发负责人找我看一个库的性能问题，说MySQL的写入速度很慢，TPS很低，但是数据库主机的IO压力并不大。经过一番排查，发现罪魁祸首就是这个参数的设置出了问题。

他的主机磁盘用的是SSD，但是innodb_io_capacity的值设置的是300。于是，InnoDB认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

虽然我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。所以接下来，我们就一起看看InnoDB怎么控制引擎按照“全力”的百分比来刷脏页。

刷脏页速度:无法控制

InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。

邻居脏页控制策略

innodb_flush_neighbors

如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值设置成0。因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。

在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

计算当前数据库脏页比例

show global status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'

show global status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'

使用a/b

删除数据,表空间不变优化:innodb_file_per_table

我建议你不论使用MySQL的哪个版本，都将这个值设置为ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

所以，将innodb_file_per_table设置为ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。

我们在删除整个表的时候，可以使用drop table命令回收表空间。但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

我们要彻底搞明白这个问题的话，就要从数据删除流程说起了。

隔离级别的选择

读未提交会导致脏读等一系列问题。

串行化会导致事务互斥串行执行,存在并发度很低的问题。

所以隔离级别一般都会选择读已提交或者可重复读。

但是从并发度来看,读已提交>可重复读。

原因1:间隙锁是在可重复读隔离级别下才会生效的。

所以，如果把隔离级别设置为读提交的话，就没有间隙锁了。但同时，你要解决可能出现的数据和日志不一致问题，需要把binlog格式设置为row。这也是现在不少公司使用的配置组合。

如果读提交隔离级别够用，也就是说，业务不需要可重复读的保证，这样考虑到读提交下操作数据的锁范围更小（没有间隙锁），这个选择是合理的。

原因2:在RR隔离级别下，条件列未命中索引会锁表！而在RC隔离级别下，只锁行!

可重复读事务隔离级别：

查询条件带索引进行的锁行。
查询条件不带索引进行的锁表。

若列上没有索引，MySQL会走聚簇(主键)索引进行全表扫描过滤。每条记录都会加上X锁。但是，为了效率考虑，MySQL在这方面进行了改进，在扫描过程中，若记录不满足过滤条件，会进行解锁操作。同时优化违背了2PL原则。

读已提交事务隔离级别级别：
不管查询条件是否带上索引都是进行的行锁

但其实我想说的是，配置是否合理，跟业务场景有关，需要具体问题具体分析。

如果确实没有可重复读的业务需求或者场景那就放心大胆的选择读已提交隔离级别吧。

查询当前session或者global的隔离级别

SELECT @@tx_isolation;

SELECT @@global.tx_isolation;

设置当前session的隔离级别命令

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

定期重建表

alter table TableName engine=innodb

optimize table TableName

以上2个命令都可以达到重建表的目的。

在实际的项目中,我们在每个月的月初会定期归档数据到csv文件中,并将历史数据做物理删除。此时产生大量的间隙,要使用以上2个命令来重建表,减少间隙,释放表空间,从而达到优化表的目的。所以对一些大表,尤其是增删改比较频繁的大表,最好定期做重建表的操作。要注意的是重建表可能会阻塞其他的写操作,因此重建表最好在夜里进行。

alter analyze optimize 区别

使用optimize table、analyze table和alter table这三种方式重建表的区别。这里，我顺便再简单和你解释一下。

从MySQL 5.6版本开始

alter table t engine = InnoDB（也就是recreate）默认的就是上面图4的流程了；

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；

optimize table t 等于recreate+analyze。

binlog_format

第一种是 statement，第二种是 row。第三种格式，叫作 mixed，其实它就是前两种格式的混合。

statement:原始sql,可能用的索引不同,导致主备不一致

row 格式:操作时记录主键,占用空间大

mixed 格式

基于上面的信息，我们来讨论一个问题：为什么会有 mixed 这种 binlog 格式的存在场景？

推论过程是这样的：

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。

但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

也就是说，mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险。

因此，如果你的线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed。

独立表空间

在配置文件（my.cnf）中设置： innodb_file_per_table = 1

优点： 1.每个表都有自己独立的表空间。 2.每个表的数据和索引都会存在自已的表空间中。 3.可以实现单表在不同的数据库中移动。 4.空间可以回收（除drop table操作处，表空不能自已回收） -Drop table操作自动回收表空间，如果对于统计分析或是日志表，删除大量数据后可以通过:alter table TableName engine=innodb;

回缩不用的空间。 -对于使innodb-plugin的Innodb使用turncate table也会使空间收缩。 -对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。

缺点： 1.单表增加过大，如超过100个G。 2.相比较之下，使用独占表空间的效率以及性能会更高一点。

预读与缓存失效相关配置

innodb_old_blocks_pct

参数：innodb_old_blocks_pct

介绍：老生代占整个LRU链长度的比例，默认是37，即整个LRU中新生代与老生代长度比例是63:37。

画外音：如果把这个参数设为100，就退化为普通LRU了。

innodb_old_blocks_time

参数：innodb_old_blocks_time

介绍：老生代停留时间窗口，单位是毫秒，默认是1000，即同时满足“被访问”与“在老生代停留时间超过1秒”两个条件，才会被插入到新生代头部。

总结

（1）缓冲池(buffer pool)是一种常见的降低磁盘访问的机制；

（2）缓冲池通常以页(page)为单位缓存数据；

（3）缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

（4）InnoDB对普通LRU进行了优化：

将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，页被访问，才进入新生代，以解决预读失效的问题

页被访问，且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

InnoDB的Hash索引

InnoDB存储引擎会监控对表上各索引页的查询，如果监控到某个索引页被频繁查询，并诊断后发现如果为这一页的数据创建Hash索引会带来更大的性能提升，则会自动为这一页的数据创建Hash索引，并称之为自适应Hash索引。自适应Hash是通过缓冲池中B+树的页进行构建的，建立速度很快，不需要对整张表的数据都构建Hash索引，所以我们又可以把自适应Hash索引看成是索引的索引。注意一点就是InnoDB只会对热点页构建自适应索引，且是由InnoDB自动创建和删除的，所以不能人为干预是否在一张InnoDB的表中创建Hash索引。

自适应Hash索引

官方有告诉我们每一种存储引擎所支持的索引结构，

在dev.mysql.com/doc/refman/…

我们找寻关于自适应Hash索引的部分，对他总结一下就是：自适应Hash索引特征能使InnoDB在具有适当的工作负载和足够缓冲池内存的系统上执行的更像内存中的数据库的操作，且不会牺牲事务特性或可靠性，MySQL能基于监视到的搜索规则，使用索引键的前缀构建Hash索引，前缀可以是任意长度，并且可能只有b+树中的某些值出现在Hash索引中，Hash索引其实就是对经常访问的索引页进行构建的。

这又说明其实InnoDB是支持Hash索引的，但并不是真正意义上的Hash，而是通过自己的监视情况自动对某些热点索引值构建的内存Hash。

开启和关闭

默认情况下自适应索引是开启状态，毕竟是可以提升性能的嘛，我们也可以通过命令开启和关闭，并可以查看自适应索引的。

开启默认就是开启的，可以通过命令show variables like ‘innodb_adaptive_hash_index’;查看自适应哈希索引的状态，并可以在命令行通过show engine innodb status\G查看自适应Hash索引的使用信息(AHI的大小，使用情况，每秒使用AHI搜索的情况等等) 关闭负载较重的情况下，就不太适合开启自适应Hash索引了，因为这样可以避免额外的索引维护带来的开销，可以在启动的时候通过参数–skip-innodb-adaptive-hash-index关闭。参考：blog.csdn.net/m0_46761060…

避免长事务对业务的影响

首先，从应用开发端来看：确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL的general_log开起来，然后随便跑一个业务逻辑，通过general_log的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的目标就是把它改成1。

确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用begin/commit框起来。我见过有些是业务并没有这个需要，但是也把好几个select语句放到了事务中。这种只读事务可以去掉。

业务连接数据库的时候，根据业务本身的预估，通过SET MAX_EXECUTION_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

其次，从数据库端来看：监控 information_schema.Innodb_trx表，设置长事务阈值，超过就报警/或者kill；Percona的pt-kill这个工具不错，推荐使用；在业务功能测试阶段要求输出所有的general_log，分析日志行为提前发现问题；如果使用的是MySQL 5.6或者更新版本，把innodb_undo_tablespaces用于设定创建的undo表空间的个数设置成2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

如何查询长事务 select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

切换状态：

set ="index_condition_pushdown=off";
set ="index_condition_pushdown=on";

48.mysql优化-配置篇+技巧篇