按列排序应该有索引吗？

I making indexes to table with millions of records which is used for searching result. I am showing results by ASC or DESC order. My question is that column should have index or not? I am 2 more indexes on that table. How performance will affect by making or not making index to that column?

我使用数百万条用于搜索结果的记录对表进行索引。我通过ASC或DESC订单显示结果。我的问题是列应该有索引吗？我在那张桌子上还有2个索引。性能如何通过制作或不制作该列的索引来影响？

3 个解决方案

#1

Order by columns are used for ordering the result set, not filtering. An index on the columns mentioned in the order by clause is unlikely to change anything, especially if it's not used to filter the data.

按列排序用于排序结果集，而不是过滤。 order by子句中提到的列的索引不太可能改变任何东西，特别是如果它不用于过滤数据。

#2

Actually - for some millions rows better to apply some well thought out index, it is not so big dataset for beginning to worry about space-performance issues.

实际上 - 对于数百万行更好地应用一些经过深思熟虑的索引，开始担心空间性能问题并不是那么大的数据集。

but

但

If you read that table once a day and update/delete rows 100 times per second - then effect from the index may degrade performance of main operations, while occasinally selecting will perform better.

如果您每天读取该表一次并每秒更新/删除行100次 - 那么索引的效果可能会降低主要操作的性能，而偶尔选择会更好地执行。

So, the answer as usual - it depends

所以，答案和往常一样 - 这取决于

#3

Using ORDER BY on indexed column is not a good idea. Actually the purpose of using index is to making searching faster so the index column helps to maintain the data in sorted order. I will suggest you to use ORDER BY at non-indexed column. I had run a sample query on the MySql and get the result as mentioned below.

在索引列上使用ORDER BY不是一个好主意。实际上，使用索引的目的是使搜索更快，因此索引列有助于按排序顺序维护数据。我建议你在非索引列使用ORDER BY。我在MySql上运行了一个示例查询，并得到如下所述的结果。

SELECT 
    ci.id AS item_id, ci.brand_id, ci.category_id, 
    ci.item_size_type, ci.is_express_processing, ci.packing, 
    ci.parent_service_id, ci.product_id, ci.size,
    ci.create_date AS item_create_date, cis.service_id, cis.quantity   
FROM 
    cart AS c 
INNER JOIN 
    cart_items AS ci ON ci.cart_id = c.id 
LEFT JOIN
    cart_item_services AS cis ON cis.item_id = ci.id 
WHERE
    c.id = 144 
ORDER BY 
    c.create_date;

Here I am using ORDER BY on a non-indexed column create_date and result is as follows:

这里我在非索引列create_date上使用ORDER BY，结果如下：

# Time: 2017-11-03T10:30:33.237056Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 0.000340  
# Lock_time: 0.000154 Rows_sent: 2  Rows_examined: 4

And now I am going to use ORDER BY on an indexed column item_id:

现在我将在索引列item_id上使用ORDER BY：

SELECT 
    ci.id AS item_id, ci.brand_id, ci.category_id, 
    ci.item_size_type, ci.is_express_processing, ci.packing, 
    ci.parent_service_id, ci.product_id, ci.size,
    ci.create_date AS item_create_date, cis.service_id, cis.quantity   
FROM 
    cart AS c 
INNER JOIN 
    cart_items AS ci ON ci.cart_id = c.id 
LEFT JOIN
    cart_item_services AS cis ON cis.item_id = ci.id 
WHERE
    c.id = 144 
ORDER BY 
    item_id;

Now I got the result as follows.

现在我得到如下结果。

# Time: 2017-11-03T10:30:47.802392Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 0.000383  
# Lock_time: 0.000176 Rows_sent: 2  Rows_examined: 4

So in the above result we can see the indexed columns Query_time and Lock_time is higher than the non-indexed column.

因此，在上面的结果中，我们可以看到索引列Query_time和Lock_time高于非索引列。

For more detailed analysis about query standard and performance you can refer below link:

有关查询标准和性能的更详细分析，请参阅以下链接：

http://mysql.rjweb.org/doc.php/index_cookbook_mysql

#1