如何解释MySQL EXPLAIN的输出? [英] How to interpret the output of MySQL EXPLAIN?

问题描述

我想从entrytable中选择列text的内容.

I want to select the content of the column text from entrytable.

EXPLAIN SELECT text
FROM entrytable
WHERE user = 'username' &&
`status` = '1' && (
    `status_spam_user` = 'no_spam'
    || (
        `status_spam_user` = 'neutral' &&
        `status_spam_system` = 'neutral'
    )
)
ORDER BY datum DESC
LIMIT 6430 , 10

该表具有三个索引:

• index_user(用户)
• index_datum(基准)
• index_status_mit_spam(状态，status_spam_user，status_spam_system)

EXPLAIN结果为:

The EXPLAIN result is:

id  select_type     table       type    possible_keys                       key         key_len     ref     rows    Extra
1   SIMPLE          entrytable  ref     index_user,index_status_mit_spam    index_user  32          const   7800    Using where; Using filesort

• possible_keys是MySQL可能想要使用的索引，还是keys MySQL实际使用的索引?
• 为什么不使用索引index_status_mit_spam?在查询中，列的顺序与索引中的顺序相同，...
• 为什么索引index_datum不用于ORDER BY?
• 如何优化表索引或查询? (上面的查询最多需要3秒，在表中大约有100万个条目)
• Is possible_keys the indices MySQL might want to use and keys the indices MySQL actually uses?
• Why is the index index_status_mit_spam not used? In the query, the colums have the same order as in the index,...
• Why is the index index_datum not used for the ORDER BY?
• How can I optimize my table-indices or the query? (The query above needs up to 3 seconds having about a million entries in the table)

推荐答案

回答您的问题:

• possible_keys是MySQL可能要使用的索引，而keys是MySQL实际使用的索引吗?是的，这是正确的.

• Is possible_keys the indices MySQL might want to use and keys are the indices MySQL actually uses? Yes this is correct.

为什么不使用索引index_status_mit_spam?在查询中，列的顺序与索引中的顺序相同. SQL 使用表索引的统计信息来确定要使用的索引. select语句中字段的顺序对使用哪个索引没有影响.索引周围的统计信息包括诸如索引唯一性等信息.可能会使用更多唯一索引.在此处了解更多信息: http: //dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html 或在此处:

Why is the index index_status_mit_spam not used? In the query, the columns have the same order as in the index. SQL uses statistics on the table's indexes to determine which index to use. The order of fields in the select statement has NO effect on which index to use. Statistics around indexes include information such as uniqueness of the index and other things. More unique indexes are likely to be used. Read more about this here: http://dev.mysql.com/doc/innodb/1.1/en/innodb-other-changes-statistics-estimation.html or here:http://dev.mysql.com/doc/refman/5.0/en//myisam-index-statistics.html. These factors determine how MySQL will select the one index to use. It will only use ONE index.

为什么索引index_datum不用于ORDER BY?在查询期间，MySQL将仅使用一个索引而不使用两个索引，因为使用第二个索引会进一步降低查询速度.读取索引不读取表.这与查询的操作效率有关.以下是可以解释一些概念的答案: https ://dba.stackexchange.com/questions/18528/performance-difference-between-clustered-and-non-clustered-index/18531#18531 或 MySQL索引以及何时对其进行分组.这些答案有很多细节，可以帮助您理解MySQL索引.

Why is the index index_datum not used for the ORDER BY? MySQL will only use one index not two during a query as using a second index will slow down the query even more. Reading an index is NOT reading the table. This is related to operational efficiency of the query. Here is answers which can explain some concepts: https://dba.stackexchange.com/questions/18528/performance-difference-between-clustered-and-non-clustered-index/18531#18531 Or Adding limit clause to MySQL query slows it down dramatically Or MySQL indexes and when to group them. These answers have a lot detail which will help you understand MySQL Indexing.

如何优化表索引或查询? (上面的查询最多需要3秒，在表中大约有100万个条目).好吧，这里有一个文件排序可能会减慢您的速度.可能是因为表中的索引过多，而MySQL选择了错误的索引.

How can I optimize my table-indices or the query? (The query above needs up to 3 seconds having about a million entries in the table). Well there is a filesort here that is probably slowing you down. It might be that the table has too many indexes and MySQL is selecting the wrong one.

您需要了解索引可以加快读取速度，并减慢对表的写入速度.因此，仅添加索引并不总是一个好主意.以上答案和指示将帮助您获得扎实的了解.

You need to understand that indexes speeds up reads and slows down writes to tables. So just adding indexes is not always a good idea. The above answers and pointers should help you gain a solid understanding.

这篇关于如何解释MySQL EXPLAIN的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！