MySQL index classification and usage analysis

MySQL indexes are divided into normal indexes, uniqueness indexes, full-text indexes, single-column indexes, multicolumn indexes, and so on. Here is a description of the different uses of the indexes.

MySQL: The index is saved in the B-tree format.

The memory storage engine can select either a hash or btree index, and the hash index can only be used for the equality comparison of = or <=>.

1. Normal index: CREATE index on Tablename (List of columns)

ALTER TABLE TableName Add index (List of columns)

CREATE TABLE TableName ([...], index [indexname] (List of columns)

2. Uniqueness Index: Create unique index

Alter ... add unique

Primary key: A unique index that must be specified as primary key

3, Full-text index: Starting from 3.23.23 Version Support full-text indexing and full-text search, Fulltext,

Can be created on a column of char, varchar, or text type.

4, single-row index, multi-column index:

Multiple single-column indexes differ from the query effect of a single multicolumn index because:

When executing a query, MySQL can use only one index, and one of the most restrictive indexes is selected from multiple indexes.

5, the leftmost prefix (leftmost prefixing): Multi-column index, for example: Fname_lname_age index, the following search conditions MySQL will use

Fname_lname_age index: Firstname,lastname,age;firstname,lastname;firstname, other conditions will not be used.

ii. determine which type of index to create based on the SQL query statement and how to optimize the query

Select an indexed column:

A. In the performance optimization process, choosing which column to create an index on is one of the most important steps. The main factors that can be considered for using indexes are

Two types of columns: columns that appear in the WHERE clause, columns that appear in the join clause.

B. Consider the distribution of the values in the column, and the larger the cardinality of the indexed columns, the better the index works.

C. Using a short index, if you index a string column, you should specify a prefix length, which can save a lot of index space and improve query speed.

D. Using the leftmost prefix

E. Do not over-index, just keep the desired index. Each additional index consumes additional disk space and reduces the performance of the write operation.

When you modify the contents of a table, the index must be updated, and sometimes refactoring may be required, so the more indexes you have, the longer it takes.

MySQL uses indexes only on the operators: <,<=,=,>,>=,between,in,

And sometimes like (a case that does not begin with a wildcard% or _).

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

Transferred from: http://www.jb51.net/article/27921.htm

MySQL index classification and usage analysis