04--lays the key points for development of MySQL lake status

Provide different MySQL scenarios for different industries

Wu Bingxi Teacher said, the business of the different industry has various characteristics, choose a suitable for their own industry MySQL application scenario is critical.

• Internet industry

Speed has always been the first key to the development of the Internet, the Internet industry uses a brief summary of the characteristics of the database is a word: quick! Fast response and high performance.

• Financial industry

The financial industry is more focused on data stability and security, and the requirements for consistency are among the highest in many industries. Choosing a cluster, the security options in the database configuration, and the control of things are very different from other industries.

• Traditional Industries

Compared with the speed and security, traditional enterprises pay more attention to business processing, the overall status of business completion is higher than performance.

Choose a different storage engine for different businesses

Similar to the application scenario, the storage engine should be selected according to the industry's characteristics. Wu Bingxi the usual MySQL storage engine for analysis.

• MyISAM

MyISAM is the most traditional MySQL storage engine, but since the MySQL5.5 version, the official has not started to do special maintenance, the engine is now basically obsolete. Wu Bingxi is strongly advised to use the storage engine as soon as possible to qiantouming, choose a new storage engine to replace it.

• Innodb

InnoDB is now a relatively new engine, but also the Wu Bingxi Teacher's key recommendation of a storage engine. InnoDB has two different versions, one is Innobase and the other is InnoDB Plugin. Now using the MYSQL5.5 and 5.6 two versions are based on InnoDB plugin, performance compared to the previous increase of more than twice times, is the perfect choice for regular business.

• Other

There are also TOKUDB engines that provide high-speed write capabilities, Inforbright engines for complex statistical analysis in the database, and many other storage engines that we can choose from.

MySQL optimized "three minus one plus"

User centralized access is the Internet in the field of "high incidence", in the face of high concurrency environment, Wu Bingxi teacher proposed: three minus one plus policy. Reduce data access, reduce network traffic, reduce CPU overhead, and increase resources.

"Military" for efficient index design

Design efficient Indexing

• Query, update, delete the Where condition must have an index, the number of rows can not exceed 1w
• The index cannot be used if the query condition does not contain the leftmost column of the index
• For range queries, only the leftmost column of the index can be leveraged
• For order by a statements, indexing on a can avoid sorting
• For GROUP by a statements, indexing on a can avoid sorting
• For multi-column sorting, all columns must be sorted in the same direction to take advantage of the index.

04--lays the key points for development of MySQL lake status