In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >
Share
Shulou(Shulou.com)05/31 Report--
This article shows you how to understand the logical architecture and InnoDB storage engine. The content is concise and easy to understand. It will definitely make your eyes shine. I hope you can gain something from the detailed introduction of this article.
I. MySQL logical architecture
1. Logical architecture diagram
Based on the logical architecture diagram below, you can get a general idea of how MySQL's architectural components work together.
A classic C/S architecture style, that is, client/server mode.
2. Layered description
client connection
It usually performs connection pool management, connection user authorization authentication, security management and other operations.
Connection configuration information can be viewed with the following command: SHOW VARIABLES LIKE '%connect %'; you can see the configuration of maximum connections and memory consumed by each connection.
core functions
The second layer encapsulates a series of core MySQL operations, such as query parsing, optimization, caching, built-in functions, triggers, views, etc., and cross-storage engine functions are implemented at this layer.
storage engine
MySQL's lowest level package is also the core function. Different storage engines have different characteristics and functions. The common point is to process data storage and extraction.
II. Concept brief
1. Storage engine
MySQL database storage engine is the architecture component of database bottom layer. Database management system uses data engine to create, query, update and delete data. Different storage engines provide different storage mechanisms, indexing techniques, locking levels and other functions. Different storage engines also have different characteristics and functions to meet the business requirements under different scenarios.
2. Support relationship
You can check the current version of MySQL and support for storage engines with the following two commands:
SELECT VERSION() ; SHOW ENGINES ;
You can see that the local environment is MySQL 5.7, which supports the following storage engines:
The default storage engine in this version is: InnoDB, which is the most versatile and powerful, supports transactions, distributed transactions, and transaction savepoints.
Common storage engines
1. InnoDB engine
(1)Basic description
InnoDB engine is MySQL default transactional engine, very widely used, very good at handling short-term transactions, with automatic crash recovery features, in daily development, generally required to use this engine.
(2), architecture diagram
InnoDB architecture diagram
This image is taken from MySQL official website documentation.
Overall divided into three layers: memory structure, Cache, disk structure.
memory structures
The memory structure consists of four components
Buffer Pool: An area in main memory where cache is performed when InnoDB accesses table and index data, greatly reducing disk IO operations and improving efficiency.
Change Buffer: Write buffer: Avoid IO operations every time you add, delete and change, improve performance.
Adaptive Hash Index: Hash index is constructed by using prefix of index keyword to improve query speed.
Log Buffer: Holds data to be written to log files on disk, and the contents of the buffer are periodically flushed to disk.
disk structures
Tables: The physical structure of the data table.
Indexes: Physical structure of the index.
Tablespaces: Tablespaces, data storage areas.
Data Dictionary: A table that stores metadata information, such as table descriptions, structures, indexes, etc.
Doublewrite Buffer: A storage area in the system table space where InnoDB writes data pages to disk when it refreshes pages in BufferPool.
Redo Log: A log of DML operations used to recover data after a crash.
Undo Logs: Snapshots of data before changes, which can be used to roll back data.
(3), characteristic description
support transactions
When executing a set of SQL statements within a transaction, either all succeed or all fail.
supports distributed transactions
Distributed transactions refer to the need to guarantee the nature of transactions even if different operations are located on different service applications. Common scenario: Orders and inventory are in different services but remain consistent.
Support row level locking
The locking mechanism for locking a row of data when locked is row-level locking. MySQL 5.7 is supported only by the InnoDB engine. The granularity of locking is small, the concurrency supported by nature is high, and the locking mechanism becomes complex accordingly.
Support MVCC
Multiversion concurrency control is achieved by keeping snapshots of data at a point in time. This means that no matter how long a transaction runs, you can see a consistent view of the data within the same transaction. Depending on when the transaction started, it also means that different transactions may see different data in the same table at the same time.
Cluster index support
Is a reorganization of the actual data on disk to sort by the values of a specified column or columns. Because the index page pointer of a clustered index points to the data page, finding data using a clustered index is almost always faster than using a non-clustered index.
2. MyISAM engine
(1)Basic description
The default storage engine for MySQL 5.1 and earlier does not support transaction and row-level locking, and does not automatically recover from natural crashes.
(2), characteristic description
locking table mechanism
Lock the entire table, not the rows, read the data with shared locks, write the data with exclusive locks.
full-text index
Full-text indexing, an index created based on word segmentation, can support complex search queries.
3 Other engines
In MySQL's architecture, the most commonly used are InnoDB and MyISAM engines, and other diverse storage engines can be familiarized with according to business needs.
Narrator: life is short, programming language is more five-horse six-way, this is annoying, so when learning to pick the key, what is the key, the most used is the key content.
IV. Storage Engine Selection
In the company's development specifications, it is generally mandatory to use the InnoDB engine, except for quirky business InnoDB cannot support.
That's how to understand logical architecture and the InnoDB storage engine. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserves, please pay attention to 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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.