MySQL|MySQL核心考案 | 系统性学习 | 精编第一次 | 无知的我费曼笔记（图文排版无水印） Learning|mysql|数据库|database|sys

无知的我正在复盘MySQL。。。
笔记特点是

重新整理了一些语言描述、排版而使用了自己认为更通俗易懂的描述
提升了总结归纳性
同样是回答了一些常见关键问题。。

文章目录

MySQL
- MySQL 存储引擎的类型
- MySQL 数据库表设计的字段设计
- MySQL VARCHAR(M)最多能存储多大的数据
- MySQL 三星索引
- MySQL InnoDB一颗B+树可以存放多少行数据
- MySQL 提高insert性能的方法
- MySQL 死锁
- MySQL 索引的基本原理
- MySQL 聚簇、非聚簇索引
- MySQL 索引的缺陷
- MySQL 索引数据结构及优劣
- MySQL 索引设计的原则
- MySQL 最左前缀原则
- MySQL 最左匹配原则
- MySQL 锁的类型
- MySQL 锁——InnoDB算法
- MySQL SQL优化
- MySQL 慢查询统计和优化
- MySQL 事务的基本特性
- MySQL 事务的隔离级别
- MySQL 幻读脏读不可重复读
- MySQL ACID原理
- MySQL MVCC
- MySQL 分表后非sharding_key的查询的处理
- MySQL 分表后的排序
- MySQL 主从同步原理
- MySQL MyISA与InnoDB区别
- MySQL InnoDB、MyISM
- MySQL 索引类型及对数据库的性能的影响
- MySQL 执行计划的查看方法

MySQL MySQL 存储引擎的类型都是插件式存储引擎。能够根据不同的应用建立不同的存储引擎表。
InnoDB存储引擎（默认事务型引擎）
应用场景是处理大量短期（short-lived）事务
MyISAM存储引擎（MySQL5.1以及之前版本是默认的）
不支持事务、行级锁且崩溃后无法安全恢复。
对整张表加锁，导致容易因为表锁的问题造成典型的性能问题
Memory引擎
速度比MyISAM表要快一个数量级，这是因为数据文件是存储在内存的
表的结构在重启以后会保留，但数据会丢失
应用场景是

查找（lookup）、映射表。比如邮编和州名映射的表
缓存周期性聚合数据的结果
保存数据分析中产生的中间数据

Archive引擎
只支持INSERT、SELECT
比MyISAM表的磁盘IO更少。这是因为会缓存所有的写并利用zlib对插入的行进行压缩
每次SELECT都会全盘扫描。所以应用场景是日志和数据采集类的应用
CSV引擎（经常使用）
将普通的CSV文件（逗号值分割的文件）作用MySQL表来处理，但是不支持索引
所以应用场景是作为一种数据交换的机制
MySQL 数据库表设计的字段设计字段类型的优先级
可以选整型就不选字符串
比如tinyint和char（1）都是1字节，但是在order by 排序时，tinyint要快。这是因为后者需要考虑字符集和校对集（就是排序优先）
使用的字段不浪费内存
这是因为字段越大，内存速度越慢
尽量避免使用NULL
这是因为不利于索引、查询。是因为 =null 或者 !=null都查询不到值，只有使用is null 或者is not null才可以
所以在创建字段时建议使用“not null default”的形式
char与varchar的选择
char长度固定，处理速度要快很多，但是耗费更对存储空间。
所以对储存空间不大、要求速度的使用char，反之使用varchar
MySQL VARCHAR(M)最多能存储多大的数据 M代表该类型最多存储的字符数，但实际有限制
其列最多定义65535个字节

除了BLOB、TEXT类型之外，其他所有的列（不包括隐藏列和记录头信息）加起来的占用字节长度不能超过65535个

MySQL 三星索引三星索引是什么&作用
对于查询来说，其实最好的索引。这是因为如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其响应时间通常比使用一个普通索引的响应时间少几个数量级
三星索引如何实现
一星：一个查询相关的索引行是相邻的或者至少相距足够靠近
二星：索引中的数据顺序和查找中的排列顺序一致
三星：索引中的列包含了查询中需要的全部列（最重要）
MySQL InnoDB一颗B+树可以存放多少行数据大概2千万
MySQL 提高insert性能的方法

合并多条insert为一条
修改参数 bulk_insert_buffer_size 调大批量插入的缓存
设置 innodb_flush_log_at_trx_commit = 0

文章图片
手动使用事务以一次性提交事务，而不是一条条地提交

MySQL 死锁死锁是什么
两个或两个以上的进程在执行过程中，因资源争夺而造成一种互相等待的状态，若无外力作用，它们都无法推进的情况
如何查看死锁
show engine innodb status 查看最近一次死锁
innoDB Lock Monitor打开锁监控，每15s会输出一次日志，使用完毕后要关闭，否则影响数据库性能
对待死锁常见两种策略
innodblockwait_timeout 设置超时时间，一直等待直到超时
发起死锁检测，发现死锁后，主动回顾死锁中的某一个事务，让其他事务继续运行
MySQL 索引的基本原理就是把无序的数据变成有序的查询

