MySQL's index

For an understanding of the index refer to this post:

Http://www.cnblogs.com/newpanderking/p/3781043.html

Types of indexes and creating posts:

http://blog.csdn.net/xluren/article/details/32746183

Advantages and disadvantages of the index:

MySQL Tutorial: The use of indexes and the pros and cons of indexes how to create MySQL indexes and the pros and cons of indexes:

1. Index is the data structure that helps MySQL to obtain data efficiently. It is critical for high performance, but people often forget or misunderstand it. An index is more important when the data is larger. Small, lightweight databases can have good performance even when they are not indexed, but performance drops quickly when the data increases. Tip: Worm replication, you can quickly copy a large number of data examples: INSERT INTO EMP SELECT * from EMP;

2. Common indexes in MySQL

Normal index

Unique index

Primary key Index

Combined index

Full-Text Indexing

FOREIGN key (only supported by the InnoDB storage engine)

2.1 Normal Index: This is the most basic index, it does not have any restrictions. There are several ways to create the following:

CREATE INDEX IndexName on tablename (username (length));

Modify table structure alter tablename ADD INDEX IndexName (username (length)) Tip:length can be less than the actual length of the field, if it is a blob and TEXT type, you must specify length, same as

When creating the table, specify the CREATE TABLE mytableuuu (ID INT NOT NULL, username VARCHAR (+) NOT NULL, INDEX IndexName (username (length))); CREATE TABLE mytable (id INT not null,username VARCHAR (+) not NULL); Create index index1 on mytable (ID); Create a normal index

