Database Query Optimization Mysql index _ MySQL

I have been working for a year. It is also the first time I have used Mysql indexes. I was amazed at the speed improvement after I added an index. When the old employee next door saw my big fuss, he calmly replied, surely? The index is the most important factor for optimization. For a small amount of work for a year, it is also the first time to use the Mysql index. I was amazed at the speed improvement after I added an index. When the old employee next door saw my big fuss, he calmly replied, "That's sure ".

For any DBMS, indexing is the most important factor for optimization. For a small amount of data, the impact of no suitable index is not very big, but as the data volume increases, the performance will drop sharply.

Xiao baoge tried it. In the 25 thousand data table, there was no index: 200 ms-700 ms. After the index was added, 10 ms-15 ms. redis cache was used for 1 ms-7 ms, if the data size is large, the index effect will be more obvious. What's more, multi-table queries.

Indexing principles

1. In addition to dictionaries, examples of indexing can be seen everywhere in daily life, such as train stations, trains, and directories of books. They work in the same way. By Constantly narrowing down the scope of data to be obtained, we can filter the final results and turn random events into ordered events, that is, we always lock data through the same search method.

The same is true for databases, but it is much more complex because not only equi-type queries, but also range queries (>, <, between, in) and fuzzy queries (like) and Union query (or. What methods should the database choose to deal with all the problems? Let's look back at the dictionary example. Can we divide the data into segments and then perform segmented queries? The simplest way is to divide 1000 to 100 data entries into the first segment, 101 to 200 into the second segment, and 201 to 300 into the third segment ...... In this way, you only need to find the third row of 250th data records. All of a sudden, you can delete 90% invalid data records. But what if it is a 10 million record? What is better to divide it? The average complexity of the search tree is lgN, which has good query performance. However, we ignore a key issue here. The complexity model is based on the same operation cost each time. The database implementation is complicated and the data is stored on the disk. To improve performance, you can read part of the data into the memory each time for computing. because we know that the disk access cost is about 100,000 times the memory access cost, it is difficult for a simple search tree to meet complex application scenarios.

2. In addition, for example, in the student information table, the index of the Student name is sorted by name. Now, when searching for a student information, you do not need to search for the entire table row by row. You can use the index to perform an ordered search (such as the binary search method) and quickly locate the matched value, to save a lot of search time.

3. When the data volume is very large and the query involves multiple tables, using indexes often speeds up the query by thousands of times.
For example, three unindexed tables t1, t2, and t3 contain only columns c1, c2, and c3. Each table contains 1000 rows of data, which is 1 ~ The value of 1000 is as follows.

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

The query result should be 1000 rows. Each row contains three equal values. To process this query without an index, you must search for all the combinations of the three tables to obtain the rows that match the WHERE clause. The possible number of combinations is 1000x1000x1000 (Billions), and the query will obviously be very slow.

If you index each table, the query process can be greatly accelerated. The index query process is as follows.

(1) Select the first row from Table t1 to view the data contained in this row.

(2) Use the index on table t2 to directly locate the row that matches the value of t1 in Table t2. Similarly, you can use the indexes on table t3 to directly locate the rows in Table t3 that match the values from t1.

(3) scan the next row of table t1 and repeat the previous process until all the rows in Table t1 are traversed.

In this case, a full scan is still performed on table t1, but the rows in these tables can be directly retrieved through index search on table t2 and table t3, which is 1 million times faster than when no index is used.

Using indexes, MySQL accelerates the WHERE clause's search for rows that meet the condition conditions. In multi-table join queries, MySQL accelerates row matching in other tables during join execution.

Index type

MySQL indexes include general indexes, unique indexes, full-text indexes, single-column indexes, multi-column indexes, and spatial indexes.

1. Common Index

When creating a common index, no restrictions are attached. This type of index can be created in any data type. Whether its value is unique or not is determined by the integrity constraints of the field. After an index is created, you can use the index to query it. For example, create a normal index on the stu_id field of the student table. You can query a record based on the index.

2. Unique Index

You can use the UNIQUE parameter to set an index as a UNIQUE index. When creating a unique index, the value of the index must be unique. For example, if you create a unique index in the stu_name field of the student table, the value of the stu_name field must be unique. A unique index can be used to quickly determine a record. A primary key is a special unique index.

3. Full-text index

You can use the FULLTEXT parameter to set the index as a full-text index. Full-TEXT indexes can only be created on CHAR, VARCHAR, or TEXT fields. When querying string fields with a large amount of data, you can use full-text indexes to increase the query speed. For example, the information field in the student table is of the TEXT type and contains a lot of TEXT information. After you create a full-text index on the information field, you can query the information field more quickly. MySQL databases support full-text indexing since version 3.23.23, but only the MyISAM storage engine supports full-text retrieval. By default, full-text index search execution is case-insensitive. However, after the index columns are binary sorted, you can perform a case-sensitive full-text index.

4. Single Column Index

Create an index on a single field in the table. A single column index is indexed only based on this field. A single-column index can be a common index, a unique index, or a full-text index. Make sure that the index corresponds to only one field.

5. Multi-column Index

A multi-column index creates an index on multiple fields in the table. This index points to multiple fields corresponding to the creation. You can query these fields. However, the index is used only when the first field is used in the query condition. For example, if you create an index with multiple columns on the id, name, and sex fields of a table, the index is used only when the id field is used in the query condition.

6. spatial indexes

You can use the SPATIAL parameter to set the index as a SPATIAL index. Spatial indexes can only be created on spatial data types, which can improve the efficiency of the system in obtaining spatial data. The Spatial Data Types in MySQL include GEOMETRY, POINT, LINESTRING, and POLYGON. Currently, only the MyISAM storage engine supports spatial retrieval, and the index field cannot be null. For beginners, such indexes are rarely used.

Index operations

1. Add the primary key (primary key index)

mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2. add UNIQUE (UNIQUE index)

mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3. Add an INDEX (Common INDEX)

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4. Add FULLTEXT (full-text index)

mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5. Add multi-column Indexes

mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Create an index

You can CREATE an INDEX when executing the create table statement, or use the create index or alter table statement to add an INDEX to the TABLE.

1．ALTER TABLE

Alter table is used to create a common 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)

Table_name is the name of the table to be indexed, and column_list indicates which columns are indexed. When multiple columns are indexed, they are separated by commas. The index name index_name is optional and is time-saving. MySQL assigns a name based on the first index 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 can add normal or UNIQUE indexes to a table.

CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)

Table_name, index_name, and column_list have the same meaning as the alter table statement, and the index name is not optional. In addition, you cannot use the create index statement to CREATE a primary key index.

3. Index type

When creating an index, you can specify whether the index can contain duplicate values. If not, the index should be created as a primary key or UNIQUE index. For single-column uniqueness indexes, this ensures that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is not repeated.
The primary key index is very similar to the UNIQUE index. In fact, the primary key index is only a UNIQUE index with the name PRIMARY. This indicates that a table can only contain one primary key, because a table cannot have two indexes with the same name.

The following SQL statement adds the 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 processed as a statement in alter table. The syntax is as follows.

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

The first two statements are equivalent. The index index_name in table_name is deleted.

The first statement is only used to delete the primary key index. Because a table only has one primary key index, 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 deleted from the table, the index is affected. If you delete a column in an index with multiple columns, the column is also deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

Note: by default, databases generate indexes for primary keys.

The above is the content of Mysql index _ MySQL optimized for database query. For more information, see PHP Chinese Network (www.php1.cn )!