好多好多无聊的 Mysql 知识又增加了【1】

前言

没前言的前言，主要是想写点关于 Mysql 整体优化的文章，想不到一写就写多了，会分几篇写，你学废了吗

先讲下要做优化的层次和思路 👇

优化的层次和思路

从整个系统和架构去看到优化的层次，优化的层次是向上递减，从最底层需求层出发的一点小调整可能到最高层硬件层呈现一个几何级的放大效果

产品需求

所有的技术实现的源头都是产品需求，产品的一个需求修改可以会放大金字塔顶端的大规模改动。

例如，产品增加每一个用户对一个商品进行点赞的功能，底层的存储数据量可能是 '用户 x 商品'，那很快就就会导致存储硬件设备的聚积增加。

例如，产品因为不懂技术，经常会提出一些脑瓜疼的需求，例如要求所有的数据都要实时更新，殊不知这个讲给技术增加多少成本和难度。这个时候技术需要提出一些相对产品调优的技术方案，例如数据在特定的业务场景中做到准实时或感知实时就可以了，既能满足到需求又能解决或降低复杂度。

一个简单的产品需求做出一点业务可接受范围的改动胜过于后在后面各种 SQL查询语句，加硬件等优化。

所以，我们有一句话就是技术一定要懂业务，这个“懂业务”并不是要求技术人员能够驱动业务或产品的模型，而是在实现技术的同时也能理解产品业务后背的需求，对产品进行优化和调整。

系统架构设计

数据库只是整个系统中重要的一环但不是全部，我们在考虑数据库设计先要知道数据库在整个系统中的定位

什么数据应该存放在数据库

首先非数据类例如二进制文件图片文本就不应该存放在数据库中，存放在数据库只能是狭义方面的数据。

在狭义方面的数据也分别两大类，分别是联机事务处理（OLTP）和联机分析处理（OLAP），OLTP 是传统的关系型数据库的应用，主要处理基本的、日常的事务处理，例如我们商城的商品上架，订单成交。OLAP 是数据仓库系统的主要应用，支持复杂的分析操作，主要用于支持企业决策管理分析。

Mysql 术语关系型数据库，天生就是为 OLTP 而生，而 OLAP 数据量不大的情况下 Mysql 也是可以支撑，这些数据可以简单版使用 Elasticsearch、Druid 做异构存放，升级版使用 HBase 大数据为支撑。

什么数据可以作为Buffer，降低数据库的压力

如何降低数据库的压力，最简单的优化就是减少数据库被使用的频率，哪怕数据一定要存放在 Mysql，是不是要实时存放，是不是可以接受一定时间端的数据不一致性。

例如，上面的例子：用户点赞，用户每一次点赞不一定要实时 Update 到 Mysql，可以先更新到内存作为 Buffer ，等到一定的时间或数据量再同步更新回 Mysql。

什么数据可以作为Cache，也是降低数据库的压力

跟上面的数据缓冲一样的道理，我们将更新不平常的数据也可以作为缓存，存放在内存中，让用户进行访问内存的数据，减少对数据库直接进行访问的次数。

挑选什么数据，怎么进行数据库数据和缓存数据的同步更多是一个策略问题。

库表设计

在开发设计的阶段有一个阶段是对库表进行设计，在开发人员看来这是一个极其重要的阶段，因为在设计完成后的大把时间就是针对这些库表进行 CURD 的代码开发。

库表设计可以分为 2 个层面的设计。

一个是逻辑层面，根据业务的需求对表格进行设计拆分，这个时候考虑的是满足范式又得打破范式，因为即得消除传递的依赖又得考虑业务查询不能有过多的表关联保持一定的冗余。

一个是物理层面，在开发的时候，应该选择什么存储引擎，对表名和字段名命名的约定，对表字段的存储应该使用何种类别

SQL索引

进行 SQL索引 优化的阶段是开发人员最为熟悉的阶段， 也就是进入 CRUD 的阶段，其实 CURD 一点都不简单，运维 DBA 每天都会丢过来大量的慢查询日志甚至还有死锁的日志，怎么进行优化，需要我们对 SQL 查询，对索引，对锁都非常的熟悉。

