目录

• 基础架构
• 日志系统
• 事务
• 索引
• 全局锁与表锁
• 行锁与事务
• 关于事务隔离
• 普通索引和唯一索引
• mysql如何选择索引
• 字符串字段加索引
• 表文件大小
• 如何让count更快
• order by如何工作的
• join是怎么工作的
• group by及union如何工作的
• 临时表
• 分区与分表
• 数据库内存会不会爆
• 自增主键
• 读写分离的坑
• 为什么执行一条语句这么慢

本文是关于前阿里资深技术专家林晓斌（网名丁奇）著作《MySQL实战45讲》的笔记。

基础架构

• mysql逻辑架构图

  

• 两部分：server层与引擎层

• server层：

  • 连接器 权限相关
  • 查询缓存 缓存，不必要使用，8.0直接淘汰
  • 分析器 词法与语法分析器
  • 优化器 比如表顺序、索引使用、语句优化等
  • 执行器

• 引擎层：存储数据，提供读写接口

• 连接器：建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

• 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接。短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

• 怎么解决长连接导致内存大的问题呢？

  • 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
  • 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

• 查询缓存：大多数情况下我会建议你不要使用查询缓存，为什么呢？因为查询缓存往往弊大于利，sql查询往往数据在变，除非是静态表。也就是说我们和官方都不建议使用mysql内置缓存

• 分析器：词法分析、语法分析

• 优化器：比如选择索引，确定连接表顺序等，目的就是根据内置优化策略优化输入的sql语句，让直行效率更高，当然内置优化器并不是一直都是对的。

• 执行器：先判断权限，调用引擎层的接口进行查询或执行。

日志系统

• 与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

• MySQL里经常说到的WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

• 有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe 。

• MySQL整体来看，其实就有两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。

• redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog（归档日志）。

• redo log与bin log的区别：

  • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
  • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  • redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

• update的流程

  • 执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  • 执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  • 引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。
  • 执行器生成这个操作的binlog，并把binlog写入磁盘。
  • 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

• redo log的写入拆成了两个步骤：prepare和commit，这就是"两阶段提交”。简单说，redo log和binlog都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

• 恢复表思路

  • 首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；
  • 然后，从备份的时间点开始，将备份的binlog依次取出来，重放到中午误删表之前的那个时刻。

• redo log用于保证crash-safe能力。innodb_flush_log_at_trx_commit这个参数设置成1的时候，表示每次事务的redo log都直接持久化到磁盘。这个参数我建议你设置成1，这样可以保证MySQL异常重启之后数据不丢失。

• sync_binlog这个参数设置成1的时候，表示每次事务的binlog都持久化到磁盘。这个参数我也建议你设置成1，这样可以保证MySQL异常重启之后binlog不丢失。

事务

• ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔离性、持久性）

• 当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

• 隔离级别。隔离得越严实，效率就会越低。

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

• 在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。

• mysql的默认隔离级别 - 为什么Mysql不选择读已提交(Read Commited)作为默认隔离级别，而选择可重复读(Repeatable Read)作为默认的隔离级别呢

  5.0的之前binlog不支持row按行记录的历史原因。

• 查看隔离级别

  show variables like 'transaction_isolation';
  

• 在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）

• 为什么不建议使用长事务？长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

• 建议你总是使用set autocommit=1, 通过显式语句的方式来启动事务，在autocommit为1的情况下，用begin显式启动的事务，如果执行commit则提交事务。

索引

• 三种索引类型：哈希表、有序数组、搜索树

• 哈希表是一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。注意哈希冲突

• 哈希表这种结构适用于只有等值查询的场景 ，比如Memcached及其他一些NoSQL引擎。

• 有序数组在等值查询和范围查询场景中的性能就都非常优秀 ，有序数组索引只适用于静态存储引擎，比如历史数据。

• 二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

• 以InnoDB的一个整数字段索引为例，这个N差不多是1200。这棵树高是4的时候，就可以存1200的3次方个值，这已经17亿了（仅算非叶子结点。假设每行记录1k，一个page页16k，则每个叶子结点可以存储16行记录，总记录为17亿*16）。考虑到树根的数据块总是在内存中的，一个10亿行的表上一个整数字段的索引，查找一个值最多只需要访问3次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

