Several important issues that the mysql index must understand

This article addresses several important issues that mysql indexes must understand. To share for your reference, the details are as follows:

1. What does the index do?

An index is used to quickly find rows with a specific value of 1 in a column. Without indexes, MySQL must start with the first record and read through the table until it finds the relevant rows.
The bigger the watch, the more time it takes. If the column in the query in the table has one index, MySQL can quickly reach one location to search for the middle of the data file without having to look at all the data.
Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in the B tree. Only the spatial column type indexes use R-trees, and the MEMORY table also supports hash indexes.

2. Index is so complicated, how should I understand index? Is there a more vivid example?

Yes, imagine 1, you have a dictionary in front of you, the data is the text of the book, you are the cpu, and the index is the table of contents of the book

3. More indexes, better?

In most cases, the index can greatly improve the query efficiency, but:

Data changes (additions, deletions, and changes) require indexes to be maintained, so more indexes mean more maintenance costs

More indexes mean more space (50 pages of a 100-page table of contents?).

For smaller tables, it may be slower to build indexes :) (read a 2-page brochure, do you need to find the catalog first?)

4. Field type of index

Type text, which can also be indexed (length specified)

myisam storage engine index key length totals cannot exceed 1000 bytes

The values used to filter should be of the same data type as the indexed columns

5. like cannot use index?

Keep like to a minimum, but not absolutely not. "xxxx%" is indexable.

Imagine 1. You are looking for an idiom dictionary. The catalogue is set in alphabetical order. Your query needs are: you want to find idioms starting with "1" ("1%") and idioms containing "1" ("%1%").

In addition to like, the following operators are also available for indexes:

< . < = =, > . > =, BETWEEN, IN
< > not in! = is not

6. What fields are not suitable for indexing?

Generally, column values are too small (e.g., gender, type, etc.) to be indexed. At 1 half, there is no need to index more than 15 percent of the table's data.)

If the column is too long, you can choose to index only part of the column (e.g., only the top 10 columns are indexed).

Very frequently updated data is not suitable for indexing. Sense)

7. Can you use more than one index per query?

Can't

8. Multi-column query how to build index ?

Only one index can be used per query, so first shoot a, b each build index scheme
a or b? Who has the highest degree of differentiation (with the least value), build who!
Of course, a joint index is also a good solution. ab or ba is the same as above

9. Issues of joint index ?

where a = "xxx" can use the AB joint index
where b = "xxx" is not possible (imagine 1 again, this is the list of books?)
So, in most cases, if you have an AB index, you don't have to build an A index

10. What common situations should not be indexed ?

like "%xxx"

not in,! =

A case where a column is functionally evaluated (where md5(password) = "xxxx")

WHERE index=1 OR A=10

If you have a string type field with a value stored (such as a cell phone number), remember not to drop the quotation marks of the value in the query, otherwise you won't be able to use the relevant index of the field, and don't worry if you don't

That is

select * from test where mobile = 13711112222;

You cannot use the index of the mobile field (if mobile is of type char or varchar)

Never try to save your phone number with int (why? You want to! Why don't you try it yourself?)

11. Covered indexes (Covering Indexes) are more efficient

If the index contains all the required values, only select, in other words, only the fields that select needs, avoid select * if not necessary

12. Question of NULL

NULL causes the index to become empty, so avoid NULL when designing the table structure (say NULL in other ways, such as -1?).

13. How to view index information and analyze whether index is used correctly ?

show index from tablename;
explain select  ... ;

Try google before writing an introductory post about explain at some other time

14, know your system, do not optimize too soon!

Premature optimization, 1 is a very annoying but always existing problem, most of the time is because they do not know their own system, do not know the real carrying capacity of their own system

For example: thousands of data news table, hundreds of thousands of text search every day, most of the time we can safely go to like, rather than to build a set of full-text search, after all, cpu is still much better than the human brain

15. Share a small case:

Once had a friend to look for board, say: the master helps see, the company website cannot open
Board smiled: master can not dare to be ah, wait for me to see again
The board spent 10 minutes to analyze the following: small and medium-sized enterprise station, small amount (230,000 pv per day), independent server, small amount of data (100M less than), should not be too slow
A project by an outsourcing team that fell into disrepair and was completely reinvented? Don't reality!
It took the board 20 minutes to index all the fields that could be indexed, and the world went quiet
Friend said: another friend said, optimization at least 2w outsourcing fee, you only 30 minutes, it seems that you are worthy of the master, choose the best restaurant
Board: then come to some Western food, Changshu Road subway station KFC waiting for you!

16. Finally: Never forget the keyword sql injection

I hope that this article is helpful to the MySQL database programming.