读mysql45讲-整理

mysql的基本架构

mysql中主要分为两层，上层是server层，包括了mysql大多数核心功能的服务，有连接器，分析器，优化器，执行器，内置函数，存储过程，触发器，试图等等。下层是存储引擎层，主要功能就是存储数据和提供数据查询接口。存储引擎层的架构模式是插件式的，支持多个不同的存储引擎，MyISAM,Innodb,Memory等等。

连接器

客户端发起连接请求，通过TCP连接，其次验证输入的账号和密码，然后到内置的mysql库查询权限，接着在这个连接中判断权限都是用的这个查询结果，如果新赋予了权限，是不会立即生效的，需要断开重连才行。

在连接成功如果没有进行操作，那么当前连接就会处于空闲状态，使用show processlist命令会看到Command列的值是sleep,超过一定时长一直没有活跃的话，mysql就会断开整个连接，这个时长可以通过修改配置wait_timeout来控制。

mysql中的连接有长连接和短连接，长连接就是执行完sql之后这个连接并不会就立即断开连接，而是处于slepp状态，下次再次执行sql的时候就会使用这个连接，类似线程池。
短连接则是执行完几个sql之后或者等待较短之间之后会立即断开连接。短连接的消耗比较大，因为建立连接的过程比较耗时，如果有很多的短连接在同一时间内发起，就会引起mysql的性能紧张。
长连接如果一直处于使用状态，会导致内存占用太大，因为在执行sql的过程中，会将临时数据放入内存中，这些资源只有在断开连接的时候才会被释放，所以如果长连接过度使用就会导致oom。

解决方法可以定期清理长连接，或者是在执行完一个耗内存sql之后，手动清理内存，执行mysql_reset_connection这个命令不会导致重新连接和权限验证，会初始化内存。

MySQL的performance schema 用于监控MySQL server在一个较低级别的运行过程中的资源消耗、资源等待等情况。

查看占用内存过高的执行sql：(没有验证过，百度复制过来的)

1. ps -ef | grep -i mysql 查询出mysql进程id
2. top -p <mysql线程id> -H 查询出mysql进程中占用内存最高的操作系统线程id
3. select thread_id,name ,PROCESSLIST_ID,THREAD_OS_ID from threads where thread_os_id = <操作系统线程id> ; 根据线程id查询出mysql线程信息
4. select sql_text from performance_schema.events_statements_current where thread_id = <mysql线程id> 找出具体sql

分析器

传入到mysql执行的sql其实是一段字符串，为了能让mysql理解具体要执行的sql是什么意思，分析器会根据规则找出关键字，比如from 后面跟着的是表名；解析出sql要操作的表，要查询的列，还有条件筛选等等。

优化器

mysql会主动优化sql语句，比如选择索引。

执行器

执行器就是去调用引擎层中的接口去存储和查询数据，在调用接口之前会先判断当前用户对要操作的表有没有权限。

日志

mysql中的日志系统有两种，一个是server层的binlog，一个是Innodb引擎层中的日志redo log。redo log日志中记载的是物理日志，具体对某个数据页中数据更新为了什么值；binlog记载的是逻辑日志，类似sql那种意思。mysql保证crash-safe能力，也就是哪怕发生异常重启，也不会丢失数据，正是依赖于redo log。

WAL(Write ahed Logging): 提前写入日志

在执行更新数据语句(update,delete,insert)的时候，mysql并不会直接将数据写入到磁盘，为了提高效率和减少IO成本，会先将整个操作记录写入到binlog中和redo log中，只更新内存中的数据，不会写入到磁盘中。

存储空间

binlog是追加内容到文本中的形式，如果一个文件写满了就会新创建一个文件；redo log则是有固定的文件存储大小，一旦用完了整个空间就会将之前的内容擦除掉，空出来留给之后用。

写入日志的方式： 两阶段提交

首先先写入到binlog中，事务状态此时是prepare，其次再写入到redo log中，写入完成之后，接着binlog中事务的状态就会变为commit。

写日志的语句格式

