Database query Optimization--mysql index

Worked for a year and was the first to use the MySQL index. I was amazed by the increase in speed after the index was added. The old staff next door saw my fuss and made a plain return to the phrase, "that's for sure."

For any DBMS, the index is the most important factor for optimization. For a small amount of data, the lack of proper index impact is not very large, but when the amount of data increases, the performance will drop sharply.

Little Pigeon tried, 25,000 data sheet, no index: 200ms-700ms, after adding index 10ms-15ms, using Redis cache 1ms-7ms, if the amount of data is larger, the index effect will be more obvious. Even more, multi-table query.

Indexing principle

1, in addition to dictionaries, life in the example of the index, such as the train station, the list of books and so on. They all work the same way, by shrinking the range of data they want to filter out the results they want, and by turning random events into sequential events, that is, we always lock data by the same search method.

The database is the same, but obviously much more complex, because not only is it facing the equivalent query, but also the scope query (>, <, between, in), Fuzzy query (like), the set query (or), and so on. How should the database choose the way to deal with all the problems? We recall the example of the dictionary, can we divide the data into segments and then query it in segments? The simplest if 1000 data, 1 to 100 is divided into the first paragraph, 101 to 200 is divided into the second paragraph, 201 to 300 is divided into the third paragraph ... This check No. 250 data, as long as the third paragraph can be, all of a sudden to remove 90% of invalid data. But what if it's a 10 million record and it's better to be divided into sections? A little algorithm based on the students will think of the search tree, its average complexity is LGN, with good query performance. But here we overlook a key problem, the complexity of the model is based on the same operating costs each time, the database implementation is more complex, the data is saved on disk, and in order to improve performance, each time you can read some of the data into memory to calculate, because we know that the cost of accessing the disk is about 100,000 times times the amount of access to memory, So a simple search tree is difficult to meet complex application scenarios.

2, in addition, such as Student information table, add student name index, index is sorted on the name. Now, when looking for a student's information, you don't need to search the full table line by row, you can use the index for ordered lookups (such as binary lookup), and quickly navigate to matching values to save a lot of search time.

3, is when the amount of data is very large, when the query involves more than one table, using the index often makes the query speed up to thousands of times.
For example, there are 3 unindexed tables T1, T2, T3, each containing only columns C1, C2, and C3, each containing 1000 rows of data, referring to the value of 1~1000, and the query that looks for the equivalent row of values is shown below.

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

The result of this query should be 1000 rows with 3 equal values per row. To process this query without indexing, you must look for all the combinations of 3 tables in order to derive those rows that match the WHERE clause. The number of possible combinations is 1000x1000x1000 (1 billion), and obviously the query will be very slow.

If you index each table, you can greatly speed up the query process. Queries using the index are handled as follows.

(1) Select the first row from the table T1 to see the data that this row contains.

(2) Use the Index on table T2 to directly locate the row in the T2 that matches the value of T1. Similarly, use the index on table T3 to directly locate rows in T3 that match the values from T1.

(3) Scan the next line of the table T1 and repeat the previous procedure until all the rows in the T1 are traversed.

In this case, a full scan is still performed on table T1, but the ability to index lookups on tables T2 and T3 directly takes rows from those tables, 1 million times times faster than unused indexes.

Using the index, MySQL accelerates the search where the clause satisfies the criteria row, while in a multi-table join query, it speeds up matching rows in other tables when the connection is executed.

Type of index

MySQL's indexes include normal indexes, uniqueness indexes, full-text indexes, single-column indexes, multicolumn indexes, and spatial indexes.

1. Normal index

When you create a normal index, no restrictions are attached. Such an index can be created in any data type, and its value is unique and non-null determined by the integrity constraints of the field itself. After indexing, queries can be queried by index. For example, create a normal index on the stu_id field of the student table. When a record is queried, it can be queried based on that index.

2. Uniqueness Index

