Linux O & M Study Notes-MySQL storage engine introduction, Study Notes mysql

Linux O & M Study Notes-MySQL storage engine introduction, Study Notes mysql

Chapter 2 MySQL storage engine

I. MySQL common Engines

MyISAM, InnoDB, HEAP (In-Memory), and NDB (clustered)

Ii. Architecture of MySQL plug-in storage engine

Iii. Two most common MySQL engines: MyISAM and InnoDB

1. MyISAM: Table-Level Lock. transactions are not supported. Performance is emphasized, and the execution speed is faster than that of InnoDB. Number of rows in the table to be saved

2. InnoDB: Row-level locks. InnoDB supports advanced database functions such as transactions and external keys. The number of rows in the table that are not saved

3. Before MySQL5.5.1 (inclusive), MyISAM engine is used by default, and InnoDB engine is used by default after MySQL5.1.

Iv. MyISAM Engine

1. Introduction

MyISAM is the default storage engine before MySQL5.1 (inclusive. It is based on older ISAM code, but there are many useful extensions.

Each MyISAM is stored as three files on the disk. The name of the first file starts with the name of the table. The extension indicates the file type. MySQL System database tables are basically MyISAM engine files:

Ls-l/data/3306/data/mysql

-Rw ---- 1 mysql 8820 Mar 28 13: 36columns_priv.frm

-Rw ---- 1 mysql 0 Mar 28 13:36 columns_priv.MYD

-Rw ---- 1 mysql 4096 Mar 28 13: 36columns_priv.MYI

...

-Rw ---- 1 mysql 10630 Mar 28 user. frm

-Rw ---- 1 mysql 456 Apr 17 user. MYD

-Rw ---- 1 mysql 2048 Apr 17 user. MYI

. Frm file storage table definition.

The data file extension is. MYD (MYData ).

The extension of the index file is. MYI (MYIndex ).

To explicitly indicate that you want to use a MyISAM TABLE, use the engine table option to specify: create table t (I INT) ENGINE = MYISAM; generally, the ENGINE option is unnecessary; myISAM is the default storage engine unless it has been changed by default.

2. Run the command to view the MyISAM engine file type

File user. frm

User. frm: MySQLtable definition file Version 9

File user. MYD

User. MYD: Hitachi SH big-endian COFF executable, not stripped

File user. MYI

User. MYI: MySQLMISAM compressed data file Version 1

3. Features of The MyISAM Engine

(1) transactions are not supported.

(2) Table-level locking (locking the entire table during update)

(3) reading and writing are blocked, but reading is not blocked.

(4) Only indexes are cached and data is not cached.

(5) fast reading speed and relatively low resource occupation

(6) foreign key constraints are not supported, but full-text index payment is supported

4. production scenarios applicable to the MyISAM Engine

(1) services with low data consistency without transaction support

(2) It is generally applicable to applications with more reads and writes, but not those with more reads or writes.

(3) businesses with relatively low read/write concurrent access (pure read/write high concurrency is also acceptable) (locking mechanism problems)

(4) services with relatively few data changes (blocking problem)

(5) Some businesses of Small and Medium websites will use

5. Optimization of the MyISAM Engine

(1) set an appropriate index (Cache Mechanism)

(2) Adjust the read/write priority and ensure that important operations are given priority based on actual needs.

(3) Enable delayed insertion to improve Writing Performance in large batches (reduce the write frequency and write as many data records as possible at one time)

(4) try to write the Insert data at the end in sequence to reduce blocking.

(5) decomposition of large and long operations to reduce the blocking time of a single operation

(6) Reduce the number of concurrent connections (reduce access to MySQL). In some high-concurrency scenarios, queue mechanisms are queued through applications.

(7) The Query Cache or Memcached Cache service can greatly improve the access efficiency for relatively static (Infrequently changed) database data.

Grep query my. cnf

Query_cache_size = 256 M

Query_cache_limit = 1 M

Query_cache_min_res_unit = 2 k

(8) MyISAM Count is especially efficient only during full table scan. Actual data access is required for Count with other conditions.

(9) The master database for master-slave synchronization can use the InnoDB engine, and the slave database can use the MyISAM engine (not recommended)

V. InnoDB Engine

1. Introduction

InnoDB provides MySQL with a transaction security (ACID-compatible) storage engine with the capabilities of commit, rollback, and crash recovery. InnoDB locks row-level and also provides an Oracle-style non-locked read in the SELECT statement. These features increase the deployment and performance of multiple users. There is no need to expand locking in InnoDB, because row-level locking in InnoDB is suitable for very small space. InnoDB also supports foreign key forcing. In SQL queries, You can freely mix InnoDB tables with other MySQL tables, or even in the same query.

InnoDB is designed for maximum performance when processing massive data volumes. Its CPU efficiency may be unmatched by any other disk-based relational database engine.

The InnoDB Storage engine is fully integrated with the MySQL server. the InnoDB Storage engine maintains its own buffer pool to cache data and indexes in the main memory. Two important disk-based resources managed by the InnoDB Storage engine are InnoDB tablespace data files and their log files. InnoDB stores its tables and indexes in a tablespace. If you specify no InnoDB configuration options, MySQL will create a 10 MB auto-scaling data file named ibdata1 In the MySQL data directory, and two 5 MB log files named ib_logfile0 and ib_logfile1.

A tablespace can contain several files (or original disk partitions ). This is different from the MyISAM table. For example, in the MyISAM table, each table is in a separate file. InnoDB tables can be of any size, even on an operating system with a file size limited to 2 GB.

2. Features of the InnoDB Engine

(1) support transactions

(2) Row-level locking (locking the current row when updating), but during full table scanning, it will still be a table lock. Pay attention to the impact of the GAP lock.

(3) read/write blocking is related to the transaction isolation level.

(4) cache indexes and Data

(5) the entire table and primary key are stored in clusters to form a Balance Tree.

(6) foreign key constraints are supported. Full-text indexes are not paid before 5.5, and are supported after 5.5.

(7) All Secondary indexes store the primary key information.

(8) supports partitions and tablespaces, similar to Oracle

(9) high requirements on hardware resources.

3. production scenarios applicable to the InnoDB Engine

(1) services with high data consistency that require transaction support

(2) Row-level locking has good adaptability to high concurrency, but it is necessary to ensure that the query is completed through the index.

(3) scenarios with frequent read/write and updates: BBS, SNS, Weibo, etc.

(4) The Memory configured in the hardware is large, and the good cache capability of InnoDB can be used to improve memory utilization and minimize disk I/O.

Grep innodb my. cnf

Innodb_additional_mem_pool_size = 4 M

Innodb_buffer_pool_size = 2048 M # cache. The official recommendation is 50-80% of the total memory.

Innodb_data_file_path = ibdata1: 128 M: autoextend

Innodb_file_io_threads = 4

Innodb_thread_concurrency = 8

Innodb_flush_log_at_trx_commit = 2

Innodb_log_buffer_size = 16 M

Innodb_log_file_size = 128 M

Innodb_log_files_in_group = 3

Innodb_max_dirty_pages_pct = 90

Innodb_lock_wait_timeout = 120

When innodb_file_per_table = 0 # is 1, each table has a tablespace file.

4. Optimization of the InnoDB Engine

(1) The primary key should be as small as possible to avoid putting too much space on the Secondary Index

(2) Avoid full table scan because table locks are used.

(3) cache all indexes and data as much as possible to increase the response speed and reduce disk IO

(4) do not use autocommit to automatically commit transactions when large volumes of small inserts are performed.

(5) Reasonably set the innodb_flush_log_at_trx_commit parameter value. We recommend that you set the value to 2 instead of excessively pursuing Security.

If innodb_flush_log_at_trx_commit = 0, log buffer writes log files to the disk every second, and does not perform any operations when committing transactions. The best performance and the worst security. When the system goes down, data is lost for one second.

Innodb_flush_log_at_trx_commit = 0: when each transaction is committed, the transaction log is written from the cache to the log file every second, and the data in the log file is refreshed to the disk. Even if the server is not down, but the MySQL service is down, data may be lost.

Innodb_flush_log_at_trx_commit = 1: When each transaction is committed, the transaction log is written from the cache to the log file, and the log file data is refreshed to the disk.

Innodb_flush_log_at_trx_commit = 2: When each transaction is committed, the transaction log is written from the cache to the log file. The log file is refreshed every second, but not necessarily to the disk. Data can be lost only when the system is down. (Recommended)

(6) Avoid Primary Key Update, because it will bring a lot of data movement

Vi. Differences between InnoDB and MyISAM

1. InnoDB does not support full-text indexes.

2. InnoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) fromtable, InnoDB scans the entire table to calculate the number of rows, however, MyISAM simply needs to read the number of lines saved. Note that when the count (*) statement contains the where condition, the operations on the two tables are the same.

