MySQL Index of the database

1 Overview

This article describes the concepts associated with indexes, and uses explain to analyze indexes

2 Related concepts of indexing

An index is an index that extracts data from a field on a table and builds a unique data structure;

The role of the index: speed up the query operation; side effects: Reduce write operation performance;

A subset of data in a table: extracts data from one or some of the fields in a table and saves it as data organized by a particular data structure;

A field or some field: The field used in the WHERE clause;

The index has an impact on the write operation, and the write operation can only be placed in the corresponding location.

The index itself is a balanced tree. Its sort is layered.

Indexes can speed up read operations, but they are the opposite effect of write operations. Because an index is written more than once, the index is created in moderation.

The index is to be built on top of the query criteria. The criteria that the index is commonly used to establish in the where statement

An index is a data structure that is set up to speed up an operation.

MySQL is indexed, so queries on massive data are more efficient

Index Benefits:

Reduce the amount of data that needs to be scanned, locate resources according to the index, reduce the number of IO;

can help avoid sorting operations because the index itself is stored in order, avoiding the use of temporary tables;

Help to convert random io to sequential IO, indicating system performance;

3 Index Type

Here are two types of indexes: B + Tree and hash

B + TREE

Balance tree, sequential storage, each leaf node to the root node of the same distance; After extracting the data out in order to take up a certain amount of space, the MySQL database index is generally b tree index, index to the left prefix, suitable for the range type of data query, the leftmost data is critical,

Query type for B + Tree index: Full key value, key value range, or key prefix;

Full-value matching: Exact Match of a value, also called full-key value matching.

WHERE COLUMN = ' value ';

Match the leftmost prefix: match only the beginning part exactly;

Wehre COLUMN like ' prefix% ';

Match Range Value:

Match exactly one column, and the range matches the other; If the first field is the same, then the second field is indexed according to the first field index

A query that accesses an index only, also known as an overlay index; this is a quick way to query

Index (name), which indicates that the name field is indexed, when you create a query condition based on name, you only need to look up the index, and you do not need to find it in the table, which is more efficient, as follows

SELECT name from students WHERE Name is like ' l% ';

Do not apply B + Tree index:

If the query condition does not start at the leftmost column, the index is invalid, and if there is an index (age,fname), then the query condition is WHERE fname= ' Jerry '; , where age>30 and Fname= ' Smith '; the index is not valid at this time because the query is not started from the left index

You cannot skip a column in an index;

Indexed as index (name,age,gender)

Condition: WHERE name= ' black ' and age > 30; the index is valid

Condition: WHERE name= ' black ' and gender= ' F '; Skip age This index entry is an invalid index

If a column in the query is a range query, the columns to the right of it are no longer able to use the index optimization query;

WHERE age>30 and Fname= ' Smith ', when the age>30 on the left side of the condition matches to a larger range, then Fname= ' Smith ' is invalid. If the condition is where fname= ' Smith ' and age>30; are valid

Hash index

Based on the hash table, it is especially suitable for the exact matching query of the value and the key value index.

A hash index is also called a key-value index. Based on key lookup values, both MyISAM and InnoDB do not support hash indexing, and only the memory storage engine supports hashing. However, the INNODB supports adaptive hash indexing.

Applicable scenarios:

Only the exact comparison query that supports equivalence, for example =, in (), <=>;mysql only supports explicit hash indexes on memory storage engine;

Index (name)

Where name= "Sunny"

No scenes:

All non-exact value queries;

4 High-performance indexing strategy

The following is a high-performance indexing strategy, which is the principle of good indexing

(1) Use the column independently in where, and try to avoid its participating operation;

WHERE age+2 > 32; Do not allow such an operation

(2) left prefix index: the number of characters that the index is built on the leftmost side of a field, to be evaluated by index selectivity

Index selectivity: The ratio of non-repeating index values to the total number of records in the data table;

(3) Multi-column index:

Multiple query criteria for and connections are more suitable for multi-column indexes than for multiple single-key indexes;

where gender= ' F ' and age>18; created index (gender,age) and not created as index (gender) index (age) Two single-key index, because index (gender,age) is more efficient

(4) Select the appropriate index column order: The most selective left side;

5 explain to analyze index validity

Explain parses whether a SELECT statement uses indexes and how indexes are used to determine if the index you have defined is valid and deletes the unused index

EXPLAIN [Explain_type] SELECT select_options

Explain_type:

EXTENDED

| Partitions

Output Result:

Id:1

Select_type:simple

Table:students

Type:const

Possible_keys:primary

Key:primary

Key_len:4

Ref:const

Rows:1

Extra:

Output result Field description

ID: The number of the first SELECT statement in the current query statement, possibly a complex query, with multiple queries

Example: Complex queries

MariaDB [sunny]> Select Name,age from Students where > (select AVG (age) from students);

The types of complex queries are mainly three kinds:

Simple sub-query

For subqueries in from

Federated queries

Note: An additional anonymous temporary table will appear for the analysis results of the federated query;

Select_type: Query Type:

Simple query: Easy

Complex query:

Simple subquery: subquery

Subquery for from: DERIVED

The first query in a federated query: PRIMARY

Additional queries after the first query in a UNION query: union

Temporary table generated by UNION query: Union RESULT

Table: query against the tables;

Type: association types, or access types, are also called access methods, that is, how MySQL queries the rows in a table, that is, the type of data that is obtained, as follows:

All: Full table scan;

Index: A full table scan based on the order of the indexes, but at the same time if the "Using index" appears in the extra column, the overwrite index is used;

