Advantages and disadvantages of Mysql storage engines

The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities. It is suitable for applications with frequent queries such as data warehouses. In MyISAM, a table is actually saved as three files, the. frm storage table definition,. MYD storage data, and. MYI storage index. The NULL value is allowed in the indexed column.

InnoDB: This type is transaction-safe. it has the same features as the BDB type and supports foreign keys. the InnoDB table is fast. it has more features than BDB. Therefore, if you need a transaction-safe storage engine, we recommend that you use it. if your data executes a large number of INSERT or UPDATE operations, InnoDB tables should be used for performance considerations. InnoDB provides MySQL with a transaction and rollback) and the transaction security (transaction-safe (ACID compliant) Table of the crash recovery capabilities. InnoDB
Provides locking on row level and non-locking read in SELECTs, which is consistent with Oracle ). These features improve the performance of multi-user concurrent operations. In the InnoDB table, no need to expand the lock escalation, because the row level locks of InnoDB is suitable for a very small space. InnoDB is the first MySQL table engine to provide foreign key constraints (foreign key constraints. InnoDB is designed to handle large-capacity database systems.
CPU utilization is incomparable to other disk-based relational database engines. Technically, InnoDB is a complete database system on the MySQL background. InnoDB establishes a dedicated buffer pool in the primary memory for high-speed data buffering and indexing. InnoDB stores data and indexes in tablespaces and may contain multiple files, which is different from others. For example, in MyISAM, tables are stored in separate files. The size of the InnoDB table is limited by the file size of the operating system, generally 2 GB. All InnoDB tables are stored in the same data file ibdata1 (multiple files or independent tablespace files), which is relatively difficult to back up, the free solution can be copying data files and backing up data files.
BINLOG, or use mysqldump.

Differences:
1. InnoDB does not support Fulltext indexes.
2. innoDB does not store the specific number of rows in the table. That is to say, when you execute select count (*) from table, InnoDB needs to scan 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.
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, update table set num = 1 where name like "% AAA %"
You can run the show create table tablename command to view the table type.
2.1 The start/commit operation on tables that do not support transactions has no effect, and has been committed before the execution of the commit operation.
You can run the following command to switch the non-transaction table to the transaction (data will not be lost). The innodb table is safer than the myisam table:
Alter table tablename type = innodb;

The read and write locks of the MyISAM storage engine are mutually exclusive,Read/write operations are serialized.. So, one process requests the read lock of a MyISAM table, and the other process also requests the write lock of the same table. How does MySQL handle this? The answer is that the write process obtains the lock first. Not only that, even if the Read Request first goes to the lock wait queue, after the write request arrives, the write lock will be inserted before the read lock request! This is because MySQL considers that write requests are generally more important than read requests. This is precisely why the MyISAM table is not suitable for a large number of update operations and query operation applications, because,A large number of update operations may make it difficult for query operations to obtain read locks, which may be blocked forever.This situation may sometimes become very bad! Myisam has read and write locks (both of which are table-level locks ).

MySQL Table-level locks can be used in two modes: Table Read Lock and Table Write Lock ). What does this mean? When reading a MyISAM table, it does not block read requests from other users to the same table, but blocks write operations on the same table; write operations on the MyISAM table will block read and write operations on the same table by other users.

InnoDB row locks are implemented by locking the index items. That is, InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, table locks are used! Row-level locks consume more resources than table locks each time they get or release locks. When two InnoDB transactions are deadlocked, the number of rows affected by each transaction is calculated, and the transaction with fewer rows is rolled back. When Innodb is not involved in the locking scenario, innodb cannot be detected. It can only be solved by locking timeout.

Database deadlock example:

Two sesison, the first update table t1, do not submit. Do not submit the second update table t2. Then session1 goes to update table t2, and session2 goes to update table t1. A deadlock occurs.

Cold backup mysql and Hot Backup mysql:

Cold backup is to directly cp all database files.

Hot Backup:

1) myisam engine. 1. flush tables with read lock; 2. cp ......; 3. unlock tables;

As a database server host, the most important thing is the overall IO performance of the host, including disks, memory and various IO-related boards.

In Mysql, The orderby keyword is used. There are two sorting methods:

Before Version 4.1, we used to retrieve the fields to be sorted and the pointer to the entire record, and then sort the fields to be sorted in the specified sorting area. After sorting, then retrieve the corresponding records based on the pointer. That is to say, this algorithm needs to access data twice. An improved algorithm has been adopted since version 4.1. Retrieve all matching records at a time. This reduces random I/O (previously recorded as random I/O after algorithm sorting ). However, this improved method greatly wastes the memory used for sorting. Therefore, to improve the performance, we try to retrieve only the required fields in the query statement.

The myql profile can be used to analyze the cpu and io usage of the query statement.

[SQL]View
Plaincopy

1. Set profiling = 1
3. Select count (*) from user;
5. Show profiles;
7. Show profile for query 1;

We can see the basic impact of cache on mysql performance. Increase speed by nearly 30 times.