Introduction to the content of MySQL database index

This article brings you an introduction to the MySQL database index. It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.

The storage engine is the core of the database. Commonly used MySQL storage engines include InnoDB, MyISAM, and memory. Index is a data structure that speeds up data query.

1. Introduction to index

1.1 Index advantages

1. Query data block
2. Data uniqueness
3. Accelerate the connection between tables

1.2 Index Disadvantages

1. The index will occupy storage space and may be larger than the table data itself
2. It is only good for query, and increases the burden of additions, deletions and modifications

1.3 Precautions for using indexes

1. Use indexes for data with large field value differences (do not use indexes for gender columns)
2. Character fields can be created by selecting only the first few characters of the field Indexes can store data easily.

2. Two important storage engines, InnoDB and MyISAM

2.1 InnoDB

2.1.1 Features

1. Support transactions
2. Process huge amounts of data
3. Table data and indexes are stored separately
4. Support row-level locking
5. Default 6B length primary key
6. The primary key itself is the index, and the index maps not the address, but the primary key, so as to find the row record

##2.1.2 InnoDB index

Only supports B-tree

2.2 MyISAM

2.2.1 Features

Does not support transactions
1. Inserts and searches are extremely fast
2. Does not support transactions
3. The first choice for simply adding data

2.2.2 MyISAM index

Only supports B-tree

2.2.3 memory index

Support B-tree and HASH

MySQL database generally defaults to innoDB storage engine.

3. Index classification

Ordinary index and unique index
1. Single column Index and combined index (combined index: only the leftmost match can be used)
2. Full-text index: Create word segments, map addresses for each word segment, and find the address of the data by finding the word segments in the index.
3. Spatial Index

4. Index design principles

The more the better.
1. Avoid creating too many indexes on frequently updated tables.
2. Tables with small data volume do not use indexes.
3. Indexes should be built on columns with large value differences.
4. Build combined indexes on tables with frequent grouping operations.
5. Use short indexes as much as possible to save disk space.

This article is over here. For more exciting content, you can pay attention to the

MySQL video tutorial on the PHP Chinese website. Column!

The above is the detailed content of Introduction to the content of MySQL database index. For more information, please follow other related articles on the PHP Chinese website!