MySQL Learning Note (eight) index

Original: MySQL learning note (eight) index

PS: To fill in yesterday's study content ... Send what you learned yesterday .... May 3 ... Continue to learn the database ...

Learning content:

Index....

Advantages of the index:

1. By creating a unique index, you can guarantee the uniqueness of each row of data in the database ...

2. The speed of the search is significantly accelerated ...

3. When querying using grouping and sorting, you can shorten the time ...

Disadvantages of the index:

1. Maintaining indexes consumes the resources of the database ...

2. The index needs to occupy disk space ...

3. When the table is added or deleted, the time will increase due to the existence of the index ...

Classification of indexes ...

Normal index and unique index ...

The normal index and the unique index are roughly the same, all belong to the b-tree structure .... The only difference is that the data column of the unique index can be empty, and the data for each row of the indexed column is unique ...

Single-column index and composite index ...

A single-column index is the only parameter that defines an index, which is the number of parameters that can be included when defining an index ... And the query of the compound index must use the first field, otherwise it will result in the case that the index is not gone ... The reason is that the composite index follows the most left-most-prefixed collection ...

Full-Text Indexing

A full-text index is a full-text lookup of supported values on columns that define an index, allowing duplicate and null values to be inserted on this column, and full-text indexing can be defined on characters or text ... Storage Engine for MyISAM ...

Spatial index

The spatial index must be created in the MyISAM table ... is the index of the field established for the data type of the space: There are four kinds of spatial data types Geometry,point,linestring,polygon ... Spatial indexes are extended using the keyword spatial ... (Honestly, I don't know exactly where the spatial index can be used) ...

Create an indexed statement ....

CREATE TABLE table_name[col_name data type][unique|fulltext|spatial  [index|key] [index_name](col_name[ length ])[asc|desc]

Normal index:

Create TableBook (BookIDint  not NULL, BookNamevarchar(255) not NULL, authorvarchar(255) not NULL, Infovarchar(255) not NULL, Commentvarchar(255), Year_publication Year  not NULL,    Index(Year_publication)); showCreate TableBook//display the appropriate information for the form we created ...Insert  intoBookValues(1,'Java','James Gosling','Good','Good',1982); explainSelect *  fromBookwhereYear_publication=1982;

The display data will have many properties: A Brief introduction ...

ID: the first few. Select_type: Represents the complexity of the query statement select. Table: Table name ... type: access type. Possible_key: Display Index ... key: number of indexes ...

Key_len: Displays the number of bytes using the index ... ref: Shows which column is used for index lookup ... rows: The number of rows to be read by the index ... extra: represents an extra information that is not suitable for display in columns, but is important ...

Unique index:

The index value of a unique index column must be unique, and if it is a composite index, the value of the combination of columns of the composite index must also be unique ...

Create TableT1 (IDint  not NULL, namevarchar(255) not NULL, Ageint  not NULL, Infovarchar(255),   Unique IndexMultidx (Id,name)//Composite unique index ...);Insert  intoT1Values(1,'We', -,'Misaya'),(2,'OMG', +,'gogoing');

Use explain query statement ...

Select *  from where id=1 and name="we"; //  Select*fromwhere name="we"// cause the index not to go: Match leftmost prefix principle not followed ...

Full-text Index ...

Fulltext indexes can be used for full-text indexing ... Only the MyISAM storage engine supports full-text indexing: Filtered indexes are not supported for full-text indexing ...

CREATE TABLEt4 (IDINT  not NULL, NAMECHAR( -) not NULL, AgeINT  not NULL, InfoVARCHAR(255), FulltextINDEXFulltxtidx (info));Insert  intoT4Values(1,'We', -,'Misaya');//Inserting DataSelect *  fromT4whereMatch (info) against ('Misaya');//Use full-text indexing to find data:

Spatial index: Spatial index, for geospatial type ... I do not know very well ... So do not introduce here, avoid fraught ...

To add an index to an existing table ....

Two different ways:

Create TableT1 (IDint  not NULL, first_namevarchar(255) not NULL, Last_Namevarchar(255) not NULL, DOB date not NULL);//Add Index .... The first way ....Alter Tabletable_nameAdd[Unique][Filltext][Spatial]【Index】【Key】[index_name](attribute value) "ASC】【desc" .....Alter TableT1Add IndexFirstidx (first_name);//make First_Name an index ... showIndex   fromT1;//View our established index ... The second way ....Create [Unique][Filltext][saptial]【Index】 onTABLE_NAME (attribute value) "ASC】【desc】//This does not support key,. Insert key will error ...
Create IndexLastidx onT1 (last_name);//Make Last_Name an index ...

To delete an index:

two different ways1.Alter Tabletable_nameDrop Indexindex_name;Alter TableT1Drop IndexFirstidx;//Delete Index ...2.Drop IndexIndex_name ontable_name;Drop IndexLastidx onT1;//Delete Index Lastidx ...

Note: The unique index of the anto_increment constraint field is not allowed to be deleted ....

Whether you add an index or delete an index, we can use Shou CREATE TABLE table_name to see how the data table is built ....

MySQL Learning Note (eight) index