Use of MySQL indexes

Explain keywords and federated index optimizations for databases:

This article briefly explains the optimization of MySQL queries and the use of explain statements. (Novice to)

Because this article is query-oriented, intuitively, first we create a table:
The student structure is as follows

Mysql> Show create table student;
+---------+----------------------------------------------------------
--------------------+
| Table | Create Table
|
+---------+----------------------------------------------------------
--------------------+
| Student | CREATE TABLE ' Student ' (
' ID ' int (one) not NULL auto_increment COMMENT ' self-growing primary key ',
' CName ' varchar (DEFAULT NULL),
' ClassName ' varchar DEFAULT NULL,
' schoolname ' varchar DEFAULT NULL,
PRIMARY KEY (' ID ')
) engine=innodb auto_increment=38 DEFAULT Charset=utf8 |
+---------+----------------------------------------------------------
--------------------+
1 row in Set (0.00 sec)

I preset some data in the table as follows:
Mysql> select * from student;
+----+-------------+-----------+------------+
| ID | CName | ClassName | Schoolname |
+----+-------------+-----------+------------+
| 12 | Rockderia1 | One Class A year | Garden Secondary School |
| 13 | rockderia2 | One Class A year | Garden Secondary School |
| 14 | Rockderia3 | One Class A year | Garden Secondary School |
| 15 | Rockderia4 | One Class A year | Garden Secondary School |
| 16 | Rockderia5 | One Class A year | Garden Secondary School |
| 17 | Rockderia6 | One Class A year | Garden Secondary School |
| 18 | Rockderia7 | One Class A year | Garden Secondary School |
| 19 | rockderia8 | One Class A year | Garden Secondary School |
| 20 | Rockderia01 | Two Classes a year | Garden Secondary School |
| 21 | ROCKDERIA02 | Two Classes a year | Garden Secondary School |
| 22 | rockderia03 | Two Classes a year | Garden Secondary School |
| 23 | Rockderia04 | Two Classes a year | Garden Secondary School |
| 24 | rockderia05 | Two Classes a year | Garden Secondary School |
| 25 | rockderia06 | Two Classes a year | Garden Secondary School |
| 26 | Rockderia07 | Two Classes a year | Garden Secondary School |
| 27 | rockderia08 | Two Classes a year | Garden Secondary School |
| 28 | Rockderia11 | One Class A year | Cold Front Secondary School |
| 29 | Rockderia12 | Two Classes a year | Cold Front Secondary School |
| 30 | Rockderia13 | Two Classes a year | Cold Front Secondary School |
| 31 | ROCKDERIA14 | Two Classes a year | Cold Front Secondary School |
| 32 | Rockderia21 | One Class A year | Pioneer Academy |
| 33 | Rockderia22 | One Class A year | Pioneer Academy |
| 34 | Rockderia23 | One Class A year | Pioneer Academy |
| 35 | Rockderia24 | One Class A year | Pioneer Academy |
| 36 | Rockderia25 | Two Classes a year | Pioneer Academy |
| 37 | Rockderia26 | Two Classes a year | Pioneer Academy |
+----+-------------+-----------+------------+
The amount of data is not particularly much. So no matter how we query, in fact, we can quickly get results. This is also a novice learning database when it is easy to fall into a misunderstanding. When it comes to big data, sometimes it turns out that our query results are sometimes slow.

For example, if the table above the data is 1 million rows, if you use the school name, class name, name to lock the results of the query, then you will find that the results will take a lot of time. As follows:

SELECT * FROM student where CName = "Rockderia1" and ClassName = "One year class" and Schoolname = "Garden Secondary School";

However, if we use another search statement:

SELECT * FROM student where ID = 12;

You will find that you will get results in an instant. Let's take a look at the Explain keyword first. This keyword is queried specifically for the SELECT statement, and is used just before the SELECT statement. Let's execute the following statement:

Explain select * FROM student where CName = "Rockderia1" and ClassName = "One year class" and Schoolname = "Garden Secondary School";

Get the result set
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+
| 1 | Simple | Student | All | NULL | NULL | NULL | NULL | 26 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+------+-------------+

For this result set, we first look at these fields type, key, rows.

which
[1]type shows the type of access, which is an important indicator, and the resulting values from good to bad are:

System > Const > Eq_ref > Ref >fulltext > Ref_or_null > Index_merge > Unique_subquery >index_subqu ery > Range > Index > All

Our all is the worst ...

[2]key shows the keys that MySQL actually decides to use.] If no index is selected, the key is null.

We did not use the index, the value is null

[3]rows Reality this number indicates how much data MySQL will traverse to find.
Our query has traversed 26 data, that is, the number of rows of data in the entire table ...

