MySQL 学习笔记

以下为笔者观看黑马程序员MySQL学习视频所做的笔记

基础

三大范式

概念解释： 三大范式其实就是数据库建表的规范。

• 第一范式：要求一张表中的数据每一列都是不可分割的原子项数据
• 第二范式：消除部分依赖，要求一张表中的每一列都完全依赖于主键（针对于组合主键），也就是不会出现某一列只和部分主键相关
• 第三范式：消除传递依赖，要求一张表中的每一列都和主键是直接依赖的，不是间接依赖。

详细举例可以看参考文章
参考文章：https://www.cnblogs.com/wsg25/p/9615100.html

为什么不推荐使用外键

阿里巴巴开发手册：

【强制】不得使用外键与级联，一切外键概念必须在应用层解决。
说明: 外键与级联更新适用于单机低并发，不适合分布式、高并发集群; 级联更新是强阻塞，存在数据库更新风暴的风险; 外键影响数据库的插入速度

外键的作用：

• 保证数据一致性和完整性
• 级联操作方便

为什么不用外键？

• 增加复杂性：不利于需求变化、每次更新数据都要考虑外键约束
• 容易出现死锁情况：因为需要请求对其他表内部加锁
• 不利于分库分表：分库分表下外键无法生效

三种多表关系分别怎么实现

• 一对多：在多的一方建立外键，指向一的一方的主键
• 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
• 一对一：在任意一方加入外键，关联另外一方的主键，外键需要设置为唯一(UNIQUE)

自连接

自连接查询

自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。
而对于自连接查询，可以是内连接查询，也可以是外连接查询。
自连接必须设置表的别名为两个不同的别名。

联合查询

使用union，把多次查询的结果联合起来形成新的查询结果集。
联合查询的多张表列数及字段类型需要一致。
union all将全部数据合并，union则会合并后进行去重

子查询

• 标量子查询
• 列子查询
• 行子查询
• 表子查询

SQL执行过程

参考文章

SQL执行顺序

from → where → group by → having → select → order by → limit

事务

事务四大特性

• 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
• 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
• 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
• 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务问题

• 赃读：一个事务读到另外一个事务还没有提交的数据。
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
• 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。

事务隔离界别

隔离级别	脏读	不可重复读	幻读
Read uncommitted(读未提交)	√	√	√
Read committed(读提交)(Oracle默认)	×	√	√
Repeatable Read(可重复度)(MySQL默认)	×	×	√
Serializable(可序化)	×	×	×

进阶

存储引擎

体系结构

• 连接层：连接处理、授权认证
• 服务层：核心服务，如SL接口、缓存查询、SQL优化
• 引擎层：负责数据的存储和提取，服务器通过API和存储引擎进行通信，索引是在存储引擎层实现的
• 存储层：将数据存储在文件系统中，完成与存储引擎的交互

MySQL是插件式存储引擎架构，将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。意味着不同表可以使用不同的存储引擎。

InnoDB

• 事务
• 行级锁
• 外键

适用条件：如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作

逻辑存储结构：

• 表空间
• 段
• 区 (1M)
• 页 (16K，B+树中的每个节点的单位)
• 行 (表中记录的每一行数据，InnoDB 存储引擎是面向行的，也就是说数据是按行进行存放的)

MyISAM

• 表锁
• 不支持事务、外键
• 速度快

适用条件：如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，如业务系统中的日志、电商中的足迹和评论等数据

Memory

• 内存存放
• hash索引
• 表锁

适用条件：Memory引擎的表数据时存储在内存中的，访问速度快，但是会受到硬件问题、或断电问题的影响，通常用于临时表及缓存。

索引

优点

• 降低了排序成本
• 提高检索效率

缺点

• 占用空间
• 提高了查询效率但是降低了更新速度，因为要维护

索引结构

• B+树（常见，InnoDB、MyISAM、Memory支持都支持）
• Hash（不支持范围查询，只有Memory支持，InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。）

使用二叉树：顺序插入会变成链表
使用红黑树：层级高
使用B树：一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度
使用Hash：不支持范围查询；查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引。

B+Tree 与 B-Tree相比，主要有以下三点区别：

• 所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

MySQL优化：
将单向链表改为双向链表且在头和尾处也加了双向指针，利于范围查询和排序

聚集索引选取规则

如果存在主键，主键索引就是聚集索引。
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

SQL性能分析

SQL执行频率

使用 show [session | global] status命令
如果是以增删改为主，我们可以考虑不对其进行索引的优化。
如果是以查询为主，那么就要考虑对数据库的索引进行优化了。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数的所有SQL语句的日志。

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

explain重要参数：

• type：表示连接类型，性能由好到差的连接类型为NULL、system、const、
  eq_ref、ref、range(索引上的范围查询)、 index(索引树扫描)、all(全表扫描) 。
• possible_key：显示可能应用在这张表上的索引，一个或多个。
• key：实际使用的索引，如果为NULL，则没有使用索引。
• key_len：表示索引中使用的字节数， 该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下， 长度越短越好 。这个与联合索引有关
• extra：解决查询的详细信息，例如出现Using temporary就需要进行优化了。
  Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort 排序。(最好进行优化)
  Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。(最好进行优化)
  Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

