Shulou(Shulou.com)05/31 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.