[TOC]

MySQL学习笔记

1. MySQL基础知识

参考文章： mysql零散的基础知识

1.1 SQL命令

数据库SQL命令可以分为四组：DDL、DML、DCL和TCL

DDL(Data Definition Language)：

数据定义语言，处理数据库schemas和描述数据应如何驻留在数据库中

DML(Data Manipulation Language)：

数据操纵语言，包括常见的SQL语句，用于存储、修改、检索和删除数据库中的数据

DCL(Data Control Language)：

数据控制语言，包含如Grant之类的命令，主要涉及数据库系统的权限等控件

TCL(Transaction Control Language)：

事务控制语言，用于处理数据库中的事务

1.2 数据库范式

现在数据库范式最多满足3NF，普遍认为范式过高虽然对数据关系有更好的约束性，但也导致数据关系表增加而令数据库IO更易繁忙

第一范式：

第一范式要求数据库中的所有字段列都是单一属性，不可再分的。

第二范式：

在满足第一范式的基础上，数据库中的表不存在非关键字段对任意关键字段的部分依赖

第三范式：

在满足第二范式的基础上，所有的非主键列之间不存在传递依赖

数据库设计时，遵循范式和反范式一直都是一个颇受争议的问题。遵循范式对数据关系有更好的约束性，并且减少数据冗余，可以更好的保证数据一致性。而反范式则是为了获得更好的性能。因此实际中要选择适合自己业务场景的。

反范式设计时，要考虑到关系表之间的插入异常、更新异常和删除异常。

1.3 横表纵表

1.4 数据类型

整数类型(byte)： BIT、BOOL、TINY INT(1)、SMALL INT(2)、 MEDIUM INT(3)、INT(4)、BIG INT(8)

浮点数类型： FLOAT(4)、DOUBL(8)、DECIMAL

字符串类型： CHAR、VARCHAR、TINY TEXT、 TEXT、MEDIUM TEXT、 LONG TEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB

日期类型： DATE、TIME、YEAR、DATETIME、TIMESTAMP

其他数据类型：ENUM、BINARY

CHAR和VARCHAR的区别什么？

• CHAR(n)和VARCHAR(n)的长度n都代表存放字符的个数，超过设定长度n的字符串会被截断
• CHAR是固定长度，VARCHAR是可变长度
• CHAR无论是否存满都会占用指定长度，不足的补空；VARCHAR只占用实际字符的长度，超出会截断
• CHAR的长度上限是255，VARCHAR是65535

BLOB和TEXT的区别是什么？

• BLOB是一个二进制对象，可以容纳可变数量的数据
• TEXT是一个不区分大小写的BLOB，保存的是字符数据

2. MySQL架构

待完成。 为什么别人能用好MySQL？

MySQL的逻辑架构可以分为四层，包括连接层、服务层、引擎曾和存储层。（InnoDB引擎下）

• 连接层：负责处理客户端的连接以及权限的认证
• 服务层：定义有许多不同的模块，包括权限判断，SQL接口，SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行等。MySQL的查询语句在读物曾进行解析、优化、缓存以及内置函数的实现和存储。
• 引擎层：负责MySQL中数据的存储和提取。MySQL中的服务器层不管理事务，事务是由存储引擎实现的，使用最广泛的存储引擎为InnoDB，其他不支持事务。
• 存储层：负责将数据存储到设备的文件系统中。

3. MySQL存储引擎

参考文章： MySQL存储引擎 - InnoDB-一棵树可以放多少数据

存储引擎是MySQL的组件，用于处理不同表类型的SQL操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。 存储引擎是针对数据表的，每个表创建时不指定则会使用默认引擎。

3.1 存储引擎的设置和修改

查看存储引擎：

-- 查看支持的存储引擎
SHOW ENGINES

-- 查看默认存储引擎
SHOW VARIABLES LIKE 'storage_engine'

