MySQL知识体系

插入缓冲

双写

自适应哈希

刷新临近页

完整的示例图解

compact行格式

Redundant行格式

数据页

系统页

Undo页

事务数据页

页中的数据记录是已单向链表的形式

系统表空间

用户表空间

撤销表空间

临时表空间

on

off

如果数据不再内存，就需要在磁盘上对该页进行查找

顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，
顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页
面单独进行磁盘 I/O 操作了。

添加字段

修改字段名，将age字段改成player_age

修改字段的数据类型

删除字段

作用是显式开启一个事务

提交事务。当提交事务后，对数据库的修改是永久性的。

意为回滚事务。

在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

删除某个保存点。

设置事务的隔离级别

脏读（Dirty Read）

不可重复读（Nnrepeatable Read）

幻读（Phantom Read）

B+ 树索引模型

InnoDB 的数据是按数据页为单位来读写的

InnoDB 中，每个数据页的大小默认是 16KB

主键索引

普通索引

唯一索引

聚簇索引

非聚簇索引：二级索引

单一索引

联合索引

innodb中会将满足一定条件的热点数据页的地址放到hash表中，
并且这些hash放到缓冲池，提升热点数据的查询效率

原理

Btree索引

Hash索引

innodb为什么选择B+树

跳表、LSM 树（todo）

当数据量非常小，比如不到1000行

数据重复度，高于10%

频繁更新字段不要创建索引

字段的数值有唯一性的限制，比如用户名

频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下

 需要经常 GROUP BY 和 ORDER BY 的列

UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

DISTINCT 字段需要创建索引

做多表 JOIN 连接操作时，创建索引需要注意以下的原则

如果索引进行了表达式计算，则会失效

如果对索引使用函数，也会造成失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，
而在 OR 后的条件列没有进行
索引，那么索引会失效。

当我们使用 LIKE 进行模糊查询的时候，前面不能是 %

索引列与 NULL 或者 NOT NULL 进行判断的时候也会失效。
索引不会储层空值，所以最好将索引设置为not null

我们在使用联合索引的时候要注意最左原则

在 WHERE 条件语句中，找到所有等值谓词中的条件列，将它们作为索引片中的开始列

将 GROUP BY 和 ORDER BY 中的列加入到索引中

将 SELECT 字段中剩余的列加入到索引片中

为什么很难存在理想的索引设计

缺点

当查询语句中的字段就是索引中的某个字段时，可以达到索引覆盖的效果

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

会导致回表

第一种方式是使用倒序存储

第二种方式是使用 hash 字段

查询主键索引的B+树

包含索引列数为1或2

包含的索引列数⼤于2

where后的条件

1. 在WHERE条件语句中，找到所有等值谓词中的条件列，将它们作为索引⽚中的开始列；

2. 将 GROUP BY和ORDER BY中的列加⼊到索引中；

3. 将SELECT字段中剩余的列加⼊到索引中。

1. 如果数据量很⼤,过多索引所需要的磁盘空间可能会成为⼀个 问题，对缓冲池所需空间的压⼒也会增加。

2. 增加了索引维护的成本。

你能看到针对⼀条SQL查询来说，三星索引是个理想的⽅式，但实际运⾏起来我们要考虑更多维护的成本， 在索引效率和索引维护之间进⾏权衡。

等值查询

范围查询

模糊查询

排序

索引失效

做checkpoint的机制将数据回写到磁盘

指的是缓冲池中被修 改过的页，与磁盘上的数据不一致。

show variables like 'innodb_buffer_pool_size'

set global innodb_buffer_pool_size = 134217729

mysql > show variables like 'innodb_buffer_pool_instances'

然后 再针对innodb_buffer_pool_instances参数进⾏修改

1. 如果该数据存在于内存中，基本上执行时间在1ms左右，效率还是很高的。
   

如果数据没有在内存中，就需要在磁盘上对该⻚进⾏查找，整体时间预估在10ms左右

