centos7-mysql-Index Optimization

index optimization to optimize query speed
-------------------------------------------------------
count, statistics a table Total row count
The MyISAM storage engine has its own counter, and it is fast to extract counter values directly using Count
InnoDB requires full table sweep face when using count, each row is inefficient
--------------------------------------------------------
Binary multimedia data, do not exist in the database
Large text data, and do not exist in the database
Different SQL statements can also affect execution efficiency
--------------------------------------------------------
Index
Explain simulation statement query status, providing data "commands used frequently"
650) this.width=650; "height=" 346 "src=" http://b246.photo.store.qq.com/psb?/81c1194a-ff39-4e50-b1b1-40057b06260f/ ysln9xenhrkj41wmpagyu5ataipjmowzodzan22h6oi!/b/dpyaaaaaaaaa&bo=pwjaaqaaaaafefu! "width=" 575 "style=" vertical-align:top; "alt=" dpyaaaaaaaaa&bo=pwjaaqaaaaafefu! "/>
My condition is Stuname=gao the hint is null because the index is not created in Stuname
Indexing is a data structure that helps MySQL to get data in hilarious
B-tree B-Tree structure
650) this.width=650; "height=" 409 "src=" http://b288.photo.store.qq.com/psb?/81c1194a-ff39-4e50-b1b1-40057b06260f/ y3kaddr24mohhy7vfhpbmuvplkx3humrkashheniajk!/b/dcabaaaaaaaa&bo=jwozaqaaaaafei8! "width=" 807 "style=" vertical-align:top; "alt=" dcabaaaaaaaa&bo=jwozaqaaaaafei8! "/>
Index reduces IO usage,
Create a silver lock need to look for high index value, relatively low no need to create, such as gender there is no index value to create, too many duplicate values

Index type
1, normal index
The most basic index, without any restrictions
2, unique index
A column value must be unique, but can be null
3, combined index
A composite index is a combination of multiple column values, but with a leftmost prefix, and you want to use a composite index, you must require that the leftmost value in the combined index is not used
4, full-text index
field types include char, varchar, text,
But for large data tables, generating a full-text index is a very time-consuming way of hard disk space
The index command uses
CREATE index IndexName on table name "which column" normal index
Create unique index indexname on table name "column value" uniquely indexed unique
CREATE index IndexName on table name "which column, which column, which column, which column" combination index
Create FULLTEXT index indexname on table name "which column" full-text index
--------------------------------------------------------------------------------------------------------
Check Index
Show index from table name;
Show keys from table name;
See what table names are in a table
--------------------------------------------------------------------------------------------------------------
Sometimes MySQL performance is reduced is the IO bottleneck, this has no way, sometimes can be resolved through the index, sometimes only to update the hardware configuration

This article is from the "11627223" blog, please be sure to keep this source http://11637223.blog.51cto.com/11627223/1911973

centos7-mysql-Index Optimization