个人中心
文章发布
退出
作者热门文章
- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
27
4
CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章mysql大数据查询优化经验分享(推荐)由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.
正儿八经mysql优化！ 。
mysql数据量少，优化没必要，数据量大，优化少不了，不优化一个查询10秒，优化得当，同样查询10毫秒.
这是多么痛的领悟！ 。
mysql优化，说程序员的话就是：索引优化和where条件优化.
实验环境：MacBook Pro MJLQ2CH/A，mysql5.7，数据量：212万+ 。
ONE:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select
*
from
article
INNER
JOIN
(
SELECT
id
FROM
article
WHERE
length(content_url) > 0
and
(
select
status
from
source
where
id = article.source_id)=1
and
(
select
status
from
category
where
id = article.category_id)=1
and
status = 1
and
id < 2164931
order
by
stick
desc
,pub_time
desc
limit 240,15
)
AS
t
USING(id);
|
咋一看，大佬肯定会想杀了我，没事做啥自关联，还是inner join。XX楼的，把我的杀猪刀拿来，我要宰了博主！！！ 。
说实话，早上出门我的脑袋没被门挤，我也不想这样的.
1.数据量大了，你要做offset很大的分页查询，还真的这样提速，原因 ---> 用join子表中的id覆盖到全表，避免全表扫描.
看我的order by（细语：不就是个order by，TM谁不会写），你把这个order by换成你自己的表中的字段desc or explain看看。Extra ---> filesort ! shit .
2.针对这种多个条件的order by，通常我们会直接给两个字段分别加index，然而还是会Extra ---> filesort。另辟蹊径，给order by后面的所有条件加一个联合索引，注意顺序一定要和你的order by顺序一致。这样Extra就只剩下where了.
再看看where，(select status from source where id = article.source_id)=1 and ...又啥JB写法！ 。
3.想过用join+index的方式，最后测试出来，和这种方式几乎无差别。生产环境是这样写的，那就这样吧，还能少两个索引（source_id，category_id），懒病犯了谁都阻挡不了，以后吃亏了又回来继续优化呗.
4.这个点是我昨晚才get到的，where条件的满足顺序是优先满足最后一个条件，从右到左，经过删除index测试，确实有效果，能从6秒降到4秒，优化了index之后再次测试发现顺序对耗时影响几乎可以忽略不计，0.X毫秒.
TWO:
|
1
2
3
4
5
6
7
|
select
*
from
article
INNER
JOIN
(
SELECT
id
FROM
article
WHERE
INSTR(ifnull(title,
''
),
'战狼'
) > 0
and
status != 9
order
by
pub_time
desc
limit 100,10
)
AS
t USING(id);
|
嗯——又是inner join....... 。
|
1
|
INSTR(ifnull(title,
''
),
'战狼'
) > 0，为啥不用
like
......
|
1.考虑到这是管理平台的搜索，没有去搜索引擎上搜，搜索引擎是一个小时才同步一次数据，数据不全。管理人员搜索时只管他要的结果，like %XX%不能走索引，效率比instr低了5倍，又测试了regexp '.*XX*.'，还是比instr耗时多一点，索性..... 。
|
1
|
desc
or
explain看看，filesort.....给pub_time加个
index
看看，还是filesort.....
|
2.这种情况有另外一种方案，SELECT id FROM article force index(pub_time)，指定使用这个索引。但是这种写法太缺灵活性了，OUT！百度一下，有高人指点迷津：把status和pub_time建个联合索引（pub_time_status，order的条件在前），让where查询的时候，把这个index自动force上.
THREE:
|
1
2
|
select
*
from
article
where
status != 9
order
by
pub_time
desc
limit 100000,25;
desc
or
explain，还是filesort.....前面不是给status和pub_time建了联合索引了吗，tell me why......
|
好吧，我也不知道，把status和pub_time再建个联合索引status_pub_time，这次where条件在前，explain没filesort了，但是这个index却没有被使用，它勾搭出了pub_time_status。搞不懂啊 。
同时我又explain了TWO的SQL，都是如下图:
这二者中删除任何一个都不行，删除一个，就有sql会filesort！ 。
FOUR:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
*
from
follow
where
(((
SELECT
status
FROM
source
WHERE
id=follow.source_id)=1
and
follow.type=1)
or
((
select
status
from
topic
WHERE
id=follow.source_id)=1
and
follow.type=2))
AND
user_id=10054
ORDER
BY
sort limit 15,15;
SELECT
*
from
follow
inner
join
(
SELECT
id
from
follow
where
(((
SELECT
status
FROM
source
WHERE
id=follow.source_id)=1
and
follow.type=1)
or
((
select
status
from
topic
WHERE
id=follow.source_id)=1
and
follow.type=2))
AND
user_id=10054
ORDER
BY
sort limit 15,15
)
as
t using(id);
(
SELECT
id, source_id, user_id,
temporary
, sort, follow_time, read_time,type
from
follow
where
(
SELECT
status
FROM
source
WHERE
id=follow.source_id)=1
and
follow.type=1
and
user_id=10054)
union
all
(
SELECT
id, source_id, user_id,
temporary
, sort, follow_time, read_time,type
from
follow
where
(
select
status
from
topic
WHERE
id=follow.source_id)=1
and
follow.type=2
and
user_id=10054)
ORDER
BY
sort limit 15,15;
|
看看这三句sql，interesting，是不是！ 。
为了公平起见，我已经优化了索引，user_id_sort(user_id,sort)，让where在用user_id判断时force上这个索引.
第一句：0.48ms 。
第二句：0.42ms 。
第三句：6ms，导致时间长那么多的原因是union(查询两次表，合并成子表)后不能用index覆盖到order by的sort上 。
有的时候union不一定比or快.
总结 。
以上所述是小编给大家分享的mysql大数据查询优化经验，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我网站的支持！ 。
最后此篇关于mysql大数据查询优化经验分享(推荐)的文章就讲到这里了,如果你想了解更多关于mysql大数据查询优化经验分享(推荐)的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。
27
4
0
该篇文章与视频是早在ITPub发布的，如今同步过来，视频与文字都下方，大家可以选择方便方式进行阅读观看。 大家好，我是陈珙，今天我想跟大家聊聊技术人的核心竞争力问题。
PHP有很多关于数组的函数，方便数组操作。 定义： 数组每个实体包含两个项：key和value，可以通过查询键来获取其相应的值。这些键可以是数值(numerical)键或关联(associati
虽然如今我们建站很容易，通过很便宜的费用购买一台虚拟主机、一个域名，以及通过开源CMS程序就可以快速搭建一个属于自己的个人网站、企业网站。但是，真正成为站长不是只要搭建好网站就行了的，我们需要解决的
我对 Ruby 一无所知，但我对 DSL 很感兴趣。 DSL 似乎是你们社区的流行语。 您是否真的出于自己的目的在 Ruby 中实现了 DSL？如果是这样，他们有多复杂，有多专注？ 我看过这个ques
当我查看 Google、Amazon 等公司的职位描述时，它们都需要 C++ 和 Linux/Unix 经验。有人可以定义这在工作资格方面通常意味着什么吗？我知道如何在 Linux 中浏览文件系统吗？
上一篇介绍了 Python 枚举类型的标准库，除了考虑到其实用性，还有一个重要的原因是其实现过程是一个非常好的学习、理解 Python 类与元类的例子。因此接下来两篇就以此为例，深入挖掘 Pyt
在linux系统下学习MySQL，第一道坎就是如何在linux下安装和配置mysql。这种一劳永逸的事，我们就有耐心的做好。 系统版本：ubuntu14.04 mysql版本：mys
就目前而言，这个问题不适合我们的问答形式。我们希望答案得到事实、引用资料或专业知识的支持，但这个问题可能会引发辩论、争论、投票或扩展讨论。如果您觉得这个问题可以改进并可能重新打开，visit the
在构建 Docker 镜像时，我发现自己处在一个陌生的地方——我觉得我正在做一些别人以前已经做过很多次的事情——而且做得更好。在大多数情况下，这种直觉是绝对正确的——我正在使用一个软件并在 Docke
我正在 Pygame 中开发一款射击类游戏供我自己娱乐，在创建玩家的基本 Action 的过程中我遇到了一些疑问，“Dash”和“Switch”均未按预期工作。 ... def switch(self
尝试对以下内容进行一些研究，但没有成功。我想我会在这里问，以防有人以前遇到过它。 我帮助一家志愿者运营的广播电台满足他们的技术需求。出现的主要问题之一是他们希望以编程方式安排广告。 有很多用于广告的简
我的组织正在考虑使用 PDFlib 在我们的 Java (Spring/Tomcat) 环境中动态创建 PDF 文件 ( http://www.pdflib.com/)。 有没有人可以分享关于这个库的
我正在考虑使用 TinyMCE 来处理我开发的网站上的文本框。我已经下载了生产版本。 起初，我将在截止日期前将它用于客户网站的后端，我需要它在一开始就开箱即用。以后我也喜欢自定义选项。 基本上，Tin
上一篇解决了通过调用类对象生成实例对象过程中可能遇到的命名空间相关的一些问题，这次我们向上回溯一层，看看类对象本身是如何产生的。 我们知道 type() 方法可以查看一个对象的类型，或者说
Guice 是一个很好的框架，可以将 API 与实现分离并开始模块化您的应用程序。 OSGi 是一个很好的框架，可以在他们自己的安全环境中加载版本化服务，并通过导出的 API 提供这些服务。 假设已经
我在一家从事网络托管的 IT 公司工作，而且我个人对 SQL 非常缺乏经验*。 *看起来很糟糕 我的一个客户正在尝试将 Epos 系统与其 magento 网站集成，在 Epos 集成过程中，他们遇到
想知道在 elasticsearch 中用于多语言索引和搜索的最佳实践或经验是什么。我通读了许多资源，并尽我所能提炼出可用的索引选项: 每种语言的单独索引； 多语言字段的多字段类型； 所有可能语言的单
有人对 PHP QuickHash (http://php.net/manual/en/book.quickhash.php) 有任何经验吗？ 一些早期测试表明，大型数组的内存使用量有了很大改善。包含
初识博客园 我是08年开始接触开发的，一开始涉及的就是.net和java，记得那会好像是jar6来着，net嘛还是2.0 那时候包括现在，找资料很多时候会找到博客园来 一开始我以为博客园是很多博主成
自 9 月以来，我一直在使用 Clojure，这是一种 JVM 上的函数式 lisp 语言。我在几个小型爱好项目中使用了它。语言非常简洁、简单，但我得出的结论是，s 表达式并不适合我。 还有其他函数式
我是一名优秀的程序员,十分优秀！