MySQL index -- how to improve query speed

Posted by xXx_Hobbes_xXx on Fri, 26 Nov 2021 16:21:40 +0100

What is the index?

If you have been to the library, you should know the library's retrieval system. The library has prepared a search catalogue for books, including book name, book number and corresponding location information, including which area, shelf and floor. We can quickly know the location of books and get the books we need through the book title or book number

The index in MySQL is equivalent to the retrieval directory of the library. It is a storage structure to help MySQL system retrieve data quickly. We can retrieve the value of the index field according to the query conditions in the index, and then quickly locate the location of the data record, so we don't need to traverse the whole data table. Moreover, the more fields in the data table, the more data records in the table, and the more obvious the speed improvement

For example, there are 4 million pieces of data in the sales flow table of a store of the whole family. Now I want to check the sales of goods with commodity number 100 on November 26, 2021. The query code is as follows

SELECT quantity, price, datetime FROM Flow meter
WHERE date > '2021-11-26' AND date < '2021-11-27' AND itemnumber = 100;
+----------+--------+---------------------+
| quantity | price | date |
+----------+--------+---------------------+
| 1.000 | 220.00 | 2020-11-26 19:45:36 |
| 1.000 | 220.00 | 2020-11-26 08:56:37 |
+----------+--------+---------------------+
2 rows in set (8.08 sec)
Copy code

You can see that there are 2 records in total, but it took 8 seconds, very slow. At the same time, I don't do table association here. It's just a single table query, and it's just the data of a store for several months. The headquarters collects the data of all stores, and the query speed is slower. Such query efficiency is certainly unacceptable

At this time, we can add indexes to the data table

Single field index

MySQL supports single field index and composite index, and single field index is commonly used. Let's learn the method of creating single field index. How to create a single word field index

Create a single field index

  • Create an index directly to the existing table through the create statement
  • Create indexes while creating tables
  • Create indexes by modifying tables

Syntax for creating an index directly on a data table:

create INDEX Index name ON TABLE Table name (field)
Copy code

Syntax for creating indexes while creating tables:

create table Table name
(
    Field data type,
    ......
    {INDEX | KEY} Index name (field)
)
Copy code

Syntax for creating indexes while modifying tables:

alter table Table name ADD {INDEX | KEY} Index name (field)
Copy code

Note: when setting primary key constraint or uniqueness constraint for a table, MySQL will automatically create a primary key index or uniqueness index

How a single field index works

To know how the index works, we need to use the keyword EXPLAIN in MySQL

EXPLAIN keyword can view the execution details of SQL statements, including the loading order of tables, how tables are connected, and index usage

First, we have such a table and data

itemnumber	barcode	goodsname	price
1	0001	book	0.47
2	0002	pen	0.44
3	0002	glue	0.19
 Copy code

Where itemnumber is the self incrementing primary key, barcode is the number, goodname is the commodity name, and price is the commodity price

Then we try to use range search

EXPLAIN select * from goodsmaster where itemnumber > '1' and itemnumber < '4' and goodsname = 'book'

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | goodsmaster | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |    33.33 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Copy code
  • type=range: indicates that the index is used to query data records in a specific range
  • rows=2: indicates the number of records to be read (that is, records with itemnumber of 2 and 3)
  • possible_keys=PRIMARY: indicates that the primary key index is optional
  • key=PRIMARY: the index used is the primary key index
  • extra=using where: further explains the execution details of sql statements

Through this example, we can find that after having an index, we can quickly locate it by querying the index, and then find the corresponding data for reading, which greatly improves the query speed

How to select index fields

In the above query, we select the primary key field as the index field. You may ask, why not select other fields as the index field? This is because itemnumber is used as a query condition. Of course, we still use goodsname as the query condition, so we can also add an index to goodsname

However, it is recommended that you select the fields that are often used as filter criteria when selecting index fields. In this way, we can play the role of index and improve the efficiency of retrieval

How to create a composite index (composite index)

Syntax for creating an index directly on a data table:

create INDEX Index name ON TABLE Table name (field 1, field 2,...)
Copy code

Create indexes while creating tables:

create table Table name
(
	Field data type,
	......,
	{INDEX | KEY} Index name (field 1, field 2,...)
)
Copy code

Create index when modifying table:

ALTER TABLE Table name ADD { INDEX | KEY } Index name (Field 1, field 2,...)
Copy code

Take the above table as an example

Suppose we add indexes to both goodsname and barcode. What we need to find is that goodsname is a book and the code is 0001. With a combined index, the query speed will be significantly improved

Index field
 name		field				Index type	Index method
test	goodsname, barcode	 UNIQUE		BTREE

EXPLAIN select * from goodsmaster where barcode='0001' and goodsname = 'book';

+----+-------------+-------------+------------+-------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | goodsmaster | NULL       | const | test          | test | 806     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


EXPLAIN select * from goodsmaster where barcode='0001' and goodsname = 'book' and itemnumber=1;
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | goodsmaster | NULL       | const | PRIMARY,test  | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Copy code

It can be seen that one index that can be used as an alternative is called test, and then test is selected as the index. Second, we add the filter condition of the primary key itemnumber to the where condition, so multiple alternative indexes will be presented. If there are multiple indexes, and the fields of these indexes appear in the query as filter fields at the same time, MySQL will choose to use the best index To perform a query operation

However, when we use the above joint index, we can also greatly optimize the query speed

Principle of composite index

Multiple fields of the composite index are in order and follow the principle of left alignment. For example, the composite index created by us is sorted by goodname and barcode, so the filtering conditions should also follow the principle from left to right. If the terminal, the latter conditions cannot use the index

EXPLAIN select * from goodsmaster where barcode='0001';

+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | goodsmaster | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

EXPLAIN select * from goodsmaster where goodsname = 'book';
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | goodsmaster | NULL       | ref  | test          | test | 403     | const |    1 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Copy code

For example, we only use a barcode field above. It can be seen that the joint index is not used. If the second search result is goodsname, which conforms to the leftmost principle, the joint index can be used.

If there is a range, suppose we add price to the joint index and place price on the far left. If your query statement becomes like this

EXPLAIN select * from goodsmaster where price > 1000 and goodsname = 'book' and barcode='0001';

+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table       | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | goodsmaster | NULL       | range | test          | test | 4       | NULL |    1 |    33.33 | Using where; Using index |
+----+-------------+-------------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
Copy code

If your price > 1000 does not find the content that meets the requirements, it will also be interrupted and the subsequent indexes cannot be used

Delete index

If you want to delete the index, you can use:

DROP INDEX Index name ON Table name;
Copy code

Of course, some indexes cannot be deleted in this way, such as the primary key index. You must delete the index by modifying the table. The syntax is as follows:

ALTER TABLE Table name DROP PRIMARY KEY;
Copy code

Finally, let me tell you about the cost of indexing. Indexing can improve the efficiency of query, but indexing also has costs. There are two main aspects, one is the cost of storage space, and the other is the cost of data operation

  • The overhead of storage space means that the index needs to occupy storage space separately
  • The cost of data operation means that once the data table changes, whether it is inserting a new data, deleting an old data, or even modifying the data, if the index field is involved, the index itself needs to be modified to ensure that the index can point to the correct record

Therefore, the more indexes, the better. Creating an index has storage overhead and operation overhead, which need to be considered comprehensively


 

Topics: Java Database Back-end Programmer Distribution