Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the characteristics of the index of MySQL database", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the characteristics of the index of MySQL database"?

Indexing is the main means to accelerate table content access, especially for queries involving joins of multiple tables. This is an important part of optimization, and we need to understand why indexes are needed, how indexes work, and how to use them to optimize queries. In this section, we will describe the characteristics of indexes and the syntax for creating and deleting indexes.

Characteristics of the index

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 when compiling MySQL.

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

MySQL can create indexes on multiple columns. An index can consist of up to 15 columns. On CHAR and VARCHAR columns, you can also use the prefix of the column as part of an index.

Although there will be fewer and fewer constraints for indexing with the further development of MySQL, there are still some constraints. The following table shows the difference between the ISAM table and the MyISAM table based on the characteristics of the index:

Table 2-1 comparison table of channel information characteristic words

Characteristics of the index 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

sixteen

sixteen

256 bytes allowed

Prefixes that can only index columns

thirty-two

sixteen

500 bytes

As you can see from this table, for the ISAM table, its 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 some other restrictions. The difference in indexing characteristics between the two table types indicates that some columns may not be indexed depending on the version of MySQL used. For example, if you use a version prior to version 3.23, you cannot index columns that contain NULL values.

The index has the following situations:

INDEX index: an index in the usual sense, and in some cases KEY is a synonym for it. The columns of the index can include duplicate values.

UNIQUE index: unique index, which ensures that the column does not contain duplicate values, and for multi-column unique indexes, it ensures that the combination of values is not duplicated.

PRIMARY KEY index: UNIQUE index is also very similar. In fact, a PRIMARY KEY index is just an UNIQUE index with a PRIMARY name. This means that a table can contain only one PRIMARY KEY.

Create and delete indexes with Alter Table statements

To add an index to an existing table, you can use ALTER TABLE or CREATE INDEX statements. ALTER TABLE is the most commonly used because it can be used to create normal, UNIQUE, or PRIMARY KEY indexes, such as:

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)

Where tbl_name is the table name to increase the index, and column_list indicates which columns to index. One (col1,col2,...) A formal list creates a multi-column index. Index values are concatenated with the values of a given column. If the index consists of more than one column, the column names are separated by commas. The index name index_name is optional, so you don't have to write it, and 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, so you can create multiple indexes at the same time.

Similarly, you can use the ALTER TABLE statement to produce the index of a column:

ALTER TABLE tbl_name DROP INDEX index_name

ALTER TABLE tbl_name DROP PRIMARY KEY

Note that the first statement above can be used to delete various types of indexes, while the third statement can only be used when deleting PRIMARY KEY indexes; in this case, no index name is required, because a table can only have one such index. If the index as PRIMARY KEY is not explicitly created, but the table has one or more UNIQUE indexes, MySQL deletes the first of those UNIQUE indexes.

If columns are deleted from the table, the index may be affected. If the deleted column is part of the index, 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, for the student used above, you might want to create an index for it to speed up the retrieval of the table:

> ALTER TABLE student

-> ADD PRIMARY KEY (id)

-> ADD INDEX mark (english,Chinese,history)

This example includes both PRIMARY and multi-column indexes. Remember, the column that uses the PRIMARY index must be a column with the NOT NULL attribute. If you want to see how the index is created, 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 above table does not include all the output, so readers can try to see for themselves.

Then use the ALTER TABLE statement to delete the index. To delete the index, you need to know the name of the index, which you can get through the SHOW INDEX statement:

Mysql > ALTER TABLE student DROP PRIMARY KEY

-> DROP INDEX mark

If you look at the index in the table, the statement and output are:

Mysql > SHOW INDEX FROM student

Empty set (0.01sec)

Create an index with CREATEDROP INDEX

You can also use the CREATE INDEX statement to create an index. Create INDEX was introduced in MySQL version 3.23, but if you use a version prior to 3.23, you can create an index using the ALTER TABLE statement (MySQL usually maps CREATE INDEX to ALTER TABLE internally). The syntax for this statement to create 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 in the ALTER TABLE statement. The index name is not optional here. Obviously, CREATE INDEX can add a normal index or UNIQUE index to a table, and you cannot create a PRIMARY KEY index with a CREATE INDEX statement.

You can use the DROP INDEX statement to delete the index. Similar to CREATE INDEX statements, DROP INDEX is usually handled internally as an ALTER TABLE statement, and DROP INDEX is introduced in MySQL 3.22.

The syntax for deleting an index statement is as follows:

DROP INDEX index_name ON tbl_name

Again, from the example in the previous section, since CREATE INDEX cannot create an PRIMARY index, here we create a multi-column index with values:

Mysql > CREATE INDEX mark ON student (english,chinese,history)

The same check of the student table shows that:

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 delete the index using the following statement:

Mysql > DROP INDEX mark ON student

Specify an index when you create a table

To create an index for a new table when you publish a CREATE TABLE statement, the syntax is similar to that of the ALTER TABLE statement, but you should specify the index creation clause in the part of the statement where you define the table column, as follows:

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, the index name is optional for both INDEX and UNIQUE, and if it is not given, MySQL will choose one for it. In addition, here KEY is an alias for INDEX, which has the same meaning.

There is a special case where you can add PRIMARY KEY after the column definition to create a single-column PRIMARY KEY index, as follows:

CREATE TABLE tbl_name

(

I INT NO

At this point, I believe you have a deeper understanding of "what are the characteristics of the index of MySQL database?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.