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 = FALSEcharacter-set-server = utf8mb4collation-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 和聚合函数模仿：

# postgresqlSELECT 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))# mysqlSELECT 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_idendDB.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 也有同样的性能，只是写法不统一:

# postgresqlDB.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 NOTHINGSQL# MySQLDB.exec(<<~SQL, args)  INSERT IGNORE INTO post_timings (topic_id, user_id, post_number, msecs)  SELECT :topic_id, :user_id, :post_number, :msecsSQL

 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应用：

# MySQLselect 1 from one_row_table where 1=2;

 full outer join

MySQL 不反对 full outer join，须要应用 LEFT JOIN + RIGHT JOIN + UNION 来模仿:

# MySQLSELECT * FROM t1LEFT JOIN t2 ON t1.id = t2.idUNIONSELECT * FROM t1RIGHT 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 来兼容：

# MySQLSELECT 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) tmp1WHERE (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 语句写法：

# postgresqlUPDATE employeesSET department_name = departments.nameFROM departmentsWHERE employees.department_id = departments.id

MySQL 的 update 语句写法：

# MySQLUPDATE employeesLEFT JOIN departments ON employees.department_id = departments.idSET 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。

# MySQLupdate 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：

# MySQLupdate 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：

# MySQLupdate 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 一次：

# MySQLupdate 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 代替：

# postgresqlWITH 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_requestsWHERE id IN (  SELECT ranked_requests.id    FROM ranked_requests   WHERE row_number > 10)# MySQLDELETE FROM web_crawler_requestsWHERE 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 援用的是更新后的值，举个例子：

# postgresqlcreate 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  | 4update tmp set c1=c2,c2=c1;select * from tmp; id | c1 | c2----+----+----  1 | 3  | 2  2 | 5  | 4# MySQLcreate 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 社区的善意。