在写入binglog的时候，可以设置参数binlog_format的值（statement,row,mixed）;设置为statement则写入到binglog中sql就是原始的sql语句，设置为row则是会记录具体的数值，mixed则是结合两者； 设置为statement的优点是记录内容小，如果是删除语句的话，比如delete tableName where column = xx，statement配置下就会记录这样一条语句，但是row则会把满足条件数据行记录下来，statement的有点就可以看出来节省空间，如果删除的数据很多的话就会更明显，但是缺点就是如果需要使用binglog来恢复数据的话，statement就没有row好用了。

binlog日志的写入流程

开启事务后，mysql会先把执行的sql记录到binlog cache中，在commit之后将binglog cache写write到文件系统的page cache中，然后再统一将page cache中的日志写到磁盘中。从binlog cache写入到page cache中的过程称之为write，从page cache中写入到磁盘的过程称之为fysnc，也就是fsync才会具体的写入到磁盘文件中。write和fsync发生的时机时可以通过配置参数sync_binlog来改变的。

• sync = 0,表示每次事务提交只会write，不会fsync
• sync=1,表示每次事务提交不仅会write，也会fsync
• sync= N （N >1）表示在累计N个事务提交之后才会触发fsync。

redo log日志的写入流程

流程和binglog日志写入流程类似，也是在事务执行过程中，先记录日志到redo log buffer中，然后写到page cache中，最后写入到磁盘中，也有一个innodb_flush_log_at_trx_commit:

• innodb_flush_log_at_trx_commit=0,表示事务提交只会写到redo log buffer中
• innodb_flush_log_at_trx_commit =1 表示事务提交后会直接持久化到磁盘中
• innodb_flush_log_at_trx_commit=2 ，表示事务提交只会写到文件系统page cache中

Innodb后台会有线程，每隔1秒就会将redo log buffer中的内容写到page cache中，然后再写入到磁盘中；所以存在这样的情况：虽然这个事务还没有提交，但是这个事务记录在redo log buffer中的内容已经被持久化到磁盘中了。

除了后台线程会将redo log buffer中的内容写到磁盘中，还会有其他情况触发这个操作：

• 当redo log buffer的大小占到innodb_log_buffer_size的一半的时候，也会将redo log buffer中的内容先写到page cache中，但是如果事务提交了就会写入到磁盘中，如果事务没有提交，就不会写入到磁盘中，而只是会留在page cache中。
• 当事务执行过程中，其他的事务提交了，并且innodb_flush_log_at_trx_commit设置的值是=1，那么就会连带着将未提交的事务写入到redo log buffer中内容也写入到page cache中，再写入到磁盘中。

触发刷盘

因为redo日志的存在，所以并不是每更新数据都直接操作磁盘数据，而是先将更新操作记录在redo log日志和 binlog日志中，但是redo log日志的空间并不是无限的，所以也需要将redo中的更新操作应用到磁盘中。在内存中的数据和在数据页中的数据不一致的时候，把内存中的数据页叫做脏页，将内存中的数据应用到磁盘中的过程称为flus，会触发flush操作的情况有下面几种：

• redo log空间已满。这个时候不得已必须将redo log日志中涉及到的操作记录应用到磁盘的数据中；这个时候对外展示的效果就是整个系统都不能再更新数据了。
• 内存已满，这时候进来一个查询请求，但是请求的数据不在内存中，就需要到磁盘中读取，然后放到内存中，但是此时内存已经满了，所以就需要淘汰一些数据页，如果淘汰的是脏页，那么就需要将这个脏页中的数据写到磁盘中。
• 系统空闲的时候。
• mysql关闭的时候，会将内存中所有的脏页都写到磁盘中。

索引

Innodb中的索引是使用B+树模型，有普通索引，唯一索引，主键索引。主键索引和普通索引的区别是，主键索引树的节点上存储的是整行记录，普通索引树的节点上存储的则是索引字段和主键id，所以如果查询字段中有字段不是普通索引的，就会触发回表操作，先根据普通索引字段查询到主键，再根据主键查询到非索引字段。

覆盖索引

普通索引对应的B+树节点种存储的是普通索引字段和主键，当select查询字段中有非普通索引字段，比如 select a,b from table where a = xx order by b 这个sql中，如果a字段是有索引的，但是b字段没有，那么就需要回表操作。为了减少回表操作，可以考虑建立一个(a,b)联合索引，这样这个索引就可以覆盖所有的查询字段。
当然联合索引的长度要大于普通索引，所以在建立的时候需要权衡，是否值得去建立一个联合索引。

