MySQL Index Summary

1. Type of index (six types)

Normal index, unique index, full-text index, single-column index, multicolumn Index, spatial index

2. Advantages and disadvantages and precautions

Pros: With indexes, you can increase query speed for tables with a large number of records.

Disadvantage: The index is space-intensive and the index affects the update insert delete speed.

The following are some considerations for using the MySQL index.

A. The index is to be created on the fields used by where and join.

B, the following action symbol can be applied index (: <,<=,=,>,>=,between,in,like does not start with%_)

It is important to note that the index is not used (<>, not in, like%_ begins)

<> can be replaced with a>1 or a<3, not in can replace with not exists

C, it is best to index when using Max () min ().

D, a single index to be created where it is really needed. Multiple-column indexes have the best left-prefix attribute, so the fields that are as far as possible to the left are most commonly used.

E, the index does not include a null value, or a null index is invalidated.

F, using a short index, a field too many words, you can set up a partial index, only take the first 10 words to save space.

G, do not operate on the column. For example: where MD5 ("password") = "Myz".

h, explain select * from Myz to test the efficiency of the statement ...

3. Storage engine and Index storage type

There are two types of index storage type: B-tree index and hash index

The MyISAM and InnoDB storage engines support the B-tree index; memory supports both hash and B-tree indexes, which by default are the former.

4. Design principles for indexing

A. Selecting a Uniqueness Index

B. Indexing columns that are frequently required for sorting, grouping, and Union operations

C. Indexing a field that is often required as a query condition

D. Limiting the number of indexes

E. Try to use a field with a small amount of data as an index

F. Use prefixes as indexes as possible

G. Delete infrequently used or infrequently used indexes

5. Create and modify an indexed SQL statement

　　

CREATE TABLETable Name (property name data type[integrity Constraint conditions], property name data type[integrity Constraint conditions],                 ......                 [unique| Fulltext| SPATIAL] INDEX|KEY[aliases](Property name[(length)] [asc| DESC])                 );

Instance

Normal index creation CREATE TABLE INT VARCHAR (a), sex BOOLEAN,index(ID)); Normal index modify CREATEindex on INDEX1 (ID); ALTERTABLEADDINDEX index1_id (ID);

  uniqueness index creation  create  table  index2 (id int  unique , Name varchar  (20 ), unique  index  index2_id (id asc  ); Uniqueness Index Modification  create  unique  index2_id on   Index2 (ID);  alter  table  index2 unique  index  index2_id (ID); 

  full-text index creation  create  table  index3 (id int , info varchar  ( Index3_info (info)) Engine=  myisam The full-text index modifies  create  fulltext index  Index3_info on   index3 (info);  alter  table  index3 add  fulltext Span style= "color: #0000ff;" >index  Index3_info (info); 

single-column index creation CREATE TABLE INT VARCHAR (+),index index4_st (subject)); single-column index modify the CREATEIndex  on index4 (name); ALTER TABLE ADD INDEX index4_name (name);

  multi-column index creation  create  table  index5 (id int , name varchar  (char  (index  Span style= "color: #000000;" > Index5_ns (name,sex)); Multi-column index modifies  create   INDEX  index5_ns on   index5 (name,sex);  alter  table  index5 "add  index  Index_ns (name,sex); 

  create  table  index6 (id int , space  GEOMETRY not  null , SPATIAL index  index_sp (space )) Engine=  MYISAM; spatial Index modification  create  SPATIAL index  index6_sp space   alter  table  index6 index  INDEX6_SP (space ); 

6. Deleting an index

DROP index index name on table name;

　　

MySQL Index Summary