数据结构优化之大表设计

问题：针对过于清晰的数据表结构造成的过多的多表 join的情况

主要的优化方案（第一阶段）

1.将关联表合并成大表：旨在减少多表联查的JOIN个数

可以合并的前提：a.几张表关联表示同一类似的业务需求；b.数据的唯一性，即几张表只有一条数据进行相互关联；

如：

【多张表JOIN问题】

SELECT u.user_id, u.user_code, u.user_name, u.avatar, 1 AS TYPE

, ap.city_name AS provinceName, a.city_name AS cityName, l.views_count AS viewsCount, l.open_time AS createTime, l.title AS title

, l.cover_img AS cover_img, '' AS introduce, l.channel_id AS channelId, c.channel_name AS channelName, c.img AS channelImg

, t.tag_id AS tag_id, t.tag_name AS tag_name, l.live_log_id AS videoId, 0 AS likeSum, 0 AS isLiked

, 0 AS commentSum, '' AS videoUrl, 0 AS videoLengthSeconds

FROM t_user u

INNER JOIN t_live_log l ON l.`user_id` = u.user_id

LEFT JOIN t_live_tag lt ON l.live_log_id = lt.live_log_id

LEFT JOIN t_tag t ON t.tag_id = lt.tag_id

INNER JOIN t_area ap ON ap.code_id = l.province

INNER JOIN t_area a ON a.code_id = l.city

INNER JOIN t_channel c ON c.channel_id = l.channel_id

WHERE l.status = 1

AND u.user_id = ?

【解决较长sql 多个JOIN关联查询以及UNION 问题】

SELECT u.user_id AS user_id, u.user_code, u.user_name, u.avatar, 2 AS type

, ap.city_name AS provinceName, a.city_name AS cityName, l.views_count AS viewsCount, p.create_time AS createTime, p.title AS title

, p.cover_img AS cover_img, '' AS introduce, l.channel_id AS channelId, c.channel_name AS channelName, c.img AS channelImg

, t.tag_id AS tag_id, t.tag_name AS tag_name, p.live_playback_id AS videoId, (

SELECT COUNT(0)

FROM t_like

WHERE target_type = 2

AND target_id = p.live_playback_id

AND is_del = 0

) AS likeSum, (

SELECT IFNULL(COUNT(0), 0)

FROM t_like

WHERE target_type = 2

AND target_id = p.live_playback_id

AND user_id = u.user_id

AND is_del = 0

) AS isLiked

, (

SELECT COUNT(0)

FROM t_comment

WHERE type = 2

AND type_id = p.live_playback_id

) AS commentSum, '' AS videoUrl, 0 AS videoLengthSeconds

FROM t_user u

INNER JOIN t_live_log l ON l.`user_id` = u.user_id

INNER JOIN t_live_playback p ON l.live_log_id = p.live_log_id

LEFT JOIN t_live_tag lt ON lt.live_log_id = l.live_log_id

LEFT JOIN t_tag t ON t.tag_id = lt.tag_id

INNER JOIN t_area ap ON ap.code_id = l.province

INNER JOIN t_area a ON a.code_id = l.city

INNER JOIN t_channel c ON c.channel_id = l.channel_Id

WHERE u.user_id = ?

AND p.is_del = 0

AND p.url_m3u8 != ''

UNION

SELECT u.user_id, u.user_code, u.user_name, u.avatar, 3 AS type

, ap.city_name AS provinceName, a.city_name AS cityName, v.views_count AS viewsCount, v.create_time AS createTime, '' AS title

, v.cover_img AS cover_img, v.introduce AS introduce, v.channel_id AS channelId, c.channel_name AS channelName, c.img AS channelImg

, t.tag_id AS tag_id, t.tag_name AS tag_name, v.video_id AS videoId, (

SELECT COUNT(0)

FROM t_like

WHERE target_type = 3

AND target_id = v.video_id

AND is_del = 0

) AS likeSum, (

SELECT IFNULL(COUNT(0), 0)

FROM t_like

WHERE target_type = 3

AND target_id = v.video_id

AND user_id = u.user_id

AND is_del = 0

) AS isLiked

, (

SELECT COUNT(0)

FROM t_comment

WHERE type = 3

AND type_id = v.video_id

) AS commentSum, v.video_url AS videoUrl, v.video_length_seconds AS videoLengthSeconds

FROM t_user u

INNER JOIN t_video v ON v.`user_id` = u.user_id

LEFT JOIN t_video_tag vt ON v.video_id = vt.video_id

LEFT JOIN t_tag t ON t.tag_id = vt.tag_id

INNER JOIN t_area ap ON ap.code_id = v.province

INNER JOIN t_area a ON a.code_id = v.city

INNER JOIN t_channel c ON c.channel_id = v.channel_id

WHERE v.is_del = 0

AND u.user_id = ?

UNION

(SELECT u.user_id, u.user_code, u.user_name, u.avatar, 4 AS type

, ap.city_name AS provinceName, a.city_name AS cityName, p.views_count AS viewsCount, p.create_time AS createTime, p.content AS title

, pi.img_url AS cover_img, '' AS introduce, 0 AS channelId, '' AS channelName, '' AS channelImg

, 0 AS tag_id, '' AS tag_name, p.picture_id AS videoId, (

SELECT COUNT(0)

FROM t_like

WHERE target_type = 4

AND target_id = p.picture_id

AND is_del = 0

AND is_del = 0

) AS likeSum, (

SELECT IFNULL(COUNT(0), 0)

FROM t_like

WHERE target_type = 4

AND target_id = p.picture_id

AND user_id = u.user_id

) AS isLiked

, (

SELECT COUNT(0)

FROM t_comment

WHERE type = 4

AND type_id = p.picture_id

) AS commentSum, '' AS videoUrl, 0 AS videoLengthSeconds

FROM t_user u

INNER JOIN t_picture p ON p.`user_id` = u.user_id

INNER JOIN t_picture_img pi ON pi.`picture_id` = p.picture_id

INNER JOIN t_area ap ON ap.code_id = p.province

INNER JOIN t_area a ON a.code_id = p.city

WHERE p.is_del = 0

AND u.user_id = ?)

