Mysql database index operation summary

In a relational database, an index is a separate, physical storage structure that sorts the values ​​of one or more columns in a database table. It is a collection and corresponding set of one or several column values ​​in a table. A list of logical pointers to the data pages in the table that physically identify these values. The index is equivalent to the table of contents of a book. You can quickly find the required content based on the page numbers in the table of contents.

This article mainly summarizes the MySQL index operation methods, including the operations of creating index, rebuilding index, querying index and deleting index. In the examples listed below, `table_name` represents the data table name, `index_name` represents the index name, and column list represents the field list (such as: `id`, `order_id`).

1. Create an index

The index can be created in the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE alone to add an index to the table. The following command statements show how to create a primary key index (PRIMARY KEY), a joint index (UNIQUE) and a normal index (INDEX) respectively.

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:

mysql>ALTER TABLE `article` ADD INDEX `id`;//Add id index to article table

Or:

mysql>ALTER TABLE `article` ADD INDEX (`id`,`order_id`); Add id index to article table, order_id index

2, rebuild the index

Rebuilding indexes is often used in regular database maintenance operations. After the database has been running for a long time, the index may be damaged, and it needs to be rebuilt. Re-indexing the data can improve retrieval efficiency.

mysql> REPAIR TABLE `table_name` QUICK;

3. Query the data table index

mysql> SHOW INDEX FROM `table_name`;

For querying data table indexes, please refer to the article on this site: Detailed explanation of mysql query table index commands

4. Delete index

Deleting an index can be achieved using the ALTER TABLE or DROP INDEX statement. DROP INDEX can be processed as a statement inside ALTER TABLE. Its format is 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 is deleted. In the last statement, it is only used to delete the PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so the index name does not need to be specified. If no PRIMARY KEY index is created but the table has one or more UNIQUE indexes, MySQL will drop the first UNIQUE index. If a column is deleted from a table, the index will be affected. For a multi-column index, if one of the columns is deleted, the column will also be deleted from the index. If you delete all the columns that make up the index, the entire index will be deleted.

The above content is a summary of MySQL index operation commands. I hope it can help everyone.

Related recommendations:

How to make the use of database indexes more efficient?

About redundant and duplicate indexes in mysql

Very important index operations in MySql

The above is the detailed content of Mysql database index operation summary. For more information, please follow other related articles on the PHP Chinese website!