In-depth MySQL database index _ MySQL

In-depth MySQL database index bitsCN.com

Indexes are the main means to accelerate table content access, especially for queries involving connections to multiple tables. This is an important part of database optimization. we need to understand why indexes are needed, how indexes work, and how they are used to optimize queries. This section describes the features of indexes and the syntax for creating and deleting indexes.

Index features

All MySQL column types can be indexed. Using indexes on related columns is the best way to improve the performance of SELECT operations.

A table can have up to 16 indexes. The maximum index length is 256 bytes, although this can be changed during MySQL compilation.

For CHAR and VARCHAR columns, you can index the column prefix. This is faster and requires less disk space than the entire index column. For BLOB and TEXT columns, you must index the column prefix. you cannot index all columns.

MySQL can create indexes on multiple columns. An index can contain up to 15 columns. (In CHAR and VARCHAR columns, you can also use the column prefix as an index part ).

Although there will be fewer and fewer constraints for MySQL to further develop and create indexes, there are still some constraints. The following table lists the differences between the ISAM table and MyISAM table based on the index features:

Table 2-1 Comparison of channel information feature words

Index features ISAM table MyISAM table
NULL value
BLOB and TEXT columns
Number of indexes in each table
Number of columns in each index
Maximum index row size
Not allowed
Cannot be indexed
16
16
256 bytes allowed
Only the prefix of the index column can be used.
32
16
500 bytes

From this table, we can see that for the ISAM table, the index column must be defined as not null, and The BLOB and TEXT columns cannot be indexed. The MyISAM table type removes these restrictions and slows down other restrictions. The differences between the two types of table indexes indicate that, depending on the MySQL version used, some Columns may not be indexed. For example, if you use a version earlier than Version 3.23, you cannot index columns that contain NULL values.

The index has the following situations:

INDEX: a common INDEX. in some cases, a KEY is a synonym. Indexed columns can contain duplicate values.

UNIQUE index: a UNIQUE index that ensures that the column does not contain duplicate values. for a UNIQUE index of multiple columns, it ensures that the combination of values is not repeated.

Primary key index: the UNIQUE index is very similar. In fact, the primary key index is only a UNIQUE index with the PRIMARY name. This indicates that a table can only contain one primary key.

Use the Alter Table statement to create and delete indexes

To add an INDEX to an existing TABLE, you can use the alter table or create index statement. Alter table is most commonly used because it can be used to create common indexes, UNIQUE indexes, or primary key indexes, such:

Alter table tbl_name add index index_name (column_list)
Alter table tbl_name add unique index_name (column_list)
Alter table tbl_name add primary key index_name (column_list)

Tbl_name indicates the name of the table to be indexed, while column_list indicates which columns are indexed. Create a multi-column index for a list in the form of (col1, col2. The index value is a series of values of a given column. If an index consists of more than one column, each column name is separated by a comma. The index name index_name is optional, so you can leave it empty. MySQL will assign it a name based on the first index column. Alter table allows you to specify changes to multiple tables in a single statement. Therefore, you can create multiple indexes at the same time.

Similarly, you can use the alter table statement to generate the column index:

Alter table tbl_name drop index index_name
Alter table tbl_name DROP PRIMARY KEY

Note that the first statement can be used to delete various types of indexes. The third statement is only used to delete the primary key index. in this case, no index name is required, because a table may only have one such index. If an index is not explicitly created as the primary key, but the table has one or more UNIQUE indexes, MySQL deletes the first of these UNIQUE indexes.

If a column is deleted from the table, the index may be affected. If the column to be deleted is an index component, the column is also deleted from the index. If all the columns that make up the index are deleted, the entire index is deleted.

For example, if student is used as an example, you may want to create an index to accelerate the table search speed:

Mysql> alter table student
-> Add primary key (id ),
-> Add index mark (english, Chinese, history );

This example includes both PRIMARY indexes and multi-column indexes. Remember, the column using the primary index must be a column with the not null attribute. if you want to see the created INDEX, you can use the show index statement:

Mysql> show index from student;

The result is:

+ --------- + ------------ + ---------- + -------------- + ------------- +-

| Table | Non_unique | Key_name | Seq_in_index | Column_name |

+ --------- + ------------ + ---------- + -------------- + ------------- +-

| Student | 0 | PRIMARY | 1 | id |

| Student | 1 | mark | 1 | english |

| Student | 1 | mark | 2 | chinese |

| Student | 1 | mark | 3 | history |

+ --------- + ------------ + ---------- + -------------- + ------------- +-

Because there are too many columns, the table does not contain all the output. you can try to view them by yourself.

Use the alter table statement to delete an INDEX. to delete an INDEX, you need to know the INDEX Name. you can use the show index statement to obtain the INDEX name:

Mysql> alter table student drop primary key,
-> Drop index mark;

The statement and output are as follows:

Mysql> show index from student;
Empty set (0.01 sec)

Use createdrop index to create an INDEX

You can also use the create index statement to CREATE an INDEX. create index is introduced in MySQL 3.23, but if you use a version earlier than MySQL 3.23, you can use the alter table statement to CREATE an INDEX (MySQL usually maps the create index to alter table in the internal part ). The syntax for creating an index is as follows:

Create unique index index_name ON tbl_name (column_list)
Create index index_name ON tbl_name (column_list)

Tbl_name, index_name, and column_list have the same meaning as the alter table statement. The index name is optional. Obviously, create index can add normal or UNIQUE indexes to the table. you cannot use the create index statement to CREATE a primary key index.

You can use the drop index statement to delete an INDEX. Similar to the create index statement, drop index is usually processed internally as an alter table statement, and drop index is introduced in MySQL 3.22.

The syntax for deleting an index is as follows:

Drop index index_name ON tbl_name

In the previous example, because the create index cannot CREATE a primary index, here we CREATE a multi-column INDEX:

Mysql> create index mark ON student (english, chinese, history );

Check the student table as follows:

Mysql> show index from student;

+ --------- + ------------ + ---------- + -------------- + ------------- +

| Table | Non_unique | Key_name | Seq_in_index | Column_name |

+ --------- + ------------ + ---------- + -------------- + ------------- +

| Student | 1 | mark | 1 | english |

| Student | 1 | mark | 2 | chinese |

| Student | 1 | mark | 3 | history |

+ --------- + ------------ + ---------- + -------------- + ------------- +

Then, use the following statement to delete the index:

Mysql> drop index mark ON student;

Specify indexes when creating a table

To CREATE an index for a new TABLE when the create table statement is released, the syntax used is similar to the syntax of the alter table Statement. However, you should specify the index creation clause in the statement section of the TABLE column that you define, as shown below:

Create table tbl_name
(
...
INDEX index_name (column_list ),
KEY index_name (column_list ),
UNIQUE index_name (column_list ),
Primary key index_name (column_list ),
...
)

Like alter table, INDEX names are optional for INDEX and UNIQUE. if not given, MySQL selects one for it. In addition, here, the KEY is an alias of the INDEX, which has the same meaning.

There is a special case: you can add a primary key after the column definition to create a single column primary key index, as shown below:

Create table tbl_name
(
I INT NObitsCN.com