• 每一个索引在InnoDB里面对应一棵B+树。

• 主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。

• 如果语句是select * from T where k=5，k是非聚簇索引，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。

• 首先自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。其次主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。所以，从性能和存储空间方面考量，自增主键往往是更合理的选择。

• 典型的KV场景适合用业务字段直接做主键。

• 覆盖索引：由于覆盖索引可以减少树的搜索次数，就是减少回表，直接在当前索引树下获取值，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

• 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。比如是否高频查询请求。

• 最左前缀：可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

• 如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。这个时候我们可以考虑下空间问题，如果a比b字段大，那么我们当然建议使用(a,b)联合索引，再建立一个b索引，避免出现两次a占用太多空间。

• 索引下推：首先需要5.6以上的版本才支持。索引下推就是在当前索引树下直接做判断，不需要再会表判断。

  mysql> select * from tuser where name like '张%' and age=10 and ismale=1;
  

  InnoDB在(name,age)索引树内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。不用回到主索引树再判断。

全局锁与表锁

• 根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁和行锁三类 。

• 全局锁：

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

  以前有一种做法，是通过FTWRL确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

• 不通过全局锁来备份

  官方自带的逻辑备份工具是mysqldump。当mysqldump使用参数–single-transaction的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。需要引擎支持事务及这个隔离级别。另外需要所有的表使用事务引擎。InnoDB替代MyISAM的原因之一。

• 表级数据锁：对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

• 表级结构锁：通常叫元数据锁MDL（metadata lock)。 MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

• MDL读写锁

  • 读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

• 注意：给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。

• 事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。所以要避免长事务。

行锁与事务

• MySQL的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如MyISAM引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁

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

• 行锁什么时候加上的？什么时候释放的？

  行锁在执行语句的时候加上的，在事务结束时才释放的。所以一个事务中，尽量将容易与其他线程冲突（锁互斥）的语句放在事务的最后面，减少对应行锁时间，减少其他线程的等待时间，提高并发。

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

  两个线程事务互相等待对方的释放锁，比如事务A锁住id=1的行做更新操作，紧接着事务B锁住id=2的行做更新操作，这个时候事务A又准备更新id=2的行，但id=2的行在事务B中还没有释放锁，事务A进入等待；事务B开始准备更新id=1的行，也发现id=1的行在事务A中还没有释放锁，事务B进入等，就这样事务A和事务B一直互相等待着对方释放锁。这就是进入死锁状态了。锁只有在事务结束后才会释放。

• 主动死锁检测，而且innodb_deadlock_detect的默认值本身就是on。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

  每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是O(n)的操作。假设有1000个并发线程要同时更新同一行，那么死锁检测操作就是100万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的CPU资源。因此，你就会看到CPU利用率很高，但是每秒却执行不了几个事务。

关于事务隔离

备注：没有读透，适合时隔一段时间再次沉下心阅读理解。

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

• 在可重复读隔离级别下，事务在启动的时候就“拍了个快照”。注意，这个快照是基于整库的。

• 快照怎么实现的？

  实际上，我们并不需要拷贝出这100G的数据。我们先来看看这个快照是怎么实现的。

  InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的。

  而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记为row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

  一个事务只需要在启动的时候声明说，“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是我启动以后才生成的，我就不认，我必须要找到它的上一个版本”。当然，如果“上一个版本”也不可见，那就得继续往前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。

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

• 一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

  • 版本未提交，不可见；
  • 版本已提交，但是是在视图创建后提交的，不可见；
  • 版本已提交，而且是在视图创建前提交的，可见。

• 更新逻辑之当前读：更新数据都是先读，后写，这个读只能是当前读，而不是事务视图读（事务开始时）。

  更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

  注意：除了update语句外，select语句如果加锁，也是当前读。（for update）

  一定要区别去事务中查询的一致性读视图。

• 可重复读的核心就是一致性读（consistent read）；而事务更新数据的时候，只能用当前读。如果当前的记录的行锁被其他事务占用的话，就需要进入锁等待。

• 读提交的逻辑和可重复读的逻辑类似，它们最主要的区别在于什么时候创建一致性读视图

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

普通索引和唯一索引

• InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。

• change buffer

  对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

  因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

• 唯一索引与普通索引

  • 对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

