MySQL

MySQL Notes

MySQL优化

优化查询效率

SQL语句层面：

select和join的先后顺序，一般先select后join
使用Explain命令
- 作用：查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用想要的索引，有没有做全表扫描
- 可能统计出的行数和实际不一样。因为MySQL中数据的单位都是页，MySQL又采用了采样统计的方法，采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值。数据是一直在变的，所以索引的统计信息也是会变的，只是一个估值。另外，优化器走错索引，还可能是因为考虑其他因素，比如回表等等
- 如果发现错误，可以使用analyze table tablename就可以重新统计索引信息；或者force index，强制走正确的索引
使用pt-query-digest，也可以分析慢查询

索引层面：

覆盖索引
- 回表问题：先通过普通索引定位到主键值，再通过聚集索引定位到行记录，性能较扫一遍索引树低很多
- 用于解决回表问题，在一棵索引树上就能获取SQL所需的所有列数据，无需回表
联合索引
- 覆盖索引的一种实现，在两个或更多个列上建立索引
- 优点
  - 联合索引比起单列分别建索引，减少了索引的数量，降低了写开销和磁盘开销
  - 效率高，多列索引能够一次性筛选出想要的数据，避免中间的回表、排序、分页等过程
- 注意调整联合时的顺序，查询语句执行时会从优先从左开始组成索引进行数据匹配（最左前缀原则）
- 可以使用explain语句，查看type字段是index还是ref，判断索引是否达到快速效果
  - index：效率不高，MySQL从索引中的第一个数据查到最后一个，直到找到符合判断的某个索引
  - ref：平常理解的“用索引快速找到数据”，索引字段必须有序
- 不可以无限建立，过多的联合索引还是占据很多空间
```
  /* 假如索引存在于(col1, col2, col3)，则索引生效于索引会生效于(col1), (col1, col2), 以及(col1, col2, col3) */

  /* 索引生效，触发ref  */
  SELECT * FROM tbl_name WHERE col1=val1;
  SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

  /* 索引不生效，(col2)和(col2, col3) 不是(col1, col2, col3)的最左前缀，触发index */
  SELECT * FROM tbl_name WHERE col2=val2;
  SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
```
索引下推
- MySQL 5.6之后的官方优化
- 如果使用了联合索引，在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
索引维护
- 索引以数据页的形式存在磁盘上
- 页分裂：插入一条数据需要写数据页，如果数据页满了，且插入位置在中间，那么存储引擎要申请一个新的数据页，把部分数据移动过去。数据插在尾部没有这个问题
- 页合并：删除一条数据，当页的利用率很低之后，就会合并页
- 避免页空洞：页分裂过程会产生“页空洞”，因为两个页都不满（分裂后只用50%），但是占用了两页的磁盘空间，因此不能进行页分裂
根据使用场景，分析普通索引和唯一索引选择的合理性
- 唯一索引上的字段的数据只能是唯一的，而普通索引可以有重复数据
- 唯一索引不能使用change buffer（因为需要进行唯一性检查），普通索引可以使用

表层面：

检查是否满足范式，减少冗余
选择合适的数据类型，比如说int比varchar处理起来更简单，少用NULL因为会增加索引开销

更高级的层面：

修改MySQL配置，例如查询缓存、索引缓冲区这种
硬件优化，分析CPU、硬盘、网络的性能瓶颈等

数据量大

