数据库的优化对性能的提高是绝对的,几倍甚至十几倍,下面总结一些优化技巧,也许对你会有帮助,欢迎一起探讨。
一、Scheme设计与数据类型优化
选择数据类型只要遵循小而简单的原则就好,越小的数据类型通常会更快,占用更少的磁盘、内存,处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期,比如,整型就比字符操作代价低,因而会使用整型来存储ip地址,使用DATETIME来存储时间,而不是使用字符串。
通常来说把可为NULL的列改为NOTNULL不会对性能提升有多少帮助,只是如果计划在列上创建索引,就应该将该列设置为NOTNULL。
对整数类型指定宽度,比如INT(11),没有任何卵用。INT使用16为存储空间,那么它的表示范围已经确定,所以INT(1)和INT(20)对于存储和计算是相同的。
UNSIGNED表示不允许负值,大致可以使正数的上限提高一倍。比如TINYINT存储范围是-~,而UNSIGNEDTINYINT存储的范围却是0–。
通常来讲,没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时,仍然可以使用BIGINT。比如需要精确到万分之一,那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。
TIMESTAMP使用4个字节存储空间,DATETIME使用8个字节存储空间。因而,TIMESTAMP只能表示–年,比DATETIME表示的范围小得多,而且TIMESTAMP的值因时区不同而不同。
大多数情况下没有使用枚举类型的必要,其中一个缺点是枚举的字符串列表是固定的,添加和删除字符串(枚举选项)必须使用ALTERTABLE(如果只只是在列表末尾追加元素,不需要重建表)。
schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列,这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话,有可能会导致CPU占用过高。
大表ALTERTABLE非常耗时,MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表,从旧表中查出所有的数据插入新表,然后再删除旧表。尤其当内存不足而表又很大,而且还有很大索引的情况下,耗时更久。当然有一些奇淫技巧可以解决这个问题,有兴趣可自行查阅。
二、优化COUNT()查询
COUNT()可能是被大家误解最多的函数了,它有两种不同的作用,其一是统计某个列值的数量,其二是统计行数。统计列值时,要求列值是非空的,它不会统计NULL。如果确认括号中的表达式不可能为空时,实际上就是在统计行数。最简单的就是当使用COUNT(*)时,并不是我们所想象的那样扩展成所有的列,实际上,它会忽略所有的列而直接统计所有的行数。
我们最常见的误解也就在这儿,在括号内指定了一列却希望统计结果是行数,而且还常常误以为前者的性能会更好。但实际并非这样,如果要统计行数,直接使用COUNT(*),意义清晰,且性能更好。
有时候某些业务场景并不需要完全精确的COUNT值,可以用近似值来代替,EXPLAIN出来的行数就是一个不错的近似值,而且执行EXPLAIN并不需要真正地去执行查询,所以成本非常低。通常来说,执行COUNT()都需要扫描大量的行才能获取到精确的数据,因此很难优化,MySQL层面还能做得也就只有覆盖索引了。如果不还能解决问题,只有从架构层面解决了,比如添加汇总表,或者使用redis这样的外部缓存系统。
三、优化关联查询
在大数据场景下,表与表之间通过一个冗余字段来关联,要比直接使用JOIN有更好的性能。如果确实需要使用关联查询的情况下,需要特别注意的是:
确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候,如果优化器关联的顺序是A、B,那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担,一般来说,除非有其他理由,只需要在关联顺序中的第二张表的相应列上创建索引(具体原因下文分析)。
确保任何的GROUPBY和ORDERBY中的表达式只涉及到一个表中的列,这样MySQL才有可能使用索引来优化。
要理解优化关联查询的第一个技巧,就需要理解MySQL是如何执行关联查询的。当前MySQL关联执行的策略非常简单,它对任何的关联都执行嵌套循环关联操作,即先在一个表中循环取出单条数据,然后在嵌套循环到下一个表中寻找匹配的行,依次下去,直到找到所有表中匹配的行为为止。然后根据各个表匹配的行,返回查询中需要的各个列。
太抽象了?以上面的示例来说明,比如有这样的一个查询:
SELECTA.xx,B.yy
FROMAINNERJOINBUSING(c)
WHEREA.xxIN(5,6)
假设MySQL按照查询中的关联顺序A、B来进行关联操作,那么可以用下面的伪代码表示MySQL如何完成这个查询:
outer_iterator=SELECTA.xx,A.cFROMAWHEREA.xxIN(5,6);
outer_row=outer_iterator.next;
while(outer_row){
inner_iterator=SELECTB.yyFROMBWHEREB.c=outer_row.c;
inner_row=inner_iterator.next;
while(inner_row){
output[inner_row.yy,outer_row.xx];
inner_row=inner_iterator.next;
}
outer_row=outer_iterator.next;
}
可以看到,最外层的查询是根据A.xx列来查询的,A.c上如果有索引的话,整个关联查询也不会使用。再看内层的查询,很明显B.c上如果有索引的话,能够加速查询,因此只需要在关联顺序中的第二张表的相应列上创建索引即可。
四、优化LIMIT分页
当需要分页操作时,通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDERBY字句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。
一个常见的问题是当偏移量非常大的时候,比如:LIMIT这样的查询,MySQL需要查询条记录然后只返回20条记录,前面的条都将被抛弃,这样的代价非常高。
优化这种查询一个最简单的办法就是尽可能的使用覆盖索引扫描,而不是查询所有的列。然后根据需要做一次关联查询再返回所有的列。对于偏移量很大时,这样做的效率会提升非常大。考虑下面的查询:
SELECTfilm_id,descriptionFROMfilmORDERBYtitleLIMIT50,5;
如果这张表非常大,那么这个查询最好改成下面的样子:
SELECTfilm.film_id,film.description
FROMfilmINNERJOIN(
SELECTfilm_idFROMfilmORDERBYtitleLIMIT50,5
)AStmpUSING(film_id);
这里的延迟关联将大大提升查询效率,让MySQL扫描尽可能少的页面,获取需要访问的记录后在根据关联列回原表查询所需要的列。
有时候如果可以使用书签记录上次取数据的位置,那么下次就可以直接从该书签记录的位置开始扫描,这样就可以避免使用OFFSET,比如下面的查询:
SELECTidFROMtLIMIT,10;
改为:
SELECTidFROMtWHEREidLIMIT10;
其他优化的办法还包括使用预先计算的汇总表,或者关联到一个冗余表,冗余表中只包含主键列和需要做排序的列。
五、优化UNION
MySQL处理UNION的策略是先创建临时表,然后再把各个查询结果插入到临时表中,最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDERBY等字句“下推”到各个子查询中,以便优化器可以充分利用这些条件先优化。
除非确实需要服务器去重,否则就一定要使用UNIONALL,如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致整个临时表的数据做唯一性检查,这样做的代价非常高。当然即使使用ALL关键字,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。虽然很多时候没有这个必要,比如有时候可以直接把每个子查询的结果返回给客户端。
一、MySQL数据库开发规范
数据库规范到底有多重要?有过初创公司经历的朋友应该都深有体会。规范是数据库运维的一个基石,能有效地减少数据库出问题的概率,保障数据库schema的合理设计并方便后续自动化的管理。
曾经我们花了大半年时间来做数据库规范化的工作,例如制定数据库开发指南、给程序员做培训等,推进的时候也会遇到一些阻力。但规范之后运维质量会有一个质的提升,也增进了DBA的工作效率。
在开发规范方面,我们划分为开发规范和运维规范两部分。
1、开发规范
表设计的规范:
字段数量建议不超过20-50个
做好数据评估,建议纯INT不超过万,含有CHAR的不要超过万。字段类型在满足需求条件下越小越好,尽量使用UNSIGNED存储非负整数,因为实际使用时候存储负数的场景不多。
将字符转换成数字存储。例如使用UNSIGNEDINT存储IPv4地址而不是用CHAR(15),但这种方式只能存储IPv4,存储不了IPv6。另外可以考虑将日期转化为数字,如:from_unixtime()、unix_timestamp()。
所有字段均定义为NOTNULL,除非你真的想存储null。
索引设计的规范:
1)所有表必须有显式主键
InnoDB表是以主键排序存储的IOT表
尽量使用短、自增的列做索引
复制结构使用row格式,如果表有主键可以加速复制
UNSIGNEDINT自增列,也可以考虑BIGINT
TINYINT做主键可能导致MySQLCrash
类型转换会导致查询效率很低
可用uuid_short()代替uuid(),转成BIGINT存储
2)合理地建立索引
选择区分度高的列作为索引
单个索引字段数不超过5,单表索引数量不超过5,避免冗余索引
建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾
复合索引排序问题,多用explain去确认
SQL编写规范:
1)避免在数据库中进行大量计算任务
大事务拆成多个事务,分批多次操作
慎用text、blob大型字段,如要用考虑好拆分方案
频繁查询的字典表考虑用Cache抗
2)优化join
避免大表与大表之间的join,考虑让小表去驱动大表join
最多允许三表join,最好控制成两表
控制join后面where选择的行数
3)注重where条件,多用EXPLAIN确认
where条件的字段,尽量用区别度高的字段,这样走索引的性能更好
出现子查询的SQL,先确认MySQL版本,利用explain确认执行计划
进行分页优化;DML时候多个value合并
SchemaReview:
1)字符集问题
表字符集选择UTF8,如果需要存储emoj表情,就改成UTF8mb4
2)Schema设计原则
核心表字段数量尽可能地少,有大字段要考虑拆分
适当考虑一些反范式的表设计,增加冗余字段,减少JOIN
资金字段考虑统一*处理成整型,避免使用decimal浮点类型存储
日志类型的表可以考虑按创建时间水平切割,定期归档历史数据
3)Schema设计目标
快速实现功能为主,保证节省资源
平衡业务技术各个方面,做好取舍
不要在DB里进行大计算,减少复杂操作
整体来说,这部分规范还是很容易遵守的,实现起来也没有什么难度,就能取得很好的效果。
2、运维规范
(1)SQL审核
SQL评审这部分工作相信让很多的DBA同学都叫苦不迭,人肉审核不仅效率低下,容易出错,对DBA的自身发展也非常不利,难道我们来上班就是为了审核SQL的吗?在经过了一段痛苦的人肉审核之后,我们接入了去哪儿网开源的Inception,并根据自身的业务特点做了一些调整。当然现在开源的SQL评审软件已经很多了,大家可以自由选择,也可以自行开发。
在审核与执行上线DDL语句的时候,要注意MySQL官方原生OnlineDDL和Percona公司的pt-osc之间的一些差异,例如pt-osc在执行时每次都要copy全表,相对来说比较慢,好处是不锁表,并且有完善的条件检测和延时负载策略控制。官方OnlineDDL虽然官方也一直在改进,但生产环境使用还不是很完美,尤其要注意执行过程中容易导致MDL锁。官方OnlineDDL也有优于pt-osc的地方,比如增删索引,重命名列等,如下图所示。
(2)权限控制
MySQL从5.6开始,逐步完善了权限系统,比如MySQL5.6可以安装检查密码强度的插件,5.7开始增加了密码过期机制、账户锁定等功能,对SSL这一块也做了一些优化,8.0版本增加了角色的功能,权限系统已经逐步在向Oracle数据库靠拢了。在日常运维中,也可以使用pt-show-grants工具提高权限审查的力度。应用程序账号应只赋予SELECT、INSERT、UPDATE权限,DELETE的逻辑改用UPDATE实现,并启用sql_safe_updates选项。
另一个有效控制权限的方法就是SQL堡垒机,早期我们通过改造MyWebSQL实现,在Web版客户端的基础上加入了一些资源控制策略、审计、语法校验等功能。后续又使用Python开发了功能更完备的SQL堡垒机,同时支持MySQL、Oracle、Greenplum等数据库。
SQL堡垒机不仅可控制公司内部人员的数据库权限,追溯各类人员对数据库的操作,也能避免大查询或全表更新的情况发生,支持审计需求,整体运维质量提升了一个台阶。
(3)MySQL版本选择
MySQL社区版,用户群体最大
MySQL企业版,收费
PerconaServer版,新特性多,和MySQL社区版最接近
MariaDB版,国内用户暂时不多
选择优先级:MySQL社区版PerconaServerMariaDBMySQL企业版
对于版本选择这件事,建议大家还是跟进官方社区版比较好,目前比较稳定的版本是MySQL5.6,推荐大家使用。有特殊需求的话再选择MySQL5.7、PXC、TiDB、TokuDB等数据库。
二、MySQL高可用架构选型
MySQL高可用方面,目前业界主流依然是基于异步复制的技术,例如Keepalived、MHA、ZooKeeper等,要求数据强一致的场景逐步开始使用分布式协议,这方面的典型代表有PXC、GroupReplication、TiDB。下面我们就重点来说说keepalived、MHA和PXC这几种大家用得比较多的架构。
1、keepalived高可用架构
业内使用非常普遍,它部署容易、方便维护,还节省服务器资源。这种架构的一个好处就是在发生切换后,原Master只需重新拉起来即可恢复高可用,不需要过多干预。扩展起来也方便,可以任意挂载只读库和灾备库。但它存在的问题也很明显,比如Keepalived的检测机制不完善、有脑裂隐患、数据一致性较弱等等。
还需要注意主从拓扑的设计。如下图,只读库挂到哪个Master比较合适?显然是M2,其它两种拓扑在发生切换后都会影响到只读库的访问。
2、MHA
MHA自诞生以来,就得到了业内的广泛