MySQL Query Performance tuning optimization

first, the concept of the indexIndex: Dictionary-like directory, set up the index can speed up the data lookup, the data constraints;

Type of index type: Primary key index: Guaranteed data uniqueness, cannot be duplicated + cannot be empty normal index: Accelerated data Lookup Unique index: Accelerated find + cannot be combined from complex index (multiple columns):

Third, the index Works 1, index principle: If you want to find the data, not set the index, will be the front-to-back carpet search data, if set up an index, will create an additional table, in a format to save the column data location, easy to find;
2, the data of the Index Table format 2.1, hash format: The column data will be hashed into a hash value (number) corresponding to the storage address of the data; Index Table:
Data Hash value (number) storage location (number) 888 666 988 4442.2. Characteristics: The data in the index table is inconsistent with the data order in the database table; (hash index, suitable for single data lookup, not suitable for range checking
Find
Btree format: Two fork tree, on the left side of the number smaller than their own, the right to put bigger than their own data
Features: Fast Range search Speed

3. Create an index:

Normal index:

CREATE INDEX name on ... Table of: Column
CREATE INDEX Zhanggen on day61. ' User ' (email);

Unique index:

Create unique index index name on table name (column name)
Drop unique index index name on table name

Combined index (leftmost prefix match):

Create unique index index name on table name (column name, column name)
Drop unique index index name on table name
Create INDEX Ix_name_email on Userinfo3 (Name,email,)

Name: Overwrite index, merge index

Overwrite index: Search directly in the index file, do not need to find the database file using the index method;

SELECT ID from day61. ' User ' WHERE id=9999;

Merge index: The way to use multiple single-column indexes to find data together;

Select ID from day61. ' User ' WHERE id=9999 and email= "[email protected]";

The difference between a combined index and an index merge:

Composite index: Multiple columns made into an index

Index merging: Use only multiple single-column indexes when using indexes;

Composite Index Efficiency > Index Merging

Combined index
----(Name,email)
SELECT * from Userinfo3 where name= ' Alex ' and email= ' asdf ';
SELECT * from Userinfo3 where name= ' Alex ';
Index Merge
----Name
----Email
SELECT * from day61. ' User ' WHERE id=1993 and email= ' [email protected] ';
SELECT * from Userinfo3 where name= ' id=1993 ';
SELECT * from Userinfo3 where email= ' [email protected] ';

Application Scenarios:

Two columns are often federated using a federated index
Index Merge: Single column data using index merge

MySQL Query Performance tuning optimization