mysql 主键索引使用_mysql使用主键代替索引

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

ID (primary)

countrycode

status

flag_cc

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)

Then I queried:

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

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?

解决方案

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.

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.

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.

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.

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.

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.

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.