如何索引这些查询? [英] How can I index these queries?

查看：51
本文介绍了如何索引这些查询?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我对要进行的索引编制有些困惑.

I am a little confused about the indexing I am going to do.

首先，我正在使用4列索引，如下所示:

First, I am using a 4-column index, like this:

索引名称-advanced_query

Index Name - advanced_query

列将用于索引-标题，类别1，类别2，类别3

Columns will be used in the index - title, category 1, category 2, category 3

索引代码

ALTER TABLE table_name ADD INDEX advanced_query (`title`, `cat_1`, `cat_2`, `cat_3`, `date_posted`)

好的，这就是(据我所知)它将如何工作:

Okay, so this is how (from what I understand) it will work:

  • 查询标题将使用索引.
  • cat_1 的查询将使用索引.
  • cat_2 的查询将使用索引.
  • cat_3 的查询将不使用索引.因此，我将为此创建一个不同的索引.
  • 对 title，cat_1 的查询将使用索引.
  • 对标题，cat_1，cat_2 的查询将使用索引.
  • 对 title，cat_1，cat_2，cat_3 的查询将使用索引.
  • 对 title，cat_1，cat_3 的查询将使用索引.
  • 对标题cat_2 的查询将使用索引.
  • 对标题，cat_2，cat_3 的查询将使用索引.
  • 对标题cat_3 的查询将使用索引.
  • cat_1，cat_2 的查询将使用索引.
  • cat_1，cat_2，cat_3 的查询将使用索引.
  • cat_1，cat_2 的查询将使用索引.
  • 查询 cat_1，cat_3 的索引.
  • a query of title will use the index.
  • a query of cat_1 will use the index.
  • a query of cat_2 will use the index.
  • a query of cat_3 will NOT use the index. So I will create a different index for it.
  • a query of title, cat_1 will use the index.
  • a query of title, cat_1, cat_2 will use the index.
  • a query of title, cat_1, cat_2, cat_3 will use the index.
  • a query of title, cat_1, cat_3 will use the index.
  • a query of title, cat_2 will use the index.
  • a query of title, cat_2, cat_3 will use the index.
  • a query of title, cat_3 will use the index.
  • a query of cat_1, cat_2 will use the index.
  • a query of cat_1, cat_2, cat_3 will use the index.
  • a query of cat_1, cat_2 will use the index.
  • a query of cat_1, cat_3 will use the index.

TL; DR

因此，在该索引中，只有 cat_3 查询不会从中受益，对吧?谢谢！

So in this index, only a query of cat_3 will not benefit from it, right? Thanks!

问题/答案

我在做什么查询?搜索帖子(标题和3个不同的类别)

