MySQL index classification and usage analysis _mysql

One, MySQL: index in B-Tree format save

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

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 indexing: from 3.23.23 Version to 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, multiple column index:

Multiple single-column indexes have different query effects from a single, multiple-column index because:

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

5, the leftmost prefix (leftmost prefixing): Multiple-column index, such as: Fname_lname_age index, the following search conditions MySQL will use

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

second, according to the SQL query statement to determine what type of index to create, how to optimize the query

Select an indexed column:

A. During the performance tuning process, it is one of the most important steps to choose which column to create an index on. You can consider using the index mainly

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

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

C. Using short indexes, if you index a string column, you should specify a prefix length that saves a lot of indexing space and improves query speed.

D. Using the left-most prefix

E. Do not overdo indexing, just keep the indexes you need. Each additional index takes up additional disk space and reduces the performance of the write operation.

When you modify the contents of a table, the index must be updated, sometimes you may need to refactor, so the more indexes, the longer it takes.

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

And at some point like (without the wildcard percent or _ the beginning of the case).

MySQL Index classification

In a database table, indexing a field can greatly improve the query speed. By making good use of these indexes, MySQL can be more efficient in querying and running. Indexing is the key to fast searching. The establishment of MySQL index is important for MySQL to run efficiently. Here are some common types of MySQL indexes.
1, the normal type index
This is the most basic type of index, and it has no uniqueness, such as restrictions. Normal indexes can be created in several 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, such as the name of ALTER TABLE TableName ADD index Index (column name 1, column name 2,...);
(3) Specify the index when creating the table, such as CREATE TABLE TableName ([...], the index index name (column name 1, column name
2,...) );
2, unique index
This index is basically the same as the previous "normal index", but there is a difference: all values of an indexed column can only appear once, that is, must be unique. Uniqueness indexes can be created in several 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) Specify the index when creating the table, such as CREATE TABLE TableName ([...], the name of the UNIQUE index (column
table));
3, PRIMARY key
A primary key is a unique index, but it must be specified as "PRIMARY key." If you've ever used a auto_increment type of column, you're probably already familiar with the concept of a primary key. Primary keys are typically specified when creating tables, such as "CREATE TABLE TableName ([...], PRIMARY key (List of columns)"; ”。 However, we can also add a primary key by modifying the table, such as "ALTER table tablename ADD PRIMARY key (a list of columns);" ”。 Each table can have only one primary key. (primary key is equivalent to aggregate index, is to find the fastest index)
4. Single column index and multi-column index
An index can be a single-column index or a multiple-column index.
(1) A single-column index is a commonly used index of a column field, a common index.
(2) A multiple-column index is an index that contains multiple column fields
ALTER TABLE student Add index sy (name,age,score);
Index SY is a multiple-column index, and multiple-column indexes can be effective in the following situations:
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 condition contains only the first column field
SELECT * FROM student where name= ' Jia ' and score<60//where conditions contain the first column field and the third word
Paragraph
Summary: Multiple-column indexes only work if the Where condition contains the first column field in the index
5. Select Index Column
How to select an indexed column, first look at the query criteria, generally the query criteria in the column as an index