MySql

mysql调优

一般设计到mysql调优可以从几个方面入手，分别是硬件、mysql系统配置、表结构优化、sql语句优化以及添加索引等。

硬件

当数据库数据量大、每次访问的数据量却很小，且客户端与数据库交互频繁、数据离散程度高、并发程度高时，可以考虑从硬件方面入手，比如说选取强劲的cpu，以及能够顶得住频繁交互的网络设备。

像数据量的的，且每次访问数据量也打的，客户端与数据库交互次数较少、并发程度低的，选择的硬件对cpu要求较低，但硬盘容量要大

如果统计报表时间过长的，可以考虑集群部署，将统计任务拆分成多个子任务进行并行统计。

mysql系统配置

• query_cache_size：查询缓存大小对于热点数据进行缓存可以提升某些查询的效率，适当的大小配置可以缓存更多数据，提升查询效率。
• sort_buffer_size：在需要排序操作时，分配指定大小的内存用以进行排序，太小的话会需要进行磁盘io导致性能下降。但是这个变量不能随便设置为过大，一般操作是在配置文件中设置小一点，然后如果实在需要较大的排序空间时，在执行sql的时候，加上以下语句单独设置大小即可。

set @@session.sort_buffer_size := <value>；
//执行语句
set @@session.sort_buffer_size := DEFAULT

• read_buffer_szie：查询时分配该大小的内存作为缓存，一次性分配
• join_buffer_size：表关联缓冲，可以设置一个全局值，也可以为每个线程单独设置，同样地关联缓冲太小也可能造成磁盘io，从而性能下降。
  一般来说配置修改是不需要经常变动的，做优化都是先把表结构优化和sql索引优化完成先才考虑配置优化。配置修改要慎重。

表结构优化

• 数据类型优化。这个是最显而易见的，按照业务需求选择合适的数据类型可以显著减少存储空间使用，提交磁盘读写效率。
• 减少一个表中不必要的列，mysql存储引擎api工作时会涉及一个行列转换的过程，过多的列会提高mysql存储引擎的工作代价，cpu负载显著提高。
• 太多的关联，“实体-属性-值”的设计模式，导致查询的时候需要关联太多的表，影响查询性能。如果能显著减少关联，可接受范围内可以对表里面的列进行冗余，减少关联。
• 物化视图，预先统计查询好我们需要的热点查询，我们在查询时只需要查询视图即可，背后实际的查询工作由视图完成。
  创建视图：CREATE VIEW 视图名(列1，列2…) AS SELECT (列1，列2…) FROM …;

添加索引

索引生效情况

全值精确匹配

匹配最左前缀

匹配列前缀

匹配范围值

精确匹配某一列（最左），并且另一列匹配范围

只访问索引的查询（覆盖索引）

语句优化

步骤

通过show status命令了解各种sql的执行频率

定位效率较低的sql语句，通过explain去分析sql语句，通过desc定位语句的问题

查询语句的执行过程

MySql的基本架构示意图

因此可将Mysql大致地分为server层和存储引擎两部分

Server层包括连接器、查询缓存、分析器、优化器、执行器等，且所有的内置函数（如日期、时间、数学和加密函数等），并且所有的跨存储引擎的功能也在这一层实现，比如存储过程、触发器、视图等

存储引擎负责数据的存储和提取，其架构模式是插件式的，mysql从5.5.5版本开始默认的存储引擎是InnoDB，在创建表的时候可以在语句后面添加engine=memory来指定存储引擎。不同的存储引擎的表数据存储方式不同，支持的功能也不同，但不同的存储引擎共用一个Server层

过程

连接，连接成功后可使用show processlist查看连接状态，command中显示为sleep表示系统有空闲连接，连接器根据wait_timeout来控制客户端长时间没动静时断开动作，默认是8小时，这是客户端在再次请求就会收到错误提示，需重新连接。数据库的连接分为长连接和短连接，长连接是指客户端持续请求，则一直使用同一个请求，短连接则是指每次执行完很少的几次查询后就断开连接，下次查询在重新建立一个连接。因此在实际应用中最好使用长连接，但全部使用长连接后内存占用会升高，这是因为mysql在执行过程中临时使用的内存时管理在连接对象里的，这些资源会在连接断开的时候才会释放，过大可能会被系统强行杀掉，可定期断开长连接，比如大查询后内存过大的时候，也可重新初始化连接资源

查询缓存，mysql拿到一个查询请求后会先到查询缓存查看是否已经执行过这条语句，如果有则直接返回结果。因为语句的执行可能会议key-value的形式来保存的，key是语句，value是结果。大多数的时候最好不要使用查询缓存，因为查询缓存往往弊大于利，由于查询缓存的失效非常频繁，只要对一个表的更新，那么这个表上所有的查询缓存都会被清空，除非是一张静态不爱，很长时间才会更新一次，只要将参数query_cache_type设置为DEMAND，这样语句默认不使用查询缓存，如果确定使用查询缓存的语句，可添加SAL_CACHE显示指定使用查询缓存查询数据。mysql8.0已经把查询缓存去掉了

分析器，对sql语句做解析，来判断该sql语句时干嘛的。先做词法分析，因为sql是由字符串和空格组成的，由此来根据字符串分析出该字符串是什么，代表什么。然后做语法分析，根据词法分析的结果，然后根据语法规则来判断该sql语句是否满足mysql的语法，如果语句不对，则会收到错误提示

优化器，分析器分析完语句是干什么的时候，在执行前会经过优化器的处理，比如当表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联的时候，决定各个表的连接顺序

执行器，开始执行语句。开始执行是会先判断是否有无对这个表的执行权限，然后根据表的引擎，去使用该引擎提供的接口

带change buffer的读过程

当内存中存在的时候，直接从内存中返回，不存在则从磁盘中读入内存，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

更新语句的执行过程

基本执行流程和查询流程一样，只不过更新语句的执行过程还设计到两个重要的日志模块，即redo log（重做日志）和binlog（归档日志）。

redo log，每一次的更新操作都需要写进磁盘，然后磁盘液压找到对应的那条记录，然后在更新，整个过程io成本、查找成本都很高。为了解决这个问题，MySql就用了WAL技术（是数据库系统提供原子性和持久化的一系列技术），关键点就是先写日志，在写磁盘，具体来说，当有一条记录需要更新的时候，存储引擎会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了，同事，存储引擎会在适当的时候将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做的，当redo log写满后（redo log大小是固定的），当满之后会把日志前面的记录更新到磁盘，然后清除。也因此当数据库发生异常重启的时候，之前提交的记录都不会丢失，这个能力称为crash-safe，需要注意的是redo log是InnoDB特有的

binlog，写入过程在在执行器执行语句的时候，会生成这个操作的binlog，然后把binlog写入到磁盘，然后执行器调用引擎的提交事务接口，引擎吧刚刚写入的redo log改成提交状态，更新完成

prepare和commit，即我们所说的两阶段提交

两阶段提交

为了让两份日志之间的逻辑一致，从而保证数据的恢复，或者扩容的时候

为什么会有两种日志

最开始Mysql是没有InnoDB的，自带的是MyISAM，但MyISAM没有crash-safe的能力，且binlog只用用于归档，也因此InnoDB才会使用redo log来实现crash-safe能力

两种日志的区别

redo log是InnoDB特有的，而binlog所有的引擎都可以使用，因为是在Server层实现的

redo log是物理日志，记录的是在某个数据页上做了什么修改，而binlog是逻辑日志，记录的是这个语句的原始逻辑，比如给ID=2这一行的c字段+1

redo log是循环写的，空间固定，而binlog是可以追加写入的，即到一定大小后会切换到下一个，并不会覆盖以前的日志

什么场景下，一天一备会比一周一备更有优势

对应的指标为RTO（恢复目标时间）

带change buffer的更新过程

涉及到四个部分：内存、redo log、数据表空间、系统表空间

更新语句在内存中，直接更新内存；否则，就在内存的change buffer区域，记录下该更新语句的信息，然后将这两个过程记入redo log中

事务隔离

事务

简单来说就是保证一组数据库操作，要么全部成功，要么全部失败。

事务的支持是在引擎中实现的，但并不是所有的引擎都支持事务，比如MyISAM就不支持

四大特性：原子性、一致性、隔离性、持久性

隔离性与隔离级别

当数据库上有多个事务同时执行时，就有可能出现脏读、不可重复读、幻读的问题，也因此与了隔离级别的概念。

事务的隔离级别

事务的隔离级别包括：读未提交、读提交、可重复读、串行化，隔离的越严实，效率越低

• 读未提交，指一个事务还没提交时，它做的变更就能被别的事务看到
• 读提交，指一个事务提交之后，它做的变更才会被其他事务看到
• 可重复读，指一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的，且未提交变更对其他事务也是不可见的
• 串行化，即对于同一行记录，写会加写锁，读会加读锁。当出现读写所冲突的时候，后访问的事务必须等前一个事务执行完后再能继续执行

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准

事务隔离的实现

在MySql中，每条记录的更新，都会同时记录一条回滚操作，且不同时刻启动的事务会有不同的视图（read-view）。同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）回滚日志会在系统中没有事务需要用到这些回滚日志时自动删除，即系统中没有比这个回滚日志更早的视图的时候，因此尽量不要使用尝事务。

长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库中可能用到的回滚记录都会被保存，导致大量占用存储空间。除此之外，长事务还占用锁资源，可能导致拖垮整个库

可重复读的核心就是一致性读；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。表结构不支持可重复读，这是因为表结构没有对应的行数据，也没有row trx_id，因此只能循环当前读的逻辑。

读提交的逻辑和可重复读的逻辑类似，它们最主要的区别是：

• 在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其它查询都共用这个一致性图；
• 在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图

事务的启动方式

显示启动事务语句，begin或start transaction。配套的提交语句是commit，回滚语句是rollba

set autocommit = 0 ，这个命令会将这个线程的自动提交关闭掉，意味着如果执行一个select语句，这个事务就启动了，而且不会自动提交。这个事务持续存在，知道主动执行commit或rollback语句，或者断开连接，所以建议使用set autocommit = 1，来启动事务

可在information_schema库的innodb_trx这个表中查询长事务

如何避免长事务对业务的影响

确实是否使用了set autocommit=0，可通过general_log，随便跑一个业务逻辑来确认

确认是否有不必要的只读事务

通过set max_execution_time命令来控制每个语句执行的最长时间，避免单个语句意外执行太长时间

监控information_schema.Innodb_trx表，设置尝事务阈值，超过就报警或者杀死

业务测试阶段要求输出所有的general_log，分析日志行为提前发现问题

如果使用的是mysql5.6以上的版本，把innodb_undo_tablespaes设置成2，或者更大的值，当数显大事务导致回滚端过大，这样设置后清理起来更方便

索引

索引的常见模型

哈希表，适用于只有等值查询的场景，比如Memcached及其他一些NoSql引擎

有序数组、在等值查询和范围查询场景中的性能优秀，使用二分，但更新所需的成本高，往中间插入时，后面的记录必须往后挪动，所以只使用与静态存储引擎

搜索树，其索引不仅存在内存中，还要写入到磁盘中，这是大多数数据库的存储不使用二叉树的原因，即当一颗百万节点的平衡二叉树，树高20，一次查询可能需要访问20个数据块，每个数据库的读取需要10ms的寻址时间，对于百万行的表，单独访问一行可能需要20个10ms的时间，为了让一个查询尽量少地读磁盘，就必须让查询过程尽量少的访问数据库。那么就要使用N叉树，这里的N叉中的N取决于数据快的大小

跳表

LSM树

InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表，每个索引对应一颗B+树

根据主键查询记录只需要搜索主键索引的b+树即可，而基于普通所用的查询需要先搜索普通索引的B+树找到主键，然后根据该主键再去主键索引的B+树找到主键对应的值，这个过程称为回表，因为主键索的叶子节点存的是行数据，而普通索引的叶子节点存的是主键，所以主键索引也被称为聚簇索引。

如何避免回表？

覆盖索引，即查询语句中查询的字段值已经在普通索引的叶子节点中，就不需要再去搜索主键索引了

索引的维护

保证索引的的有序性。

以B+树为例，当某个叶子点点的数据源已经满了，这时需要申请一个新的数据页，然后挪动部分数据过去，这个过程称为页分裂，除了性能受影响外，还影响数据页的利用率

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

最左前缀原则

索引下推优化

MySql5.6引入，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，以此减少回表次数。

怎么选择普通索引和唯一索引

两者的选择锁造成的影响是微乎其微的，因为InnoDB的数据是按数据页为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16kb。所以当找到某条记录的时候，它所在的数据源就已经在内存里了，那么对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算，挡了，如果该记录正好是数据页的最后一条记录，那么取下一条记录就比较复杂，但是对于整形的字段，这种情况出现的概率比较低，所以对于查询过程来说，两种索引的选择影响不大，性能差不多。

对于更新过程来说，当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话没在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个也有关的操作，通过这种方式就能保证这个数据逻辑的正确性。change buffer是可以持久化数据的，减少度盘操作，而且数据读入内存时需要占用buffer pool的，因此可以避免占用内存，提高内存利用率。可通过innodb_change_buffer_max_size来动态设置change buffer的大小，对应唯一索引来说没必要设置change buffer，因为其在判断唯一的时候需将数据页读入内存中，因此更新操作中，普通索引的性能会唯一索引好。普通索引在更新时，只需将记录更新在change buffer中就结束了，减少了随机io的访问，而将change buffer中的操作应用到原数据源称为merge。对于写多读少的场景来说，使用change buffer更为合适，比如账单，日志等