ORDER BY createTime DESC

2.做字段迁移处理：即减少中间关联表，主要针对类似type表的一些处理，将type表的id和类型等字段都迁移到主表中，非外键，但是还保留type表，保证单独查询类型的时候有效；

如：查询频道对应的礼物列表

SELECT *

FROM t_gift g

INNER JOIN t_gift_channel gc ON gc.gift_id = g.gift_id

WHERE gc.channel_id = ?

【优化后】

将t_channel 频道表中的字段去重后迁移到t_gift 礼物表中

原 礼物表: t_gift

原 频道表：t_channel

修改后的礼物表：t_gift （将频道表的字段去重后加到礼物表中，增大礼物表的字段数目）

同样类似的关联查询就少了中间关联表 t_gift_channel ,由JOIN联查变为单表查询，但是还是保留 t_channel 表 ，因为有其他需求用到了单查频道的地方；

3.合并成大表或者做表字段迁移的目的以及优缺点

目的：

a.数据扁平化、反序列化处理：增加字段 、合并成大表 将 表结构复杂化是为了减少表个数、从而减少JOIN、UNION 个数，提高查询速率；

b.更好的使用redis、ElasticSearch、rabbitMQ等技术，redis对 JOIN 等多表关联有很大难度，改成大表方便做缓存处理；ElasticSearch等搜索引擎也是基于全表扫描的，更钟爱大表扫描；

缺点：

小表变大表，会增加冗余字段、使整体表结构设计复杂化，对sql语句有更高的要求；需要把握一个平衡点；

4.SQL级别的优化

【sql编写规范：】

1.禁止select * from table 这种不选择列和不加where条件的sql上线

2.禁止使用子查询

3.禁止使用超过5张表 JOIN 的sql

4.禁止在where 条件字段上使用函数

5.oltp 业务事物控制在1000行以内

6.禁止高并发功能对超过1000行的结果集做 group by distinct 等聚合运算

7.尽量避免在两个大表 JOIN 中使用 or 条件，可以使用两次查询代替

【阿里巴巴sql规范】

1. 【强制】不要使用 count( 列名 ) 或 count( 常量 ) 来替代 count( * ) ， count( * ) 是 SQL 92 定义的

标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。

说明： count( * ) 会统计值为 NULL 的行，而 count( 列名 ) 不会统计此列为 NULL 值的行。

2. 【强制】 count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct

col 1, col 2 ) 如果其中一列全为 NULL ，那么即使另一列有不同的值，也返回为 0。

3. 【强制】当某一列的值全是 NULL 时， count(col) 的返回结果为 0，但 sum(col) 的返回结果为

NULL ，因此使用 sum() 时需注意 NPE 问题。

正例：可以使用如下方式来避免 sum 的 NPE 问题： SELECT IF(ISNULL(SUM(g)) ,0, SUM(g))

FROM table;

4. 【强制】使用 ISNULL() 来判断是否为 NULL 值。

说明： NULL 与任何值的直接比较都为 NULL。

1 ） NULL<>NULL 的返回结果是 NULL ，而不是 false 。

2 ） NULL=NULL 的返回结果是 NULL ，而不是 true 。

3 ） NULL<>1 的返回结果是 NULL ，而不是 true 。

5. 【强制】 在代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

6. 【强制】不得使用外键与级联，一切外键概念必须在应用层解决。

说明：以学生和成绩的关系为例，学生表中的 student _ id 是主键，那么成绩表中的 student _ id

则为外键。如果更新学生表中的 student _ id ，同时触发成绩表中的 student _ id 更新，即为

级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群 ； 级联更新是强阻

塞，存在数据库更新风暴的风险 ； 外键影响数据库的插入速度。

7. 【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

8. 【强制】数据订正时，删除和修改记录时，要先 select ，避免出现误删除，确认无误才能执

行更新语句。

9. 【推荐】 in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控

制在 1000 个之内。

10. 【参考】如果有全球化需要，所有的字符存储与表示，均以 utf -8 编码，注意字符统计函数

的区别。

说明：

SELECT LENGTH( "轻松工作" )； 返回为 12

SELECT CHARACTER _ LENGTH( "轻松工作" )； 返回为 4

如果需要存储表情，那么选择 utfmb 4 来进行存储，注意它与 utf -8 编码的区别。

11. 【参考】 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger ，有可能造成事故，故不建议在开发代码中使用此语句。

说明： TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

12. 【强制】 避免使用 * ，必须根据业务字段需求展示需要的字段；

详见：阿里巴巴Java开发规约-终极版.pdf

5.缓存表 和 汇总表 的建立（不推荐）

在允许误差和脏数据的情况下，针对大数据量的报表统计查询，可以建立缓存表和预处理表，根据定时任务执行，查询的时候直接给出的是缓存表或预处理表的数据结果；

（补充：如果可以实现实时性的数据查询和拼接可以考虑）

6.不可避免的长sql多表关联查询

放在代码程序中实现（service 和 serviceImpl），最好是不经常变动的固定模块。如：我的动态等

7.读写分离、建立索引

数据库初步读写分离方案：一主两从（1主MySQL服务器、2只读MySQL数据库）

只查询：2只读MySQL数据库

只写入：1主MySQL服务器

既查又写：1主MySQL服务器