MySQL Index basics

MySQL Index basics

1. Basic concepts:

An index is a special database structure that can be used to quickly query specific records in a database table. Indexes are an important way to improve database performance. An index is created on a table and is a structure that sorts the values of one or more columns in a database table. can improve query speed. In MySQL, all data types can be indexed.

1. Advantages of the index:

Increase query speed

Control the uniqueness of a record by using the uniqueness of the index

Reduce the time to group and sort in a query

Can speed up the connection between table and table

1. Disadvantages of the index:

Storage index consumes disk space

Perform data modification operations (INSERT, UPDATE, DELETE) to produce index maintenance

Rebuilding the index every time you modify the table structure

1. Classification of indexes

Normal index: This is the most basic index, it does not have any restrictions.

Unique indexes: Similar to normal indexes, except that the values of indexed columns must be unique, but allow null values (note and primary key are different)

Full-Text indexing: MySQL supports full-text indexing and full-text retrieval from version 3.23.23, Fulltext indexes can only be used for MyISAM tables;

Single-column index, multicolumn index

1. Create an index

– Create a normal index:

Create index name on table name (column)

ALTER TABLE name add index index name (column)

– Create a unique index:

Create unique index index name on table name (column name)

ALTER TABLE name add unique index name (column)

1. Drop INDEX: Dropped index index name on table name
2. Index design principles

To make the index more efficient to use, you must consider which fields to create indexes on and what types of indexes to create when you create the index.

A) Select a uniqueness Index

b) Indexing fields that often require sorting, grouping, and Union operations

c) Indexing a field that is frequently used as a query condition

d) Limit the number of indexes

e) Use an index with a small amount of data as much as possible

f) Use prefixes as far as possible to index

g) Delete indexes that are no longer used or are seldom used

MySQL Index basics