--查看具体某一个表所使用的存储引擎，这个默认存储引擎被修改了！
show create table [tablename]

--准确查看某个数据库中的某一表所使用的存储引擎
show table status like 'tablename'
show table status from database where name = 'tablename'

设置存储引擎：

-- 建表时指定存储引擎。默认的就是INNODB，不需要设置
CREATE TABLE [tablename] (int i...) ENGINE=CSV

-- 修改存储引擎
ALTER TABLE [tablename] ENGINE=InnoDB

-- 修改默认存储引擎，也可以在配置文件my.cnf中修改默认引擎
SET default_storage_engine=NDBCLUSTER

3.2 常见存储引擎

MySQL常见的存储引擎有 InnoDB、MyISAM、Memory、NDB，其中InnoDB是默认引擎。

InnoDB存储引擎

• InnoDB支持事务，面向在线事务处理(OLTP)的应用。这也是MySQL将默认引擎改为InnoDB的重要原因之一。
• InnoDB支持外键，MyISAM不支持，对有外键的InnoDB表转成MyISAM会失败。
• InnoDB是聚簇索引，数据文件放在主键索引的叶子节点上，因此必须要有主键，主键索引效率高，使用辅助索引需要进行主键索引的回表查询（覆盖索引除外）。
• InnoDB支持最小锁粒度是行锁，数据并发性能更好。这也是改用InnoDB引擎的重要原因之一。
• InnoDB不保存表的具体行数，执行 select count(1) from [tablename] 时需要扫描全表；而MyISAM有数据行计数变量，速度更快。
• MySQL5.5.8版本之后，将默认存储引擎改成InnoDB。

MyISAM存储引擎

• MyISAM引擎不支持事务，主要面向一些OLAP数据库应用。
• MyISAM引擎的索引与InnoDB不同的是，其索引与实际数据是分离的，索引中叶子节点存放的是具体数据的地址，是非聚集索引，主键索引和辅助索引相互独立，叶子节点中都存放了数据文件的指针。
• MyISAM支持的最小锁粒度是表锁，一个更新语句会锁定整张表，并发性能低，安全性高。
• MyISAM中保存了表的具体行数，查询数量时会直接读取计数变量返回，速度很快。
• MyISAM引擎支持全文索引，InnoDB在5.6后才加入了全文索引。
• MyISAM引擎更注重性能，速度较快一些。

Memory存储引擎

• Memory存储引擎将表中的数据放在内存中，速度更快，但是不支持持久化。

4. MySQL索引

参考文章： 一口气搞懂MySQL索引所有知识点

4.1 索引的介绍

索引是帮助MySQL高效获取数据的数据机构，类似一本书的目录索引。

通常所说的索引，有聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，默认都是使用B+树的结构组织索引。

使用索引的好处和坏处

好处：

• 索引可以提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低了数据排序对CPU的消耗
  • 索引列会自动排序，包括单列索引和组合索引
  • 如果按照索引列进行排序，order by 语句的效率会非常高

坏处：

• 索引会占据磁盘空间
• 索引虽然能够提高数据查询效率，但是会降低数据更新的效率，因为每次增删改数据操作是，都要保存或更新相应的索引文件。

4.2 索引的类型

• 唯一索引：索引列中的值必须是唯一的额，但是允许为null值
• 主键索引：特殊的唯一索引，值唯一且不允许为null值
• 普通索引：MySQL中基本索引类型，允许列值重复，也允许为null
• 全文索引：只能在CHAR、VARCHAR、TEXT类型字段上创建全文索引。当字段长度比较大时，普通索引中使用like进行模糊查询效率比较低，可以使用全文索引
• 空间索引：5.7后支持全文索引，支持OpenGIS集合数据模型
• 前缀索引：在CHAR、VARCHAR、TEXT类型的列上创建索引时，可以指定索引列的长度，即列内容的前缀进行索引。

按照索引列的数量分类

