【技术修养】关于 MySQL 的基础知识整理

前言

作为一个后端开发工程师，数据库是非常重要的知识。对于国内大多数互联网公司来说，MySQL 无疑是最常见也是最多使用的数据库。这篇文章，简单聊聊 MySQL 的一些原理知识和使用经验(里面有些内容来自网络，这里做了归纳整理，相关引用来源放在 reference 中)

如何设计合理的表结构？

在后端业务场景中，我们需要对数据库的表结构进行设计，以方便我们更合理的存储和管理业务数据。那我们应该如何设计出简洁的、结构清晰、不冗余、易管理、易查询的数据表？

众多的实践总结出了一套可复用的方法论。这里主要包含表的设计范式，合理的设计索引

设计范式&反范式

数据库主要包含三大范式和逆范式的设计理念。（当然，也存在第四范式，第五范式等等，但是一般满足三大范式就足够应付绝大多数业务场景）

三大范式

第一范式

• 所谓第一范式(1NF) 是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值。若某一列有多个值，可以将该列单独拆分成一个实体，新实体和原实体间是一对多的关系。

第二范式

• 第二范式(2NF) 在第一范式的基础上更进一步，目标是确保表中的每列都和主键相关，即：非主属性必须完全依赖于主键。

  如果一个关系满足第一范式，并且除了主键之外的其他列，都依赖于该主键，则满足第二范式。

第三范式

• 第三范式(3NF) 是指实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。即：属性不依赖于其他非主属性。
  如果一个实体中出现其他实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

逆范式

为什么在有三大范式之后，还会出现逆范式呢？这主要是归结于业务的场景的复杂性和业务对查询速度较高的要求。举个例子，一个业务场景下涉及多张表结构数据，包含一张主题表和多扩展表和属性表。这样的业务需求往往需要多表查询，或者连表 join. 为了降低查询的复杂度和提高查询的响应速度，有时候我们会在主题表里冗余存储扩展表和属性表里面的字段数据，这样可以提供我们的查询效率。

不过在出现需要逆范式的场景时，需要仔细斟酌一下是否有必要。或者之前的表结构是否设计合理。又或者考虑我们是否可以将多表数据旁路汇总到 NoSQL(比如 es, mongoDB) 中，以更好的提高我们的查询效率。

索引

什么是索引？简单来说，索引就像是书的目录，当我们想要查询某个知识的时候，我们会先查询目录，找到对应知识点的页面，再到指定页面进行翻阅，而不需要从头开始一页一页去找。

索引的原理

数据结构

不同索引的设计会使用的数据结构。对于 MySQL 来说，我们需要重点了解 B+树，B 树，Hash。

关于 B+ 树、B 树、hash 的介绍可以参考如下 Wikipedia。

Wikipedia: B+树

Wikipedia: B树

Wikipedia: hash

B+ 树中的每个节点都是一个数据页，其结构示意图如下

B 树的结构示意图如下：

Hash 的结构示意图如下：

索引分类

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。

唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

• 可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
• 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

• 可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
• 可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引

全文索引： 是目前搜索引擎使用的一种关键技术。

联合索引： 通过将多个字段组合成一个索引，该索引就被称为联合索引。

⚠️：在 MySQL B+ 树的索引之下，主键索引的叶子结点存储数据，其他的索引的叶子结点存储的是主键索引的是位置。除非是覆盖索引，不然都需要回表查询。

索引的设计

索引设计的原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

3.尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。

4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

设计覆盖索引以避免回表查询

在 MySQL B+ 树的索引之下，主键索引的叶子结点存储数据，其他的索引的叶子结点存储的是主键索引的是位置。除非是覆盖索引，不然都需要回表查询。

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到，这样就不需要回表查询了。

在B+树的索引中，叶子节点可能存储了当前的key值，也可能存储了当前的key值以及整行的数据，这就是聚簇索引和非聚簇索引。 在InnoDB中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则隐式的生成一个键来建立聚簇索引。

最左前缀原则

创建联合索引时，我们需要注意创建时的顺序问题，因为联合索引 (a, b, c) 和 (c, b, a) 在使用的时候会存在差别。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

比如，如果创建了一个 (a, b, c) 联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

• where a=1；
• where a=1 and b=2 and c=3；
• where a=1 and b=2；

