Index operations for MySQL

An index is a special database structure that can be used to quickly query specific records in a database table. Indexes are an important way to improve database performance. In MySQL, all data types can be indexed. MySQL's indexes include normal indexes, uniqueness indexes, full-text indexes, single-column indexes, multicolumn indexes, and spatial indexes.

An index is a structure that sorts the values of a single column or multiple columns in a database. Applying the index can greatly improve the speed of the query. The user queries the data through the index, not only can improve the query speed, also can reduce the server load. When the user queries the data, the system can not traverse all the records in the data table, but simply query the index columns. The data query of the general process is realized by traversing all the data and looking for matching records in the database. An index is like a book's directory compared to a general form of a query. When a user looks up data through an index, it is like a user querying a section of a knowledge point through a directory. This helps the user to effectively improve the search speed. Therefore, the use of indexes can effectively improve the overall performance of the database system.

When applying a MySQL database, it is not always necessary for the user to apply an index to refine the query when querying the data. There are two sides to everything. Using an index can improve the speed of retrieving data, but creating and maintaining indexes is time -consuming and proportional to the size of the data, and the indexes need to occupy physical space, causing a lot of trouble in maintaining the data.

Overall, indexes can increase the speed of queries, but affect the user's insert operations. Because, when you insert records into an indexed table, the database system sorts by index. Therefore, the user can insert the data after the index is deleted, and the user can re-create the index when the data insert operation is complete.

The

Different storage engines define the maximum number of indexes and the maximum index length for each table. , with a total index length of at least 256 bytes. Some storage engines support more indexes and larger index lengths.

1. A normal index, that is, an index that does not apply any restrictions, which can be created in any data type. The constraint on the field itself can determine whether its value is empty or unique. After the type index is created, the user can query through the index.
2. Unique index, you can set a unique index using the unique parameter. When the index is created, the value of the index must be unique, and with a unique index, the user can quickly locate a record and the primary key is a special unique index.
3. Full-text indexing, using 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, query for fields with article reply content. You can apply the full-text indexing method. It is important to note that, by default, the full-text index case is not sensitive. If the indexed column uses a binary sort, you can perform a case-sensitive full-text index.
4. A single-column index is the index of only one field. It can include three kinds of indexing methods described above. The criteria for applying the index only need to guarantee that the index value corresponds to a field.
5. A multicolumn index is an index that is created on more than one field in a table. The index points to multiple fields that are created at the time that the user can query through these fields. To apply the index, the user must use the first field in these fields.
6. Spatial indexes 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. Only the MyISAM storage engine in MySQL supports spatial retrieval, and the indexed fields cannot be null.

Create an index when creating a table

Create Table [If not exists]data table name (' ID 'int( One) unsigned not NULLauto_increment, ' hotel_id 'int( One) unsigned not NULLCOMMENT'ID of the hotel ID hotel_base', ' Created_at 'datetime  not NULL DEFAULTNow () COMMENT'creation Time', ' Updated_at 'datetime  not NULL DEFAULTNow () COMMENT'Update Time',[unique| Fulltext| SPATIAL] index|KEY[aliases](Property name 1[(length)] [asc| DESC])PRIMARY KEY(' id '),KEY' index_hotel_id ' (' hotel_id ')) COMMENT='Hotel Service Commitment Information'ENGINE=InnoDBDEFAULTCHARSET=UTF8 COLLATE utf8_unicode_ci;

The parameters have the following meanings:

Unique: An optional parameter that indicates that the index is a unique index.

Fulltext: Optional parameter that indicates that the index is full-text search.

Spatial: An optional parameter that indicates that the index is a spatial index.

The index and key parameters are used to specify the field index, and when the user chooses, only one of them can be selected. In addition, the alias is an optional parameter, and the role is to name the created index the new name.

Property name 1: Refers to the field name of the index, which must be pre-defined.

Length: optional parameter, which refers to the length of the index and must be a string type to be used .

ASC/DESC: Optional parameters, ASC for ascending order, DESC for descending order.

Create Table  Int (primarykeynotnullvarchar ( (+) ,varchar (+),UNIQUEINDEXASC) );

Create TableTelephone (IDint( One)Primary KeyAuto_increment not NULL, namevarchar( -) not NULL, Telvarchar( -) not NULL,IndexTel_num (Tel ( -)), the field length in the data table is 50, and the index is created with a field length of 20, which is designed to improve query efficiency and query speed. 

Create TableInformation (IDint( One) auto_incrementPrimary Key  not NULL, namevarchar( -) not NULL, Sexvarchar(5) not NULL, Birthdayvarchar( -) not NULL,INDEXinfo (name, sex)); In a multicolumn index, the index is used only if the first field in those fields is used in the query criteria (that is, the name field in the example above). 

Create an index in an established data table

CREATE [unique| Fulltext| SPATIAL] INDEX index_name on table_name (properties [(length)] [asc| DESC]);

The parameters of the command are described below:

Index_name is the index name, which is the role of assigning a new name to the user-created index.

TABLE_NAME is the table name, which specifies the name of the table where the index is created.

Length is an optional parameter that specifies the index length.

The ASC and DESC parameters, which specify the sort order of the data table.

As with creating an index when a data table is created, creating an index in an established data table also contains 6 indexes.

Normal index CREATE index stu_info on studentinfo (SID);

Unique index CREATE unique index index1_id on index1 (CID);

Full-text index CREATE fulltext index index2_info on INDEX2 (info);

Single-column index CREATE index index3_addr on Index3 (Address (4));

Multi-column Index CREATE index index4_na on index4 (name, address);

Spatial index CREATE Spatial index name on data table name (field name);

modifying indexes

Modifying an index on a table that already exists can add an index to the data table through the Altertable statement, with the following basic structure:

ALTER TABLE table_name ADD [unique| Fulltext| SPATIAL] INDEX index_name (property name [(length)][asc| DESC]);

ALTER TABLE Studentinfo ADD INDEX Timer (Time (20)):

Delete Index

In MySQL, after an index is created, the index of the specified table can be deleted if the user no longer needs the index. Because these already established and infrequently used indexes can consume system resources on the one hand, and may cause the update speed to decrease, which greatly affects the performance of the data table. Therefore, you can manually delete the specified index when the user does not need the index for the table. Dropping an index can be done with a drop statement

DROP INDEX index_name on table_name;

Where the parameter index_name is the name of the index that the user needs to delete, and the parameter table_name specifies the data table name.

Index operations for MySQL