Mysql选错索引的原因分析

当没有指定使用哪个索引，且一张表中有多个索引时，此时使用哪个索引时由mysql的优化器来确定的。

当一条本来执行很快的语句，由于mysql选错索引而导致执行速度变慢的原因：

当启动两个事务时，其中一个事务在查询之前不断做删除并新增，由于我们说过选择索引是由优化器来确定的，而优化器选择索引的目的是为了找到一个最优的执行方案，并用最小的代价去执行语句，在数据库里面，扫描行数是影响执行代价的因素之一，扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的cpu资源越少，当然，这并不是唯一的判断标准，优化器还会结合是否使用临时表，是否排序等因素进行综合判断

优化器有时为什么放着行数少的不用，反而使用行数多的呢？这是因为，如果选择行数少的索引，每次拿到索引上的值，都会回到主键索引上查出整行数据，这个代价优化器也会算进去的，所以，此时优化器会选择直接在主键索引上进行扫描，从而导致了全表扫描，没有额外的代价，虽然此时执行时间并不是最优的。可通过analyze table 表名 的命名来修正统计信息

语句执行之前，是不能精确地判断满足条件的有多少条，而是根据统计信息来估算的。索引的基数，可通过show index from 表名的方法来查看每个字段中索引的基数，而索引的基数是通过采样统计的方法来确定的，采样统计是，InnoDB默认会选择N个数据页，统计这些页面上不同值，然后得到一个平均值，再乘以这个索引的页面数，这样就得到了这个索引的基数。因为数据表是会持续更新的，所以当变更的数据行超过1/M的时候，会自动触发重新做一次索引采样统计。

存储索引统计的方式可通过设置参数Innodb_stats_persistent的值来选择：

为on时，表示统计信息会持久化存储，这时，默认的N是20，M是10

为off时，表示统计信息值存储在内存中，这时，默认的N是8，M是16

索引选择异常和处理

可通过force index来指定索引

可通过修改语句去诱导优化器选择正确的索引，比如排序、限制记录数等

新建一个更合适的索引、或删掉误用的索引

前缀索引

好处是可以节省空间，且不用额外增加太多的查询成本，坏处是增加了扫描行数，影响性能，还有就是使用了前缀索引，就不能使用覆盖索引，会导致始终会回到主键索引查询数据

选择的前缀区分度越高越好，这样重复的键值就越少，可通过统计索引上有多少个不同的值来判断使用多长的前缀

对于前面字符串区分度不高的，还可以使用倒序存储，

还可以使用hash字段，来保存查询字段的校验码，并这个hash字段上创建索引，在插入新纪录的时候，同时将得到的校验码填到这个行字段中。由于可能存在hash冲突，所以还需要在where条件中判断查询字段的值是否相同

覆盖索引

是指索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据

索引失效问题

如果对索引字段做了函数统计，就用不上索引了，这是Mysql规定的，因为加了函数之后会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。但不是不使用索引，而是走了全索引扫码

隐式类型转换，查询条件中某字段是varchar类型，却输入了整形，这时候mysql会将整形转换为varchar类型，此时查询会走全表扫描，因为对于优化器来说，转换类型相当于做了函数操作，从而导致优化器放弃走树搜索功能

隐式字符编码转换也是一样的

解决办法：修改字段属性，如果数据量大的话修改sql语句，即将参入的参数进行转换

hash索引和B-Tree索引

InnoDB中表数据的存储依赖于主键索引，当表不建主键索引时，InnoDB是怎么做的

对于InnoDB，可以简单地把所有数据视为索引，每一个索引都对应一个B+数，而主键对应的索引就是聚簇索引，表的所有数据都存储在聚簇索引上，而除了聚簇索引的普通索引存储的只是主键的引用，所以，查询的时候对于普通索引需要进行回表才能取到具体数据。

缺少主键，且存储引擎是InnoDB，Mysql是如何做的

如果定义了主键，那么InnoDB会使用主键作为聚簇索引

如果没有定义主键，那么会使用非空的唯一索引作为聚簇索引

如果既没有主键，也找不到合适的非空唯一索引，那么InnoDB会自动生成一个不可见的名为ROW_ID的列，索引名为GEN_CLUST_INDEX的聚簇索引，改列是一个6字节的自增数值，随着插入而自增

但这会影响性能，没有主键索引，查询会进行全表扫描，而且还会印象数据插入性能，插入数据需要生成ROW_ID，而生成的ROW_ID是全局共享的，并发会导致锁竞争，进而影响性能

explain

分析语句，是否使用索引、扫描行数等

MySql锁

全局锁

对整个数据库实例加锁。

全局锁的典型使用场景是做全库逻辑备份。也就是吧整库每个表都select出来存成文本。

全局读锁

加全局读锁的命令：Flush tables with read lock（FTWRL）。让整个库处于只读状态，线程中的数据更新语句、数据定义语句和更新类事务的提交语句会被阻塞。

此时如果做数据库备份：

• 如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
• 如果在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟

由此看来加全局读锁做备份并不太好，但备份为什么还要加锁？

如果不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的，既然是视图逻辑不一致，那可以在可重复读隔离级别下开启一个事务官方自带的逻辑备份工具是mysqldump，当mysqldump使用蚕食-single-transaction的时候，导数据之前就会启动一个事务，以此来保证能够拿到一致性视图，这是因为mvcc的支持，此时数数据是可以正常更新的，为什么还要FTWRL？那是由于有些存储引擎是不支持事务的

除了加全局读锁使数据可进入只读状态，也可以使用set global readonly=true的方式使数据库进入只读状态，但不建议使用，一是global影响范围大，二是异常处理机制上有差异。

表级锁

表锁

语法是lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放，需要注意的是lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。也因此对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响还是很大的

元数据锁（MDL）

MDL不需要显示使用，在访问一个表的时候会被自动加上。

MDL的作用是保证读写的正确性。当对一个表做增删改查操作的时候，加MDL读锁；当对表做结构变更操作的时候，加MDL写锁。

读锁之间不互斥，因此可以有多个线程同事对一张表增删改查。

读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果两个线程要用时给一个表加字段，其中一个要等另一个执行完才能开始执行。

MDL是系统默认加的，由于MDL在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放，如果有读写读的场景，整个表会出现完全不能读写的状态，且这个库的线程很快就会爆满。

行锁

Mysql的行锁是在引擎层由各个引擎自己实现的，但并不是所有的引擎都支持行锁，比如MyISAM就不支持。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度，这也是为什么InnoDB能取代MyISAM的原因之一。

行锁是针对表中行记录的锁。即两个事务同时更新同一行时，需等待另一个事务更新完后才能执行。

在InnoDB事务中，行锁是在需要的时候才加上的，但不是不需要了就立刻释放，而是要等到事务结束时才释放，这个就是两阶段锁协议。

死锁和死锁检测

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

当出现死锁时，可采用两种策略：

一是直接进入等待，直到超时，可通过设置参数innodb_lock_wait_timeout来设置超时时间,在innodb中默认是50s，这意味着当出现死锁后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行，这个时间往往是无法接受的。但设置太短的话有可能会出现误伤，因为有可能不是死锁，而是锁等待。

二是发起死锁检测，主动回滚死锁链条中的某一个事务，让其他事务的以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个检测。但如果是多个事务更新同一行，都是去判断是否是死锁，复杂度为O(n),n越大，检测级别就越大，这是cpu利用率就会很高，但每秒却执行不了几个事务，性能就会下降，因此需要控制并发度，可在中间件时间，也可修改mysql源码，使线程排队。也可将一行改成逻辑上的多行来减少锁冲突。

当出现死锁时，可执行show engine innodb status命令查看输出信息

视图

在MySql中，有两个视图的概念：

一个是view。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是create view … ，它的查询方法与表一样。

另一个是InnoDB在实现MVCC时用到的一致性读视图，即consistent read view，用于支持RC（Read Committed ，读提交）和RR（Repeatable Read，可重复读）隔离级别的实现。它没有物理结构，作用是事务执行期间用来定义“我能看到什么数据”

快照

InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候想InnoDB的事务系统申请的，是按申请顺序严格递增的。而每行数据也可以有多个版本，每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时就得数据版本会被保留，并且在新的数据版本中能够有信息可以直接拿到它。

InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID，所谓活跃指的就是启动了但没提交。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1，记为高水位。视图和高水位，组成了当前事务的一致性视图。而数据版本的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的。

也因此，视图数组把所有的row trx_id分成了集中不同的情况：

• 如果落在绿色部分，表示这个版本是已提交的事务或者市当前事务自己生成的，这个数据是可见的；
• 如果落在红色部分，表示这个版本是有将来启动的事务生成的，是肯定不可见的；
• 如果落在黄色部分，且row trx_id在数组中，表示这个版本是由还没提交的事务生成的，是不可见的，否则表示这个办事是已经提交了的事务生成的，是可见的。

因此，InnoDB利用了”所欲数据都有多个版本“的特性，实现了”秒级创建快照“的能力。

更新数据都是先读后写的，而这个读，只能读当前的值，称为当前读，如果select语句加了锁，也是当前读

redo log和change buffer

redo log主要节省的随机写磁盘的io消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的io消耗。

语句在某一时刻执行慢的原因

1. redo log写满了，需要flush 脏页，脏页即内存数据和磁盘数据页内容不一致，需尽量避免，因为此时系统不能再更新了，更新操作会堵塞
2. 内存满了，需要先将脏页写到磁盘，常态
3. mysql认为系统“空闲”，在请求活跃的情况，需见缝插针的flush脏页
4. mysql正常关闭的时候，会把内存的脏页都flush到磁盘上，这样在下次启动是，可以直接从磁盘上读数据，启动速度会加快

InnoDB刷脏页的控制策略

设置innodb_io_capacity这个参数来告诉InnoDB所在主机的io能力，才能够刷多快。如果某磁盘的读写能力很快，但设置的innodb_io_capacity却很低，InnoDB就会认为这个系统的io能力就这么查，所以刷脏页会特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页的累积，影响了查询和更新性能。而且innodb_io_capacity也不能设置的很满，因为除了刷脏页之外，还需要服务用户请求，所以InnoDB的刷盘速度主要参考脏页比列和redo log写盘速度，会根据这两个树单独算出两个数字。

设置innodb_flush_neighbors来控制是否需要刷掉连续相邻的脏页，1是，0否，自己刷自己，mysql8.0中，默认是0

为什么表数据删掉一半，表文件大小不变

mysql8.0之前是将表结构存在以.frm为后缀的文件里，8.0版本是放在系统数据表中，因为表结构定义占用的空间很小。

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：

为off表示的是表的数据放在系统共享表空间，也就是跟数据字典放在一起

为on表示的是每个InnoDB表数据存储在一个以.ibd为后缀的文件中。

从mysql5.6.6开始，一直默认为on，且应该都设置为on。因为一个表单独存储为一个文件更容易管理，而且在不需要这个表的时候，使用drop table命令，系统就会直接删除这个文件，而如果方法共享表空间中，即使表删除了，空间也是不会回收的。

由于InnoDB是通过B+树来组织数据的，当删掉某条已一条记录，并在该位置新增一条记录时，InnoDB可能会复用这个位置，且磁盘文件的大小并不会缩小，同样的，当删掉整个数据页，也会复用。但记录的复用跟数据页的复用是不同的，记录的复用，值只限于符合范围条件的数据，而当整个也从b+树里面摘掉后，可以复用到任何位置，所以当整个表删除后，所有的数据页都会被标记为可复用的，磁盘上文件不会变小，也因此，delete命令只是把删除的记录标记为可复用，磁盘文件的大小不会变，这些被标记为可复用，但没有被使用的空间，看起来像是“空洞”。空洞的造成也可能是不按照索引顺序随机插入的，这造成了索引的数据页分裂，更新索引上的值也会造成空洞

为了收缩空洞，需要重建表，构造一个结构相同的表，然后按照转id递增的顺序从旧表中一行一行的读出来插入到新表中，mysql5.5之前可通过alter table 旧表名 engine=InnoDB命令来重建表，会自动的完成转存数据，交换表名，删除旧表的操作，这个过程可能会造成数据丢失，因为旧表有可能插入新的数据。5.6版本开始引入了online DDL，采用临时表，且允许对旧表做增删改操作，虽然DDL之前是要拿到MDL写锁的，但是这个写锁在真正拷贝数据之前就退化成读锁了，退化是为了实现Online，MDL读锁不会阻塞增删改操作。不干脆解锁的原因是为了保护自己，禁止其他线程对这个表同时做DDL

对于大表来说很耗io和cpu资源，建议使用GitHub开源的gh-ost来做

count(*)

不同的mysql引擎，其count(*)的是实现方式不一样。MyISAM引擎把一个表的总行数存在了磁盘上，因此其count的效率很高，对没加where条件来说；而InnoDB就比较麻烦，它执行count的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

为什么InnoDB不跟MyISAM一样，把数字存起来？这是因为即使是同一时刻的多个查询，由于多版本并发控制的原因，且InnoDB默认的隔离级别是可重复读，InnoDB表“应该返回多少行”也是不确定的，只能把数据一行一行地读出以此判断，可见的行才能用于计算基于该查询的表的总行数

