大数据有道之Mysql实用技能

一、

Mysql简介

Mysql作为主流关系型数据库,不仅为服务端提供业务数据的存储和检索功能,同时可以辅助支持kafka offset、spark job、redis key等元数据管理工作。

由于Mysql具有开源、高并发等特性,目前被广泛应用大数据项目开发中。

结合生产环境场景,大数据有道将从以下4方面介绍Mysql实用开发技能:

1.数据时效性:设置update_time标识

2.数据唯一性:使用唯一键(UNIQUE KEY)

3.数据一致性:更新唯一键数据

4.数据高效性:建立组合索引

注:文中操作基于Mysql 8.0.11版本,InnoDB引擎。

二、

实用技能

1.数据实效性:设置update_utime

a.背景:大数据实时处理场景下需要确保业务指标的时效性,同时方便跟踪数据处理流程。

b.解决方法:建表中设置update_time字段,类型为CURRENT_TIMESTAMP,该字段能够自动标识数据写入的时间。

c.执行示例。

2.数据唯一性:使用唯一键

a.背景:Lambda数据架构在遇到上游业务异常或变更场景时,经常需要重导和回溯历史数据,而电商类的数据因业务复杂需要严格限制数据的一致性和正确性,尽量避免重复数据(重复数据也会增大作业资源和耗时)。

b.解决方法:建表脚本中设置唯一键(UNIQUE KEY),标识唯一一条业务记录。

c.验证示例:发生重复订单写入时,Mysql会发出唯一键重复告警。

3.数据一致性:更新唯一键数据

a.背景:针对上游业务数据重导和回溯场景,下游需要做到数据实时更新,同时要保证数据与上游业务的一致性。

b.解决方法及执行验证:

i.事务更新:Mysql提供事务回滚,通过delete后插入最新数据(能够保证删除和插入共同执行)

ii.on duplicate key update:mysql特有算子,插入前检查是否有相同的UNIQUE KEY,若没有相当于insert,若有则相当于delete+insert (2 rows affected)

iii.replace into:mysql特有算子,“替换”前检查是否有相同的UNIQUE KEY,若没有相当于insert,若有则相当于delete+insert(2 rows affected)

c.更新方法对比:i和iii频繁操作会增加大量主键(id),占用大量表空间;ii在源数据基础上修改不会增加主键(id),但有出现死锁风险。

4.数据高效性:建立组合索引

a.背景:在GB级Mysql表中查询和删改数据时,如果没有索引的话就需要对全表数据进行磁盘询道,这对于Mysql抑或服务端无疑是件恐怖的事情。

b.优化方法:根据业务场景对特定字段增加单独或组合索引(由于UNIQUE KEY具有索引属性,为对比索引效率,以下对hour和valid字段添加组合索引(“alter table bigdatayoudao_table add index `idx_hour_valid` (`hour`,`valid`);”))。

c.执行结果:增加索引后查询数据量降低95.5%左右,查询耗时降低88%左右,执行性能有了可观的提升。

d.组合索引注意事项:

i.“最左前缀”法则,即组合索引中最左(第一个字段)在where条件中时索引才使用生效,与组合索引中字段顺序无关,因此4.b中示例不会触发uniq_order_id索引生效;

ii.索引列独立使用,即索引字段不能参与计算,如where hour+1='23'或者where hour like '%2%'时索引idx_hour_valid失效,但where hour like '2%'可行,这与BTREE中索引存储值有关;

iii.索引区分度,公式为count(distinct col1,col2)/count(*)表示不重复组合字段比例,UNIQUE KEY区分度为1,不考虑业务场景时,区分度越高越适合建立索引,查询性能则越高。

三、

归纳总结

1.针对实时业务场景,Mysql可以根据系统时间(CURRENT_TIMESTAMP)标识数据到达时刻,便于上、下游作业的troubleshooting;

2.为保证Mysql数据唯一性和一致性,可以通过设置UNIQUE KEY方式达到目的,与此同时更新数据时则需要根据UNIQUE KEY来完成数据的替换或者更新;

3.索引是Mysql高效查询的必备属性,合理设置组合索引能过极大提升服务端和应用端的性能。

大数据有道BIgData In Thinking!

大数据有道,为您提供通俗易懂的技能分享,让大数据更容易!

Mysql是一个关系型数据库管理系统(RDBMS:Relational Database Management System),服务端设计数据存储的检索的关联数据库,能承受高并发访问支持kafka offset、spark job、元数据管理、

Mysql作为主流关系型数据库管理系统,不仅为服务端提供业务数据的存储和检索功能,同时可以支持kafka offset、spark job、redis key等元数据管理功能。由于其开源、承受高并发访问等优点,

Mysql作为主流关系型数据库管理系统,不仅为服务端提供业务数据的存储和检索功能,同时可以支持kafka offset、spark job、redis key等元数据管理功能。由于其开源、承受高并发访问等优点,

Mysql作为主流关系型数据库管理系统,不仅为服务端提供业务数据的存储和检索功能,同时可以支持kafka offset、spark job、redis key等元数据管理功能。由于其开源、承受高并发访问等优点,

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181125G0YPS800?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券