Flush tables with read lock (FTWRL)

如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆

如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟

既然要全库只读，为什么不使用 set global readonly=true 的方式呢

另一类表级的锁是 MDL（metadata lock)

表锁的语法是 lock tables … read/write。

MyISAM不支撑行锁

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

加锁开销

锁死概率

并发性能

读锁或 S 锁

排它锁也叫独占锁、写锁或 X 锁

乐观锁（Optimistic Locking）认为对同一数据的并发操作不会总发生，属于小概率事件，
不用每次都对数据上锁，也就是不采用数据库自身的锁机制，而是通过程序来实现。在程序
上，我们可以采用版本号机制或者时间戳机制实现。

适用于读多写少

对数据被其他事务的修改持保守态度，会
通过数据库自身的锁机制来实现，从而保证数据操作的排它性

适用于写操作多的场景

如果事务涉及多个表，操作比较复杂，那么可以尽量一次锁定所有的资源，而不是逐步
来获取，这样可以减少死锁发生的概率；

如果事务需要更新数据表中的大部分数据，数据表又比较大，这时可以采用锁升级的方
式，比如将行级锁升级为表级锁，从而减少死锁产生的概率；

不同事务并发读写多张数据表，可以约定访问表的顺序，采用相同的顺序降低死锁发生
的概率

死锁检测

直接进入等待，直到超时

MVCC是通过数据⾏的多个版本管理来实现数据库的并发控制

使用MVCC的好处

快照读

当前读

InnoDB

事务版本号

行记录的隐藏列

Undo Log

Read View, 帮助我们解决了行的可见性问题

Read View的结构

Read View的原则

查询数据的步骤

读已提交时 , 一个事务中的每一次SELECT查询都会获取一次Read View

可重复读时 , 一个事务只在第一次SELECT时会获取Read View , 后面所有的SELECT 都会复用这个Read View

读未提交

读已提交

可重复读

可重复读+ next-key

在可重复读的情况下，InnoDB可以通过Next-Key锁+MVCC来解决幻读问题。

在读已提交的情况下，即使采⽤了MVCC⽅式也会出现幻读。

innodb锁三种类

当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log里面，并更新内存，这个时候更新就算完成了

执行事务的时候，写完内存和日志，事务就算完成

先写日志再写磁盘。

恢复redolog上已经提交的数据

statement

row

mix

结构

视图作为一张虚拟表，帮我们封装了底层与数据表的接口

视图本身是虚拟表，本身不存储数据，如果想通过视图对底层数据表进行修改会受到限制

视图一般只用于查询

优点

缺点

一个数据页16KB

IOPS：每秒I/O数的简称，表示一秒中输入输出操作（比如读和写）的次数

可以用IOPS数值来描述一个数据库的IO操作量

顺序读写主要时间花费在了传输时间

随机读写需要多次寻道和旋转延迟

执行时间长

等待时间长

数据量大

使用连接池

Centos系统的内核参数，磁盘调度策略等优化

异步复制

半同步复制

全同步复制

多库并行复制

GTID复制

垂直分库

水平分库

Haproxy+keepallived

业务分库

表的字段过多，将一张表拆分为多张表

单表行数过多采用数据分配到不同表

在满足业务要求的情况下，数值型>字符型（CHAR>VARCHAR）

逻辑查询优化

物理查询优化

查询路径优化

使用 Redis 或 Memcached 作为缓存

所谓的执行计划，落实到底层，无非就是先访问哪个表，用哪个索引还是全表扫描，
拿到数据之后如何去聚簇索引回表，是否要基于临时磁盘文件做分组聚合或者排序

id越大就先执行，相同id的从上到下执行

PRIMARY，查询类型是主查询的意思

SUBQUERY，也就是子查询

SIMPLE，简单的查询

union_result

union_result

MATERIALIZED

derived

consts

ref

range

index

all

