Example Analysis of single column Index in MySQL Index Classification
This article shows you the sample analysis of single-column indexes in the MySQL index classification, which is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.
A partitioned table does not support full-text indexes, spatial indexes, foreign key indexes, and primary and unique indexes on a partitioned table must contain all the columns used in the partitioned expression.
Index classification: single column index
If you see type = all or key = null in the result of explain, you can judge that the query scanned the entire table
Mysql > EXPLAIN
Select * from np_order_lyz m where m.order_id = '296285'
+-- +
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-- +
| | 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 45241 | Using where |
+-- +
1 row in set
Add Index
Alter table np_order_lyz add INDEX (order_id)
Mysql > EXPLAIN
Select * from np_order_lyz m where m.order_id = '296285'
Now there is only one row, and the index uses key bit order_id. It is estimated that the number of rows read is much less than before, so the query speed is accelerated.
Mysql > EXPLAIN
Select * from np_order_lyz m where m.order_id = '296285'
+-+-
| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+-+-
| | 1 | SIMPLE | m | ref | order_id | order_id | 8 | const | 1 | Using index condition |
+-+-
1 row in set
Note: indexes can be created repeatedly, but creating duplicate indexes will incur performance overhead, so we should try our best to avoid creating duplicate indexes. When duplicate indexes are found, duplicate indexes should be deleted and only one should be retained.
The above is a sample analysis of single-column indexes in the MySQL index classification. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.
