MySQL composite index, General index summary

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. Here are a few examples to compare the performance impact of different query conditions.

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 where ORDER by a
Poor: SELECT * from test where ORDER by B
Poor: SELECT * from test where 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.

So I went online to check the relevant information:(about the composite Index optimization)

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.



The establishment principle of compound index:

If you are likely to perform a search more than once on only one column, the column should be the first column in the composite Index. If you are likely to perform a separate search for two columns in a two-column index, you should create another index that contains only the second column.
As shown, if you need to query for age and gender in your query, you should create a new composite index that contains age and gender.
A primary key that contains multiple columns is always automatically created as a composite index, in the order in which they appear in the table definition, rather than in the order specified in the primary key definition. Determine which column should be at the top, considering the search that will be performed by the primary key in the future.
Note that creating a composite index should contain a few columns, and these columns are often used in select queries. Including too many columns in a composite index does not give you too much benefit. And because of the amount of memory used to store the values of the columns of the composite index, the consequence is memory overflow and performance degradation.


Optimization of the sorting of composite indexes:

Composite indexes are optimized only for order by statements that have the same or opposite ordering in the index.
When you create a composite index, each column defines either ascending or descending. If you define a composite index:

SQL code

1. CREATE INDEX Idx_example
2. On table1 (col1 ASC, col2 DESC, col3 ASC)

Three of these columns are: col1 ascending, col2 descending, col3 Ascending. Now if we execute two queries
1:select col1, col2, col3 from table1 ORDER by col1 ASC, col2 DESC, col3 ASC
Same as Index order
2:select col1, col2, col3 from table1 order BY col1 Desc, col2 ASC, col3 desc
Opposite the index order
Queries can not be composite index optimization.
If the query is:
Select col1, col2, col3 from table1 ORDER by col1 ASC, col2 ASC, col3 ASC
When the sorting result is completely different from the index, the query is not optimized by the composite index at this time.


The role of the query optimizer in the WHERE query:

If a multi-column index exists on columns Col1 and COL2, the following statement: Select * from table where col1=val1 and col2=val2 the query optimizer tries to determine which index will find fewer rows. Then use the resulting index to fetch the value.
1. If there is a multi-column index, any leftmost index prefix can be used by the optimizer. Therefore, the order of the Federated index is different, affecting the selection of the index, as far as possible to put less value in front.
For example: A multi-column index (col1, col2, col3)
Then the search for the index in the column (col1), (col1 col2), (col1 col2 col3) will work.

SQL code

1. SELECT * from TB WHERE col1 = val1
2. SELECT * from TB WHERE col1 = val1 and col2 = Val2
3. SELECT * from TB WHERE col1 = val1 and col2 = val2 and col3 = Val3

2. If the column does not constitute the leftmost prefix of the index, the established index will not work.
Such as:

SQL code

1. SELECT * from TB WHERE col3 = Val3
2. SELECT * from TB WHERE col2 = val2
3. SELECT * from TB WHERE col2 = Val2 and Col3=val3

3. If the query condition of a like statement does not start with a wildcard character, the index is used.
such as:% Car or% car% do not use index.
Vehicle% use index.
Disadvantages of the index:
1. Consume disk space.
2. Added operation time for insert and delete. The more indexes a table has, the slower it is to insert and delete. It is not advisable to build too many indexes if the system requires fast entry.

The following are some common index throttling issues

1. Use not equal to operator (<>! =)
In this case, even if there is an index in column dept_id, the query statement performs a full table scan
SELECT * FROM dept where Staff_num <> 1000;
However, the development does require such a query, there is no way to solve the problem?
Yes!
By querying with or syntax instead of an equal sign, you can use the index to avoid a full table scan: The above statement is changed to the following so that the index can be used.

SQL code

1. SELECT * FROM dept Shere Staff_num < or dept_id > 1000;

2. Using is null or is not NULL
Using is null or is Nuo null also restricts the use of the index because the database does not define a null value. If there is a lot of NULL in the indexed column, the index will not be used (unless the index is a bitmap index and the bitmap index will be explained in detail in a later blog post). Using NULL in an SQL statement can cause a lot of trouble.
The solution to this problem is to define the columns that need to be indexed as non-null (NOT NULL) when the table is built

3. Using functions
If you do not use a function-based index, using a function in the WHERE clause for columns that have indexes causes the optimizer to ignore those indexes. The following query will not use the index:

SQL code

1. SELECT * from the staff where trunc (birthdate) = ' 01-may-82 ';

However, the function is applied to the condition, the index can be effective, the above statement is changed to the following statement, you can search by index.

SQL code

1. SELECT * from the staff where birthdate < (to_date (' 01-may-82 ') + 0.9999);

4. Comparing data types that do not match
Comparing data types that do not match is also one of the hard-to-find performance issues.
In the following example, dept_id is a VARCHAR2 field with an index on this field, but the following statement performs a full-table scan.

SQL code

1. SELECT * FROM dept where dept_id = 900198;

