我如何优化sql的价格和时间戳字段?

Question

checking explain (mysqlnd 8.1):

explain analyze       SELECT *
FROM `ts_products`  WHERE `ts_products`.`creator_id` = '2' AND 
`ts_products`.`title` like '%no%'     AND 
`ts_products`.`status` = 'A'     AND 
ts_products.published_at >= '2022-04-01' AND 
ts_products.published_at < '2022-04-21' AND 
ts_products.sale_price > '261' 
ORDER BY `sale_price` asc
on table :
CREATE TABLE `ts_products` (
`id` bigint unsigned NOT NULL AUTO_INCREMENT,
`creator_id` bigint unsigned NOT NULL,
`title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`status` enum('D','P','A','I') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'D' COMMENT ' D => Draft, P=>Pending Review, A=>Active, I=>Inactive',
`slug` varchar(260) COLLATE utf8mb4_unicode_ci NOT NULL,
`sku` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`regular_price` decimal(9,2) DEFAULT NULL,
`sale_price` decimal(9,2) DEFAULT NULL,
`in_stock` tinyint(1) NOT NULL DEFAULT '0',
`stock_qty` mediumint unsigned NOT NULL DEFAULT '0',
`has_discount_price` tinyint(1) NOT NULL DEFAULT '0',
`is_featured` tinyint(1) NOT NULL DEFAULT '0',
`short_description` mediumtext COLLATE utf8mb4_unicode_ci,
`description` longtext COLLATE utf8mb4_unicode_ci,
`published_at` datetime DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ts_products_slug_index` (`slug`),
UNIQUE KEY `ts_products_sku_index` (`sku`),
UNIQUE KEY `ts_products_creator_id_3fields_index` (`creator_id`,`sale_price`,`status`,`title`,`published_at`),
KEY `ts_products_status_title_regular_price_published_at_index` (`status`,`title`,`regular_price`,`published_at`),
KEY `ts_products_status_5fields_index` (`status`,`in_stock`,`has_discount_price`,`is_featured`,`title`,`published_at`,`stock_qty`,`sale_price`),
CONSTRAINT `ts_products_creator_id_foreign` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=801 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

我看到ts_products_creator_id_3fields_index索引被选中，看起来很好，但是看看输出:

-> Index range scan on ts_products using ts_products_creator_id_3fields_index over (creator_id = 2 AND 261.00 < sale_price), with index condition: ((ts_products.creator_id = 2) and (ts_products.title like '%no%') and (ts_products.`status` = 'A') and (ts_products.published_at >= TIMESTAMP'2022-04-01 00:00:00') and (ts_products.published_at < TIMESTAMP'2022-04-21 00:00:00') and (ts_products.sale_price > 261.00))  (cost=20.06 rows=44) (actual time=0.070..0.179 rows=2 loops=1)

我想知道如何标志">="/";& lt;"one_answers"/";;工作的地方。据我所知，只有'='适用于索引。在ts_products_creator_id_3fields_index索引中添加sale_price和publishhed_at是否有意义?

如何优化这个请求?

谢谢!

Answer 1

这可能有帮助:

INDEX(creator_id, status, sale_price)

有一个列是不可索引的(title like '%no%')，因为前导通配符，还有2个范围:published_at和sale_price。我选择后者，因为它可能在WHERE和ORDER BY中都有用。

请提供EXPLAIN.

如Index Cookbook中所讨论的，由=测试的列应该在复合索引中首先出现，然后是一个'range'列。