普通索引和唯一索引

普通索引和唯一索引除了在业务上有特定的要求之外，如果表里的数据属于是写多读少的话，优先考虑普通索引。对于更新数据的语句来说，并不会每次都直接更新磁盘上的数据。在进行更新数据的时候，除了会写redo log和binlog之外，会将更新操作记录在内存中的change_buffer中，change_buffer分配的大小根据innodb_change_buffer_max_size参数配置，配置值是个百分比的数，分母是buffer_pool分配的内存大小。

如果是更新的数据在内存中，那直接更新内存；普通索引可以直接将更新操作记录在change_buffer中，唯一索引需要先判断是否违反唯一键冲突，然后再写入到change_buffer中，如果更新操作很频繁和数据很多的情况下差距就会很大，所有优先写多读少的情况优先考虑普通索引。

如果更新的数据不在内存中，先将更新操作记录到change_buffer中，紧接着又需要查询这条数据，就需要将这条数据读入到内存中，再将change_buffer中记录这页的有关更新操作应用到数据页中，保证数据的逻辑正确，这种将change_buffer应用到原始数据的过程叫做merge，除了查询会触发merge操作之外，innodb后台会定期有线程扫描触发merge，以及数据库正常关闭的时候。
为了最大化提高change_buffer的使用效率，就是尽量将change_buffer中写满再触发merge操作是最合适的，merge操作反而会让change_buffer适得其反，又要花费内存空间维护，但是又没有起到作用，频繁的需要读磁盘，所以change_buffer比较适合写多读少的表。

最左前缀原则

对于索引字段而言，并不是只有等值判断才会走索引；例如对于name字段添加索引，如果查询条件是name like '%杨%'这个也是可以走索引的，这就是索引的最左前缀原则。联合索引的左边前n个字段也是可以走索引的，字符串索引的前n个字符也是可以走索引的。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

索引下推

对于联合索引(a,b),如果一个select语句中有a和b的条件过滤，那么在进行回表之前，会先进行a和b字段的过滤，而不是只过滤a字段的条件，这样就减少了回表操作。

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

索引的选择

一个sql会使用哪个索引由mysql中优化器来决定的，当然也可以使用force indexName(index column)来强制使用某个索引，这种写法不是很合理，可能索引名变了或者是使用的索引不正确。影响优化器选择索引的因素有：扫描行数，是否排序，是否使用临时表，包括上面的回表次数等等。

扫描行数统计规则

mysql判断一个sql中的索引的扫描行数结果是估算出来的，并且是通过采样统计的方式。过程大概是随机选择N个数据页，然后在N个数据页中统计不同值的数量，再求平均值，最后乘以这个索引的页面数量，就是这个索引的扫描行数。

如果在使用explain分析sql的时候发现，扫描行数和预估行数大相径庭，可以先执行analyze table tableName语句来重新统计索引信息；但是并不是扫描行数越少，优化器就会选择这个索引，优化器还会考虑其他因素的影响，也就是上面说的是否排序，是否使用临时表，回表操作次数等等。

事务

事务的四大特性：A（原子性），C（一致性），I（隔离性），D（持久性）。
事务的等级： 未提交读，提交读，可重复读，串行化。
未提交读： 事务在未提交的时候，修改的数据对其他事务也是可见的，会导致脏读。
提交读： 事务只有提交完成之后，修改过后的数据才会对其他事务可见。
可重复读： 事务过程中看到的数据都是一样的。
串行化： 在执行事务的时候会加上不同的锁，读锁和写锁，如果出现锁冲突，需要等待前一个事务执行完。

一致性视图

可重复读保证事务过程中看到的数据是一致的，在开启事务的时候，会创建一个视图，这个视图相当于对整个库的一个快照，但是并不会查询整个库的数据作为视图数据，而是当前事务能看到的数据。
每次创建一个事务的时候Innodb都会分配一个严格递增的id，结合mysql的MVVC(Multiple Version concurrency control, 多版本并发控制)，在对数据进行更新的时候，就相当于一个新版本，每个版本都会保更新数据的事务的id，记作row trx_id,这样根据row trx_id和当前版本的数据值可以回推到上个版本的值。
事务在启动的瞬间，Innodb会将当前活跃的事务的id值记录下来，活跃的事务就是已经创建了但是还没有提交的事务；这个记录的活跃事务id值数组最小值叫做低水位，将当前事务id值加1叫做高水位。由这个活跃事务id值数组，和高水位就组成了一致性视图。在启动的这个事务中，其他事务的提交结果都是根据row trx_id和当前启动的事务id比较结果来判断是否可见。

