网易首页 > 网易号 > 正文 申请入驻

让你脑洞大开的MySQL优化技巧

0
分享至

由于分库分表的原因,和开发规定了不能使用 表表JOIN 语句。因此,我们要将 JOIN 语句的转化成使用 IN 来做。如现在有 表 A(a_id, c_a)c_a有普通索引,表 B(b_id, c_a) 这两个表要关联, 应该转化为以下步骤处理:

  • 先查询B中的 a_id

SELECT c_a FROM B WHERE xxx;

  • 使用 IN 查询 A 表

SELECT a_id, ... FROM A WHERE c_a IN(在 1 中查出来的 c_a)

场景

现在表的数据量有 800万。

一般的使用语句是:

SELECT * FROM A WHERE c_a IN(955555, 955556, 955557, 955558, 955559);

上面语句会执行的很快,知道使用 explain 的都明白这样一般都是会使用索引的,并且是所有范围扫描。

MySQL不会从 1 开始 扫描 800万,而是从555555 扫描到 555559(只要扫描5行数据)。

在一般情况下是没有什么问题的。但是如果 IN 里面的数据是不连续的就有很大问题了。

创建表结构语句

CREATE TABLE t(
id INT unsigned NOT NULL AUTO_INCREMENT,
cid INT unsigned NOT NULL DEFAULT 0,
c1 VARCHAR(50) NOT NULL DEFAULT '',
c2 VARCHAR(50) NOT NULL DEFAULT '',
c3 VARCHAR(50) NOT NULL DEFAULT '',
c4 VARCHAR(50) NOT NULL DEFAULT '',
c5 VARCHAR(50) NOT NULL DEFAULT '',
c6 VARCHAR(50) NOT NULL DEFAULT '',
PRIMARY KEY(id),
INDEX idx$cid(cid)
);
INSERT INTO t VALUES(
NULL,
FLOOR(RAND() * 1000000),
REPEAT('a', 50),
REPEAT('a', 50),
REPEAT('a', 50),
REPEAT('a', 50),
REPEAT('a', 50),
REPEAT('a', 50)
);
-- 重复执行
INSERT INTO t
SELECT NULL,
FLOOR(RAND() * 1000000),
c1,
c2,
c3,
c4,
c5,
c6
FROM t;

下面是具体的实验过程

  • 使用IN查询连续的数

SELECT *
FROM t
WHERE cid IN(955555, 955556, 955557, 955558, 955559);
+---------+--------+-----------------------------------
| id | cid | c1
+---------+--------+-----------------------------------
| 319330 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
| 1885293 | 955555 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
| ......
| 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
| 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+---------+--------+-----------------------------------
41 rows in set (0.15 sec)

  • 使用IN查询不连续的数

SELECT *
FROM t
WHERE cid IN(1, 5000, 50000, 500000, 955559);
+---------+--------+-----------------------------------
| id | cid | c1
+---------+--------+-----------------------------------
| 1 | 341702 | 1 | aaaaaaaaaaaaaaaaaaaaaaaaa
| 1 | 1045176 | 1 | aaaaaaaaaaaaaaaaaaaaaaaaa
......
| 955559 | 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa
| 955559 | 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa
+--------+---------+--------+--------------------------
41 rows in set (4.34 sec)

  • 使用UNION优化

SELECT *
FROM (
SELECT 1 AS cid UNION ALL
SELECT 5000 UNION ALL
SELECT 50000 UNION ALL
SELECT 500000 UNION ALL
SELECT 955559
) AS tmp, t
WHERE tmp.cid = t.cid;
+---------+--------+-----------------------------------
| id | cid | c1
+---------+--------+-----------------------------------
| 1 | 341702 | 1 | aaaaaaaaaaaaaaaaaaaaaaaaa
| 1 | 1045176 | 1 | aaaaaaaaaaaaaaaaaaaaaaaaa
......
| 955559 | 8733757 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa
| 955559 | 8796305 | 955559 | aaaaaaaaaaaaaaaaaaaaaaaaa
+--------+---------+--------+--------------------------
41 rows in set (0.01 sec)

从上面可以看出上面使用UNION的方法生成一个临时表作为关联的主表。

拓展

要是MySQL有只带的一个行转列的函数那就完美了。这样我们就可以不用使用UNION了。

SELECT 1, 5000, 50000, 500000, 955559;
+---+------+-------+--------+--------+
| 1 | 5000 | 50000 | 500000 | 955559 |
+---+------+-------+--------+--------+
| 1 | 5000 | 50000 | 500000 | 955559 |
+---+------+-------+--------+--------+
1 row in set (0.00 sec)
变成以下
SELECT row_to_col(1, 5000, 50000, 500000, 955559);
+--------+
| id |
+--------+
| 1 |
| 5000 |
| 50000 |
| 500000 |
| 955559 |
+--------+

要是能像上面就太棒了简直。

  • 原文来自:http://www.ttlsa.com/mysql/mysql-sql-performance-let-you-head-big-open/
  • 本文地址:https://www.linuxprobe.com/special-optimize-sql.html

特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。

Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.

相关推荐
热点推荐
2003年,张柏芝和陈小春同游曼谷,结果两人被媒体拍摄下来

2003年,张柏芝和陈小春同游曼谷,结果两人被媒体拍摄下来

小白兔趣闻
2024-03-28 20:32:17
北京楼市定海神针,北京西城区突破120000元,东城区突破100000元

