麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
863
2023-05-14
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 博大精深,需要学习的东西很多,一文不能面面俱到,还需不断学习。
作者:崔皓
简介:十六年开发和架构经验,曾担任过惠普武汉交付中心技术专家,需求分析师,项目经理,后在创业公司担任技术/产品经理。善于学习,乐于分享。目前专注于技术架构与研发管理。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。