Summary of MYSQ Index operation commands (create, rebuild, query, and delete index commands) _mysql

In the examples listed below, ' table_name ' represents the data table name, ' Index_name ' represents the index name, and the column list represents the field list (for example: ' id ', ' order_id ').

1. CREATE index

The creation of the index can be done in the CREATE TABLE statement, or you can add indexes to the table separately with the CREATE INDEX or ALTER TABLE. The following command statements show how to create a primary key index (PRIMARY key), a federated Index (UNIQUE), and a method of the normal index (index).

Copy Code code as follows:

Mysql>alter TABLE ' table_name ' ADD INDEX ' index_name ' (column list);
Mysql>alter TABLE ' table_name ' ADD UNIQUE ' index_name ' (column list);
Mysql>alter TABLE ' table_name ' ADD PRIMARY KEY ' index_name ' (column list);
Mysql>create INDEX ' index_name ' on ' table_name ' (column_list);
Mysql>create UNIQUE INDEX ' index_name ' on ' table_name ' (column_list);

For example:

Copy Code code as follows:

Mysql>alter table ' article ' Add index ' ID ';//Add ID index to article table

Or:

Copy Code code as follows:

Mysql>alter table ' article ' Add index (' id ', ' order_id '); Add ID index to Article table, ORDER_ID Index

2. Rebuilding index

Rebuilding indexes are often used in regular database maintenance operations. After the database has been running for a long time, the index is likely to be corrupted, which requires rebuilding. Rebuilding indexes on data can improve retrieval efficiency.

Copy Code code as follows:

mysql> REPAIR TABLE ' table_name ' QUICK;

3, Query data table index

The MySQL Query Table Index command has two forms of command:

Copy Code code as follows:

Mysql> show INDEX from ' table_name ';

Or:

Copy Code code as follows:

Mysql> show the keys from ' table_name ';

The results of running the above command will be shown below, and the meaning of each column in the resulting results is explained below.

Copy Code code as follows:

The name of the table tables.
Non_unique 0 If the index cannot include duplicate words. 1 if it is possible.
The name of the Key_name index.
Seq_in_index the column sequence number in the index, starting at 1.
COLUMN_NAME column name.
How the collation column is stored in the index. In MySQL, there is a value of ' a ' (ascending) or null (no classification).
An estimate of the number of unique values in the cardinality index. Can be updated by running Analyze table or myisamchk-a. The cardinality is counted according to the statistics that are stored as integers, so even for small tables, this value is not necessarily accurate. The larger the cardinality, the greater the chance that MySQL will use the index when it comes to syndication.
Sub_part the number of characters indexed if the column is only partially indexed. Null if the entire column is indexed.
Packed indicates how the keyword is compressed. Null if it is not compressed.
Null contains Yes if the column contains null. If not, the column contains No.
Index_type used Indexing method (Btree, Fulltext, HASH, Rtree).
Comment more commentary.

4. Delete Index

The deletion index can be implemented using ALTER TABLE or the DROP INDEX statement. DROP index can be handled as a statement inside ALTER TABLE in the following format:

Copy Code code as follows:

Mysql>drop index ' index_name ' on ' table_name ' (column list);
Mysql>alter TABLE ' table_name ' DROP INDEX ' index_name ' (column list);
Mysql>alter TABLE ' table_name ' DROP UNIQUE ' index_name ' (column list);
Mysql>alter TABLE ' table_name ' DROP PRIMARY KEY ' index_name ' (column list);

In the previous three statements, the index index_name in table_name was deleted. In the last statement, it is only used in the deletion of the primary key index, because a table can have only one primary key index, so you can also not specify an index name. If the primary key index is not created, but the table has one or more unique indexes, MySQL deletes the first unique index. If you delete a column from the table, the index is affected. For indexes of multiple-column combinations, if you delete one of the columns, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.