目录

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

基础架构

  • mysql逻辑架构图

    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亿了。考虑到树根的数据块总是在内存中的,一个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语句还是有其优势的;
      1. 不能使用被驱动表的索引,只能使用Block Nested-Loop Join算法,这样的语句就尽量不要使用;
      1. 在使用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的事务加了写锁,但一直没有提交事务的情况下,另一个会话查询该行数据时,会出现读锁与写锁冲突,进入等待