In-depth MySQL database index (1)

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	Allow 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.