Delete indexes: Drop index index1 on mytable; There is a concept, row definition: defined when declaring a field (column), such as primary key table definition: Defined after all fields (columns) are declared, such as primary Key,index CREATE TABLE mytable (id INT not NULL, Username VARCHAR (+) not Null,index index1 (username)); 3.0 Unique index column values must be unique, but null values are allowed. 1) Creating index: Create unique index indexname on tableName (tablecolumns (length)) 2) Modify table structure: Alter tableName ADD UNIQUE [IndexName] On (Tablecolumns (length) 3) directly specified when creating the table: Create TABLE TableName ([...], UNIQUE [IndexName] (tablecolumns (length)); 4.0 primary key index (primary key) It is a special unique index and is not allowed to have null values. The primary key index is typically created at the time of the Build table: CREATE TABLE MyTable (ID INT not NULL, username VARCHAR (+) NOT NULL, PRIMARY key (ID)); Of course, you can also use the ALTER command. Tip: Remember that a table can have only one primary key. The primary key index is what we call the primary key. In one table, the primary key can only have one, but the normal index and the unique index can have multiple. 5.0 Combined Index 5.1 Multi-column index, which consists of multiple columns together to form an index. Add Combination index ALTER TABLE mytable add index name_city_age (username,city,age); For a composite index, you must prefix the leftmost index, and then arrange it in order to use the combined index, which cannot have an interval between them. The following can be used to combine indexes:

Username,city,age

Usernname,city

Usernname The following cannot be used on a composite index:

City,age

City

Age takes note of the combined index, and if there are multiple indexes in a table, we can consider using a composite index to optimize it.

5.2 Explain statement: You can query the index type explain statement used by the SQL query statement

6. Why is there an index and the query accelerates? This is because creating an index can greatly improve the performance of the system. First, by creating a unique index, you can guarantee the uniqueness of each row of data in a database table. Second, it can greatly speed up the retrieval of data, which is the main reason for creating indexes. Thirdly, the connection between tables and tables can be accelerated, particularly in terms of achieving referential integrity of the data. Finally, when using grouping and sorting clauses for data retrieval, you can also significantly reduce the time to group and sort in queries. By using the index, we can improve the performance of the system by using the optimized hidden device in the process of querying. 6.1 In MySQL, BTREE, binary tree two tree sort 35 17 39 9 28 65 56 87 6.2 Advantages of the index: speed up the query. 6.2 Disadvantages of indexing: taking up a lot of disk space. However, there are effects on insertions, deletions, and updates. 6.3 When using the index, there are some tips and considerations:

The index will not contain a column with null values as long as the column contains null values that will not be included in the index, and this column is invalid for this composite index as long as there is a column in the composite index that contains a null value. So we don't want the default value of the field to be null when the database is designed.

Index the string column using a short index, if possible, specify a prefix length. For example, if you have a column of char (255), and if the majority value is unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only improve query speed but also save disk space and I/O operations.

Index column sort the MySQL query uses only one index, so if an index is already used in the WHERE clause, the column in order by is not indexed. So do not use sort operations where the default sorting of the database is acceptable, and try not to include multiple columns, if you need to create a composite index for those columns.

Like statements generally do not encourage the use of like operations, if not used, how to use is also an issue. Like "? a%" does not use indexes, while like "aaa%" can use indexes.

Do not operate on columns select * from the users where year (adddate) <2007; The operation will be performed on each line, which will cause the index to fail with a full table scan, so we can change to select * from users where adddate< ' 2007-01-01 ';

Do not use not in and <> operations

The most common scenario is to build an index for the field that appears in the WHERE clause. To facilitate the narration, we first set up a table as follows.

The code codes are as follows:

CREATE TABLE MyTable ( ID Serial primary Key, category_id int NOT NULL default 0, USER_ID int NOT NULL default 0, adddate int NOT NULL default 0 );

It's easy, but it's enough to illustrate the problem. If you use a statement similar to the following at query time:

SELECT * FROM MyTable WHERE category_id=1;

The most straightforward response is to create a simple index for category_id:

CREATE INDEX Mytable_categoryid
On MyTable (category_id);

OK, get it done? Don't be happy, if you have more than one choice. For example:

SELECT * FROM MyTable WHERE category_id=1 and user_id=2;

Your first reaction might be to create an index for user_id. Not good, this is not the best way. You can build multiple indexes.

CREATE INDEX Mytable_categoryid_userid on MyTable (category_id,user_id);

Did you notice my habit of naming it? I use "Table name _ Field 1 _ Field 2 Name" way. You'll soon know why I did it.

Now that you've indexed the appropriate fields, it's a bit of a worry, you might ask, does the database actually use these indexes? Testing is OK, for most databases, this is easy, just use the explain command:

EXPLAIN

SELECT * FROM MyTable
WHERE category_id=1 and user_id=2;

This is what Postgres 7.1 returns (exactly as I expected)

Notice:query PLAN:

Index Scan using Mytable_categoryid_userid on
MyTable (cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

The above is the Postgres data, you can see that the database used an index when querying (a good start), and it is using the second index I created. See the benefits of naming me above, you immediately know it uses the appropriate index.

And then, a little bit more complicated, what if there's an order by sentence? Believe it or not, most databases will benefit from the index when they use the order by.

SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by Adddate DESC;

A little confused, huh? As simple as creating an index for a field in the WHERE clause, an index is also created for the field in the ORDER BY clause:

CREATE INDEX Mytable_categoryid_userid_adddate
On MyTable (category_id,user_id,adddate);

Note: "Mytable_categoryid_userid_adddate" will be truncated to

"Mytable_categoryid_userid_addda"

CREATE

EXPLAIN SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by Adddate DESC;

Notice:query PLAN:

Sort (cost=2.03..2.03 Rows=1 width=16)
-Index Scan using Mytable_categoryid_userid_addda
On MyTable (cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

Look at the output of explain, it seems a bit scary ah, the database did a we do not ask for a sort, this is how the performance is damaged, it seems that we are a bit too optimistic about the database itself, then, to the database a little more hints.

To skip the sorting step, we don't need another index, just change the query statement a little bit. Here's the postgres, we'll give the database an extra hint--in the order BY statement, add the field in the where statement. This is only a technical process and is not necessary because there is actually no sort operation on the other two fields, but if you join, Postgres will know what it should do.

EXPLAIN SELECT * FROM MyTable
WHERE category_id=1 and user_id=2
ORDER by category_id desc,user_id desc,adddate DESC;

Notice:query PLAN:

Index Scan Backward using
Mytable_categoryid_userid_addda on MyTable
(cost=0.00..2.02 Rows=1 width=16)

EXPLAIN

Now we're using the index we expect, and it's pretty smart, knowing that you can start reading from behind the index, avoiding any sort of ordering.

That's a little bit more, but if your database is huge and the daily page requests are millions, I think you'll benefit a lot. But what if you want to make more complex queries, such as querying multiple tables together, especially if the fields in the where constraint are from more than one table? I usually try to avoid this, because the database has to combine everything in each table and then exclude the inappropriate rows, which can be costly.

If you can't avoid it, you should look at each table you want to combine, and use the above strategy to build the index, and then use the explain command to verify that you have used the index you expect. If it is, then OK. If not, you may want to create a temporary table to combine them and use the appropriate index.

It is important to note that too many indexes will affect the speed of updates and insertions because it requires the same update for each index file. For a table that often needs to be updated and inserted, there is no need to index a rarely used where clause, and for smaller tables, the cost of sorting is not significant and there is no need to create additional indexes.

The above is only a few very basic things, in fact, there are a lot of learning, explain alone we can not determine whether the method is the most optimized, each database has its own some of the optimizer, although may not be perfect, but they will be compared in the query of which way faster, in some cases, Indexing is not necessarily fast, for example, when the index is placed in a discontinuous storage space, which increases the load on the read disk, and therefore, which is optimal, should be verified by the actual use of the environment.

In the beginning, if the table is not large, there is no need to index, my opinion is to be indexed when needed, there are also some commands to optimize the table, such as MySQL available "OPTIMIZE table."

In summary, you should have some basic concepts on how to build the right index for your database.

MySQL's index