• change buffer 策略与web缓存策略

  change buffer的策略，有点类似于web应用的计数缓存机制，统计计数时，优先更新内存级缓存，访问计数时，从数据库获取数据并和缓存数据merge合并后返回真实统计数据。如果访问不是很频繁，可以考虑在一些特殊访问该统计数据时进行数据merge并写入库，也可以考虑后台定期merge并写入库，当然还可以根据业务特殊操作做特殊时期的merge并入库。

• redo log与change buffer

  change buffer在数据更新的时候，用于读取数据到内存做更新，或直接在内存更新数据。

  redo log，在事务更新或插入操作临时写入内存，定期写入磁盘

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

mysql如何选择索引

• 写SQL语句的时候，并没有主动指定使用哪个索引。也就是说，使用哪个索引是由MySQL来确定的。

• 将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中

  set long_query_time=0;
  

• 优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

• 扫描行数是怎么判断的？

  MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

  • 索引区分度

    这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。

    我们可以使用show index方法，看到一个索引的基数。

  • MySQL采样统计的方法

    采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

  • 使用普通索引时会把回表的代价算进去

  • analyze table t 命令，可以用来重新统计索引信息

• 选择索引

  mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
  

  按正常理论来说：

  如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索引上去查出每一行，然后根据字段b来过滤。显然这样需要扫描1000行。

  如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描50001行。

  但mysql有可能选择错索引，如果选择错了b作为索引，那要看是不是扫描行数有错，是否可以通过analyze table t来重新统计索引信息，,是否涉及临时表或排序，最后如果实在没有办法就还是直接手动force index(a)

• force index还是数据库内部调整优化

  其实使用force index最主要的问题还是变更的及时性。因为选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上force index。而是等到线上出现问题的时候，你才会再去修改SQL语句、加上force index。

  但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷。

  既然优化器放弃了使用索引a，说明a还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。 比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

  现在order by b,a 这种写法，要求按照b,a排序，就意味着使用这两个索引都需要排序。因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。

  当然有些语句实在改不动了，就考虑force index。

字符串字段加索引

• 使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

  mysql> alter table SUser add index index1(email);//不使用前缀索引
  或
  mysql> alter table SUser add index index2(email(6));//使用前缀索引
  

  字符串字段使用前缀索引，除了节省空间外，要注意避免存在太多前缀相同的行记录，尽可能的让前缀有足够的区分度，避免回表判断。

• 如果字符串使用了前缀索引就用不上覆盖索引对查询性能的优化了

  假设email字段最长是18字节，即使用以下方式定义前缀索引，引擎层仍然不确定是否完整，需要回表查询email字段信息

  alter table SUser add index index2(email(18));
  

  这也是你在选择是否使用前缀索引时需要考虑的一个因素。

• 如果对字符串前缀索引不清楚的话，建议不使用前缀索引。避免带来回表、不能使用覆盖索引等问题。

表文件大小

• 将innodb_file_per_table设置为ON，是推荐做法

  • 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
  • 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

  从MySQL 5.6.6版本开始，它的默认值就是ON了。

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

• delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。

  也就是说，通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

• 不止是删除数据会造成空洞，插入数据也会造成一些空洞

  插入数据导致页分裂。也就是说，经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

• 重建表

  可以使用 alter table A engine=InnoDB 命令来重建表。重建表后主键索引更紧凑，数据页的利用率也更高，也就收缩了表的空间。

  optimize table、analyze table和alter table这三种方式重建表的区别：

  • alter table t engine = InnoDB（也就是recreate）重建表；
  • analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；
  • optimize table t 等于recreate + analyze；

如何让count更快

• 对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。MySQL优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

• show table status命令显示的行数也不能直接使用。

  索引统计的值是通过采样来估算的。实际上，TABLE_ROWS就是从这个采样估算得来的，因此它也很不准。有多不准呢，官方文档说误差可能达到40%到50%。

• 所以：

  • MyISAM表虽然count(*)很快，但是不支持事务；
  • show table status命令虽然返回很快，但是不准确；
  • InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

• 如何获取准确的count呢

  建议：你需要自己找一个地方，把操作记录表的行数存起来。快速准确但不简单，也就是如果要保证很准确且数据不丢失的话，要有很多完善环节。