优化SQL和索引
增加一层缓存，比如Redis或者Memcached
做主从同步
- 一个数据库服务器异步复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。master主服务器发生变化时，变化会实时同步到slave服务器
- 优点：水平拓展数据库的负载能力；数据备份，提升了容错性；读写分离，比如master负责写，slave负责读，避免锁表带来不利影响，同时分担数据访问压力
- 主机器上，主从同步事件会被写到binlog文件中(statement: 操作数据库的SQL语句；row: 每条数据的变化；mixed: statement与row的混合)。slave连接master节点后，master节点会创建一个binlog dump线程，用于发送bin log
- 把slave的线程分成两个线程，一个做binlogs的同步(我们称为I/O线程)，接受主服务器binlog dump进程发来的更新之后，保存在本地的relay log中；另一个做还原现场的工作(我们称为SQL线程)，读取relay log的内容，解析成具体的操作来执行。如此，master端的binlogs能以最小的延时，同步到slave，而slave这边的现场还原工作就可以慢慢来
- 默认情况下主库在执行完客户端提交的事务后会将结果返给给客户端，之后立即开始继续处理其他客户提交操作，并不关心从库是否已经接收并处理（优化：半同步复制，主库线程等待至少一个从库反馈，再进行后续操作）
- 有时主库tps并发较高，产生的DDL数量超过slave一个sql线程所能承受的范围；或slave的一些大型query语句产生了锁等待。这时就会产生主从延迟
  - 对于写了之后立马就要保证可以查到的场景，采用强制读主库的方式，这样就可以保证读到数据
  - 适当降低slave库的数据安全等级，比如说把innodb_flushlog等参数设置为0，提高sql语句执行效率
  - 使用多台slave来分摊读请求，再从这些slave中取一台专用的服务器，只作为备份用，不进行其他任何操作，就能相对最大限度地达到实时的要求
  - 使用比主库更好的硬件作为slave
做垂直拆分，分析模块的耦合度，把一个大的系统拆分成多个小的系统，按照系统业务进行分类，把数据压力分布到不同的数据库上面，即分布式数据库
做水平拆分
- 某个字段为依据（例如uid），按照一定规则（例如取模），将一个库（表）上的数据拆分到多个库（表）上，以降低单库（表）大小
- 拆分后，每个库（表）结构一样，但数据不一样，没有交集

锁

按加锁范围划分

简单来说，MySQL中有三种加锁范围不同的锁：

表级锁：开销小，加锁快；锁粒度大；发生锁冲突概率最高，并发度最低
页面锁，开销和加锁时间介于表锁和行锁之间；锁粒度中等
行级锁：开销大，加锁慢；锁粒度小；发生锁冲突概率最低，并发度最高

如果要进一步详细划分：

全局锁
- 对整个数据库实例加锁，使得整个库处于只读状态
- 典型的应用场景是做全库的逻辑备份，全局锁确保没有其他线程对当前数据库做更新，然后再对整个库做备份
表锁
- 有读写两种模式，可以直接LOCK TABLE table1 locktype(read/write)来指定
- 读锁不互斥，写锁互斥
行锁
- 多个事务操作同一行数据时，后来的事务处于阻塞等待状态，但此时他们可以操作其他行数据
- 一般行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，因为需要请求大量的锁资源，速度慢，内存消耗大；并且需要等待事务完整执行完毕后才能释放
- 行锁可能会引起死锁问题，比如两个不同事务分别持有了一把锁，然后试图加对方的另一把锁，产生死锁
  - 通过 innodb_lock_wait_timeout 来设置超时时间，InnoDB 中默认值是 50s，即第一个被锁住的事务等待超过 50s 才会超时退出，其他事务才能得以执行
  - 通过设置 innodb_deadlock_detect = on，发起死锁检测，发现死锁之后主动回滚死锁链条中的某一个事务，让其他事务得以继续执行
- 热点行问题：若干事务短时间内都要集中更新同一行，每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，消耗大量CPU
  - 临时关闭若干线程
  - 控制数据库并发度
  - 分治，考虑通过将一行改成逻辑上的多行来减少锁冲突

按模式划分

MySQL中有两种不同模式的锁：

乐观锁用数据版本（Version）记录机制实现；当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。注意如果产生了冲突，上层应用可能会多次重试执行，这导致性能降低，因此乐观锁更适合写入较少的场景。
悲观锁悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，需要耗费较多的时间。有两种常见的实现方法：
- 共享锁：共享锁又称读锁 (read lock)，多个事务对于同一数据可以共享一把锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到所有共享锁被释放。
- 排他锁：排他锁又称写锁（write lock），若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取，不能进行写操作，需等待其释放