• 单列索引
• 组合索引：组合索引使用时要遵循最左前缀匹配元组，条件允许的情况下使用组合索引替代多个单列索引

4.3 索引的数据结构

Hash表：

Hash表在等值查询时效率很高，但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式；因此不适合作为经常需要查找和范围查找的数据库索引。

二叉查找树：

二叉树正常情况下可以进行折半查找而减少IO次数，但是二叉树不稳定，容易形成单链，查找时仍然很复杂，这种不稳定的结构选择设计时会尽量避免。

平衡二叉树：

平衡二叉树除了具备二叉查找树的特点，最主要的特征就是左右两个子树的层级最多相差1，在插入删除数据时要使用左旋/右旋操作来保持二叉树的平衡。平衡二叉树的查询性能最接近二分查找法，但是仍有一些不足之处：

• 时间复杂度和树高有关，每个节点的读取对应一次磁盘IO操作，数据量大时查询性能会比较差
• 平衡二叉树不支持范围快速查找，范围查询时要从根节点进行多次遍历

B树：

MySQL的数据是存储在磁盘文件中的，查询处理数据时，需要把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，因此我们优化的重点就是要尽量减少磁盘IO操作的次数。二叉树访问时每个节点就会发生一次磁盘IO，因此可以通过降低树的高度来减少磁盘IO的次数。

二叉树一个节点的数据结构为一个val+两个children指针，大概16(8+4*2)字节，而InnoDB一次IO默认会读取一页的数据量(16K)，这样每次IO操作数据空间利用率极低，为了最大化利用一次IO空间，就是在每个节点中存放多个元素，即每个节点存储更多的数据，这样就能将树的高度降的很低，这种结构就称为B树。

• B树是一种多叉平衡查找树，特点有：
  • B树的节点中存储着多个元素，每个内节点有多个分叉
  • 节点中的元素包含键值和数据(data)，按照键值从小到大排列，即所有的节点中都存储了数据
  • 父节点中的元素不会出现在子节点中
  • 所有的叶子节点都位于同一层，即叶子有着相同的深度，叶子节点之间没有指针连接
• B树结构图
• 使用B树进行查找的过程：
  • B树每个节点中的数据是一次IO操作读取出来的
  • B树种每个节点种存放许多个键值，IO操作读取到内存种后，会进行遍历比较，再查找指定的节点或根据键值返回数据
  • 多叉树、每个节点存放多个键值，大大减少了树的高度，减少了磁盘IO的次数
• B树的不足点：
  • B树不支持范围查询的快速查找，如果要进行范围查询，仍然是要从根节点多次遍历查询
  • 因为B树节点中存放多个键以及对应的data，data对应数据库表的一行数据，随着表中的列增加，data占用空间变大，则一个节点中可以存放的数据个数就会减少，树会变高，查询时磁盘IO次数也会增加。

B+树：

B+树是在B树的基础上进行了改造，B+树相对B树的改变主要有：

• B+树只有叶子节点才会存储数据，非叶子节点只存放键(进行比较，可以覆盖索引)
• B+树叶子节点之间使用双向指针连接，最底层叶子节点形成了双向有序链表
• B+树由于只在叶子节点中存放数据，因此叶子节点中包含了所有的索引项(查找并返回数据)

由于数据都放在了叶子节点中，非叶子节点只存放键值，每个节点中可以存放更多的索引键值，因此从理论上讲B+树高度是要比B树低的。

虽然非叶子节点中不存放数据，但是也存在覆盖索引的情况，如果索引中数据(key/id)满足要求，也可能直接返回，无需检索到最底层的叶子节点。

• 等值查询：

  B+树进行等值查询时，在叶子节点找到节点时，取出对应的data，如果data是实际的数据(InnoDB中叶子节点存放数据)则直接返回；如果是对应的地址(MyIsam中存放的是磁盘地址)，则根据磁盘地址到磁盘中取出数据，完成查询。

