mysql index classification and purpose analysis

1.MySQL: the index is saved in B tree format

The Memory storage engine can select the Hash or BTree indexes, and the Hash indexes can only be used for = or < = > Equality comparison.

1. General 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. Exclusive index: create unique index

alter... add unique

Primary key: 1 unique index, must be specified as primary key

3. Full-text indexing: full-text indexing and full-text retrieval are supported from version 3.23.23, FULLTEXT,

It can be created on columns of type char, varchar, or text.

4. Single-column index, multi-column index:

The query effect of multiple single-column indexes is different from that of a single multi-column index, because:

When a query is executed, MySQL can only use one index and selects the most restrictive index from among multiple indexes.

5. Leftmost prefix (Leftmost Prefixing) : multi-column index, for example: fname_lname_age index, the following search criteria MySQL will be used

fname_lname_age index: firstname,lastname,age; firstname lastname; firstname will not be used in other cases.

2. Determine which type of index to create according to the sql query statement, and how to optimize the query

Select the index column:

a. Selecting which column to index is the most important step in performance optimization. The main things you can consider using an index are

There are two types of columns: columns that appear in the where clause and columns that appear in the join clause.

b. The larger the cardinality of the indexed column, the better the index, considering the distribution of the values in the column.

c. Use a short index. If you are indexing a string column, you should specify a prefix length.

d. Use the leftmost prefix

e. Don't over-index, just keep as many indexes as you need. Each additional index takes up additional disk space and reduces the performance of write operations.

Indexes must be updated and sometimes refactored when the contents of a table are modified, so the more indexes you have, the longer it takes.

MySQL only USES indexes for 1 operand: < , < =,=, > , > =,between,in,

And sometimes like(not starting with a wildcard % or _).

mysql index classification

Indexing a field in a database table can greatly improve query speed. By making good use of these indexes, MySQL's queries and operations can be made more efficient. Indexing is the key to a fast search. The establishment of the MySQL index is important for the efficient operation of MySQL. Several common MySQL index types are described below.
1. Common index
This is the most basic type of index, and it has no restrictions such as singleness. Common indexes can be created in the following ways:
(1) create an index, such as CREATE INDEX index name ON tablename (column name 1, column name 2,...) ;
(2) modify the table, such as ALTER TABLE tablename ADD INDEX index name (column name 1, column name 2,...) ;
(3) specify the index when creating the table, for example, CREATE TABLE tablename ([...]) , the name of the INDEX index (column name 1, column name
2,...) );
2. Exclusive index
This index is essentially the same as the previous "normal index" with one difference: all the values of the indexed column can only occur once, that is, only once. A unique index can be created in the following ways:
(1) create an index, such as CREATE UNIQUE INDEX index name ON tablename (list of columns);
(2) modify the table, such as ALTER TABLE tablename ADD UNIQUE index name (list of columns);
(3) specify the index when creating the table, for example, CREATE TABLE tablename ([...]) , the name of the UNIQUE index (the column of the column
Table));
3, the primary key
The primary key is a unique index, but it must be specified as "PRIMARY KEY". If you've ever used columns of type AUTO_INCREMENT, you're probably familiar with concepts like primary keys. Primary key 1 is usually specified when creating a table, such as "CREATE TABLE tablename ([...]] , PRIMARY KEY (list of columns); ". However, we can also add a primary key by modifying the table, for example, "ALTER TABLE tablename ADD PRIMARY KEY"; ". Each table can have only one primary key. (the primary key is the equivalent of an aggregate index, which is the fastest index to find)
4. Single-column index and multi-column index
Indexes can be single-column or multi-column.
(1) single column index is a common index of 1 column field, a common index.
(2) a multi-column index is an index with multiple column fields
alter table add index sy(name,age, score);
The index sy is a multi-column index. Multi-column indexes are valid in the following situations:
select * from student where name='jia' and age > ='12' //where condition contains the first column field and of the index
The second field
select * from student where name='jia' //where condition contains only the first column field
select * from student where name='jia' and score < The 60//where condition contains the first column field and the third word
Period of
Summary: a multi-column index is only valid if the where condition contains the first column field in the index
5. Select the index column
How should you select an index column? First, look at the query criteria