This is the 21st day of my participation in the August Text Challenge.More challenges in August

Introduce the InnoDB

InnoDB, one of MySQL’s database engines, is now the default storage engine of MySQL, and one of the standards for MySQL AB to publish binary. InnoDB was developed by Innobase Oy and acquired by Oracle in May 2006. Compared to traditional ISAM and MyISAM, InnoDB features acid-compliant Transaction functionality, similar to PostgreSQL.

InnoDB currently uses a dual-track licensing system, one for GPL and the other for proprietary software.

InnoDB architecture diagram

Threading model

Innodb is a multithreaded model

  • Master Thread: the main Thread that is responsible for asynchronously refreshing the buffer pool to disk, flushing dirty pages, merging and inserting buffers, and UNDO page recycling
  • IO Thread: insert buffer,log IO thread,4个write,4个read
  • Purge Thread: Purge the undo page to support multiple counties
  • Page Cleaner Thread: Clean Page refresh, separate from Master, improve query performance

Innodb, MyISAM comparison

1. The InnoDB engine

Execute the following code to insert 1000 entries into a table using the InnoDB engine

import pandas as pd from sqlalchemy import create_engine import time db = Create_engine ('mysql+pymysql://mysql:[email protected]:3306/test') start = time.time() for I in range(1000): data = {'index': i, 'name': 'name_' + str(i), 'age': i, 'salary': i, 'level': i} df = pd.DataFrame(data, index=[0]) df.to_sql('innodb', db, if_exists='append', index=False) end = time.time() print(end - start)Copy the code

After executing the above code for three times, the program takes 12.58s, 14.10s and 12.71s to write 1000 pieces of data, with an average write time of 13.13s.

2. MyISAM engine

Execute the following code to insert 1000 entries into the table using the MyISAM engine

import pandas as pd from sqlalchemy import create_engine import time db = Create_engine ('mysql+pymysql://mysql:[email protected]:3306/test') start = time.time() for I in range(1000): data = {'index': i, 'name': 'name_' + str(i), 'age': i, 'salary': i, 'level': i} df = pd.DataFrame(data, index=[0]) df.to_sql('myisam', db, if_exists='append', index=False) end = time.time() print(end - start)Copy the code

After executing the above code for three times, the program takes 6.64s, 6.99s, 7.29s to write 1000 pieces of data, and the average write time is 6.97s.

1. Storage structure

MyISAM: Each MyISAM is stored as three files on disk. Table definition file, data file, index file. The name of the first file starts with the name of the table, and the extension indicates the file type. . FRM File storage table definition. The extension name of the data file. MYD (MYData). The index file extension is. MYI (MYIndex).

InnoDB: All tables are stored in the same data file (can be multiple files, or separate table space files), InnoDB table size is only limited by the size of the operating system file, generally 2GB.

2. Storage space

MyISAM: MyISAM supports three different storage formats: static table (default, but note that the end of data can not have Spaces, will be removed), dynamic table, compressed table. After a table is created and data is imported, it does not need to be modified. You can use compressed tables to greatly reduce disk space usage.

InnoDB: Needs more memory and storage, it creates its own buffer pool in main memory for caching data and indexes.

3. Portability, backup and recovery

MyISAM: Data is stored as files, so it is very convenient for cross-platform data transfer. You can operate on a single table during backup and restore.

InnoDB: The free solutions can be copying data files, backing up binlogs, or using mysqldump, which is relatively painful when the data volume is tens of gigabytes.

4. Transaction support

MyISAM: The emphasis is on performance, atomicity per query, faster number of executions than InnoDB types, but no transaction support.

InnoDB: provides advanced database functions such as transaction support transactions and external keys. Transaction-safe (ACID Compliant) tables with COMMIT, Rollback, and Crash recovery capabilities.