mysql使用主键而不是索引 [英] mysql uses primary key instead of index

问题描述

我有一个很大的表，有几百万行:

I have a pretty large table with a few million rows:

ID (primary)
countrycode
status
flag_cc

我尝试了以下sql语句，但是速度很慢:

I tried the following sql statement, but it was quite slow:

SELECT id, countrycode, status, flag_cc FROM table WHERE ID>=200000 AND countrycode=3 AND status=1 AND flag_cc=0

因此，我认为添加索引以加快查询速度是一个好主意:

So I thought it would be a good idea to add an index to fasten that query up:

ADD INDEX myindex(id, countrycode, status, flag_cc)

然后我查询:

EXPLAIN SELECT id, countrycode, status, flag_cc FROM table WHERE ID>=200000 AND countrycode=3 AND status=1 AND flag_cc=0

但是mysql要使用主键而不是我的键.因此，我使用了FORCE INDEX并将主键与我的键进行了比较.可悲的是，主键要快得多.

but mysql wants to use the primary key instead of my key. So I used FORCE INDEX and compared the primary key to my key.. Sadly the primary key was a lot faster.

那怎么可能?如果主键太慢，是否有可能优化该查询?

How could that be? And is it ever possible to optimize that query if even the primary key is too slow?

推荐答案

您的问题基本上是什么是好的索引?".您可能需要考虑在MySQL文档中阅读它们，在此处是关于stackoverflow的，并且仅使用任何搜索引擎即可.

Your question is basically "What is a good index?". You might want to consider reading up on them in the MySQL documentation, here on stackoverflow and just by using any search engine.

考虑像大百科全书中的索引这样的索引.定义了很多主题，因此索引可以帮助您更快地找到所需的内容.

Consider an index like the index in a big encyclopedia. There are a lot of topics defined, so an index helps you find what you're looking for a little faster.

但是索引中应该包含什么?类别(科学，娱乐，人等)?然后，当您找到类别时，每个类别中仍然有很多文章.假设共有10000篇文章，其中1000篇属于科学类别.如果您正在寻找科学的东西，那仍然有1000篇文章需要您仔细阅读才能找到确切的文章.用数据库术语来说，该索引没有很好的基数:如果您没有其他内容但又没有足够的具体信息来真正加快速度，则很好.通过以字母开头(索引中有26个字母，所以索引也是如此)，使用索引将要查找的文章数除以大约26，这也不是很具体.)

But what should be in the index? The category (science, entertainment, people, ...)? Then when you found the category, there's still plenty of articles that fall inside each category. Say that there are 10 000 articles total of which 1000 are in the science category. If you are looking for something science-ey, that still leaves you with 1000 articles to look through for your exact article. In database terms, this index does not have good cardinality: it's nice if you've got nothing else but not specific enough to really speed things up. The same would hold for an index by starting letter (26 letters in the alphabet so using the index you divide the number of articles to look for by roughly 26, which also isn't very specific).

在数据库中，这意味着主键是一个很好的索引字段:此字段的一个值恰好对应于数据中的一个值，因此，一旦使用索引来查找它，就没有任何可浏览的内容了.您已经找到了特定的记录.

In databases, this means a primary key is a very good field to index: one value of this field corresponds to exactly one value in the data, so once the index is used to find it, there's nothing left to look through; you've already found the specific record.

相反，一个true/false标志只会将您的数据最多分为两组，因此即使使用索引后，仍然有大量数据可以浏览.

A true/false flag on the other hand, only divides your data in a maximum of two groups, so that still leaves plenty of data to look through even after using the index.

当然有例外.例如，具有true/false列的表.通常，这是要索引的错误列.但是，您可能知道所有记录中只有0.01％的该列的值为"true"，并且您的查询将查找真实值，而不是错误值.在这种情况下，该true/false列是要索引的好列.

There are exceptions, of course. For example, a table with a true/false column. Normally this is a bad column to index. But it's possibly that you know that only 0.01% of all records will have the value 'true' for that column and your query looks for the true values, never the false values. In that case, that true/false column is a good column to index.

然后是范围问题:您不是在搜索特定的ID，而是在搜索整个ID，因此即使ID是唯一的，它仍会标记索引的整个部分(因此是数据)作为使用索引后仍然可以浏览的内容".因此，尽管它具有良好的基数，但可能不是用于此特定查询的最佳索引.

Then there's the range problem: you're not searching for a specific ID, but a whole range of them, so even if the ID is unique, it would still mark a whole section of the index (and thus the data) as 'things to still look through after using the index'. So while it has good cardinality, it may not be the best index to use for this specific query.

另一个问题是，当您不在索引的第一列上进行搜索时，MySQL无法浏览多列索引.因此，索引(ID，国家/地区代码，状态，flag_cc)将意味着MySQL仍必须开始使用ID索引，这在查询中属于范围条件，上一段解释了为什么这样做很糟糕.如果MySQL确定仍然值得这样做，则只有在应用了索引的ID部分之后，它才能从countrycode部分开始.这也许就是MySQL即使要给它另一个选择也要使用主键索引的原因.

Another problem is that MySQL cannot look through a multi-column index when you're not searching on the first column of the index. So an index (ID, countrycode, status, flag_cc) would mean that MySQL still has to start using the index by ID, which in your query is a range condition, and the previous paragraph explains why that's bad. Only after applying the ID part of the index can it start with the countrycode part, if MySQL determines that's even still worth the effort. This is probably why MySQL wants to use your primary key index even though you've given it another option.

在表上应用所有这些信息:where子句包含所有列，因此从基数最高(值相异最大)且不用作where子句的范围的列(因此不创建索引)开始建立索引ID).如果flag_cc包含许多不同的值，请使用该值.如果status或countrycode包含更多甚至更多的不同值，请使用其中之一.根据您索引的第一列的具体程度，对单个列进行索引可能就足够了.如果不是，请尝试将基数次佳的列添加到索引中，等等.

Applying all this information on your table: your where clause contains all columns, so build an index starting with the column that has the highest cardinality (the most different values) and that is not used as a range where clause (so not ID). If flag_cc contains plenty of different values, use that. If status or countrycode contains even more of different values, use one of those. Depending on how specific the first column you index is, indexing a single column may be enough. If not, try adding the column with the next-best cardinality to the index, et cetera.

当然，请记住，索引(通常，并非总是如此)可加快查找速度，但会减慢更新，插入和删除的速度！

And of course, remember that indexes (usually, not always) speed up lookups, but slow down updates, inserts and deletes!

所以您看到，这不是一个非常简单的问题.还要考虑我概述的内容只是索引冰山的一角.

So you see, it's not a very simple problem. Also consider that the things I've outlined are just the tip of the indexing ice berg.

资料来源:
http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/
https://dev.mysql.com/doc/refman/5.6/zh-CN/multiple-column-indexes.html

Sources:
http://webmonkeyuk.wordpress.com/2010/09/27/what-makes-a-good-mysql-index-part-2-cardinality/
https://dev.mysql.com/doc/refman/5.6/en/multiple-column-indexes.html

这篇关于mysql使用主键而不是索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！