MySQL Series: Index basic operations

1. Introduction to the Index

An index is a special database structure that you can use to quickly query specific records in your data.

The indexes in MySQL include: normal index, uniqueness index, full-text index, single-column index, multicolumn Index, spatial index, and so on.

1.1 Index definition

An index is a combination of one or more columns in a database table that improves the query speed of the data in the table.

An index is a structure that is created in a table to sort the values of one or more columns in a data table.

The MySQL index contains two types of storage: Btree index and Hash index. The InnoDB and MyISAM storage engines support Btree indexes, and the memory storage engine supports hash indexes and Btree indexes, which are hash indexed by default.

Advantages of indexing: You can improve the speed of retrieving data.

Disadvantages of indexes: It takes time to create and maintain indexes, and the time spent increases with the amount of data.

Indexes need to occupy physical space, each index must occupy a certain physical space;

When you add, delete, and modify data, you maintain indexes dynamically, reducing the speed of data maintenance.

Indexes can increase the speed of queries, but they affect the speed at which records are inserted. When you insert a record into an indexed table, the database system sorts by index.

When inserting a large number of records, it is best to delete the index from the table, insert the data, and then create the index when the insert is complete.

1.2 Index classification

The indexes in MySQL include: normal index, uniqueness index, full-text index, single-column index, multicolumn Index, spatial index, and so on.

　　1>. Normal index

When you create a normal index, no restrictions are attached.

　　2>. Uniqueness Index

Use unique to set a uniqueness index. When you create a uniqueness index, the value that restricts the index must be unique.

A unique index that allows you to determine a record more quickly.

A primary key is a unique index of uniqueness.

　　3>. Full-Text Indexing

Use Fulltext to set the index to full-text indexing. A full-text index can only be created on a field of char, varchar, or text type.

Full-text indexing can improve query speed when querying a field of a string type with a large amount of data.

　　Only the MyISAM storage engine supports full-text indexing.

　　4>. Single-column index

　　

1.3 Index Design principles

2. Create an index

MySQL Series: Index basic operations