我不知道如何管理 mysql 数据库表索引,并且我尝试实现它,但我不确定如何管理这个广泛的 MySQL 数据库表的索引(将在我最后完成,但只需要一些与此表有关的信息)。
我使用不同的查询并根据搜索需要使用此字段:id
、post_id
、imdbRating
、genres
、year
、cast
和posterURLs
。
较慢的查询可以清楚地了解我为什么要使用索引。
/* QUERY 1 using for search filters, filter with tr.tax_id, pd.type or mo.streamingInfo (slower) */
SELECT pd.id, pd.title, pd.slug, mo.posterURLs, mo.imdbRating
FROM posts pd
INNER JOIN movies mo ON (pd.id = mo.post_id)
INNER JOIN term_relation tr ON (pd.id = tr.obj_id) /* Used with filter (skippable) */
WHERE pd.type = 'movies' /* Used with filters (skippable) */
AND pd.status = 1
AND mo.type = "movie"
AND mo.streamingInfo REGEXP "jio|zee5|voot" /* Used with filters (skippable) */
AND tr.tax_id IN (123456,78910,111213) /* Used with filter (skippable) */
GROUP BY pd.id
ORDER BY mo.year DESC, pd.date DESC /* Can be mo.year DESC or mo.imdbRating ASC/DESC */
LIMIT 0, 24;
/* QUERY 2 only from movie table (slower) */
SELECT * FROM movies WHERE post_id='123456'; /* Resolved with INDEX(post_id) */
SELECT post_id FROM movies WHERE cast REGEXP "(Cast Title)" AND post_id <> 123456 AND post_id <> 0 ORDER BY year DESC LIMIT 12
我已经在表上有一个复合索引posts
,它使用这些索引idx_type_status_date: (type, status, date, id)
及其各自的顺序。但是,我想知道是否也可以在电影表上创建单独的索引?
假设
id
唯一指定该行,那么您应该有对于数字(和日期等)列,只需
并使用类似的东西
对于
TEXT
字段,请使用FULLTEXT
:和
可能有更好的方法可以做到
genre
。有多少种不同的值?您只需要测试一项吗?或者很多,比如顺便说一句,这是什么
age
意思?如果它指的是电影上映多少年前,那么这是存储信息的糟糕方法,因为所有条目每年都会发生变化。相反,在 上进行测试year
。某些查询可能涉及多个测试。如果您有一百万部电影,某些此类查询可能会很慢。返回一些特定的、缓慢的查询以进一步细化——例如“复合”索引和/或组合列
FULLTEXT
。更多 (在查询等之后,添加到问题中)
ORDER BY mo.year DESC, pd.date DESC
-- 不能使用索引,因为它涉及两个表。WHERE ... tr.term_id IN (...)
将LEFT JOIN
变为INNER JOIN
. (这会让读者感到困惑。)如果“流派检查”是可选的,请删除JOIN
和AND .. IN
。也就是说,根据用户提供的参数动态构建查询。GROUP BY pd.id
意味着您可以以某种方式从连接中获取多行。这是“爆炸-内爆综合症”,可以通过以下方式避免(并加速)(抱歉,但我在你的询问中迷失了方向,所以我犹豫是否要为你完整地拼写出来。)
可以
EXISTS
使用INNER JOIN
. 哪种方式可能并不重要。(或者它可能会工作得更好。)这可能会有所帮助:
如果
pd.type
始终跟踪mo.type
,则可能可以避免额外的检查。更多 2
一个综合索引
tr
:INDEX(post_id, term_id)
。此外,如果该表只是一个多对多映射,则将其丢弃id
并具有:更多讨论: Index Cookbook 它讨论了如何基于查询设计索引。
折腾后EXISTS
需要
和
可能会受益于
或者可能需要
吉图布
查询 1:如果没有任何过滤器存在并且两种类型是冗余的,则“INDEX(type, status)”可能有用。(列的任一顺序)。
查询2:
INDEX(post_id)
查询 3:如果
FULLTEXT
和MATCH
有效,那就是正确的方法。查询 4:我没有看到
JOIN
to 表sd
阻碍进一步优化的因素:
ORDER BY
提到两个表LIMIT
。REGEXP
TEXT
在内部或上过滤JSON
。无论您使用什么 GUI 来管理数据库,都应该有某种方法来添加索引。
使用SQL
创建唯一索引
允许重复
如何决定对哪些列建立索引
id
可能是任何名称中包含该内容的列。尽管帖子
@Rick James
给出了快速答案。但是,直接跳转到实现 INDEX(尤其是新手)可能很危险,并且会显着降低插入性能。
~
Indexes are double-ended sword, should be used with caution.
您可以了解更多信息: https: //use-the-index-luke.com/