InnoDB原理

Mysql体系结构

DB与Instance

DB：数据库可以是ibd文件、放在内存的文件，是物理操作系统文件或其他形式文件类型的集合。
Instance：Mysql数据库由后台线程及一个共享内存区组成。

数据库实例才是真正操作数据库文件的。在集群情况下，可能存在一个DB被多个Instance使用的情况。

Mysql被设计为单进程多线程，在OS上的表现是一个进程。

插件式表存储引擎

存储引擎基于表，而不是DB。

存储引擎对开发人员透明。

索引原理

MySQL使用的是B+树作为索引的数据结构

B树是一个分支内按顺序存放多个节点数据的数据结构；而B+树在此基础上，在分支内只存储索引，只在叶子节点存储数据（这样每一层可以存储更多索引，减少层数），并且在叶节点之间用指针互相连接，提高访问效率。

引擎

MyISAM，B+树存储的Data就是数据的地址（非聚集索引、稀疏索引）
InnoDB，直接存储数据（聚集索引）

为什么InnoDB建议每张表必须建立主键，并用自增整型？

ibd必须用B+树索引，而整型是天然的索引；否则ibd会自己维护一个唯一id行（隐藏的主键）。
因此UUID比较效率会比整型更低。
而自增则根本避免了重复，并且只在一端变化，已经有的数据无需做修改，减少了维持有序的成本。
如果不自增，而是随机添加，那么新增的数很可能会触发分裂、平衡，造成冗余索引。

B+树如何支持范围查询

Hash结构的索引，不支持范围查询；而B+树只用找到两端，然后顺着指针拿到所有节点就好了（叶节点是双指针连接的，并且有序）。

联合主键索引，为什么是最左前缀原则？

最左前缀原则：不能跳过左边的索引，必须从最左边索引开始，逐步增加条件。
因为联合主键索引底层的B+树就是按照主键顺序排序的，会从左到右进行比较；如果跳过了左边的主键，那就找不到了，因为第二个主键不一定是排好序的！。
首先按照第一个主键排序，然后按照第二个主键排序。在同一个主键内，二级主键是有序的，但是跳出这个圈，就是无序的。

索引优化原则

explain性能分析

explain extended: $rows * filtered/100$可以估算出将要和explain中前一个表达式进行连接的行数。
show warning: Mysql的提示信息，可能会帮你优化。

字段解释

select_type

• Primary 最外层的select。
• Subquery 不在from语句中，包含在select中的子查询。
• Derived 包含在from语句中的子查询。派生表。
  id
• 表示执行顺序，顺序越靠后优先级越高
  type
  效率优先级 system > const > eq_ref > ref > range > index > ALL

一般来说range是及格线，最好达到ref

• NULL mysql通过优化和底层原理，不访问表或索引就取到值。如求最小值，通过B+树直接拿到。
• system, const system是const的特例，const表示常量查询。表只有一行，为const查询。只有一条元组匹配，为system查询。
• eq_ref 主键关联查询，表有几行。
• ref 使用非主键（不唯一）索引，表有很多行。使用普通索引或唯一索引的部分前缀。
• range 范围查找，包括比较符号。
• index 无查询条件，全选。扫描全索引就能拿到结果，一般扫描二级索引（Mysql优先选择同等条件下更小的索引）。
• ALL 全表扫描。扫描聚集索引，比index更大

sql语句优化

-- 不走索引
select * from your_table where a > cond1 and b = cond2 and c = cond3
-- 走索引
select * from your_table where a = cond1 and b > cond2 and c = cond3

第一张表，第一个索引就开始范围查询，sql会认为范围太大，不走索引。
第二张表，第二个索引才开始范围查询，在合理范围内，会走索引。

• 尽量使用覆盖索引，这样会走索引

-- 推荐，其中name和age都有索引
select name, age from your_table where condition
-- 不推荐
select * from your_table where condition

• 少用or或in；不对索引用函数；不让索引发生隐式转换
• 减少搜索范围；范围过大，mysql会认为全表扫描更快，从而不走索引
• force index(your_index) 强制索引（注意，不一定更快！可能更多回表）
• 试试用like代替范围查询

缓存

Mysql8以后移除了缓存。

Mysql缓存的本质是KV Map。然而，对于高频修改的数据，Map缓存下来的是脏数据，因此不实用。

索引下推

在mysql5.6以后，每过滤一条数据，同时还会比较其他条件，只回表符合条件的主键，减少数据量。
like基本上会走索引下推。

Trace工具

-- 开启trace
set session optmizer_trace="enabled=on",end_markers_in_json=on;
-- 执行你的语句后，执行下面语句查询TRACE即为sql执行情况
select * from information_schema.OPTIMIZER_TRACE;

