6、MVCC

MVCC：

1、全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的理念，维持一个数据的多个版本，使得读写操作没有冲突。

2、MVCC在MySQL InnoDB中实现目的主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

MySQL InnoDB下的当前读和快照读

⦁ 当前读

1、像select lock in share mode(共享锁)、select for update 、update、insert、delete(排他锁)这些操作都是一种当前读，就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

2、当前读可以认为是悲观锁的具体功能实现

⦁ 快照读

1、不加锁的select就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC，可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

2、快照读就是MVCC思想在MySQL的具体非阻塞读功能实现，MVCC的目的就是为了实现读-写冲突不加锁，提高并发读写性能，而这个读指的就是快照读。

3、快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。

因为大佬不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出了MVCC，所以我们可以形成两个组合：

MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突

MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突

MVCC的实现原理

MVCC实现原理主要是依赖记录中的 四个隐式字段、undo日志 、Consistent Read View来实现的。

四个隐式字段：

⦁ DB_TRX_ID：

6byte，最近修改(修改/插入)事务ID：记录创建这条记录/最后一次修改该记录的事务ID

⦁ DB_ROLL_PTR

7byte，回滚指针，指向这条记录的上一个版本（存储于rollback segment里）

⦁ DB_ROW_ID

6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引

⦁ FLAG

一个删除flag隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除flag变了

事务对一条记录的修改，会导致该记录的undo log成为一条记录版本线性表(链表)，undo log的链首就是最新的旧记录，链尾就是最早的旧记录。

undo日志：此知识点上文已经说过了，对MVCC有帮助的实质是update undo log，undo log实际上就是存在rollback segment中旧记录链。

一致读视图 Consistent Read View：Read View是事务进行快照读操作的时候生产的读视图(Read View)，在该事务执行的快照读的那一刻，会生成数据库系统当前的一个快照，记录并维护系统当前活跃事务的ID(InnoDB里面每个事务有一个唯一的事务ID，叫作transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按申请顺序严格递增的)。拿着这个ID跟记录中ID对比进行选择性展示，这里说下大致的思维。

你可以简单的理解为MVCC为每一行增加了两个隐藏字段，两个字段分别保存了这个行的当前事务ID跟行的删除事务ID。

⦁ insert时：

InnoDB为新插入的每一行保存当前系统版本号作为版本号。

⦁ select时：