• 范围查询：

  B+树进行范围查询时，首先进行范围左端点的查询，与等值查询一样完成当前值的查询，之后会直接使用叶子节点的链表进行后续的查询，读取其他磁盘页，最终完成整个范围查询（叶子节点中整体有序）。

• 总结：B+树保证了等值查询是快速的，也保证了范围查询的快速查找。

4.4 索引的使用

具体索引使用过程，以MyIsam存储引擎中的索引和InnoDB存储引擎中索引的使用不同来介绍

MyIsam存储引擎：

MyIsam引擎中也是使用的B+树构建索引树，叶子节点存放的键是列的值，data是索引所在行的磁盘地址。

MyIsam中的主键索引和辅助索引的结构是完全一样的，叶子节点存储的数据都是记录行所在的磁盘地址，查询数据时，最终要根据磁盘地址读取数据。

**InnoDB存储引擎： **

InnoDB存储引擎相比于MyIsam可以定义自增主键，且主键索引更为特殊，也叫做聚簇索引，所有非主键索引都叫做辅助索引。

规则： InnoDB会将定义的主键列作聚簇索引；如果没有定义主键则会选择第一个不为null的唯一索引作聚簇索引；如果都没有，则会生成一个隐式字段构建聚簇索引。

• 聚簇索引： InnoDB的索引中，只有聚簇索引的叶子节点存放data数据，使用主键查询到叶子节点后返回数据
• 辅助索引： 辅助索引的叶子节点存放的都是主键值，使用辅助索引时会先查询到叶子节点中的主键，根据主键值去到聚簇索引中进行回表查询data
• 联合索引： 联合索引创建时，是以联合索引中列的数据进行排序，因此使用联合索引进行查询是，要符合最左匹配原则，即检索列的顺序要和创建索引时顺序一致，否则不会走索引。
• 覆盖索引： 覆盖索引是一种常见的优化手段，即在使用辅助索引时，要先拿到主键索引再回表查询数据，如果当前所有中包含了需要查询的所有内容（字段值+主键值），则不需要进行主键的回表查询，即覆盖索引。

数据库索引优化手段：

1. 避免回表

   

2. 使用联合索引

   • 使用联合索引替代多个单列索引，不仅可以节省空间，还更容易使用到覆盖索引
   • 联合索引创建时，将频繁使用、区分度高的列放在前边
   • 如果当前索引存在频繁查询的返回列，可考虑将此列加入联合索引，更容易使用覆盖索引减少回表查询

为什么推荐使用整型自增主键而不是选择UUID？

• UUID是字符串，比整型消耗更多的存储空间
• B+树查找时要进行节点值的比较，整型值要比字符串更容易比较（字符串会依次比较每一个字符）
• 自增的整型索引在磁盘中存储时会连续存储，读取时也是连续；而UUID随机产生，读取时表中上下两条数据分散的，范围查询时效果差一些
• 插入或删除数据时，整型自增主键建树时不会破坏树结构，UUID新增数据时可能会引起树结构的重新构建

4.5 索引的使用语句

---创建索引        如果是BLOB、TEXT类型，必须指定length 
CREATE [UNIQUE] INDEX [indexname] ON [tablename](columnName(length));
---修改表结构(添加索引) 
    ---- PRIMARY Key / UNIQUE [indexname] / INDEX [indexname] / FULLTEXT [indexname]
    ---- 主键索引        唯一索引                普通索引            全文索引
ALTER table [tablename] ADD [UNIQUE] INDEX [indexname](columnname)

---删除索引
DROP INDEX [indexname] ON [tablename]

---查看索引信息    \G用来格式化输出信息
SHOW INDEX FROM [tablename] \G

5. MySQL事务

参考文章： 彻底搞懂MySQL事务日志 MySQL事务日志-原来MySQL面试还会问这些

必须了解的 MySQL 三大日志 MySQL事务与MVCC机制 MySQL事务的10个问题

5.1 事务特性（ACID）

