MySQL Index base operation Summary (iv) _mysql

1. Why Use indexes:
The index in the database object is actually similar to the directory of the book, mainly to improve the speed of retrieving data from the table. Because the data is stored in a database table, the index is created on a database table object, consisting of a field in a table or a key generated by several fields that are stored in a data structure (a B-tree or hash table) that can be quickly and efficiently found by using MySQL to find the field associated with the key. Depending on the storage type of the index, you can divide the index into B-Tree index (btree) and hash index (hash). Note: The InnoDB and MyISAM storage engines support the Btree type index, and the memory storage engine supports the hash type index, which defaults to the former index.
MySQL supports 6 kinds of indexes, namely, normal index, unique index, FULL-TEXT Index, Single-column index, multiple-column index, spatial index.
The following conditions are appropriate for creating an index:
1. Fields that are frequently queried. The field that appears in the WHERE clause.
2. In the grouped field, that is, the field that appears in the GROUP BY clause.
3. A federated query between the child table of the dependency and the parent table, which is the primary key or foreign key field.
4. Set a field with a unique full constraint.

2. Create and view indexes:
index operations include creating indexes, viewing indexes, and deleting indexes. To create an index is to index a field or multiple fields in a table, in MySQL, you can usually create indexes in three ways, create indexes when creating tables, create indexes on tables that already exist, and create indexes from SQL statements ALTER TABLE.
2.1 Create and view normal indexes:
A common index is when you create an index without attaching any restrictions (unique, non-empty, and so on). Indexes of this type can be created on any type of field.
2.1.1 Create a normal index when creating a table:

The syntax format is as follows:

CREATE TABLE table_name (
 property name data type,
 property name data type,
 ...
 ) Property name data type,
 index| KEY [index name] (property name 1 [(length)] [asc| DESC]) 
);

Description: The index or key parameter is used to specify the field as an index, the indexed name parameter is used to specify the name of the index being created, and the property name 1 parameter is used to specify the name of the field associated with the index, and the length argument is used to specify the length of the index, "asc| The DESC parameter is used to specify ascending or descending order.
Note: When you create an index, you can specify the length of the index. This is because different storage engines define the maximum number of indexes and the maximum index length for the table. The storage engine that MySQL supports supports at least 16 indexes per table, with a total index length of at least 256 bytes.

Example:

CREATE TABLE t_dept (
 deptno INT,
 dname VARCHAR (),
 Loc VARCHAR (),
 INDEX Index_deptno (DEPTNO)
);

Description
You can see if the index was successfully created by show create TABLE t_dept \g;
You can verify that the index in the database table is used by EXPLAIN SELECT * from T_dept WHERE deptno=1\g. If the value in the Possible_keys and key fields in the execution result is Index_deptno for the index name that is created, the index already exists and is enabled.

2.1.2 Create a normal index on a table that already exists:

The SQL statement CREATE index implementation is implemented in the form of the following syntax:
CREATE Index Index name
On table name (property name [(length)] [adc| DESC])

2.1.3 creates a normal index through an SQL statement alter TABLE:

The grammatical form is:
ALTER TABLE table_name ADD index| KEY Index Name (property name [(length)] [asc| DESC]);

2.2 Create and view unique indexes:
A unique index is that when an index is created, the value of the restricted index must be unique. This type of index enables you to query a record more quickly. In MySQL, depending on how the index is created, it can be divided into automatic indexing and manual indexing.
An automatic index is an index that is automatically created by the system when an integrity constraint is set in the database table. Manual indexing refers to manually creating an index on a table. When you set a field in a table as a primary key or a unique integrity constraint, the system automatically creates a unique index that associates the field.

2.2.1 Create a unique index when creating a table:

The grammatical form is:

CREATE TABLE table_name (
 property name data type,
 property name data type,
 ...
 ) Property name data type,
 UNIQUE index| KEY [index name] (property name 1 [(length)] [ASC | DESC])
);

Description: Unique index or unique key means creating unique indexes.

2.2.2 Creates a unique index on a table that already exists:

The SQL statement is used to create the UNIQUE index, and the syntax is:
CREATE UNIQUE Index index name
On table name (property name [(length)] [asc| DESC]);

2.2.3 creates a unique index through an SQL statement ALTER TABLE:

The grammatical form is:
ALTER TABLE table_name ADD UNIQUE index| KEY Index Name (property name [(length)] [asc| DESC]);

2.3 Create and view Full-text indexes:
Full-text indexing is primarily associated with fields of data type char, varchar, and text so that you can quickly query for fields of string types with large data volumes. MySQL can only create a full-text engine on a database table that is MyISAM by the storage engine. By default, the Full-text engine's search execution is case-insensitive, and if the Full-text engine is associated with a binary data type, it is executed in a case-sensitive manner.

2.3.1 Create a Full-text index when a table is created:

The grammatical form is:

CREATE TABLE table_name (
 property name data type,
 property name data type,
 ...
 ) Property name data type,
 fulltext index| KEY [index name] (property name 1 [(length)] [asc| DESC])
);

2.3.2 Create a Full-text index on a table that already exists:

The grammatical form is:
CREATE Fulltext Index name
On table name (property name [(length)] [asc| DESC]);

2.3.3 creates a Full-text index through an SQL statement alter TABLE:

The grammatical form is:
ALTER TABLE table_name
ADD Fulltext index| KEY Index Name (property name [(length)] [asc| DESC]);

2.4 Create and view multiple-column indexes:
A multiple-column index is a field that is not a field but a field when an index is created. Although you can query through the fields that are associated, a multiple-column index is used only if the first field in the associated field is used in the query criteria.
2.4.1 Create a multiple-column index when creating a table:

The grammatical forms are as follows:

CREATE TABLE table_name (
 property name data type,
 property name data type,
 ...
 ) Property name data type,
 index| KEY [index name] 
 (property name 1 [(length)] [asc| DESC]),
 ...
 (Property name 1 [(length)] [asc| DESC])
);

When the preceding statement creates an index, the associated field is at least greater than one field.

2.4.2 creates a multiple-column index on a table that already exists:

The grammatical form is:

CREATE index index name on table name (
 property name [(length)] [asc| DESC],
 ...
 Property name n [(length)] [asc| DESC]
); 

2.4.3 creates a multiple-column index from the SQL statement altre TABLE:

The grammatical form is:
ALTER TABLE table_name ADD index| KEY Index Name (property name [(length)] [asc| DESC], property name n [(length)] [asc| DESC]);

3. Delete index:

Delete the syntax form of the index:
DROP INDEX index_name on table_name

4. View index:

View the syntax of the index:
Show INDEX from table_name

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.