mysql index learning summary

Indexes

The index is mainly for the columns in the table. The purpose of adding the index is to speed up the query. The index can add a single column or multiple columns

1 general index

1.1 adding indexes when creating tables

A normal index can be created on any column without adding any restrictions (unique, non empty, etc.) to the index when creating this kind of index

Format:

create table tableName(
propname1 type1, propname2 type2,
INDEX|KEY indexName (propname1(length) ASC|DESC));

INDEX|KEY indicates that one of them can be selected. Index is generally used for index, indexName can be omitted and length can be omitted;

ASC|DESC means you can select one. ASC means it will be displayed in ascending order when querying index fields, DESC means it will be displayed in descending order when querying index fields, and ASC|DESC can also be omitted

[example] a student table has id, name, class and teacher fields, and an index is added to the name field

create table student(id int, name varchar(4),
class_name varchar(5), teacher varchar(4),
INDEX index_name(name);

1.2 adding indexes to existing tables

Format:

ALTER table tableName ADD INDEX|KEY indexName(porpname(length) ASC|DESC);

INDEX|KEY indicates that one of them can be selected. Index is generally used for index, indexName can be omitted and length can be omitted;

ASC|DESC means you can select one. ASC means it will be displayed in ascending order when querying index fields, DESC means it will be displayed in descending order when querying index fields, and ASC|DESC can also be omitted

2 unique index

Set a UNIQUE index for a column, which needs to be a UNIQUE attribute

2.1 adding indexes when creating tables

Format:

create table tableName(
propname1 type1, propname2 type2,
UNIQUE INDEX|KEY indexName (propname1(length) ASC|DESC));

Compared with ordinary indexes, you only need to add UNIQUE before INDEX

2.2 adding indexes to existing tables

Mode 1 format:

ALTER table tableName ADD 
UNIQUE INDEX|KEY indexName(porpname(length) ASC|DESC);

Mode 2 format:

create UNIQUE INDEX|KEY indexName on tableName(
propname(length) ASC|DESC);

3 full text index

Full text index is mainly used to build word segmentation based index for the fields of character type (varchar/char/text). The fields added with index in query conditions are similar to fuzzy query

3.1 adding indexes when creating tables

Create format:

create table tableName(
propname1 type1, propname2 type2,
FULLTEXT INDEX|KEY indexName (propname1(length));

Compared with ordinary indexes, you only need to add FULLTEXT before INDEX

3.2 adding indexes to existing tables

Create format:
Mode 1:

ALTER table tableName ADD 
FULLTEXT INDEX|KEY indexName(porpname(length));

Mode 2 format:

create FULLTEXT INDEX|KEY indexName on tableName(
propname(length));

3.3 use index

Format:

select * from tableName where MATCH(propname)AGAINST('keyword');

4 multi column index

Multi column index is to add indexes to multiple columns. The multi column index will be triggered only when the first index field is used in the query criteria

4.1 adding indexes when creating tables

Create format:

create table tableName(
propname1 type1, propname2 type2,propname3, type3,
INDEX|KEY indexName (
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

When querying, use: select * from tablename where prodname > 0;
The first index field is used as the query condition, and the multi column index will be triggered

4.2 adding indexes to existing tables

Create format:
Mode 1:

ALTER table tableName ADD 
INDEX|KEY indexName(
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

Mode 2 format:

create INDEX|KEY indexName on tableName(
propname1(length)ASC|DESC, 
propname2(length)ASC|DESC, 
propname3(length)ASC|DESC);

5 view index execution

Using explain query statements
The conditions of the query statement contain index fields, and the execution of the index will be displayed

Among them,
key: the index used. If it is null, it means there is no index
possible_keys: displays the index applied to the table
key_len: is the number of bytes used in the index

6 hide index

The index can be hidden so that the index will not be triggered. It can be used to test the index
Format:

ALTER table tableName INDEX indexName INVISIBLE; //Hide index
ALTER table tableName INDEX indexName VISIBLE; //Unhide

7 delete index

The index may reduce the update speed of the table, and the index can be deleted
Format:

DROP INDEX indexName ON tableName;

If you need to update the index, you can delete it before creating it

Tags: MySQL

Posted on Sat, 11 Sep 2021 19:35:25 -0400 by skbanta