3. For fields of the AUTO_INCREMENT type, InnoDB must contain only the index of this field. However, in the MyISAM table, you can create a joint index with other fields.

4. When deleting FROM table, InnoDB does not create a new table, but deletes a row.

5. The load table frommaster operation does not work for InnoDB. The solution is to change the InnoDB TABLE to the MyISAM TABLE first, and then the InnoDB TABLE after the data is imported, however, it is not applicable to tables that use additional InnoDB features (such as foreign keys.

6. In addition, the row lock of the InnoDB table is not absolute. If MySQL cannot determine the scope to be scanned when executing an SQL statement, the InnoDB table will also lock the entire table, for example, updatetable set num = 1 where name like "% aaa %"

7. Summary

The main difference between the two types is that Innodb supports transaction processing and Foreign keys and row-level locks. MyISAM is not supported. Therefore, MyISAM is often considered only suitable for small projects.

VII. Advantages of MyISAM (online articles, not necessarily)

As a user of MySQL, Innodb and MyISAM are both preferred. If the database platform meets the requirement of 99.9% stability, convenient scalability and high availability, MyISAM is definitely the first choice. The reason is as follows:

1. Most projects hosted on the platform are read-write-less projects, while MyISAM has a much better read performance than Innodb.

2. MyISAM indexes and data are separated, and the indexes are compressed, so the memory usage increases a lot. More indexes can be loaded, while Innodb is closely bound with indexes and data. Without compression, Innodb is much larger than MyISAM.

3. Application developers often fail to update the where write range of a table every one or two months. As a result, the table cannot be used normally, at this time, the superiority of MyISAM is reflected. The files of the corresponding table can be retrieved from the compressed package copied on the current day, stored in a database directory, dumped into SQL, and then exported back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast. Don't tell me to let Innodb regularly use the xxx. SQL export mechanism for backup, because the minimum data volume of a database instance is usually dozens of GB.

4. In terms of application logic, selectcount (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements, in fact, this operation of Innodb locks the table. Many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.

5. There are often many application departments that need to regularly provide some table data to them. For MyISAM, It is very convenient to send it to the corresponding table's frm. MYD and MYI files, so that they can start the database in the corresponding version, and Innodb needs to export xxx. SQL, because the dictionary data files cannot be used by the other party.

6. If Innodb and MyISAM are compared with insert write operations, Innodb still cannot achieve the Write Performance of MyISAM. If it is an index-based update operation, although MyISAM may be inferior to Innodb, however, it is also a problem whether the database can catch up with such highly concurrent writes. It is better to solve this problem through the Multi-instance database/table sharding architecture.

7. If MyISAM is used, the merge engine can greatly accelerate the development speed of the Application Department. They only need to perform some selectcount (*) operations on the merge table, it is very suitable for business tables of a type of rows (such as logs, survey statistics) with a total number of hundreds of millions of projects.

Of course, Innodb is not absolutely unnecessary. Innodb is used for transaction projects. In addition, some may say that MyISAM cannot resist too many write operations, but it can be compensated by the architecture.

8. Modify MySQL service engine in batches

1. Single modification: SQL statement Modification

(1) Modify commands

Alter table table_name Engine = InnoDB;

Alter table table_name Engine = MyISAM;

(2) Example

A. view the storage engine of the student table

Show create table student;

+ --------- + --------------------------------------------------------

| Table | Create T able |

+ --------- + ---------------------------------------------------------

| Student | create table 'student '(

'Id' int (2) default null,

'Name' varchar (20) DEFAULT NULL

) ENGINE = InnoDBDEFAULT CHARSET = latin1 |

+ --------- + -------------------------------------------------------- +

B. Modify the engine

Alter table student engine = MyISAM;

C. view the storage engine of the student table

Show create table student;

+ --------- + --------------------------------------------------------

| Table | Create T able |

+ --------- + ---------------------------------------------------------

| Student | create table 'student '(

'Id' int (2) default null,

'Name' varchar (20) DEFAULT NULL

) ENGINE = MyISAMDEFAULT CHARSET = latin1 |

+ --------- + -------------------------------------------------------- +

2. Batch Modification

(1) Method 1: Use the for Loop

(2) Method 2: Use sed to Perform engine conversion on the backup content (poor performance when the data volume is large)

Sed-e's # InnoDB # MyISAM # G' bak_InnoDB. SQL> bak_MyISAM. SQL

Mysql-uroot-p

(3) method 3: Use mysql_convert_table_format for engine Conversion

A. View commands

Which mysql_convert_table_format

/Application/mysql/bin/mysql_convert_table_format

B. Change the specified table

Mysql_convert_table_format -- user = root -- password = '000000' -- socket =/data/123456/mysql. sock -- engine = InnoDB test student

C. modify all tables in the database in batches

Mysql_convert_table_format -- user = root -- password = '000000' -- socket =/data/123456/mysql. sock -- engine = InnoDBtest