Thus, if you do not do the processing, this query statement is actually the table of 26 data are used as query target results. The results are then individually determined to satisfy the conditions of where.

Let's do the following statement:

Mysql> Explain select * FROM student where ID = 12;
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | Simple | Student | Const | PRIMARY | PRIMARY | 4 | Const | 1 | NULL |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+

We see that there is a big difference between the sentence and the above statement. The number of statements traversed first turns into one (rows = 1), and key also has a value, called primary. The type becomes a very high-efficiency const

In fact, the biggest difference between the two statements is that the following statement our search criteria used in the index.

This brings up a concept: index.
There are a few questions to know about the index:

1. What is the purpose of the index?
The purpose of the index is to increase the query speed.

2. What are the drawbacks of indexing?
There is no free lunch, so while improving query efficiency, maintaining the index needs to take time to maintain when the table additions and deletions are changed, while the index requires space to maintain (maintain a certain structure).

3. Index classification
Indexes are generally divided into: Ordinary index, unique index, self-increment primary key, ordinary primary key.

4. What is the difference between an index and a primary key?
[1] A primary key is a column or combination of columns that can uniquely mark a row of data. The focus is uniqueness. The composition of the index does not necessarily mark a single row of data. Unless it is a unique set of indexes.
[2] The unique index is similar to the primary key, and is not allowed to be duplicated. However, the value of the unique index may be empty. The primary key is not available. So it can be said that the primary key is a unique index, but not the other way.
[3] Creating a primary key will create an index by default.

5. How the index is queried.
The purpose of an index is to make it easy to find, and he needs to divide it with real data. Here I make an analogy. Every row of data in the database, we compared him to a room in a hotel. The index system is like a floor plan that draws all the rooms. The common self-growing primary key, which comes with an index,
So maintaining this set of indexes requires that the room number be marked on each room in the floor plan. So the waiter received the task to 301 room to send water, the waiter did not need to go into the room into the house asked: This is not 301 ah? There's your water! But a look on the floor plan, found 301 of the location
In the third floor plugging head, directly past to 301 water. This is why the data traversed at the time of the primary key query is only 1.
But sometimes the demand is not so simple, if the hotel room is fixed, there are 12,344 beds. Waiter again received demand, room inside 1th bed live Zhang San, 2nd bed Live John Doe, 3rd bed live with Zhang Fei's that room to eat. The waiter looked at the floor plan, the instant Meng forced. Helpless He only
All right, let's go to the room and ask who is bed 1th. Who is the second bed? On the number, still can't stop, because, Qiao! Maybe there is another room a bed also called Zhang San, two bed also called John Doe, three bed also called Zhang Fei, you also have to give him to get a eat! So not much to explain, all the rooms have to be ink again.
Actually, just.
SELECT * FROM student where CName = "Rockderia1" and ClassName = "One year class" and Schoolname = "Garden Secondary School";
This is the process of completion. Now we can finally understand why the query is so slow when there are a lot of data.

This time will say, on the floor plan to stick each room of the person's name is not good! Yes, this is the process of customizing the index! Of course it takes a long time to maintain the index (modify the floor plan), but once the maintenance is complete, it will be incredibly simple to look for the requirements (all the query actions are in the floor plan,
That is, the index is done)

6. How the index is implemented.
The above example is very intuitive, in fact, even if all the data to be queried in the index to maintain, in the index query when also need a structure. The most convenient search structure for indexes may be (B-tree, + + tree) or hash structure. The exact structure depends on what kind of database storage engine you use.
(InnoDB is the name of the engine). The hash structure has the lowest query complexity and locks a value directly from the coordinates. The query complexity of the B-tree is the depth of the tree. However, fuzzy queries are supported.

To get a quick look at the index, let's try it out. Try creating an index for the student table. Makes the above query more efficient.

Create INDEX Nameclassschool on student (CName, ClassName, schoolname);

We found one more message in Show create TABLE: KEY ' Nameclassschool ' (' CName ', ' ClassName ', ' schoolname ')

To do it again:
Explain select * FROM student where CName = "Rockderia1" and ClassName = "One year class" and Schoolname = "Garden Secondary School";

Mysql> Explain select * FROM student where CName = "Rockderia1" and ClassName = "One year class" and Schoolname = "Garden Secondary School";
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+- -------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+- -------------------------+
| 1 | Simple | Student | Ref | Nameclassschool | Nameclassschool | 189 | Const,const,const | 1 | Using where; Using Index |
+----+-------------+---------+------+-----------------+-----------------+---------+-------------------+------+- -------------------------+

As you can see, rows becomes 1. Description In addition to the index operation, only one row of data was found/manipulated.

Use of MySQL indexes