• 原子性（Atomicity）：语句要么全执行，要么全不执行，是事务最核心的特性，事务本身就是以原子性来定义的，主要是基于undo log日志来实现的。
• 一致性（Consistency）：事务追求的最终目标，一致性的实现需要数据库层面的保障，也需要应用层面的保障。数据库的完整性约束没有被破坏。
• 隔离性（Isolation）：保证事务执行尽可能不受其他事务影响，InnoDB默认的隔离级别是RR，RR的实现主要基于锁机制（行锁）、数据的隐藏列（MVCC）、undo log、和类next-key lock机制。
• 持久性（Durability）：保证事务提交后不会因为宕机等原因导致数据丢失，主要基于redo log 实现。

5.2 并发读写问题

在并发情况下，MySQL的同时读写可能会导致三类问题：脏读、不可重复读、幻读

• 脏读：当前事务中读取到其他事务未提交的数据，就是脏读
  • 事务A读取数据时，读取到事务B未提交的数据，而事务B最后没有顺利提交，导致事务回滚，这时事务A读到的内容就是脏数据。
• 不可重复读：在事务A中现后两次读取同一个数据，但两次读取的结果不一样；
  • 脏读是读到了其他事务未提交的数据，而不可重复读是读取到了其他事务新提交的内容，即事务A在执行的过程中，数据被其他事务修改了
• 幻读：事务A中按照某个条件现后两次查询数据库，两次结果的行数不同，这种现象称为幻读。
  • 不可重复读是两次读取的数据发生了变化，重在更新；幻读则是两次读取的数据行数不同，重在有新数据插入。

5.2 事务隔离级别

参考文章：Innodb中的事务隔离级别和锁的关系

事务的隔离级别

• Read UnCommitted（读未提交）：可能发生脏读、不可重复读、幻读
• Read Committed（读已提交）：不会发生脏读，可能发生不可重复读、幻读
• Repeatable Read（可重复读）：不会发生脏读、不可重复读，可能发生幻读
• Serializable（可串行化）：不会发生脏读、不可重复读、幻读

MVCC

MVCC（Muti-Version Concurrency Control，多版本并发控制协议），就是用来实现可重复读隔离级别的。

MVCC的特点就是，在同一时刻，不同事务可以读取到不同版本的数据，从而解决脏读和不可重复读的问题。

• MVCC实际上就是通过数据的隐藏列和回滚日志（undo log），实现多个版本数据的共存，这样的好处就是，使用MVCC读取数据时，不用加锁就可以避免同时读写的冲突。
• MVCC的每一行数据重会保存隐藏列，如创建时版本号、删除时版本号、指向undo log的回滚指针。每次开始新的事务，版本号就会递增，MVCC机制下，事务会查询小于等于创建版本号和大于等于删除版本号的数据，来保证数据的可重复读。
• next-key lock： 行锁的一种，即Record Lock（行锁）+gap lock（间隙锁），RR级别下就是通过next-key lock来避免幻读现象，其会将查询数据的数据行以及数据行的间隙进行加锁。防止其他事务在这个间隙修改或插入数据。
  • 间隙锁必须保证查询条件有索引，否则会加表锁而不是行锁（无索引时扫描全表）
• MVCC是快照读来避免幻读，间隙锁是当前读中避免幻读

5.3 事务日志

MySQL日志系统是数据库的重要组件，用于记录数据库的更新和修改，在数据库发生故障时可以通过不同的日志记录恢复数据，日志系统决定着MySQL的鲁棒性和稳健性。

MySQL的日志有很多种，如二进制日志（bin log）、错误日志、查询日志、慢查询日志等，此外，InnoDB存储引擎还提供了两种日志，redo log（重做日志）和undo log（回滚日志）