• 不同的count方式

  • count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；

  • count(*)是例外 ，并不会把全部字段取出来，而是专门做了优化，不取值。count(*)肯定不是null，按行累加。

  • 对于count(主键id)来说 ，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。server层拿到id后，判断是不可能为空的，就按行累加。

  • 对于count(1)来说 ，InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

  • 而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

  所以结论是：按照效率排序的话，count(字段)< count(主键) < count(1) < count(*)，所以我们的建议是尽量使用count(*)

order by如何工作的

• 全字段排序

  select city,name,age from t where city='杭州' order by name limit 1000 ;
  

  • 初始化sort_buffer，确定放入name、city、age这三个字段；
  • 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
  • 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
  • 从索引city取下一个记录的主键id；
  • 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
  • 对sort_buffer中的数据按照字段name做快速排序；
  • 按照排序结果取前1000行返回给客户端。

  万变不离其宗：搜索->回表->获取行数据->放入sort_buffer->快速排序->取limit

• sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

• 外部排序：内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。可以这么简单理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。然后把这12个有序文件再合并成一个有序的大文件。

• max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法，也就是不再采用全字段排序方法

• rowid排序方法

  • 初始化sort_buffer，确定放入两个字段，即name和id；
  • 从索引city找到第一个满足city='杭州’条件的主键id，也就是图中的ID_X；
  • 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
  • 从索引city取下一个记录的主键id；
  • 重复步骤3、4直到不满足city='杭州’条件为止，也就是图中的ID_Y；
  • 对sort_buffer中的数据按照字段name进行排序；
  • 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

  比起全字段排序方法，rowid排序方法多了最后一步的回表取必要的字段，但换取了sort_buffer的空间。

• 两种排序方法的比较

  如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

  如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

  这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

  对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。

• 通过索引优化order by

  其实，并不是所有的order by语句，都需要排序操作的。

  从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

  如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序的话，那就不需要额外进行排序了。

  alter table t add index city_user(city, name);
  

  建立索引之后，order by本身就不需要再进行排序了，直接查询到满足条件的记录即可：

  • 从索引(city,name)找到第一个满足city='杭州’条件的主键id；
  • 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
  • 从索引(city,name)取下一个记录主键id；
  • 重复步骤2、3，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

  使用索引优化了排序，也减少了搜索扫描记录数，不再全索引树扫描。

• 覆盖索引进一步优化

  由于需要返回city、name、age，如果针对这个业务逻辑，我们还可以为这三个字段创建联合索引，充分使用索引减少扫描数，使用索引让字段天然有序，使用覆盖索引避免回表：

  alter table t add index city_user_age(city, name, age);
  

  • 从索引(city,name,age)找到第一个满足city='杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
  • 从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
  • 重复执行步骤2，直到查到第1000条记录，或者是不满足city='杭州’条件时循环结束。

  注意：我们有个前提的，如果查询city、name、age的业务逻辑是高频的操作，我们才有考虑这样的联合索引，毕竟索引的维护也是有代价的。

• order by rand()

  建议不要使用

  order by rand() 使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

  从原表（InnoDB）获取所有数据到内存临时表（memory），再从内存临时表取相应的值到sort buffer中进行排序，排序完成后，回到内存临时表取出满足条件的行的所需字段值，返回结果。

• InnoDB会优先选择全字段排序

  对于InnoDB来说，优先选择全字段排序会减少磁盘的访问，所以相对于rowid排序，InnoDB会优先选择全字段排序

• memory内存表会优先选择rowid排序

  对于内存表，从sort buffer排序后回表获取查询所需的更多字段值时，不需要访问磁盘，直接访问内存就可以得到数据，因为数据本身就是在内存中。

• rowid

  mysql的引擎都有个6个字节的rowid来标识数据行的唯一性，如果创建的表没有主键，那么InnoDB将会自己生成rowid作为主键。对于memory引擎，memory引擎不是索引组织表，这个rowid就是表数组的一个下标。

• 硬盘临时表

  mysql配置内存临时表大小，默认值是16M，如果临时表大小超过配置大小，就会使用磁盘临时表。

  磁盘临时表使用的是InnoDB引擎，内存临时表使用的是memory引擎。