最左前缀法则

如果索引使用了多列（联合索引），要遵守最左前缀法则。
最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。
最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。
尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效情况

• 列运算：在索引列上进行运算操作， 索引将失效。
• 不加引号：字符串类型字段使用时，不加引号，索引将失效。
• 模糊查询：尾部模糊匹配(在关键字后面加%)，索引不会失效。头部模糊匹配(在关键字前面加了%)，索引失效。
• or连接：用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
• 如果MySQL评估使用索引比全表更慢，则不使用索引。如果走全表扫描更快，则放弃索引，走全表扫描。

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。尽量使用覆盖索引（联合索引且索引字段是所查询字段），减少select *。

前缀索引

将字符串的一部分前缀，建立索引。前缀长度可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好。

索引设计原则

1). 针对于数据量较大，且查询比较频繁的表建立索引。
2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。
8) 尽量降低主键的长度。一个原因是二级索引的内容是要存储主键，如果主键长度大，那么就会消耗更多空间。同时主键大的话搜索时候也会消耗更多时间。
9) 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。因为这样插入时候是乱序插入，性能比顺序插入差，参考索引主键顺序插入效果和主键乱序插入效果的区别。同时长度较大，与第8点同理
10) 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
11) 避免对主键的修改。

SQL优化

插入数据

• 批量插入
• 手动控制事务
• 按照主键顺序插入（涉及到页分裂）
• 大批量插入使用MySQL的load指令

主键优化

行数据，都是存储在聚集索引的叶子节点上的

• 页分裂
• 页合并

当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记（flaged）为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到 ERGE_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前
或后）看看是否可以将两个页合并以优化空间使用。

order by 优化

Using filesort : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index : 通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

创建联合索引可以声明索引的顺序为升序还是倒序

order by优化原则:
A. 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
B. 尽量使用覆盖索引。如果不是使用覆盖索引排序，那仍然是会出现filesort
C. 多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
D. 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小
sort_buffer_size(默认256k)。

group by 优化

A. 在分组操作时，可以通过索引来提高效率。
B. 分组操作时，索引的使用也是满足最左前缀法则的。

limit 优化

一般分页查询时，通过创建 覆盖索引 能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count 优化

• MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高； 但是如果是带条件的count，MyISAM也慢。
• InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count(数字) InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
count(*) InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count(*)，所以尽量使用 count(*)。

update 优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁 。

update要使用索引进行更新，不然就会导致行锁升级为表锁，性能下降

锁

全局锁

一旦加了全局锁之后，其他的DDL、DML全部都处于阻塞状态，但是可以执行DQL语句，也就是处于只读状态

数据库中加全局锁，是一个比较重的操作，存在以下问题：
如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆。
如果在从库上备份，那么在备份期间从库不能执行主库同步过来的二进制日志（binlog），会导致主从延迟。

表级锁

对于表级锁，主要分为以下三类：

• 表锁
• 元数据锁（meta data lock，MDL）
• 意向锁

表锁

对于表锁，分为两类：
表共享读锁（read lock）所有客户端（包含加锁的客户端）只能读不能写
表独占写锁（write lock）加锁的客户端可以读和写，其他客户端都不能读和写

元数据锁

meta data lock , 元数据锁，简写MDL。
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。

意向锁

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

分类：

• 意向共享锁
• 意向排他锁

行级锁

锁定粒度最小，发生锁冲突的概率最低，并发度最高。
行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

分类：

• 行锁（加在索引上，不是加在记录上，支持RC、RR）
• 间隙锁（避免幻读，支持RR）
• 临键锁（以上两种的组合，支持RR）

行锁分共享锁和排他锁：

• select默认不加锁，select + lock in share mode用共享锁，select + for update用排他锁
• insert、update、delete都是加排他锁

间隙锁（Gap Lock）：锁定索引记录间隙（不含该记录），确保索引记录间隙不变，防止其他事务在这个间隙进行insert，产生幻读。在RR隔离级别下都支持。

默认情况下，InnoDB在 REPEATABLE READ 事务隔离级别运行，InnoDB使用 next-key 锁进行搜索和索引扫描，以防止幻读。

• 索引上的等值查询(唯一索引)，给不存在的记录加锁时, 优化为间隙锁 。
• 索引上的等值查询(非唯一普通索引)，向右遍历时最后一个值不满足查询需求时，next-keylock 退化为间隙锁。
• 索引上的范围查询(唯一索引) – 会访问到不满足条件的第一个值为止。

对于可重复读默认使用的就是next key lock，但是对于“唯一索引”，比如主键的索引，next key lock会降级成行锁，而不会锁住一个区间。因此，如果上面的事务1的update使用的是主键，事务2也使用主键进行插入，那么实际上事务2根本不会被阻塞，可以立即插入并返回。而对于非唯一索引，next key lock则不会降级。

InnoDB引擎

事务原理

