MySQL Storage engine

MySQL Storage engine

Understand the architecture of MySQL before you become familiar with the storage engine

1 connectors refers to the interaction of SQL in different languages

2 Management serveices & Utilities: System Management and control tools

3 Connection Pool: Connection pooling.

Manage buffered user connections, threading, and other needs for caching

4 SQL Interface:sql interface.

Accepts the user's SQL command and returns the result that the user needs to query. For example, select from is called SQL Interface

5 Parser: Parser.

When the SQL command is passed to the parser, it is validated and parsed by the parser. The parser is implemented by Lex and YACC and is a very long script.

主要功能：a . 将SQL语句分解成数据结构，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 b.  如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的

6 Optimizer: Query optimizer.

SQL statements use the query optimizer to optimize queries before they are queried. He uses the Select-drop-join strategy for querying.

As an example, you can understand: Select Uid,name from user where gender = 1;

This select query is selected based on the where statement instead of querying all of the tables before gender filtering

This select query first attributes the property based on the UID and name, instead of removing the attributes from the filter

Join these two query criteria to generate the final query result.

7 Cache and Buffer: query caching.

If the query cache has hit query results, the query can go directly to the query cache to fetch the data.

This caching mechanism is made up of a series of small caches. such as table cache, record cache, key cache, permission cache, etc.

8 Engine: Storage engine.

The storage engine is the specific subsystem in MySQL that deals with files. It is also one of MySQL's most distinctive places.

The MySQL storage engine is plug-in. It customizes a file access mechanism based on an abstract interface of the file access layer provided by MySQL AB (the access mechanism is called the storage engine)

Now there are many kinds of storage engines, the advantages of each storage engine are different, the most common myisam,innodb,bdb

By default MySQL is using the MyISAM engine, it queries fast, has better index optimization and data compression technology. However, it does not support transactions.

InnoDB supports transactions and provides row-level locking, and the application is quite extensive.
MySQL also supports its own custom storage engine, and even the different tables in a library use different storage engines, which are allowed.

Advantages of MyISAM

• Fast, low disk space consumption;
• The disk usage of a library or table can be either checked by the operating system or by the size of the corresponding file (clip), or by the SQL Statement Show table status

Advantages of InnoDB

• Support Transactions
• Row-level locking, which locks the row when updating. If the full table is scanned. It is also a full table lock. So it's important to build an index.
• With efficient caching features. Indexes and data can be cached
• Supports partitioning, table space
• FOREIGN KEY constraints are supported.
• Full-text indexing is supported after version 5.5
• The hardware resource requirements are higher than the MyISAM engine.

To view stored commands

1: Do not enter the database to view mysql> Show table status from Cilent where name= ' user '; +------+--------+---------+------------+------+-- --------------+-------------+-----------------+--------------+-----------+----------------+-------------------- -+---------------------+------------+-----------------+----------+----------------+---------+| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | create_options | Comment |+------+--------+---------+------------+------+----------------+-------------+-----------------+------- -------+-----------+----------------+---------------------+---------------------+------------+----------------- +----------+----------------+---------+| user |      InnoDB | 10 |    Dynamic |           2 |       8192 |               16384 |            0 |         0 |           0 | NULL | 2018-07-03 17:09:14 | 2018-07-03 17:10:28 | NULL |     Utf8_general_ci |                NULL |         | |+------+--------+---------+------------+------+----------------+-------------+-----------------+-------------- +-----------+----------------+---------------------+---------------------+------------+-----------------+------ ----+----------------+---------+1 row in Set (0.03 sec)

The table above ' name ' corresponds to the user table name, the engine corresponds to the InnoDB storage engine, version corresponding to the versions ' up and down is one by one corresponding to my side is 5.7 version into the database view

mysql> show create table user;+-------+-----------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                |+-------+-----------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE "user" (  "name" varchar(10) DEFAULT NULL,  "sroce" int(5) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

Several ways to modify the storage engine

1:alter table name engine= storage engine (modifies tables that already exist)

mysql> alter table user engine=MyISAM;Query OK, 2 rows affected (0.03 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> show create table user;+-------+-----------------------------------------------------------------------------------------------------------------------------+| Table | Create Table                                                                                                                |+-------+-----------------------------------------------------------------------------------------------------------------------------+| user  | CREATE TABLE "user" (  "name" varchar(10) DEFAULT NULL,  "sroce" int(5) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

2: Modify the join in the MY.CNF configuration file (the default storage engine is the one modified in the configuration file when the new table is created)

• default-storage-engine= Storage Engine

[[email protected] ~] vim /etc/my.cnf[mysqld]default-storage-engine=MyISAM[[email protected] ~] systemctl restart mysqld.service  #改完配置文件重启服务#下面直接创建表默认的存储引擎就是配置文件里的那个mysql> create table infos (id int);Query OK, 0 rows affected (0.00 sec)mysql> show create table infos;+-------+-----------------------------------------------------------------------------------------+| Table | Create Table                                                                            |+-------+-----------------------------------------------------------------------------------------+| infos | CREATE TABLE "infos" (  "id" int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+-----------------------------------------------------------------------------------------+1 row in set (0.00 sec)

3:create table name engine= storage engine (select when creating a table)

mysql> create table info (id int) engine=MyISAM;Query OK, 0 rows affected (0.00 sec)mysql> show create table info;+-------+----------------------------------------------------------------------------------------+| Table | Create Table                                                                           |+-------+----------------------------------------------------------------------------------------+| info  | CREATE TABLE "info" (  "id" int(11) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8 |+-------+----------------------------------------------------------------------------------------+1 row in set (0.00 sec)

4:mysql_convert_table_format--host= that host login--user= user--password= password--socket=/tmp/mysql.sock--engine=InnoDB database table name ( Multiple tables simultaneously modify the storage engine)

(above this command in version 5.7 is not only available in 5.5 versions)

#在修改前先安装下列三个工具包[[email protected] bin] yum install perl-DBD-MySQL -y[[email protected] bin] yum install mysal-DBI -y[[email protected] bin] yum install perl-DBI -y[[email protected] bin] mysql_convert_table_format --host=localhost --user=root --password=123123 --socket=/tmp/mysql.sock --engine=InnoDB cilent user info

MySQL Storage engine