系统配置/硬件

在 Mysql 中存在大量的系统参数配置，其中绝大多数采用默认的就可以，但大概有一小批参数是需要我们根据实际情况进行调优处理。

通过根据服务器目前的状况，修改 Mysql 的系统参数，达到合理利用服务现有资源，最大合理提高 Mysql 性能。

数据的准备

sakila

做测试总需要有一批样板的数据，Mysql 官方为我们提供了 Sakila 模拟DVD租赁的样本数据库，这个对我们下面会进行的一些实验是一个不错的选择。

登陆 dev.mysql.com/doc/sakila/… 进行下载

解压后导入我们的 Mysql 即可，用 WorkBench 查看数据图库模型关系

存储引擎的选择

最常见也是最常使用的是 MyISAM 和 InnoDB，当然还有其他一些，例如 Memory，Merge等，但因为确实太少使用了，基本不在我们的讨论范围之内。

MyISAM

MyISAM 是 Mysql 5.5版本之前默认的存储引擎，它的优势在于读的速度快，但是它不具备事务、外键。

存储结构

每一个 MyISAM 表在磁盘上都有三个文件，文件名和表名一致，区别在于扩张名，分别为：

• .frm : 存储定义
• .myd : 存储数据
• .myi : 存储索引

frm 是定义表的结构，其实不算存储引擎的部分。myd 文件是存储索引，但只是保存所在页的指针，和 myi 可以分开在不同的目录，平均分布 IO，加快访问的速度。

锁与并发

MyISAM 并不存在脏读或者幻读的现象，以为 MyISAM 进行加锁是对整一张表进行上锁，就是所谓的表级锁。

在并发读操作的时候，所有的表都可以获得共享锁（读锁），每个连接都是相互不干扰。

在写数据的时候，会获得排它锁（写锁），会把整个表进行上锁，而其他的操作包括读和写操作都会处于等待中。

坏表和修复

在很多年前，笔者就时常碰到 MyISAM 坏表的情况，MyISAM 坏表的原因还是表多的，例如：服务器死机，磁盘故障，mysqld 在写进程时被 kill 掉...

一般常用的几个命令去解决

check table `actor`         //检查表
repair table `actor`        //修复表
optimize table `actor`      //优化表

InnoDB

InnoDB 是 Mysql 5.5版本以后的默认存储引擎，具备事务，可以在高并发的条件下实现数据的一致性。

存储结构

InnoDB 存储表，会将表的定义和数据索引分开为 2 类文件

• .frm 存储定义
• .ibd 存储数据索引

frm 是定义存储，其实无论是什么存储引擎都会存在。

InnoDB 存储数据和索引有 共享表空间 和 独占表空间 两种存储方式，可以通过 innodb_file_per_table 进行控制

innodb_file_per_table 为 ON 表示独占空间，每张表都存在自己的ibd文件，这个文件保存这张表的数据和索引。这种配置比较的灵活，可以实现表在物理的灵活迁移，性能和效率也会相对好一些。

innodb_file_per_table 为 OFF 表示为共享空间，每一个数据库下所有的表数据和索引都会保存在一个文件中，名字为ibdata1。这种将所有数据和索引都存放在一个文件中，经过一段长时间的删除后，可能导致表空间产生大量的间隙。

加锁方法

意向锁 是InnoDB自动加的，不需要用户干预，对于 单条的 Update、Insert 、Delete语句，InnoDB 会自动给设计数据集加上排他锁（X）。autocommit 默认是打开的，每条SQL语句会默认被封装成一个事务。

对于普通的Select语句，InnoDB是不会加任何的锁。

共享锁/排他锁

1. 事务拿到某一行的共享 S 锁，才可以读取这一行
2. 事务拿到某一行的排他 X 锁，才可能修改或删除这一行

	S	X
S	兼容	互斥
X	互斥	互斥

即：

