新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > MYSQL

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

时间:2019-12-02 16:06:16  来源:  作者:

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

 

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 语句查看。

 

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

 

 

Explain 查看覆盖索引标示

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

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

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

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

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

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

 

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

 

 

回表示意图

查询优化

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

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

影响查询效率的因素

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

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

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

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

查询基础

 

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

 

 

查询流程图

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

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

如何发现查询慢的 SQL

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

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

SHOWVARIABLESLIKE'%slow_query_log%';

 

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

 

 

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

SETGLOBAL slow_query_log=1; 

 

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

 

 

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

SETGLOBAL long_query_time=3; 

 

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

 

 

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

 

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

 

 

Explain 分析 SQL 查询

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

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

 

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

 

 

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 博大精深,需要学习的东西很多,一文不能面面俱到,还需不断学习。

作者:崔皓

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



Tags:   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
题:广州“出入大门”的白衣守护者  作者 蔡敏婕 陈淑华  在广州白云国际机场,有13个医疗急救点。...【详细内容】
2020-03-23     点击:(0)  评论:(0)  加入收藏
新冠肺炎疫情对全世界经济的冲击正逐步浮现,香港的“超级联系人”角色也因此受到挑战。笔者在大湾区多个城市游走多年,最终在深圳成功开创自己的事业,深深感觉到香港年轻一代仍有其不可替代的优势。...【详细内容】
2020-03-23     点击:(1)  评论:(0)  加入收藏
(杨佩佩)记者21日从山西联运集团股份有限公司获悉,自19日起,山西支援湖北医护人员及配偶、子女乘坐山西省太原市建南汽车站46条线路的客运班车终身免...【详细内容】
2020-03-23     点击:(2)  评论:(0)  加入收藏
3月17日自美国洛杉矶乘坐中国南方航空CZ610次航班(座位号R52H),于18日5时25分抵达沈阳桃仙机场,在机场边检站采样做新冠病毒核酸检测。...【详细内容】
2020-03-23     点击:(5)  评论:(0)  加入收藏
缅甸外交官、联合国驻缅甸机构人员在到达缅甸后需进行14天的居家隔离。...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
(记者 王国安)泰国卫生部21日公布,新增新冠肺炎确诊病例89例,再创单日确诊病例新高。...【详细内容】
2020-03-23     点击:(6)  评论:(0)  加入收藏
根据西班牙当地媒体当地时间20日下午的消息,目前效力于西甲西班牙人的中国球员武磊确诊感染新冠病毒。西媒确认武磊是4位感染病毒的球员之一。...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
安徽省建设合肥综合性国家科学中心人工智能研究院,开展基础研究、应用基础研究、技术创新和应用示范,为人工智能产业创新发展提供强大知识储备和技术...【详细内容】
2020-03-23     点击:(5)  评论:(0)  加入收藏
据湖北十堰市人民政府网站消息,为确保全市人民群众出行安全,十堰市新型肺炎疫情防控指挥部21日就规范人车安全有序流动有关事项发布通告。...【详细内容】
2020-03-23     点击:(3)  评论:(0)  加入收藏
据陕西省卫健委网站消息,2020年3月20日8时至21日8时,陕西新增1例境外(英国)输入新冠肺炎确诊病例;陕西本地无新增确诊病例,本地新增疑似病例0例,...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
根据大陆最新诊疗方案,病例确诊需结合核酸检测、血清特异性抗体检测、胸部影像学检查等多个标准。...【详细内容】
2020-03-23     点击:(6)  评论:(0)  加入收藏
我和表妹的对话虽是半开玩笑,但已然成为半个假洋鬼子的我,笃定“法国人要么美、要么死”的说法话糙理不糙,因为每天我都和一个非常有代表性的“法国人”样本一起工作、生活——我的爱人法兰克。...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
中国驻菲律宾大使黄溪连在机场会见洛钦,并向菲方移交了中国援助的抗疫物资。...【详细内容】
2020-03-23     点击:(5)  评论:(0)  加入收藏
当日21时10分,内蒙古森林消防总队参与跨区域协防的400余名指战员从河北石家庄临时驻地出发,前往五台山火场实施增援。...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
(袁超)贵州茅台酒股份有限公司2020年第一次临时股东大会20日选举高卫东为公司第二届董事会董事,在随后召开的董事会会议上,选举高卫东为公司第二届董...【详细内容】
2020-03-23     点击:(4)  评论:(0)  加入收藏
据路透社报道,21日,伊朗总统鲁哈尼表示,伊朗为抗击新冠肺炎疫情,采取了包括旅行限制等在内的社会疏离措施,这些措施将持续两到三周的时间,因为他...【详细内容】
2020-03-23     点击:(5)  评论:(0)  加入收藏
3月19日,武汉协和医院超声影像科门前,贴满了提示候诊患者保持距离的告示。中青报·中青网记者 李峥苨/摄  3月19日下午,武汉华中科技大学同济医学院附属同济医院门前,久违地出现了车辆川流不息的景象。...【详细内容】
2020-03-23     点击:(6)  评论:(0)  加入收藏
智利卫生部长马纳利奇20日说,从21日零时起,智利全国无限期关闭电影院、剧院、酒吧、舞厅等场所,餐厅将取消堂食,但保留外卖。...【详细内容】
2020-03-23     点击:(5)  评论:(0)  加入收藏
报到时要采取有效措施避免大量人员长时间聚集,家长一律不得进入校园,对学生携带的行李要做好入校前消毒工作。...【详细内容】
2020-03-23     点击:(6)  评论:(0)  加入收藏
(记者 陈冠言) 3月21日,广西对口支援湖北省十堰市抗疫医疗队乘坐北部湾航空包机返回南宁。...【详细内容】
2020-03-22     点击:(4)  评论:(0)  加入收藏
相关文章
    无相关信息
最新更新
栏目热门
栏目头条