当前读

在可重复读隔离级别下启动的事务中，如果存在更新语句，那就需要先读后写；
如果当前事务执行过程中，有一个某行的数据的更新语句，另外一个事务对同一行也做了更新，并且这个后来的事务是前一个事务启动之后启动的，但在前一个事务提交之前提交的，正常按照一致性视图来说，前一个事务在提交之前是看不见后一个的提交完成的事务的结果的，但是按照这个逻辑走的话，就会让后一个事务的记录丢失掉了。所以有当前读的这个规则。

锁

mysql中的锁根据锁范围可以分为全局锁，表锁，MDL锁，行锁。

全局锁

就相当于对整个库上锁，适用于不支持事务的引擎，Innodb引擎在可重复读级别下开启事务的话就会创建一个整个库的数据一致性视图，就不会采用全局锁的方式。全局锁的sql语句是Flush table with read lock(FTWRL),开启全局锁之后，对整个库的更新语句都会被阻塞，影响范围很大。

表级锁

表级锁有两种，一种是表锁，另一种是MDL锁

表锁

上锁的语句是lock table tableName read/write，解锁的语句是 unlock tables; 如果是线程1对表A上使用写锁，那么其他线程的读和写操作都会被阻塞，线程1的读写操作可以正常执行；但是如果线程1对表A使用的是读锁，那么不仅是其他线程的写操作被阻塞，线程1的写操作也会执行失败，并且线程1在未解锁之前，也不能对其他表进行读和写的操作

MDL锁

MDL锁是隐式使用的，并不需要显式的加锁和解锁，是自动的。在对表进行增删改查的时候，默认会加上MDL读锁；在修改表结构，增加删除列的时候会加上MDL写锁；读锁和读锁不会有冲突，读锁和写锁，写锁和写锁都是互斥的。

在对一个大表进行修改表结构的时候，也就是MDL写锁占用的时间比较长的话，这个时候的增删改查操作是会被阻塞的。
对一个小表进行修改表结构的时候也有可能会导致MDL读锁被阻塞：如果修改表结构的操作被阻塞了，就是尝试添加MDL写锁的时候，发现前一个sql开启了一个长事务或者其他耗时的操作，导致添加MDL读锁操作被阻塞，那么之后增删改查操作也会被阻塞。

因为长事务导致MDL锁阻塞的解决方法可以在mysql的information_schema库中的innodb_trx表关闭长事务，或者也可以在加MDL锁的操作语句，alter table上加上超时时间，超过设置时长自动放弃等待。

行锁

行锁就是针对表中的行数据，颗粒度更小。

两阶段锁协议

如果在一个事务中有会加锁的sql，锁并不是在开启事务的时候加上的，而是在执行需要加锁的sql开始的时候加上的，比如update语句，并且这个锁只有在提交事务的时候才会释放。所以尽量把带有并发冲突，也就是会加上锁的sql放在事务的结尾，这样可以避免长时间的加锁。

死锁

mysql中预防死锁的方法有： 等待超时，死锁检测。

• 等待超时就是在sql执行被阻塞的时间如果超过了设置的时长就会放弃执行，对应的参数是innodb_lock_wait_timeout,缺点是可能前一个sql就是执行的时间比较长，容易引起误杀。
• 死锁检测是在发生死锁之后，主动回滚其中一个事务，让其他事务能够继续执行下去，配置参数innodb_deadlock_detect参数为on表示开启死锁检测。

但是死锁检测会带来额外的消耗，每次发生锁等待的时候，就需要去判断是否是死锁，对于一个读少写多的表来说，会有很多时间浪费在判断是否是死锁上面，为了解决整个问题：

• 确定不会发生死锁，关闭死锁检测
• 使用中间件，控制对表的并发量
• 将对可能发生冲突的资源拆分，例如更新一条数据的数值字段，将这个数值字段再细分为更多维度的值，从而将更新同一行分为更新不同行。