把创建了索引的列的内容进行排序
对排序结果生成倒排表
在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

MySQL 聚簇、非聚簇索引都是B+树的数据结构
聚簇索引是什么

将数据存储与索引放到了一块、并且是按照一定的顺序组织的，找到索引也就找到了数据，数据的物理存放顺序与索引顺序是一致的
即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的

非聚簇索引是什么

叶子节点不存储数据、存储的是数据行地址，也就是说根据索引查找到数据行的位置再取磁盘查找数据
这个就有点类似一本树的目录，比如我们要找第三章第一节，那我们先在这个目录里面找，找到对应的页码后再去对应的页码看文章。

区别及应用场景

聚簇索引查询效率更高。这是因为查询通过聚簇索引可以直接获取数据，相比而言，非聚簇索引需要第二次查询（非覆盖索引的情况下）
聚簇索引对于范围查询的效率很高。这是因为其数据是按照大小排列的
聚簇索引适合用在排序的场合；非聚簇索引不适合、

MySQL 索引的缺陷维护索引很昂贵。当特别是插入新行或者主键被更新导至要分页(page split)的时
建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须被移动的行数据可能造成碎片。使用独享表空间可以弱化碎片
会出现聚簇索引有可能有比全表扫面更慢。这是因为当表因为使用UUId（随机ID）作为主键，使数据存储稀疏时
所以建议使用int的auto_increment作为主键
如果主键比较大的话，那辅助索引将会变的更大。
这是因为辅助索引的叶子存储的是主键值
过长的主键值，会导致非叶子节点占用更多的物理空间
MySQL 索引数据结构及优劣

索引的数据结构和具体存储引擎的实现有关。在MySQL中使用较多的索引如下

Hash索引是什么

底层的数据结构就是哈希表
就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快
因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快

MySQL|MySQL核心考案 | 系统性学习 | 精编第一次 | 无知的我费曼笔记（图文排版无水印）

文章图片

B+树索引是什么

是 InnoDB存储引擎的默认索引
是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。
在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动
而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景

文章图片

区别及使用场景
如果是等值查询，那么哈希索引明显有绝对优势
这是因为当键值都是唯一的，只需要经过一次算法即可找到相应的键值
// 但是如果当键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据
如果是范围查询检索，这时候哈希索引就毫无用武之地了。
因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
哈希索引无法利用索引完成排序及模糊查询（如 like ‘xxx%’）
这是因为这种部分模糊查询，其实本质上也是范围查询
哈希索引也不支持多列联合索引的最左匹配规则
在有大量重复键值情况下，哈希索引的效率也是极低的。

这是因为存在哈希碰撞问题
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大

MySQL 索引设计的原则查询更快、占用空间更小

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的表，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配。
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可。
定义有外键的数据列一定要建立索引。
更新频繁字段不适合创建索引
若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)8. 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
对于定义为text、image和bit的数据类型的列不要建立索引。

MySQL 最左前缀原则 MySQL 最左匹配原则 MySQL 锁的类型 MySQL 锁——InnoDB算法

Record lock：单个行记录上的锁
Gap lock：间隙锁，锁定一个范围，不包括记录本身
Next-key lock：record+gap 锁定一个范围，包含记录本身

相关知识点：

innodb对于行的查询使用next-key lock
Next-locking keying为了解决Phantom Problem幻读问题
当查询的索引含有唯一属性时，将next-key lock降级为record key
Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A.
将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1

MySQL SQL优化 MySQL 慢查询统计和优化

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？

是查询条件没有命中索引？
是load了不需要的数据列？
还是数据量太大？

所以优化也是针对这三个方向来的

首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

MySQL 事务的基本特性

ACID

