Mysql learning-InnoDB and Myisam, mysqlinnodbmyisam

Source: Internet
Author: User
Tags table definition

Mysql learning-InnoDB and Myisam, mysqlinnodbmyisam

MysqlInnoDB and Myisam are two types of storage. When creating a table in Mysql, you can specify the options, as shown in:

The following describes the differences between the two types of storage:
1. The transaction mechanisms of the two types of data storage are different.
InnoDB supports transactions, but Myisam does not. However, Myisam performs better in queries.
2. Different locking mechanisms
InnoDB is a row-Level Lock that supports concurrent operations to a greater extent. Myisam is a table-Level Lock.
3. Data Operations
Using InnoDB for data modification, addition, and deletion has higher performance, while using Myisam for queries with large data volumes.
When querying the number of database entries, InnoDB does not save the number of data entries in the table and recalculates each time. Myisam saves the total number of entries in each table and does not need to recalculate each query.
During query, InnoDB loads a lot more than Myisam. For example, InnoDB caches data blocks and Myisam caches only index blocks. InnoDB uses ing and Myisam uses offset.
4. Storage
InnoDB resources are based on tablespace data files and log files. The size is limited to the operating system file size.
Myisam is stored in the disk as a file:. frm storage table definition;. MYD data file;. MYI index file
5. Field Auto-Increment
For fields of the auto_increment type, InnoDB must contain only the index of this field. However, you can create a joint index with other fields in Myisam.
6. Mysiam supports full-TEXT indexing (full-TEXT indexing can be created on VARCHAR or TEXT columns). InnoDB does not support full-TEXT indexing.
7. When deleting a table, InnoDB will not recreate the table and delete a row.

Row-level locks of InnoDB tables are not absolute, for example:
Update table set num = 1 where name like "% a % ";
InnoDB also scans the entire table for uncertain scan ranges.
Speaking of this, I will consider the read/write splitting of the database. The read database can use the Myisam engine for storage, and the write database uses the InnoDB Engine for storage. We will discuss the master-slave synchronization of the database later.
Methods to Improve InnoDB database performance:
The innodb_flush_log_at_trx_commit option affects the performance of the InnoDB database. If it is set to 1, the data is automatically submitted each time the data is inserted, resulting in a sharp drop in performance. The setting to 0 significantly improves the performance.
I will write a special article on Mysql optimization later.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.