其他类型的锁

间隙锁：

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。这样做是为了防止幻读，以及利于后续数据恢复

事务

MySQL并发事务产生的问题

丢失更新
- 第一类：A事务撤销，把已经提交的B事务的更新数据覆盖了
- 第二类：A事务提交，把已经提交的B事务的更新数据覆盖了
脏读
- 读到未提交更新的数据
不可重复读
- 读到已经提交更新的数据，但一个事务范围内两个相同的查询返回了不同数据
幻读
- 读到另一个事务提交新插入/删除的数据

MySQL事务隔离级别

以下四种隔离级别按从高到底排序，四种隔离级别的安全性与性能成反比，最安全的性能最差，最不安全的性能最好。

SERIALIZABLE（串行化）一个事务在执行过程中完全看不到其他事务对数据库所做的更新。当两个事务同时操作数据库中相同数据时，如果第一个事务已经在访问该数据，第二个事务只能停下来等待，必须等到第一个事务结束后才能恢复运行。可以解决上述所有并发事务问题。
REPEATABLE READ（可重复读）(MySQL的默认事务隔离级别) 一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他事务对已有记录的更新。可以解决除幻读之外所有的并发事务问题。
READ COMMITTED（读已提交数据）当数据库系统使用READ COMMITTED隔离级别时，一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且还能看到其他事务已经提交的对已有记录的更新。可以解决第一类丢失更新和脏读问题。
READ UNCOMMITTED（读未提交数据）一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且还能看到其他事务没有提交的对已有记录的更新。可以解决第一类丢失更新问题。

MySQL中与事务操作相关的两种日志？

重做日志(redo log)
- 确保事务的持久性，防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做
- 物理格式的日志，记录的是物理数据页面的修改的信息
- 事务开始之后就产生redo log，redo log的落盘并不是随着事务的提交才写入的，而是在事务的执行过程中，便开始写入redo log文件中；对应事务的脏页写入到磁盘之后，redo log的使命也就完成了，其占用的空间就可以被覆盖
回滚日志(undo log)
- 保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC）
- 逻辑格式的日志，在执行undo的时候，仅仅是将数据从逻辑上恢复至事务之前的状态，而不是从物理页面上操作实现
- 事务开始之前，将当前是的版本生成undo log；当事务提交之后，undo log并不能立马被删除，而是放入待清理的链表，由purge线程判断是否由其他事务在使用undo段中表的上一个事务之前的版本信息，决定是否可以清理undo log的日志空间
- MySQL 5.6之前，undo文件是保持在共享表空间的，这个共享表空间是不会也不能自动收缩的，所以即便回滚了版本，文件也不会变小。之后的版本支持把undo表空间配制成了独立文件，修复了这一问题

MVCC

简介

MVCC（multiversion concurrency control），多版本并发控制，主要是通过在每一行记录中增加2个字段，实现一个版本链：

DB_TRX_ID：6个字节，表示最近一次修改本记录的事务ID
DB_ROLL_PTR：7个字节，回滚指针，指向回滚段中的undo log record，用于找出这个记录的上个修改版本的数据

MVCC与undo log中相关记录配合使用，加上可见性算法，使得各个事务可以在不加锁的情况下能够同时地读取到某行记录上的准确值，提高了并发效率。

不同事务隔离级别下的工作

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。其他两个隔离级别都和MVCC不兼容，因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行，而SERIALIZABLE会对所有读取到的行都加锁。

对于另两个级别，二者的主要区别是ReadView生成策略不同。ReadView中有个列表来存储系统中当前活跃的读写事务，也就是begin了还未提交的事务，通过这个列表来判断记录的某个版本是否对当前事务可见。最主要的与可见性相关的属性如下：

