mysql怎么查的快 mysql怎么查询最快

如果mysql里面的数据过多，查询太慢怎么办？

问题

创新互联建站提供成都做网站、网站设计、网页设计，品牌网站制作，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 正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

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 正确进行优化判断。

但目前我们的实验仅限于猜测，猜中了万事大吉，猜不中就无法做出好的诊断。

如何利用ClickHouse高速汇总查询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