查询速度慢,当按

下面的查询在1.000.000行的表上运行需要20多秒。

SELECT max(listing.category) AS category,
    max(listing.subcategory) AS subcategory,
    max(listing.created_at) AS date,
    listing.keystring AS listing_keystring
FROM listing
WHERE listing.privacy > 10
GROUP BY listing.keystring
ORDER BY date
LIMIT 10

如果我去掉 “order by “部分,查询运行时间不到1秒。

根据解释声明:EXTRA说 “使用where;使用临时;使用filesort “Type说 “Index “Key说 “listing_idx_keystring_category”

这里的创建表语句。

 'CREATE TABLE `listing` (`id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `bw` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subcategory` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `place` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `time` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_hidden` tinyint(1) NOT NULL DEFAULT ''0'',
  `privacy` int(11) NOT NULL DEFAULT ''20'',
  `keystring` varchar(255) COLLATE utf8mb4_unicode_ci
      GENERATED ALWAYS AS (concat(`bw`,`category`,ifnull(`subcategory`,''''),ifnull(`place`,''''),ifnull(`time`,''''))) STORED,
  PRIMARY KEY (`id`),
  KEY `listing_idx_privacy` (`privacy`),
  KEY `listing_idx_keystring_category` (`keystring`,`category`),
  KEY `idx_listing_created_at_privacy` (`created_at`,`privacy`),
  KEY `idx_listing_created_at` (`created_at`),
  KEY `idx_listing_bw` (`bw`),
  KEY `idx_listing_subcategory` (`subcategory`),
  KEY `idx_listing_category` (`category`),
  KEY `idx_listing_place` (`place`),
  KEY `idx_listing_time` (`time`)
) ENGINE=InnoDB AUTO_INCREMENT=1500001
      DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

有没有什么办法可以提高速度?

我试过添加不同的索引组合,但没有效果……

解决方案:

对于这个查询。

select 
    max(category) as category, 
    max(subcategory) as subcategory,
    max(created_at) as date, 
    keystring as listing_keystring
from listing
where privacy > 10
group by keystring
order by date
limit 10

我会从以下索引开始: (privacy, keystring). 这在以下情况下是匹配的 where 谓词和 group by 列。你还没有这个索引,所以先试试。

你可能也想试试。(privacy, keystring, category, subcategory, created_at);那是相当多的列,所以我不确定MySQL是否真的会考虑它,但它肯定值得一试;在最好的情况下,MySQL会把它作为一个独立的索引。覆盖指数,只看索引就执行整个查询。

需要注意的是,盲目地在很多列组合上创建索引是没有用的。一方面,需要根据每个查询来调整索引策略。另一方面,更多的索引意味着更多的存储量,也意味着数据库每一次DML操作的工作量(update, insert, delete),但却没有任何好处。select 查询。除非你有很好的理由保留每一个现有的索引,否则我建议放弃它们。

给TA打赏
共{{data.count}}人
人已打赏
未分类

如何填写表格的其他部分

2022-9-28 15:24:19

未分类

构建docker镜像时如何修改 "core_pattern"?

2022-9-28 15:24:21

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索