Range: Scope scan is scoped to the index; the scan position starts at an item in the index and ends with another item;

Ref: All rows that match to a single value in the table returned by the index (more than one row matching the given value);

Example:

There are multiple AGE=60 records in the following results, the type is ref

MariaDB [sunny]> Explain select name from students where age=60;

Eq_ref: A single row that matches to a single value in the table returned by the index, only one row is returned, but it needs to be compared to an additional reference value rather than a constant, such as through a variable;

Const,system: Compared to a constant, and returns only one row, both of which are most efficient.

Example

In the following result, the ID is the primary key, and the resulting type is const

MariaDB [sunny]> Explain select name from students where id=60;

Possiable_keys: Indexes that may be used in a query may have multiple indexes;

Key: The index used in the query, the really used index if there is, there is only one;

Key_len: The length of the index used in the query, representing the first few bytes of the index;

Ref: The column or constant value that is referenced when the query is completed using the index displayed by the key field;

Rows:mysql estimates the number of rows that need to be read in order to find all target items; The value is greater than or equal to the qualifying row;

Extra: Additional Information

Using index: A query made with an overlay index; an efficient method.

Using Where: Once you get the data, you have to filter again to get the final result;

Using temporary: A temporary table is used to complete the query;

Using Filesort: An external index is used to sort the results, and if the query results are more than 16M in size, it is not possible to sort using temporary tables in memory, so it is inefficient to put the data on disk and read it again, so that if you see additional information with a using Filesort, it is recommended to optimize, otherwise execution performance will be poor.

Site access is slow, it is possible that the site is under a DDoS attack, or the site is too large, or it may be the business layer has a problem, such as the code has a bug, or a data layer problem, because the concurrency is too large or initiated a replication query, index creation is unreasonable.

Example

Create a table, insert a large amount of data with a for loop, and then use explain to analyze the validity of the index

Create a table

CREATE TABLE students (ID int unsigned auto_increment primary key,name char () not null,age tinyint unsigned,gender enum (' F ', ' M '), major varchar (200));

Inserting data

MariaDB [sunny]> INSERT INTO students values (1, "Sunny", +, "M", "math"), (2, "Tracy", +, "F", "Engilsh");

Note that in order to generate a large amount of data, the next step is to generate a large amount of data in the shell and insert it directly in a for loop.

Because the field gender is an enumerated type, this defines an array gender (' F ' M ') to be generated

Defining arrays

[[email protected] sunny] #gender = (' F ' M ')

Using a For loop to insert large amounts of data, you can execute SQL statements in the shell with MYSQL-E

[[email protected] sunny] #for i in {1..1000};d o mysql-uroot-ppass123456-e "insert into sunny.students (Name,age,gender) VALUES (' stu$i ', ' $[$RANDOM%80+18] ', ' ${gender[$RANDOM%2]} '); "; Done

Explain parsing query statements

MariaDB [sunny]> Explain SELECT * from students where age>90;

+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

| 1 | Simple | Students | All | NULL | NULL | NULL |  NULL | 950 | Using where |

+------+-------------+----------+------+---------------+------+---------+------+------+-------------+

1 row in Set (0.01 sec)

The above results indicate that the query type of the SELECT statement is simple, is executed in the students table, the type of data obtained is all, that is, doing a full table scan, loading the entire table into memory, traversing all the data results, loading 950 rows of data, using the Where condition. Possible_keys is null, indicating that the index is not used

Create an index

#创建单索引

MariaDB [sunny]> CREATE INDEX age in students (age);

#创建多字段索引

MariaDB [sunny]> CREATE INDEX age_and_name on students (AGE,NAME);

View index, the same key_name appears multiple times, indicating that it is a multi-field index

MariaDB [sunny]> Show index from students;

Delete Index

MariaDB [sunny]> DROP index age_and_name on students;

Once the index has been created, analyze it again with explain

MariaDB [sunny]> Explain SELECT * from students where age>90;

+------+-------------+----------+-------+---------------+------+---------+------+------+----------------------- +

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+------+-------------+----------+-------+---------------+------+---------+------+------+----------------------- +

| 1 | Simple | Students | Range | Age | Age | 2 |  NULL | 104 | Using Index Condition |

+------+-------------+----------+-------+---------------+------+---------+------+------+----------------------- +

1 row in Set (0.00 sec)

The above results indicate that the query type of the SELECT statement is simple, is executed in the students table, the type of the obtained data is range, that is, the sort, Possible_keys indicates that the index that may be used is age,key the actual index is Age,key_len Only the first 2 bytes of the index entry are used, loading 104 rows of data, using the indexed conditional query

MariaDB [sunny]> Explain select name from students where age>90;

+------+-------------+----------+-------+------------------+--------------+---------+------+------+------------ --------------+

| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |

+------+-------------+----------+-------+------------------+--------------+---------+------+------+------------ --------------+

| 1 | Simple | Students | Range | Age,age_and_name | Age_and_name | 2 |  NULL | 103 | Using where; Using Index |

+------+-------------+----------+-------+------------------+--------------+---------+------+------+------------ --------------+

1 row in Set (0.01 sec)

The result Possible_keys has two age,age_and_name index, the actual index key is Age_and_name,extra has a using index to represent the overwrite index here if the index (name,age), This is not indexed index (name,age), but the simple age index

More about explain, suggested view article: MySQL query optimization explain in-depth analysis: http://www.jb51.net/article/38357.htm

MySQL Index of the database