What is the usage of mysql index keyword

In mysql, the index keyword can be used to create an index, the syntax "CREATE INDEX index name ON table name (column name)"; it can be used to view the index, the syntax "SHOW INDEX FROM table name"; it can also be used To modify the index, the syntax is "DROP INDEX index name ON table name".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

In mysql, index refers to the index, which is a special database structure that is composed of one or more columns in the data table. It can be used to quickly query a specific value in the data table. record of.

The index keyword can be used to create an index, view the index, or modify the index.

index keyword creates an index

You can use the CREATE INDEX statement specifically for creating an index to create an index on an existing table, but this statement cannot create a primary key. .

CREATE INDEX 索引名 ON 表名 (列名 [长度] [ASC|DESC])

• 24011a09e8f20deb609b6e9d89f0f75c: Specify the index name. A table can create multiple indexes, but each index has a unique name in the table.

• 722e3d59fd24604761db25f00f9b264f: Specify the name of the table to create an index.

• 4856a503a57d954e03d677c32e973360: Specify the column name to create an index. You can usually consider using columns that frequently appear in the JOIN clause and WHERE clause in the query statement as index columns.

• 93d887edaff759fd3eed38159bc15ea1: Optional. Specifies that the length characters preceding the column are used to create the index. Creating an index using part of a column can help reduce the size of the index file and save the space occupied by the index columns. In some cases, only the prefix of a column can be indexed. The length of an index column has a maximum limit of 255 bytes (1000 bytes for MyISAM and InnoDB tables). If the length of an index column exceeds this limit, it can only be indexed using the column's prefix. In addition, columns of type BLOB or TEXT must also use prefix indexes.

• ASC|DESC: Optional. ASC specifies that the index is sorted in ascending order, DESC specifies that the index is sorted in descending order, and the default is ASC.

For example, to add a new index for column c4, use the following statement:

CREATE INDEX idx_c4 ON t(c4);

By default, if the index type is not specified, MySQL will create a B-Tree index.

index keyword view index

SHOW INDEX FROM 表名 [FROM 数据库名]

The syntax is as follows:

• 722e3d59fd24604761db25f00f9b264f: Specify the need to view The data table name of the index.

• 51ede4c6d43f3b2169203bd49746727d: Specify the database where the data table that needs to be viewed index is located, which can be omitted. For example, the SHOW INDEX FROM student FROM test; statement means to view the index of the student data table in the test database.

Example:

mysql> SHOW INDEX FROM tb_stu_info2\G
*************************** 1. row ***************************
        Table: tb_stu_info2
   Non_unique: 0
     Key_name: height
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.03 sec)

The main parameters are described as follows:

Parameters	Description
Table	represents the name of the data table to create the index, here is the tb_stu_info2 data table.
Non_unique	Indicates whether the index is a unique index. If it is not a unique index, the value of this column is 1; if it is a unique index, the value of this column is 0.
Key_name	represents the name of the index.
Seq_in_index	Indicates the position of the column in the index. If the index is a single column, the value of the column is 1; if the index is a combined index, the value of the column is 1. The column values ​​are the order in which each column appears in the index definition.
Column_name	represents the column field that defines the index.
Collation	Indicates the order in which columns are stored in the index. In MySQL, ascending order displays the value "A" (ascending order), if displayed as NULL, it means no classification.
Cardinality	An estimate of the number of unique values ​​in the index. Cardinality counts against statistics that are stored as integers, so even for small tables, the value does not need to be exact. The larger the cardinality, the greater the chance that MySQL will use the index when doing joins.
Sub_part	represents the number of indexed characters in the column. If the column is only partially indexed, the value of the column is the number of characters indexed; if the entire column is indexed, the value of the column is NULL.
Packed	Indicates how keywords are packed. If not compressed, the value is NULL.
Null	is used to display whether the index column contains NULL. If a column contains NULL, the value of the column is YES. If not, the value of this column is NO.
Index_type	Display the type and method used by the index (BTREE, FULLTEXT, HASH, RTREE).
Comment	Display comments.

index关键字修改索引

DROP INDEX <索引名> ON <表名>

语法说明如下：

• b493f009a433abb929a361542484fbbf：要删除的索引名。

• a26d98d33123a70024fa8ba5642906c6：指定该索引所在的表名。

说明：

在 MySQL 中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。

【相关推荐：mysql视频教程】

The above is the detailed content of What is the usage of mysql index keyword. For more information, please follow other related articles on the PHP Chinese website!