One 、 Index classification
1.1、 There are several categories of indexes :
1) Single value index : That is, an index contains only a single column , A table can have multiple single-column indexes .
2) unique index : The value of the index column must be unique , But you can have an empty value .
3) Composite index : An index contains multiple columns , Such as INDEX MultiIdx(id,name,age)
4) Full-text index : Only in MyISAM Only on the engine , Only in CHAR、VARCHAR、TEXT Full-text indexes are used on type fields .
5) Spatial index : A spatial index is an index of a field of a spatial data type .
Two 、 Index operation
2.1、 Create index
grammar :CREATE INDEX The index name ON table (column[,column]...);
CREATE INDEX ID_INDEX ON emp (ID);CREATE INDEX NAME_INDEX ON emp (NAME);
2.2、 Delete index
grammar :DROP INDEX The index name ON Table name ;
DROP INDEX NAME_INDEX ON emp;
2.3、 Look at the index
grammar :SHOW INDEX FROM Table name ;
SHOW INDEX FROM emp;
2.4、 Automatic index creation
1) When a primary key is defined on a table , It will automatically create a corresponding unique index .
2) When a foreign key is defined on the table , It will automatically create a normal index .
3、 ... and 、EXPLAIN
3.1、 About EXPLAIN
effect : Used to see if the index is in use , And output the information of the index it uses .
3.2、EXPLAIN Examples of use
3.3、EXPLAIN Output information
id:select The serial number of the query , Contains a set of numbers , Represents execution in a query select The order of clauses or operation tables .
select_type: What is used SELECT Query type .