show table status可查看表的行数，但是不准确

可使用缓存系统保存计数，但有可能会丢失更新，因为redis数据也是存在内存中的，即使写入到磁盘中，也会因为刚插入一条记录，redis+1，此时redis刚好异常重启，即使读取了磁盘，也会丢失了+1的操作。可以在redis异常重启后再重新count(*)获取真实行再存

count(字段)<count(主键 id)<count(1)≈count()，所以我建议你，尽量使用 count(*)。

日志

Mysql能够实现主从复制，出久华，回滚，其关键在于binlog、redo log、undo log这三个日志

binlog

用于记录数据库表结构和表数据变更（增删改、create、truncate等）的二进制日志

可使用mysqlbinlog查看binlog日志信息

可使用show variables like ‘%log_bin%’;命令查看binlog是否打开

在my.cnf中配置binlog，修改后重启即可

# 打开binlog
log-bin=mysql-bin
# 选择ROW(行)模式
binlog-format=ROW

执行show master status;命令可查看当前写入的binlog文件名

mysqlbinlog中有个参数 stop-datetime可用于定位哪个时间的binlog

作用

可用于主从复制。一般在公司中做一主二从的结构是，就需要master节点打开binlog日志，从机订阅binlog日志的信息，因为binlog日志记录了数据库数据的变更，所以当master发生数据变更是，从机也能随着master节点的数据变更而变更，做到主从复制的效果。

可用于数据恢复。因为binlog记录了数据库的变更，所以可以用于数据恢复。Position参数是用于记录binlog日志的执政。当我们需要恢复数据时，只要指定—start-position，挥着指定—start-datetime和—stop-datetime，就可以恢复指定区间的数据

binlog的三种格式

参数配置 binlog-format = *，可通过show binlog events in ‘’；查看binlog中的内容

statement

此时binlog记录的就是SQl语句的原文

如果执行的是一条delete语句，且该语句中带有limit，参数，就会有一个warning，很可能会出现主备数据不一致的情况，即主库和备库中执行相同语句，所使用的的索引时不同的，mysql会认为这样是有风险的。

row

此时binlog记录的是执行事件event，Table_map event，用于说明接下来的操作的表；Delete_rows event，用于定义删除的行为。

且记录的是操作行的主键id吗，这样binlog传到备库的时候，肯定会操作主键值的行，不会有主备数据不一致的问题。

可通过myqlbinlog工具解析binlog日志，然后将解析结果整个mysql执行

mixed

由于有些statement格式的binlog可能会导致主备不一致，所以需要row格式。但row格式的缺点是很占用空间，比如用一个delete语句删除10w行数据，用statement的话就是一个sql语句被记录到binlog中，占用几十个字节的空间。但如果用row格式的binlog，就要吧这10w条记录都写到binlog中，这样子，不仅会占用更大的空间，同时写binlog也要消耗IO资源，影响执行速度。

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

但现在很多情况并没有用到mixed，而是row，这么做的理由有很多，比如恢复数据：

执行delete时，row格式的binlog也会把被删掉的行的整行信息保存起来，所以，如果在执行完delete时发现错误，可直接把binlog中记录的delete语句转成insert，把被错删的数据插入回去就可以了。同理，insert语句也是一样的，将insert转成delete。如果是update，只需要把这个event前后的两行信息对调一下，再去数据库里面执行，就能恢复这个更新操作。MariaDB的Flashback工具就是基于此原理来回滚数据的。

redo log

为了避免将内存数据修改成功后发生宕机而没有及时刷到磁盘中的情况。mysql是支持持久化的，所以最终结果都会更新到磁盘中。至于为什么一开始不直接保存到磁盘中，是由于mysql是使用页作为存储结构，更新时会先把这条记录所在的也加载到内存中，然后对记录进行修改。

更新完后会先写入到redo log buffer中（因为数据是在内存中更新的），再由redo log buffer写入到redo log file中，此时redo log file记录这xxx页做了xxx修改。

所以，即使mysql宕机了，也可以通过redo log进行数据恢复

在单独做增删改的时候，InnoDB会自己启动一个事务，在语句执行完成的时候提交，而真正在redo log file文件也是在提交后完成的。文件名为ib_logfile+数字

undo log

主要用于回滚，mysql数据库的事务的原子性就是通过undo log实现的，即对数据库的一系列操作，要么全部成功，要么全部失败。

undo log主要存储的是数据的逻辑变化日志，比如insert一条记录，那么undo log就会生成一条对应的delete日志。简单点说，就是undo log记录的是数据修改之前的数据，因为需要支持回滚。

undo log也可以实现多版本控制（MVCC），undo log记录中包含了记录更改前的镜像，如果更改数据的事务未提交，对于隔离级别大于等于read commit的事务而言，不应该返回更改后的数据，而应该返回老版本的数据。

binlog和redo log相关问题

事务在两阶段提交的不同时刻，mysql发生异常重启会出现什么现象，如何处理的。

当在写入redo log之后，写binlog之前发生了crash，由于此时binlog还没写，redo log也没有提交，所以崩溃恢复的时候，这个事务就会回滚，而且binlog还没写，也就不会传到备库。

当binlog写完后，但redo log还没提交，此时发生crash：

如果redo log里面的事务是完整的，也就是已经有了commit表示，则直接提交；

如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并且完整，如果是，则提交事务；否则回滚

Mysql是如何确认binlog是否完整

一个事务的binlog的完整格式：statement格式的binlog，最后会有COMMIT；row格式的binlog，最后会有一个XIDevent。Mysql5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志，由于磁盘原因，可能会在日志中间出错的情况，Mysql可以通过验证checksum的结果来发现。

redo log和binlog的关联性

redo log和binlog是通过一个共同的数据字段：XID进行关联的。崩溃恢复的时候，会按顺序扫描redo log：

如果碰到既有prepare、又有commit的redo log，就直接提交；

如果碰到只有prepare，而没有commit的redo log，就拿着XID去binlog找对应的事务。

保证主从库的数据一致性

只用binlog，而不用redo log的话，由于InnoDB并不是Mysql原生的存储引擎，且MyISAM设计指出本就没有崩溃恢复的功能，虽然InnoDB支持崩溃恢复和事务，但也只有redo log有崩溃恢复的能力，而binlog没有，因为binlog没有能力恢复数据页

反过来不使用binlog，只使用redo log的话，只从崩溃恢复上来讲是可行的，虽然此时没有了两阶段提交，但系统依然是crash-safe的。从生产环境上可以发现，binlog一直是开着的，binlog有这redo log无法替代的功能，一是归档，由于redo log是循环写的，因此当redo log满的时候，会从头开始写入，也就没办法保存历史记录，而binlog的作用之一就体现在这，可以保存这些历史记录；二是Mysql依赖于binlog，binlog作为Mysql一开始就有的功能，被用在了很多地方，比如Mysql的高可用基础，就是binlog复制，还有就是许多异构系统需要靠消费binlog来更新自己的数据

存储引擎

InnoDB

InnoDB使用的是WAL技术，所谓WAL技术也就是数据库系统提供原子性和持久化的一系列技术

执行事务时，会先写完内存和日志，此时事务就算完成了。之后系统崩溃后，可依赖于日志来恢复数据页

B+树结构

order by的工作原理

通过explain查看order by语句的执行情况，其中Extra字段中的Using filesort表示的就是需要排序。Mysql会给每个线程分配一块内存用于排序，称为sort_buffer。

排序的动作可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。sort_buffer_size就是Mysql为排序开辟的内存的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成，但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，而且外部排序一般使用的是归并排序，因此会生成多个临时文件

虽然只对原表的数据读了一边，剩下的操作都是在sort_buff或者临时文件中执行的，但当查询返回的字段很多的时候，sort_buffer能够同时放下的行数就少了，要分成很多歌临时文件，排序的性能会很差，且单行如果过大，这个方法的效率就不好。需要修改max_length_for_sort_data参数，这个参数是Mysql专门控制用于排序的行数据的长度的一个参数，意思是乳沟单行的长度超过设置的值，Mysql就认为单行太大，要换一个算法，此时sort_buffer会记录排序值和主键值，取完后排序，在按照主键值回到原表中取出需要的字段值生成结果集返回给客户端

随机获取多行记录

order by rand() limit 3;随机排序后去前3条记录

Sql查询慢的原因

数据库本身压力大，导致数据库服务器CPU占用率高或者IO利用率高

有可能是表被锁住了，可执行show processlist命令查看当前语句状态，如果state字段中出现Waiting for table metadata lock就表示，现在有一个线程正在表t上请求或者持有MDL写锁。可通过设置performance_schema=on，但会有10%左右的性能损失，然后查看sys.schema_table_lock_waits这张表的阻塞ID，通过kill命令杀死，会自动回滚写的操作。

也有可能是某个线程正在flush，即有线程正在执行flush tables [表名] with read lock;如果不写表名，则表示关闭Mysql里所有打开的表，但这个命令执行会很快，也因此代表它也被被的语句堵住了，然后它又堵住了select

等行锁，针对某条记录进行查询的回加读锁，此时该条记录正在被其他线程持有写锁，查询也会被堵住，mysql5.7以上版本可通过查看sys.Innodb_lock_waits表查到是谁占用了写锁

没有索引，走了全表扫描，当数据量大了，执行时间就线性涨上去了

带lock in share mode和不带的影响，当不带的时候，是一致性读，当查询开启事务后，另外一个事务正在更新语句，就会产生很多回滚日志，一致性读会依次执行undo log，最后才将结果返回

当查询参数的长度大于了字段定义的长度，Mysql会自动截取定义的最长长度进行匹配，而且可能由于是select * ，查询到多少条记录就会做多少次回表，每次回表查出的记录在到server层判断字段值与参数值不符合，返回空，所以执行时间会很慢。

幻读

指的是一个事务在前后两次查询同一范围时，看到了前一次查询没有看到的行。

在可重复读的隔离级别下，普通的查询是快照度，是不会看到别的事务插入的数据的，因此，幻读在当前读下才会出现，且专指新插入的行

查询语句加入了 for update，表示该查询是当前读，而当前读就是要能读到所有已经提交的记录的最新值

解决幻读

幻读的产生原因是：行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB只好引入新的锁，也就是间隙锁（Gap Lock）

所谓间隙锁，锁的就是两个值之间的空隙，这样使用for update加了行锁后，还同时加入了间隙锁，这样可以确保无法再插入新的记录。间隙锁和行锁是不会产生冲突的，两锁合并后称为next-key lock，是前开后闭的区间

加锁规则

两个原则，两个优化和一个“bug”：

原则1：加锁的基本大卫是next-key lock ，前开后闭

原则2：查找过程访问到的对象才会加锁

优化1：索引上的等值查询，给唯一索引加锁的时候，next-key lock退化为行锁

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

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

系统上线后，Mysql临时提高性能的方法

短链接风暴

正常的短连接模式就是连接到数据库后，执行很少的Sql语句局断开，下次需要的时候在重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

Mysql建立连接的过程成本是很高的，除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限，虽然在数据库压力比较小的时候，短连接造成的额外成本并不明显，但是，短连接模型还是会存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。

max_connections参数，用来控制一个Mysql实例同事存在的连接数上线，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”，而这，从业务的角度来看就是数据库不可用了。当机器负载较高的时候，处理现有请求的时间会变长，每个连接保持的时间也会跟着变长，这时，再有新的连接进来的话，就有可能超过max_connections的限制

虽然可通过调高max_connections，但这样做是有风险的，因为其设置参数的目的是想保护Mysql，如果改的太大，连接数增加，会造成系统负载过大，大量的资源耗费在权限验证等逻辑上，结果可能适得其反，已经连接的线程有可能拿不到CPU资源去执行业务请求。

通过处理那些占着连接但不工作的线程，可通过设置wait_timeout参数，将线程空闲超过多少秒后直接断开连接；也可主动kill掉那些在show processlist中显示为sleep的线程，但这个过程可能是有损的，因此，要kill掉的应该是事务外的空闲线程，可通过查information_schema库的innodb_trx表查看哪个线程处于事务中，命令为 kill connection+空闲事务id，而且从数据库主动断开连接，客户端是不知道的，只有等下一次连接后才会接收到报错信息，因此是有损的。

慢查询性能问题

可能出现慢查询的情况：

索引没设计好；Sql语句没写好；Mysql选错了索引

当索引没设计好时，最高效的做法是alter table，当然对于生产环境也不可能一上来就alter，如果有主备库的话，先在备库中执行set sql_log_bin=off，也就是不写binlog，然后再执行alter table修改索引，完之后主备切换，备库变主库，主库变备库，更改完后，在切换后的备库再执行第一步的步骤，此方法是针对紧急处理时应对的方法。平时最好是gh-ost（伪装的备库），可从主备库拉取binlog

当语句没写好时，可通过增加query_rewrite规则，然后执行call query_rewrite使之生效，即查询重写

当选错索引时，应急的方案就是查询重写给语句加上force index

QPS突增问题

有时候由于业务突然出现高峰，或者应用程序bug，导致某个语句的QPS突然暴增，也可能导致Mysql压力过大，影响服务。此情况可以下掉出现bug的功能：

