Advantages and disadvantages of MySQL two storage engines

The MyISAM engine is a non-transactional engine that provides high-speed storage and retrieval, as well as full-text search capabilities, and is suitable for applications where queries are frequently used in data warehouses. In MyISAM, a table is actually saved as three files, the. frm store table definition,. MyD store data,.  Myi the storage index. A null value is allowed in the column of the index.

How to view the storage engine for a table

SHOW  TABLE< Span class= "Apple-converted-space" > STATUS FROM database WHERE name  =  

InnoDB: This type is transaction-safe. It has the same characteristics as the BDB type, and they also support foreign keys. InnoDB tables are fast. Has a richer feature than BDB, so it is recommended if a transaction-safe storage engine is required. If your data performs a large number of inserts or update, for performance reasons, you should use the InnoDB table, InnoDB to MySQL Provides a transaction security (Transaction-safe (ACID compliant)) Table with transaction (commit), rollback (rollback), and crash-repair capability (crash recovery capabilities). The InnoDB provides a row lock (locking on row level) that provides an unlocked read (non-locking read in selects) that is consistent with the Oracle type. These features improve the performance of multi-user concurrency operations. There is no need to widen the lock (lock escalation) in the InnoDB table because the InnoDB column lock (Row level locks) is suitable for very small space. InnoDB is the first table engine on MySQL to provide a foreign key constraint (FOREIGN key constraints). InnoDB's design goal is to handle a large-capacity database system, which is not comparable to other disk-based relational database engines. Technically, InnoDB is a complete database system placed in the background of MySQL, InnoDB in the main memory to establish its dedicated buffer pool for caching data and indexes. InnoDB the data and index in the table space, may contain multiple files, which is different from other, for example, in MyISAM, the table is stored in a separate file. The size of the InnoDB table is limited only by the size of the operating system file, typically 2 GB. InnoDB All tables are stored in the same data file Ibdata1 (or possibly multiple files, or stand-alone tablespace files), relatively poorly backed up, the free scheme can be copy data files, backup Binlog, or mysqldump.

Difference:
1.InnoDB does not support indexes of type Fulltext.
The exact number of rows in the table is not saved in 2.InnoDB, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the saved rows. Note that when the COUNT (*) statement contains a where condition, the operation of the two tables is the same.
3. For a field of type auto_increment, InnoDB must contain only the index of that field, but in the MyISAM table, you can establish a federated index with other fields.
4.DELETE from table, InnoDB does not reestablish the table, but deletes one row at a time.
In addition, the row lock of the InnoDB table is not absolute, and if MySQL cannot determine the range to scan when executing an SQL statement, the InnoDB table also locks the full table, such as the Update table set num=1 where name like "%aaa%"
You can use the Show CREATE TABLE tablename command to see the type of the table.
2.1 Doing a start/commit operation on a table that does not support transactions has no effect and has been committed before committing a commit
You can execute the following command to toggle non-transactional tables to transactions (data is not lost), and the InnoDB table is more secure than the MyISAM table:
ALTER TABLE TableName TYPE=INNODB;

The read and write locks of the MyISAM storage engine are mutually exclusive, and the read and write operations are serial . So, one process requests a read lock on a MyISAM table, and another process requests a write lock on the same table, how does mysql handle it? The answer is that the write process gets the lock first. Not only that, even if the read request goes to the lock waiting queue, and the write request is reached, the write lock is inserted before the read lock request! This is because MySQL considers writing requests to be generally more important than reading requests. This is why the MyISAM table is not well suited for applications with a large number of update operations and query operations, because a large number of update operations can cause query operations to be difficult to obtain read locks, which can be blocked forever. This situation can sometimes get very bad! MyISAM is a read lock and a write lock (2 locks are table-level locks).

There are two modes for MySQL table-level locks: table-Shared read lock and table write lock. What do you mean, it means that when you read a MyISAM table, it does not block other users from reading requests for the same table, but it blocks writes to the same table, and writes to the MyISAM table blocks other users from reading and writing to the same table.

InnoDB row locks are implemented by locking the index entries, which means that only the data is retrieved through the index criteria, InnoDB uses row-level locks, or the table lock is used! Row-level locks consume more resources than table locks each time they acquire locks and release locks. When a deadlock occurs on a InnoDB two transaction, the number of rows affected by each transaction is calculated, and then the transaction with the least number of rows is rolled back. When InnoDB is not involved in a locked scene, InnoDB is not detected. Can only be resolved by locking timeout.

Database Deadlock Example:

Two Sesison, the first update table T1, do not commit. The second Update table T2, do not commit. Then session1 go to Update table T2,session2 to update table T1, this time there is a deadlock.

Cold backup MySQL and hot backup mysql:

Cold backup is the direct CP all database files.

Hot backup:

1) MyISAM engine. 1, flush tables with read lock; 2. CP ...; 3, unlock tables;

As the host of the database server, the most critical is the overall IO performance of the host, including disk, memory, and various IO-related board cards.

In MySQL, there are two ways to sort by using the By keyword:

Version 4.1 used to take a pointer to the field that needs to be sorted and the entire record, then sort the fields that need to be sorted in the specified sort area, sort them, and then remove the corresponding records according to the pointer. In other words, this algorithm requires access to data two times. Starting with version 4.1, an improved algorithm was adopted. All eligible records are taken out at once. This reduces the random IO (before the algorithm is sorted to record the random io). But this improved approach greatly wastes the memory used when sorting. So in order to improve performance, try to only take out the fields we need 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;

The basic thing we can see from this is the performance impact of caching on MySQL. Nearly 30 times times faster

Advantages and disadvantages of MySQL two storage engines