• 多个事务可以拿到一把S锁，读读可以并行
• 只有一个事务可以拿到X锁，写写/读写是互斥

锁的问题在于，读写必须是互斥的，那多个线程就无法进行充分并行，如果是这样InnoDB的效率就非常的底下。

而且在过往的经验告诉我们，有 2 个事务，第一个事务先对一行进行 Update 进行写操作，另一个事务对这一行进行读取 ，我们发现其实是可以的，并没有阻塞。这其实是 MVCC （数据多版本）进行解决。

行级锁

InnoDB 是支持到行级锁，但实际上也并不是每次的查询都能落到行级锁，查询必须是命中索引才能使用到行级锁，如果没有会自动退化到表级锁。

需要特别注意的是，InnoDB 行锁是通过给索引加锁来实现的，无论是索引、唯一索引或者普通索引，InnoDB 都会使用行锁来对数据，别忘了检查执行 explain 计划，在某些情况下在where条件下明明带入了索引进行检索，但有可能 Mysql 认为全表扫描效率更高，所以就不使用索引，这个时候 InnoDb 使用的就是表级锁。

因为 InnoDB 是针对索引进行加锁，很多同学以为行级锁就是对行的记录进行上锁，这个是不对的。存在这样的可能，多个session虽然访问的是不同行的记录，但是如果是使用相同的索引键，还是会出现锁冲突的。

用 sakila 样本库来做个测试：

part1.

part2.

part3.

间隙锁

通过上面的例子，我们知道所谓的行级锁并不是真的锁住某一行的记录，而是锁定一个范围，依据条件锁定部分范围，而不是映射在某一个行上，因此有一个学名：间隙锁。

比如：

select * from customer where store_id >= 2 and store_id<=10 LOCK IN SHARE MODE

就会锁住 store_id 在 2～10的范围，就无法插入 2-10 这个区间内任何一条记录。

对比与选择

对比

	MyISAM	InnoDB
存储结构	每个表拆文件进行存储	数据和索引共同存储，分为共享表和独占表空间存储
事务支持	不支持事务	支持事务
锁差异	表级锁	提供行级锁，但也不是绝对，如果Sql不能确定扫描范围也可能升级为表锁
表主键	允许没有主键存在，索引保存行地址	如果没有主键或唯一索引，会自动生成用户不可见的6字节主键
表的具体行数	有保存表总行数，直接获取	需要遍历整个表获取
CRUD操作	如果执行大量Select，MyISAM会是更好选择	如果存在大量的 Update/Insert ，InnoDB会是更好的选择
外键	不支持	支持
索引	非聚簇索引，索引只保存地址	使用聚簇索引、索引就是数据，顺序存储，因此能缓存索引，缓存数据
清空表	MyISAM直接重建表	一行一行删除，效率慢

选择

在Mysql 5.5.x 开始，InnoDB 就被 Mysql 作为默认的存储引擎，如果在没有特别的需求，我们直接选择 InnoDB 即可。

当然在一些特殊需求上 MyISAM 还有用武之地，例如在存在大量读而很少些的业务上，例如新闻博客之类，还可以在主从读写分离上，从库采用 MyISAM ，而主库使用 InnoDB。

库表设计

一般我们的数据库表设计需要经历的阶段是

1. 需求分析：全面了解产品设计的存储需求，需要存储什么数据，这些数据有什么特点，数据处理指的是如何对数据库进行读取和写入以及对数据的响应时间有什么用的要求。
2. 进行逻辑设计和物理设计

逻辑设计

在理解完需求后，进行逻辑的设计主要的点是在于怎么进行范式设计和反范式设计，如果逻辑设计不好的话，会在后面大大影响开发人员的编码效率和降低系统执行的效率。

第一范式

所谓的第一范式（1NF），就是表的列具有原子性不能再被分解，只要能放进去Mysql，基本都是能服务第一范式。 当然也有例外，例如：