• 好的随机取值方法

  如果order by rand的效果会越来越不理想，我们就会想到在业务逻辑层给出随机值后，再由mysql读取即可；

  首先，取出表的id最小值min和最大值max，通过随机函数rand(min,max)获取一个随机值R，mysql查询id=R的一条记录

  其次，看起来好像效率很好，但万一表中id不连续，也就是存在空洞记录的时候，就会返回空。可以在考虑查询id>=R limit 1的方式获取

  再次，也是由于空洞的原因，所以并不是真正的随机，业务逻辑层的随机前提是min和max间的数值都是存在且等概率的，但如果存在空洞记录的时候概率就有了偏差

  再再次，考虑获取表行数C，获取rand(1,C)一个随机整数R，通过limit R,1可以真实获取到记录且保证等概率，但这个limit R,1 实际上总共对表做了C+R+1行的扫描（limit需要扫描R行后，再扫描下一行并取出返回），效率比不上上一个策略

  最后，我偏向于使用业务逻辑生成随机R，mysql查询id=R的记录，效率好，前提是表主键id连续，不存在空洞，可以做到的。

join是怎么工作的

• B+树搜索时间复杂度：假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M

• join一般查找流程

  假设t1表100行记录，t2表1000行记录。t2.a创建索引。

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

  如果使用join，则mysql优化器会选择使用哪个表作为驱动表，而不是单纯从sql语句确认哪个表在前面谁就是驱动表，这个涉及到索引等考虑。

  • 从表t1中读入一行数据 R；
  • 从数据行R中，取出a字段到表t2里去查找；(这里先不细看如何在t2里查找)
  • 取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  • 重复执行步骤1到3，直到表t1的末尾循环结束。

  如果t2.a字段创建了索引，那么mysql优化器会使用到这个索引，则这个语句的更完整流程是这样的：

  • 从表t1中读入一行数据 R；
  • 从数据行R中，取出a字段到表t2，通过索引树a，进行查找对应的主键id；
  • 回表取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；
  • 重复执行步骤1到3，直到表t1的末尾循环结束。

  我们看下总共扫描多少行：

  • 对驱动表t1做了全表扫描，这个过程需要扫描100行；
  • 而对于每一行R，根据a字段去表t2查找，走的是树搜索过程。由于我们构造的数据都是一一对应的，因此每次的搜索过程都只扫描一行，也是总共扫描100行；
  • 所以，整个执行流程，总扫描行数是200。

  流程小结：

  • 驱动表条件查找，返回一行记录R
  • 通过on字段去被驱动表查找相应条件的记录。可以直接在聚簇索引上查找，也可以在非聚簇索引树上查找再回表，取得相应的行，并跟R组成一行，作为结果集的一行
  • 重复以上步骤，直到驱动表条件遍历结束

• 可用到被驱动表的索引时，使用join语句，性能比强行拆成多个单表执行SQL语句的性能要好；如果使用join语句的话，需要让小表做驱动表。

  在这个join语句执行过程中，驱动表是走全表扫描，而被驱动表是走树搜索。

  假设被驱动表的行数是M。每次在被驱动表查一行数据，要先搜索索引a，再搜索主键索引。每次搜索一棵树近似复杂度是以2为底的M的对数，记为log2M，所以在被驱动表上查一行的时间复杂度是 2*log2M。

  假设驱动表的行数是N，执行过程就要扫描驱动表N行，然后对于每一行，到被驱动表上匹配一次。

  因此整个执行过程，近似复杂度是 N + N*2*log2M。

• 被驱动表没有索引时，且join_buffer足够大时，哪个表做驱动表影响不大

  • 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；
  • 扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。

• 被驱动表没有索引，且join_buffer不够大时，尽量选择小表作为驱动表

  要是驱动表是一个大表，join_buffer放不下怎么办呢？

  join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

• 那到底能不能用join语句呢？

  • 如果可以使用Index Nested-Loop Join算法，也就是说可以用上被驱动表上的索引，其实是没问题的；
  • 如果使用Block Nested-Loop Join算法，也就是说被驱动表没有索引可以使用时，扫描行数就会过多。尤其是在大表上的join操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

  所以你在判断要不要使用join语句时，就是看explain结果里面，Extra字段里面有没有出现“Block Nested Loop”字样。

