1 个查询，用于选择同一表中不同类别中的多行-解网

问：

我有一个 PHP 脚本，它使用 4 个单独的查询从一个 mysql 表中读取最后 25 行（总共 100 行）。

例：

SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat1' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat1' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat2' ORDER BY datetime DESC LIMIT 25
SELECT * FROM table WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat2' ORDER BY datetime DESC LIMIT 25

我使用 4 查询，因为我必须读取不同的记录，基于：名称和类别，两者都是索引。Datetime 也是一个索引。

但是这种方式很慢（表大约是60MB，250K行）。我想知道这是否可以通过 1 个查询以更快的方式完成。

编辑mysql表结构

CREATE TABLE IF NOT EXISTS table (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `category` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `data1` double NOT NULL,
  `data2` double NOT NULL,
  `data3` double NOT NULL,
  #....
  `data50` double NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datetime` (`datetime`),
  KEY `category` (`category`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mysql select 查询优化

如果它很慢，它不应该放在这么小的桌子上，很可能是由于缺乏适当的索引（尝试）。向我们显示查询的语句和输出。您可以将查询放在一起或将子句更改为类似，但显然您的排序和限制将超过组合子句，而不是每个（名称、类别）组合。(category, name, datetime)CREATE TABLEEXPLAINUNION ALLWHEREWHERE datetime <= 'xxx' AND name IN ('john', 'doe') AND category IN ('cat1', 'cat2')WHERE

0赞 User12345 9/25/2023

运行此查询时花费了多少时间？1秒？5秒？

0赞 ipel 9/25/2023

@P.Salmon 是的，我需要选择总共 100 行

0赞 ipel 9/25/2023

@user1191247类别、名称、日期时间和索引。我添加了表结构

答：

1赞 user1191247 9/25/2023 #1

如果要一起运行查询，可以使用 UNION ALL 将查询结果组合在一起：

(
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat1'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat1'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'john' AND category = 'cat2'
    ORDER BY datetime DESC
    LIMIT 25
) UNION ALL (
    SELECT *
    FROM `table`
    WHERE datetime <= 'xxx' AND name = 'doe' AND category = 'cat2'
    ORDER BY datetime DESC
    LIMIT 25
)

请注意，由于 and 子句，各个查询需要括在括号中。ORDER BYLIMIT

添加复合索引：

ALTER TABLE `table` ADD INDEX (category, name, datetime);

您可以删除单列索引，因为它是此新索引中的第一列。category

create table t(id int,category varchar(4),name varchar(20));
insert into t values
(1,'cat1','john'),(2,'cat1','john'),(3,'cat1','john'),(20,'cat1','john'),(30,'cat1','john'),
(4,'cat2','john'),(5,'cat2','john'),(6,'cat2','john'),(50,'cat2','john'),
(7,'cat1','doe'),(8,'cat1','doe'),(9,'cat1','doe'),
(10,'cat2','doe'),(11,'cat2','doe'),(12,'cat2','doe'),(120,'cat2','doe');

with cte as(
  select *,
  row_number() over (partition by name,category order by id desc) rn
  from t
  where id < 20 and name in('john','doe') and category in('cat1','cat2'))
select * from cte 
  where rn < 3
  order by name,category;

https://dbfiddle.uk/05tdde0k

您需要自己测试这是否比您拥有的更快

上一个：WordPress （WooCommerce）：使用 SELECT 获取 30 天order_total（获取总收入金额）

下一个：如何在MySQL中对同一表的多列进行INNER JOIN

1 个查询，用于选择同一表中不同类别中的多行

1 query to select multiple rows in the same table but in different category

评论

评论