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 codeSyntax for creating indexes while modifying tables:
alter table Table name ADD {INDEX | KEY} Index name (field)
Copy codeNote: 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 codeCreate index when modifying table:
ALTER TABLE Table name ADD { INDEX | KEY } Index name (Field 1, field 2,...)
Copy codeTake 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