每天早上都给自己一个希望,

试着不为明天而烦恼,

不为昨天而叹息,

只为今天更美好。

希望大家能在2018年这最后的一个月里,

继续为自己的梦想而奋斗,

努力完成2018年的计划!

今日分享主题

《MySQL数据库优化》

MySQL是一个广泛应用的关系型数据库管理系统,有体积小、速度快、维护简单,源码开放等特点。 MySQL在数据库排行榜中一直稳居前三甲(在开源数据库领域排名第一),是国内外各大中互联网公司数据库首选,广泛应用于后端数据存储服务领域。

虽然MySQL性能优秀,但是也常有程序猿因不熟悉MySQL优化知识,导致开发的应用性能较差,不能满足用户需求,为此而饱受困扰。

今天咱们就来聊聊数据库性能优化的秘密。

应用访问数据库性能不佳有很多原因,可分为应用层面,系统层面,数据库层等多方面。具体有如系统架构,后端存储选型是否合理;操作系统或硬件设置是否没有优化(如raid选择,swap设置关闭);数据库表设计是否合理,SQL慢查询需要优化等等。

图1 数据库应用优化方向

虽然数据库层面的优化非常重要,但是应用及架构层面的优化也是不能忽视,例如应用访问数据时加一层数据缓存,可以极大的减轻数据库的访问压力。在数据库schama模式经常变化的场景,可能更适用于mongodb等NoSQL数据库,此时就不能强求MySQL有好的表现。

操作系统优化及数据库配置优化等通常由运维或DBA同学负责, 在本文中咱们主要面向开发者介绍数据库优化原则,主要有图2所示:

图2 数据库优化原则

数据表首先要设计合理,对核心SQL要重点关注进行优化。原则上核心SQL都必须要使用能到索引,以提升执行效率。不要在索引列上进行函数计算,以免索引失效。SQL力求简单高效,要避免复杂的join和子查询等。

数据库模式(schama)是指数据库的组织和结构。模式中包含了schema对象,可以是表(table),视图(view),存储过程(stored procedures),索引(key)等。

数据表设计是数据库模式(schama)设计的核心,设计出结构合理的表,是构建性能优秀数据库应用的基础。

数据表设计优化需要考虑如下因素:

图3 数据表设计优化

数据表命名:

数据表名建议只由小写字母,下划线,数字组成。库名/表名/列名有较明确的含义,简短明晰为宜。

列字段设计:

正确选择数据类型,如数字类型和字段类型不要混淆,否则会有索引失效或性能问题。 性能方面Char>Varchar>Text,故若明确知道字长的时候选择Char类型,通常字符列选择,大家可选择Varchar,少用Text。

字段大小以预期够用为原则。如标识字段is_del可由int改为tinyint类型。  字段避免使用null字段。

表索引设计:

数据表必须有主键。主键尽量小,不宜过大。主键建议int ,自增。

数据库范式:

数据库范式设计是一种平衡的艺术,并不能一定使用严格的三范式设计。

图4 SQL如找出付费用户最近发布的10条信息,若是只有一部分用户是付费用户,则两表join效率低下。而若是此查询非常频繁,则可设计反范式。把user信息和message信息合并到一个表中,减少join提升性能。

图4 数据表设计优化-平衡范式

数据表拆分:

若数据表记录特别大时(通常不建议表>3000W),可能要进行数据表拆分。拆分可分为水平 拆分和 垂直拆分。

若各子表结构相同,称为水平拆分,通常可依id或时间等维度进行水平拆分。当一个表太复杂,字段数太多,可依业务逻辑拆成多个表,这种称之为垂直拆分。

图5 数据表设计优化-数据表拆分

访问数据库慢通常和SQL没有优化有关,从而存在慢查询。 提升DB访问速度,需要定位出性能的瓶颈点,快速找到SQL主要的开销发生在哪里?

5.1 SQL开销

MySQL是IO密集型应用,开销通常主要是会在磁盘访问及网络传输,咱们要尽量编写出简洁高效的SQL才能提升数据库查询性能。

