Rapid mastery of database indexing technology [database design]

1. < /span>

   index classification:

     General index, primary key index, unique index, full-text index, composite ( combo index .

2. When to create an index:

(1) A field that is more frequent as a query condition ;

(2) The uniqueness of the field is too poor ( such as the Gender/Status field, etc. ), even if we frequently as a condition of the query, not fit to create the index;

(3) Data update very frequently fields (such as the statistics platform current online person Number field/Item sales quantity fields, etc.) is not suitable to create an index.

3. Create an index (it can also be created directly using a visualizer, which we demonstrate in code):

(1) General index:

Create index index name on table name (table field)

(2) primary key index:

When you create a table, you set the primary key field, such as ID, which is the primary key index.

(3) Unique index:

① If the field sets a unique constraint (unique), the field is a unique index;

② creating unique indexes directly: Create unique index name on table name (Field 1, Field 2 ...)

(4) Full-text index:

As the name implies, full-text indexing is primarily a search for files, text (such as product details, articles, news, and so on).

Note: with full-text indexing, the engine of the table must be set to MyISAM, otherwise invalid.

① creation: Create FULLTEXT index index name on table name (Field 1, Field 2 ...)

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/87/60/wKiom1feXFCyLEV7AAFpxHjcloI627.png "title=" 1111. PNG "alt=" Wkiom1fexfcylev7aafpxhjcloi627.png "/>

② using:

I) Selete field 1, Field 2 ... from table name where match (matches the corresponding full-text indexed field) against (fuzzy retrieval of the Flying Chinese string).

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/87/60/wKiom1feXIfRdQsUAAEirGZ74Rw583.png "title=" 2222. PNG "alt=" Wkiom1fexifrdqsuaaeirgz74rw583.png "/>

II) Where condition cannot be used: full-text indexed field like '% string% ', otherwise the full-text index is invalid.

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/87/5D/wKioL1feXSyDIBQNAAFz7HfNg90850.png "title=" 33333. PNG "alt=" Wkiol1fexsydibqnaafz7hfng90850.png "/>

III) MySQL provides fulltext only effective for English, processing Chinese to use Sphinx (Coresseek) technology to do Chinese processing, later will tell Sphinx (coresseek) installation and use;

③ Combined Index:

Create index index name on table name (Field 1, Field 2 ...)

4. The cost of the index:

(1). Creating an index is a cost that takes up a certain amount of disk space;650) this.width=650; "Src=" http://s2.51cto.com/wyfs02/M00 /87/5f/wkiol1fehunjl70oaac8tyq6qa8754.png "title=" 99999999.png "alt=" Wkiol1fehunjl70oaac8tyq6qa8754.png "/>

(2). Index data traction is very difficult;

(3). In order to maintain the index file, it will cause the increment, delete and change operation to be slow .

Little Common sense: 1. Show index from the table name, you can view the corresponding table detailed use of the index;

2. Explain: in the case of SQL not executing, you can see the detailed execution of SQL, so that we analyze SQL and optimize sql.

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/87/5E/wKioL1feZGjT1DESAADhdAr1fqc273.png "title=" 00000. PNG "alt=" Wkiol1fezgjt1desaadhdar1fqc273.png "/>

This article is from the "Yao Technology blog" blog, make sure to keep this source http://yaowusheng.blog.51cto.com/11020801/1853754

Rapid mastery of database indexing technology [database design]