大数据有道之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等元数据管理功能。由于其开源、承受高并发访问等优点，