需要注意的是，因为有查询优化器，所以 a 字段在 where 子句的顺序并不重要。

但是，如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效:

• where b=2；
• where c=3；
• where b=2 and c=3；

有一个比较特殊的查询条件：where a = 1 and c = 3 ，符合最左匹配吗？

这种其实严格意义上来说是属于索引截断，不同版本处理方式也不一样。

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，Server 层从存储引擎层获取到数据行后，然后在 Server 层再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

索引下推的大概原理是：截断的字段不会在 Server 层进行条件判断，而是会被下推到「存储引擎层」进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引里的），然后过滤出符合条件的数据后再返回给 Server 层。由于在引擎层就过滤掉大量的数据，无需再回表读取数据来进行判断，减少回表次数，从而提升了性能。

比如下面这条 where a = 1 and c = 0 语句，我们可以从执行计划中的 Extra=Using index condition 使用了索引下推功能。

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

锁

锁的分类

全局锁

要使用全局锁，则要执行这条命令：

flush tables with read lock

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据的增删改操作，比如 insert、delete、update等语句；
• 对表结构的更改操作，比如 alter table、drop table 等语句。

如果要释放全局锁，则要执行这条命令：

unlock tables

当然，当会话断开了，全局锁会被自动释放。

表级锁

//表级别的共享锁，也就是读锁；
lock tables t_student read;

//表级别的独占锁，也就是写锁；
lock tables t_stuent write;

// 释放锁
unlock tables  // 另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

MySQL 里面表级别的锁有这几种：

• 表锁；
• 元数据锁（MDL）;
• 意向锁；
• AUTO-INC 锁；

行锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

事务

事务的基本要素（ACID）

1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

mysql默认的事务隔离级别为repeatable-read

事务的实现原理

事务隔离级别是怎么实现的？

MYSQL 事务的底层原理

存储引擎

引擎是什么？

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

在文件系统中，MySQL将每个数据库（也可以称之为schema）保存为数据目录下的一个子目录。创建表时，MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。例如创建一个名为 MyTable的表，MySQL会在MyTable.frm文件中保存该表的定义。

因为MySQL使用文件系统的目录和文件来保存数据库和表的定义，大小写敏感性和具体的平台密切相关。在Windows中，大小写是不敏感的；而在类Unix中则是敏感的。不同的存储引擎保存数据和索引的方式是不同的，但表的定义则是在MySQL服务层统一处理的。

查看支持的引擎

show engines;

InnoDB

InnoDB是MySQL默认的事务型引擎，也是最重要、最广泛的存储引擎。它的设计是用来处理大量短期事务，短期事务大部分是正常提交的，很少回滚。InnoDB的性能和自动崩溃恢复特性，使得它在非事务型存储的需求中，也很流行。除了非常特别的原因需要使用其他引擎，InnoDB也是非常好值得花时间研究的对象。

InnoDB的数据存储在表空间中，表空间是由InnoDB管理的黑盒文件系统，由一系列系统文件组成。InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间存储介质。

InnoDB通过间隙锁（next-key locking）防止幻读的出现。InnoDB是基于聚簇索引建立，与其他存储引擎有很大的区别，聚簇索引对主键查询有很高的性能，不过它的二级索引（secondary index，非主键索引）必须包含主键列。所以如果主键列很大的话，索引会很大。

MyISAM

在5.1之前，MyISAM是默认的引擎，MyISAM有大量的特心态，包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁，而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务，但是很多人的概念中依然是不支持事务的引擎。

MyISAM并不是无所事处。对于一些只读数据，或者表空间较小，可以忍受恢复操作，可以使用MyISAM。MyISAM会将表存储在两个文件中：数据文件、索引文件。分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表定义选择那种行格式。MyISAM表的行记录数，取决于磁盘空间和操作系统中的单个文件最大尺寸。

在MySQL中，默认配置只能存储256TB的数据。因为指向数据记录的指针长度是6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项。两个相乘是最大的大小。会导致重建索引。

MyISAM是对整个表加锁，而不是行锁，读取的时候对表加共享锁，写入的时候加排他锁。但是在表有读取查询的同时，也可以往表内写入记录。

对于MyISAM，即使是Blob，Text等等长字段，也可以基于前500字符创建索引，MyISAM支持全文索引，这是一个基于分词创建的索引，也可以支持复杂的查询。

