MySQL understands indexes, adds indexes, uses indexes (which can cause indexes to fail)

Indexes are used to quickly find rows that have a specific value in a column. Without the use of an index, MySQL must start with the 1th record and then read the entire table until the relevant rows are found, and also consider the IO overhead of each read-in data page. If you take an index, you can quickly read the target page and get the target record based on the page that the index points to and the location of the record in the page.

In most cases, the B-tree is used (by default) to build the index. Only the index of the spatial column type uses the R-tree, and the memory table also supports the hash index. The B-Tree is a balanced multi-fork tree, and how many values each node holds depends on the amount of space the value occupies, and how many records are stored on each page. The values in the nodes are arranged in a non-descending order, and the values in the nodes are always less than or equal to the values in the node that points to it.

When MySQL constructs an index using a B-tree, the leaves point to specific pages and records. And a leaf has a pointer pointing to the next leaf.

The use of indexes requires attention:

⑴ only sets the index of where and order by fields that require querying, avoiding meaningless drive overhead;

⑵ Combined index supports prefix index;

⑶ Update the table, such as adding and deleting records, MySQL will automatically update the index, keep the tree balance; so more indexes mean more maintenance costs .

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

Indexes are divided into four categories:

Index----Normal indexes, data can be duplicated

Fulltext----Full-text index, which is used to index a large table's text field (Char,varchar,text). Syntax is the same as a normal index.

Unique----single index, unique index, requiring all records to be unique

Primary key----PRIMARY key index, that is, the corresponding column must be the primary key on the basis of the unique index

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

Principle

1, the single-table data is too small, the index will affect the speed; updated very frequent data unsuitable for indexing

After the 2,where conditions, order by, and group BY, etc. are filtered, the following fields are best indexed. According to the actual situation, choose primary KEY, UNIQUE, index index, but not the more the better, to moderate

3, union query, subquery and other multi-table operations when the connection field to be indexed

PS: When the data volume is particularly large, it is best not to use federated queries, even if you have indexed

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.

Detailed

A federated index is also called a composite index. For composite indexes: MySQL left-to-right uses fields from the index, and a query can use only one part of the index, but only the leftmost section. For example, the index is key index (A,B,C). can support a | a,b| A,b,c 3 combinations to find, but B,c is not supported. The index is very effective when the leftmost field is a constant reference.

An index on two or more columns is called a composite index.
With additional columns in the index, you can narrow the scope of your search, but using an index with two columns differs from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a surname and a name, and the phone book is first sorted by last name, and then by name for people with the same last name. If you know the last name, the phone book will be useful, and if you know the first and last names, the phone book is more useful, but if you only know the first name, the phone book will be useless.
So when you create a composite index, you should carefully consider the order of the columns. A composite index is useful when you perform a search on all columns in an index or when you perform a search on only the first few columns, and the composite index is useless when you perform a search on any subsequent column.
such as: the establishment of the name, age, gender composite index.

CREATE TABLE Test (
a int,
b int,
c int,
KEY A (A,B,C)
);

Excellent: SELECT * from Test where a=10 and b>50
Poor: SELECT * from Test where A50

Excellent: SELECT * FROM Test order by a
Poor: SELECT * from Test order by B
Poor: SELECT * from Test ORDER by C

Excellent: SELECT * from Test where a=10 order by a
Excellent: SELECT * from Test where a=10 order by B
Poor: SELECT * from Test where a=10 order by C

Excellent: SELECT * from Test where a>10 order by a
Poor: SELECT * from Test where a>10 order by B
Poor: SELECT * from Test where a>10 order by C

Excellent: SELECT * from Test where a=10 and b=10 order by a
Excellent: SELECT * from Test where a=10 and b=10 order by B
Excellent: SELECT * from Test where a=10 and b=10 order by C

Excellent: SELECT * from Test where a=10 and b=10 order by a
Excellent: SELECT * from Test where a=10 and b>10 order by B
Poor: SELECT * from Test where a=10 and b>10 order by C

Indexing principles

1. The fewer indexes the better
Cause: When modifying the data, the first index is updated to reduce the write speed.
2. The narrowest field is placed on the left side of the key
3. Avoid file sort sorting, temporary tables, and table scans.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

Reprinted from: http://www.cnblogs.com/DaBing0806/p/4876228.html

MySQL understands indexes, adds indexes, uses indexes (which can cause indexes to fail)