Detailed explanation of mysql index basics

MySQL - Index Basics

The structure of the user table used to test index creation in this article is as follows:

What is an index

#Index (also called "key" in MySQL) is a storage engine that quickly finds records A kind of data structure

——"High Performance MySQL"

We need to know that the index is actually a data structure, and its function is to help us quickly match and find the data we need. is one of the most commonly used tools for database performance optimization. Its role is equivalent to a shopping guide in a supermarket or a catalog in a book.

Index type

You can useSHOW INDEX FROM table_name;View index details

1. Primary key index PRIMARY KEY

It is a special unique index that does not allow null values. Generally, the primary key index is created at the same time when creating the table.

Note: A table can only have one primary key

2. Unique index UNIQUE

The value of the unique index column must be unique, but NULL values ​​are allowed. In the case of a composite index, the combination of column values ​​must be unique.

You can create a unique index through ALTER TABLE table_name ADD UNIQUE (column);

You can create a unique combined index by ALTER TABLE table_name ADD UNIQUE (column1,column2);

3, ordinary Index INDEX

The most basic index, it has no restrictions.

You can create a normal index through ALTER TABLE table_name ADD INDEX index_name (column);

# #4. Composite index INDEX

Combined index, that is, an index contains multiple columns. Mostly used to avoid table query.

You can create a combined index through

ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);

5. Full-text index FULLTEXT

Full-text index (also called full-text search) is a key technology currently used by search engines.

You can create a full-text index through ALTER TABLE table_name ADD FULLTEXT (column);

The index cannot be modified once it is created. If you want to modify the index, you can only delete and rebuild it. You can use

DROP INDEX index_name ON table_name; to delete the index.

Principles of index design

1. The columns suitable for indexing are the columns that appear in the where clause, or the columns specified in the join clause

2. For classes with smaller cardinality, the indexing effect is poor. There is no need to create an index on this column.

3. Use a short index. If you index a long string column, you should specify a prefix length, so It can save a lot of index space

4. Don’t over-index. Indexes require additional disk space and reduce the performance of write operations. When the table content is modified, the index will be updated or even reconstructed. The more index columns there are, the longer this time will be. So only keep the required indexes to facilitate querying.

Related referencesphp中文网

The above is the detailed content of Detailed explanation of mysql index basics. For more information, please follow other related articles on the PHP Chinese website!