MyISAM可以选择延迟更新索引键，在创建表的时候指定delay_key_write选项，在每次修改执行完成时，不会立刻将修改的索引数据写入磁盘，而是写到缓存区，只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能，但是在主机崩溃时会造成索引损坏，需要执行修复操作。

MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改，那么这个表适合MyISAM压缩表。可以使用myisampack对MyISAM表进行打包，压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用，因此可以减少磁盘IO，提升性能，压缩表也支持索引，但是索引也是只读的。

整体来说MyISAM并没有那么不堪，但是由于没有行锁机制，所以在海量写入的时候，会导致所有查询处于Locked状态。

其他存储引擎

MySQL还有一些其他特殊用途的引擎，有些可能不再支持，具体支持情况参考数据库支持引擎。

这个简单了解即可，实现中基本上用不上这些

Archive

Archive引擎支持是Insert，Select操作，现在支持索引，Archive引擎会缓存所有的写，并利用zlib对写入行进行压缩，所以比MyISAM表的磁盘IO更少。但是在每次Select查询都需要执行全表扫描。所以在Archive适合日志和数据采集应用。这类应用在分析时往往需要全表扫描忙活着更快的Insert操作场景中也可以使用。

Archive引擎支持行级锁和专用的缓存区，所以可以实现高并发写入，在查询开始到返回表存在的所有行数之前，Archive会阻止其他Select执行，用来实现一致性读。另外也实现了批量写入结束前批量写入数据对读操作不可见，这种机制模仿了事务和MVCC的特性，但是Archive不是一个事务型引擎，而是针对高写入压缩做了优化的简单引擎。

Blackhole

Blackhole没有实现任何存储机制，它会舍弃所有写入数据，不做任何保存，但是服务器会记录Blackhole表的日志，用于复制数据到备库，或者只是简单的记录到日志，这种特殊的存储引擎可以在一些特俗的复制架构和日志审核时发挥作用。但是不推荐。

CSV

CSV引擎可以将普通的CSV文件作为MySQL表来处理，但是这种表不支持索引，CSV可以在数据库运行时拷贝或者拷出文件，可以将Excel等电子表格中的数据存储未CSV文件，然后复制到MySQL中，就能在MySQL中打开使用。同样，如果将数据写入到一个CSV引擎表，其他外部程序也可以从表的数据文件中读取CSV的数据。因此CSV可以作为数据交换机制。非常好用。

Federated

Federated引擎是访问其他MySQL服务器的一个代理，它会创建一个到远程MySQL服务器的客户端连接，并将查询传输到远程服务器执行，然后提取或者发送需要的数据。最初设计该存储引擎是为了和企业级数据库如MicrosoftSQLServer和Oracle的类似特性竞争的，可以说更多的是一种市场行为。尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

Memroy

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表（以前也叫做HEAP表）是非常有用的。Memory表至少比MyISAM表要快一个数量级，因为所有的数据都保存在内存中，不需要进行磁盘I/O。Memory表的结构在重启以后还会保留，但数据会丢失。

Memroy表在很多场景可以发挥好的作用：

1. 用于查找（lookup）或者映射（mapping）表，例如将邮编和州名映射的表。
2. 用于缓存周期性聚合数据（periodicallyaggregateddata）的结果。
3. 用于保存数据分析中产生的中间数据。

Memory表支持Hash索引，因此查找操作非常快。虽然Memory表的速度非常快，但还是无法取代传统的基于磁盘的表。Memroy表是表级锁，因此并发写入的性能较低。它不支持BLOB或TEXT类型的列，并且每行的长度是固定的，所以即使指定了VARCHAR列，实际存储时也会转换成CHAR，这可能导致部分内存的浪费。如果MySQL在执行查询的过程中需要使用临时表来保存中间结果，内部使用的临时表就是Memory表。如果中间结果太大超出了Memory表的限制，或者含有BLOB或TEXT字段，则临时表会转换成MyISAM表。

Merge

Merge引擎是My ISAM引擎的一个变种。Merge表是由多个MyISAM表合并而来的虚拟表。如果将MySQL用于日志或者数据仓库类应用，该引擎可以发挥作用。但是引入分区功能后，该引擎已经被放弃

NDB 集群 引擎

