我对即将要做的索引编制感到有些困惑。
首先，我使用4列索引，如下所示：
索引名称-advanced_query
索引中将使用列-标题，类别1，类别2，类别3
索引代码
ALTER TABLE table_name ADD INDEX advanced_query (`title`, `cat_1`, `cat_2`, `cat_3`, `date_posted`)
好的，这就是它的工作方式（据我了解）：
TL; DR
因此，在该索引中，仅 cat_3查询 不会从中受益，对吧？谢谢！
问/答
我在做什么查询？搜索帖子（标题和3个不同的类别）
桌子的大小是多少？少于两千行
表的结构？
CREATE TABLE `post_lists` ( `id` int(100) NOT NULL AUTO_INCREMENT, `users_id` varchar(100) NOT NULL, `code` varchar(255) NOT NULL, `date_posted` datetime NOT NULL, `date_updated` datetime NOT NULL, `title` varchar(255) NOT NULL, `cat_1` varchar(255) NOT NULL, `cat_3_code` varchar(255) NOT NULL, `details` varchar(10000) NOT NULL, `cat_2` varchar(255) NOT NULL, `cat_3` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`), KEY `date_posted` (`date_posted`), KEY `code` (`urlcode`), KEY `users_id_date_posted` (`users_id`,`date_posted`), KEY `title_date_posted` (`title`,`date_posted`), KEY `cat_1_date_posted` (`cat_1`,`date_posted`) ) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1
该表将使用多少次？大多数时候。这是 高级搜索 功能，因此不仅像 基本搜索 那样频繁。
这就是我实际上将如何使用索引的方式。
示例表
title | cat_1 | cat_2 | cat_3 | date_posted
我的查询很简单：
SELECT * FROM tbl_name WHERE title LIKE %title% ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' and cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_3 = 'cat_3' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_2 = 'cat_2' ORDER BY date_posted DESC
SELECT * FROM tbl_name WHERE cat_3 = 'cat_3' ORDER BY date_posted DESC
我该如何查询？
编辑
嗨，我阅读并搜索了全文搜索，我正在考虑使用它（在基本搜索中）而不是LIKE %wildcard%将其应用到title和上details，我的问题是我希望它们进行排序ORDER BY date_posted DESC，因此我应该date_posted在全文搜索中添加或创建一个单独的索引？
LIKE %wildcard%
title
details
ORDER BY date_posted DESC
date_posted
我认为对您的问题的“答案”有点复杂，您对使用索引的假设并不总是正确的。
简短的答案是：“取决于”。
实际上，索引的使用取决于几个因素：表中的记录数，索引结构，请求的字段，查询中的条件，统计信息。
1）记录数：如果很小，则db引擎可能会决定不使用索引（尤其是如果您将SELECT的SELECT *写入SELECT –表中的几列不在索引-中）。
如果仅选择索引中的部分或全部列，则可以使用索引（也不考虑WHERE条件）。
2）索引结构：正如您所指出的，它是相关的。Morevore有两种可以“使用”索引的主要方法：扫描和查找。寻求是最有效的。在大多数情况下，您是否会以编写顺序查找索引中的列：从表的标题中选择标题，例如“ ABC％”）。注意：如果您写了LIKE’％ABC％’，则它不能进行搜索，而只能进行扫描。（扫描表示db必须从头到尾查找整个索引，而查找时他将直接转到相关页面，因为您将使用姓氏在电话簿中查找某人的电话号码）。
3）要求的字段：您应该考虑如果您编写SELECT *（如上所述，db引擎可能会决定使用全表扫描）
4）查询条件。
5）统计信息：db引擎写入有关数据和索引（记录数，结构等）的统计信息。如果它们未更新，则可能以“错误”方式使用或不使用索引。
-----更新：简单（不详尽…）演示
实际上（使用少量数据，我不得不注释您的KEY’title_date_posted’以便在某些情况下使用“ advanced_query”索引：否则，它似乎尝试使用它；正如我告诉您的那样，db引擎会做出内部决策使用的索引）。
在rextester.com上完成的测试：
##DROP TABLE post_lists; CREATE TABLE `post_lists` ( `id` int(100) NOT NULL AUTO_INCREMENT, `users_id` varchar(100) NOT NULL, `code` varchar(255) NOT NULL, `date_posted` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `date_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `title` varchar(255) NOT NULL, `cat_1` varchar(255) NOT NULL, `cat_3_code` varchar(255) NOT NULL, `details` varchar(10000) NULL, `cat_2` varchar(255) NOT NULL, `cat_3` varchar(255) NOT NULL, UNIQUE KEY `id` (`id`) , KEY `date_posted` (`date_posted`) , KEY `code` (`code`) , KEY `users_id_date_posted` (`users_id`,`date_posted`) ##, KEY `title_date_posted` (`title`,`date_posted`) , KEY `cat_1_date_posted` (`cat_1`,`date_posted`) ) DEFAULT CHARSET=latin1; ALTER TABLE post_lists ADD INDEX advanced_query (`title`, `cat_1`, `cat_2`, `cat_3`, `date_posted`); INSERT INTO post_lists (users_id, code, title, cat_1, cat_3_code, details, cat_2, cat_3) VALUES ('123', 'ABC', 'TITLE1', '001','C3','blah blah blah', '002', '003'); INSERT INTO post_lists (users_id, code, title, cat_1, cat_3_code, details, cat_2, cat_3) VALUES ('456', 'ABC', 'TITLE2', '002','C32','blah blah blah', '0021', '0031'); SELECT * FROM post_lists; EXPLAIN SELECT * FROM post_lists WHERE title = 'TITLE1'; EXPLAIN SELECT title FROM post_lists WHERE title = 'TITLE1'; EXPLAIN SELECT title, cat_1, cat_3, code FROM post_lists WHERE title = 'TITLE1'; EXPLAIN SELECT title, cat_1, cat_3 FROM post_lists WHERE title = 'TITLE1'; DROP TABLE post_lists;
输出：
+----+----+----------+------+---------------------+---------------------+--------+-------+------------+----------------+-------+-------+ | | id | users_id | code | date_posted | date_updated | title | cat_1 | cat_3_code | details | cat_2 | cat_3 | +----+----+----------+------+---------------------+---------------------+--------+-------+------------+----------------+-------+-------+ | 1 | 1 | 123 | ABC | 27.06.2017 11:02:16 | 27.06.2017 11:02:16 | TITLE1 | 001 | C3 | blah blah blah | 002 | 003 | | 2 | 2 | 456 | ABC | 27.06.2017 11:02:16 | 27.06.2017 11:02:16 | TITLE2 | 002 | C32 | blah blah blah | 0021 | 0031 | +----+----+----------+------+---------------------+---------------------+--------+-------+------------+----------------+-------+-------+ +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | 1 | SIMPLE | post_lists | NULL | ref | advanced_query | advanced_query | 257 | const | 1 | 100 | NULL | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | 1 | SIMPLE | post_lists | NULL | ref | advanced_query | advanced_query | 257 | const | 1 | 100 | Using index | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ | 1 | 1 | SIMPLE | post_lists | NULL | ref | advanced_query | advanced_query | 257 | const | 1 | 100 | NULL | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------+ +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | 1 | SIMPLE | post_lists | NULL | ref | advanced_query | advanced_query | 257 | const | 1 | 100 | Using index | +----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
