共计 13434 个字符,预计需要花费 34 分钟才能阅读完成。
AskTUG.com 技术问答网站置信大家都不生疏,但除了日常熟知的前端页面外,背地撑持其运行的数据库还有一个鲜为人知的故事。 本文由 AskTUG.com 的作者之一王兴宗老师分享,揭秘诞生于 Discourse 的 AskTUG.com,从 PostgreSQL 迁徙到 MySQL 最初稳固运行在 TiDB 的微妙故事。
一个广告:
AskTUG.com 是 TiDB User、Contributor、合作伙伴的聚集地,在这里你能够找到所有 TiDB 相干问题的答案。欢送大家注册体验~
链接:https://asktug.com/
背景
“通过一个平台,肯定能找到 TiDB 所有问题的称心答案。”
因为这样的欲望,TiDB 生态中的用户、Contributor、合作伙伴一起建设了 AskTUG.com 技术问答网站,并于 2019 年 8 月正式公开上线。作为 TUG 成员学习、分享的“聚集地”,TiDB 用户能够在这里提出、解答问题,互相交换探讨,这里会集 TiDB 用户的个体智慧。自上线以来,AskTUG.com 逐步吸引了越来越多用户的关注,截止 2021 年 6 月底,AskTUG.com 已有 7000+ 注册用户,积淀了 1.6w+ 问题和 300+ 技术文章。
很多小伙伴都曾经发现,AskTUG.com 的后端程序是一个 Discourse 程序。对于 Discourse 是 Stack Overflow 的联结创始人 Jeff Atwood 推出的一个新的开源论坛我的项目,其目标是为了扭转十年未变的论坛软件。在 AskTUG.com 建设之初,从以下几个角度确定应用 Discourse:
- 功能强大 :Discourse 个性丰盛,可定制性强,是论坛界的 WordPress。与其余传统的论坛相比,Discourse 简化了传统论坛的分类,取而代之是热贴,这点有点像问答,它能够防止用户进入传统论坛后找不到方向的迷茫,这个个性从 AskTUG.com 的页面便可见一斑:
-
受众宽泛水平 :大部分风行的开源我的项目都抉择应用 Discourse 来搭建本人的社区,包含:
- Docker:https://forums.docker.com/
- Github Atom:https://discuss.atom.io/
- Mozilla:https://discourse.mozilla.org/
- TiDB:https://asktug.com/
- Discourse:https://meta.discourse.org/
- Rust:https://users.rust-lang.org/
-
- 更多:https://discourse.org/customers
- 易用性好 :Discourse 的帖子是气泡模式展示,全副 Ajax 加载,有电脑和挪动版,论坛采纳了瀑布流的设计,主动加载下一页,无需手动翻页,简略来说这是一个很赞的零碎。
为什么要迁徙
到当初为止,Everything is Good,除了一点:Discourse 官网只反对 PostgreSQL 这一种数据库。
作为一家开源数据库厂商,咱们有极大的激情和充沛的理由让 AskTUG.com 跑在本人的数据库 TiDB 上,最后有这个想法时,当然是找有没有曾经将 Discourse port 到 MySQL 的计划,后果是问的人多,口头的没有。
于是咱们决定本人来做 Discourse 数据库革新这件事件。起因有二:
- 吃本人的狗粮,验证 TiDB 的兼容性。
- Discourse 是一个典型的 HTAP 型利用,它的治理后盾有很简单的报表查问,随着论坛数据量减少,单机 PostgreSQL、MySQL 都很容易呈现性能瓶颈。TiDB 5.0 引入的 TiFlash MPP 计算模型正好满足了这种利用场景需要,通过引入 TiFlash 节点,对一些简单的统计分析类查问做并行处理,达到减速的成果。并且不须要改变 SQL 和简单的 ETL 流程。
迁徙实际
后面咱们讲述了做 AskTUG & Discourse 数据库革新我的项目的起因始末,接来下,则会粗疏地讲下从 PostgreSQL 迁徙到 MySQL / TiDB 踩过的“坑”,如果有从 PG 迁徙到 MySQL 的敌人能够拿来参考。
TiDB 同时兼容 MySQL 协定和生态,迁徙便捷,运维老本极低。因而,Discourse 从 PG 迁徙到 TiDB 大抵分为两步 :
第一步:将 Discourse 迁徙到 MySQL;
第二步:适配 TiDB。
Migrate to MySQL 5.7
🌟mini_sql
minisql 是一个轻量级的 sql wraper,不便做一些 ORM 不善于的查问,并且能够避免 SQL 注入。之前只反对 PG 和 sqlite。Discourse 的代码依赖 minisql 的中央十分多,重写的话工作量微小,patch mini_sql 来反对 MySQL 是可能迁徙实现的一个重要步骤:https://github.com/discourse/…
🌟 schema migration
Rails 的 schema migration 用来保护 DDL,反映的是数据库 schema 的变动过程,对于迁徙来说,其实减少了工作量,解决办法是,学生成一份最终的 schema.rb 文件,在最终后果上做批改,生成一份新的 migration 文件。两头过程产生的 migration 文件删掉就能够了。
🌟 character set utf8mb4
database.yml
development:
prepared_statements: false
encoding: utf8mb4
socket: /tmp/mysql.sock
adapter: mysql2
/etc/mysql/my.cnf
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
🌟 MySQL can index only the first N chars of a BLOB or TEXT column
PG 的所有类型都是能够索引的,MySQL 不能索引 text 类型,解决办法是,索引的时候指定长度:
t.index ["error"], name: "index_incoming_emails_on_error", length: 100
但对于组合索引的状况更简单,只能疏忽带 text 类型的,好在索引不影响性能。
🌟 data migration
pg2mysql 能够把 pgdump 进去的 insert 语句转换成兼容 MySQL 语法的模式,但只限于简略的模式,一些带有 array 和 json 的格局会乱掉,但这部分用 Ruby 解决起来是正确的,分成两局部解决,首先 pg2mysql 来解决排除一些转换出错的表,比方 user_options、site_settings 等:
PGPASSWORD=yourpass pg_dump discourse_development -h localhost
--quote-all-identifiers
--quote-all-identifiers
--inserts
--disable-dollar-quoting
--column-inserts
--exclude-table-data user_options
--exclude-table-data user_api_keys
--exclude-table-data reviewable_histories
--exclude-table-data reviewables
--exclude-table-data notifications
--exclude-table-data site_settings
--exclude-table-data reviewables
--no-acl
--no-owner
--format p
--data-only -f pgfile.sql
剩下一部分数据应用 seed_dump 来迁徙:
bundle exec rake db:seed:dump
MODELS=UserApiKey,UserOption,ReviewableHistory,
Reviewable,Notification,SiteSetting
EXCLUDE=[] IMPORT=true
🌟 distinct on
PG 有一个 distinct on 的用法,等价于 MySQLONLY\_FULL\_GROUP_BY 参数敞开时的成果,但从 MySQL 5.7 开始,这个参数默认曾经开启了。所以解决办法一个是关掉 ONLY\_FULL\_GROUP_BY 参数,另一个是用 GROUP 和聚合函数模仿:
# postgresql
SELECT DISTINCT ON (pr.user_id) pr.user_id, pr.post_id, pr.created_at granted_at
FROM post_revisions pr
JOIN badge_posts p on p.id = pr.post_id
WHERE p.wiki
AND NOT pr.hidden
AND (:backfill OR p.id IN (:post_ids))
# mysql
SELECT pr.user_id, MIN(pr.post_id) AS post_id, MIN(pr.created_at) AS granted_at
FROM post_revisions pr
JOIN badge_posts p on p.id = pr.post_id
WHERE p.wiki
AND NOT pr.hidden
AND (:backfill OR p.id IN (:post_ids))
GROUP BY pr.user_id
🌟 returning
PG 的 UPDATE、DELETE、INSERT 语句都能够带一个 returning 关键词,用来返回批改 / 插入之后的后果。对于 UPDATE 和 DELETE 语句,MySQL 改起来比拟容易,只须要拆成两步,先查出主键,再更新或删除:
update users set updated_at = now() where id = 801 returning id,updated_at ;
id | updated_at
-----+---------------------------
801 | 2019-12-30 15:43:35.81969
MySQL 版本 :
update users set updated_at = now() where id = 801;
select id, updated_at from users where id = 801;
+-----+---------------------+
| id | updated_at |
+-----+---------------------+
| 801 | 2019-12-30 15:45:46 |
+-----+---------------------+
对于单条 INSERT 状况,须要应用 last\_insert\_id() 函数:
PG 版本 :
insert into category_users(user_id, category_id, notification_level) values(100,100,1) returning id, user_id, category_id;
id | user_id | category_id
----+---------+-------------
59 | 100 | 100
改成 MySQL 版本:
insert into category_users(user_id, category_id, notification_level) values(100,100,1);
select id, category_id, user_id from category_users where id = last_insert_id();
+----+-------------+---------+
| id | category_id | user_id |
+----+-------------+---------+
| 48 | 100 | 100 |
+----+-------------+---------+
对于批量插入,须要改成单条 INSERT,再应用 last\_insert\_id() 函数,因为 MySQL 没有提供 last\_insert\_id() 函数:
ub_ids = records.map do |ub|
DB.exec("INSERT IGNORE INTO user_badges(badge_id, user_id, granted_at, granted_by_id, post_id)
VALUES (:badge_id, :user_id, :granted_at, :granted_by_id, :post_id)",
badge_id: badge.id,
user_id: ub.user_id,
granted_at: ub.granted_at,
granted_by_id: -1,
post_id: ub.post_id
)
DB.raw_connection.last_id
end
DB.query("SELECT id, user_id, granted_at FROM user_badges WHERE id IN (:ub_ids)", ub_ids: ub_ids)
🌟 insert into on conflict do nothing
PG 9.5 开始反对 upsert,MySQL 也有同样的性能,只是写法不统一:
# postgresql
DB.exec(<<~SQL, args)
INSERT INTO post_timings (topic_id, user_id, post_number, msecs)
SELECT :topic_id, :user_id, :post_number, :msecs
ON CONFLICT DO NOTHING
SQL
# MySQL
DB.exec(<<~SQL, args)
INSERT IGNORE INTO post_timings (topic_id, user_id, post_number, msecs)
SELECT :topic_id, :user_id, :post_number, :msecs
SQL
🌟 select without from
PG 里容许这样的语法:select 1 where 1=2;
但在 MySQL 里这是不非法的,因为没有 FROM 子句,解决办法很 trick,手动建一个只有一条数据的表,专门用来兼容这个语法。
execute("create table one_row_table (id int)")
execute("insert into one_row_table values (1)")
MySQL 应用:
# MySQL
select 1 from one_row_table where 1=2;
🌟 full outer join
MySQL 不反对 full outer join,须要应用 LEFT JOIN + RIGHT JOIN + UNION 来模仿:
# MySQL
SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id
🌟 recursive cte
MySQL 8.0 之前不反对 CTE/Recursive CTE,构造简略的 CTE 能够间接改成子查问,除了可读性差以外,性能上没任何影响。recursive CTE 能够用 User-defined variables 来模仿。Discourse 里有一个嵌套回复的查问:
WITH RECURSIVE breadcrumb(id, level) AS (
SELECT 8543, 0
UNION
SELECT reply_id, level + 1
FROM post_replies AS r
JOIN breadcrumb AS b ON (r.post_id = b.id)
WHERE r.post_id <> r.reply_id
AND b.level < 1000
), breadcrumb_with_count AS (
SELECT
id,
level,
COUNT(*) AS count
FROM post_replies AS r
JOIN breadcrumb AS b ON (r.reply_id = b.id)
WHERE r.reply_id <> r.post_id
GROUP BY id, level
)
SELECT id, level
FROM breadcrumb_with_count
ORDER BY id
应用 MySQL 5.7 来兼容:
# MySQL
SELECT id, level FROM (SELECT id, level, count(*) as count FROM (SELECT reply_id AS id, length(@pv) - length((replace(@pv, ',', ''))) AS level
FROM (SELECT * FROM post_replies ORDER BY post_id, reply_id) pr,
(SELECT @pv := 8543) init
WHERE find_in_set(post_id, @pv)
AND length(@pv := concat(@pv, ',', reply_id))
) tmp GROUP BY id, level
) tmp1
WHERE (count = 1)
ORDER BY id
PG 的 cte 是能够嵌套的,比方 Discourse 里这段查问,注 WITH period_actions 是嵌套在 flag_count 外面的:
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE u.moderator = 'true'
AND u.id > 0
),
time_read AS (SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv
ON m.user_id = uv.user_id
WHERE uv.visited_at >= '#{report.start_date}'
AND uv.visited_at <= '#{report.end_date}'
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (#{PostActionType.flag_types_without_custom.values.join(',')})
AND created_at >= '#{report.start_date}'
AND created_at <= '#{report.end_date}'
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
这种用子查问模仿起来就非常复杂, 能够应用长期表来兼容,查问局部不须要任何批改 ,只须要按依赖程序把 WITH 局部换成长期表:
DB.exec(<<~SQL)
CREATE TEMPORARY TABLE IF NOT EXISTS mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE u.moderator = true
AND u.id > 0
)
SQL
DB.exec(<<~SQL)
CREATE TEMPORARY TABLE IF NOT EXISTS time_read AS (SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv
ON m.user_id = uv.user_id
WHERE uv.visited_at >= '#{report.start_date.to_s(:db)}'
AND uv.visited_at <= '#{report.end_date.to_s(:db)}'
GROUP BY uv.user_id
)
SQL
🌟 delete & update
PG 和 MySQL 的 update/delete 语句写法是不一样的,应用 ORM 会主动解决,但 Discourse 里大量代码是应用 mini_sql 手写的 SQL,须要一一替换。
PG 的 update 语句写法:
# postgresql
UPDATE employees
SET department_name = departments.name
FROM departments
WHERE employees.department_id = departments.id
MySQL 的 update 语句写法:
# MySQL
UPDATE employees
LEFT JOIN departments ON employees.department_id = departments.id
SET department_name = departments.name
delete 语句也相似。
🌟 You can’t specify target table xx for update in FROM clause
从 PG 迁到 MySQL 之后,很多语句会报这样一个谬误:You can’t specify target table ‘users’ for update in FROM clause。
# MySQL
update users set updated_at = now() where id in (select id from users where id < 10);
# You can't specify target table'users' for update in FROM clause
解决办法是子查问里再用 derived table:
# MySQL
update users set updated_at = now() where id in (select id from (select * from users) u where id < 10
);
🌟 MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
还拿下面的查问举例,子查问如果带 LIMIT:
# MySQL
update users set updated_at = now() where id in (select id from (select * from users) u where id < 10 limit 10
);
# MySQL doesn't yet support'LIMIT & IN/ALL/ANY/SOME subquery'
最简略的解决办法是再 derived 一次:
# MySQL
update users set updated_at = now() where id in (
select id from (select id from (select * from users) u where id < 10 limit 10
) u1
);
🌟 window function
MySQL 8.0 之前没有窗口函数,能够应用 User-Defined Variables 代替:
# postgresql
WITH ranked_requests AS (SELECT row_number() OVER (ORDER BY count DESC) as row_number, id
FROM web_crawler_requests
WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
)
DELETE FROM web_crawler_requests
WHERE id IN (
SELECT ranked_requests.id
FROM ranked_requests
WHERE row_number > 10
)
# MySQL
DELETE FROM web_crawler_requests
WHERE id IN (
SELECT ranked_requests.id
FROM (
SELECT @r := @r + 1 as row_number, id
FROM web_crawler_requests, (SELECT @r := 0) t
WHERE date = '#{1.day.ago.strftime("%Y-%m-%d")}'
ORDER BY count DESC
) ranked_requests
WHERE row_number > 10
)
🌟 swap columns
MySQL 和 PG 在解决 update 语句时,column 的援用行为是不统一的,PG 援用的是原始值,而 MySQL 援用的是更新后的值,举个例子:
# postgresql
create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));
insert into tmp values (1,2,3);
insert into tmp values (2,4,5);
select * from tmp;
id | c1 | c2
----+----+----
1 | 3 | 2
2 | 5 | 4
update tmp set c1=c2,c2=c1;
select * from tmp;
id | c1 | c2
----+----+----
1 | 3 | 2
2 | 5 | 4
# MySQL
create table tmp (id integer primary key, c1 varchar(10), c2 varchar(10));
insert into tmp values (1,2,3);
insert into tmp values (2,4,5);
select * from tmp;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 2 | 3 |
| 2 | 4 | 5 |
+----+------+------+
update tmp set c1=c2,c2=c1;
select * from tmp;
+----+------+------+
| id | c1 | c2 |
+----+------+------+
| 1 | 3 | 3 |
| 2 | 5 | 5 |
+----+------+------+
🌟 function
PG 和 MySQL 的一些内置函数名称和行为会有一些不统一:
- regexp_replace -> replace
- pg_sleep -> sleep
- ilike -> lower + like
- ~* -\> regexp
- || -\> concat
- set local statementtimeout -> set session statementtimeout
- offset a limit b -> limit a offset b
- @ -\> ABS
- interval -> date_add 或者 datediff
- extract epoch from -> unix_timestimp
- unnest -> union all
- json 语法:json->> ‘username’ to json ->> ‘$.username’
- position in -> locate
- generate_series -> union
- greatest & least -> greatest/least + coalesce
🌟 type & casting
MySQL 应用 cast 函数,PG 也反对同样的语法,不过更罕用的是四个点::,比方 SELECT 1::varchar,MySQL 的转换类型只能是上面 5 种:CHAR[(N)]、DATE、DATETIME、DECIMAL、SIGNED、TIME。
select cast('1' as signed);
Rails 里 string 类型,PG 映射成 varchar,MySQL 映射成 varchar(255),而 PG 的 varchar 其实是能够存储超过 255 的,Discourse 里一些应用 string 类型的数据会超过 255,转成 MySQL 当前会被截断,解决办法是对这部分列应用 text 类型。
🌟 keywords
MySQL 和 PG 的 keywords 列表并不完全一致,比方 read 在 MySQL 里是关键字,在 PG 里并不是。对于 ORM 生产的 SQL 曾经解决好了,一些手写的 SQL 须要本人去 quote,PG 应用 ””,MySQL 应用 “。
🌟 expression index
PG 反对表达式索引:
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
Discourse 外面一些性能会在表达式索引下面加惟一束缚,MySQL 没有间接的对应,然而能够应用 Stored Generated Column 来模仿,先冗余一个 Stored Generated Column,再在下面加惟一束缚,达到了同样的成果。
Rails 也反对:
t.virtual "virtual_parent_category_id", type: :string, as: "COALESCE(parent_category_id,'-1')", stored: true
t.index "virtual_parent_category_id, name", name: "unique_index_categories_on_name", unique: true
🌟 array && json
PG 反对 array 和 json 类型,MySQL 5.7 已结有了 JSON,Discourse 里,ARRAY 和 JSON 的应用场景比拟繁多,都是用来存储,没有高级检索需要,间接应用 JSON 能够代替 PG 的 array 和 json。但 MySQL 的 JSON 和 text 都不反对 default value,只能在应用层设置,能够应用:https://github.com/FooBarWidg…
适配 TiDB
TiDB 反对 MySQL 传输协定及其绝大多数的语法,然而一些个性因为在分布式环境下没法很好地实现,所以在局部个性的体现依然与 MySQL 有一些差别,详见文档 https://pingcap.com/docs-cn/s…,接下来咱们次要看一下本次迁徙中波及到的一些小问题。
🌟 TiDB 保留关键字
TiDB 在新版本 (本次迁徙应用 v3.0.7) 中反对了 Window Function,引入了 group
、rank
、row_number
等函数,但比拟非凡的是上述函数名都会被 TiDB 当做关键词解决,所以咱们在开启窗口函数的时候须要批改命名与窗口函数名相似的 SQL,将相干的关键字用反引号包住。
TiDB 保留关键字:https://pingcap.com/docs-cn/s…
TiDB 窗口函数: https://pingcap.com/docs-cn/s…
🌟 Insert into select 语法不兼容
TiDB 临时不反对该语法,能够应用 insert into select from dual 绕过:
invalid: insert into t1 (i) select 1;
valid: insert into t1 (i) select 1 from dual;
🌟 嵌套事务 & savepoint
TiDB 不反对嵌套事务,同样也不反对 savepoint。然而 Rails ActiveRecord 在数据库是 MySQL 或者 PostgreSQL 时,应用 savepoint 来模仿嵌套事务,并应用 requires_new
选项来管制,文档:https://api.rubyonrails.org/c…。
所以在数据库迁徙到 TiDB 后,咱们须要调整业务代码,将原有波及到 嵌套事务
的逻辑,调整为单层事务,遇到异样对立回滚,同时在 discourse 中勾销应用 requires_new
选项。
TiDB 弱小的兼容性
TiDB 100% 兼容 MySQL 5.7 协定。除此之外,还反对了 MySQL 5.7 罕用的性能及语法。MySQL 5.7 生态中的零碎工具(PHPMyAdmin、Navicat、MySQL Workbench、mysqldump、Mydumper/Myloader)、客户端等均实用于 TiDB。同时,TiDB 5.0 之后,很多新的个性也将陆续公布,比方表达式索引、CTE、长期表等,新版本的 TiDB 兼容性越来越好,从 MySQL 或 PostgreSQL 迁徙到 TiDB 也会变得越来越容易。
总结
该我的项目曾经 100% 实现,且目前 AskTUG 网站(https://asktug.com)安稳地运行在 TiDB(以后版本:tidb-v5.0.x)上已一年无余。是的,在没扭转体验的状况下,谁也没有发现数据库曾经轻轻扭转了~证实了跑在 PG 上的业务迁徙到 TiDB 的可行性。
我的项目的地址是:https://github.com/tidb-incub…,能够通过 fork & 提 PR 的形式来参加改良并关注该项目标停顿,也十分欢送 Ruby 社区,Ruby On Rails 社区,Discourse 社区的小伙伴来感触下来自 TiDB 社区的善意。