Mysql index must understand several important issues _mysql

This article describes the MySQL index must understand several important issues. Share to everyone for your reference, specific as follows:

1, what is the index?

Indexes are used to quickly find rows that have a specific value in a column. Without indexing, MySQL must start with the 1th record and read through the entire table until the relevant rows are found.
The larger the table, the more time it takes. If the query column in the table has an index, MySQL can quickly reach a location to find the middle of the data file, there is no need to look at all the data.
Most MySQL indexes (PRIMARY KEY, UNIQUE, index, and fulltext) are stored in the B-tree. Only the index of the spatial column type uses the R-tree, and the memory table also supports the hash index.

2, the index is very complex, how can I understand the index, there is no more image point example?

Yes, imagine, you have a dictionary, data is the body of the book, you are that CPU, and index, is the directory of books

3, the more the index the better?

In most cases, indexing can significantly improve query efficiency, but:

Data changes (deletions) need to maintain the index, so more indexes mean more maintenance costs

More indexes mean more space (a 100-page book with 50 pages of catalogs). ）

Too small table, the index may be more slow oh: (read a 2-page brochure, you also go to find the directory?) ）

4, the index of the field type problem

Text type, also indexed (need to specify length)

MyISAM Storage Engine Index key length synthesis cannot exceed 1000 bytes

The values used to filter to keep the same data type as the indexed column

5, like can not use the index?

Minimize like, but not absolutely unavailable, "xxxx%" can be used to index,

Imagine, you are looking at an idiom dictionary, the catalogue is set up according to the pinyin order of the idiom, the inquiry demand is, you want to find the idiom that begins with "one" ("One%"), and you want to find the idiom containing one word ("% one%")

In addition to like, the following operators can also be used to index:

<,<=,=,>,>=,between,in
<>,not in,! = Then no

6, what kind of field does not fit the index?

In general, the value uniqueness of the column is too small (such as sex, type, etc.), does not fit the index (how to call too small?) Half of the data in the same value is over 15 of the table, so there's no need to index it.

Too long column, you can choose to only set up a partial index, (such as: Only take the top ten indexed)

Updated very frequently data is not suitable for indexing (how to call very?) Tacit

7, a query can use more than one index?

No

8, multiple columns query how to build the index?

Once a query can only use one index, so first shoot a,b each index scheme
A or B? Who is more differentiated (least of the same value), build who!
Of course, the Federated Index is also a good scheme, AB, or BA, then ibid, the distinction of high, in the former

9, the problem of the joint index?

where a = "xxx" can use AB federated index
where B = "xxx" is not (think again, this is the directory of the book?) ）
So, in most cases, with the AB index, you don't have to build a index of a.

10, which common conditions can not be indexed?

Like "%xxx"

Not in,!=

A function operation on a column (such as where MD5 (password) = "xxxx")

WHERE index=1 OR a=10

A String type field that has a numeric value, such as a cell phone number, remember not to throw out quotation marks when querying, otherwise you can't use the relevant index of the field, or it doesn't matter.

Also namely

SELECT * FROM test where mobile = 13711112222;

However, the index of the mobile field cannot be used (if mobile is char or varchar type)

BTW, do not try to use INT to save cell phone number (why?) Think for yourself! Or try it yourself.

11, the coverage index (covering Indexes) has more efficient

If the index contains all the values you want, select them only, in other words, select only the fields that you need to use, and if you don't need to, try to avoid a select *

12, NULL problem

Null causes the index to be fake, so you should avoid the presence of NULL in designing the table structure (in other ways you want to express null, for example, 1?). ）

13, how to view the index information, how to analyze the correct use of the index?

Show index from TableName;
Explain select ...;

About explain, another day can find a time to write a special entry post, before this, you can try Google

14, understand their own system, do not prematurely optimize!

Premature optimization, has always been a very annoying and constant problems, most of the time because they do not understand their own system, do not know their own system's true carrying capacity

For example: Thousands of data news table, hundreds of thousands of times a day body search, most of the time we can rest assured to like, and not to build a set of Full-text search what, after all, the CPU is still worse than the human brain too much

15, share a small case:

Once a friend to find the board, said: Master Help See, the company's website can not open
The board smiled: the master can not deserve Ah, see me again
The Board took 10 minutes to analyze the following: Small and medium Enterprises station, a small amount (twenty thousand or thirty thousand PV per day), independent server, the amount of data is not small (100M), should not be too slow
A project outsourced by a team that has been in disrepair and makeover? Not realistic!
So the board took 20 minutes to index the fields that could be indexed, so the world was quiet.
Friend said: Another brother said, optimize at least 2w outsourcing fee, you only use 30 minutes, it seems, master you are well-deserved, choose the best restaurant
Board: Then a little Western bar, Changshu Road subway station KFC waiting for you!

16, the last: Never forget the keyword SQL injection

I hope this article will help you with MySQL database program design.