这种就不是属于1NF，数据库的每一列都必须是不可分割，不能是集合数组。需要将其拆分为name，age，score三个字段。但其实这种集合放在一个字段里面在我们现在的开发也是经常发生，因为在数据库中只需要把这个集合当作一个字段，这个集合其实是在前端或业务层进行拆分，所以在现代的开发并不需要拘泥一定是属于范式。

第二范式

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，满足了第二范式就必须先满足第一范式。第二范式要求数据库表中每行都必须被唯一区分。

即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。

👆 看不懂？没关系，我也看不懂，通俗的说就是保证每张表只描述一件事情。

下面举个通俗的例子：

上面这个表满足第一范式，但是这个表设计的并不好，这张表描述了2个事情，学生的信息和课程的信息，“学分”依赖于“课程”，“姓名”和“年龄”依赖于“学号”。 这样带来的后果是：

1. 数据冗余：同一门课有N个学生选修，学生的信息就重复了N次
2. 更新异常：若调整了一个课程的学分，数据库表中所有跟这个课程相关的行都必须更新，否则会出现数据不一致的问题。
3. 插入异常：如果开设一门新的课程，但是暂时无人选修，由于没有关键字段学号，这门课程将无法插入数据库。
4. 删除异常：如果删除一门课程，则需要对所有的相关行进行删除，但是，与此同时，相关学生已经选修的记录也会被删除。

进行调整：

在学生和课程之间增加了一张中间表，很好的解决上面的问题，就是说 保证每张表只描述一件事情。

第三范式

满足第三范式必须先满足第二范式，第三范式的属性不依赖其他非主属性，第三范式要求一个数据库表中不包含其他表中已经包含非主键关键信息，非PK字段不能有从属关系。

不良的例子：地址依赖院系

改良：

反范式设计

范式可以避免数据冗余，减少数据库表的空间，减少维护数据完整性带来的麻烦，但是等级越高的范式设计出来的表就越多，可能会导致我们的业务查询设计到更多的表，需要做多表关联。

所以，得站在业务的角度对范式进行反范式的处理，反范式的设计就是为了性能和效率考虑的对数据库3范式进行违反，允许少量的数据冗余，换句话说就是用空间换取一定的时间。

对比一下范式和反范式分别的优缺点

• 范式的优点：
  1. 减少数据冗余
  2. 范式化的表逼反范式化的表要小
  3. 范式化的更新操作逼反范式要快
• 范式化的缺点：
  1. 增加更多的关联查询
  2. 索引难以优化
• 反范式的优点：
  1. 减少表的关联
  2. 能更好的优化索引
• 反范式的缺点：
  1. 存在更多的数据冗余
  2. 修改数据需要修改更多的表

理解范式，但别拘泥于范式，明白范式和反范式的优缺点将更灵活的应用的实际的业务去修中，才是更重要。

物理设计

当完成了Mysql的逻辑设计后，就开始进入我们创建数据库和表的实际开发阶段。为表选择一个合适的存储引擎，合适的字符集，规范好表和字段的命名规则，选择好字段类型，创建好相关的外键约束...

选择合适的存储引擎

在上面的章节我们已经对比了InnoDB和MyISAM的特性，选择什么引擎得跟进业务的特性，如果没有确定的需求，请选择InnoDB，如果是非常明显的读多写少的表，可以考虑采用 MyISAM ，这个例如是新闻门户，博客文章的网站，还有就是从库也可以适当的考虑使用 MyISAM。

说下笔者的真实体会，笔者以前在三大门户之一，用的是 MySIAM ，版本是5.5X, 因为是门户网站读比写高一个数量级，在 5.5 的版本下，做过测试，MyISAM 的读效率确实比 InnoDB 要高不少，但是中间也出现一些坏表的情况，经常是需要人为手动的修复。在这个后面很少使用 MyISAM。

合适字符集的选择

字符集是一套符合和编码，校验规则（collation）是在字符集内用于比较字符的一套规则，即字符集的排序规则。MySQL可以使用对种字符集和检验规则来组织字符。

一般在新建数据库或新建表，都需要指定数据库的字符集，一般都是选择utf8，但后面也出现了一个utf8mb4这个字符集。

