Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

Why you need an index

Take our most familiar dictionary as an example, when we need to search for a strange word, we need to find the word in the contents page according to strokes, radicals, or general pinyin conditions, and then know which page the word is located in the dictionary.

Consider the other case, where we simply scroll through the entire dictionary until we find the word we need đŸ€”. This operation takes a lot less time than the previous one.

In this case, the dictionary’s table of contents plays the role of [index], and its benefit is to help us greatly improve the speed of query

Note: in mysql, due to the particularity of the data: ② Data will be constantly changed [modified/deleted];

We need to be extra careful when using indexes!! Data changes mean that indexes need to be maintained synchronously!! In addition, indexing requires extra storage space for maintenance!! Use caution!!

Normal index

create index index_name on table_name (column_name)
Copy the code

The only index

A unique index adds a unique constraint unique compared to a normal index

create unique index index_name on table_name (column_name)
Copy the code

Created when the table is created

create table test (
  id int not null,
  uname varchar(20),
  phone varchar(20),
  index ind_uname (uname(20)),  -- ind_uname is our custom index name and can be omitted using the default
  unique ind_u_phone (phone(20))   Create a unique index
);
Copy the code

Modify the

-- Appends normal indexes
alter table table_name add index [index_name] (column_name)
-- Appends unique indexes
alter table table_name add unique [index_name] (column_name(length))
Copy the code

As shown in the figure, if no index name is specified, mysql creates the index automatically using the field name +’_’+n mode

delete

drop index [index_name] on table_name;
Copy the code

Displays the current table index

show index from test
Copy the code