Should we include sort column, primary key on composite index (MySQL)

Refresh

February 2019

Views

562 time

1

Table (Simplified):

+---------------------------------------------------------------------+
| id (Primary AI) |  user_id  |  status  |  type  |   data   |   ip   |
+=====================================================================+
|        1        |     3     |    0     |   abc  |   a-s-d  |    -   |
+---------------------------------------------------------------------+
|        2        |     1     |    0     |   ooo  |   z-z-z  |    -   |
+---------------------------------------------------------------------+
|        3        |     3     |    0     |   ooo  |   f-f-f  |    -   |
+---------------------------------------------------------------------+
|        4        |     2     |    0     |   abc  |   h-h-h  |    -   |
+---------------------------------------------------------------------+
|        5        |     1     |    0     |   abc  |   a-s-d  |    -   |
+---------------------------------------------------------------------+

More Info:

ID is the Primary Key of this table (Auto Increment)

Please Note that i have used ID (Primary Key) as the 3rd Seq_in_index on new index

I have created a Composite Index for the mentioned table

CREATE INDEX userid_type_id ON table (user_id, type, id);

The id in this Index is only used for sorting.

Sample Query

SELECT id, status, data, ip 
                      FROM `table`
                      WHERE user_id=3 AND type='abc' 
                      ORDER BY id DESC;

My questions are:

  1. Is it a good (performance) practice to insert ID in the composite index? as it is only used for ORDER BY

  2. Is it okay to use ID (primary key) as the third sequence in index while it is first column on the table

  3. Did i choose my Index correctly based on my sample query?

Edit:

I use InnoDB

1 answers

4

Ответ зависит от двигателя вы используете:

  • MyISAM - добавление идентификатора индекса может и, вероятно, поможет
  • InnoDB - Первичный ключ уже является частью каждого вторичного индекса, потому что InnoDB хранит строки в ВТКЕЕ отсортированы по первичному ключу и индексу необходимы первичный, чтобы указать фактический ряд - в том случае, добавив, что является излишним, если он является последним в индексе (но делать это не добавит его в два раза, так что он не должен делать вещи хуже). В некоторых случаях вы можете добавить его в качестве не-в прошлом, или у вас есть многоколончатый первичные и добавить несколько столбцов в индекс в другом порядке - не должна быть никаких проблем с ним, InnoDB будет добавлять остальные столбцы первичных к этому индексу, но можно использовать те добавлены раньше без их дублирования)

Так ответов:

  1. В InnoDB нет необходимости, в MyISAM это хорошо в случае, если вы на самом деле использовать эту сортировку, если вы не используете его, добавив его только делает этот показатель больше.
  2. Порядок столбцов в определении таблицы и порядка в индексе отдельные вещи, так что это нормально
  3. Да, этот показатель кажется очень хорошо - но вы можете проверить себя с помощью EXPLAIN , есть возможность еще более высокой производительности - « индекс покрытие », но это приходит с ценой , так , если запрос не является критическим и неэффективным, это, вероятно , слишком много.