1. 由全新业务的bug导致的话，假设DB运维是比较规范的，也就是说白名单是一个个加的，这种情况下，如果能够确定业务方会下掉这个功能的话，可以先从数据库中把该白名单去掉
2. 如果该功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接，这样连接不成功，QPS就会变成0
3. 如果这个功能和主题功能是部署在一起的，那么只能通过处理语句来限制，这时，可以使用查询重写功能，把压力最大的Sql语句直接重写成 select 1 返回。这个方案的风险很高：如果别的功能里面也用到了这个Sql，会有误伤；由于许多业务并不是靠一个语句就能完成逻辑的，所以单独处理压力大的语句以select 1 返回的话，可能会导致后面的业务逻辑一起失败

方案1和2都需要依赖于规范的运维体系：虚拟化、白名单机制、业务账户分离等，由此可见更多的准备，往往意味着更稳定的系统

Mysql是如何保证数据不丢失的

只要redo log和binlog保证持久化到磁盘了，就能确保Mysql异常重启后数据的恢复。

binlog的写入机制

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

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入，这就设计到binlog cache的保存问题。系统给binlog cache分配了一片内存，每个线程一个，参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小，如果超过了这个大小，就要暂存到磁盘，事务提交的时候，执行器会把binlog cache里的完整事务写入到binlog中，并清空binlog cache，其中写入到binlog cache的才做是write，写入到binlog文件的操作是fsync，write和fsync的时机，是由参数sync_binlog控制的，为0，表示每次提交事务都只write，不fsync；为1，表示每次提交事务都会执行fsync；N大于1，表示每次提交事务都write，但积累N个事务后才fsync。因此出现io瓶颈时，也可设置较大的值，用以提升性能，其风险是事务个数没到N就发生异常重启，就会丢失这几个事务的binlog日志。

虽然每个线程都有自己的binlog cache，但最后都会写入到同一个binlog文件中。

redo log的写入机制

事务在执行过程中，生成的redo log是要先写到redo log buffer中

redo log buffer的三种可能状态：

1. 存在redo log buffer中，物理上是在MySql进程内存中
2. 写到磁盘(write)，但是没有持久化(fsync)，物理上是在文件系统的page cache里面
3. 持久化到磁盘，对应的是hard disk

日志写到redo log buffer是很快的，write到page cache也差不多，但持久化到磁盘的速度就慢多了，因此为了控制redo log的写入策略，InnoDB提供了innodb_flush_log_at_trx_commit参数：

• 为0时，表示每次事务提交时都知识吧redo log留在redo log buffer中；
• 为1时，表示每次事务提交时都将redo log直接持久化到磁盘；
• 为2时，表示每次事务提交时都只是把redo log写到page cache；

InnoDB有一个后台线程，每隔一秒，就会把redo log buffer中的日志，调用write写到文件系统的page cache，然后调用fsync持久化到磁盘（该操作是轮询的）。事务执行中间过程的redo log也是直接卸载redo log buffer中的，这些redo log也会被后台线程一起持久化到磁盘，也就是说，一个没有提交的事务的redo log，也是可能会被持久化到磁盘的

吃了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的redo log写入到磁盘中

一种是，redo log buffer占用的空间即将达到innode_log_buffer_size一半的时候，后台线程会主动写盘，由于这个事务并没有提交，所以这个写盘动作知识write，而没有调用fsync，也就是只留在文件系统的page cache；

另外一种是，并行的事务提交的时候，顺带将另外一个事务的redo log buffer持久化到磁盘。假设一个事务A执行到一半，已经写了一些redo log到buffer中，这时候有另外一线程的事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么按照这个参数的逻辑，事务B要吧redo log buffer里的日志全部持久化到磁盘，这是就会带上事务A在redo log buffer里的日志一起持久化到磁盘。

时序上redo log先prepare，再写binlog，最后再把redo log commit。如果把innodb_flush_log_at_trx_commit设置为1，那么redo log在prepare阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于prepare的redo log，再加上binlog来恢复。每次后台轮询刷盘，再加上奔溃恢复逻辑，InnoDB就认为redo log在commit的时候就不需要fscny，只会write到文件系统的page cache中就足够了

通常我们说的Mysql的“双1”配置，指的就是sync_binlog和innodb_flush_log_at_trx_commit都设置为1，也就是说，一个事务完整提交前，需要等待两次刷盘，一次是redo log（prepare阶段），一次是binlog。

业务高峰期、皮联防导入数据等场景不应该用双1

组提交机制

双1配置的两次刷盘，如果Mysql的TPS是每秒liangw的话，每秒就会写四万次盘，但磁盘能力也就只有两万的话，怎么就能实现两万的TPS？

涉及到日志逻辑序列号(LNS)的概念，LNS是单调递增的，用来对应redo log的一个个写入点。每次写入长度为length的redo log，LSN的值都会加上length。LNS也会写到InnoDB的数据页，来确保数据页不会被多次执行重复的redo log。

当多个并发事务在prepare阶段都写完了redo log buffer后，哪个事务最先到达的就会被选为这组的leader，等leader要去写盘的时候，就会带上这组事务中最大的LSN，等leader返回是，所有小于等于该LSN的redo log都已经被持久化到磁盘，这是其他事务就可以直接返回了。

所以，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。但如果只有单线程压测，那就只能老老实实地一个事务对应一次持久化操作了。在并发更新场景下，第一个事务写完redo log buffer以后，接下来这个fsync越晚调用，组员可能越多，节约IOPS的效果越好。为了让一次fsync带的组员更多，Mysql做了一个优化：拖时间，在两阶段提交的时候，binlog虽然是一个动作，但却是分两步走的：先把binlog从binlog cache中写到磁盘上的binlog文件，在调用fsync持久化。Mysql为了让组提交的效果更好，把redo log做fsync的时间拖到了commit之前：

这样一来，binlog也可以组提交了，不过通过步骤3执行会很快，所以binlog只有少部分会被一起持久化，如果想提升binlog组提交的效果，可通过设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count来实现，第一个参数表示延迟多少微秒后才调用fsync，第二个参数表示积累多少次以后才调用fsync，且两者是或的关系，只要满足一个就会调用fsync，虽然可能会增加语句的响应时间，但没有丢失数据的风险。

Mysql主备一致原理

主要是通过binlog去执行相关的sql，需要确保主库与备库之间执行相同的binlog

双M结构，即节点与节点之间互为主备关系，这种关系有可能会出现循环执行的情况，为避免这种情况，可根据不同的库设置不同的server id，且另外的库拿到主库后生成的binlog的server id需与主库的server id一致

主备切换流程

在状态1中，客户端的读写都直接访问节点A，而节点B是A的备库，只是将A的更新都同步过来到本地执行，这样可以保持节点B和A的数据都是相同的。当需要切换的时候，就会切成状态2，这时哭护短读写访问的都是节点B，而节点A是B的备库。

在状态1中，虽然节点B没有被直接访问，但还是建议将B节点设为只读（readonly）模式，主要有一下几个考虑：

• 有时候一些运营类的查询语句会被放到备库上去查，设置为只读可以防止误操作；
• 防止切换逻辑有bug，比如切换过程中初出现双写，造成主备不一致；
• 可以用readonly状态，来判断节点的角色

readonly的设置对超级权限用户是无效的，而用于同步更新的线程，也只有超级权限才能拥有，因此设置只读模式不会对主库同步更新有影响。

1. 备库跟主库之间维持了一个长连接，主库内部有一个线程，专门用于服务备库的长连接，主库在做更新操作的时候也会写入日志，事务日志同步的完整过程：
2. 在备库上通过change master命令，设置主库的IP、端口、用户名、密码，以及要从哪个位置开始请求binlog，这个位置包含文件名和日志偏移量；
3. 在备库上执行start slave命令，这时候备库会启动两个线程，即io_thread和sql_thread，其中io负责与主库建立连接；
4. 主库校验完用户名、密码后，开始按照备库传过来的位置，从本地读取binlog，发送给备库；
5. 备库拿到binlog后，写到本地文件，称为中转日志（relay log）；
6. sql_thread读取中转日志，解析出日志里的命令，并执行。

后来由于多线程复制方案的引入，sql_thread演化成为了多个线程

主备延迟

就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值，每个事务的binlog里面都有一个时间字段，用于记录主库上写入的时间，备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，就会得到seconds_behind_master。

可在备库上执行show slave status命令，返回结果里面会显示seconds_behind_master，用于表示当前备库延迟了多少秒。

备库连接到主句的时候，会通过执行SELECT UNIX_TIMESTAMP()函数来获得当前主库的系统时间，如果这时发现主库的系统时间与备库自己的系统时间不一致，备库再执行seconds_behind_master计算的时候会自动扣掉这个差值，以此来保证主备延迟的值的准确性

主备延迟的来源

备库所在的机器的性能比主库的机器的性能差。由于更新过程中会触发大量的读操作，当所有备库集中在一台机器上时，会造成多个备库都在争抢资源，导致了主备延迟。现在都会做主备切换，因此机器性能会相同，称为对称部署

备库的压力大。当备库上的查询耗费了大量的cpu，影响了同步速度，造成主备延迟。可以设置一主多从，用以分担备库的读压力；也可将binlog输出到外部系统，比如hadoop等，让这些外部系统提供统计类查询的能力

大事务。由于主库上必须等事务执行完成才会写入binlog，再传给备库，所以，当一个主库上的语句执行多长时间，那这个事务很可能就会导致从库延迟多长时间。比如一次性地用delete删除太多数据；大表DDL等，这些都是大事务场景

备库的并行复制能力

主备切换时的策略

由于主备延迟的存在

可靠性优先策略

主备切换过程中石油不可用时间的，即主库与备库都处于readonly(只读)状态时，系统不可写时，在这个状态中，当备库判断延迟时间是否处于0时是比较耗时的，而可靠性优先策略会事先在主库编程只读状态前，判断备库的延迟时间的值是否小于某个值，这个值是可用性优先策略决定的，只有当小于这个只时，主库才会变为只读状态，即把readonly设置为true

可用性优先策略

如果直接将连接切到备库，不等主备数据同步，这样就几乎没有不可用时间，事后再补数据，但带来的代价就是可能会出现数据不一致的情况。

主备并行复制

备库会将sql-thread拆分成一个coordinator和多个worker线程，coordinator只负责读取中转日志和分发事务，真正的更新日志变成了worker线程。worker线程的个数，有参数slave_parallel_workers决定的，对于32核物理机的话设置为8~16之间是最好的。

coordinator在分发的时候，需满足一下两个基本要求：

• 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个worker线程中。由于CPU的调度策略，执行的事务的顺序可能会相反，这样会导致主备数据不一致问题
• 同一个事务不能被拆开，必须放到同一个worker线程中。虽然最终能保证主备一致，但如果当某个更新事务完成后，还有一个查询事务的话，mysql会认为这个事务只更新了一半，破坏了事务逻辑的隔离性

5.6之前的并行复制策略

Mysql5.6之前的版本是不支持并行复制的，但可以自己写策略

按表分发策略

按表分发事务的基本思路是，如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证worker与worker之间不会更新同一行。每个worker对应一个hash表，用于保存当前正在这个worker的执行队列里的事务所涉及的表，hash表的ket是库名.表名，value是一个数字，表示队列中有多少个事务修改这个表

按行分发策略

主要目的是解决热点表的并行复制问题，因为对于热点表，按表分发策略是行不通的，当所示的更新事务都涉及到同一个表时，所有事务都会被分配到用一个worker中，这样就编程了单线程复制了。按行的核心思路就是：如果两个事务没有更新相同的行，它们在备库上可以并行执行，这个模式要求binlog格式必须是row。按行复制和按表复制的数据结构差不多，也是为每个worker分配一个hash表

这样做的缺陷是比较耗费内存和CPU

5.6的并行复制策略

5.6版本虽然支持了并行复制，但支持的粒度是按库并行的，即hash表中的key是数据库名，其效果取决于压力模型，如果在主库上有多个DB，并且各个DB的压力均衡，使用这个策略的效果会很好，相当于按表按行分发，其key仅仅为库名，少了几个字节，且不要求binlog格式，因为即使是statement，也很容易拿到库名。但如果所有的表都放在同一个DB中，这个策略就没有效果了

由于需要特地移动数据，这个策略用得并不多

MariaDb的并行复制策略

利用的是组提交的特性：

• 能够在同一组里提交的事务，一定不会修改同一行；
• 主库上可以并行执行的事务，备库上也一定是可以并行执行的

实现：

1. 在一组里面一起提交的事务，有一个相同的commit_id,下一组就是commit_id+1
2. commit_id直接写到binlog里面
3. 传到备库应用的时候，相同commit_id的事务分发到多个worker执行
4. 这一组全部执行万恒后，coordinator再去取下一批

目标是模拟主库的并行模式，并没有真正实现，在主库中，一组事务在commit的时候，下一组事务是同事处于执行中状态的

5.7的并行复制策略

由参数slave-parallel-type来控制并行复制策略：

配置为DATABASE，表示使用5.6版本的安库并行策略

配置为LOGICAL_CLOCK，表示的就是类似MariaDb的策略，不过5.7这个策略，针对并行度做了优化。同时处于执行状态的所有事务，是不能并行的，因为这里面可能有由于锁冲突而处于锁等待状态的事务，若果这些事务在备库上被分配到不同的worker，就会出现备库跟主库不一致的情况，而MariaDb策略是所有处于commit状态的事务可以并行，这表示通过了锁冲突的校验，而5.7的思想是：

