Shulou(Shulou.com)06/01 Report--

This article mainly introduces the principle of MySQL and optimization of the case study, introduced in the article is very detailed, has a certain reference value, interested friends must read!

MySQL is currently the most widely used database software (DBMS) for Internet companies, and there is no one. From startups to BAT,GOOGLE,FACEBOOK, they all use MySQL as data storage in their own business. Aliyun also provides a cloud version of MySQL, the cloud database RDS MySQL version. This series of articles hope to help you better understand MySQL, give better play to the performance of the database, and make our data storage more efficient.

History of MySQL

MySQL is an open source free software, we can get its source code directly on the Internet. MySQL has a history of more than 20 years so far, and the general milestones are as follows:

● was released in 1996 by MySQL 1.0, which is only aimed at a small group of people. In October 1996, MySQL 3.11.1 was released (MySQL does not have version 2.x), and at first only the binary version under Solaris was available. A month later, the Linux version appeared. Over the next two years, MySQL was ported to each platform in turn.

● from 1999 to 2000, MySQL AB was founded in Sweden and developed the Berkeley DB engine. Because BDB supports transaction processing, MySQL has supported transaction processing since then.

● 2001 V3.23:MyISAM engine, and prototype Innodb engine

● 2003 v4.0: new syntax features, Innodb becomes a standard component, adding query_cache

● 2006 v5.0: views, triggers, stored procedures and other functions added

● 2008 V5.1: partition, line replication

● 2010 V5.5:Innodb becomes the default engine, semi-synchronous replication

● V5.6 Innodb improvements, replication capabilities, etc.

● V5.7 adds new storage engines such as mariaDB

Architecture of MySQL

Instead of being the same as the database of the same period, MySQL adopted its own unique architecture. This structure can be understood by a famous poem by du Mu, a poet of the Tang Dynasty:

The Summer Palace Viewed from afar, the hill's paved with brocades in piles; The palace doors on hilltops opened one by one. A steed which raised red dust won the fair mistress'smiles, How many steeds which brought her fruit died on the run.

There are three roles:

Concubine ●: responsible for making demands and eating lychees

Minister ●: responsible for arranging litchi picking, which route to take, handing out official documents, etc.

● courier: responsible for transporting litchi

This corresponds to three roles in the MySQL architecture: client, processing engine, and execution engine.

This is represented by the architecture diagram.

● client

Equivalent to the role of the concubine: the user operates the client to query, modify, add and delete data

● processing engine

The processing engine, which is equivalent to the role of a minister, is responsible for parsing SQL statements and generating execution plans. In addition, we are responsible for the following responsibilities, which we can pay attention to when optimizing:

Responsible for managing connections and threads: using the multithreading model, setting thread_cache_size to take advantage of existing threads

Responsible for managing query cache: using off-the-shelf results, caching result sets directly, and using sql_no_cache hint shielding when testing statement performance

● storage engine

Storage engine is equivalent to the role of couriers, responsible for the actual data storage and data reading, modification and other operations. Different storage engines will show different characteristics in the actual implementation. It will be introduced in detail in a later article.

The above is all the contents of this article "case study of MySQL principles and Optimization". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.