NDB集群存储引擎，作为SQL和NDB原生协议之间的接口。MySQL服务器、NDB集群存储引擎，以及分布式的、share-nothing的、容灾的、高可用的NDB数据库的组合，被称为MySQL集群（MySQLCluster）。

慢 SQL 排查

查询优化神器 - explain命令

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

1. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
2. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4. order by limit 形式的sql语句让排序的表优先查
5. 了解业务方使用场景
6. 加索引时参照建索引的几大原则
7. 观察结果，不符合预期继续从0分析

开启慢查询

有两种方式可以开启慢查询

1. 修改配置文件
2. 设置全局变量

方式一需要修改配置文件 my.ini，在[mysqld]段落中加入如下参数：

[mysqld]log_output='FILE,TABLE'slow_query_log='ON'long_query_time=0.001

然后需要重启 MySQL 才可以生效，命令为 service mysqld restart

方式二无需重启即可生效，但是重启会导致设置失效，设置的命令如下所示：

SET GLOBAL slow_query_log = 'ON';SET GLOBAL log_output = 'FILE,TABLE';SET GLOBAL long_query_time = 0.001;

这样就可以将慢查询日志同时记录在文件以及 mysql.slow_log 表中。

通过第二种方式开启慢查询日志，然后使用全表查询语句 SELECT * FROM user

然后再查询慢查询日志：SELECT * FROM mysql.slow_log，可以发现其中有这样一条记录：

其中，start_time 为执行时间，user_host 为用户的主机名，query_time 为查询所花费的时间，lock_time 为该查询使用锁的时间，rows_sent 为这条查询返回了多少数据给客户端，rows_examined 表示这条语句扫描了多少行，db 为数据库，sql_text 为这条 SQL，thread_id 为执行这条查询的线程 id。

这样我们就可以通过 slow_log 表的数据进行分析，然后对 SQL 进行调优了。

以上是通过 Table 来进行分析的，下面来通过文件的慢查询是怎么样的。

如果不知道文件保存在哪里，可以使用 SHOW VARIABLES LIKE '%slow_query_log_file%' 来查看文件保存位置，打开慢查询日志文件，可以看出每五行表示一个慢 SQL，这样查看比较费事，可以使用一些工具来查看。

慢查询日志文件

mysqldumpslow

MySQL 内置了 mysqldumpslow 这个工具来帮助我们分析慢查询日志文件，Windows 环境下使用该工具需要安装 Perl 环境。

可以通过 -help 来查看它的命令参数：

比如我们可以通过 mysqldumpslow -s t 10 LAPTOP-8817LKVE-slow.log 命令得到按照查询时间排序的 10 条 SQL 。

pt-query-digest

除此之外还有 pt-query-digest，这个是 Percona Toolkit 中的工具之一，下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/，如果是 Windows 系统，可以在安装 Perl 的环境下，把脚本下载下来：https://raw.githubusercontent.com/percona/percona-toolkit/3.x/bin/pt-query-digest

下面先对 pt-query-digest 进行简单的介绍：

pt-query-digest 是用于分析 MySQL 慢查询的一个第三方工具，可以分析 binlog、General log 和 slowlog，也可以通过 showprocesslist 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析，可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。

有兴趣的可以先下载下来自己玩玩，将在后续的文章中对 pt-query-digest 工具进行详细介绍。

show processlist

还有种情况是慢查询还在执行中，慢查询日志里是找不到慢 SQL 呢，这个时候可以用 show processlist 命令来寻找慢查询，该命令可以显示正在运行的线程，执行结果如下图所示，可以根据 Time 的大小来判断是否为慢查询。

经验之谈

• 设计表的时候要考虑范式，避免不合理的表结构设计
• 创建表字段的时候一定要考虑好字段的大小，避免过大或过小
• 善用索引
• 学会慢查询的一些技巧
• 学会 MySQL dump 的技巧
  • MySQL mysqldump数据导出详解
  • mysqldump备份避免表锁
  • mysqldump备份时导致所有数据表锁定，无法提供服务

Reference

• MySQL技术内幕：InnoDB存储引擎
• 知乎：彻底搞清楚数据库 E-R 模型设计
• MySQL’s UTF-8: Is It Real?
• MySQL 有哪些锁？
• 事务隔离级别是怎么实现的？
• MYSQL 事务的底层原理