MySQL Document summary cont. 2

MySQL optimization continued:

1:show index from TABLE_NAME View indexes usage where cardinality cardinality, that is, the value group index sensitivity, the greater the number of rows that need to be scanned when cardinality, the larger is the smaller.

2:mysql evaluation of the index scan data is very large when the use may be discarded, when using limit, the situation can be changed, MySQL will use the index

3: Row format for MySQL. InnoDB the latest version of the Compact, the old version is redundant, using the new format or reduce disk space 20%, the corresponding increase in CPU time, the char type store utf-8 characters, the old version is 3*n bytes, the new version is N ~ 3*n

4: In a field less than 8KB is stored as far as possible using varchar instead of blobs, in the group by and order by can produce temporary tables, in the absence of BLOB fields, temporary tables can use the memory engine.

5: When there are many field tables that are not commonly used, it is generally recommended that these fields be split into separate tables, which are processed by join, especially when the blob, text type.

6:optimizer_switch该参数可以查看mysql采用的一些优化策略，如MRR，ICP等是否打开，都可以手动设置

7:index hints can use using index, Force Index, ignore index to influence MySQL so choose

8:innodb_buffer_pool can store the SELECT, INSERT, update and other operational data (including indexes and database data) to improve performance, the LRU algorithm to replace the corresponding block, insert the data block into the middle of the list by default about 5/ Around 8 can be manually adjusted, which prevents frequent data refresh.

9:myisam Key_cache Store index data, database data is cached by the local operating system file system, MyISAM can also use Midpoint insertion strategy, KEY_ If the cache is not available (such as rebuilding), MySQL will enable the local file system cache (which will certainly degrade in performance).

MySQL Document summary cont. 2