• 同事处于prepare状态的事务，在备库执行是可以并行的；
• 处于prepare状态的事务，与处于commit状态的事务之间，在备库执行是也是可以并行的

可通过设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count来制造更多处于prepare的事务，以此来增加备库复制的并行度，是故意让主库提交慢些，让备库执行快些

5.7.22的并行复制策略

基于WRITWSET的并行复制，相应的增加了一个参数binlog-transaction-dependency-tracking，用来控制是否用这个新策略，三种选值：

• COMMIT_ORDER，表示的就是前面介绍的，根据同事进入prepare和commit来判断是否可以进行并行策略；
• WRITWSET，表示的是对于事务设计更新的每一行，计算出这一行的hash值，组成集合writeset。如果两个事务没有操作相同的行，也就是说它们的writeset没有交集，就可以并行
• WRITWSET_SESSION，是在WRITWSET的基础上多了一个约束，即在主库上同一个线程先后执行的两个事务，在备库执行的时候，要保证相同的先后顺序。

为了保证唯一标识，这个hash值是通过“库名+表名+索引名+值”计算出来的。如果一个表上除了有主键索引外，还有其他唯一索引，那么对于每个唯一索引，inset语句对应的writeset就要多增加一个hash值。

优势

writeset是在主库生成后直接写入到binlog里面的，这样在备库执行的时候，不需要解析binlog内容(event里的行数据)，节省了很多计算量

不需要把整个事务的binlog都扫一遍才能决定分发到那个worker，更省内容

由于备库的分发策略不依赖于binlog内容，所以binlog是statement格式也是可以的

劣势

对于表上没主见和外键约束的场景，WRITWSET策略是没办法进行的，也会暂时退化为单线程模型

mysql高可用

除了binlog保证最终一致性之外，还需要

一主多从

A和A‘互为主备关系，从库B、C、D指向主库A。一主多从的设计，一般用于读写分离，主库负责所有的写入和一部分读，其他的读请求则有从库分担。

主库故障时，主备切换的结果

当主库A发生故障后，A’会成为新的主库，从库B、C、D也要改接到A’。相比于一主一备，正是由于多了从库B、C、D重新指向的这个过程，所以主备切换的复杂性也响应增加了

基于位点的主备切换

要将节点B设置成节点A’的从库的时候，需要执行一条change master命令：

CHANGE MASTER TO

MASTER_HOST=$host_name

MASTER_PORT=$port

MASTER_USER=$user_name

MASTER_PASSWORD=$password

MASTER_LOG_FILE=$master_log_name

MASTER_LOG_POS=$master_log_pos

六个参数从上往下分别为主库A’的ip、端口、用户名、密码，最后两个表示要从主库的master_log_name文件的master_log_pos这个位置的日志继续同步，而这个位置就是我们所说的同步位点，也就是主库对应的文件名和日志偏移量

因此，从库B要连接到主库A’就不可避免的要找A’的同步位点，因为A和A’的同步位点是不同的，但这个位点很难精确取到，只能去一个大概位置

GTID

全程是Global Transaction Identifier，也就是全局事务ID，是一个事务在提交的时候生成的，是这个事务的唯一标识。他由两部分组成，格式是：GTID=server_uuid:gon

其中server_uuid是一个实例第一次启动时自动生成的，是一个全局唯一的值；gno是一个证书，初始值是1，每次提交事务的时候分配给这个事务，并加1。

GTID模式的启动，只需要在启动一个Mysql实例的时候，加上参数gtid_mode=on和enforce_gtid_consistency=on就可以了。

在GTID模式下，每个事务都会跟一个GTID一一对应。这个GTID有两种生成方式，而使用哪种方式取决于session和gtid_next的值：

如果gtid_next=automatic，代表使用默认值。这时，Mysqk就会把server_uuid：gno分配给这个事务

如果gtid_next是一个指定的GTID的值，比如通过set gtid_next=’current_gtid’，且current_gtid已经存在于实例的GTID集合中，接下来执行的这个事务会直接被系统忽略；否则就将这个current_gtid分配给接下来要执行的事务，也就说系统不需要给这个事务生成新的GTID，因此gno也不用加1

在GTID模式下，备库要设置A’为新的主库，语法为：

CHANGE MASTER TO

MASTER_HOST=$host_name

MASTER_PORT=$port

MASTER_USER=$user_name

MASTER_PASSWORD=$password

master_auto_position=1

master_auto_position=1就表示这个主备关系使用的是GTID协议。可以看到已经不再使用上一个方式中最后的两个参数

当在备库执行start slave命令，取binlog时：

1. 备库指定主库，基于主备协议建立连接；
2. 备库把自己的GTID集合发送给主库；
3. 主库算出自己的GTID集合和备库的GTID集合的差集，也就是所有存在主库中，但不存在备库的GTID的集合，判断主库本地是否包含了这个差集需要的所有的binlog事务，如果不包含，表示主库已经把备库需要的binlog给删掉了，直接返回错误；否则，主库从自己的binlog文件里面，找出第一个不在备库的GTID集合的事务发送给备库
4. 之后就从这个事务开始，往后读取文件，按顺序去binlog发送给备库去执行

与基于位点协议由备库决定不同的是，是有主库在内部实现的

Mysql通过binlog文件头部的Previous_gtids可以快速定位到binlog中的某一个GTID位置

读写分离

读写分离的主要目标是分摊主库的压力

由Proxy根据类型和上下文决定请求的分发路由，也可通过负载均衡由客户端自己选择后端数据库进行查询

客户端直连与带proxy转发的特点

客户端直连方案，因为少了一层proxy转发，所以查询性能稍微好一点，并且整体架构简单，排查问题更方便。但这种方案，由于要了解后端部署细节，所以在出现主备切换、库迁移等操作的时候，客户端都会感知到，并且需要调整数据库连接信息。这种架构一般会伴随一个负责管理后端的组件，比如Zookeeper，尽量让业务端值专注于业务逻辑开发

带proxy的架构，对于客户端比较友好。客户端不需要关注后端细节，连接维护、后端信息维护等工作，都是由proxy完成的。但这样的话，对后端维护团队的要求会更高。而且，proxy也需要有高可用架构。因此，带proxy架构的整体就相对比较复杂。

无论哪种架构，都有可能碰到由于主从可能存在延迟，客户端执行完一个更新事务后马上发起查询，如果查询选择的是从库的话，就有可能读到刚刚的事务更新之前的状态，这种在从库上读到系统的一个过期状态的现在，称之为过期读

过期读解决方案

强制走主库方案

将查询请求做分类：

对于必须要拿到最新结果的请求，强制将其发到主库上。比如，在一个交易平台上，卖家发布商品以后，马上要返回主页面，看商品是否发布成功。那么，这个请求需要拿到最新的结果，就必须走主库

对于可以读到旧数据的请求，才将其发到从库上。在这个交易平台上，买家来逛商铺页面，就算晚几秒看到最新发布的商品，也是可以接受的，那么，这类骑牛就可以走从库

Sleep方案

主库更新后，读从库之前先sleep一下。具体方案就是类似于执行一条select sleep(1)命令。这个方案的假设是，大多数情况下主备延迟在1秒之内，做一个sleep可以有很大概率拿到最新的数据

判断主备无延迟方案

要确保备库无延迟，通常有三种做法：

每次从库执行查询前，先判断seconds_behind_master是否已经等于0.如果还不等于0，那就必须等到这个参数变为0才能执行查询请求。

对比位点确保主备无延迟。通过show slave status命令查看，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集合确保主备无延迟。通过show slave status命令查看，Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。如果这两个集合相同，也表示备库接收到的日志都已经同步完成

semi-sync

半同步复制，设计思想：

1. 事务提交的时候，主库吧binlog发给从库；
2. 从库收到binlog以后，发回给主库一个ack，表示收到了；
3. 主库收到这个ack以后，才能给客户端返回事务完成的确认；

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

semi-sync配合关于位点的判断，就能够确定在从库上执行的查询骑牛，可以避免过期读。但这种情况，只对一主一备的场景下。如果是一主多从的话，主库只要等到一个从库的ack，就开始给客户端返回确认，这时，在从库上执行查询请求，就有两种情况：如果查询是落在这个响应了ack的从库，是能确保读到最新数据；但如果是查询落在其他从库上，它们可能还没有收到最新的日志，就会产生过期度的问题

等主库位点方案

GTID方案

数据库是否出问题

判断数据库是否有问题，可通过：

select 1判断

select 1判断，但实际上select 1成功返回，只能说明这个库的进程还在，并不能说明数据库没有问题。参数 innodb_thread_concurrency 可控制InnoDB的并发线程上限，当达到这个值后，新接收到的请求就会进入等待，直到有线程退出。当有线程堵住的时候，用select 1是检查不出问题的。innodb_thread_concurrency 设置为0，表示不限制并发线程数，但这肯定是不行的，因为一个机器的CPU核数是有限的，线程全冲进来，上下文切换的成本太高。通常设置为64~128之间，此外还需理解并发连接和并发查询的概念，show processlist的结果中看到的连接指的就是并发连接，而当前正在执行的语句才是我们所说的并发查询，并发连接数达到几千个影响并不大，就是多占用一些内存而已，而并发查询太高才是CPU杀手，实际上，在线程进入锁等待以后，并发线程的技术会减一，也就是锁等行锁（也包括间隙锁）的线程是不算在128里面的，这样设计的目的是进入锁等待的线程不迟CPU

查表判断

为了能够检测InnoDB并发线程数过多导致的系统不可用的情况

更新事务要写binlog，而一旦binlog所在磁盘的空间占用率达到100%，那么所有的更新语句和事务提交的commit语句就都会被堵住。但，系统这时还是可以正常读数据的

更新判断

可添加一个字段，每次更新时记录该更新的时间

内部统计

Mysql5.6以后的版本都提供了performance_schema库，就在file_summary_by_event_name表里统计了每次IO请求的时间，然后通过MAX_TIMER的值来判断数据库是否出问题了

误删数据后的解决办法

误删行

即使用了delete语句误删了数据行，可通过FlashBack工具通过闪回把数据恢复回来。FlashBack恢复原理是修改binlog的内容，拿回原库重放。而能够使用这个方案的前提是，需要确保binlog_format=row和binlog_row_image=FULL

当然，不建议再主库执行FlashBack解析后的语句，恢复数据比较安全的做饭，是恢复出一个备份，或者找一个从库作为临时库，在这个临时库上执行这些操作，然后再将确认过的临时库的数据，恢复回主库。这样做是因为，一个在执行线上逻辑的主库，数据状态的变更往往是有关联的。可能由于发现数据问题的时间晚了一点，就导致已经在之前误操作的基础上，业务代码逻辑又继续修改了其他数据。所以，如果这时单独恢复这几行数据，而又未经确认的话，就可能会出现对数据的二次破坏

误删库/表

这种情况下，要想恢复数据，就需要使用全量备份，加增量日志的方式。这个方案要求线上有定期的全量备份，并且实时备份binlog

延迟复制备库

虽然可以通过利用并行复制来加速恢复数据的过程，但这个方案任然存在恢复时间不可控问题。如果一个库的备份特别大，或者误操作的时间距离上一个全量备份的时间较长，比如一周一备的实例，在备份之后的第六天发生误操作，那就需要恢复六天的日志，这个恢复时间可能是要按天来计算的。

不允许太长的恢复时间，可以考虑搭建延迟复制的备库，Mysql5.6引入的

一般的主备复制结构存在的问题是，如果主库上有个表被误删，这个命令很快也会被发给所有从库，进而导致所有从库的数据表也一起被误删了。

延迟复制的备库是一种特殊的备库，通过CHANGE MASTER TO MASTER_DELAY = N命令，可以指定这个备库连续保持跟主库有N秒的延迟。比如把N设置为3600，这就代表了如果主库上有数据被误删了，并且在1小时内发现了这个误操作命令，这个命令就还没有在这个延迟复制的备库执行。这时候到这个备库上执行stop slave，再通过之前介绍的方法，跳过误操作命令，就可以恢复出需要的数据。

预防误删库/表的方法

账号分离

目的是为了避免写错命令。只给业务开发人员DML权限，而不给truncate/drop权限。而如果业务开发人员有DDL需求的话，也可以通过开发管理系统得到支持。即使是DBA成员，日常也都规定只使用读账号，必要的时候才使用有更新权限的账户。

制定操作规范

目的是避免写错要删错的表名。在删除数据表之前，必须先对表做改名操作。然后，观察一点时间，确保对业务无影响以后在删除这张表。改表名的时候，要求给表名加固定的后缀，然后删除表的动作必须通过管理系统执行，并且管理系统删除表的时候，只能删除固定后缀的表。

rm删除数据

对于一个有高可用机制的MySql集群来说，最不怕的就是rm删除数据了，只要不是恶意的把整个集群删除，而知识删掉了其中某一个节点的数据的话，HA系统就会开始工作，选出一个新的主库，从而保证整个集群的正常工作。这时，只需要在这个节点上把数据恢复回来，再接入整个集群。面对批量下线机器的操作，建议是将备份跨机房，或者最好是跨城市保存。

kill命令