rows_estimation: cost就是扫描成本。
considered_execution_plans是最终考虑的执行计划。

Order by与Group by

key_len分析走了什么索引：

• int 4字节
• char(n) n字节
• varchar(n) n+2字节，其中2字节用于存储长度
• null 允许为null，会再用1字节存储
  根据上面知识，可以根据索引类型反推使用了什么索引。

order by不会走索引。只看前面where语句用到的索引

select * where a = cond1 and c = cond3 order by b;
select * where a = cond1 orderby c;

c对应第三个索引。上面两条语句，第一条只走a索引，用b、c索引排序；第二条用a索引，不用索引排序，而是文件排序。因为跳过了索引b，c是无序存储的。

select * where a = cond1 order by b, c;
select * where a = cond1 order by c, b;
select * where a = cond1 and b = cond order by b, c;
select * where a = cond1 order by b asc, c desc;

上面第一条语句走a索引，会用bc排序，因为底层存储是有序的。第二条不走索引，因为顺序不对！
第三条会用ab索引，因为b条件是个常量，不需要orderby c，被mysql优化了；
第四条走a索引，不用bc排序，因为底层是升序排序，而desc变成降序了。（mysql8以后有降序索引支持）

select * where a in (cond1.1, cond1.2) order by b, c;

不走索引，filesort，因为多个查询条件，在需要orderby排序时相当于范围索引。

orderby总结

1. orderby按照最左前缀，或orderby与where子句结合且满足最左前缀时，会走索引。
   groupby与orderby基本相同，实质也是先排序后分组。可以使用order by null禁用排序，加速过程。

FileSort原理

• 单路排序：一次性取出满足条件的所有字段，然后在sort buffer中进行排序；trace可以看到sort_mode: <sort_key, additional_key>。
• 双路排序：回表排序，只用排序字段和行id在sort buffer排序，trace可以看到<sort_key, rowid>。占用空间会少一点，因为只用到了特定的排序字段。因为没有取出所有数据，排序完成后，还要拿id回表查询所有数据。

Mysql有系统变量max_length_for_sort_data，默认1024Byte。如果大于它，用双路排序模式；小于则使用单路排序可以。

如何设计索引

什么时候建索引

不是建完表就想当然地建索引，而是后来根据用户使用最频繁的字段建索引。
在主体功能开发完后，把使用到的所有sql语句拉出来分析，然后建索引。

联合索引尽量覆盖条件

少建单值索引（联合索引可以过滤更多字段，而且sql一般只会用1个索引），设计1-3个联合索引包含sql的where、orderby、groupby字段，索引顺序满足sql语句的最左前缀原则。
对于unique，确保无重复的字段，可以建单值索引。

小基字段不建索引

例如枚举字段，一共就几个不同的值，建了索引，范围太大，还是需要全表扫描。

长字符串用前缀索引

尽量对占用空间小的列设计索引，如tinyint，也不占存储空间；
对于varchar(255)这样的字段，可以将每个值的前20个字符放在索引树中，如KEY index(name(20), age)。
但是注意，前缀索引只在where有效（而且需要回表），对于orderby、groupby还是要重新排序的。

优先满足where，而不是orderby

大多数情况我们会用where筛选出少量数据，然后再排序；这样效率更高。

在控台识别慢sql，针对性做索引优化

根据mysql慢查询日志（要手动设置，会影响性能），long_query_time默认是10秒钟

范围查找放最后

不放最后，那很多情况下索引不满足有序条件

根据设计的索引反向优化sql

比如有索引province, city, sex, hobby, age
age索引不一定能用到，因为sex和hobby会过滤一批。但是我们可以把sex和hobby手动写sql为全选，这样就能走到age。注意，必须是基数小的情况才可以这么做。不过一般生产环境数据量都很大，这么做值得。
在比如统计最近一周的活跃用户，但是

select * from your_table where province = cond and sex in ('female', 'male') and age >= x and age <= y and login_time >= time

这里login_time用不了索引。
可以设计一个标志，7天内是否登录，然后将这个标志作为索引字段，放age前面。
再根据其他高频场景建立辅助索引。

读多写少可以多建索引，写多就要少建

分页索引优化

select * from your_table limit 90000, 5;
select * from your_table where id > 90000 limit 5;

第一条语句实际上会从1顺序找到90000，然后把前面的数据删除，所以会出现翻页到后面越来越慢的情况。
第二条语句就能利用索引。但是前提是主键必须是自增而且连续的，例如中间被删除断号了那就不行。

