图片来自 Pexels

大量信息的存储和查询都会用到 MySQL，因此它的优化就对系统性能提升就尤为重要了。

由于 MySQL 的优化范围较广，从软件到硬件，从配置到应用，无法一一道来。

今天就从开发者的角度介绍一下 MySQL 应用优化。包括数据类型，数据表查询/修改，索引和查询等几个方面。

字段是用来存放数据的单元，设计好字段是设计数据库的第一步，同样会影响到系统的性能。

设计字段有一个基本的原则，保小不保大，也就是能够用字节少的字段就不用字节数大的字段，目的是为了节省空间，提高查询效率。

更小的字段，占用更小的磁盘空间，内存空间，更小的 IO 消耗。下面针对使用场景，说一些字段类型选取的经验，供大家参考。

手机号：通常我们在存储手机号的时候，喜欢用 Varchar 类型。

如果是 11 位的手机号，假设我们用 utf8 的编码，每位字节就需要 3 个字节，那么就需要 11*33=33 个字节来存放；如果我们使用 bigint，只需要 8 个字节就可以存放。

IP 地址：同上，IP 地址也可以通过 int（4 字节）在存放，可以通过 INET_ATON() 函数把 IP 地址转成数字。这里需要注意溢出的问题，需要用无符号的 int。

年龄，枚举类型：可以用 tinyint 来存放，它只占用 1 个字节，无符号的 tinyint 可以表示 0-255 的范围，基本够用了。

Char 和 Varchar 是我们常用的字符类型。char(N) 用来记录固定长度的字符，如果长度不足 N 的，用空格补齐。

varchar(N) 用来保存可变长度的字符，它会额外增加 1-2 字节来保存字符串的长度。

Char 和 Varchar 占用的字节数，根据数据库的编码格式不同而不同。Latin1 占用 1 个字节，gbk 占用 2 个字节，utf8 占用 3 个字节。

用法方面，如果存储的内容是可变长度的，例如：家庭住址，用户描述就可以用 Varchar。

如果内容是固定长度的，例如：UUID（36 位），或者是 MD5 加密串（32 位），就可以使用 Char 存放。

Datetime 和 Timestamp 都是可以精确到秒的时间类型，但是 Datetime 占用 8 个字节，而 Timestamp 占用 4 个字节。

所以在日常建表的时候可以有限选择 Timestamp。不过他们有下面几个小区别，需要注意的。

区别一：存储数据方式不一样。

Timestamp 是转化成 utc 时间进行存储，查询时，转化为客户端时间返回的。

区别二：两者存储时间的范围不一样。

说了如何高效地选择存储数据的类型以后，再来看看如何高效地读取数据。MySQL 作为关系型数据库，在处理复杂业务的时候多会选择表与表之间的关联。

这会导致我们在查询数据的时候，会关联其他的表，特别是一些多维度数据查询的时候，这种关联就尤为突出。

此时，为了提高查询的效率，我们会对某些字段做冗余处理，让这些字段同时存在于多张表中。

但是，这又会带来其他的问题，例如：如果针对冗余字段进行修改的时候，就需要对多张表进行修改，并且需要让这个修改保持在一个事物中。

如果处理不当，会导致数据的不一致性。这里需要根据具体情况采取查询策略，例如：需要跨多张表查询公司销售额信息。

由于，销售信息需要连接多张表，并且对销售量和金额做求和操作，直接查询显然是不妥当的。

可以生成后台服务，定时从相关表中取出信息，计算出结果放入一张汇总表中。

将汇总表中需要查询的条件字段加上索引信息，提高查询的效率。这种做法，限于查询数据实时性不强的情况。

在高速迭代开发过程中，业务变化快，数据库会根据业务的变化进行迭代。所以，在开发新产品初期，表结构会面临频繁地修改。

MySQL 的 ALTERTABLE 操作性能对大表来说是个问题。MySQL 执行修改表结构操作的方法是，用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。

这一操作需要花费大量时间，如果内存不足而表数据很大，并且索引较多的情况，会造成长时间的锁表。

有极端的情况，有些 ALTERTABLE 操作需要花费数个小时甚至数天才能完成。