kill query + 线程id，表示终止这个线程正在执行的语句；

kill connection + 线程id，这里的connection可缺省，表示断开这个线程的连接，如果这个线程有语句正在执行，也会停止正在执行的语句的。

执行kill命令

收到kill命令后，并不是马上停止线程的意思，而是告诉线程，这条语句已经不需要继续执行了，可以开始执行停止的逻辑了，由于语句可能占有锁，此时必须要先释放锁，才能停止

kill query + 线程id：

把线程的运行状态改成THD::KILL_QUERY(将变量 killed 赋值为THD::KILL_QUERY)；然后给线程发一个信号。发信号的目的，是告诉线程来处理THD::KILL_QUERY状态

kill不掉的例子

show processlist中的Command列显示的是Killed

由于IO压力过大，读写IO的函数一直无法返回，导致不能即使判断线程的状态。

终止逻辑耗时较长。

关于客户端的两个误解

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

实际上，当使用默认参数连接的时候，Mysql客户端会提供一个本地库名和表名补全的功能，为了实现这个功能，客户端在连接成功后，需要多做一些操作：执行show databases；切到库，执行show tables；把这两个命令的结果用于构建一个本地的哈希表。在这些操作中，最花时间的就是第三步在本地构建哈希表的操作。所以，当一个库中的表个数非常多的时候，这一步就会花比较长的时间。也就是说，我们感知到的连接过程慢，其实并不是连接慢，也不是服务端慢，而是客户端慢。

解决办法就是在连接命令中加上-A，就可以关掉这个自动补全的功能，然后客户端就可以快速返回了。这里的自动补全的效果就是，在输入库名或者表名的时候，输入前缀，可以使用Tab键自动补全表名或者显示提示。除了加-a以外，还可以加-quick（或者简写为-q）参数，也可以跳过这个补全阶段。但是这个-q是一个更容易引起误会的参数，也是关于客户端常见的一个误解。设置这个参数，可能会降低服务端的性能。

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

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

Mysql客户端默认采用第一种方式，而如果加上-q参数，就会使用第二种不缓存的方式。采用不缓存的方式时，如果本地处理得慢，就会导致服务端发送结果被阻塞，因此会让服务端变慢。但设置-q可以达到让客户端变得更快：

• 跳过表名自动补全功能。
• mysql_store_result需要申请本地内存来缓存查询结果，如果查询结果太大，会耗费较多的本地内存，可能会影响客户端本地机器的性能。
• 设置之后是不会把执行命令记录到本地的命令历史文件

当查询的数据量大于内存时，会不会把内存撑爆

在做逻辑备份的时候，会对整库进行扫码，如果每次这样会把内存吃光，那么逻辑备份不是早就挂了？所以，对大表做全表扫描，看开应该是没问题的

全表扫描对server层的影响

当要把全表扫描的结果保存在哭护短时，会使用类似这样的命令：mysql -hpost -upwd -e “select * from db1.t” > $target_file

由于InnoDB的数据是保存在主键索引上的，所以全表扫描实际上是直接扫描表的主键索引。这条查询语句由于没有其他的判断条件，所以查到的每一行都可以直接放到结果集里面，然后返回给客户端。实际上，服务端并不需要保存一个完整的结果集。 取数据和发数据的流程是这样的：

1. 获取一行，写到net_buffer中。这块内存的大小是由参数net_buffer_length定义的，默认是16k
2. 重复获取行，知道net_buffer写满，调用网络接口发出去
3. 如果发送成功，就情况net_buffer，然后继续执行下一行，并写入net_buffer
4. 如果发送函数返回EAGAIN或WSAEWOILDBLOCK，就表示本地网络栈（socket send buffer）写满了，进入等待，直到网络栈重新科协，再继续发送

从此流程可以看到，一个查询在发送过程中，占用的Mysql内部的内存最大的就是net_buffer_length这么大，并不会达到查询的值，且socket send buffer也不可能达到几百G（默认定义/proc/sys/net/core/wmen_default），如果socket send buffer被写满，就会暂停读数据的流程。也就是说，Mysql是边读边发的。这就意味着，如果客户端接收得慢，会导致Mysql服务端由于结果发不出去，这个事务的执行时间就会变长。

对于正常的线上业务来说，如果一个查询的返回结果不会很多的话，建议使用mysql_store_result这个接口，直接把查询结果保存到本地内存。

如果维护的Mysql里看到很多歌线程都处于Sending to client这个状态，这意味着需要开发人员优化查询结果，并评估这么多的返回结果是否合理。如果要快速减少处于这个状态的线程的话，将net_buffer_length参数设置为一个更大的值是一个可选的方案。

与Sending to client长相类似的状态是Sending data，这是一个语句正在执行的状态。当查询语句进入执行阶段后，首先就会把状态设置为Sending data，然后发送执行结果的列相关的信息（meta data）给客户端，再继续执行语句的流程，执行完成后，把状态设置成空字符串。也就是说Sending data并不一定是指正在发送数据，而可能是处于执行器过程中的任意阶段，比如锁等待过程

仅当一个线程处于等待客户端接收结果的状态才会显示Sending to client；而如果线程成Sending data，就表示语句正在执行

查询的结果是分段发送给客户端的，因此全表扫墓爱，查询返回大量的数据，并不会把内存打爆

全表扫描对InnoDB的影响

InnoDB内存的一个作用是保存更新的结果，再配合redo log，这样就避免了随机写盘。内存的数据页是在Buffer Pool（BP）中管理的，在WAL里Buffer Pool起到了加速更新的作用。而实际上，Buffer Pool还有一个更重要的作用，就是加速查询。

由于有WAL机制，当事务提交的时候，磁盘上的数据页时旧的，那么如果这时候马上有一个查询要来读这个数据页，并不会马上把redo log应用到数据页，因为这时候内存数据页的结果是最新的，直接读内存页就可以了，也不需要读磁盘，直接从内存拿结果，速度是很快的。所以说BP还有加速查询的作用

而Buffer Pool对查询的加速效果，依赖于一种重要的指标，即：内存命中率。可以在show engine innodb status结果中查看一个系统当前的BP命中率（Buffer pool hit rate）。一般情况下，一个稳定服务的线上系统，要保证响应时间符合要求的话，内存命中率要在99%以上。如果所有查询需要的数据页都能够直接从内存中得到，那是最好的，对应的命中率就是100%，但这在实际生产是很难做到的。

InnoDB Buffer Pool的大小是由参数innodb_buffer_pool_size确定的额一般建议设置成可用物理内存的60%~80%。

InnoDB内存管理用的是最近最少使用的LRU算法，这个算法的核心就是淘汰最近未使用的数据。这个算法是用链表实现的：

图中state 1里，链表头部是P1，表示P1是最近刚刚被访问过的数据页；假设内存里只能放下这么多数据页，这时候有一个请求访问P3，状态会变成state 2，P3会被移到最前面。state 3表示，这次访问的数据页时不存在于链表中的，所以需要在Buffer Pool中新申请一个数据页Px，加到链表头部，但是由于内存已经满了，不能申请新的内存。于是会清空链表末尾Pm这个数据页的内存，存入Px的内容，然后放到链表头部。从效果上看，就是最久没有被访问的数据页Pm被淘汰了。

当要扫描一个几百G的表，而这个表是一个历史数据表，平时没有业务访问它。那么安装这个LRU算法扫描的话，就会把当前的Buffer Pool里的数据全部淘汰掉，存入扫描过程中访问到的数据页的内容。也就是说Buffer Pool里面主要放的是这个历史数据表的数据。对于一个正在做业务服务的库，这可不妙。这时当有业务请求时，会看到Buffer Pool的内存命中率急剧下降，磁盘压力增加，sql语句响应变慢。所以，InnoDB不能直接使用这个LRU算法，但实际上，InnoDB已经对LRU算法做了改进：

在InnoDB实现上，按照5:3的比例把整个LRU链表分成了young区域和old区域。图中LRU_old指向的就是old区域的一个位置，是整个链表的5/8处，也就是说，靠近链表头部的5/8是young区域，靠近链表尾部的3/8是old区域。此时，state1中要访问数据页P3，由于P3处于young区域，因此和优化前的LRU算法一样，将其移到链表头部，变成state2；之后要访问一个新的不存在于当前链表的数据页的时候，虽然依旧是淘汰掉数据页Pm，但是新插入的数据页Px，是放在LRU_old处。而处于old区域的数据页，每次被访问的时候都要做判断：若这个数据页在LRU链表中存在的时候超过了1秒，就把它移动到链表头部；否则位置保持不变。其中1秒的这个时间，是由参数innodb_old_blocks_time控制的。其默认值是1000，单位是毫秒。

这个策略就是为了处理类似全表扫描的操作量身定制的，改进后的LRU算法的操作逻辑：

1. 扫描过程中，需要新插入的数据页都被放到old区域；
2. 一个数据页里面有多条记录，这个数据页会被多次访问到，但由于是顺序扫描，这个数据页第一次按被访问和最后一次被访问的时间间隔不会超过1秒，因此还是会被保留在old区域；
3. 在继续扫描后续的数据，之前的这个数据页之后也不会再被访问到，于是始终没有机会移到链表头部，也就是young区域，很快就会被淘汰出去

可以看到，这个策略最大的收益，就是在扫描大表的过程中，虽然也用到了Buffer Pool，但是对young区域完全没有影响，从而保证了Buffer Pool响应正常业务的查询命中率

JOIN到底该不该使用

视情况而定
通过EXPLAIN分析语句，查看Extra字段使用了哪种算法
如果是Index Nested-Loop Join 算法，也就是说用上了被驱动表的索引，是可以使用join的
如果是 Block Nested-Loop Join 算法，就尽量不要使用，因为扫描行数会过多，尤其是大表，占用大量的系统资源
且在join时，尽量使用小表作为驱动表，即数据量小的表

临时表

临时表和内存表是两个不同的概念，内存表指的是Memory引擎的表，建表语法是create table … engine = memory，这种表的数据都保存在内存里，系统重启的时候会被清理，但是表结构还在。而临时表可以使用各种引擎类型，如果使用InnoDB或者MyISAM建的临时表，写数据的时候是写到磁盘上的。临时表也可以使用Memory引擎。临时表的几个特性

• 临时表的建表语法是create temporary table …；
• 一个临时表只能被创建它的session访问，对其他线程不可见；
• 临时表可以与普通表同名，当一个session中存在同名的临时表和普通表时，show create 语句，以及增啥改查语句访问的是临时表；
• show tables命令不显示临时表

由于临时表只能被创建它的session访问，所以在这个session结束的时候，会自动删除临时表，也正是由于这个特性，临时表就特别适合join的优化

临时表的应用

由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。

分库分表系统的垮库查询

一般分库分表的场景，就是要把一个逻辑上的大表分散到不同的数据库实例上。比如将一个大表，按照字段拆分成1024个表，然后分部到32个数据库实例上：

在这个架构中，分区key的选择是以“减少垮库和跨表查询”为依据的。如果大部分的语句都会包含f的等值条件，那么就要用f做分区键。这样，在proxy这一层解析玩SQL语句以后，就能确定将这条语句路由到那个分表做查询。

当查询语句中没有分区key作为查询条件，且需要排序时，这时Mysql只能到所有的分区中区查找满足条件的所有的行，然后统一做order by操作，通常为了解决这个问题，有两种常见的思路：

• 在proxy层的进程代码中实现排序。这种方式的优势是处理速度快，拿到分库的数据以后，直接在内存中参与计算。不过这种方案的缺点也比较明显：需要的开发工作量较大，比如复杂的查询时；对proxy端的压力比较大，尤其是很容易出现内存不够用和CPU瓶颈的问题。
• 把各个分库拿到的数据汇总到一个Mysql实例的一个表中，然后在这个汇总实例上做逻辑操作。

临时表和主被复制

当binlog_format=row，那么跟临时表有关的语句就不会记录到binlog里。也就是说，只在binlo_format=statment/mixed的时候，binlog中才会记录临时表的操作。当主库线程退出的时候，会自动删除临时表，但是备库同步线程是持续在运行的，所以，在主库退出线程的时候还需要在写一个DROP TEMPORARY TABLE传给备库执行删除临时表。

当主库两个不同的线程创建同名的临时表时，备库中虽然应用日志线程是共用的，也就是说在应用线程里面会先后执行create语句，但备库里统一线程执行create，且表名相同，这显然是一个bug，因此在create时，备库会将这两个同名的临时表当做不同的表来处理，也就是说，会根据table_def_key的不用来创建临时表，即库名+表名+serverid+主库线程id

内部临时表

union的执行流程用到了内部临时表

union的语意是去两个select查询结果的并集，即两个select集合加起来，重复的行只保留一行。

执行流程：

1. 常见一个内存临时表，这个临时表只有一个整形字段f，并且f是主键字段；
2. 执行第一个字查询，得到的结果存入临时表中；
3. 执行第二个子查询：当拿到相同的行时，会试图插入临时表，但由于临时表中存在相同的行，违反了唯一性约束，所以会插入失败，然后继续往下执行，取到不同的行，插入临时表中
4. 从临时表中按行取出数据，返回结果，并删除临时表

可以看到，这里的内存临时报起到了暂存数据的作用，而且计算过程还用上了临时表主键id的唯一性越是，实现了union的语意。

如果是union all的话就没有了去重的语义，此时是不需要临时表的

