sql查询优化选择或内部连接

6fe3ivhb  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(274)

你好，我有一个问题，对我的一个要求，这是一个很长的时间缓慢。我测试了使用索引，没有结果。有人建议我将inner join替换为slect，但我不知道如何处理同一个表上的不同操作。
table：

CREATE TABLE `cryptos` (
  `uuid` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `shortname` varchar(255) DEFAULT NULL,
  [...]
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `cryptos_infos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
 [...]
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `cryptos_infos_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `eventcryptorelations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  [...]
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  `fk_eventid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  KEY `fk_eventid` (`fk_eventid`),
  CONSTRAINT `eventcryptorelations_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `eventcryptorelations_ibfk_2` FOREIGN KEY (`fk_eventid`) REFERENCES `events` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `events` (
  `uuid` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  [...]
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `github_activ_repos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  [...]
  `fk_githubid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_githubid` (`fk_githubid`),
  CONSTRAINT `github_activ_repos_ibfk_1` FOREIGN KEY (`fk_githubid`) REFERENCES `github_info_repos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `github_info_repos` (
  `uuid` char(36) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
  `idgit` int(11) DEFAULT NULL,
  [...]
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `github_info_repos_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `prices` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `open` double DEFAULT NULL,
  [...]
  `date` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `prices_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `trendsSearches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` datetime DEFAULT NULL,
  `formattedTime` varchar(255) DEFAULT NULL,
  `value` float DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `trendsSearches_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `twitter_activs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  [...]
  `fk_twitterid` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_twitterid` (`fk_twitterid`),
  CONSTRAINT `twitter_activs_ibfk_1` FOREIGN KEY (`fk_twitterid`) REFERENCES `twitter_infos` (`idtwitter`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `twitter_infos` (
  `idtwitter` double NOT NULL,
 [...]
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`idtwitter`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `twitter_infos_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `volumes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `volumefrom` double DEFAULT NULL,
  [...]
  `fk_cryptoid` char(36) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cryptoid` (`fk_cryptoid`),
  CONSTRAINT `volumes_ibfk_1` FOREIGN KEY (`fk_cryptoid`) REFERENCES `cryptos` (`uuid`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

为了更好地理解，我缩小了table。
查询说明：
我需要按照cryptos\u info表中的'rank'字段对前100个条目进行排序。最新的入门价和24小时前的价格。使用其他表上的其他信息，例如卷上的条目、github表上的条目。。。。
查询：

select cryptos_infos.rank, cryptos.shortname, cryptos.imageurl, cryptos_infos.coinminde,
(SELECT open FROM prices WHERE prices.fk_cryptoid = cryptos.uuid and prices.date LIKE '2018-09-16%' order by prices.date DESC limit 1) as priceone,
 (SELECT open FROM prices WHERE prices.fk_cryptoid = cryptos.uuid and prices.date LIKE '2018-09-16%' order by prices.date DESC limit 1) as pricetwo , 
(SELECT P.open FROM prices P WHERE P.fk_cryptoid = cryptos.uuid order by P.createdAt DESC limit 1) as open,
 (select count(E.hot) from events E where E.uuid in (select EC.fk_eventid from eventcryptorelations EC where EC.fk_cryptoid = cryptos.uuid) and E.hot = 1) as hot,
 (SELECT value FROM trendsSearches WHERE trendsSearches.fk_cryptoid = cryptos.uuid order by trendsSearches.time DESC limit 1) as trenone,
 (SELECT value FROM trendsSearches WHERE trendsSearches.fk_cryptoid = cryptos.uuid  order by trendsSearches.time DESC LIMIT 1,1) as trentwo,
 (select GAR.stars from github_activ_repos GAR where GAR.fk_githubid = ( select uuid from github_info_repos GIR, github_activ_repos GR where GIR.fk_cryptoid = cryptos.uuid and GIR.uuid = GR.fk_githubid order by GR.stars DESC limit 1) order by GAR.time DESC limit 1) as gitone, 
(select GAR.stars from github_activ_repos GAR where GAR.fk_githubid = ( select uuid from github_info_repos GIR, github_activ_repos GR where GIR.fk_cryptoid = cryptos.uuid and GIR.uuid = GR.fk_githubid order by GR.stars DESC limit 1) order by GAR.time DESC limit 1,1) as gittwo,
 (SELECT abonnees FROM twitter_activs WHERE twitter_activs.fk_twitterid = (select idtwitter from twitter_infos where twitter_infos.fk_cryptoid = cryptos.uuid) and twitter_activs.createdAt LIKE '2018-09-16%' order by twitter_activs.createdAt DESC limit 1) as abotone, 
(SELECT abonnees FROM twitter_activs WHERE twitter_activs.fk_twitterid = (select idtwitter from twitter_infos where twitter_infos.fk_cryptoid = cryptos.uuid) and twitter_activs.createdAt LIKE '2018-09-16%' order by twitter_activs.createdAt DESC limit 1) as abottwo 
from cryptos, cryptos_infos where cryptos.uuid = cryptos_infos.fk_cryptoid and cryptos_infos.rank is not null  order by cryptos_infos.rank ASC limit 100;

信息：所有请求=30秒
请求-选择表上价格=1.23s
慢行问题所在的行：

(SELECT open FROM prices WHERE prices.fk_cryptoid = cryptos.uuid and prices.date LIKE '2018-09-23%' order by prices.date DESC limit 1) as priceone,
 (SELECT open FROM prices WHERE prices.fk_cryptoid = cryptos.uuid and prices.date LIKE '2018-09-23%' order by prices.date DESC limit 1) as pricetwo , 
(SELECT P.open FROM prices P WHERE P.fk_cryptoid = cryptos.uuid order by P.createdAt DESC limit 1) as open,
sqlmysql

1条答案

按热度按时间
bd1hkmkf

bd1hkmkf1#

首先，不要使用 like 日期。取而代之的是：

(SELECT open FROM prices p WHERE p.fk_cryptoid = cryptos.uuid and p.date >= '2018-09-23' AND p.date < '2018-09-24' order by p.date DESC limit 1) as priceone,
(SELECT open FROM prices p WHERE p.fk_cryptoid = cryptos.uuid and p.date >= '2018-09-23' AND p.date < '2018-09-24' order by p.date DESC limit 1) as pricetwo , 
(SELECT P.open FROM prices P WHERE P.fk_cryptoid = cryptos.uuid order by P.createdAt DESC limit 1) as open,

为此，您可以在上尝试索引 prices(fk_cryptoid, p.date, open) 以及 prices(fk_cryptoid, createdAt, open) .
你可能需要更激烈一点。目标是为子查询使用索引。我不确定mysql是否足够聪明，可以在这些情况下做到这一点。
您可能需要更改现有的 date 列仅为日期，并添加一个新的datetime列，其中包含日期/时间（或仅包含时间列）。然后可以将子查询短语为：

(SELECT open FROM prices p WHERE p.fk_cryptoid = cryptos.uuid and p.date = '2018-09-23' order by p.datetime DESC limit 1) as priceone,

你想要的索引已经打开了 (fk_cryptoid, date, datetime, open) .

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com