MySQL性能优化之骨灰级高阶神技

【51CTO.com原创稿件】在程序，语言，架构更新换代频繁的今天，MySQL 恐怕是大家使用最多的存储数据库了。

 

 

 

图片来自 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 时间进行存储，查询时，转化为客户端时间返回的。

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

Timestamp 为'1970-01-01 00:00:01.000000' 到'2038-01-19 03:14:07.999999'。

Datetime为'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'。

数据表查询/修改优化

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

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

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

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

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

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

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

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

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

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

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

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

这里推荐两种小技巧：

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

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

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

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

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

如果只是删除或者更改某一列的默认值，那么直接可以使用 Alert table modify column 和 Alert table alert column 来实现。

索引优化

说了字段和表再来聊聊索引。对于索引的优化网上有很多的说法，都是在实际工作中总结出来的，这里没有一定的标准。

针对我们使用比较多的 InnoDB 的存储引擎(使用的 B-Tree 索引)，推荐几个方法给大家。

索引独立

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

select *from user where date(create_date)=curdate() 
selectuserId from user where userId+1=5 

类似上面的语句就是将索引作为了函数中的参数和表达式的一部分，是不推荐这样使用的。

前缀索引

有时候索引字段长度较大，例如：VarChar，Blob，Text。当搜索的时候，这会让索引变得大且慢。

通常的做法是，可以索引开始的部分字符，这样可以节约索引空间，提高索引效率。

既然索引全部字符行不通，那么索引多少字符就是我们要讨论的问题了。

这里需要引入一个概念，索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。

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

Select1.0*count(distinct FirstName)/count(*) from user 

假设这个结果是 0.75 再用 left 函数对该字段取部分字符，例如取从左开始的 3，4，5 个字段。

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

从左取3个字段的时候， 
Select 1.0*count(distinct left(FirstName,3))/count(*) from user 
结果为0.58 
 
从左取4个字段的时候， 
Select 1.0*count(distinct left(FirstName,4))/count(*) from user 
结果为0.67 
 
从左取5个字段的时候， 
Select 1.0*count(distinct left(FirstName,5))/count(*) from user 
结果为0.74 

从上面尝试发现，字段 FirstName 取左边字符，从 3-5 的获取可以看出，当从左边取第 5 个字符的时候，选择性 0.74 最接近 0.75。

因此，可以将 FirstName 的前面 5 个字符作为前缀索引，这样建立索引的效果基本和 FirstName 全部字符建立索引的效果一致。而又不用将 FirstName 整个字段都当成索引。

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

Alter tableuser add key(FirstName(5)) 

多列索引及其顺序

多列索引，顾名思义就是将多列字段作为索引。假设在 user 表中通过搜索 LastName 和 FirstName 条件来查找数据。

可能出现以下语句：

