Preface://MySQL concurrency, asynchronous IO, process hijacking recently looking at high performance MySQL, record writing study notes: High performance MySQL learning notes (i) architecture and historical notes core content: MySQL server infrastructure, the main differences between various storage engines, and the importance of these differences ; First, MySQL logical architecture
The first layer of the architecture diagram: That is, the top-level service is not unique to MySQL, most web-based client/server tools or servers have similar architecture, such as link processing, authorization authentication, security and so on;//Each client connection will have a thread in the server process this connection query executes only on a separate thread, and the thread can only run on a CPU core or CPU in turn. //Client (application) when connected to MySQL, the MySQL service will make some column authentication for its connection (such as user name password, permission control, etc.)//This also answers many developers in the use of the database, blind switch connection, and even do not shut down the connection to the MySQL server to bring the loss;Second-tier architecture: MySQL Core Services and features are available at this level, including query interpretation, analysis, optimization, caching and all built-in functions (e.g., date, time, math and cryptographic functions), all across the storage engine's capabilities at this level: stored procedures, triggers, views, etc.//Query Interpretation: Understanding parsing, translating SQL statements into MySQL internal data structures (parse tree)//Analysis: Grammar analysis, whether there is a grammatical error;//Optimization: This is a very heavy topic, has not been deep into here, a general understanding of the meaning: the need to execute the SQL statements to optimize processing, such as where the order after the condition;//Built-in functions: note is built-in, can also be customized, but it is not recommended to do this on MySQL, remember that the database is only used to store data, database performance space left to CRUD///The MySQL optimizer is analyzed separately in the following sectionsThird-tier architecture: contains the storage engine, the storage engine is responsible for the storage and extraction of data in MySQL, and the Gnu/linux written by the various file systems, each storage engine has its advantages and disadvantages, the server through the API and the storage engine communication, these interfaces mask the differences between different storage engines, Making these differences transparent to the upper-level query process, the storage engine API contains more than 10 underlying functions for performing operations such as ' Start a transaction ' or ' fetch a row of records based on primary key ', but the storage engine does not parse SQL, nor does the different storage engines communicate with each other, but simply responds to the upper service The request of the device. Second, MySQL concurrency control Concurrency: Multiple statements at the same time, there will be concurrency problems, control concurrency and no data errors, and so on, depends on how the database system in the lock how to design. MySQL is only one database that can support row-level locks. MySQL Lock is divided into: table lock, row lock;//Update t_users Set login_count = 0 where 1 = 1; # Generate a table-level lock//Update t_users set age = where id = 9527; # generates a row-level lockEach MySQL storage engine can implement its own lock policy and lock granularity.//Lock policy: the so-called locking strategy is to find a balance between the cost of the lock and the security of the data, which is directly linked to performance;//Lock granularity: a way to increase the concurrency of shared resources so that locked objects are more selective. ///The smaller the amount of data locked, the higher the number of concurrent system support.
Three, deadlock deadlock refers to two or more transactions (note that a single UPDATE statement is essentially a transaction) on the same resource occupied each other, and request to lock up the resources occupied by the other side, resulting in the phenomenon of vicious cycle of death.//But annotations, understood as two people waiting for each other;MySQL database in the deadlock phenomenon, implemented a variety of deadlock detection and deadlock timeout mechanism, such as the INNERDB storage engine, it can detect the deadlock of the cyclic dependency, and immediately return an error. Four, storage engine storage engine Key Learning two (Innerdb, MyISAM):
//Other storage engine used very little, MySQL official also provides some APIs, there are many enthusiasts on the basis of their own implementation of many storage engines,like deep digging, you can go to see;Innerdb:Transactional storage engine, as the default storage engine for MySQL, should be said to be available in MySQL 4.0 + later versions. He is designed to handle a large number of short-term transactions, most of which are normally submitted and rarely rolled back.InnoDB's performance and automatic crash recovery features make him popular in non-transactional storage requirements,unless there are very specific reasons to use other storage engines, the InnoDB engine should be considered limited. InnoDB data is stored in a tablespace, and the table space is a black box managed by InnoDB, consisting of a series of data files. InnoDB can store the data and indexes of each table in a separate file. InnoDB can also use bare devices as a storage medium for table space, but now the filesystem is no longer a necessary choice for the bare device. The InnoDB uses MVCC to support high concurrency and achieves four standard isolation levels. The default level is repeatable read, and the Gap lock policy prevents Phantom reads from appearing, and the gap lock is innodb not only the rows involved in the query, but also locks the gaps in the index to prevent the insertion of phantom rows. InnoDB has done a lot of internal spending, including the predictable read-ahead read from disk, the ability to automatically create a hash index in memory to speed up the adaptive hash index of read operations, and the ability to accelerate insert caching for insertions. As a transactional storage engine, INNODB supports true hot backup through a number of mechanisms and tools, Oracle provides MySQL Enterprise backup, and Percona provides open source xtrabackup that can do just that.other storage engines for MySQL do not support hot backup, to get a consistent view you need to stop writing to all tables, while in a read-write mixed scenario, stopping the write may mean stopping the read. Technical Depth:http://blog.csdn.net/cd520yy/article/details/8541422MyISAM:MyISAM lock level, unlike INNERDB support to row-level locks, it only supports table-level locks, and MyISAM does not support transactions, do not know the classmate, do not after you perform a back operation after the face of the asked, obviously rolled back why the data was submitted.read a very early on the MySQL storage engine how to make a choice of article, the author of Innerdb and MyISAM respectively do the performance of reading and writing comparison,Undoubtedly, MyISAM read and write operation is higher than Innerdb, but MyISAM also has a lot of drawbacks, so in the choice, should be based on business needs to make decisions,Otherwise there is a problem, it will be a pit mate. As with some log tables, you can select MyISAM directly. MyISAM does not support hot backups, but checks and repairs can be performed manually or automatically, but the fix and transaction recovery and crash recovery are different concepts here. Repairing a table can result in some data loss, and the repair operation is very slow. V. How to choose the right storage engine summarize a sentence: Unless you need to use some of the features that INNERDB does not have, and there is no other way to replace it, you should prioritize the INNERDB storage engine;//Not very special cases, do not mix multiple storage engines; don't easily believe "MyI SAM faster than Innerdb, this conclusion is often not absolute. In many of our known scenarios, the speed of Innerdb can be MyISAM, especially with clustered indexes, or data that needs to be accessed into memory applications.
MySQL Basics (Basic architecture, storage engine differences)
