MySQL High performance index (Ⅰ)

In development, we know that the bottleneck of most applications is the execution time of SQL statements, where the security of SQL statements is not discussed, only high-performance SQL statements are discussed, and the legendary----index is closely linked to high-performance SQL statements.

Index-a data structure that works on the storage engine to quickly find records.

MySQL uses the index by first locating the index value and then finding the data row based on the value of the index.

Index of the B-tree index

B-tree indexes usually mean that all values are stored sequentially, with the same distance from each leaf node to the root. Icon:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/80/BB/wKiom1dEBtyS_ibUAABL-MTMzcE070.png "title=" Qq20160524154624.png "alt=" Wkiom1debtys_ibuaabl-mtmzce070.png "/>

The B-tree index provides fast access to data because the storage engine does not need to perform a full table scan to obtain data, theoretically b-tree the search time complexity of no more than O (㏒2 (M)).

This article tests the use case:

Table: Tbprofitshop

Number of fields: 15

Record Count: 20w

Some limitations on the B-tree index:

⑴. The index cannot be used if it is not indexed by the leftmost prefix of the index. Primary index: ID, normal index: Fdphone

such as: 650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/80/CD/wKioL1dECyLj4tzUAAA7aSj7o2c565.png "title=" Qq20160524160042.png "alt=" Wkiol1decylj4tzuaaa7asj7o2c565.png "/>

This will use the index:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/80/D6/wKiom1dEComBaCljAAA5e-QHs0I016.png "title=" Qq20160524160210.png "alt=" Wkiom1decombacljaaa5e-qhs0i016.png "/>

⑵. Columns in the index cannot be skipped. Primary index: ID, Federated Index: (fdphone,fdcreate), such as:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/80/DB/wKioL1dEDKqAmAzTAAA6qTOBQPE768.png "title=" Qq20160524160708.png "alt=" Wkiol1dedkqamaztaaa6qtobqpe768.png "/>

If you skip fdphone here and use fdcreate directly, the query cannot use the index.

⑶. If there is a range query for a column in the query, none of its right columns will be able to use the Index optimization query. Primary index: ID, Federated index:(fdphone,fdcreate). such as:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/80/DD/wKiom1dEDZTDumGEAAA73FHbk4Y939.png "title=" Qq20160524161507.png "alt=" Wkiom1dedztdumgeaaa73fhbk4y939.png "/>

Note: In development, you often see a numeric type of string, and then the fire directly uses the number type. The data should match the field type, in this case Fdphone is the character type, we use the number type to see how to:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/80/DD/wKiom1dEDu_xuX3wAAA695JshE8004.png "title=" Qq20160524162047.png "alt=" Wkiom1dedu_xux3waaa695jshe8004.png "/>

Then look at the string type:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/80/DD/wKiom1dEDymBiHI8AAA4yTaEPhQ485.png "title=" Qq20160524162153.png "alt=" Wkiom1dedymbihi8aaa4ytaephq485.png "/>

All: In index construction, the order of index construction seems important, and it is often the key to efficient indexing.

This article is from the "Years" blog, please be sure to keep this source http://moron.blog.51cto.com/9245572/1782567

MySQL High performance index (Ⅰ)