select * from your_table as t inner join (select id from your_table order by name limit 90000, 5) as tc on t.id = tc.id

• 覆盖索引再回表：利用覆盖索引找出一个小的结果集，再回表，会尽可能地利用索引。

Join表关联查询优化

select * from t1 inner join t2 on t1.id = t2.id

NLJ算法（有索引）

Nested-Loop Join
一次一行地循环从驱动表中读取行，根据关联字段从被驱动表中取出满足条件的行，然后取合集。（磁盘扫描）
由于使用了索引，不需要全表扫描，扫描量较少。

• inner join：Mysql会自动优化，将更小的表作为驱动表，扫描更少的行数完成任务
• left join：左表为驱动表
• right join：右表为驱动表

Extra没有出现Using join buffer一般就是使用NLJ算法，如果使用的条件没有建立索引，使用NLJ性能较低，mysql会选择使用BNL算法。

BNL算法（无索引）

Block Nested-Loop Join
将驱动表的数据全部放入join buffer（这块空间在内存里面），然后将被驱动表每一行拿出来与join buffer比对。
这个过程中，两张表都会做全表扫描（磁盘扫描），然后在join buffer（内存）中比对；如果驱动表太大，要放2次，那么被驱动表也会被全表扫描2次！

比较上面两种算法，使用到join关联查询时，最好走索引。
尽量小表驱动大表，可以用straight_join中指定驱动表（只适用于inner join）；注意这里说的“小表”，是根据条件过滤出来数据量更少的表

in与exist

小表驱动大表

in子句内的查询会先执行。

当B表数据量小于A表，in优于exist。

select * from A where if in (select id from B) 
-- 先执行B表查询
-- 相当于
for (select id from B) {
    select * from A where A.id = B.id
}

尽量避免in操作，把in的范围控制在1000以内。

exists外层的语句会先执行

当A表数据量小于B表，exists优于in。

