MySQL SQL优化-让你脑洞大开
admin- 首页
- 证书相关
- MySQL SQL优化-让你脑洞大开
MySQL SQL优化-让你脑洞大开
发布日期：2017-04-27由于分库分表的原因，和开发规定了不能使用 表表JOIN 语句。因此，我们要将 JOIN 语句的转化成使用 IN 来做。如现在有 表 A(a_id, c_a)c_a有普通索引,表 B(b_id, c_a) 这两个表要关联, 应该转化为以下步骤处理:
- 先查询B中的 a_id
| 1 | SELECT c_a FROM B WHERE xxx ; |
- 使用 IN 查询 A 表
| 1 | SELECT a_id , . . . FROM A WHERE c_a IN (在 1 中查出来的 c_a ) |
场景
现在表的数据量有 800万。
一般的使用语句是：
| 1 | SELECT * FROM A WHERE c_a IN ( 955555 , 955556 , 955557 , 955558 , 955559 ) ; |
上面语句会执行的很快，知道使用 explain 的都明白这样一般都是会使用索引的，并且是所有范围扫描。
MySQL不会从 1 开始 扫描 800万，而是从555555 扫描到 555559(只要扫描5行数据)。
在一般情况下是没有什么问题的。但是如果 IN 里面的数据是不连续的就有很大问题了。
创建表结构语句
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 | 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查询连续的数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | 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查询不连续的数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 | 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优化
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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了。
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | 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 | + — — — — + |
文章转载来自：trustauth.cn
上一篇：nginx日志request_time 和upstream_response_time区别
下一篇：SEO常用辅助工具整合
相关新闻
- SSL证书对网站访问速度有影响吗
- 个人隐私数据泄露有哪些危害？如何预防？
- 部署SSL证书有哪些常见的错误
- 国际证书、国产证书和国密证书
- 游戏开发为什么离不开代码签名？
- 僵尸网络攻击手法与防范方式
- SSL证书助力保障网络数据安全
- 网站加密与不加密区别
- SSL证书有哪些类型和价格差异
- ca机构颁发的证书包括那些内容呢？
