Basic syntax of MySQL index

An index is a sorted data structure! The fields that can be used for searching in the where condition and sorting in the order by condition can be quickly located and found by indexing the physical address of the data.

Index Classification

1. Normal index: no constraints, mainly used to improve query efficiency

2. Unique index (UNIQUE): adds data uniqueness constraints based on ordinary indexes. There can be multiple

3. Primary key index: The primary key index adds a not-null constraint on the basis of the unique index, that is, NOT NULL+UNIQUE, and there can only be one

4. Full-text index (FULLTEXT): MySQL's built-in full-text index only supports English.

Generally, a dedicated full-text search engine is used, such as ES (ElasticSearch)

Create Index

#Normal index

ALTER TABLE table name ADD INDEX index name (column_list);

#Unique index

ALTER TABLE table name ADD UNIQUE index name (column_list);

#Primary key index

ALTER TABLE table name ADD PRIMARY KEY index name (column_list);

#Full-text index (string data type)

ALTER TABLE table name ADD fulltext index name (column_list);  

#View index

show index from student \G

Here, \G is used instead of the semicolon terminator; the output data information can be formatted.

#Delete index

Modify the table to delete the index

ALTER TABLE table name DROP INDEX index name;

Drop the primary key index

ALTER TABLE table name DROP PRIMARY KEY;

When the index column is one column, it is a single index;
An index created by combining multiple columns is called a joint index.

Under what circumstances can an index be created?

1. The value of the field has uniqueness restrictions, such as id

2. Fields that are frequently used as WHERE query conditions, especially when the data table is large

If the amount of data is very large, it is terrible to have no WHERE condition filtering.

3. Columns that need to be frequently GROUP BY and ORDER BY

4. The WHERE condition columns of UPDATE and DELETE generally also need to create indexes

The reason is that we need to retrieve the record based on the WHERE condition column first, and then update or delete it. The effect of updating data with non-index fields is more obvious.

If there are too many indexes, it will cause a burden when updating data if it involves index updates.

5.DISTINCT fields need to create indexes

6. When performing multi-table JOIN operations, you need to pay attention to the following principles when creating indexes

The number of connected tables should not exceed 3. Each additional table is equivalent to adding a nested loop, and the order of magnitude will increase very quickly. Create indexes for the fields used for connections, and the types of the fields in multiple tables must be consistent.

When do you not need to create an index?

The value of an index is to quickly locate a field. If a field cannot be located, there is usually no need to create an index.

1. There is no need to create indexes for fields that are not used in the WHERE conditions (including GROUP BY and ORDER BY);

2. If the table has too few records, for example, less than 1,000 rows, then there is no need to create an index;

3. If there is a large amount of duplicate data in a field, there is no need to create an index, such as the gender field;

4. Frequently updated fields do not necessarily need to be indexed. Because when updating data, you also need to update the index. If there are too many indexes, it will cause a burden when updating the index, thus affecting efficiency.

Under what circumstances will the index fail?

1. If the index column is used for expression calculation and function, it will fail

2. In the WHERE clause, if the condition column before OR is indexed, but the condition column after OR is not indexed, the index will become invalid.

3. When we use LIKE for fuzzy query, the expression cannot start with %

4. Try to set the index column to NOT NULL constraint

To determine whether an index column is NOT NULL, a full table scan is often required. Therefore, it is best to set the field to a NOT NULL constraint when designing the data table. For example, you can set the default value of an INT type field to 0. Sets the default value of a character type to an empty string ('')

The above is the detailed content of the basic syntax of MySQL index. For more information about MySQL index syntax, please pay attention to other related articles on 123WORDPRESS.COM!