跟type结合起来的，意思就是说你type确定访问方式了，
那么到底有哪些索引是可供选择，可以使用的呢，这都会放这里

就是使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息

预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据

经过搜索条件过滤之后的剩余数据的百分比

例子

就是在possible_keys里实际选择的那个索引

索引的长度

Nested Loop（嵌套循环的访问方式）

using index(使用了覆盖索引，仅仅涉及到了一个二级索引，不需要回表)

using temporary

Using index condition

Using where

Using join buffer

Using filesort

inner join

outer join

驱动表、被驱动表

针对多表查询的语句，我们要尽量给两个表都加上索引，索引要确保从驱动表里查询也是通过索引去查找，
接着对被驱动表查询也通过索引去查找。如果能做到这一点，你的多表关联语句性能就会很高！

根据成本优化选择执行计划

执行show VARIABLES LIKE '%quer%'

第一个表示慢日志是否开启，默认关闭，第二表示慢日志的文件的位置。
一般执行时间超过10秒的sql语句就会被放进这个文件里面（10秒是默认值，可以修改），这样就可以定位到执行慢的sql语句。

ID：表示查询中执行select子句或操作表的顺序，当id相同时，按从上到下的顺序执行，id不同时，id数值大的先执行。

select_type：select_type代表的是查询的类型,主要是用于区别普通查询,联合查询,子查询。
    SIMPLE ：简单的select查询，查询中不包含子查询或者UNION。
    primary：查询中若包含任何的子查询,最外层查询则被标记为primary。
    subquery：表示子查询。
    dervied:zai from列表中包含的子查询标记为derived(衍生) mysql会递归执行浙西子查询,把结果放在临时表里。
    UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVE
    union result :从union表获取结果的select。

table：表示执行的表

type：type所显示的是查询使用了哪种类型…
这个字段很重要，从好到坏的顺序是：system > const > eq_ref > ref > range > index > all
一般来说，至少要保证能达到range级别。
     system:该类型表示表中只有一行记录
     const:常量匹配,表示通过索引一次就可以找到数据
     eq_ref:唯一性索引扫描,对于每个索引建,表中只有一条记录和它匹配
     ref 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
     range:只查找范围内的数据,使用索引来匹配行,查询的范围缩小了,自己比前面的类型性能更好
     index:(full index scan)全索引文件扫描比all要好不少,将直接从索引数据结构中找寻数据,比全表扫描理论上快不少
     all: Full Table Scan 将遍历全表以找到匹配的行

possible_keys: 显示可能应用在这张表中的索引，一个或多个,但不一定会使用到

keys：显示查询语句中实际使用的索引,如果没有使用则为null

key_len: 显示索引中使用的字节数,通过key_len计算查询中使用的索引长度,在不损失精确性的情况下索引长度越短越好,key_len显示的值为索引字段最可能的长度,并非是实际使用长度

ref:显示索引的那一列被使用了，如果可能的话，最好是一个常数。

rows:根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数,越少越好

extra:包含十分重要的额外信息
Usiing filesort:说明mysql读数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql无法利用索引完成的排序称为"文件排序".
    Using temporary:使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
    Using index:表示相应的select操作中使用了覆盖索引,.
    Using where : 表明使用了where过滤
    Using join buffer:表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些
    impossible where:where子句的值总是false，不能用来获取任何元组

通过一些提高性能的经验来修改sql语句，或者让sql走索引。

show master logs； 查看所有Binlog的日志列表
show master status； 查看最后一个Binlog日志的编号名称，以及最后一个事件结束的位置
flush logs； 刷新Binlog 此刻开始产生一个新编号的Binlog日志文件
reset master； 清空所有的Binlog日志（慎用）

show binlog events in ‘L1WWITAFW5IVTNG-bin.000001’ from 500 limit 1，2；从指定的位置，带有偏移，开始查看binlog日志 ，限制查询的条件

count(字段)<count(主键 id)<count(1)≈count(*)