MySQL index knowledge summary

The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL.

To use an analogy, if MySQL with a proper design and indexes is a Lamborghini, then MySQL without an index is a human-powered tricycle.

Taking the table of contents (index) of a Chinese dictionary as an example, we can quickly find the required characters through the table of contents (index) sorted by pinyin, strokes, radicals, etc.

Indexes are divided into single-column indexes and composite indexes. A single-column index is an index that contains only a single column. A table can have multiple single-column indexes, but these are not composite indexes. Composite index, that is, an index containing multiple columns.

When creating an index, you need to ensure that the index is applied to the SQL query statement (usually as a condition of the WHERE clause).

In fact, the index is also a table that stores the primary key and index fields and points to the records of the entity table.

The above all talks about the benefits of using indexes, but excessive use of indexes will lead to abuse. Therefore, indexes also have their disadvantages: although indexes greatly improve query speed, they also reduce the speed of updating tables, such as INSERT, UPDATE, and DELETE. Because when updating a table, MySQL not only needs to save the data, but also the index file.

Building an index takes up disk space for index files.

Normal index

Create Index

This is the most basic index and it has no restrictions. It can be created in the following ways:

CREATE INDEX indexName ON mytable(username(length));

If it is CHAR or VARCHAR type, length can be smaller than the actual length of the field; if it is BLOB or TEXT type, length must be specified.

Modify table structure (add index)

ALTER table tableName ADD INDEX indexName(columnName)

Specify directly when creating a table

CREATE TABLE mytable( 
 
ID INT NOT NULL,  
 
username VARCHAR(16) NOT NULL, 
 
INDEX [indexName] (username(length)) 
 
);

Syntax to delete an index

DROP INDEX [indexName] ON mytable;

Unique Index

It is similar to the previous ordinary index, except that the values ​​of the index column must be unique, but null values ​​are allowed. If it is a composite index, the combination of column values ​​must be unique. It can be created in the following ways:

Create Index

CREATE UNIQUE INDEX indexName ON mytable(username(length))

Modify table structure

ALTER table mytable ADD UNIQUE [indexName] (username(length))

Specify directly when creating a table

CREATE TABLE mytable( 
 
ID INT NOT NULL,  
 
username VARCHAR(16) NOT NULL, 
 
UNIQUE [indexName] (username(length)) 
 
);

Adding and dropping indexes using the ALTER command

There are four ways to add indexes to a table:

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): This statement adds a primary key, which means that the index value must be unique and cannot be NULL.
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): This statement creates an index whose values ​​must be unique (except NULL, which may appear multiple times).
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): Add a normal index. The index value can appear multiple times.
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list): This statement specifies the index as FULLTEXT for full-text indexing.

The following example adds an index to a table.

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

You can also remove indexes using the DROP clause in the ALTER command. Try the following example to delete the index:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

Use the ALTER command to add and delete primary keys

A primary key can only be applied to one column. When adding a primary key index, you need to ensure that the primary key is not empty (NOT NULL) by default. The following are examples:

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

You can also delete a primary key using the ALTER command:

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

Display index information

You can use the SHOW INDEX command to list the relevant index information in the table. You can format the output by adding \G.

Try the following examples:

mysql> SHOW INDEX FROM table_name; \G
........

The above is the detailed content of the summary of MySQL index knowledge. For more information about MySQL index, please pay attention to other related articles on 123WORDPRESS.COM!