What query am I doing ? searching a post (it's title and 3 different categories)

桌子的大小是多少?少于两千行

What is the size of table ? Less than 2 thousand rows

表的结构?

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

此表将使用多少次?大多数时候.这是高级搜索功能，因此不像基本搜索那样频繁.

How many times will this table use ? Most of the time. This is the advanced search function so not just frequently as the basic search is.

这就是我实际使用索引的方式.

This is how I actually will use the index.

示例表

title | cat_1 | cat_2 | cat_3 | date_posted

我的查询很简单:

  1. 标题

SELECT * FROM tbl_name WHERE title LIKE %title% ORDER BY date_posted DESC

标题 + cat_1

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' ORDER BY date_posted DESC

标题 + cat_1 + cat_2

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC

标题 + cat_1 + cat_2 + cat_3

title + cat_1 + cat_2 + cat_3

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

标题 + cat_1 + cat_3

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_1 = 'cat_1' and cat_3 = 'cat_3' ORDER BY date_posted DESC

标题 + cat_2

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' ORDER BY date_posted DESC

标题 + cat_2 + cat_3

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

标题 + cat_3

SELECT * FROM tbl_name WHERE title LIKE %title% AND cat_3 = 'cat_3' ORDER BY date_posted DESC

cat_1

SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' ORDER BY date_posted DESC

cat_1 + cat_2

SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' ORDER BY date_posted DESC

cat_1 + cat_2 + cat_3

SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_2 = 'cat_2' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

cat_1 + cat_3

SELECT * FROM tbl_name WHERE cat_1 = 'cat_1' AND cat_3 = 'cat_3' ORDER BY date_posted DESC

cat_2

SELECT * FROM tbl_name WHERE cat_2 = 'cat_2' ORDER BY date_posted DESC

cat_2 + cat_3

SELECT * FROM tbl_name WHERE cat_2 = 'cat_2' ORDER BY date_posted DESC

cat_3

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还是创建单独的索引?

Hi, I read and searched about Full Text Search, and I am thinking to use it (in basic search) instead of LIKE %wildcard% and applying it to title and details, my problem is I want them to sort ORDER BY date_posted DESC, so should I add date_posted in Full Text Search or create a separate index?

推荐答案

我认为您的问题的答案"有点复杂，并且您对使用索引的假设并不总是正确的.

I think an "answer" to your question is a bit complex and your supposition on index use are not always correct.

简短的答案是:取决于".

The shorter answer would be: "It depends".

实际上，索引的使用取决于几个因素:表中的记录数，索引结构，请求的字段，查询中的条件，统计信息.

Actually the use of an index depends by several factor: number of records in table, index structure, field requested, condition in query, statistics.

1)记录数:如果记录数很小，则db引擎可能决定不使用索引(特别是如果您将SELECT的SELECT *写入SELECT –表中的几列不在索引中-).

1) Number of records: if it is small, maybe db engine decide not to use the index (especially if you write SELECT * of SELECT --several columns in table not in index --).

如果仅选择索引中的部分或全部列，则可以使用索引(也无需考虑WHERE条件).

The index could be used (not considering WHERE condition too) if you SELECT only some of or all the columns in index.

2)索引结构:正如您所指出的，它是相关的. Morevore有两种可以使用"索引的主要方法:扫描和查找.寻求是最有效的.在大多数情况下，您是否会以编写顺序查找索引中的列:从表的标题中选择标题，例如"ABC％").注意:如果您写了LIKE'％ABC％'，则它不能进行搜索，而只能进行扫描. (扫描表示db必须从头到尾查找整个索引，而查找时他将直接转到相关页面，因为您将使用姓氏在电话簿中查找某人的电话号码).

2) index structure: as you pointed, it's relevant. Morevore there are two different main ways an index can be "used": scan and seek. Seek is the most efficient. In most cases you have a seek if you look for the columns in the index in the same order you wrote them: eg. SELECT TITLE FROM YOUR TABLE WHERE TITLE LIKE 'ABC%'). Note: if you wrote LIKE '%ABC%' it can't do a seek, but a scan. (A scan mean that db have to look for the whole index, from the beginning to the end, while with a seek he goes directly to the relevant pages, as you will do looking for a phone number of a person in a phonebook using lastname).

3)要求提供的字段:您应该考虑一下，如果您编写SELECT *(如上所述，db引擎可能会决定还是使用全表扫描)

3) Field requested: you should consider that if you write SELECT * (as I pointed above, db engine could decide to use anyway a full table scan)

4)查询条件.

5)统计信息:db引擎写入有关数据和索引(记录数，结构等)的统计信息.如果它们未更新，则可能以错误"方式使用或不使用索引.

5) Statistics: db engine write statistics on data and index (number of record, structure,etc). If they are not updated, it's possibile it use or don't use index in "erroneus" way.

-----更新:简单(并非详尽无遗...)演示

实际上(使用这么小的数据，我不得不注释您的KEY'title_date_posted'以便在某些情况下使用"advanced_query"索引:否则，它似乎试图使用它； 正如我告诉您的那样，数据库引擎会在内部决定要使用的索引).

Actually (with this small data, I had to comment your KEY 'title_date_posted' to make it use in some cases the "advanced_query" index: otherwise it seems to try to use that; as I told you, db engine makes an internal decisions what index to use).

在rextester.com上进行的测试:

Test done on 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 |
+----+----+-------------+------------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+

这篇关于如何索引这些查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