This is the first day of my participation in the August More text Challenge. For details, see: August More Text Challenge

describe

MySQL is different mainly in the storage engine architecture. The plug-in storage engine architecture separates query processing from other system tasks and data storage and extraction

Mysql system architecture

The connection layer

The connection layer is the top layer of the mysql architecture. It mainly contains some clients and connection services

  • Complete some similar connection processing, authorization, and related security schemes
  • Introduced the concept of thread pools to provide threads for clients that authenticate secure connections
  • Secure connections based on SSL are possible, and the server also verifies all of its operation permissions for clients that are securely accessed

The service layer

The service layer is the second layer of the mysql architecture and does the following

  • Performs most of the core service functions, including query parsing, analysis, optimization, caching, and all the built-in functions
  • All cross-storage engine functionality is also implemented at this layer, including triggers, stored procedures, views, etc.

Engine layer

The engine layer is the third layer of mysql

  • The storage engine is really responsible for storing and extracting data from mysql
  • The server communicates with the storage engine through an API
  • Different storage engines have different functions. You can select one as required

Storage layer

This layer is the lowest level of the mysql architecture, and its main role is to store the architectural data on the file system running with the device, and complete the interaction with the storage engine

The storage engine

describe

The storage engine is a component of mysql that is used to process different types of SQL operations. Multiple tables in a database can use different storage engines to meet various performance and practical requirements

Operations related to storage engines

Viewing a storage Engine

  • View the supported storage engines
show engines;
Copy the code
  • View the default storage engine
show variables like %storage_engine%;
Copy the code
  • View the storage engine in a table
show table status like 'tablename';
Copy the code

Setting the Storage Engine

  • Specify storage engine when creating table (default is INNODB, no need to set)
create table tabel_name engine = INNODB;
Copy the code
  • Modifying a Storage Engine
alter table table_name engine = INNODB;
Copy the code
  • You can also modify the default storage engine in the configuration file my.cnf
SET default_storage_engine=NDBCLUSTER;
Copy the code

Storage engine MyISAM vs. InnoDB

  1. InnoDB supports transactions; MyISAM does not support transactions;
  2. InnoDB supports foreign keys; MyISAM does not support foreign keys; [Bug Mc-10875] – Converting an InnoDB table with foreign keys to a MyISAM table fails
  3. InnoDB is a clustered index, MyISAM is a non-clustered index
  4. InnoDB does not store the specific number of rows in the table. Select count(*) from table requires a full table scan. MyISAM uses a variable to hold the number of rows in the entire table. Just perform the count operation to read the value of this variable. It is very fast
  5. InnoDB minimum granularity lock is row lock, suitable for high concurrency; MyISAM minimum-grained locks are table locks, which are not suitable for high concurrency. An update statement will lock the entire table, which will block all other queries and updates, thus limiting concurrent access
  6. InnoDB caches real data as well as indexes. It has high memory requirements, and memory size has a critical impact on performance. MyISAM only caches indexes, not real data;
  7. InnoDB table occupies large space, MyISAM table occupies small space
  8. InnoDB is focused on transactions, MyISAM is focused on performance