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

作者:零壹技术栈  来源/微信公众号:zero-one-technology 发布日期:2019-10-28


在程序,语言,架构更新换代频繁的今天,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*fromuserwheredate(create_date)=curdate()selectuserIdfromuserwhereuserId+1=5
类似上面的语句就是将索引作为了函数中的参数和表达式的一部分,是不推荐这样使用的。
前缀索引
有时候索引字段长度较大,例如:VarChar,Blob,Text。当搜索的时候,这会让索引变得大且慢。通常的做法是,可以索引开始的部分字符,这样可以节约索引空间,提高索引效率。
既然索引全部字符行不通,那幺索引多少字符就是我们要讨论的问题了。这里需要引入一个概念,索引的选择性。索引的选择性是指,不重复的索引值和数据表的记录总数的比值。索引的选择性越高则查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
例如:有一张 user 表,其中有一个字段是 FirstName,如何计算这个字段的选择性,如下:
Select1.0*count(distinctFirstName)/count(*)fromuser
假设这个结果是 0.75 再用 left 函数对该字段取部分字符,例如取从左开始的 3,4,5 个字段。
分别查看其选择性,目的是看当选择多少字符的时候,选择性最接近 0.75。
从左取3个字段的时候,Select1.0*count(distinctleft(FirstName,3))/count(*)fromuser结果为0.58
从左取4个字段的时候,Select1.0*count(distinctleft(FirstName,4))/count(*)fromuser结果为0.67
从左取5个字段的时候,Select1.0*count(distinctleft(FirstName,5))/count(*)fromuser结果为0.74
从上面尝试发现,字段 FirstName 取左边字符,从 3-5 的获取可以看出,当从左边取第 5 个字符的时候,选择性 0.74 最接近 0.75。因此,可以将 FirstName 的前面 5 个字符作为前缀索引,这样建立索引的效果基本和 FirstName 全部字符建立索引的效果一致。而又不用将 FirstName 整个字段都当成索引。
于是可以用下面语句修改索引信息:
Altertableuseraddkey(FirstName(5))
多列索引及其顺序
多列索引,顾名思义就是将多列字段作为索引。假设在 user 表中通过搜索 LastName 和 FirstName 条件来查找数据。
可能出现以下语句:
Select*fromuserwhereLastName=‘Green’Select*fromuserwhereLastName=‘Green’andFirstName=‘Jack’Select*fromuserwhereLastName=‘Green’and(FirstName=‘Jack’orFirstName=‘Michael’Select*fromuserwhereLastName=‘Green’andFirstName>=‘M’andFirstName<‘N’
如果分别在 LastName 和 FirstName 上面建立索引:
Select*fromuserwhereLastName=‘Green’andFirstName=‘Jack’
当运行上面这段代码的时候,系统会让选择性高的 SQL 的索引生效,另外一个索引是用不上的。因此我们就需要建立多列索引(合并索引)。
语句如下:
Altertableuseraddkey(LastName,FirstName)
既然定义了多列索引,那幺其中的索引顺序是否也需要考虑呢?在一个多列 B-Tree 索引中,索引列的顺序意味着,索引首先按照最左列进行排序,其次是第二列。索引可以按照升序或者降序进行扫描,以满足精确符合列顺序的 ORDERBY、GROUPBY 和 DISTINCT 等子句的查询需求。所以,多列索引的顺序是需要考虑的。这里给出的建议是,将选择性最高的索引列放在前面。接上面的例子,还是 LastName 和 FirstName 作为多列索引。看谁应该放前面。
通过按照选择性规则,写如下 SQL 语句:
先计算LastName的选择性Selectcount(disctincLastName)/count(*)fromuser结果为0.02
再计算FirstName的选择性Selectcount(disctincFirstName)/count(*)fromuser结果0.05
FirstName 的选择性要高于 LastName 的选择性。因此调整多列索引的顺序如下:
Altertableuseraddkey(FirstName,LastName)
覆盖索引
当使用 Select 的数据列只用从索引中取得,而不必从数据表中读取,换句话说查询列要被所使用的索引覆盖。
例如:User 表中将 LastName 作为索引。如果写以下查询语句:
SelectLastNamefromuser
LastName 及作为索引,又在查询内容中显示出来,那幺 LastName 就是覆盖索引。覆盖索引是高效查找行方法,通过索引就可以读取数据,就不需要再到数据表中读取数据了。
而且覆盖索引会以 Usingindex 作为标示,可以通过 Explain 语句查看。Explain 查看覆盖索引标示覆盖索引主要应用在 Count 等一些聚合操作上,提升查询的效率。例如上面提到的Selectcount(LastName) from user 就可以把 LastName 设置为索引。
还有可以进行列查询的回表优化,如下:
SelectLastName,FirstNamefromuserwhereLastName=‘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%';②如果没有开启,通过命令开启慢查询日志
SETGLOBALslow_query_log=1;③设置慢查询日志的时间,这里的单位是秒,意思是只要是执行时间超过 X 秒的查询语句被记录到这个日志中。这里的 X 就是你要设置的。(下面的例子设置的是 3 秒)
SETGLOBALlong_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 存储引擎会进行逐行扫描,如果表中记录比较多,性能就是问题了。
Selectcount(*)fromuserwhereid>7
如果先将所有的行数 Count 出来,再减去 id<=7 的记录,这样速度就会快一些。
Select(selectcount(*)-(selectcount(*)fromuserwhereid<=7)fromuser)
如果有一个货物表 items,其中有一个 color 字段来表示货物的颜色,如果需要知道颜色是蓝色或者红色的货物的数量,可以这幺写:
Selectcount(color=‘blue’orcolor=‘red’)fromitemsSelectcount(*)fromitemswherecolor=‘blue’andcolor=‘red’
不过颜色本身是除斥的字段,所以可以优化成下面的 SQL。
Selectcount(color=‘blue’ornull)asblue,count(color=‘red’ornull)asredfromitems
②GROUPBY优化MySQL 通过索引来优化 GROUPBY 查询。在无法使用索引的时候,会使用两种策略优化:临时表和文件排序分组。
可以通过两个参数 SQL_BIG_RESULT 和 SQL_SMALL_RESULT 提升其性能。
这两个参数只对 Select 语句有效。它们告诉优化器对 GROUPBY 查询使用临时表及排序。SQL_SMALL_RESULT 告诉优化器结果集会很小,可以将结果集放在内存中的索引临时表,以避免排序操作。如果是 SQL_BIG_RESULT,则告诉优化器结果集可能会非常大,建议使用磁盘临时表做排序操作。
例如:
SelectSQL_BUFFER_RESULTfield1,count(*)fromtable1groupbyfield1
假设两个表做关联查询,选择查询表中的标识列(主键)分组效率会高。
例如 actor 表和 film 表通过 actorId 做关联,查询如下:
Selectactor.FirstName,actor.LastName,count(*)fromfilminnerjoinactorusing(actorId)Groupbyactor.FirstName,actor.LastName
就可以修改为:
Selectactor.FirstName,actor.LastName,count(*)fromfilminnerjoinactorusing(actorId)Groupbyfilm.actorId
③LimitLimit 对我们再熟悉也不过了,特别是在做分页操作的时候,经常会用到它。但在偏移量非常的时候问题就来了。例如,Limit 1000,20 就需要偏移 1000条数据以后,再返回后面的 20条记录,前面的 1000 条数据是被抛弃掉的。
按照上例 SQL 代码如下:
Selectnamefromuserorderbyidlimit1000,20
这里通过 id 索引到第 1001 条记录,然后取 20 条记录。这里利用 id 的索引的优势直接跳过了前面 1000 条记录。
Selectnamefromuserwhereid>=1001orderbyidlimit20
总结从开发者的角度了解 MySQL 的应用优化。从数据类型的选择开始,针对数值类型,字符类型,时间类型进行了举例说明。接下来谈到,作为数据表的查询,修改的优化,我们应该注意哪些细节。然后,聊了索引独立,前缀索引,多列索引,覆盖索引的优化方法。最后,针对使用最多的查询优化进行了探讨。从影响查询的因素到查询基础,再到如何发现慢查询,用几个 SQL 优化的建议结束了我们的 MySQL 应用优化之旅。写完全文感觉 MySQL 博大精深,需要学习的东西很多,一文不能面面俱到,还需不断学习。
精彩文章推荐:
一文深度揭秘Redis的磁盘持久化机制想实现高可用?先搞定负载均衡原理这一次,彻底弄懂“秒杀系统”大牛总结的MySQL锁优化,写得太好了!
10种常见的MySQL错误,你可中招?
图解“红黑树”原理,一看就明白!花了一个星期,我终于把RPC框架整明白了!关于GC原理和性能调优,看这一篇就够了!支撑百万并发的“零拷贝”技术,你了解吗?终于明白为什幺要“分库分表”了!这样讲API网关,你应该能明白了吧!这篇文章,专治MQ中间件各种疑难杂症没看这篇干货,别说你会使用“缓存”神一样的CAP理论被应用在何方?一文快速搞懂MySQL InnoDB事务ACID实现原理
超详细的Elasticsearch高性能优化实践
浅谈常见的NoSQL技术方案和选型
心里没点B树,怎能吃透数据库索引底层原理
Redis单线程却能支撑高并发
深入Redis哨兵模式与高可用集群
关注公众号:
本帐号持续分享后端技术干货,包括虚拟机基础,多线程编程,高性能框架,异步、缓存和消息中间件,分布式和微服务,架构学习和进阶等学习资料和文章。
微信扫一扫

关注零壹技术栈微信公众号,获取更多精彩内容