utf8mb4

Mysql 的utf8最多只支持三个字节的UTF-8字符，也就是只支持到了 Unicode 中的“基本多文本平面（U 0000至U FFFF）”，包含了控制符、拉丁文，中、日、韩等绝大多数国际字符，但并不是所有。随着手机端的emoji表情的兴趣，只有三个字节的utf8已经存放不下，所以就诞生了 utf8mb4 ，mb4 就是 most byts 4，简单的说就是 utf8mb4 是 utf8 的超集并且完全兼容utf8，能够用4个字节存储更多字符。

utf8mb4unicodeci 与 utf8mb4generalci 如何选择

字符除了需要存储，还需要排序或比较大小，涉及到与编码字符集对应的 排序字符集（collation）。ut8mb4对应的排序字符集常用的有 utf8mb4_unicode_ci、utf8mb4_general_ci

主要从排序准确性和性能两方面看：

• 准确性 utf8mb4_unicode_ci 是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序 utf8mb4_general_ci 没有实现Unicode排序规则，在遇到某些特殊语言或字符是，排序结果可能不是所期望的。

• 性能 utf8mb4_general_ci 在比较和排序的时候更快 utf8mb4_unicode_ci 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

如何选择 理论上 utf8mb4_general_ci 性能会更好，但实际上，现在的计算机这点性能远远构不成查询效率的决定性因素，真正决定查询效率还是 SQL 语句和索引和优化。推荐还是使用 utf8mb4_unicode_ci，虽然说字符顺序排序是否那么重要，但也难保在某些特殊的查询出现错乱。

选择合适的字段数据类型

一个原则： 当一个列可以选择多种数据类型时，应该优先考虑整数数字类型，其次是实数数字类型，再其次是日期或者二进制类型，最后是字符类型。相对于相同级别的数据类型，应该优先考虑占用空间小的数据类型。

数字类型字段选择

整数类型

Mysql 有5种整数类型

以 tinyint 为例，占用 1 字节，也就是8位，可以存在 255，分别为 signed 和 unsigned，其中signed存储的是 （-128～127）具有负数的整数，而 unsigned 只能存储正整数 （0～255），如果存储不需要负数，使用unsigned，可以让数据增加一倍。

错误的 int(x)

不少同学想要2位整数的int，本着节省空间指定了int（2），其实这个是错的，int(M)在Integer数据类型中，M代表最大现实宽度，M的值跟int(M)所占多少存储空间是没有任何关系，int(2),int(3),int(4)在磁盘都是占 4bytes 的存储空间。

如果只是想要2位十进制，直接使用 tinyint 类型。

实数类型

FLOAT和DOUBLE类型使用标准的浮点运算进行近似计算，这里注意是近似计算，所以在一些计算结果中可能会因为精度损失而导致一些意想不到的结果。

MySQL DECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据。

定义数据类型为DECIMAL的列，请使用以下语法

column_name DECIMAL(P,D);

• P是表示有效数字数的精度。 P范围为1〜65。
• D是表示小数点后的位数。 D的范围是0~30。MySQL要求D小于或等于(<=)P。

DECIMAL 货币数据

经常使用DECIMAL数据类型的货币数据，如价格，工资，账户余额等。如果要设计一个处理货币数据的数据库，则可参考以下语法

amount DECIMAL(19,2);

如果遵守公认会计原则(GAAP)规则，则货币栏必须至少包含4位小数，以确保舍入值不超过$0.01。 在这种情况下，应该定义具有4位小数的列:

amount DECIMAL(19,4);

字符类型字段选择

VarChar 和 Char 在不同存储引擎中的表现是不同的，介于我们绝大部分是使用InnoDB，我们只讨论在 InnoDB 中的使用。

VARCHAR

varchar(M) 这个用于表明占用字符而不是字节，有不少同学会错意为这个是字节的占用。这里占用的字节数得先看我们选择了什么样的字符集，以utf8为例子，一个 utf8 字符会占用 3个字节，varchar(10)表明这个字段最多会占用30个字节。

