加快阅读速度的索引| mysql

分享于2022年07月17日 blob indexing mysql sql sql-like 问答
【问题标题】:加快阅读速度的索引| mysql(indexing for speeding up reading | mysql)
【发布时间】:2022-01-25 15:12:31
【问题描述】:

有人问我以下问题:

对于以下每个查询,设计一个或多个索引 假设表有足够的行会加快速度。对于每个 查询提交将索引添加到相关的 SQL 语句 别名中的表。

a. SELECT film_id, description FROM film WHERE description LIKE 'A B ';

b. SELECT film_id, description FROM film_text WHERE description LIKE 'A B ';

c。 SELECT f.film_id, f.title, a.last_update FROM film AS f INNER JOIN film_actor AS fa ON f.film_id = fa.film_id INNER JOIN actor AS a ON fa.actor_id = a.actor_id WHERE f.rating = 'G' AND a.last_update `>= '2006-02-15 04:34:33';

我尝试在描述列上创建索引,但收到错误代码 1770 : BLOB/TEXT column 'description' used in key specification without a key length

它是为此目的索引的正确列吗?如果是这样,我该如何解决这个问题?如果不是,索引的正确列是什么?

表格由

CREATE TABLE `film` (
 `film_id` smallint unsigned NOT NULL AUTO_INCREMENT,

 `title` varchar(255) NOT NULL,

 `description` text,

 `release_year` year DEFAULT NULL,

 `language_id` tinyint unsigned NOT NULL,

 `original_language_id` tinyint unsigned DEFAULT NULL,

 `rental_duration` tinyint unsigned NOT NULL DEFAULT ''3'',

 `rental_rate` decimal(4,2) NOT NULL DEFAULT ''4.99'',

 `length` smallint unsigned DEFAULT NULL,

 `replacement_cost` decimal(5,2) NOT NULL DEFAULT ''19.99'',

 `rating` enum(''G'',''PG'',''PG-13'',''R'',''NC-17'') DEFAULT ''G'',

 `special_features` set(''Trailers'',''Commentaries'',''Deleted Scenes'',''Behind the Scenes'') DEFAULT NULL,

 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

 PRIMARY KEY (`film_id`),

 KEY `idx_title` (`title`),

 KEY `idx_fk_language_id` (`language_id`),

 KEY `idx_fk_original_language_id` (`original_language_id`),

 CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE,

 CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb3

谢谢!

  • 问题不完整。请包括表格的定义。
  • 为什么不指定密钥长度?
  • @TheImpaler,已编辑,我还有什么需要说明的吗?
  • @Jov 创建具有 TEXT 数据类型(如 description )成员的索引时,数据库通常只索引它的第一个字符; MySQL 询问您要索引多少个 description 字符。例如,如果您想要基于前 50 个字符的索引,您可以这样做: create index ix1 on film (description(50)); 。出于性能原因,您不能索引整个事物。
  • @theImpaler 酷,非常感谢:)

【解决方案1】:

查询 3 有点棘手。

优化器可能会考虑是从 f.rating = 'G' 还是 a.last_update >= '2006-02-15 04:34:33' . It will decide based on limited knowledge about the distribution of data in the tables f and a` 开始。

如果它以 f 开头,例如当请求的评分出现在很少的行中时:

f:  INDEX(rating)
fa:  INDEX(film_id,  actor_id)
a:  INDEX(last_update)

如果以 a 开头,比如日期范围比较窄的时候:

a:  INDEX(last_update, actor_id)
fa:  INDEX(actor_id, film_id)
f:  INDEX(rating, film_id)

结合它们,我建议:

f:  INDEX(rating, film_id)       -- (handles both cases)
fa:  INDEX(film_id, actor_id)
fa:  INDEX(actor_id, film_id)    -- (yes both orderings)
a:  INDEX(last_update, actor_id)
a:  INDEX(actor_id, last_update) -- (yes both orderings)

更多讨论: http://mysql.rjweb.org/doc.php/index_cookbook_mysql