当前位置:首页 » 《随便一记》 » 正文

阿里云SQL优化挑战赛实战-190毫秒干到2毫秒

22 人参与  2022年11月18日 08:43  分类 : 《随便一记》  评论

点击全文阅读


使用一个多表查询的例子:

首先创建表:

CREATE DATABASE IF NOT EXISTS mysql_test_bruce DEFAULT CHARACTER SET UTF8;CREATE TABLE `a`(  `id` INT(11) NOT NULL AUTO_INCREMENT,  `seller_id` BIGINT(20) DEFAULT NULL,  `seller_name` VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8_BIN DEFAULT NULL,  `gmt_create` VARCHAR(30) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;CREATE TABLE `b`(  `id` INT(11) NOT NULL AUTO_INCREMENT,  `seller_name` VARCHAR(100) DEFAULT NULL,  `user_id` VARCHAR(50) DEFAULT NULL,  `user_name` VARCHAR(50) DEFAULT NULL,  `sales` BIGINT(20) DEFAULT NULL,  `gmt_create` VARCHAR(30) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;CREATE TABLE `c`(  `id` INT(11) NOT NULL AUTO_INCREMENT,  `user_id` VARCHAR(50) DEFAULT NULL,  `order_id` VARCHAR(100) DEFAULT NULL,  `state` BIGINT(20) DEFAULT NULL,  `gmt_create` VARCHAR(30) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=UTF8;

查询语句如下:

explain SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create; 

 查询花费了190ms

查看查询计划,发现都是用了全表扫描,显然是需要优化的。 

怎样优化呢?

1.既然是关联查询,那么需要先找到驱动表,小表驱动大表

对3张表分别进行查询,查看那张表得到的数据最少,将其做为驱动表

从结果来看,a和c表都可以作为驱动表,但是因为要使用a表的gmt_create作为查询条件,如果从索引的利用率来说,使用a表作为驱动表更合适

2.建立索引 

查询中没有用到索引,所以这里需要先建立索引

查询语句:

SELECT a.seller_id, a.seller_name, b.user_name, c.state FROM a,b,c WHERE
a.seller_name=b.seller_name
AND b.user_id = c.user_id AND c.user_id=17
AND a.gmt_create BETWEEN DATE_ADD(NOW(),INTERVAL - 600 MINUTE)
AND DATE_ADD(NOW(),INTERVAL 600 MINUTE )
ORDER BY a.gmt_create;

为了优化排序,给a表的gmt_create字段建立索引

b表的seller_name字段因为要与a表进行关联,所以是必不可少需要建立索引的

c表的user_id字段因为要与b表进行关联,所以也是要建立索引的

再次查询:

可以看到C表使用了user_id索引,但是a、b两表却没有用到索引,还是全表扫描,这是怎么回事呢?

再加一个参数extended去查看更加详细的执行计划:

通过上面执行计划的分析,发现问题如下:

主要是索引字段在使用的时候发生了隐式转换

采用如下解决方案:

再次执行sql语句:索引失效的问题被解决。

 以上就是一个完整的sql优化的思路,这种过程很少有人总结,希望能够对大家有帮助。


点击全文阅读


本文链接:http://zhangshiyu.com/post/48882.html

<< 上一篇 下一篇 >>

  • 评论(0)
  • 赞助本站

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。

最新文章

  • 他在永夜长眠/最后的吻是告别列表_他在永夜长眠/最后的吻是告别(苏娆时砚清)
  • 公主请开门,我是世子啊!隐藏剧情_秦浩白莲高分言情_小说后续在线阅读_无删减免费完结_
  • 最后的吻是告别/他在永夜长眠(苏娆时砚清)_最后的吻是告别/他在永夜长眠
  • 江晚棠裴砚心小说(海棠无香夜未眠)+前传(江晚棠裴砚心)阅读
  • 重生后,我拒绝入选英雄联盟名人堂全文+后续_女友荣誉沈重听书_小说后续在线阅读_无删减免费完结_
  • 他在永夜长眠+番外+后续(苏娆时砚清)全书免费_(苏娆时砚清)他在永夜长眠+番外+后续后续(苏娆时砚清)
  • 完结文拾云为笺焚风作别精彩欣赏列表_完结文拾云为笺焚风作别精彩欣赏(姜知月周景珩郁萌)
  • 拾云为笺,焚风作别免费姜知月周景珩郁萌完本_拾云为笺,焚风作别免费(姜知月周景珩郁萌)
  • 祁逢叫什么(全盛京盼我死,摄政王却教我反杀)小说全集阅读无广告(全盛京盼我死,摄政王却教我反杀)番外+续集
  • 褪色的极光(谢砚之沈嫣然)_褪色的极光
  • 被夫君和师父双重背叛后,我提剑杀疯了精选作品_师姐师傅楚楚可怜无广告_小说后续在线阅读_无删减免费完结_
  • 全书浏览拾云为笺焚风作别后续更新+番外(姜知月周景珩郁萌)_拾云为笺焚风作别后续更新+番外(姜知月周景珩郁萌)全书结局

    关于我们 | 我要投稿 | 免责申明

    Copyright © 2020-2022 ZhangShiYu.com Rights Reserved.豫ICP备2022013469号-1