让你脑洞大开的MySQL优化技巧
来源:互联网 发布:四川农业大学网络教育 编辑:程序博客网 时间:2024/06/08 05:50
由于分库分表的原因,和开发规定了不能使用 表表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 tSELECT NULL, FLOOR(RAND() * 1000000), c1, c2, c3, c4, c5, c6FROM t;
下面是具体的实验过程
- 使用IN查询连续的数
SELECT *FROM tWHERE 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 tWHERE 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, tWHERE 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.linuxprobe.com/special-optimize-sql.html
免费提供最新Linux技术教程书籍,为开源技术爱好者努力做得更多更好:http://www.linuxprobe.com/thread
0 0
- 让你脑洞大开的MySQL优化技巧
- 让你脑洞大开的MySQL优化技巧
- 让你脑洞大开的MySQL优化技巧
- 让你脑洞大开的MySQL优化技巧
- MySQL数据库表结构设计优化技巧总结 让你的表结构更加合理
- 那些可能被你忽略的MySQL优化技巧
- 优化网站页面小技巧—让搜索引擎更容易找到你的页面! 同行广场
- 【MySQL】10条SQL优化语句,让你的MySQL数据库跑得更快!
- 【MySQL】10条SQL优化语句,让你的MySQL数据库跑得更快!
- mysql最好的优化技巧
- MySQL最好的优化技巧
- 让你电脑速度提高的技巧
- 10个让你早起的技巧
- 10个让你早起的技巧
- 10个让你早起的技巧
- 10个让你早起的技巧
- 10个让你早起的技巧
- CSS技巧让你的网站更上一层楼
- Unix高级编程:获取文件元数据、文件夹操作
- VTK修炼之道15:图像处理_显示(vtkImageViewer2 & vtkImageActor)
- hihoCoder 1403 后缀数组 重复旋律
- vlc打开h264参数配置
- SICP 2.35 树叶子节点的数量
- 让你脑洞大开的MySQL优化技巧
- 算法竞赛入门经典 习题3-2 单词的长度(word)
- Color 递归
- Unix高级编程:库函数与系统调用函数区别、文件锁、进程基础
- mac修改host文件,让你的mac轻松上google
- Java学习之泛型
- github pages + 域名和外网ip访问本地服务器
- bzoj1811mea(不等式)
- RxJava