• 重做日志（redo log）： 重做日志是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。（即什么地方从什么改成了什么这样）

  • 预写式技术（Write Ahead logging）：InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或是按照设定的更新策略将日志种的内容更新到磁盘中，这就是所谓的预写式技术，这种技术可以大大减少IO操作的频率，提升数据刷新的效率。
  • 脏数据刷盘：redo log日志的大小是固定的，为了能够持续不断的对更新记录进行写入，redo log中设置了两个标识位置，checkpoint（记录擦除的位置）和write_pos（记录写入的位置）。移动是循环的，类似圆周运动，当写位置追上擦除位置时，会停下来进行脏数据刷盘（即将redo log中的数据写入到磁盘中）。
  • 缓冲池（buffer pool）： 在内存中分配的一个区域，包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。
    • 当请求读取数据时，会判断是否在缓冲池命中，如果未命中才会在磁盘上进行检索后放入缓冲池；
    • 当请求写入数据时，会先写入缓冲池，缓冲池中修改的数据会定期刷新到磁盘中，即刷脏
    • 当数据修改时，除了会修改缓冲池中数据，还会在redo log中记录当前操作，事务提交时，会根据redo log的记录的数据进行刷盘。

• 二进制日志（bin log）：二进制日志（bin log）是服务层的日志，被称为归档日志。bin log 主要是记录数据库的变化情况，内容包括数据库所有的更新操作。所有涉及数据变动的操作，都要记录进二进制日志中。有了binlog可以很方便的对数据进行复制和备份，因而常用作主从数据的同步。

  • redo log记录的是物理日志，即对某个数据进行了怎么样的修改；而binlog是逻辑日志，记录的是SQL语句的原始逻辑。
  • redo log是InnoDB引擎特有的，作用在引擎层；binlog是通过MySQL的服务层实现的，所有引擎都可以使用
  • redo log日志文件的空间固定，因此使用时是循环写入和擦除；binlog日志文件的空间不固定，写完会切换下一个文件，因此是追加写入，不会覆盖已有文件。

• 一条SQL语句执行时，涉及到binlog和redo log 的读写：

  
  • MySQL执行更新语句时，服务层进行语句的解析和执行，引擎层进行数据的提取和存储；同时在服务存对binlog写入，在引擎层进行redo log的写入。
  • 对redo log写入时有两个阶段的提交，一个是binlog写入之前prepare状态的写入，二是binlog写入之后commit状态的写入，保证了binlog和redo log日志的一致性。

• 回滚日志（undo log）： 回滚日志也是InnoDB引擎提供的日志，是对数据进行回滚。在事务对数据库进行修改时，InnoDB引擎不仅会记录redo log，还会生成对应的undo log日志，如果事务失败执行rollback，事务回滚，就会利用undo log中的信息将数据回滚到之前的样子。

  • 与redo log不同的是，undo log也是逻辑日志，记录了SQL语句执行相关的信息，当发生回滚时，InnoDB引擎会根据undo log日志中的记录做与之前相反的工作。
  • undo log日志有两个作用，一个是提供事务回滚，一个是实现MVCC。

6. MySQL锁机制

7. MySQL主从复制

主从复制就是从原来的数据库复制一个完全一样的数据库，并保持主、从数据库数据的一致性。

主从复制的原理实际上就是通过binlog日志实现的，binlog日志中保存了数据库中所有SQL语句，通过对binlog日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。

主从复制的过程如图，主要涉及到三个线程

• 主服务器中的发送线程：负责将binlog日志发送到从服务器
• 从服务器上的I/O线程：用来读取主服务器发送过滤的binlog日志内容，并拷贝到本地中继日志中
• 从服务器的SQL线程：用来读取中继日志中关于数据更新的SQL语句并执行，实现主从数据一致
• 主从复制的好处有：
  • 通过复制实现数据的异地备份，避免数据丢失
  • 实现架构的扩展，业务量越来越大时，I/O访问量过高时，采用多库存储，降低磁盘I/O频率
  • 实现读写分离，使数据库能支持更大的并发
  • 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷

8. MySQL存储过程

9. MySQL优化