四大特性，实际上分为两个部分。
其中的原子性、一致性、持久化，实际上是由InnoDB中的两份日志来保证的，一份是redo log日志，一份是undo log日志。
而隔离性是通过数据库的锁，加上MVCC来保证的。

redo log 进阶篇 P116 物理日志，记录的是数据页的物理修改
undo log 进阶篇 P118 逻辑日志，记录的是相反操作，用于rollback

MVCC

• 当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。（在select后面加上 lock in share mode 共享锁）
• 快照读：读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。（简单的select）

• Read Committed：每次select，都生成一个快照读。
• Repeatable Read：开启事务后第一个select语句才是快照读的地方。
• Serializable：快照读会退化为当前读。

MVCC

原子性、一致性、持久型由 redolog 与 undolog 保证
隔离性由锁 + MVCC来保证

MVCC，全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undo log日志版本链、readView。

三个隐藏字段：

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。

undolog

回滚日志，在insert、update、delete的时候产生的便于数据回滚的日志。
当insert的时候，产生的undo log日志只在回滚时需要，在事务提交后，可被立即删除。
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会立即被删除。

版本链：P128

不同事务或相同事务对同一条记录进行修改，会导致该记录的undolog生成一条记录版本链表，链表的头部是最新的旧记录，链表尾部是最早的旧记录。

readview

ReadView中包含了四个核心字段：

字段	含义
m_ids	当前活跃的事务ID集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1（因为事务ID是自增的）
creator_trx_id	ReadView创建者的事务ID

而在readview中就规定了版本链数据的访问规则：
trx_id 代表当前undolog版本链对应事务ID。

条件	是否可以访问	说明
trx_id == creator_trx_id	可以访问该版本	成立，说明数据是当前这个事务更改的。
trx_id < min_trx_id	可以访问该版本	成立，说明数据已经提交了。
trx_id > max_trx_id	不可以访问该版本	成立，说明该事务是在ReadView生成后才开启。
min_trx_id <= trx_id<= max_trx_id	如果trx_id不在m_ids中，是可以访问该版本的	成立，说明数据已经提交。

不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED ：在事务中每一次执行快照读时生成ReadView。
REPEATABLE READ：仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView。

为什么MVCC无法解决幻读的问题

MVCC有时候能解决幻读，有时候不能。

能解决幻读的情况：
使用快照读的时候，幻读就是通过MVCC解决的。

不能解决幻读的情况：
使用当前读的时候，这时候需要借助gap锁来解决幻读。

使用MVCC出现幻读的情况：
1.a事务先select，b事务insert确实会加一个gap锁，但是如果b事务commit，这个gap锁就会释放（释放后a事务可以随意操作），
2.a事务再select出来的结果在MVCC下还和第一次select一样，
3.接着a事务不加条件地update，这个update会作用在所有行上（包括b事务新加的），
4.a事务再次select就会出现b事务中的新行，并且这个新行已经被update修改了.
上面这样，事务2提交之后，事务1再次执行update，因为这个是当前读，他会读取最新的数据，包括别的事务已经提交的，所以就会导致此时前后读取的数据不一致，出现幻读。
参考文章：https://www.cnblogs.com/xuwc/p/13873293.html

两次快照读之间插入了一次当前读，就可能会导致幻读的产生，因为在第二次快照读生成了新的ReadView，所以看到了之前看不到的东西。

运维篇

日志

错误日志

记录了当 mysqld 启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

二进制日志

二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句
作用：①. 灾难时的数据恢复；②. MySQL的主从复制。

查询日志

记录了客户端的所有操作语句

慢查询日志

记录了所有执行时间超过参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志

主从复制

主从复制是指将主数据库的 DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步。

MySQL支持一台主库同时向多台从库进行复制， 从库同时也可以作为其他从服务器的主库，实现链状复制。

MySQL 复制的优点主要包含以下三个方面：

• 主库出现问题，可以快速切换到从库提供服务。
• 实现读写分离，降低主库的访问压力。
• 可以在从库中执行备份，以避免备份期间影响主库服务。

原理

核心是二进制日志

分库分表

单数据库性能瓶颈：IO瓶颈、CPU瓶颈

拆分策略

• 垂直拆分、水平拆分
• 分库、分表

记法：垂直（大家不一样），水平（大家都一样），分库（以表为依据），分表（以字段为依据）

实现技术

shardingJDBC：（总结就是改写SQL）基于AOP原理，在应用程序中对本地执行的SQL进行拦截，解析、改写、路由处理。需要自行编码配置实现，只支持java语言，性能较高。
MyCat：（总结就是不改写SQL）数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语言，性能不及前者

MyCat

基于Java语言编写的MySQL数据库中间件。
开发人员只需要连接MyCat即可，而具体底层用到几台数据库，每一台数据库服务器里面存储了什么数据，都无需关心。

逻辑结构：

具体的数据存储还是在物理结构，也就是数据库服务器中存储的。

rule.xml

里面有一个配置属性叫algorithm，指定分片函数与function的对应关系，对分片结果有影响。

读写分离

读写分离,简单地说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作，从数据库提供读操作，这样能有效地减轻单台数据库的压力。

• 一主一从
  

• 双主双从
  

一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机。