MySQL(12)-Index

Ordinary index-non-clustered index

Ordinary index is to create an index without any additional restrictions, such as only one, non-empty restrictions, this type of index can be created on any data type field.

When creating a table, create an index table

table of Contents

​ Ordinary index-non-clustered index

​ Unique index

​ Primary key index

​ Full-text index

​ Create a multi-column index


Create a normal index on an existing label

Unique index

The value of the index column is required to be unique , and it needs to be marked with UNIQUE

Create a unique index when creating a table

Create a unique index on an existing label

Create a unique index through the alter table statement

Primary key index

Increase the primary key through the keyword alter

Full-text index

Limited to char, varchar, text fields

Restrict the creation of full-text indexes on MyISAM data tables

Created when the table is built

Create on an existing label

Through alter table

 

Create a multi-column index

When using a multi-column index, in a multi-column index, only when the first column of these columns is used in the query condition, the multi-column index will be used.

 

View index

In command Line Client mode

Delete index

create unique index ix_sno on student(sno)

create index ix_sname on student(sname)

create index ix_sage on student(sage)

create index ix_sno_cno on sc(sno,cno)

show create table student \G

show create table sc \G

drop index ix_sname on student

drop index ix_sage on student

Query the results of the designated student number (such as sno: 1001), and sort the results from high to low; the student number, course number, and results are required to be displayed

In order to improve the query speed, please create a corresponding index table.

create index ix_sno on sc(sno)

create index ix_score on sc(score)

create index ix_sno_cno sc(cno)

Find the average age of all

Find the average age of boys and girls

Find all the information of all the oldest students

Seek information for all students from old to young

In order to improve the query speed, please create a corresponding index table.

create index ix_sage on student(sage)

Do not index or delete the index before inserting a large amount of data, and then create the index;

Insert into the temporary table first, and then insert into the student table with insert into;

  • If you find that the student table is very slow when you insert new rows of data or delete data, what should you do
  • If you find the reality, ask us to have a large number of query tasks for age, as follows
  • If you need to complete the following query statement:
  • Delete the ordinary index table of the age field on the student table
  • View the index information of the student and sc tables
  • Create a combined index table of student number and course number on the sc table
  • Create a common index table for the name field on the student table, and create a common index table for the age field
  • Create a unique index table for the student number field on the student table