up_limit_id：当前已经提交的事务号 + 1，事务号 < up_limit_id ，对于当前Read View都是可见的。理解起来就是创建Read View视图的时候，之前已经提交的事务对于该事务肯定是可见的。
low_limit_id：当前最大的事务号 + 1，事务号 >= low_limit_id，对于当前Read View都是不可见的。理解起来就是在创建Read View视图之后创建的事务对于该事务肯定是不可见的。
trx_ids：为活跃事务id列表，即ReadView初始化时当前未提交的事务列表。所以当进行RR读的时候，trx_ids中的事务对于本事务是不可见的（除了自身事务，自身事务对于表的修改对于自己当然是可见的）。理解起来就是创建RV时，将当前活跃事务ID记录下来，后续即使他们提交对于本事务也是不可见的。

对于REPEATABLE READ，只有第一次读的时候会生成ReadView，之后的读都是复用这个ReadView；对于READ COMMITTED，每次查询都会生成一个独立的ReadView。

存储引擎(MySQL)

InnoDB

MySQL 5.5之后的默认引擎
使用B+树作为索引结构，表数据文件本身就是索引，叶节点data域保存了完整的数据记录。索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
索引分为两层，一层是主键索引，按照主键构建一棵B+树，只要建立了主键就会自动加上索引，叶子节点上存放着整张表的行记录数据；另一层是普通索引，叶子结点没有存放行记录的全部数据，只包含了需要的主键值，以及一个标签告诉存储引擎在哪里可以找到这行数据。

MyISAM

MySQL 5.5之前的默认引擎
使用B+树作为索引结构，叶节点的data域存放的是数据记录的地址。
索引也是两层结构，与InnoDB类似，但是普通索引存储的是行记录的头指针
基本只适合频繁查询（读）的表，因为MyISAM锁是表锁，只有读读之间是并发的，写写之间和读写之间都是串行的，所以写起来慢

两个引擎的主要区别和选择

InnoDB是聚簇索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，否则其他索引也会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的
InnoDB支持外键，而MyISAM不支持
InnoDB锁粒度是行锁，而MyISAM是表锁
InnoDB实现了缓冲管理，不仅能缓冲索引也能缓冲数据，并且会自动创建散列索引以加快数据的获取
InnoDB支持事务，MyISAM不支持。对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL放在begin和commit之间，组成一个事务
InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数
主机宕机后，InnoDB表恢复性更好，MyISAM表易损坏

其他引擎

MEMORY引擎，提供内存表，不支持事务和外键。显著提高访问数据的速度，可用于缓存会频繁访问的、可以重构的数据、计算结果、统计值、中间结果。

其他问题

Change Buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

change buffer适合写多读少的场景（账单、日志…），页面写完后马上被访问的概率很小，对机械硬盘而言性能提升尤其明显。相反，如果更新后来紧接着查询操作，可以考虑关掉 change buffer。

自增ID用尽

默认情况下，AUTO_INCREMENT ID的最大值是4294967295，如果想再尝试插入一条新数据，ID将不会继续增长，会导致主键冲突。因此如果数据很多时，自增ID应该尽量使用bigint unsigned，范围是 -2^63 到 2^63 - 1

如果创建表没有显示申明主键，InnoDB会自动创建一个不可见的、长度为6字节的row_id，且InnoDB 维护了一个全局的 dictsys.row_id，所有未定义主键的表都共享该row_id，每次插入一条数据，都把全局row_id当成主键id，然后全局row_id加1。虽然row_id在代码实现上使用的是bigint unsigned类型，但因为其实际长度只有6字节，因此row_id超过2^48时就会溢出，存在主键冲突可能。

另外，MySQL 保存了一个全局变量 thread_id_counter，每新建一个连接，就将 thread_id_counter 复制给这个新连接的线程变量。thread_id_counter 定义的大小是 4 个字节，因此达到 2^32 - 1 后，它就会重置为 0，然后继续增加。

PREVIOUS火焰纹章风花雪月四线心得

NEXT数据库理论