• VARCHAR类型用于存储可变长字符串，是最常见的字符串数据类型。它比定长类型更节省空间，因为它仅使用必要的空间，虽然我们定义了 varchar(55) 但我们只使用了10个字符，最多也只占用了10个字符。
• 如果varchar的长度小于255，会额外使用1个字节来记录长度，如果长度大于255，会额外使用2个字节来记录长度。

那么是否只要控制在255以内，我们使用varchar(10)和varchar(200)是没有区别的，其实不然，varchar在一些场景，Mysql的内存临时表中为了优化性能也是一种定长方式呈现，所以我们尽量是贴近业务来进行长度的定义。

VarChar 使用的场景：

• 字符串列的最大长度比平均长度大很多
• 列的更新比较少，Mysql 的Update列是通过分裂页的方式来进行。
• 使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

CHAR

CHAR类型是定长的：MySQL总是根据定义的字符串长度分配足够的空间。

Char适合存储短并且所有的值都接近同一个长度。例如，Char非常适合存储MD5值或者是UUID或者是手机号码等固定位数的编码。如果对于插入数据不租规定长度，char会在后面补充填充。

为什么 Char 效率高但是可能会浪费更多空间

例如 Char(1)用来进行存储，如果是单字节的字符那只需要一个字节，但是 VarhChar(1) ，则需要2个字节，因为有一个字节用来记录长度。

我们可以看到varchar在每个数据的开头，都会分配出来1～2字节用来记录数据的长度，在数据段的结尾还有一个空间段（1字节）标记结尾记录字段的字节。Mysql在读取数据的时候，先要去读取开口获取数据段的长度，然后往下读取响应的字节数。所以在Mysql进行遍历的时候，磁针要比char类型的列多读更多的磁盘来获取数据。

char 类型在放数据的时候，中间是没有间隔的，因为char是定长，在创建表的时候已经确定告诉Mysql长度，mysql在查询数据的时候，只需要分段查询即可。

日期时间类型字段选择

• 时间精度 在5.6.5的版本之后，在默认的秒精确度上，可以带小数，最多带6位小数，即可以精确到 microseconds (6 digits) precision

• 时间范围 TIMESTAMP 的时间范围只能从 1970-01-01 到 2038-01-19，而DATATIME可以从 1000-01-01 到 9999-12-31

• 时区区别 TIMESTAMP，它把客户端插入的时间从当前时区转化为UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。而对于DATETIME，不做任何改变，基本上是原样输入和输出

• 占用字节 在网上查到的是 ： （TIMESTAMP 占用4个字节，DATETIME占用8个字节），但其实这个是5.6.4旧版本的信息 5.6.4版本开始DATETIME非小数时间部分仅占用5字节，如果有秒的小数部分会占用0-3个字节，v5.6.4版本开始TIMESTAMP非小数部分占用4个字节，小数部分占用0-3个字节

比较推荐还是使用用DATETIME进行存储，能支持更大的日期范围（2038应该很快就吵了），也不会因为时区作出转化，虽然比 TIMESTAMP 多处 1+ 字节。在现代的计算机并不会成为瓶颈。

字段几个原则

尽量用数字表示字符串

• 存储IP使用整形 Mysql 提供了两个方式处理IP地址

  inet_aton 把ip转为无符号整型(4-8位) inet_ntoa 把整型转为IP地址

• 金额存储 如果对精度没有要求，只要求存储2位小数，推荐使用用整形存储

  100.01元 -> 10001分

尽可能选择小的数据类型和指定段的长度

一般情况下，应该尽量选择使用可以正确存储数据的最小数据类型。更小的数据类型通常更快，因为它们站用更小的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少

尽可能使用 not null

通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。因为查询中包含可以NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、统计索引和值比较都更为复杂。同时，可为NULL的列被索引时，每个索引记录需要一个额外的字节。一般，把可为NULL的列改为NOT NULL带来的提升比较少

字段注释要完整

如果没有文档，没有数据字典，字段的注释就是最后的防线。