“This is the 21st day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”
What is the index?
If you’ve been to a library, you probably know the library search system. The library has prepared a search catalogue for books, including title, isbn, corresponding location information, including which section, which shelf, which floor. We can quickly get the location of the book and get the book we need through the title or isbn
The index in MySQL is equivalent to the search directory in the library. It is a storage structure that helps the MySQL system to quickly retrieve data. We can retrieve the value of the index field based on the query criteria in the index, and then quickly locate the data record without traversing the entire table. Moreover, the more fields in the data table, the more data records in the table, the more significant the speed increase
For example: There are 4 million pieces of data in the sales statement of a store of FamilyFamily. Now I want to check the sales of the product with the number of 100 on 2021-11-26. The query code is as follows
SELECT quantity, price, datetime FROM TABLE 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 | | | | 220.00 1.000 2020-11-26 08:56:37 | + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 2 rows in the set (8.08 SEC)Copy the codeYou can see that there are two records, but it takes eight seconds, which is very slow. At the same time, I did not do table association here, this is only a single table query, and only a few months of data for a store. However, the headquarters collects all the data of the stores together, so the query speed is slower. Such query efficiency is definitely unacceptable to us
At this point, we can add indexes to the table
Single field index
MySQL supports single-field indexes and composite indexes. Single-field indexes are commonly used in MySQL. How do I create a single-word field index
Create a single-field index
- Create an index directly to an existing table using the CREATE statement
- Create indexes at the same time as the table is created
- Create indexes by modifying tables
Syntax for creating indexes directly on tables:
createThe INDEX INDEX nameON TABLETable name (field)Copy the codeSyntax for creating an index while creating a table
create tableTable name (field data type,...... {INDEX|KEY} index name (field))Copy the codeAlter table create index syntax:
alter tableThe name of the tableADD {INDEX |KEY} Index name (field)Copy the codeNote: MySQL automatically creates a primary key or unique index for a table that has a primary key constraint or unique index
How single-field indexes work
To understand how indexes work, we need to use the EXPLAIN keyword in MySQL
The EXPLAIN keyword allows you to view details about the execution of SQL statements, including the order in which the tables were loaded, how the tables were joined, and how the indexes were used
First of all, we have this table and this data
Itemnumber Barcode Goodsname Price 1 0001 Book 0.47 2 0002 Pen 0.44 3 0002 Glue 0.19Copy the codeItemnumber indicates the auto-increment primary key, barcode indicates the id, goodsname indicates the product name, and price indicates the product price
Then we tried using a 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 the code- Type =range: queries a specific range of data records using the index
- Rows =2: Indicates the number of records to read (i.e., records with itemNumber 2 and itemNumber 3)
- Possible_keys =PRIMARY: Possible PRIMARY key index is available
- Key =PRIMARY: The index that is actually used is the PRIMARY key index
- Extra =using WHERE: Further explains the execution details of SQL statements
Through this example, we can find that with an index, we can quickly locate the index by querying the index, and then find the corresponding data for reading, which greatly improves the speed of the query
How do I select index fields
In the above query, we selected the primary key field as the index field. You may ask, why not select another field as the index field? This is because itemNumber is used as a query condition, and we used goodsName as a query condition, so we can index goodsName as well
However, it is recommended that you select index fields that are often used for filtering. Only in this way can index play a role and improve the efficiency of retrieval
How to create composite indexes (composite indexes)
Syntax for creating indexes directly on tables:
createThe INDEX INDEX nameON TABLETable name (field1, the field2,...).Copy the codeCreate an index while creating a table
create tableTable name (field data type,...... , {INDEX|KEY} Index name (field1, the field2,...). )Copy the codeCreate index when modifying table:
ALTER TABLEThe name of the tableADD { INDEX |KEY} Index name (field1, the field2,...).Copy the codeAgain, use the table above as an example
If we add indexes to goodsName and barcode, we need to find records with goodsname as a book and code 0001. With combined indexes, the query speed will be significantly improved
Index Field Name Field Index Type Index Method Test Goodsname, barcodeUNIQUE 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 the codeYou can see that an index named test is available as an alternative, and test is selected as the index. Second, we add primary key ItemNumber filtering condition in THE WHERE condition, so multiple alternative indexes will be presented. If there are multiple indexes, and the fields of these indexes also appear in the query as filtering fields, MySQL will choose the optimal index to perform the query operation
However, when we use the above federated index, we can also greatly optimize the query speed
The principle of composite indexes
The multiple fields of a composite index are ordered and follow the principle of left-aligned. For example, the sorting method of the composite index we created is Goodsname and barcode, so the screening conditions should follow the principle from left to right. If the terminal is used, the following 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 the codeFor example, we only used barcode as the field above, so it can be seen that the joint index is not used. Then, the second search result is Goodsname, which conforms to the leftmost principle, so the joint index can be used.
If there is a range, suppose we add price to the syndication index, and place price to the far left, if your query looks 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 the codeIf your price>1000 does not meet the content, it will also break, and the following index will not be used
Remove the index
If you want to drop an index, you can use:
DROPThe INDEX INDEX nameONThe name of the table.Copy the codeOf course, some indexes cannot be dropped in this way, such as primary key indexes. You must modify the table to delete the indexes. The syntax is as follows:
ALTER TABLEThe name of the tableDROP PRIMARYThe KEY;Copy the codeFinally, let me tell you about the cost of indexing. Indexes can improve the efficiency of query, but there are costs to build indexes, mainly in two aspects: the cost of storage space and the cost of data operation
- The storage space cost refers to the storage space required by the index
- The overhead of data manipulation refers to the fact that whenever a table changes, whether it is inserting new data, deleting old data, or even modifying data, if index fields are involved, the index itself needs to be modified to ensure that the index points to the correct record
Therefore, the more indexes, the better. Creating indexes has storage and operation costs, which need to be taken into consideration