group by的执行流程用到了内部临时表

语义是将表中的数据按照某个字段的结果排序输出，并进行分组统计

执行流程：

1. 创建内存临时表，表里面有两个字段m和c，主键是m
2. 扫描表的索引a，一次取出叶子节点上的id值，计算结果后记为x。如果临时表中没有主键为x的行，就插入，否则就将这一行的c值加1
3. 遍历完后，再根据字段做排序，得到结果集返回给客户端并删除临时表

如果不需要排序，在在末尾增加order by null

当临时表的空间满了后，会把内存临时表转成磁盘临时表，磁盘临时表默认使用的引擎是InnoDB

group by优化方法 — 索引

从上可以看到，不论是使用内存临时表还是磁盘临时表，group by逻辑都需要构造一个带唯一索引的表，执行代价都是比较高的。如果表的数据量比较大，上面这个group by语句执行起来就会很慢，优化的方法就是加索引

Myslq5.7版本支持了generated column机制，用来实现列数据的关联更新，可以用 alter table 表名 add column 列名 字段类型 generated always as(),ass index(列名);创建一个列，然后在该列上创建一个索引，然后可以执行select z，count(*) as c from 表名 group by z;这样就不需要临时表，也不需要排序了

直接排序

在group by 语句中加入SQL_BIG_RESULT这个提示，告诉优化器这个语句涉及的数据量很大，让其直接用磁盘临时表。由于磁盘临时表一般是B+树存储，此存储效率步入数组来得高，这时，优化器会考虑磁盘问题，然后选择数据来存储数据，执行流程是这样的：

1. 初始化sort_buffer，确定放入一个字段，记为m
2. 扫描表的索通a，依次取出记录存入sort_buffer中
3. 扫描完成后，对sort_buffer的字段m做排序，如果sort_buffer内存不够用，就会利用磁盘临时文件辅助排序
4. 排序完成后，就得到一个有序数组

什么时候使用内存临时表

如果语句执行过程可以一遍读数据，一边直接得到结果，是不需要额外内存的，否则就需要额外的内存来保存中间结果；

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

如果执行逻辑需要用到二维表特性，就会有限考虑使用临时表。

InnoDB和Memory

InnoDB把数据数据放在主键索引上，其他索引上保存的是主键id，这种方式称为索引组织表。而Memory引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，这种方式成为堆组织表。

因此可以看出：

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

Memory表的主键索引时hash索引，如果执行范围查询是用不上主键索引的，需要走全表扫描，因此要让Memory表走范围扫描，需要Memory表使用B-Tree索引，而Memory表是支持B-Tree索引的，sql: alter table 表名 add index a_btree_index using btree(字段名);

虽然Memory表的速度快，但不建议在生产环境中使用Memory表，原因：锁粒度问题和数据持久化问题

Memory表的锁

Memory表不支持行锁，只支持表锁，因此，一张Memory表只要有更新就会堵住其他所有在这个表上的读写操作。这里的表锁和MDL锁不同，但都是表级的锁

因此，跟行锁比起来，表锁对并发访问的支持不够友好。所以，Memory表的锁粒度问题，决定了他在处理并发事务的时候，性能也不会太好

Memory数据持久性

数据放在内存中，数据访问快，这是Memory表的的优势，但也是一个劣势，因为，数据重启的时候，所有的Memory都会被清空，在高可用架构下，这无疑是一种bug

自增主键

在一张含有自增主键的表中执行show create table命令，可以看到AUTO_INCREMENT=N，N表示下一次插入数据是，如果需要自动生成自增值，则值为N。虽然次数显示了自增值，但自增值却不是保存在表结构定义里的，而是根据不同引擎，选择的保存策略也会不同：

MyISAM中的自增值保存在数据文件中；

InnoDB的自增值保存在内存里，并且到了Mysql8.0版本后，才有了自增值持久化的功能：在Mysql5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10，因为此时数据表里面的数据的最大id是9。在Mysql8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

不能连续原因

表复制

mysqldump

将数据导出成一组insert语句

导出csv文件

变量

系统变量

有系统提供的，不是用户自定义的，属于服务器层面。系统变量分为全局变量和会话变量

语法：

查看所有的系统变量：show global|session variables

查看满足条件的部分系统变量：show global|session variables like ‘%char%’

查看指定的某个系统变量的值：select @@global|@@session .系统变量名

为某个系统变量赋值：set global|session 系统变量名=值 或者 set @@global|@@session .系统变量名=值

自定义变量

相当于会话变量，只针对当前连接，包括用户变量和局部变量

语法：

声明并初始化：set @用户变量名=值 或者 set @用户变量名:=值 或者 select @用户变量名:=值

更新同上步骤

查看：select @用户变量名

声明局部变量： declare 变量名 类型 或者 declare 变量名 类型 default 值

局部变量赋值：跟用户变赋值一样，但不需要加@

查看局部变量名：select 局部变量名

存储过程和函数

存储过程

一组预先编译好的sql语句的集合，可以理解成批处理语句

优点：

提高代码的重用性

简化操作

减少了编译次数和连接次数，提高了效率

创建：

delimiter 结束标记(自定义) 可不加  
create procedure 存储过程名(参数列表)
begin
    存储过程体：一组合法的sql语句，如果仅仅为一条sql语句，begin和end可以省略。多条sql以分号隔开
end 结束标记

参数列表包含三部分：参数模式 参数名 参数类型 比如in name varchar(20)

参数模式，包括 in(该模式将参数作为输入，也就是需要调用方传入值)、out(该模式将参数作为输出，也就是将该参数作为返回值)、inout(该模式可作为输入和输出)

调用：call 存储过程名(实参列表)

sql注入

sql注入，简单来说就是用户在前端web页面输入恶意的sql语句用来欺骗后端服务器去执行恶意的sql代码，从而导致数据库数据泄露或者遭受攻击。

解决

PreparedStatement类

在用JDBC进行连接时使用PreparedStatement类去代替Statement,或者传入的条件参数完全不使用String字符串

使用PreparedStatement去写sql语句时，程序会对该条sql首先进行预编译，然后会将传入的字符串参数以字符串的形式去处理，即会在参数的两边自动加上单引号（’param’），而Statement则是直接简单粗暴地通过人工的字符串拼接的方式去写sql，那这样就很容易被sql注入。

底层主要方法是setString(),

/**
     * Set a parameter to a Java String value. The driver converts this to a SQL
     * VARCHAR or LONGVARCHAR value (depending on the arguments size relative to
     * the driver's limits on VARCHARs) when it sends it to the database.
     * 
     * @param parameterIndex
     *            the first parameter is 1...
     * @param x
     *            the parameter value
     * 
     * @exception SQLException
     *                if a database access error occurs
     */
    public void setString(int parameterIndex, String x) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            // if the passed string is null, then set this column to null
            if (x == null) {
                setNull(parameterIndex, Types.CHAR);
            } else {
                checkClosed();
                int stringLength = x.length();
                if (this.connection.isNoBackslashEscapesSet()) {
                    // Scan for any nasty chars
                    boolean needsHexEscape = isEscapeNeededForString(x, stringLength);
                    if (!needsHexEscape) {
                        byte[] parameterAsBytes = null;
                        StringBuilder quotedString = new StringBuilder(x.length() + 2);
                        quotedString.append('\'');
                        quotedString.append(x);
                        quotedString.append('\'');
                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString(), this.charConverter, this.charEncoding,
                                    this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(quotedString.toString());
                        }
                        setInternal(parameterIndex, parameterAsBytes);
                    } else {
                        byte[] parameterAsBytes = null;
                        if (!this.isLoadDataQuery) {
                            parameterAsBytes = StringUtils.getBytes(x, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                    this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                        } else {
                            // Send with platform character encoding
                            parameterAsBytes = StringUtils.getBytes(x);
                        }
                        setBytes(parameterIndex, parameterAsBytes);
                    }
                    return;
                }
                String parameterAsString = x;
                boolean needsQuoted = true;
                if (this.isLoadDataQuery || isEscapeNeededForString(x, stringLength)) {
                    needsQuoted = false; // saves an allocation later
                    StringBuilder buf = new StringBuilder((int) (x.length() * 1.1));
                    buf.append('\'');
                    //
                    // Note: buf.append(char) is _faster_ than appending in blocks, because the block append requires a System.arraycopy().... go figure...
                    //
                    for (int i = 0; i < stringLength; ++i) {
                        char c = x.charAt(i);
                        switch (c) {
                            case 0: /* Must be escaped for 'mysql' */
                                buf.append('\\');
                                buf.append('0');
                                break;
                            case '\n': /* Must be escaped for logs */
                                buf.append('\\');
                                buf.append('n');
                                break;
                            case '\r':
                                buf.append('\\');
                                buf.append('r');
                                break;
                            case '\\':
                                buf.append('\\');
                                buf.append('\\');
                                break;
                            case '\'':
                                buf.append('\\');
                                buf.append('\'');
                                break;
                            case '"': /* Better safe than sorry */
                                if (this.usingAnsiMode) {
                                    buf.append('\\');
                                }
                                buf.append('"');
                                break;
                            case '\032': /* This gives problems on Win32 */
                                buf.append('\\');
                                buf.append('Z');
                                break;
                            case '\u00a5':
                            case '\u20a9':
                                // escape characters interpreted as backslash by mysql
                                if (this.charsetEncoder != null) {
                                    CharBuffer cbuf = CharBuffer.allocate(1);
                                    ByteBuffer bbuf = ByteBuffer.allocate(1);
                                    cbuf.put(c);
                                    cbuf.position(0);
                                    this.charsetEncoder.encode(cbuf, bbuf, true);
                                    if (bbuf.get(0) == '\\') {
                                        buf.append('\\');
                                    }
                                }
                                buf.append(c);
                                break;
                            default:
                                buf.append(c);
                        }
                    }
                    buf.append('\'');
                    parameterAsString = buf.toString();
                }
                byte[] parameterAsBytes = null;
                if (!this.isLoadDataQuery) {
                    if (needsQuoted) {
                        parameterAsBytes = StringUtils.getBytesWrapped(parameterAsString, '\'', '\'', this.charConverter, this.charEncoding,
                                this.connection.getServerCharset(), this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    } else {
                        parameterAsBytes = StringUtils.getBytes(parameterAsString, this.charConverter, this.charEncoding, this.connection.getServerCharset(),
                                this.connection.parserKnowsUnicode(), getExceptionInterceptor());
                    }
                } else {
                    // Send with platform character encoding
                    parameterAsBytes = StringUtils.getBytes(parameterAsString);
                }
                setInternal(parameterIndex, parameterAsBytes);
                this.parameterTypes[parameterIndex - 1 + getParameterIndexOffset()] = Types.VARCHAR;
            }
        }
    }

后面一部分则是如何有效防止sql注入的重点，代码中通过一个for循环，将字符串参数通过提取每一位上的char字符进行遍历，并通过switch()….case 条件语句进行判断，当出现换行符、引号、斜杠等特殊字符时，对这些特殊字符进行转义。有些特殊字符没做处理，比如%，虽然此种SQL注入危害不大，但这种查询会耗尽系统资源，从而演化成拒绝服务攻击。

PreparedStatement是与编译的，可以提高批处理效率，因此对于一次性操作并不会带来额外的好处。My SQL数据库，它默认批处理是关闭的，所以需要打开My SQL批处理，使用参数来打开rewriteBatchedStatements=true

但是时常地（有需要时）打开和关闭数据库会造成数据库资源浪费，影响数据库性能，这个时候就会想到用数据库连接池（c3p0）。

jdbc读取数据库从resultSet中遍历结果集，存在硬编码（写死的），不利于系统维护，所以最好能将结果集自动映射成java对象
由此产生了mybatis。

mybatis

使用#{param}占位符的方式去避免sql注入

mybatis的 {}是不能防止sql注入的，它能够通过字符串拼接的形式来任意摆弄你的sql语句，而#{}则可以很大程度上地防止sql注入

其底层也是JDBC中的PreparedStatement类在起作用

mybatis中的#和$的区别：

1、#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。
如：where username=#{username}，如果传入的值是111,那么解析成sql时的值为where username=”111”, 如果传入的值是id，则解析成的sql为where username=”id”.　
2、{username}，如果传入的值是111,那么解析成sql时的值为where username=111；
如果传入的值是;drop table user;，则解析成的sql为：select id, username, password, role from user where username=;drop table user;
3、#方式能够很大程度防止sql注入，方式一般用于传入数据库对象，例如传入表名.
5、一般能用#的就别用{xxx}”这样的参数，要手工地做好过滤工作，来防止sql注入攻击。
6、在MyBatis中，“{xxx}”这样的参数格式。所以，这样的参数需要我们在代码中手工进行处理来防止注入。
【结论】在编写MyBatis的映射语句时，尽量采用“#{xxx}”这样的格式。若不得不使用“${xxx}”这样的参数，要手工地做好过滤工作，来防止SQL注入攻击。

过滤器过滤特殊字符

CriteriaBuilder动态查询

基本原理

基本原理都是对特殊字符进行转义

数据库连接池

为什么要使用连接池

1. 在项目的实际开发中，连接数据库一般都会使用两种方式。

