共计 3339 个字符，预计需要花费 9 分钟才能阅读完成。
这篇文章给大家分享的是有关 mysql 大数据查询优化的示例分析的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。
mysql 数据量少，优化没必要，数据量大，优化少不了，不优化一个查询 10 秒，优化得当，同样查询 10 毫秒。
这是多么痛的领悟！
mysql 优化，说程序员的话就是：索引优化和 where 条件优化。
实验环境：MacBook Pro MJLQ2CH/A，mysql5.7，数据量：212 万 +
ONE：
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：
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…….
INSTR(ifnull(title,), 战狼 ) 0，为啥不用 like......1. 考虑到这是管理平台的搜索，没有去搜索引擎上搜，搜索引擎是一个小时才同步一次数据，数据不全。管理人员搜索时只管他要的结果，like %XX% 不能走索引，效率比 instr 低了 5 倍，又测试了 regexp .*XX*.，还是比 instr 耗时多一点，索性 …..
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：
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：
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 大数据查询优化的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！