这里推荐两种小技巧：

• 先把数据库拷贝到一台非生产服务器上，在上面做修改表操作，此时的修改不会影响生产库。

  修改完毕以后在做数据库的切换，把非生产数据库切换成生产库。不过需要注意的时候，在做表结构修改的时候，生产库会生成一些数据。这里需要通过脚本根据时间区间导入这部分数据。

• “影子拷贝”，即生成一张表结构相同的不同名新数据表（更改数据结构以后的表）。

  然后导入原表的数据到新表，导入成功以后停止数据库，修改原表和新表的名字，最终将数据访问指向新表。

  在运行正常以后，将原表删除。这里有现成的工具可以协助完成上述操作，“online schema change”,”openark toolkit”

“索引独立”是指索引列不能是表达式的一部分，也不能是函数的参数。例如：假设 User 表中分别把 create_date 和 userId 设置为索引。

例如：有一张 user 表，其中有一个字段是 FirstName，如何计算这个字段的选择性，如下：

分别查看其选择性，目的是看当选择多少字符的时候，选择性最接近 0.75。

于是可以用下面语句修改索引信息：

可能出现以下语句：

如果分别在 LastName 和 FirstName 上面建立索引：

语句如下：

通过按照选择性规则，写如下 SQL 语句：

FirstName 的选择性要高于 LastName 的选择性。因此调整多列索引的顺序如下：

例如：User 表中将 LastName 作为索引。如果写以下查询语句：

而且覆盖索引会以 Usingindex 作为标示，可以通过 Explain 语句查看。

还有可以进行列查询的回表优化，如下：

这里需要通过两次搜索完成。简单点说就是使用了覆盖索引以后，一次就可以查到想要的记录，不用在查第二次了。

• 响应时间，由两部分组成，他们分别是，服务时间和排队时间。服务时间是指数据库处理查询花费的时间。

  排队时间是指服务器因为等待某些资源花费的时间。例如：I/O 操作，等待其他事务释放锁的时间。

• 扫描记录行数，在查询过程中数据库锁扫描的行记录。理想情况下扫描的行数和返回的行数是相同的。不过通常来说，扫描的行数都会大于返回记录的行数。

• 返回记录行数，返回实际要查询的结果。

• 客户端发送一条查询给服务器。

• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。

• 解析器对 SQL 进行解析，它通过关键字将 SQL 语句进行解析，并生成一棵对应的“解析树”。MySQL 解析器将使用 MySQL 语法规则验证和解析查询。

• 预处理器则根据一些 MySQL 规则进一步检查解析树是否合法，并且验证权限。例如，检查数据表和数据列是否存在，解析名字和别名看是否有歧义。

• MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。

• 将结果返回给客户端。

④查看多少 SQL 语句是超过查询阀值的（3 秒）

将 Explain 关键字放在要执行的 SQL 语句前面，可以模拟优化器执行 SQL 语句，从而知道 MySQL 是如何处理你的 SQL 语句的。

①Count 优化

从 user 表中搜索 id 大于 7 的所有用户。如果是 InnoDB 存储引擎会进行逐行扫描，如果表中记录比较多，性能就是问题了。

如果先将所有的行数 Count 出来，再减去 id<=7 的记录，这样速度就会快一些。

如果有一个货物表 items，其中有一个 color 字段来表示货物的颜色，如果需要知道颜色是蓝色或者红色的货物的数量，可以这么写：

不过颜色本身是除斥的字段，所以可以优化成下面的 SQL。

②GROUPBY 优化

例如：

例如 actor 表和 film 表通过 actorId 做关联，查询如下：

就可以修改为：

③Limit

按照上例 SQL 代码如下：

这里通过 id 索引到第 1001 条记录，然后取 20 条记录。这里利用 id 的索引的优势直接跳过了前面 1000 条记录。

往期热门，好玩有趣:

出绝招，用Python帮你秒杀双11的商品，女朋友都夸我能干！

手把手教你，菜鸟也能用Python写一个2048游戏

用Python做个海量小姐姐素描图

Python里三个最高逼格的调试神器

本篇文章来源于: 菜鸟学Python