1、 InnoDB只会查找版本早于当前事务版本的数据行(也就是行的系统版本号<=事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

2、行的删除版本要么未定义，要么大于当前事务版本号，这可以确保事务读取到的信息在事务开始之前未被删除。

3、只有1，2 同时满足的记录，才能返回作为查询结果。

⦁ delete时：

InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

⦁ update时：

InnoDB执行update，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要update的行的删除时间。

上面只是一个浅显的讲解MVCC选择标准流程，源码层面应该是根据低水位跟高水位来截取的。具体实现可自行百度。

重点：

1、事务中快照读的结果是非常依赖该事务首次出现快照读的地方，即某个事务中首次出现快照读的地方非常关键，它有决定该事务后续快照读结果的能力。

2、在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View。

7、缓冲池（buffer pool）

应用系统分层架构，为了加速数据访问，会把最常访问的数据，放在缓存(cache)里，避免每次都去访问数据库。操作系统，会有缓冲池(buffer pool)机制，避免每次访问磁盘，以加速数据的访问。MySQL作为一个存储系统，同样具有缓冲池(buffer pool)机制，以避免每次查询数据都进行磁盘IO，主要作用：

1、存在的意义是加速查询

2、缓冲池(buffer pool) 是一种常见的降低磁盘访问 的机制；

3、缓冲池通常以页(page 16K)为单位缓存数据；

4、缓冲池的常见管理算法是LRU，memcache，OS，InnoDB都使用了这种算法；

5、InnoDB对普通LRU进行了优化：将缓冲池分为老生代和新生代，入缓冲池的页，优先进入老生代，该页被访问，才进入新生代，以解决预读失效的问题页被访问。且在老生代停留时间超过配置阈值的，才进入新生代，以解决批量数据访问，大量热数据淘汰的问题

预读失效：

由于预读(Read-Ahead)，提前把页放入了缓冲池，但最终MySQL并没有从页中读取数据，称为预读失效

缓冲池污染：

当某一个SQL语句，要批量扫描大量数据时，可能导致把缓冲池的所有页都替换出去，导致大量热数据被换出，MySQL性能急剧下降，这种情况叫缓冲池污染。解决办法：加入老生代停留时间窗口策略后，短时间内被大量加载的页，并不会立刻插入新生代头部，而是优先淘汰那些，短期内仅仅访问了一次的页。

8、table瘦身

空洞：

MySQL执行delete命令其实只是把记录的位置，或者数据页标记为了可复用，但磁盘文件的大小是不会变的。通过delete命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是空洞。插入的时候引发分裂同样会产生空洞。

重建表思路：

1、新建一个跟A表结构相同的表B

2、按照主键ID将A数据一行行读取同步到表B

3、用表B替换表A实现效果上的瘦身。

重建表指令：

1、alter table A engine=InnoDB，慎重用，牛逼的DBA都用下面的开源工具。

2、推荐Github：gh-ost

9、SQL Joins、统计、 随机查询

7种join具体如下：统计：

1、MyISAM模式下把一个表的总行数存在了磁盘上，直接拿来用即可

2、InnoDB引擎由于 MVCC的原因，需要把数据读出来然后累计求和

3、性能来说 由好到坏：count(字段) < count(主键id) < count(1) ≈ count(*)，尽量用count(*)。

随机查询：

mysql> select word from words order by rand() limit 3;

直接使用order by rand()，explain 这个语句发现需要 Using temporary和 Using filesort，查询的执行代价往往是比较大的。所以在设计的时要避开这种写法。

mysql> select count(*) into @C from t;

set @Y1 = floor(@C * rand());

set @Y2 = floor(@C * rand());

set @Y3 = floor(@C * rand());

select * from t limit @Y1,1;

select * from t limit @Y2,1;

select * from t limit @Y3,1;

这样可以避免临时表跟排序的产生，最终查询行数 = C + (Y1+1) + (Y2+1) + (Y3+1)

exist 和 in 对比：

1、in查询时首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。

2、子查询使用 exists，会先进行主查询，将查询到的每行数据循环带入子查询校验是否存在，过滤出整体的返回数据。

3、两表大小相当，in 和 exists 差别不大。内表大，用 exists 效率较高；内表小，用 in 效率较高。

4、查询用not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。not exists都比not in要快。

10、MySQL优化

SQL优化主要分4个方向：SQL语句跟索引、表结构、系统配置、硬件。

总优化思路就是最大化利用索引、尽可能避免全表扫描、减少无效数据的查询：

1、减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘 IO。

2、返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘 IO 及网络 IO。

3、减少交互次数：批量 DML 操作，函数存储等减少数据连接次数。

4、减少服务器 CPU 开销：尽量减少数据库排序操作以及全表查询，减少 CPU 内存占用 。

5、分表分区：使用表分区，可以增加并行操作，更大限度利用 CPU 资源。

SQL语句优化大致举例：

1、合理建立覆盖索引：可以有效减少回表。

2、union，or，in都能命中索引，建议使用in

3、负向条件(!=、<>、not in、not exists、not like 等) 索引不会使用索引，建议用in。

4、在列上进行运算或使用函数会使索引失效，从而进行全表扫描

5、小心隐式类型转换，原字符串用整型会触发CAST函数导致索引失效。原int用字符串则会走索引。

6、不建议使用%前缀模糊查询。

7、多表关联查询时，小表在前，大表在后。在 MySQL 中，执行 from 后的表关联查询是从左往右执行的(Oracle 相反)，第一张表会涉及到全表扫描。

8、调整 Where 字句中的连接顺序，MySQL 采用从左往右，自上而下的顺序解析 where 子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

SQL调优大致思路：

1、先用慢查询日志定位具体需要优化的sql

2、使用 explain 执行计划查看索引使用情况

3、重点关注(一般情况下根据这4列就能找到索引问题)：

1、key（查看有没有使用索引）

2、key_len（查看索引使用是否充分）

3、type（查看索引类型）

4、Extra（查看附加信息：排序、临时表、where条件为false等）

4、根据上1步找出的索引问题优化sql 5、再回到第2步

表结构优化：

1、尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED 。

2、VARCHAR的长度只分配真正需要的空间 。

3、尽量使用TIMESTAMP而非DATETIME 。

4、单表不要有太多字段，建议在20以内。

5、避免使用NULL字段，很难查询优化且占用额外索引空间。字符串默认为''。

读写分离：

只在主服务器上写，只在从服务器上读。对应到数据库集群一般都是一主一从、一主多从。业务服务器把需要写的操作都写到主数据库中，读的操作都去从库查询。主库会同步数据到从库保证数据的一致性。一般 读写分离 的实现方式有两种：代码封装跟数据库中间件。

分库分表：分库分表分为垂直和水平两个方式，一般是先垂直后水平。

1、垂直分库：将应用分为若干模块，比如订单模块、用户模块、商品模块、支付模块等等。其实就是微服务的理念。

2、垂直分表：一般将不常用字段跟数据较大的字段做拆分。

3、水平分表：根据场景选择什么字段作分表字段，比如淘宝日订单1000万，用userId作分表字段，数据查询支持到最近6个月的订单，超过6个月的做归档处理，那么6个月的数据量就是18亿，分1024张表，每个表存200W数据，hash(userId)%100找到对应表格。

4、ID生成器：分布式ID 需要跨库全局唯一方便查询存储-检索数据，确保唯一性跟数字递增性。

目前主要流行的分库分表工具 就是Mycat和sharding-sphere。

TiDB：开源分布式数据库，结合了传统的 RDBMS 和NoSQL 的最佳特性。TiDB 兼容 MySQL，支持无限的水平扩展，具备强一致性和高可用性。TiDB 的目标是为 OLTP(Online Transactional Processing) 和 OLAP (Online Analytical Processing) 场景提供一站式的解决方案。TiDB 具备如下核心特点

1、支持 MySQL 协议（开发接入成本低）。

2、100% 支持事务（数据一致性实现简单、可靠）。

3、无限水平拓展（不必考虑分库分表），不停服务。

4、TiDB 支持和 MySQL 的互备。

5、遵循jdbc原则，学习成本低，强关系型，强一致性，不用担心主从配置，不用考虑分库分表，还可以无缝动态扩展。

适合：

1、原业务的 MySQL 的业务遇到单机容量或者性能瓶颈时，可以考虑使用 TiDB 无缝替换 MySQL。

2、大数据量下，MySQL 复杂查询很慢。

3、大数据量下，数据增长很快，接近单机处理的极限，不想分库分表或者使用数据库中间件等对业务侵入性较大、对业务有约束的 Sharding 方案。

4、大数据量下，有高并发实时写入、实时查询、实时统计分析的需求。5、有分布式事务、多数据中心的数据 100% 强一致性、auto-failover 的高可用的需求。

不适合：

1、单机 MySQL 能满足的场景也用不到 TiDB。

2、数据条数少于 5000w 的场景下通常用不到 TiDB，TiDB 是为大规模的数据场景设计的。

3、如果你的应用数据量小（所有数据千万级别行以下），且没有高可用、强一致性或者多数据中心复制等要求，那么就不适合使用 TiDB。