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);
ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);
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 clause2. 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 space4. 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!