MySQL indexes need to know a few notes

The board has done 2 years of web development training (Getting started?). ), get a lot of students praise, it is quite a feeling of accomplishment, ready to take some time according to some of the lessons prepared at the time to sort out a series of articles, hoping to bring more people help, this is the first series of articles

Note: An article on popular science, Daniel Bypass

What does an index do?

Indexes are used to quickly find rows that have a specific value in a column. Without an index, MySQL must start with the 1th record and then read the entire table until the relevant rows are found.
The larger the table, the more time it takes. If there is an index to the column queried in the table, MySQL can quickly reach a location to find the middle of the data file, and 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.

Index is so complicated, how do I understand the index, there is not a more image point example?

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

The more indexes the better?

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

• Data changes (deletions) require maintenance of indexes, so more indexes mean more maintenance costs

• More indexes mean more space (a 100-page book, but a 50-page catalogue?). ）

• Too small to build the index may be slower oh: (Read a 2-page brochure, you go to find the directory?) ）

field type issues for indexes

• Text type, can also be indexed (specify length)
• MyISAM Storage Engine Index key length synthesis cannot exceed 1000 bytes
• The values used to filter as much as possible to keep the same data type as indexed columns

Like cannot use an index?

• Try to minimize the like, but not absolutely unavailable, "xxxx%" can be used to index,

Imagine that you are looking at an idiom dictionary, the directory is based on the idiom Pinyin order establishment, query demand is, you want to find the "one" word beginning idiom ("one Percent"), and you want to find the idiom containing a word ("% one%")

• In addition to the like, the following operators are also available to the index:

<,<=,=,>,>=,between,in

<>,not in,! = No

What kind of fields do not fit the index?

• In general, the value of the column is too small (such as gender, type, etc.), do not fit the index (what is called too small?). In half, the data of the same value exceeds 15 of the table, so there is no need to index it.
• Too long columns, you can choose to set up only partial indexes (e.g., take top ten indexes)
• Data that is updated very frequently is not suitable for indexing (how is it called very?) Tacit

Can I use multiple indexes on a single query?

No

How does a multi-column query build the index?

Once a query can only use one index, so first of all to shoot a, b each indexed scheme

A or B? Who has a higher degree of differentiation (the least of the same value), build who!

Of course, the joint index is also a good solution, AB, or BA, then ibid, the high degree of distinction, in the former

Problems with federated indexing?

where a = "xxx" can use the AB federated Index
where B = "xxx" is not (imagine, is this the book's catalogue?) ）

So, in most cases, with AB index, you don't have to build a index.

What are the common conditions that cannot be indexed?

• Like "%xxx"
• Not IN,! =
• A case where a column is functional (such as where MD5 (password) = "xxxx")
• WHERE index=1 OR a=10
• A String type field (such as a phone number) that has a numeric value stored, remember not to drop the value quotation marks when querying, otherwise it is not OK to use the field related index.

Also that

SELECT * FROM test where mobile = 13711112222;

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

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

Overlay index (covering Indexes) for greater efficiency

The index contains all of the required values, just select them, in other words, select only the fields that need to be used, and if not necessary, avoid SELECT *

The problem of NULL

Null causes the index to be empty, so you should avoid the existence of NULL when designing the table structure (other ways to express the null you want to express, such as-1?). ）

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

Show index from TableName;
Explain select ...;

About explain, you can find a time to write an introductory post sometime, before you can try Google

Understand your system and don't optimize it prematurely!

Premature optimization, has always been a very annoying and time-to-exist problem, most of the time because you do not know your system, not knowing the real load capacity of their system

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

Share a small case:

Once a friend to find the board, said: Master to see, the company website can not open

The board smiled: master can deserve Ah, wait for me to see again

The Board took 10 minutes to analyze the following: Small and medium-sized enterprise station, a small amount (twenty thousand or thirty thousand PV per day), a standalone server, the amount of data is small (100M less), should not be too slow
A project outsourced by a team that has been in disrepair and reinvented? Not realistic!

So, the board spent 20 minutes to index the fields are indexed, so the world is quiet

Friend said: "Another Buddy 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 some Western food, Changshu Road subway station KFC waiting for you!

Transferred from: http://thephper.com/?p=142

MySQL indexes need to know a few notes