（1） 使用JDBC直接连接数据库，其他程序每访问一次数据库，访问程序直接跟数据库打交道，建立一次数据库连接，用完在释放连接资源。
（2） 使用连接池技术，建立一个连接池，由连接池连接到数据库，在将数据库连接对象connection放到连接池中，此时其他程序访问数据库时，不是直接和数据库打交道，而是和连接池交互，当使用完连接资源后释放，该连接资源将会放回池里，等待下一次使用。
2.下面我们进一步来分析这两种连接方式的差异。
（1）使用JDBC直接连接数据库，如图所示：

这种方式当成千上万个用户方访问数据库时，就需要直接从数据库获取成千上万个Connection对象，这对数据库来说，无疑会造成巨大的压力，会导致整个数据库服务器奔溃。性能会受很大的影响。
（2）使用连接池连接数据库，如喜下图所示：

使用连接池技术，就会从根本上解决使用JDBC连接的问题。即使有成千上万用户访问数据库时，而不是直接和数据库打交道，而是从连接池中得到事先放在池中的Connection连接对象，而连接池只和数据库连接一次。这样就保证了数据访问性能。

目的

主要是避免多次重复的资源申请、释放造成GC频繁，资源消耗

alibaba durid连接池的使用

pom

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.38</version>
</dependency>
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.3</version>
</dependency>

xml

配置信息参考官网：https://github.com/alibaba/druid/wiki/DruidDataSource%E9%85%8D%E7%BD%AE

spring:
    datasource:
        type: com.alibaba.druid.pool.DruidDataSource
        driverClassName: com.mysql.jdbc.Driver
        druid:
            first:
                url: jdbc:mysql://localhost:3306/test1_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                username: root
                password: root
            second:
                url: jdbc:mysql://localhost:3306/test2_system?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
                username: root
                password: root
            initial-size: 30
            max-active: 100
            min-idle: 20
            max-wait: 60000
            pool-prepared-statements: true
            max-pool-prepared-statement-per-connection-size: 20
            min-evictable-idle-time-millis: 300000
            validation-query: SELECT 1 FROM DUAL
            test-while-idle: true
            test-on-borrow: false
            test-on-return: false
            stat-view-servlet:
                enabled: true
                url-pattern: /druid/*
            filter:
                stat:
                    log-slow-sql: true
                    slow-sql-millis: 1000
                    merge-sql: true
                wall:
                    config:
                        multi-statement-allow: true

SpringBoot注入Druid连接池

import com.alibaba.druid.support.http.WebStatFilter;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
@WebFilter(filterName="druidWebStatFilter",
           urlPatterns="/*",
           initParams={
               @WebInitParam(name="exclusions",value="*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"),// 忽略资源
           })
public class DruidStatFilter extends WebStatFilter {
}

import com.alibaba.druid.support.http.StatViewServlet;
import javax.servlet.annotation.WebInitParam;
import javax.servlet.annotation.WebServlet;
@WebServlet(urlPatterns = "/druid/*", initParams={
        @WebInitParam(name="allow",value=""),// IP白名单 (没有配置或者为空，则允许所有访问)
        @WebInitParam(name="deny",value="192.168.16.11"),// IP黑名单 (存在共同时，deny优先于allow)
        @WebInitParam(name="loginUsername",value="admin"),// 用户名
        @WebInitParam(name="loginPassword",value="admin"),// 密码
        @WebInitParam(name="resetEnable",value="true")// 禁用HTML页面上的“Reset All”功能
})
public class DruidStatViewServlet extends StatViewServlet {
    private static final long serialVersionUID = 2359758657306626394L;
}

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletComponentScan;
import org.springframework.context.annotation.Bean;
import javax.sql.DataSource;
// SpringBootApplication注解：负责启动SpringBoot项目。这个注解实现了扫描包的功能，扫描范围是该文件所在的当前目录和同级目录。
// 我们需要格外注意这个注解所在文件的位置，因为它和扫包有关系。什么是扫包？就是发现Service,Controller,Component然后自动注入到Spring容器中。
@SpringBootApplication
@ServletComponentScan
public class Application {
    public static void main(String[] args){
        SpringApplication.run(Application.class, args);
    }
    @Bean("druidDataSource")
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource druidDataSource() { return new DruidDataSource(); }
}

Druid连接池

Druid连接池是阿里巴巴开源的数据库连接池项目。Druid连接池为监控而生，内置强大的监控功能，监控特性不影响性能。功能强大，能防SQL注入，内置Loging能诊断Hack应用行为。

durid连接池原理

Druid连接池的核心功能主要就是注册一个DataSource的bean，连接池、获取连接等都依赖于DataSource的实现类DruidDataSourceWrapper，连接池功能主要是维护了一个数组，在项目启动时提前创建了一些数据库连接放到了里面复用

数据库事务

它是一些列严密操作动作，要么都操作完成，要么都回滚撤销。

事务具备ACID四种特性，Atomic（原子性）、Consistency（一致性）、Isolation（隔离性）和Durability（持久性）

（1）原子性（Atomicity）
事务最基本的操作单元，要么全部成功，要么全部失败，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性（Consistency）
事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。如果事务成功地完成，那么系统中所有变化将正确地应用，系统处于有效状态。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。
（3）隔离性（Isolation）
指的是在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务查看数据更新时，数据所处的状态要么是另一事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看到中间状态的数据。
（4）持久性（Durability）
指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来。即使发生系统崩溃，重新启动数据库系统后，数据库还能恢复到事务成功结束时的状态。

事务的传播特性

事务传播行为就是多个事务方法调用时，如何定义方法间事务的传播。

Spring定义了7种事务传播行为：

1. propagation_required：如果当前没有事务，就新建一个事务，如果已经存在一个事务，就加入到这个事务中，这是Spring默认的。
2. propagation_supports：支持当前事务，如果没有当前事务，就以非事务方法执行。
3. propagation_mandatory：使用当前事务，如果没有当前事务，就以非事务方法执行。
4. propagation_required_new：新建事务，如果当前存在事务，把当前事务挂起。
5. propagation_not_supported：以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。
6. propagation_never：以非事务方式执行操作，如果当前事务存在则抛出异常。
7. propagation_nested：如果当前存在事务，则在嵌套事务内执行，否则，则实行与propagation_required类似的操作。

在spring中只需要在@Transaction注解中添加propagation属性就可以使用，例如：@Transactional(propagation=Propagation.REQUIRED)

事务的隔离级别

1. read uncommited（读未提交）：是最低的事务隔离级别，它允许另外一个事务可以看到这个事务未提交的数据。
2. read commited（读提交）：保证一个事务提交后才能被另外一个事务读取，另外一个事务不能读取该事务未提交的数据。
3. repeatable read（可重复读）：这种事务隔离级别可以防止脏读，不可重复度，但是可能会出现幻读。它除了保证一个事务不能被另外一个事务读取未提交的数据之外还能避免不可重复读的发生。
4. serializable（序列化）：这是花费最高代价但最可靠的事务隔离级别。事务被处理为顺序执行，除了防止脏读，不可重复读之外，还可以避免幻读。

脏读、不可重复读、幻读的概念：

脏读是指当一个事务正在访问数据，并且对数据进行了修改，而这种数据还没有提交到数据库中，这时，另外一个事务也访问这个数据，染回使用可这个数据，由于这个数据还没有提交，那么另外一个事务读取到的这个数据我们称之为脏数据。依据脏数据所做的操作可能是不正确的。

不可重复读指在一个事务内，多次读同一数据。在这个事务还没有执行结束，另外一个事务也访问该同一数据，那么在第一个事务中的两次读取数据之间，由于第二个事务的修改，第一个事务两次读到的数据可能是不一样的，这样就发生了在一个事务内两次连续读到的数据是不一样的，这种情况被称为是不可重复读。

幻读：一个事务先后读取一个范围的记录，但两次读取的记录数不同，我们称之为幻读，即两次执行同一条select语句会出现不同的结果，第二次读会增加一条数据行，并没有说这两次执行是在同一个事务中。

隔离级别的使用只需要在@Transactional中添加isolation属性就可以了，例如：@Transactional(isolation = Isolation.READ_UNCOMMITTED

事务的实现

1. 编程式事务管理对基于POJO的应用来说是唯一选择。我们需要在代码中调用beginTansation()、commit()、rollback()等事务管理相关的方法，这就是编程式事务管理。
2. 基于TansationProxyFactoryBean的声明事务管理
3. 基于@Transactional的声明式事务管理
4. 基于Aspectj AOP配置事务

@Transactional事务注解

其底层是基于spring aop实现的，通过cglib动态创建代理对象

在spring中想要对一个方法进行代理的话，需要定义切点，而@Tansactional注解就是一个切点，这样才能知道@Transactional注解标注的方法或类需要被代理

添加@Tansactional注解后spring会在ioc容器中创建一个beanFactory的实例，这个实例可以看作是一个切点，在判断bean在初始化过程中是否需要创建代理对象，都需要验证这个实例是否适用这个bean的切点，如果适用则创建代理对象，并把实例对象注入到代理对象中。

判断是否使用切点是通过aopUtils中的findAdvisorsThatCanApply方法去判断的，经过一系列调用后，最终通过SpringTransactionAnnotationParser的parseTransactionAnnotation(java.lang.reflect.AnnotatedElement)方法

@Override
public TransactionAttribute parseTransactionAnnotation(AnnotatedElement ae) {
    //这里就是分析Method是否被@Transactional注解标注，有的话，不用说BeanFactoryTransactionAttributeSourceAdvisor适配当前bean，进行代理，并且注入切点
    //BeanFactoryTransactionAttributeSourceAdvisor
   AnnotationAttributes attributes = AnnotatedElementUtils.getMergedAnnotationAttributes(ae, Transactional.class);
   if (attributes != null) {
      return parseTransactionAnnotation(attributes);
   }
   else {
      return null;
   }
}

@Transactional的作用一个就是标识方法需要被代理，一个就是携带事务管理需要的一些属性信息。

利用DynamicAdvisedInterceptor#intercept返回的TransactionInterceptor（也叫最终事务管理者）实现代理逻辑的调用，调用其invoke方法，将cglib方法调用出入到该方法中，且cglib方法包装了目标对象的方法调用的所有必须信息，也因此invoke还可以注入一些其他逻辑，比如事务管理逻辑

使用方式

1. 放到类名，则这个类下所有的public方法都起作用
2. 放到方法上，则只对这个public方法其作用

对于其它非public的方法,如果标记了@Transactional也不会报错,但方法没有事务功能.

配置

（1）、事务的传播：@Transactional(propagation=Propagation.REQUIRED) 如果有事务, 那么加入事务, 没有的话新建一个(默认情况下)

（2）、事务的超时性：@Transactional(timeout=30) //默认是30秒 注意这里说的是事务的超时性而不是Connection的超时性，这两个是有区别的

（3）、事务的隔离级别：@Transactional(isolation = Isolation.READ_UNCOMMITTED)，读取未提交数据(会出现脏读, 不可重复读) 基本不使用

（4）、回滚：指定单一异常类：@Transactional(rollbackFor=RuntimeException.class)指定多个异常类：@Transactional(rollbackFor={RuntimeException.class, Exception.class})该属性用于设置需要进行回滚的异常类数组，当方法中抛出指定异常数组中的异常时，则进行事务回滚。

（5）、只读：@Transactional(readOnly=true)，该属性用于设置当前事务是否为只读事务，设置为true表示只读，false则表示可读写，默认值为false。

Spring事务的使用

SpringBoot事务的使用

在主启动类上添加@EnableTransactionManagement来开启事务，然后在访问数据库的Service方法上添加注解 @Transactional 便可。

关于事务管理器，不管是JPA还是JDBC等都实现自接口 PlatformTransactionManager 如果你添加的是 spring-boot-starter-jdbc 依赖，框架会默认注入 DataSourceTransactionManager 实例。如果你添加的是 spring-boot-starter-data-jpa 依赖，框架会默认注入 JpaTransactionManager 实例。

Mysql中的事务

隐式事务

事务美哦与明显的开启和结束的标记，比如insert、update、delete语句，默认自动提交是开启的

显示事务

事务具有明显的开启和结束的标记，需禁用自动提交功能(set autocommit=0;#值针对当前事务有效，每次开启事务需再次关闭)

set autocommit=0;
#开启事务的语句
start transaction;
update ***;
update ***;
stop transaction;
#结束事务的语句，根据情况是否提交事务是否回滚事务

并发时事务

对于同时运行的多个事务，当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制，就会导致各种并发问题：

• 脏读，对于两个事务T1和T2，T1读取了已经被T2更新但没有被提交的字段之后，若T2回滚，则T1读取的内容就是临时且无效的
• 不可重复读，对于两个事务T1和T2，T1读取了一个字段，然后T2更新了该字段之后，提交后，T1事务还没结束，再次读取同一个字段，值就不同了，即多次查询的结果不一致
• 幻读，对于两个事务T1和T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行，还没提交。之后，如果T1再次读取同一个表，就会多出几行

隔离级别

可通过设置隔离级别来避免上述遇到的几种情况，使事务与事务之间互不影响，mysql默认的隔离级别是可重复读

读未提交

允许事务读取未被其他事务提交的变更。脏读、不可重复度、幻读的问题都会出现

读已提交

只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读问题仍然可能出现

可重复读

确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复度，但幻读的问题仍然存在

串行化

确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有并发问题都可以避免，但性能十分低下