北京楼市定海神针,北京西城区突破120000元,东城区突破100000元

有事问彭叔
2024-04-27 10:53:51
美媒:哈登的作用在季后赛显现了,祖巴茨的短板得到明显改善

美媒:哈登的作用在季后赛显现了,祖巴茨的短板得到明显改善

大漠风光
2024-04-27 06:44:08
比亚迪太狠了!强硬拒绝意大利建厂邀请:先把欠中国的70亿还上

比亚迪太狠了!强硬拒绝意大利建厂邀请:先把欠中国的70亿还上

兵国大事
2024-04-26 15:26:28
投靠古天乐也没用!baby为复出走极端,剪超短发cos华晨宇引群嘲

投靠古天乐也没用!baby为复出走极端,剪超短发cos华晨宇引群嘲

八卦王者
2024-04-27 13:58:51
按摩时,足浴技师问你“之前来过么?”可能在暗示你,别不当回事

按摩时,足浴技师问你“之前来过么?”可能在暗示你,别不当回事

毒舌混知所
2024-03-30 07:50:03
又要引援了?广东有望完成“史诗级”补强,杜锋要押宝“双王炸”

又要引援了?广东有望完成“史诗级”补强,杜锋要押宝“双王炸”

绯雨儿
2024-04-27 00:20:40
向太豪宅摔伤,大腿骨裂手术5小时,本人曝惊险过程,言论惹争议

向太豪宅摔伤,大腿骨裂手术5小时,本人曝惊险过程,言论惹争议

古希腊掌管松饼的神
2024-04-26 17:30:04
问界M9海内外拿下10万大定!单车达600亿,华为吃掉豪车大块蛋糕

问界M9海内外拿下10万大定!单车达600亿,华为吃掉豪车大块蛋糕

资本百科
2024-04-26 23:11:31
大兴机场一旅客遗失水杯里竟放着3块玉器 民警帮助找回

大兴机场一旅客遗失水杯里竟放着3块玉器 民警帮助找回

北青网-北京青年报
2024-04-26 18:49:03
上门按摩师:假装盲人后,富家母女在我面前展现鲜为人知的一面

上门按摩师:假装盲人后,富家母女在我面前展现鲜为人知的一面

茶馆说书人
2023-09-08 11:46:14
两性关系:六十岁后,夫妻哪一方更会想有夫妻生活!

两性关系:六十岁后,夫妻哪一方更会想有夫妻生活!

社会潜伏者
2024-04-27 04:53:51
金晨作为一名实力演员,纹纹身真的合适吗?

金晨作为一名实力演员,纹纹身真的合适吗?

娱乐八卦木木子
2024-04-27 02:29:45
回顾《古惑仔》真实夫妻,吴志雄娶小结巴原型,大天二妻子旺夫相

回顾《古惑仔》真实夫妻,吴志雄娶小结巴原型,大天二妻子旺夫相

娱乐圈酸柠檬
2024-04-26 14:53:29
央视公开宣布,歼35已上舰,双发WS19全球第一,战力远超美军F35

央视公开宣布,歼35已上舰,双发WS19全球第一,战力远超美军F35

胖福的小木屋
2024-04-26 23:48:35
中国第一批“上环”的女性,医生直言:30年了,难逃出这4种结局

中国第一批“上环”的女性,医生直言:30年了,难逃出这4种结局

元芳
2024-04-27 14:16:02
法网公布三大新闻:纳达尔不享受种子待遇,球场盖顶夜场依旧!

法网公布三大新闻:纳达尔不享受种子待遇,球场盖顶夜场依旧!

网球之家
2024-04-27 13:06:39
卡米拉:后悔了哭了,查尔斯的遗产全留威廉王子,连哈里都没分。

卡米拉:后悔了哭了,查尔斯的遗产全留威廉王子,连哈里都没分。

奇趣横生娱乐q
2024-04-26 19:22:28
一个人身上最了不起的能力:两个字

一个人身上最了不起的能力:两个字

知和大叔
2024-04-25 23:42:00
杭州孤男寡女爬山真相大白,同学关系,女子擦伤的地方很清白!

杭州孤男寡女爬山真相大白,同学关系,女子擦伤的地方很清白!

小怪吃美食
2024-04-27 13:56:30
2024-04-27 16:12:49
孙有匪
孙有匪
科技
1595文章数 2009关注度
往期回顾 全部

科技要闻

特斯拉这款车型刚上市几天,就上调价格

头条要闻

43岁抗癌网红去世:曾是医生 如今留下2名幼子

头条要闻

43岁抗癌网红去世:曾是医生 如今留下2名幼子

体育要闻

时代要落幕了?詹姆斯杜兰特陷0-3绝境

娱乐要闻

金靖回应不官宣恋情结婚的原因

财经要闻

北京房价回到2016年

汽车要闻

5月上市/智能化丰富 海狮 07EV正式到店

态度原创

数码
健康
游戏
公开课
军事航空

数码要闻

苹果已停止升级 Mac 起步内存,库克更看重优化软硬件集成度

这2种水果可降低高血压死亡风险

《沙漠大冒险》有卡顿问题 PC版限制帧率可略微改善

公开课

睡前进食会让你发胖吗?

军事要闻

佩洛西称俄在支持"挺巴"的美国反战学生 扎哈罗娃回应

无障碍浏览 进入关怀版