MySQL index Operation Command learning notes

Next let's take a look at the notes on MySQL index operation commands. This article includes the creation, deletion, modification, and viewing of mysql indexes.

Mysq index type:Common Index, unique index, and primary index

1. Common Index

The only task of a common INDEX (INDEX defined by the KEY or INDEX keyword) is to speed up data access. Therefore, the query condition (WHERE column =…) must be ...) Or create an index for the data column in the order by column. If possible, you should select the most neat and compact data column (such as an integer data column) to create an index.

2. Unique Index

Normal indexes allow indexed data columns to contain duplicate values. For example, because a person may have the same name, the same name may appear twice or more times in the same "employee profile" data table.
If you can determine that a data column will only contain different values, you should use the keyword UNIQUE to define it as a UNIQUE index when creating an index for this data column. The advantage of doing so: First, it simplifies MySQL's management of this index, and this index becomes more efficient. Second, MySQL inserts a data table with a new record, automatically checks whether the value of this field of the new record has already exists in this field of a record; if yes, MySQL rejects the insert of that new record. That is to say, the unique index can ensure the uniqueness of data records. In fact, in many cases, the purpose of creating a unique index is not to speed up access, but to avoid data duplication.

3. Primary Index

I have already repeatedly stressed that an index must be created for the primary key field. This index is called a "primary index ". The only difference between a PRIMARY index and a UNIQUE index is that the keywords used by the former during definition are PRIMARY rather than UNIQUE.

4. Foreign Key Index

If a foreign key constraint is defined 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

Indexes can cover multiple data columns, such as INDEX (columnA, columnB) indexes. This index features that MySQL can selectively use such an index. If you only need to use an INDEX on the columnA data column for the query operation, you can use a composite INDEX (columnA, columnB ). However, this method is only applicable to the combination of data columns in the composite index. For example, INDEX (A, B, C) can be used as an INDEX of A or (A, B), but cannot be used as B, C or (B, C).

6. Index Length

When defining indexes for CHAR and VARCHAR data columns, you can limit the index length to a given number of characters (This number must be less than the maximum number of characters allowed by this field ). The advantage of this is that you can generate an index file with a relatively small size and fast retrieval speed. In most applications, most of the string data in the database is based on a variety of names, and the index length is set to 10 ~ 15 characters is enough to narrow the search range to a few data records.

The maximum index length allowed by MySQL is 255 characters.

Full-text index

A common index on a text field can only accelerate the retrieval of strings (characters starting with the field content) at the top of the field content. If a field contains a large text segment consisting of several or even multiple words, the normal index will be useless. This kind of search usually appears in the form of LIKE % word %, which is very complicated for MySQL. If the amount of data to be processed is large, the response time will be very long.

This type of scenario is where full-text indexes can be used. When an index of this type is generated, MySQL creates a list of all the words that appear in the text, and searches for relevant data records based on the list. The full-text index can be created along with the data TABLE, or you can use the following command to ADD it later if necessary: alter table tablename add fulltext (column1, column2) has full-text index, you can use the SELECT query command to retrieve data records containing one or more given words.

Create an index

The syntax for creating an index in MySQL is as follows:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING index_type]
ON table_name (index_col_name ,...)

The corresponding syntax variable information is as follows:

[UNIQUE | FULLTEXT | SPATIAL]
The three keywords in brackets indicate the index types created. They indicate the unique index, full-text index, and spatial index. If no keyword is specified, the default value is normal index.
Index_name
Index_name indicates the index name, which is defined by the user for later management operations such as modifying the index.
Index_type
Index_type indicates the specific implementation method of indexes. in MySQL, there are two different forms of indexes-BTREE indexes and HASH indexes. In tables with the storage engine MyISAM and InnoDB, only BTREE can be used. The default value is BTREE. In tables with the storage engine MEMORY or HEAP, the HASH and BTREE indexes can be used, the default value is HASH.
Index_col_name
Index_col_name indicates the name of the field to be indexed. You can also create a composite index for multiple fields. You only need to separate multiple field names with commas.

In addition, for fields of the CHAR or VARCHAR type, we can create an index using only the first part of the field content. We only need to add a length after the corresponding field name, such) you only need to use the length character before the field content to create an index. Here, we use the username field in the User table (type: VARCHAR (50) as an example to create an index using the six-character prefix of the username field.

Create index idx_user_username ON user (username (6 ));

Because the first six characters of most fields are usually different, this index is not much slower than the index created using all the content of the field. In addition, using part of the field to create an index can greatly reduce the index file, which saves a lot of disk space and may increase the INSERT operation speed.

In MySQL, the prefix length is up to 255 bytes. For data tables whose storage engine is MyISAM or InnoDB, the prefix is up to 1000 bytes.

Note that in MySQL, for fields of the big data type such as TEXT and BLOB, the prefix length (length) must be given before the index can be created successfully.

NOTE 1: The index creation syntax also has the following variants:

The Code is as follows:	Copy code
Alter table table_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

NOTE 2:
In MySQL, only when the storage engine of a data table is MyISAM, InnoDB, or BDB, you can add an index to a column with a NULL value or a column with a data type of TEXT or BLOB.
Delete Index

The method for deleting an index in MySQL is very simple. The complete syntax is as follows:

The Code is as follows:	Copy code
-- Delete the index with the specified name in the specified table Alter table table_name Drop index index_name;

Here, we write an SQL statement to delete the index idx_user_username in the above index creation example. The Code details are as follows:

The Code is as follows:	Copy code
-- Delete the index named idx_user_username Alter table user Drop index idx_user_username;

Modify Index

MySQL does not provide direct instructions for modifying indexes. In general, we need to delete the original index and create an index with the same name as needed, this allows you to modify indexes in disguise.

The Code is as follows:	Copy code
-- Delete first Alter table user Drop index idx_user_username; -- Create an index with the same name as the modified content Create index idx_user_username ON user (username (8 ));

View Indexes

In MySQL, to view the indexes in a database table, you only need to use either of the following two commands.

The Code is as follows:	Copy code
-- If the user db_name or other commands are not used to specify a specific database before viewing the index, the FROM db_name must be added. Show index from table_name [FROM db_name] -- If you do not use user db_name or other commands to specify a specific database before viewing the index, you must add the db_name. prefix. Show index from [db_name.] table_name