select * from A where exists (select * from B where B.id = A.id)
-- 先执行A表查询
-- 相当于
for (select id from A) {
    select * from B where B.id = A.id

exists子查询有时候可以用join来代替。

count

统计数据量：count(*)效率更高（有专门优化，而且会统计null行），其他方法有索引的、索引小的更快

什么时候分库分表

单表行数超过500万行/单表容量超过2GB

索引规约

1. 唯一特性字段，在数据库层面就用唯一索引，根本避免脏数据。
2. 超过3张表不要用join；需要用到join，数据类型严格一致；多表查询，关联字段要有索引。（用Java做，Java集群可以提高性能，而MySQL不容易拓展）
3. varchar索引只建立20长度就可以了。
4. 搜索，严禁左模糊、全模糊，如果需要可以调用搜索引擎（ES）

事务和锁

并发事务处理的问题

• 更新丢失：多个事务选择同一行，并基于最初始的值更新该行。最后的更新会覆盖之前的更新。
• 脏读：一个事务正在修改一条记录，此时另一个事务读取了未提交、未更新的数据。
• 不可重读：一个事务内部的相同查询语句在不同时刻读出的结果不一致（数据被改变/删除）。
• 幻读：一个事务按相同查询条件重新读取以前检索过的数据，却发现其他事务插入了新的符合条件的数据。

隔离级别

通过设置隔离级别，可以避免上述问题

隔离级别	脏读	不可重复读	幻读
读未提交
读已提交	x
可重复读	x	x
可串行化	x	x	x

然而隔离级别越高，对性能影响越大；实际上隔离就是使事务串行化，而不是并发。
mysql默认开启可重复读。此时，一个事务，从数据库读取的数据始终一致（实际值会变，只是select使用历史旧版本），但是更新值时不会出错（使用实际值，insert、update、delete使用当前版本）。使用了MVCC机制。然而，当我们在Java中使用这个读出来的历史版本计算，就会出问题.
在可串行化级别中，select语句会加写锁。

锁

• 乐观锁：使用版本号比对（性能较好）
• 悲观锁
  • 读锁（Shared）：读操作可以同时进行不受影响
  • 写锁（eXclusive）：当前写操作未完成，会阻断其他写锁、读锁
• 表锁：锁住整张表，一般用于整表数据迁移，并发程度最低。

lock table your_table read, your_table2 write;
-- 显示为1即加了锁
show open tables;
unlock tables;

• 行锁：只锁一行数据，开销大；加锁慢，会出现死锁；并发程度高。

间隙锁 某些情况下解决幻读问题

# id # name
    1 name1
    2 name2
    10 name3
    20 name4
-- 有(2, 10), (10, 20), (20, inf)三个区间
update account set name = 'your_name' whre id > 8 and id < 18
-- (8-18)，覆盖了(2,10), (10, 20)两个区间，(2, 20)区间都会上写锁

间隙锁只有在可重复读级别下才生效。

临键锁

临键锁（Next-key Locks）是行锁与间隙锁的结合。

行锁分析

show status like 'innodb_row_lock%'

• lock_time_avg：平均等待时长
• lock_waits：等待次数
• lock_time：等待总时长

-- 查看事务
select * from INFORMATION_SCHEMA INNODB_TRX;
-- 释放锁
kill <trx_mysql_thread_id>

-- 查看锁
select * from INFORMATION_SCHEMA INNODB_LOCKS;
-- 查看锁等待
                                 INNODB_LOCK_WAITS;

锁优化总结

• 尽量让数据检索通过索引完成，避免行锁升级为表锁。
• 合理设计索引，缩小锁的范围
• 减少检索范围，避免间隙锁
• 控制事务大小，减少锁定资源量和时长，设计事务加锁的sql尽量放到最后执行
• 尽可能第级别事务隔离

MVCC

Multi-Version Concurrency Control多版本并发控制
Mysql读已提交和可重复读两个隔离级别实现了MVCC

undo日志

一行数据被多个事务依次修改，每个日志修改成功，都会保留undo日志，用指针表串联

• 事务id：只有第一条修改语句被执行，才会真正分配事务id（select不会）

read-view

事务开启后，执行任何查询sql时会生成当前事务一致性视图readview，这个值在事务结束前不会变化。
readview由未提交的事务id数组与已创建的最大事务id组成。

事务里任何sql查询结果都需要从对应版本链最新数据开始，逐条与read-review做比对

Trac10: 
begin;
update col1 xxxx where id = 1
Trac20:
begin;
update col2 yyyy where id = 1
Trac30:
begin;
update name thename where id = 1
commit;
Trac(temp): -- 只有update语句才会分配id
select name where id = 1 -- 查询结果为thename 
-- read-view: [10, 20]未提交id, 30已创建最大id
-- 此时，10前的id，都是已提交事务；20后的id，都是未提交事务；30后的事务，还没开始
-- 注意，已提交的事务30，不一定小于20
Trac10:
update name namenew where id = 1
Trac(temp):
select name where id = 1 -- 查询结果还是为thename，Trac10不可见
Trac10:
commit;
Trac(temp):
select name where id = 1 -- 查询结果为thename！因为read-review保持不变

已提交事务	未提交+已提交事务	未开始
10之前	min_id-max_id	30之后

版本链比对规则：

1. 行事务trx_id < min_id（已提交），可见
2. trx_id > max_id（未开始），不可见
3. trx_id在数组中（未提交），不可见。
4. trx_id = max_id或不属于数组（已提交），可见。

读已提交，和可重复读的read-view的区别：
读已提交：每次查询都会生成最新的read-view，每次都取最新数据。

Mysql执行过程

sql语句.update t set name="new_name" where id = 1 -> server层.连接器
server层: {
    连接器 -> 分析器 -> 优化器 -> 执行器
    连接器 -> 查询缓存
    binlog文件
}
server层.执行器 -> InnoDB存储引擎
InnoDB存储引擎 :{
    Buffer Pool缓存池
    Redo Log Buffer
    undo日志文件
    redo日志文件
}
ibd磁盘文件.page.name=old_name -> InnoDB存储引擎.Buffer Pool缓存池: 1. 加载缓存数据（id为1的记录所在的page数据）
InnoDB存储引擎.Buffer Pool缓存池 -> InnoDB存储引擎.undo日志文件: 2. 写入更新前数据的旧值，便于回滚
server层.执行器 -> InnoDB存储引擎.Buffer Pool缓存池: 3. 更新内存数据old_name为new_name
server层.执行器 -> InnoDB存储引擎.Redo Log Buffer: 4. 写redo日志
InnoDB存储引擎.Redo Log Buffer -> InnoDB存储引擎.redo日志文件.name=new_name: 5. 准备提交事务，redo日志写入磁盘
server层.执行器 -> server层.binlog文件: 6. 准备提交事务，binlog日志写入磁盘
server层.binlog文件 -> InnoDB存储引擎.redo日志文件: 7. 写入commit标记到redo日志文件（保证redo与binlog数据一致），标志事务完成
InnoDB存储引擎.Buffer Pool缓存池 -> ibd磁盘文件: 通过IO线程，统一以page为单位写入磁盘

这一套机制保证了读写性能：首先通过顺序IO（利用缓存局部性，性能高）将语句操作写入磁盘的日志文件，最后再通过IO线程通过随机IO，将数据以页为单位写入磁盘ibd。