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
新闻标题:mysql怎么查的快 mysql怎么查询最快
网页地址:http://pwwzsj.com/article/hhhoed.html