Mysql index basic operation summary (4), mysql Index

Mysql index basic operation summary (4), mysql Index

1. Why index: 
The index in the database object is similar to the book directory, mainly to speed up data retrieval from the table. Because the data is stored in the database table, the index is created on the database table object and consists of keys generated by one or more fields in the table, these keys are stored in the data structure (B-tree or hash table). Through MySQL, you can quickly and effectively find the fields associated with the key. Based on the index storage type, indexes can be divided into B-type tree indexes and HASH indexes ). Note: the InnoDB and MyISAM storage engines Support B-tree indexes. The MEMORY storage engine supports HASH indexes. The default value is the former index.
MySQL supports six types of indexes: Common Index, unique index, full-text index, single-column index, multi-column index, and spatial index.
You can create an index in the following cases:
1. fields that are frequently queried. That is, the field that appears in the WHERE clause.
2. Fields in the GROUP, that is, fields that appear in the group by clause.
3. Joint query between the child table and the parent table with dependency, that is, the primary key or foreign key field.
4. Set fields with unique and complete constraints.

2. Create and view indexes:
Index operations include creating an index, viewing an index, and deleting an index. Creating an index is to create an index on one or more fields in the Table. in MySQL, you can create an index in three ways, create an index when creating a TABLE, create an index on an existing TABLE, and create an index using the SQL statement ALTER TABLE.
2.1 create and view common indexes:
Common indexes are created without any restrictions (unique or non-empty ). This type of index can be created on any type of field.
2.1.1 create a common index when creating a table:

The syntax format is as follows:

Create table table_name (attribute name data type, attribute name data type ,...... attribute name data type, INDEX | KEY [INDEX name] (attribute name 1 [(length)] [ASC | DESC]);

Note: The INDEX or KEY parameter is used to specify the field as the INDEX, and the "INDEX name" parameter is used to specify the name of the created INDEX, the "attribute name 1" parameter is used to specify the name of the field associated with the index, the "length" parameter is used to specify the index length, and the "ASC | DESC" parameter is used to specify the ascending or descending order.
Note: You can specify the index length when creating an index. This is because different storage engines define the maximum index count and maximum index length of a table. The storage engine supported by MySQL supports at least 16 indexes for each table, and the total index length must be at least 256 bytes.

Example:

CREATE TABLE t_dept( deptno INT, dname VARCHAR(30), loc VARCHAR(40), INDEX index_deptno (deptno));

Note:
You can use show create table t_dept \ G to check whether the index is successfully created;
You can use the explain select * FROM t_dept WHERE deptno = 1 \ G; command to verify whether the indexes in the database table are used. If the values in the possible_keys and key fields in the execution result are both the created index name index_deptno, the index already exists and is enabled.

2.1.2 create a common index on an existing table:

Use the SQL statement CREATE INDEX. The syntax format is as follows:
Create index name
ON Table Name (attribute name [(length)] [ADC | DESC])

2.1.3 Using SQL statement ALTER TABLE to create a common index:

Syntax format:
Alter table table_name add index | key index name (attribute name [(length)] [ASC | DESC]);

2.2 create and view a unique index:
The so-called unique index means that when creating an index, the index value must be unique. This type of index allows you to query a record more quickly. In MySQL, indexes can be divided into automatic indexing and manual indexing.
The so-called automatic index means that when integrity constraints are set in the database table, the table will be automatically created by the system. Manual indexing refers to manually creating an index on a table. When a field in the table is set to a primary key or a unique Integrity Constraint, the system automatically creates a unique index associated with the field.

2.2.1 create a unique index when creating a table:

Syntax format:

Create table table_name (attribute name data type, attribute name data type ,...... attribute name data type, unique index | KEY [INDEX name] (attribute name 1 [(length)] [ASC | DESC]);

Note: unique index or unique key indicates creating a unique index.

2.2.2 create a unique index on an existing table:

Use the SQL statement CREATE UNIQUE INDEX. The syntax format is:
Create unique index name
ON Table Name (attribute name [(length)] [ASC | DESC]);

2.2.3 use the SQL statement ALTER TABLE to create a unique index:

Syntax format:
Alter table table_name add unique index | key index name (attribute name [(length)] [ASC | DESC]);

2.3 create and view full-text indexes:
Full-TEXT indexes are mainly associated with fields with CHAR, VARCHAR, and TEXT data types, so that you can query string fields with large data volumes more quickly. MySQL can only create full-text engines on database tables whose storage engine is MyISAM. By default, full-text engine searches are executed in case-insensitive mode. If the fields associated with the full-text engine are of the binary data type, they are executed in case-sensitive mode.

2.3.1 create a full-text index when creating a table:

Syntax format:

Create table table_name (attribute name data type, attribute name data type ,...... attribute name data type, fulltext index | KEY [INDEX name] (attribute name 1 [(length)] [ASC | DESC]);

2.3.2 create a full-text index on an existing table:

Syntax format:
Create fulltext index name
ON Table Name (attribute name [(length)] [ASC | DESC]);

2.3.3 use the SQL statement ALTER TABLE to create a full-text index:

Syntax format:
Alter table table_name
Add fulltext index | key index name (attribute name [(length)] [ASC | DESC]);

2.4 create and view multi-column indexes:
The multi-column index means that when an index is created, the associated field is not a field, but multiple fields. Although the associated fields can be queried, only the first field in the associated field is used in the query condition.
2.4.1 create a multi-column index when creating a table:

The syntax format is as follows:

Create table table_name (attribute name data type, attribute name data type ,...... attribute name data type, INDEX | KEY [INDEX name] (attribute name 1 [(length)] [ASC | DESC]), ...... (attribute name 1 [(length)] [ASC | DESC]);

When the preceding statement creates an index, the associated field must be at least one field.

2.4.2 create multiple-column indexes on an existing table:

Syntax format:

Create index name ON table name (attribute name [(length)] [ASC | DESC],... attribute name n [(length)] [ASC | DESC]);

2.4.3 create multi-column indexes using the SQL statement ALTRE TABLE:

Syntax format:
Alter table table_name add index | key index name (attribute name [(length)] [ASC | DESC], attribute name n [(length)] [ASC | DESC]);

3. delete an index:

Syntax for deleting an index:
Drop index index_name ON table_name

4. view the index:

View the index Syntax:
Show index from table_name

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.