Categories: 技术原创

MYSQL面试八股文-已有的大表加字段和索引(解决卡顿问题)

背景

社交产品,用户表,用户数据600万条,字段87个(别问为什么这么多,问就是业务需要+祖传屎山);此时需要增加一个geometry类型的字段将用户的经纬度数据存入数据表中并增加空间索引, 且要求必须在原表中新加字段(别杠为什么不开新表,杠就是业务需要)。

机器配置:18核36线程CPU,64GB内存。

表结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CREATE TABLE `july_user` (
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`third_user_id` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '第三方登陆id',
`type` VARCHAR(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户类型;d:dady,b:bady',
`age` tinyint NOT NULL DEFAULT '0' COMMENT '用户年龄',
`birthday` DATE NOT NULL DEFAULT '1970-01-01' COMMENT '用户生日',
`sex` tinyint NOT NULL DEFAULT '0' COMMENT '性别0.女,1男',
`sex_filter` tinyint NOT NULL DEFAULT '1' COMMENT '性别筛选1.双性,2男,3女',
`country` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '国家',
`state` VARCHAR(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '省份',
`address` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '地址',
`city` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '城市',
`longitude` DOUBLE(25,20) NOT NULL DEFAULT '0.00000000000000000000' COMMENT '经度',
`latitude` DOUBLE(25,20) NOT NULL DEFAULT '0.00000000000000000000' COMMENT '纬度',
`occupation` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '职业',
`education` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '学历',
`net_assets` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '收入',
`signature` VARCHAR(1500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '签名',
`need_padded_info` tinyint NOT NULL DEFAULT '1' COMMENT '是否需要补充信息',
`is_certified` tinyint NOT NULL DEFAULT '0' COMMENT '审核状态',
`certified_date` datetime NOT NULL COMMENT '审核时间',
`certified_tag` tinyint NOT NULL DEFAULT '0' COMMENT '审核标签',
`certified_tag_time` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '审核标签通过时间',
`is_send_vip` tinyint NOT NULL DEFAULT '0' COMMENT '是否赠送过新注册用户会员',
`is_vip` tinyint NOT NULL DEFAULT '0' COMMENT '是否会员',
`vip_expiry_date` INT UNSIGNED NOT NULL DEFAULT '0' COMMENT '会员过期时间',
`cougar_is_vip` tinyint NOT NULL DEFAULT '0' COMMENT 'cougar是否会员',
`cougar_vip_expiry_date` INT NOT NULL DEFAULT '0' COMMENT 'cougar会员过期时间',
`gay_is_vip` tinyint NOT NULL DEFAULT '0' COMMENT 'gay是否会员',
`gay_vip_expiry_date` INT NOT NULL DEFAULT '0' COMMENT 'gay会员过期时间',
`les_is_vip` tinyint NOT NULL DEFAULT '0' COMMENT 'les是否会员',
`les_vip_expiry_date` INT NOT NULL DEFAULT '0' COMMENT 'les会员过期时间',
`is_deactivate` tinyint NOT NULL DEFAULT '0' COMMENT '是否冻结账号',
`is_passed_by_admin` tinyint NOT NULL DEFAULT '0' COMMENT '是否被管理员通过',
`is_quickpass` tinyint NOT NULL DEFAULT '0' COMMENT '是否快速通过审核',
`is_verify` tinyint NOT NULL DEFAULT '0' COMMENT '是否认证;0.未认证 1.认证中 2.认证通过 3.认证拒绝',
`verify_level` tinyint NOT NULL DEFAULT '0' COMMENT '认证等级',
`verify_type` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '认证类型',
`verify_date` datetime NOT NULL COMMENT '认证时间',
`is_publish_verify_moment` tinyint NOT NULL DEFAULT '0' COMMENT '是否发布了认证moment 0.否 1.是 V460版本后认证通过的用户才有该值',
`is_quality_user` tinyint NOT NULL DEFAULT '0' COMMENT '是否优质推荐',
`like_noti_switch` tinyint NOT NULL DEFAULT '1' COMMENT '是否开启喜欢开关',
`message_noti_switch` tinyint NOT NULL DEFAULT '1' COMMENT '是否开启message开关',
`avatar` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '头像',
`password` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '密码',
`email` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '邮箱',
`realname` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '昵称',
`online_date` INT NOT NULL DEFAULT '0' COMMENT '在线时间',
`register_date` datetime NOT NULL COMMENT '注册时间',
`modify_date` datetime NOT NULL COMMENT '编辑时间',
`regist_date_switch_on` tinyint NOT NULL DEFAULT '1' COMMENT '注册时间开关(0:关/1:开)',
`third_user_email` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '第三方用户邮箱',
`verify_code` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '验证码',
`verify_code_date` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '验证码时间',
`is_certified_email` tinyint NOT NULL DEFAULT '0' COMMENT '是否邮箱认证',
`rel_ticket` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'rel购买标识',
`rel` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'rel信息',
`info` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '版本信息',
`app_version` VARCHAR(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT 'app版本',
`anonymous_name` VARCHAR(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '匿名昵称',
`anonymous_avatar` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '匿名头像',
`udid` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '唯一udid',
`today_moments_count` tinyint NOT NULL DEFAULT '0' COMMENT '今天moment数量',
`is_admin` tinyint NOT NULL DEFAULT '0' COMMENT '控制是否为小助手账户',
`beauty_verify_is_disabled` tinyint NOT NULL DEFAULT '0' COMMENT '认证是否被停⽤, 0未停⽤, 1已停⽤',
`change_beauty_verify_state` tinyint NOT NULL DEFAULT '0' COMMENT '更改认证状态:0:未提交,1:已提交/审核中,2:审核成功,3:审核失败',
`vip_vote_out_times` tinyint NOT NULL DEFAULT '0' COMMENT '会员允许的免审核的机会次数',
`third_platform` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '0' COMMENT '第三方平台',
`gay_role` tinyint NOT NULL DEFAULT '0' COMMENT '同性恋角色:0:other,1:button,2:vers,3:top',
`words` VARCHAR(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '关键词过滤',
`user_language` VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户使用的语言',
`birthday_timestamp` INT NOT NULL DEFAULT '0' COMMENT '生日timestamp',
`language` VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户会的语言',
`is_open_block_message` tinyint NOT NULL DEFAULT '0' COMMENT 'Block Message开关是否打开 0:关闭 1:打开',
`is_open_control_who_see_you` tinyint NOT NULL DEFAULT '0' COMMENT 'is_open_control_who_see_you 开关是否打开 0:关闭 1:打开',
`is_certified_student` tinyint NOT NULL DEFAULT '0' COMMENT '用户是否学生认证 0.未认证 1.认证',
`email_code` VARCHAR(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '用户主动验证邮箱的校验code',
`student_verify_time` INT NOT NULL DEFAULT '0' COMMENT '学生认证时间',
`is_trace_visit` tinyint NOT NULL DEFAULT '0' COMMENT '是否无痕浏览 0.否 1.是',
`risk_score` VARCHAR(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户MaxMind评测风险值',
`first_login_sudynew` tinyint NOT NULL DEFAULT '1' COMMENT '是否未登陆过新版sudy, 1是, 0 否',
`have_contact` tinyint NOT NULL DEFAULT '0' COMMENT '是否有联系方式',
`free_like_count` tinyint UNSIGNED NOT NULL DEFAULT '2' COMMENT '(超级喜欢)免费剩余次数',
`buy_like_count` INT NOT NULL DEFAULT '0' COMMENT '(超级喜欢)购买剩余次数',
`vip_like_count` tinyint UNSIGNED NOT NULL DEFAULT '0' COMMENT '(超级喜欢)会员剩余次数',
`message_count` INT NOT NULL DEFAULT '0' COMMENT '购买的message剩余次数',
`video_message_mins` INT NOT NULL DEFAULT '0' COMMENT '够买的视频剩余时长,分钟数',
`user_location` geometry NOT NULL DEFAULT (point(0.0,0.0)) COMMENT '用户位置序列化存储',
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6541872 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

传统解决方案

由于MySQL自身问题,此处添加geometry字段时不能同时设置not null和默认值,所以需要分两句SQL进行执行。

添加字段:

1
2
3
4
5
ALTER TABLE `july_user`
ADD COLUMN `user_location` geometry DEFAULT (POINT(0.0, 0.0)) COMMENT '用户位置序列化存储' AFTER `video_message_mins`;


-- 执行时间91秒

设置字段默认值字段:

1
2
3
4
ALTER TABLE `july_user` MODIFY COLUMN `user_location` geometry NOT NULL DEFAULT (POINT(0.0, 0.0))  COMMENT '用户位置序列化存储';


-- 执行时间53秒

将原有数据写入新字段:

1
2
3
4
UPDATE `july_user` SET user_location = POINT(longitude, latitude);


-- 执行时间142秒

添加空间索引:

1
2
3
4
ALTER TABLE `july_user` ADD SPATIAL KEY `user_location_index` (`user_location`) COMMENT '用户位置索引';


-- 执行时间644秒

从上面的SQL执行结果可知,虽然达到了目的,但总的执行时长达到了惊人的930秒,整体执行完需要16分钟左右。
而在这期间,虽然MySQL引入了ONLINE DDL,整个表依然有接近8分钟的时间不可使用,这种情况在线上无疑是不可接受的,严重的时候甚至会导致整个服务崩溃。

更优雅的解决方案

①创建一个临时的新表,首先复制旧表的结构(包含索引)
②给新表加上新增的字段
③把旧表的数据复制过来
④重命名旧表为不可用表名,重命名新表的名字为旧表的名字,在适当的时机删除旧表

①创建一个临时的新表,首先复制旧表的结构(包含索引)

1
2
3
CREATE TABLE july_user_slave LIKE july_user;

-- 执行时间0.1毫秒

②给新表加上新增的字段并添加索引

1
2
3
4
5
6
7
ALTER TABLE `july_user_slave`
ADD COLUMN `user_location` geometry DEFAULT (POINT(0.0, 0.0)) COMMENT '用户位置序列化存储' AFTER `video_message_mins`;

ALTER TABLE `july_user_slave` MODIFY COLUMN `user_location` geometry NOT NULL DEFAULT (POINT(0.0, 0.0))  COMMENT '用户位置序列化存储';


-- 执行时间0.1毫秒

③把旧表的数据复制过来并将数据处理正确

1
2
3
4
5
INSERT INTO `july_user_slave`(user_id, third_user_id, TYPE, age, birthday, sex, sex_filter, country, state, address, city, longitude, latitude, occupation, education, net_assets, signature, need_padded_info, is_certified, certified_date, certified_tag, certified_tag_time, is_send_vip, is_vip, vip_expiry_date, cougar_is_vip, cougar_vip_expiry_date, gay_is_vip, gay_vip_expiry_date, les_is_vip, les_vip_expiry_date, is_deactivate, is_passed_by_admin, is_quickpass, is_verify, verify_level, verify_type, verify_date, is_publish_verify_moment, is_quality_user, like_noti_switch, message_noti_switch, avatar, password, email, realname, online_date, register_date, modify_date, regist_date_switch_on, third_user_email, verify_code, verify_code_date, is_certified_email, rel_ticket, rel, info, app_version, anonymous_name, anonymous_avatar, udid, today_moments_count, is_admin, beauty_verify_is_disabled, change_beauty_verify_state, vip_vote_out_times, third_platform, gay_role, words, user_language, birthday_timestamp, LANGUAGE, is_open_block_message, is_open_control_who_see_you, is_certified_student, email_code, student_verify_time, is_trace_visit, risk_score, first_login_sudynew, have_contact, free_like_count, buy_like_count, vip_like_count, message_count, video_message_mins) SELECT user_id, third_user_id, TYPE, age, birthday, sex, sex_filter, country, state, address, city, longitude, latitude, occupation, education, net_assets, signature, need_padded_info, is_certified, certified_date, certified_tag, certified_tag_time, is_send_vip, is_vip, vip_expiry_date, cougar_is_vip, cougar_vip_expiry_date, gay_is_vip, gay_vip_expiry_date, les_is_vip, les_vip_expiry_date, is_deactivate, is_passed_by_admin, is_quickpass, is_verify, verify_level, verify_type, verify_date, is_publish_verify_moment, is_quality_user, like_noti_switch, message_noti_switch, avatar, password, email, realname, online_date, register_date, modify_date, regist_date_switch_on, third_user_email, verify_code, verify_code_date, is_certified_email, rel_ticket, rel, info, app_version, anonymous_name, anonymous_avatar, udid, today_moments_count, is_admin, beauty_verify_is_disabled, change_beauty_verify_state, vip_vote_out_times, third_platform, gay_role, words, user_language, birthday_timestamp, LANGUAGE, is_open_block_message, is_open_control_who_see_you, is_certified_student, email_code, student_verify_time, is_trace_visit, risk_score, first_login_sudynew, have_contact, free_like_count, buy_like_count, vip_like_count, message_count, video_message_mins FROM july_user;
-- 执行时间 181 s

UPDATE `july_user_slave` SET user_location = POINT(longitude, latitude);
-- 执行时间 147 s

④重命名旧表为不可用表名,重命名新表的名字为旧表的名字,在适当的时机删除旧表

1
2
3
4
5
ALTER TABLE july_user RENAME TO july_user_bak;
ALTER TABLE july_user_slave RENAME TO july_user;


-- 执行时间0.1毫秒

总体执行时间330秒,从16分钟缩短到6分钟左右。而且在整个执行过程中不会锁定业务需要使用的表。
需要注意的是在重命名表的时候需要挑一个业务不活跃的时间点,尽量降低业务短时中断的时间到用户不可察觉,同时将数据的不一致风向降到最低。

总结:

一般情况下,十几万的数据量,可以直接进行加字段操作。
如果数据量较大的情况下,整体思路是创建一个新表,复制数据,然后将表名进行更换。

程序猿老龚(龚杰洪)原创,版权所有,转载请注明出处.

龚杰洪

Recent Posts

GOLANG面试八股文-并发控制

背景 协程A执行过程中需要创建…

1 年 ago

MYSQL面试八股文-常见面试问题和答案整理二

索引B+树的理解和坑 MYSQ…

1 年 ago

MYSQL面试八股文-InnoDB的MVCC实现机制

背景 什么是MVCC? MVC…

1 年 ago

MYSQL面试八股文-索引类型和使用相关总结

什么是索引? 索引是一种用于加…

1 年 ago

MYSQL面试八股文-索引优化之全文索引(解决文本搜索问题)

背景:为什么要有全文索引 在当…

1 年 ago