Introduction to methods of viewing, creating and deleting indexes in MySQL

I believe that everyone will deal with mysql in development. This article mainly introduces the methods of viewing, creating and deleting indexes in MySQL, and analyzes MySQL in detail in the form of examples. The role of indexes, as well as related implementation skills for viewing, creating and deleting indexes!

The examples in this article describe the methods of viewing, creating and deleting indexes in MySQL. Share it with everyone for your reference. The details are as follows:

1. The index function

On the index column, in addition to the ordered search mentioned above, the database uses various rapid positioning technologies to greatly improve the

queryefficiency. Especially when the amount of data is very large and the query involves multiple tables, using indexes can often speed up the query thousands of times.

For example, there are three unindexed tables t1, t2, and t3, which contain only columns c1, c2, and c3 respectively. Each table contains 1000 rows of data, which refers to the value from 1 to 1000. Search The query for rows with equal values ​​is as follows.

SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3

The result of this query should be 1000 rows, each row containing 3 equal values. To process this query without an index, you must look for all combinations of the 3 tables to get those rows that match the

WHERE clause. The number of possible combinations is 1000×1000×1000 (billions), so obviously the query will be very slow.

If each table is indexed, the query process can be greatly accelerated. Query processing using indexes is as follows.

(1) Select the first row from table t1 to view the data contained in this row.

(2) Use the index on table t2 to directly locate the row in t2 that matches the value of t1. Similarly, use the index on table t3 to directly locate the row in t3 that matches the value from t1.
(3) Scan the next row of table t1 and repeat the previous process until all rows in t1 are traversed.

In this case, a full scan is still performed on table t1, but index lookups on tables t2 and t3 can be performed to directly retrieve the rows in these tables, which is one million faster than without using indexes. times.

Using indexes, MySQL accelerates the
search for rows whose WHERE clause satisfies the condition, and when performing multi-table connection queries, it speeds up matching rows in other tables when performing the connection.

2. Create index

You can create an index when executing the CREATE TABLE statement, or you can use CREATE INDEX or ALTER TABLE alone to add an index to the table.

1. ALTER TABLE

ALTER TABLE is used to create a normal index, UNIQUE index or PRIMARY KEY index.

ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

The table_name is the name of the table to be indexed, column_list indicates which columns to index, and when there are multiple columns, separate them with commas. The index name index_name is optional. By default, MySQL will assign a name based on the first index column. Additionally, ALTER TABLE allows multiple tables to be altered in a single statement, so multiple indexes can be created at the same time.

2. CREATE INDEX

CREATE INDEX can add ordinary indexes or UNIQUE indexes to the table.

CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

table_name, index_name and column_list have the same meaning as in the ALTER TABLE statement, and the index name is not optional. In addition, you cannot use the CREATE INDEX statement to create a PRIMARY KEY index.

3. Index type

When creating an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a PRIMARY KEY or UNIQUE index. For a single-column unique index, this guarantees that the single column does not contain duplicate values. For multi-column unique indexes, it is guaranteed that the combination of multiple values ​​is not repeated.

PRIMARY KEY index and UNIQUE index are very similar. In fact, a PRIMARY KEY index is just a UNIQUE index with the name PRIMARY. This means that a table can only contain one PRIMARY KEY, because it is impossible to have two indexes with the same name in a table.

The following SQL statement adds a PRIMARY KEY index on sid to the students table.

The code is as follows:

ALTER TABLE students ADD PRIMARY KEY (sid)

4. Delete the index

You can use the ALTER TABLE or DROP INDEX statement to delete the index. Similar to the CREATE INDEX statement, DROP INDEX can be processed as a statement inside ALTER TABLE. The syntax is as follows.

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

Among them, the first two statements are equivalent, delete the index index_name in table_name.

The third statement is only used when deleting the PRIMARY KEY index, because a table can only have one PRIMARY KEY index, so there is no need to specify the index name. If no PRIMARY KEY index is created, but the table has one or more UNIQUE indexes, MySQL drops the first UNIQUE index.
If a column is deleted from the 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.

5． View index

mysql> show index from tblname;
mysql> show keys from tblname;

· Table
表的名称。
· Non_unique
如果索引不能包括重复词，则为0。如果可以，则为1。
· Key_name
索引的名称。
· Seq_in_index
索引中的列序列号，从1开始。
· Column_name
列名称。
· Collation
列以什么方式存储在索引中。在MySQL中，有值‘A'（升序）或NULL（无分类）。
· Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
· Sub_part
如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
· Packed
指示关键字如何被压缩。如果没有被压缩，则为NULL。
· Null
如果列含有NULL，则含有YES。如果没有，则该列含有NO。
· Index_type
用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
· Comment

总结：

通过本文的详细学习，相信有很多小伙伴对MySQL实现查看与创建以及删除索引的方法有了进一步的了解，希望对你有所帮助！

相关推荐：

MySQL如何创建和删除索引？

MySQL创建索引、重建索引、查询索引、删除索引

mysql建立索引删除索引很慢的解决

The above is the detailed content of Introduction to methods of viewing, creating and deleting indexes in MySQL. For more information, please follow other related articles on the PHP Chinese website!