MySQL Index classification

In a database table, indexing a field can greatly improve query speed. By leveraging these indexes, you can make MySQL queries and operations more efficient. Indexing is the key to fast searching. MySQL indexing is important for the efficient operation of MySQL. Here are a few common types of MySQL indexes.
1, the ordinary type index
This is the most basic type of index, and it has no limitations such as uniqueness. Normal indexes can be created in the following ways:
(1) Create an index, such as the name of the CREATE INDEX index on tablename (column name 1, column name 2,...);
(2) Modify the table, for example ALTER TABLE tablename ADD index name (column name 1, column name 2,...);
(3) When creating a table, specify the index, such as CREATE TABLE TableName ([...], the name of the index (column name 1, column name
2,...) );
2. Unique index
This index is basically the same as the previous "normal index", but there is one difference: all the values of an indexed column can only occur once, that is, they must be unique. A unique index can be created in the following ways:
(1) Create an index, such as the name of the Create UNIQUE Index index on tablename (List of columns);
(2) Modify the table, such as the name of the ALTER TABLE TableName ADD UNIQUE Index (List of columns);
(3) When creating a table, specify the index, such as CREATE TABLE TableName ([...], the name of the UNIQUE index (column of column
table));
3. Primary key
The primary key is a unique index, but it must be specified as "PRIMARY key". If you've ever used a auto_increment type column, you're probably already familiar with concepts like the primary key. The primary key is typically specified when creating the table, such as "CREATE TABLE TableName ([...], PRIMARY KEY (List of columns)"; ”。 However, we can also add the primary key by modifying the table, such as "ALTER table tablename Add PRIMARY key (List of columns); ”。 There can be only one primary key per table. (The primary key is equivalent to the aggregate index, which is the fastest index to find)
4. Single-row index and multi-column index
The index can be a single-column index or a multicolumn index.
(1) A single-column index is the index of a commonly used field, a common index.
(2) Multiple-column indexes are indexes that contain multiple column fields
ALTER TABLE student Add index sy (name,age,score);
Index SY is a multicolumn index, and multi-column indexes are valid in the following cases:
SELECT * FROM student where name= ' Jia ' and age>= '//where conditions contain the first column field of the index and
A second field
SELECT * FROM student where name= ' Jia '//where conditions contain only first column fields
SELECT * FROM student where name= ' Jia ' and Score<60//where conditions contain first column field and third word
Paragraph
Summary: Multi-column indexes are valid only if the first column field in the index is included in the Where condition
5. Select an index column
How to choose the Index column, first of all to look at the query criteria, generally the query criteria in the column as an index

MySQL Index classification