原子性
指的是一个事务中的操作要么全部成功，要么全部失败。
一致性
是数据库总是从一个一致性的状态转换到另外一个一致性的状态。
比如 A想要转账给B 100 块钱。假设A只有90块，在支付之前，数据库里的数据都是符合约束的,但是如果事务执行成功了，数据库数据就破坏约束了，因此事务不能成功，而为了解决这个问题，事务提供了一致性的保证
隔离性
是一个事务的修改在最终提交前，对其他事务是不可见的。
持久性
是一旦事务提交，所做的修改就会永久保存到数据库中。
MySQL 事务的隔离级别 read uncommit 读未提交
就是可能会读到其他事务未提交的数据，也叫做脏读。
比如用户本来应该读取到id=1的用户age应该是10，结果读取到了其他事务还没有提交的事务，结果读取结果age=20
read commit 读已提交
就是两次读取结果不一致，叫做不可重复读。不可重复读解决了脏读的问题，他只会读取已经提交的事务。
比如用户开启事务读取id=1用户，查询到age=10，再次读取发现结果=20，在同一个事务里同一个查询读取到不同的结果叫做不可重复读。
repeatable read 可重复读（默认级别）
就是每次读取结果都一样，但是有可能产生幻读。
serializable 串行（一般不使用）
会给每一行读取的数据加锁。
这就意味着会导致大量超时和锁竞争的问题
MySQL 幻读脏读不可重复读脏读(Drity Read)
某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read)
在一个事务的两次查询之中数据不一致。这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read)
在一个事务的两次查询中数据笔数不一致。
例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
MySQL ACID原理略
MySQL MVCC 多版本并发控制
读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了，不同的事务session会看到自己特定版本的数据，版本链MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。
其他两个隔离级别够和MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。
略
MySQL 分表后非sharding_key的查询的处理

可以做一个mapping表，比如这时候商家要查询订单列表怎么办呢？不带user_id查询的话你总不能扫全表吧？所以我们可以做一个映射关系表，保存商家和用户的关系，查询的时候先通过商家查询到用户列表，再通过user_id去查询。
宽表，对数据实时性要求不是很高的场景，比如查询订单列表，可以把订单表同步到离线（实时）数仓，再基于数仓去做成一张宽表，再基于其他如es提供查询服务。
数据量不是很大的话，比如后台的一些查询之类的，也可以通过多线程扫表，然后再聚合结果的方式来做。或者异步的形式也是可以的。

MySQL 分表后的排序 union
排序字段是唯一索引：

首先第一页的查询：将各表的结果集进行合并，然后再次排序
第二页及以后的查询，需要传入上一页排序字段的最后一个值，及排序方式。
根据排序方式，及这个值进行查询。如排序字段date，上一页最后值为3，排序方式降序。查询的时候sql为select … from table where date < 3 order by date desc limit 0,10。这样再将几个表的结果合并排序即可。

MySQL 主从同步原理 Mysql的主从复制中主要有三个线程： master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

保存。主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
发送。主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
写入。从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
重放。从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

如何定位主从同步的位置？
主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置。从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。
引出问题假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了
由于mysql默认的复制方式是异步的。就是主库把日志发送给从库后不关心从库是否已经处理
解决办法1 全同步复制
主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。
解决办法2 半同步复制
和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。
MySQL MyISA与InnoDB区别 MyISAM

不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

InnoDb

支持ACID的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数；一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

MySQL InnoDB、MyISM InnoDB

里面一定有主键，主键一定是聚簇索引，不手动设置、则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的隐藏id来当作主键索引。
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

MyISM

使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。
表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。
由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

MySQL 索引类型及对数据库的性能的影响普通索引：允许被索引的数据列包含重复的值。
唯一索引：可以保证数据记录的唯一性。
主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录

使用关键字 PRIMARY KEY 来创建。

联合索引：索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引。
全文索引：通过建立倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题，

是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column); 创建全文索引

索引可以极大的提高数据的查询速度。

通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件

索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。
MySQL 执行计划的查看方法

执行计划就是 sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数

EXPLAIN SELECT * from A where X=? and Y=?

文章图片

1.id ：是一个有顺序的编号，是查询的顺序号，有几个 select 就显示几行。id的顺序是按 select 出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为NULL最后执行。
2.selectType 表示查询中每个select子句的类型

SIMPLE：表示此查询不包含 UNION 查询或子查询
PRIMARY：表示此查询是最外层的查询（包含子查询）
SUBQUERY：子查询中的第一个 SELECT
UNION：表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION： UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.
DERIVED：衍生，表示导出表的SELECT（FROM子句的子查询）

3.table：表示该语句查询的表
4.type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：

const：通过索引一次命中，匹配一行数据
system: 表中只有一行记录，相当于系统表；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
ref: 非唯一性索引扫描,返回匹配某个值的所有
range: 只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>；
index: 只遍历索引树；
ALL: 表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，执行效率越慢。
执行效率 ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index

5.possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到。
6.key：此字段是 mysql 在当前查询时所真正使用到的索引。他是possible_keys的子集
7.key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
9.rows：mysql 查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大
10.filtered：返回结果的行占需要读到的行(rows列的值)的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少
【MySQL|MySQL核心考案 | 系统性学习 | 精编第一次 | 无知的我费曼笔记（图文排版无水印）】11.extra

using filesort ：表示 mysql 对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询 cpu 资源消耗大，延时大。
using index：覆盖索引扫描，表示查询在索引树中就可查找所需数据，不用扫描表数据文件，往往说明性能错。using temporary：查询有使用临时表, 一般出现于排序，分组和多表 join 的情况，查询效率不高，建议优化。
using where ：sql使用了where过滤,效率较高。