其他

重建表

可能在执行完一个delete语句之后会发现，表的存储大小并没有减少。因为在删除行数据的时候，并不会将删除的空间给回收，而是将删除的行空间标记为已删除，这样如果下次其他数据插入到这个地方时候就可以复用这个空间。如果删除的是整个表的数据，那么整个表的数据页就是被标记为可复用。想要真正实现删除存储空间，需要的是重建表。可以执行alter table tabelName engine = Innodb; 具体内部的实现过程是新建一个临时文件，然后扫描原表，根据原表的所有数据页记录生成一个B+树，再将整个B+树存储到新建的临时文件中；在生成临时文件的过程中，会将所有对原表的操作记录日志记载到一个临时日志中；等生成的临时文件生成完毕之后，就会将临时日志的操作记录应用到临时文件中，逻辑上保证临时文件中的数据是和原表的数据是一致的，最后用临时文件中的数据文件替换原表之前的数据文件。这一系列操作都是在引擎层中完成的。

order by

在sql中如果有使用到了order by，并且排序字段没有索引，在explain分析sql的时候extra字段大概率就会有 using filesort的字样，表示需要进行排序；mysql会为线程分配一块用于排序的内存空间sort_buffer。

对于一个简单的sql比如 select name from tableName where sex = ‘woman’ limit 100 order by age,（并没有在age，sex字段加上索引），通常情况下的执行流程大概如下：

• 初始化sort_buffer，将age ，name字段放入到sort_buffer中。
• 全表扫描，每次取出一条数据判断sex是否满足条件
• 满足条件的数据行将name age 字段放入到sort_buffer中
• 一直重复上面的操作，直至取完所有数据
• 对sort_buffer中的age字段进行排序
• 取前100条数据

全字段排序

在上面的执行流程中，放入到sort_buffer中的是需要的所有字段，所以称为全字段排序，如果排序的数据量过大，在内存中无法进行，就需要使用到外部文件，这个就涉及到sort_buffer的空间大小，通过配置参数sort_buffer_size来决定，如果数据量小于sort_buffer_size就在内存中完成排序，如果排序的数据量大于sort_buffer_size,就使用磁盘临时文件来辅助排序。

rowid排序

在全字段排序中，会将需要的所有字段都放入到sort_buffer中，如果放入的字段很多，那么就会减少放入到sort_buffer中的行数。如果放入的字段大小超过了一定大小，max_length_for_sort_data参数，这个是mysql用来控制排序的行数据的长度，如果放入的字段大小超过了max_length_for_sort_data，就采用rowid排序。rowid和全字段排序的区别就是，放入到sort_buffer中的是rowid和age，也就是排序字段和主键id，这样就会提高放入sort_buffer中的数据行数。但是这样带来的缺点就是因为放入到sort_buffer中的只是主键id和age字段，查询字段确实name，就需要进行回表操作。

join何时用

如果join的字段可以走索引,就可以使用join；如果用不到索引，mysql会使用到Block Nested-Loop join,这样会扫描被驱动表，也就是会扫描大表全表多次，会占用大量的资源，不推荐使用。

MRR

在根据主键进行回表操作的时候，如果按照单个id进行查询会导致随机访问，所以mysql提供了一个配置，mrr_cost_based设置为off，就是固定使用Multi-Range Read优化，MRR就是采取尽量使用顺序读盘的方式提供效率。在回表的时候，会将id值先放入到read_rb_buffer中，所有满足条件的id都放放入到read_rb_buffer中之后再排序，根据排序好的id值去查询。

generated column

generated column可以指定一个列随着另外一个列的改变而改变，mysql有两种方式，一个是virtual generated column ,另一个是stored generated column,区别就是后者会将这个列持久化存储到磁盘上，mysql默认是virtual generated column方式存储的。

group by的使用相关

1. 尽量让group by的字段用上索引，否则会使用到临时表
2. 如果没有排序的要求，可以用group by后面加上order by null
3. 如果group by的数据量不大，可以用适当调大tmp_table_size的大小，直接使用内存临时表
4. 如果group by的数据量过大，可以使用SQL_BIG_RESULT提示让mysql直接使用排序算法