Use the unique parameter to set the index to a unique index. When you create a uniqueness index, the value that restricts the index must be unique. For example, if you create a uniqueness index in the Stu_name field of the student table, the value of the Stu_name field must be unique. A unique index allows you to determine a record more quickly. A primary key is a unique index of uniqueness.

3. Full-Text Indexing

Use the fulltext parameter to set the index to full-text indexing. A full-text index can only be created on a field of char, varchar, or text type. Full-text indexing can improve query speed when querying a field of a string type with a large amount of data. For example, the information field of the student table is the text type, which contains a lot of text information. After you establish a full-text index on the information field, you can increase the speed of querying the information field. MySQL database supports full-text indexing starting from version 3.23.23, but only the MyISAM storage engine supports full-text retrieval. By default, the search execution of full-text indexes is case-insensitive. However, after the indexed columns are sorted using binary, a case-sensitive full-text index can be performed.

4. Single-column index

Create an index on a single field in the table. A single-column index is indexed only by that field. A single-column index can be a normal index or a uniqueness index, or it can be a full-text index. Just make sure that the index corresponds to only one field.

5. Multi-column Index

A multicolumn index is an index that is created on more than one field in a table. The index points to multiple fields at the time of creation and can be queried by these fields. However, the index is used only when the first field in these fields is used in the query criteria. For example, if you create a multicolumn index on the ID, name, and sex fields in a table, the index is used only if the query criteria uses the ID field.

6. Spatial index

Use the spatial parameter to set the index to a spatial index. Spatial indexes can only be built on spatial data types, which improves the efficiency of the system in obtaining spatial data. The spatial data types in MySQL include geometry and point, linestring, and polygon. Currently only the MyISAM storage engine supports spatial retrieval, and the indexed fields cannot be null values. For beginners, this type of index is seldom used.

Operation of the Index

1. Add primary key (primary key index)

`table_name` `column` 

2. Add unique (unique index)

`table_name` `column` 

3. Add index (normal index)

`table_name` `column` 

4. Add fulltext (full-text index)

`table_name` `column`)

5. Adding Multi-column indexes

`table_name` `column1`,`column2`,`column3` )

Create an index

You can create an index when you execute the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone.

1. ALTER TABLE

ALTER table is used to create a normal index, a unique index, or a primary key index.

ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)

Where table_name is the name of the table to increase the index, column_list indicates which columns to index, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name based on the first indexed column. In addition, ALTER TABLE allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time.

2. CREATE INDEX

CREATE Index to add a normal or unique index to a table.

CREATEINDEXON table_name (column_list)CREATEINDEXON table_name (column_list)

TABLE_NAME, index_name, and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not selectable. In addition, the primary key index cannot be created with the CREATE INDEX statement.

3. Index type

When you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a primary key or a unique index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.
The PRIMARY key index is very similar to a unique index. In fact, the PRIMARY key index is only a unique index with the name PRIMARY. This means that a table can contain only one primary KEY because it is not possible to have two indexes with the same name in a table.

The following SQL statement adds a primary key index to the students table on the SID.

ALTER TABLE students ADD PRIMARY KEY (sid)

Delete Index

You can use the ALTER TABLE or DROP INDEX statement to delete an index. Similar to the CREATE INDEX statement, DROP Index can be handled as a statement inside ALTER TABLE, with the following syntax.

DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY

Where the first two statements are equivalent, delete the index index_name in table_name.

The 3rd statement is only used when deleting the primary key index, because a table may have only one primary key index, so you do not need to specify the index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index.

If a column is removed from the table, the index is affected. For multiple-column combinations of indexes, if one of the columns is deleted, the column is also removed from the index. If you delete all the columns that make up the index, the entire index is deleted.

Note: The general database will be indexed by default for the primary key

Reference article:
http://blog.csdn.net/yuanzhuohang/article/details/6497021
Http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html
Http://edu.cnzz.cn/201305/88671f51.shtml

Database query Optimization--mysql index