查看原文
其他

十六年老司机笔记:MySQL性能优化之必备技能

崔皓 高可用架构 2019-11-28
导读:MySQL 是目前广泛使用的数据库,但很多项目对 MySQL 的使用仍然存在欠优化的地方,本文根据作者长年的经验提出了 MySQL 数据库优化方法,这些方法是否适合你的项目?还有哪些优化方法值得分享?欢迎留言建议。

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


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


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


数据类型优化


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


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


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


数值类型


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


如果是 11 位的手机号,假设我们用 utf8 的编码,每位字节就需要 3 个字节,那么就需要 11*3=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()
select userId from user where userId+1=5


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


前缀索引


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

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


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

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

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


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

select 1.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 table user 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的选择性
select count(disctinc LastName)/count(*) from user
结果为0.02


再计算FirstName的选择性
select count(disctinc FirstName)/count(*) from user
结果0.05


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

alter table user add key(FirstName ,LastName)

覆盖索引


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

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

select LastName from user


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

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


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

Explain 查看覆盖索引标示

覆盖索引主要应用在 Count 等一些聚合操作上,提升查询的效率。例如上面提到的 Select count(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。


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


SHOW VARIABLES LIKE '%slow_query_log%';


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

SET GLOBAL slow_query_log=1;


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


SET GLOBAL 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 <=7from 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 nullas blue, count(color=‘red’ or nullas red from items


②GROUPBY 优化


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


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


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

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

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


例如:

select SQL_BUFFER_RESULT field1, count(*) from table1 group by 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 limit 1000,20


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

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


总结


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

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

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

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


作者:崔皓

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


参考阅读:


本文作者崔皓,授权转载自51CTO技术栈。技术原创及架构实践文章,欢迎通过公众号菜单「联系我们」进行投稿。


高可用架构

改变互联网的构建方式


长按二维码 关注「高可用架构」公众号

    您可能也对以下帖子感兴趣

    文章有问题?点此查看未经处理的缓存