13518219792
问题
创新互联建站提供成都做网站、网站设计、网页设计，品牌网站制作，1元广告等致力于企业网站建设与公司网站制作，10年的网站开发和建站经验,助力企业信息化建设，成功案例突破上1000家,是您实现网站建设的好选择.
我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办？
实验
我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。
写个简单的脚本，制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
执行了 16.80s，感觉是非常慢了。
现在用一下 DBA 三板斧，看看执行计划：
感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息。
那我们来 show warnings 看看 MySQL 改写后的 SQL：
我们格式化一下 SQL：
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：
select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2. 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，
而关联子查询就需要循环迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA： 扫描 B 表，找到其中的第一条满足 rA 条件的记录。
显然，关联子查询的扫描成本会高于非关联子查询。
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。
...
可以看到执行时间变成了 0.67s。
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表，执行计划不能提供有效信息。
\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。
\3. 我们增加了 hint，指导 MySQL 正确进行优化判断。
但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。
问题
我们有一个 SQL，用于找到没有主键 / 唯一键的表，但是在 MySQL 5.7 上运行特别慢，怎么办？
实验
我们搭建一个 MySQL 5.7 的环境，此处省略搭建步骤。
写个简单的脚本，制造一批带主键和不带主键的表：
执行一下脚本：
现在执行以下 SQL 看看效果：
...
执行了 16.80s，感觉是非常慢了。
现在用一下 DBA 三板斧，看看执行计划：
感觉有点惨，由于 information_schema.columns 是元数据表，没有必要的统计信息。
那我们来 show warnings 看看 MySQL 改写后的 SQL：
我们格式化一下 SQL：
可以看到 MySQL 将
select from A where A.x not in (select x from B) //非关联子查询
转换成了
select from A where not exists (select 1 from B where B.x = a.x) //关联子查询
如果我们自己是 MySQL，在执行非关联子查询时，可以使用很简单的策略：
select from A where A.x not in (select x from B where ...) //非关联子查询:1. 扫描 B 表中的所有记录，找到满足条件的记录，存放在临时表 C 中，建好索引2. 扫描 A 表中的记录，与临时表 C 中的记录进行比对，直接在索引里比对，
而关联子查询就需要循环迭代：
select from A where not exists (select 1 from B where B.x = a.x and ...) //关联子查询扫描 A 表的每一条记录 rA： 扫描 B 表，找到其中的第一条满足 rA 条件的记录。
显然，关联子查询的扫描成本会高于非关联子查询。
我们希望 MySQL 能先"缓存"子查询的结果（缓存这一步叫物化，MATERIALIZATION），但MySQL 认为不缓存更快，我们就需要给予 MySQL 一定指导。
...
可以看到执行时间变成了 0.67s。
整理
我们诊断的关键点如下：
\1. 对于 information_schema 中的元数据表，执行计划不能提供有效信息。
\2. 通过查看 MySQL 改写后的 SQL，我们猜测了优化器发生了误判。
\3. 我们增加了 hint，指导 MySQL 正确进行优化判断。
但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。
直连到MySQL的功能现在已被添加到ClickHouse中。所以可以直接从ClickHouse查询MySQL表中的数据。
ClickHouse是一个超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面(如GROUP BY)，ClickHouse的查询更快，因此通常情况下在MySQL上进行聚合。
下面是在自己的虚拟环境中做的测试记录。既然是虚拟环境非生产环境，请看参考应用级别，而不是严格的测试。
正确准备和测试大约1000万个数据项。
执行脚本以创建样本数据。这是约500 MB。
在MySQL中创建一个表并导入它。
由于似乎字符串数据比数字值更容易汇总，所以内容是数字的，但它们是可变的类型。
然后执行计算操作。
用BLOB类型做GROUPBY似乎很慢。
您可以在ClickHouse上使用MySQL数据，只需在下面的表格中指定它，而不是表名。
启动ClickHouse客户端并进行测试。
如果您在启动时不添加--multiline选项，则不会放置多行查询，因此请继续。
SELECT
data1,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data1
┌─data1─┬─COUNT()─┐
│ 4│1999013│
│ 3│1998988│
│ 2│1999993│
│ 5│2001553│
│ 1│2000453│
└───────┴─────────┘
5 rows in set. Elapsed: 2.685 sec. Processed 10.00 million rows,40.00 MB(3.72 million rows/s.,14.90 MB/s.)
SELECT
data2,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data2
┌─data2─┬─COUNT()─┐
│6 │ 999786│
│8 │1001805│
│9 │1001438│
│3 │1000357│
│2 │1000648│
│4 │ 998349│
│5 │ 998889│
│10 │ 999424│
│1 │1000530│
│7 │ 998774│
└───────┴─────────┘
10 rows in set. Elapsed: 2.692 sec. Processed 10.00 million rows,101.00 MB(3.71 million rows/s.,37.52 MB/s.)
SELECT
data3,
COUNT(*)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data3
-- 结果省略
100000 rows in set. Elapsed: 5.236 sec. Processed 10.00 million rows,138.89 MB(1.91 million rows/s.,26.52 MB/s.)
SELECT
data1,
uniqExact(data5)
FROM mysql('localhost','mikage','testdata','mikage','')
GROUP BY data1
┌─data1─┬─uniqExact(data5)─┐
│ 4│ 1811674│
│ 3│ 1812072│
│ 2│ 1812503│
│ 5│ 1814106│
│ 1│ 1813005│
└───────┴──────────────────┘
5 rows in set. Elapsed: 12.944 sec. Processed 10.00 million rows,198.89 MB(772.55 thousand rows/s.,15.37 MB/s.)
-- ClickHouse有一个函数来粗略计算一个唯一的数字，所以让我们来计算一下。
在MySQL中，相当耗时的查询也可以在很短的时间内处理。
重复统计时，最好将数据复制到ClickHouse一次。
如果您复制它，后续查询将更快。
建议暂时使用StripeLog引擎。
如果您有一个主键，您可能还想要使用MergeTree表。这是在ClickHouse中最常用的引擎。
有必要用ORDER BY指定数据的排序顺序（即使有重复也没有问题）。
我会尝试以前的查询。以下是StripeLog引擎的测试结果。
测试耗时总结如下:
我认为这个错误很大，因为它在VM环境下仅测试了一次
从左边开始为，(1)MySQL中的时间 (2) ClickHouse从MySQL读取和处理数据的时间 (3) 在ClickHouse上处理复制数据的时间。
执行查询 MySQL处理时间 (秒) MySQL-ClickHouse处理时间 (秒) ClickHouse处理时间 (秒)
groupby(data1) 3.22 2.685 0.071
groupby(data2) 4.01 2.692 0.177
groupby(data3) 212.82 5.236 0.779
groupby(data1)+uniq(data5) 183.56 12.944 1.725
groupby(data1)+uniq(data5)概算 (无此功能) 6.026 0.285
当引用MySQL数据时，如果没有对应于ClickHouse的类型，它似乎是String类型。
没有相应的类型如Decimal类型，所以它也是String类型。
如果你想把它作为一个数值，精度将会改变，但是似乎有必要在MySQL端保持Double类型。
而且，Date和DateTime类型在MySQL和ClickHouse之间的范围也是不同的。
ClickHouse日期类型是1970 - 2038年之间。
如果有超出范围的数据，可能需要使其成为字符串类型，按年份，月份，日期分列，并将其作为数值复制。
参照源码如下：
MySQL类型 ClickHouse 类型 参考
tinyint UInt8 / Int8
smallint UInt16 / Int16
int / mediumint UInt32 / Int32
bigint UInt64 / Int64
float Float32
double Float64
dateDate 有可以表达的范围差异
datetime DateTime有可以表达的范围差异
timestamp DateTime
binaryFixed String
除上述以外 String
