Common optimization knowledge of MySQL

Index type: Primary key index, unique index, federated Index, normal index, full-text index

1. After indexing on a column, if you are using a like query, the percent semicolon is not indexed on the left. such as select a from t where BB like '%hehe '; in this case, BB has no index to use.

2. If the or keyword is used, there is a field on either side of the left or right without an index. Like select a from t where B=1 or c=2; In this case, if B or C has an index, it is not indexed.

3. When the group by is sorted by default, the sorting is very performance, and if not necessary, an order by null can be added. This is better. By the way, the order by frequently ordered field plus index.

(You can view it by Explai SQL without using an index on it.) ）

4. A field of data type, can meet business requirements, the smaller the better. For example, the use of enums is much better than varchar.

5. In financial systems or money, decimals are saved using decimal, which is best used.

6. If you know that you want to find one in business, it's best to add a limit of 1 to SQL.

MyISAM the storage engine, the index is. Myi file: Data exists. MyD file ... frm is a table structure file.

the difference between MyISAM and InnoDB can be found in detail: http://www.oschina.net/question/17_4248

MyISAM Deleting the Delete does not really remove the database file, it needs to execute optimize table to clear the fragment. Table-level locks.

The data that exists in the INNODB will be in a file ibdata1 outside. In the MyD file. It's not like MyISAM. Support transactions. Foreign keys are supported. General row-level locks.

Common optimization knowledge of MySQL