针对SQL开销优化可以采用如下措施:

减少数据访问(增加索引,减少磁盘访问)

返回更少数据(明确查询列,减少网络传输或磁盘访问)

减少交互次数(减少不必要的查询或合并,减少数据传输)

减少服务器CPU开销(合理,索引减少CPU开销)

利用更多资源(增加硬件资源)

图6 SQL开销及优化法则

5.2 慢SQL发现

把大象关进冰箱需要3步。慢SQL优化也需要三步:发现慢查询à确认à优化 。

发现慢查询可以有如下方法:

应用日志记录SQL执行时间

SQL命令show full processlist

数据库开启慢查询日志记录

使用Explain ,Profile分析工具

通过日志记录发现慢SQL熟为人知。我介绍下DBA常用慢查询分析工具Explain及Profile。

Explain是分析SQL执行计划(QEP)的重要工具,可以对SQL的性能优劣有一个快速的判断。使用见下图。

图7 Explain 分析命令

图8 Explain 分析命令

若Explain结果中出现图8中标红的情况,则需要注意,因为此情况表示该SQL存在性能问题,需要优化。

Profile命令可微秒级统计当前会话资源的消耗情况, 可精确记录并展示一条SQL语句的执行各步骤中所消耗时间等。

图9 Profile 分析命令

5.3 SQL索引优化

索引(index):

是一种帮助DB高效获取数据的数据结构,其包含着对数据表记录的引用指针。比如我们要在字典中找某一字,如何才能快速找到呢?那就是通过字典的目录。对数据库来说,索引的作用就是给‘数据’加目录。

通过给SQL增加合适的索引,可显著的提升SQL的执行效率,从而避免慢查询的发生。索引优化是数据库SQL优化的重要手段,重要的查询SQL必须要能够使用到合适的索引。

图10 索引介绍

索引优化示例

单列索引:单列索引最简单,当SQL只访问一个表并且where条件只有一个字段的情况下,只要对此条件字段加索引就可。

联合索引:当SQL查询中where条件包括多个条件列,则要使用联合索引。而不应该是增加两个单独的单列索引。 示例见图11

在这要指出的是:索引idx_a_b_c(a,b,c) 通常可以替换idx_a_b(a,b)及idx_a(a),为避免索引冗余,需要把idx_a_b,idx_a索引删除。

图11 索引优化示例

多表查询索引优化示例可见图12.

图12 索引优化示例-多表查询

图13 索引优化示例-多表查询

另外索引优化还有覆盖索引,前缀索引等知识点。

覆盖索引:

若索引包含所有查询需要的数据可称为覆盖索引(Covering Index),即⽆需回表即可取得结果。 覆盖索引因为不需要回表,所查询效率较高。

前缀索引:

有时索引字段是很长的字符列,这会让索引变得大且慢。通常可以只使用索引开始的部分字符,做前缀索引,从而提高索引效率。

限于篇幅就不详细举例讲解了。

索引失效

索引可以极大的提升SQL执行效率,但是要注意。索引在一些情况下是会失效的,若是不注意可能就会掉进索引失效的泥坑,而引起性能问题。

程序员较易出的错误有,在索引字段上做计算,索引和查询列类型不同等等。

图14 索引失效

图15 索引失效

本文主要介绍了数据库优化的一些知识,重点讲授了数据库优化的方向,数据表schema设计优化,SQL优化等。

SQL优化中索引优化非常重要,重要的查询SQL都必须要能使用到索引,并且要注意在一些情况下索引可能会失效。

Effective MySQL之SQL语句最优化

 面向程序员的数据库访问性能优化法则

往期内容回顾:

产品推荐:

1)标准直播:

为中小型商户提供的集合了推流、转码、分发、录制、播放、活动管理等功能的一站式直播解决方案。通过直播平台可以快速部署直播业务

2)标准点播:

为中小型商户提供上传、存储、转码、分发、播放及视频管理功能的一站式视频服务能力,解决视频双向加速问题,满足全终端实时观看,为不同网络条件下的用户提供流畅的观看体验

欢迎大家前来咨询,