This is because Oracle automatically converts the WHERE clause to To_number (dept_id) = 900198, which is what 3 says, which limits the use of the index.
You can use the index by changing the SQL statement to the following form

SQL code

1. SELECT * FROM dept where dept_id = ' 900198 ';

And there's the article on Lao Wang's blog.

Http://hi.baidu.com/thinkinginlamp/blog/item/9940728be3986015c8fc7a85.html

Http://hi.baidu.com/thinkinginlamp/blog/item/a352918fe70d96fd503d925e.html

http://blog.csdn.net/fbd2011/article/details/7341312

http://blog.csdn.net/lovelyhermione/article/details/4580866

1. General Index

The only task for a normal index (an index defined by the keyword key or index) is to speed up access to the data. Therefore, you should only create indexes for the columns of data that most often appear in the query condition (wherecolumn=) or Sort condition (orderbycolumn). Whenever possible, you should choose a data column that is the most tidy and compact data (such as an integer type of data column) to create an index.

2. Unique index

A normal index allows the indexed data column to contain duplicate values. For example, because a person might have the same name, it may occur two or more times in the same employee profile data table.

If you can determine that a data column will contain only values that are different from each other, you should define it as a unique index with the keyword unique when creating an index for that data column. The advantage of this is that it simplifies MySQL's management of the index, which makes it more efficient, and that MySQL automatically checks to see if the value of this field in the new record has already appeared in the field of a record when a new record is inserted into the data table; MySQL will refuse to insert that new record. In other words, a unique index guarantees the uniqueness of the data record. In fact, in many cases, the goal of creating a unique index is often not to improve access speed, but to avoid duplication of data.

3. Primary index

It has been repeated several times before: You must create an index for the primary key field, which is called the "primary Index". The only difference between a primary index and a unique index is that the first keyword used in the definition is primary rather than unique.

4. Foreign KEY Index

If you define a FOREIGN key constraint for a foreign key field, MySQL defines an internal index to help you manage and use foreign key constraints in the most efficient way.

5. Composite Index

An index can overwrite multiple data columns, such as an index (COLUMNA,COLUMNB). The feature of this index is that MySQL can selectively use one such index. If the query operation requires only one index on the ColumnA data column, you can use the composite Index, index (COLUMNA,COLUMNB). However, this usage applies only to combinations of data columns that are arranged in the composite index. For example, index (A,B,C) can be used as an index of A or (b), but not as an index of B, C, or (b,c).

6, the length of the index

When you define an index for a data column of char and varchar type, you can limit the length of the index to a given number of characters (this number must be less than the maximum number of characters allowed for this field). The advantage of this is that you can generate an index file that is smaller in size and faster to retrieve. In most applications, the string data in the database is dominated by a variety of names, and the length of the index is set to 10~15 characters enough to narrow the search to a few data records. When you create indexes on data columns of BLOB and text type, you must limit the length of the index; The maximum index allowed by MySQL a normal index on a text field of a full-text index can only speed up the retrieval of the string that appears at the front of the field, that is, the character at the beginning of the field content. If a field contains a large paragraph of text consisting of several or even multiple words, the normal index does not work. This kind of retrieval often appears in the form, which is very complex for MySQL, and if the amount of data to be processed is large, the response time will be very long.

Such occasions are where full-text indexing (Full-textindex) can be used. When this type of index is generated, MySQL creates a list of all the words that appear in the text, and the query operation retrieves the relevant data records based on the list. A full-text index can be created with a data table, or it can be added using the following command when necessary later:

Altertabletablenameaddfulltext (COLUMN1,COLUMN2) has a full-text index, you can use the Select query command to retrieve data records that contain one or more given words. The following is the basic syntax for this type of query command:

Select*fromtablename

Wherematch (Column1,column2) against (' Word1 ', ' word2 ', ' Word3 ')

The above command will query all data records for Word1, Word2, and Word3 in the Column1 and Column2 fields.

Attach a practice (200,000 data):

Mysql-Uroot-p123456

UseVlcmarket
SelectCount(*) FromVlc_caiji_detail_1;
Select * FromVlc_caiji_detail_1whereCid=934 andDays=3;
0.09sec

Select * FromVlc_caiji_detail_1whereCid=5 andDays=8;
0.08sec


Index:
Select * FromVlc_caiji_detail_1whereCid=934 andDays=3;
0.03
Select * FromVlc_caiji_detail_1whereCid=5 andDays=8;
0.02


Select * FromVlc_caiji_detail_1whereCid=734 andDays=7;
0.01
Select * FromVlc_caiji_detail_1whereCid=30 andDays=1;
0.01


Composite Index
CREATE INDEX cid_days on Vlc_caiji_detail_1(Cid,Days);

Select * from Vlc_caiji_detail_1 where Cid=934 and days =3 0.00sec

select * from Vlc_caiji_detail_1 where Cid=5 and Days=8 0.00sec

Summary: Composite index The most ox c so you know ...

MySQL composite index, General index summary