Select *from user where LastName = ‘Green’ 
Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’ 
Select *from user where LastName = ‘Green’ and (FirstName = ‘Jack’ or FirstName =‘Michael’ 
Select *from user where LastName = ‘Green’ and FirstName >=‘M’ and FirstName<‘N’ 

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

Select *from user where LastName = ‘Green’ and FirstName = ‘Jack’ 

当运行上面这段代码的时候，系统会让选择性高的 SQL 的索引生效，另外一个索引是用不上的。因此我们就需要建立多列索引(合并索引)。

语句如下：

Alter table user add key(LastName, FirstName) 

既然定义了多列索引，那么其中的索引顺序是否也需要考虑呢?在一个多列 B-Tree 索引中，索引列的顺序意味着，索引首先按照最左列进行排序，其次是第二列。

索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的 ORDERBY、GROUPBY 和 DISTINCT 等子句的查询需求。

所以，多列索引的顺序是需要考虑的。这里给出的建议是，将选择性最高的索引列放在前面。

接上面的例子，还是 LastName 和 FirstName 作为多列索引。看谁应该放前面。

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

先计算LastName的选择性 
Selectcount(disctinc LastName)/count(*) from user 
结果为0.02 
再计算FirstName的选择性 
Selectcount(disctinc FirstName)/count(*) from user 
结果0.05 

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

Alter tableuser add key(FirstName ，LastName) 

覆盖索引

当使用 Select 的数据列只用从索引中取得，而不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。

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

Select LastName from user 

LastName 及作为索引，又在查询内容中显示出来，那么 LastName 就是覆盖索引。

覆盖索引是高效查找行方法，通过索引就可以读取数据，就不需要再到数据表中读取数据了。

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

 

 

 

Explain 查看覆盖索引标示

覆盖索引主要应用在 Count 等一些聚合操作上，提升查询的效率。例如上面提到的 Selectcount(LastName) from user 就可以把 LastName 设置为索引。

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

Select LastName, FirstName from user where LastName=‘Jack’ 

如果此时 LastName 设置为索引，可以将 LastName 和 FirstName 设置为多列索引(联合索引)。

避免回表行为的发生。这里的回表是指二级索引搜索到以后，再找到聚合索引，然后在查找 PK 的过程。

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

 

 

 

回表示意图

查询优化

作为程序开发人员来说，使用得最多的就是 SQL 语句了，最多的操作就是查询了。

我们一起来看看，哪些因素会影响查询记录，查询基本原理是什么，以及如何发现和优化 SQL 语句。

影响查询效率的因素

一般来说，影响查询的因素有三部分组成，如下：

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

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

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

查询基础

 

 

 

查询流程图

说了影响查询效率的因素以后，来看看查询这件事情在 MySQL 中是如何运作的，可以帮助我理解，查询优化工作是在哪里进行的：

• 客户端发送一条查询给服务器。
• 服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。
• 解析器对 SQL 进行解析，它通过关键字将 SQL 语句进行解析，并生成一棵对应的“解析树”。MySQL 解析器将使用 MySQL 语法规则验证和解析查询。
• 预处理器则根据一些 MySQL 规则进一步检查解析树是否合法，并且验证权限。例如，检查数据表和数据列是否存在，解析名字和别名看是否有歧义。
• MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
• 将结果返回给客户端。

如何发现查询慢的 SQL

说了影响查询缓慢的因素以及查询的基本流程以后，再来看看如何发现查询慢的 SQL。这里 MySQL 提供了日志，其中可以查询执行比较慢的 SQL。

①查看慢查询日志是否开启

SHOWVARIABLESLIKE'%slow_query_log%';

 

 

 

②如果没有开启，通过命令开启慢查询日志

SETGLOBAL slow_query_log=1; 

 

 

 

③设置慢查询日志的时间，这里的单位是秒，意思是只要是执行时间超过 X 秒的查询语句被记录到这个日志中。这里的 X 就是你要设置的。(下面的例子设置的是 3 秒)

SETGLOBAL long_query_time=3; 

 

 

 

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

 

 

 

Explain 分析 SQL 查询

通过上面的方法可以知道哪些 SQL 花费了较多的时间，那么如何对这些 SQL 语句进行分析呢。毕竟，我们的目的是通过分析以后，优化 SQL 从而提高其性能。

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

 

 

 

Explain 执行 SQL 示意图

上面每个字段的含义，在这里不展开描述。

SQL 优化建议

如果发现慢查询的 SQL，我们就需要针对其问题进行优化。这里针对几个常见的 SQL 给出一些优化建议。

类似 SQL 优化的文章和例子在网上种类繁多，千奇百怪。建议在优化之前，先查看慢查询日志和 Explain 的语句，再进行优化，做到有的放矢。

①Count 优化

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

Select count(*) from user where id>7 

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

Select (select count(*) - (select count(*) from user where id <=7) from user) 

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

Select count(color=‘blue’ or color=‘red’) from items 
Select count(*) from items where color=‘blue’ and color=‘red’ 

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

Select count(color=‘blue’ or null) as blue, count(color=‘red’ or null) as red from items 

②GROUPBY 优化

MySQL 通过索引来优化 GROUPBY 查询。在无法使用索引的时候，会使用两种策略优化：临时表和文件排序分组。

可以通过两个参数 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 提升其性能。

这两个参数只对 Select 语句有效。它们告诉优化器对 GROUPBY 查询使用临时表及排序。

SQL_SMALL_RESULT 告诉优化器结果集会很小，可以将结果集放在内存中的索引临时表，以避免排序操作。

如果是 SQL_BIG_RESULT，则告诉优化器结果集可能会非常大，建议使用磁盘临时表做排序操作。

例如：

SelectSQL_BUFFER_RESULTfield1, count(*) from table1 groupby field1 

假设两个表做关联查询，选择查询表中的标识列(主键)分组效率会高。

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

Select actor.FirstName, actor.LastName,count(*) from film inner join actor using(actorId) 
Group by actor.FirstName,actor.LastName 

就可以修改为：

Select actor.FirstName, actor.LastName, count(*) from film inner join actor using(actorId) 
Group by film.actorId 

③Limit

Limit 对我们再熟悉也不过了，特别是在做分页操作的时候，经常会用到它。但在偏移量非常的时候问题就来了。

例如，Limit 1000，20 就需要偏移 1000 条数据以后，再返回后面的 20 条记录，前面的 1000 条数据是被抛弃掉的。

按照上例 SQL 代码如下：

Select name from user order by id limit1000,20 

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

Select name from user where id>=1001order by id limit 20 

总结

从开发者的角度了解 MySQL 的应用优化。从数据类型的选择开始，针对数值类型，字符类型，时间类型进行了举例说明。

接下来谈到，作为数据表的查询，修改的优化，我们应该注意哪些细节。然后，聊了索引独立，前缀索引，多列索引，覆盖索引的优化方法。

最后，针对使用最多的查询优化进行了探讨。从影响查询的因素到查询基础，再到如何发现慢查询，用几个 SQL 优化的建议结束了我们的 MySQL 应用优化之旅。

写完全文感觉 MySQL 博大精深，需要学习的东西很多，一文不能面面俱到，还需不断学习。

作者：崔皓

简介：十六年开发和架构经验，曾担任过惠普武汉交付中心技术专家，需求分析师，项目经理，后在创业公司担任技术/产品经理。善于学习，乐于分享。目前专注于技术架构与研发管理。