• 如果要使用join，应该选择大表做驱动表还是选择小表做驱动表？

  • 如果是Index Nested-Loop Join算法，应该选择小表做驱动表；
  • 如果是Block Nested-Loop Join算法：
    • 在join_buffer_size足够大的时候，是一样的；
    • 在join_buffer_size不够大的时候（这种情况更常见），应该选择小表做驱动表。

  所以，这个问题的结论就是，总是应该使用小表做驱动表。

• 那如何定义小表呢？

  更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

  所以我们应该尽量补充过滤条件，尽量让可能是驱动表的查找结果足够小。

  select * from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=50;
  select * from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=50;
  

  两表的b字段都没有索引，在存在条件t2.id<50的情况下，显然t2只返回50条结果集，相比较于t1全表100条记录来说，t2可以算做小表。

  select t1.b,t2.* from  t1  straight_join t2 on (t1.b=t2.b) where t2.id<=100;
  select t1.b,t2.* from  t2  straight_join t1 on (t1.b=t2.b) where t2.id<=100;
  

  虽然t2在条件t2.id<=100下，扫描数和t1全表扫描一样，但t2返回了全部字段，而t1只返回了b字段，显然t1返回的结果集会更小些（当然要具体看实际业务表的字段长度等情况），所以选择t1作为驱动表会更好。

• 小结

  • 1. 如果可以使用被驱动表的索引，join语句还是有其优势的；
  • 2. 不能使用被驱动表的索引，只能使用Block Nested-Loop Join算法，这样的语句就尽量不要使用；
  • 3. 在使用join的时候，应该让小表做驱动表。

group by及union如何工作的

• union

  (select 1000 as f) union (select id from t1 order by id desc limit 2);
  

  union这个语句的执行流程是这样的：

  创建一个内存临时表，这个临时表只有一个整型字段 f，并且 f 是主键字段。

  执行第一个子查询，得到 1000 这个值，并存入临时表中。

  执行第二个子查询：拿到第一行 id=1000，试图插入临时表中。但由于 1000 这个值已经存在于临时表了，违反了唯一性约束，所以插入失败，然后继续执行；取到第二行 id=999，插入临时表成功。

  从临时表中按行取出数据，返回结果，并删除临时表，结果中包含两行数据分别是 1000 和 999。

• union all

  如果把上面这个语句中的 union 改成 union all 的话，就没有了“去重”的语义。这样执行的时候，就依次执行子查询，得到的结果直接作为结果集的一部分，发给客户端。因此也就不需要临时表了。

• group by语句的执行流程是这样的

  以以下sql为例：

  select id%10 as m, count(*) as c from t1 group by m;
  

  • 创建内存临时表，表里有两个字段 m 和 c，主键是 m；
  • 扫描表 t1 的索引 a，依次取出叶子节点上的 id 值，计算 id%10 的结果，记为 x；
    • 如果临时表中没有主键为 x 的行，就插入一个记录 (x,1);
    • 如果表中有主键为 x 的行，就将 x 这一行的 c 值加 1；
  • 遍历完成后，再根据字段 m 做排序，得到结果集返回给客户端。

• group by优化

  • 如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；group by 分组后默认自然顺序排序。如果不想排序，那就order by null。
  • 尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
  • 如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
  • 如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

临时表

• 内存表与临时表

  内存表是引擎为memory的表，临时表可以使用各种引擎类型，比如采用InnoDB，写数据的时候写入磁盘，可以用memory引擎，写入内存。

• 临时表特性

  临时表在使用上有以下几个特点：

  • 建表语法是 create temporary table …。
  • 一个临时表只能被创建它的 session 访问，对其他线程不可见。所以，图中 session A 创建的临时表 t，对于 session B 就是不可见的。
  • 临时表可以与普通表同名。
  • session A 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表。
  • show tables 命令不显示临时表。

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

