Mysql index concept and design principles

Key type:

1. Primary key

2. Unique key

3. Foreign key

Index Type:

1. Hash index

2. Ordered index (btree/rtree)

Creation of the index:

1. Primary key creates two indexes: Hash index and ordered index

2. Unique key creates two indexes: Hash index and ordered index

3. Generally defined indexes create only ordered indexes

Primary key and unique key each index uses the scene:

1. When the operation type is =, use the hash index

2. When the operation type is range, use the ordered index

Index usage principles:

1. The primary key-based operation is the fastest and requires the full use of the primary key

2. For primary and unique keys if no scope operation is required, we recommend using the using hash to restrict the primary key and unique key to create only the hash index

3. Index based on type int is faster than the varchar type based index speed

Mysql index concept and design principles