Summary of basic operations of mysql index (4)_MySQL

1. Why use index:
The index in the database object is actually similar to the table of contents of the book, mainly to improve the speed of retrieving data from the table. Since the data is stored in a database table, the index is created on the database table object and consists of keys generated by one field or multiple fields in the table. These keys are stored in a data structure (B-tree or hash table). MySQL allows you to quickly and efficiently find fields associated with keys. According to the storage type of the index, the index can be divided into B-tree index (BTREE) and hash index (HASH). Note: InnoDB and MyISAM storage engines support BTREE type indexes, and MEMORY storage engines support HASH type indexes. The former index is used by default.
MySQL supports 6 types of indexes, namely ordinary index, unique index, full-text index, single-column index, multi-column index, and spatial index.
The following situations are suitable for index creation:
1. Fields that are frequently queried. That is, the fields that appear in the WHERE clause.
2. The fields in the group, that is, the fields that appear in the GROUP BY clause.
3. Joint query between the child table and the parent table where there is a dependency relationship, that is, the primary key or foreign key field.
4. Set a unique complete constraint field.

2. Create and view index:
Index operations include creating indexes, viewing indexes, and deleting indexes. The so-called index creation is to create an index on one field or multiple fields of the table. In MySQL, there are usually three ways to create an index. Create an index when creating a table, create an index on an existing table, and pass the SQL statement ALTER. TABLE creates an index.
2.1 Create and view ordinary index:
The so-called ordinary index means that no restrictions (unique, non-empty, etc.) are attached when creating the index. This type of index 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(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC]) 
);

Note: The INDEX or KEY parameter is used to specify the field as the index, 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, and the "length" parameter is used To specify the length of the index, the "ASC|DESC" parameter is used to specify ascending or descending order.
Note: When creating an index, you can specify the length of the index. This is because different storage engines define the maximum number of indexes and maximum index length for a table. The storage engine supported by MySQL supports at least 16 indexes for each table, and the total index length is at least 256 bytes.

Example:

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

Description:
You can check whether the index is successfully created by SHOW CREATE TABLE t_dept G;
You can use EXPLAIN SELECT * FROM t_dept WHERE deptno=1G; to verify whether the index in the database table is used. If the values ​​in the possible_keys and key fields in the execution result are both the created index name index_deptno, it means that the index already exists and has been enabled.

2.1.2 Create a normal index on an existing table:

Achieved through SQL statement CREATE INDEX, its syntax is:
CREATE INDEX index name
ON table name (attribute name [(length)] [ADC|DESC])

2.1.3 Create a normal index through the SQL statement ALTER TABLE:

The grammatical form is:
ALTER TABLE table_name ADD INDEX|KEY index name (attribute name [(length)] [ASC|DESC]);

2.2 Create and view unique index:
The so-called unique index means that when creating an index, the value of the restricted index must be unique. This type of index can help you query a record faster. In MySQL, according to the way of creating index, it can be divided into two types: automatic index and manual index.
The so-called automatic indexing means that when integrity constraints are set in a database table, the table will automatically be indexed by the system. The so-called manual index refers to manually creating an index on the table. When a field in a table is set as 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:

The grammatical form is:

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 UNIQUE INDEX|KEY [索引名] (属性名1 [(长度)] [ASC | DESC])
);

Explanation: UNIQUE INDEX or UNIQUE KEY means creating a unique index.

2.2.2 Create a unique index on an existing table:

Achieved through SQL statement CREATE UNIQUE INDEX, the syntax is:
CREATE UNIQUE INDEX index name
ON table name (attribute name [(length)] [ASC|DESC]);

2.2.3 Create a unique index through the SQL statement ALTER TABLE:

The grammatical form is:
ALTER TABLE table_name ADD UNIQUE INDEX|KEY index name (attribute name [(length)] [ASC|DESC]);

2.3 创建和查看全文索引：
全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能更加快速的查询数据量较大的字符串类型的字段。MySQL只能在存储引擎为MyISAM的数据库表上创建全文引擎。默认情况下，全文引擎的搜索执行方式为不区分大小写，如果全文引擎所关联的字段为二进制数据类型，则以区分大小写的方式执行。

2.3.1 创建表时创建全文索引：

语法形式为：

CREATE TABLE table_name (
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 FULLTEXT INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC])
);

2.3.2 在已经存在的表上创建全文索引：

语法形式为：
CREATE FULLTEXT INDEX 索引名
ON 表名 (属性名 [(长度)] [ASC|DESC]);

2.3.3 通过SQL语句ALTER TABLE 创建全文索引：

语法形式为：
ALTER TABLE table_name
ADD FULLTEXT INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

2.4 创建和查看多列索引：
所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。
2.4.1 创建表时创建多列索引：

语法形式如下：

CREATE TABLE table_name(
 属性名 数据类型,
 属性名 数据类型,
 ......
 属性名 数据类型,
 INDEX|KEY [索引名] 
 (属性名1 [(长度)] [ASC|DESC]),
 ......
 (属性名1 [(长度)] [ASC|DESC])
);

上述语句创建索引时，所关联的字段至少大于一个字段。

2.4.2 在已经存在的表上创建多列索引：

语法形式为：

CREATE INDEX 索引名 ON 表名 (
 属性名 [(长度)] [ASC|DESC],
 ......
 属性名n [(长度)] [ASC|DESC]
); 

2.4.3 通过SQL语句ALTRE TABLE 创建多列索引：

语法形式为：
ALTER TABLE table_name ADD INDEX|KEY 索引名(属性名 [(长度)] [ASC|DESC],属性名n [(长度)] [ASC|DESC]);

3. 删除索引：

删除索引的语法形式：
DROP INDEX index_name ON table_name

4. 查看索引：

查看索引的语法形式：
SHOW INDEX FROM table_name

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持。