• 临时表使用场景

  由于不用担心线程之间的重名冲突，临时表经常会被用在复杂查询的优化过程中。其中，分库分表系统的跨库查询就是一个典型的使用场景，把各个分库拿到的数据，汇总到一个 MySQL 实例的一个表中，然后在这个汇总实例上做逻辑操作。

  比如：

  比如。将一个大表 ht，按照字段 f，拆分成 1024 个分表，然后分布到 32 个数据库实例上。

  现在要查询：

  select v from ht where k >= M order by t_modified desc limit 100;
  

  思路：

  • 在汇总库上创建一个临时表 temp_ht，表里包含三个字段 v、k、t_modified；

  • 在各个分库上执行

    select v,k,t_modified from ht_x where k >= M order by t_modified desc limit 100;
    

  • 把分库执行的结果插入到 temp_ht 表中；

  • 执行

    select v from temp_ht order by t_modified desc limit 100; 
    

分区与分表

• 分区与用户手工分表

  分区表和手工分表，一个是由 server 层来决定使用哪个分区，一个是由应用层代码来决定使用哪个分表。因此，从引擎层看，这两种方式也是没有差别的

• 不建议使用分区的原因

  • MySQL 在第一次打开分区表的时候，需要访问所有的分区；
  • 在 server 层，认为这是同一张表，因此所有分区共用同一个 MDL 锁；
  • 在引擎层，认为这是不同的表，因此 MDL 锁之后的执行过程，会根据分区表规则，只访问必要的分区。

  实际使用时，分区表跟用户分表比起来，有两个绕不开的问题：

  • 一个是第一次访问的时候需要访问所有分区
  • 另一个是共用 MDL 锁

  因此，如果要使用分区表，就不要创建太多的分区。我见过一个用户做了按天分区策略，然后预先创建了 10 年的分区。这种情况下，访问分区表的性能自然是不好的。

  这里有两个问题需要注意：

  • 分区并不是越细越好。实际上，单表或者单分区的数据一千万行，只要没有特别大的索引，对于现在的硬件能力来说都已经是小表了。
  • 分区也不要提前预留太多，在使用之前预先创建即可。比如，如果是按月分区，每年年底时再把下一年度的 12 个新分区创建上即可。对于没有数据的历史分区，要及时的 drop 掉。

• 分区的使用场景

  分区表的一个显而易见的优势是对业务透明，相对于用户分表来说，使用分区表的业务代码更简洁。还有，分区表可以很方便的清理历史数据。

数据库内存会不会爆

由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。

所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。

而对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。

并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。

当然，我们前面文章有说过，全表扫描还是比较耗费 IO 资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。

自增主键

• 在 MySQL 5.7 及之前的版本

  自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

  举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。

• 在 MySQL 8.0 版本

  将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

• 自增主键的生成性能如何

  这个需要测试一下，数据库的自增主键也用做生成唯一数字，作为其他单号，比如：并发量小的订单号，性能可能一般。

• 自增主键有最大值吗？如果有，到了怎么办？

  最大值应该有，因为数字总有个范围，到了当做字符串的一部分，然后再自增拼接上另一部分，貌似也可以。

• 自增主键的作用？保存机制？修改机制？

  作用：让主键索引尽量地保持递增顺序插入，避免页分裂，使索引更紧凑。

• 表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

• row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。

读写分离的坑

• 读写分离的基本结构

  • 客户端直连，由客户端做负载均衡

  • 中间proxy架构

• 过期读解决方案

  • 强制走主库方案（客户端查询请求分类连接）
  • sleep 方案（主库更新后，读从库之前先 sleep 一下）
  • 判断主备无延迟方案
  • 配合 semi-sync 方案（引入半同步复制）
  • 等主库位点方案
  • 等 GTID 方案

其实，在实际应用中，这几个方案是可以混合使用的。比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。但话说回来，过期读在本质上是由一写多读导致的。在实际应用中，可能会有别的不需要等待就可以水平扩展的数据库方案，但这往往是用牺牲写性能换来的，也就是需要在读性能和写性能中取权衡。

为什么执行一条语句这么慢

• 查询长时间不返回

  正常查询长时间不返回，首先考虑查询语句是否正常，如果简单到select * from t limit 1;都长时间不返回，意味着可能表t被锁住了。可以通过show proceelist查看相关线程信息。

• 等mdl锁

  mdl锁是表锁，在curd的时候，都会加上mdl锁，

• 等行锁 record lock

  一个update的事务加了写锁，但一直没有提交事务的情况下，另一个会话查询该行数据时，会出现读锁与写锁冲突，进入等待