"InnoDB and MyISAM Difference" "http://jeck2046.blog.51cto.com/184478/90499"
InnoDB and MyISAM are the two most common table types used by many people when using MySQL, both of which have pros and cons, depending on the application. The basic difference is that the MyISAM type does not support advanced processing such as transaction processing, and InnoDB type support.
The MyISAM type of table emphasizes performance, which executes faster than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database functionality such as transaction support, external keys, and so on.
Myiasm is a new version of the Iasm table, with the following extensions: • Portability at the binary level. A null column index. • Less fragmentation of the variable-length line than the ISAM table. • Supports large files. • Better index compression. • Better and faster auto_increment processing. Here are some details and implementation differences: 1. InnoDB does not support indexes of type Fulltext. 2.
the exact number of rows in the InnoDB table is not saved, that is, when you execute select COUNT (*) from table, InnoDB scans the entire table to calculate how many rows, but MyISAM simply reads the number of rows saved. 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. 5. The LOAD TABLE from master operation has no effect on InnoDB, and the workaround is to first
Change InnoDB table to MyISAM table, import data and change to InnoDB table, but for tables that use additional InnoDB attributes, such as foreign keys, do not apply. In addition, the row lock of the InnoDB table is not absolute, if MySQL is executed when executing an SQL statement
Unable to determine the range to scan, the InnoDB table also locks the entire table, such as the Update table set num=1 where name like "%aaa%" in summary, any one of the tables is not omnipotent, only appropriate for the business type to select the appropriate table type, To maximize the performance advantage of MySQL. "A detailed comparison of MySQL database storage engine MyISAM and InnoDB" "http://blog.csdn.net/fbd2011/article/details/7057109" ISAM is indexed sequential An abbreviation for access method, which has indexed sequential access methods. It is the standard way to store records and files. Not transactional security, and foreign keys are not supported, and if a large number of Select,insert MyISAM are performed, it is more appropriate. InnoDB: A transaction-safe engine that supports foreign keys, row locks, and transactions is his greatest feature. If you have a large number of update and insert, it is recommended to use InnoDB, especially for multiple concurrency and high QPS scenarios. MyISAM only supports table-level locks. InnoDB supports transactional and row-level locks, and is the most characteristic of InnoDB. ACID properties of the transaction: Atomicity,consistent,isolation,durable. Several problems caused by concurrent transactions: Update lost, Dirty read, non-repeatable read, Phantom read. Transaction ISOLATION LEVEL: uncommitted read (READ UNCOMMITTED), committed read (read Committed), REPEATABLE read (Repeatable read), serializable (Serializable) InnoDB has the following types of row lock modes: Shared lock, Exclusive lock, Intent shared lock (table lock), intent exclusive lock (table lock), Gap lock. NOTE: When the statement does not use an index, INNODB cannot determine the line of action, and this time uses the intent lock, which is the table lock. "MySQL Technology insider InnoDB storage Engine--innodb Storage Engine" "http://blog.csdn.net/yingminxing/article/details/8268484" "MySQL InnoDB Index principle detailed" "http://blog.csdn.net/chenlvzhou/article/details/40536473" "MySQL database storage engine detailed" "http://blog.csdn.net/koudaidai/article/details/7495738"
What is a MySQL database Typically, a database is a collection of data, and a database can be a collection of files on a memory or a collection of some memory data.
What we usually call MySQL database, SQL Server database and so on is actually a database management system, they can store data, and provide the ability to query and update data in the database, and so on. Depending on how the database stores the data and how the data is implemented, there are differences and similarities between the databases.
The MySQL database is a relational database of open source code. Currently, it can provide features such as support for SQL language, subqueries, stored procedures, triggers, views, indexes, transactions, locks, foreign key constraints, and image replication. In the late stages, we will explain these features in detail.
As with large database systems such as Oracle and SQL Server, MySQL is also a database of client/server systems and single-process multithreaded architectures.
One important feature of MySQL that distinguishes it from other database systems is that
plug-in storage engine support。
what is a storage engine?
Storage Engine The White is
How to store data, how to index stored data, how to update, query data, and other techniques to implement the method。 Because the storage of data in a relational database is stored as a table, the storage engine can also be called
Table Type(That is, the type that stores and operates this table).
There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. The MySQL database provides a variety of storage engines. Users can choose different storage engines for the data table according to different requirements, and users can write their own storage engine according to their own needs.
What storage engines are in MySQL?
1 MyISAM: This engine was first provided by MySQL. This engine can also be divided into static MyISAM, dynamic MyISAM and compression MyISAM three kinds:
Static MyISAM: If the length of each data column in the datasheet is pre-fixed, the server will automatically select this type of table. Because each record in the data table occupies the same amount of space, the table accesses and updates are highly efficient. When data is compromised, recovery is easier to do.
Dynamic MyISAM: If the varchar, xxxtext, or Xxxblob fields appear in the datasheet, the server will automatically select this type of table. This table has a small storage space relative to static MyISAM, but because each record has a different length, the data in the data table may
Discrete StorageIn memory, which in turn leads to a decrease in execution efficiency. Also, there may be a lot of fragmentation in memory. Therefore, this type of table is often used with the optimize table command or the optimization tool to
defragmentation。
Compression MyISAM: The two types of tables mentioned above can be compressed with the Myisamchk tool. This type of table further reduces the amount of storage consumed, but the table can no longer be modified after it is compressed. In addition, because it is compressed data, such a table should be read to the first time to extract the rows.
However, whatever the MyISAM table, it is now
transactional, row-level, and foreign key constraints are not supportedThe function.
2 MyISAM Merge Engine: This type is a variant of the MyISAM type. Merging tables is the merging of several identical MyISAM tables into a single virtual table. Often applied to logs and data warehouses.
The 3 Innodb:innodb table type can be seen as a further update to the MyISAM product, which provides
transactions, row-level lock mechanisms, and foreign key constraintsThe function.
4 Memory (Heap): This type of data table only exists in RAM. It uses a hash index, so the data is accessed very quickly. Because it exists in
Memory, so this type is often used in
Temp TableIn
5 Archive: This type only supports SELECT and INSERT statements, and does not support indexing. Often applied to logging and aggregation analysis.
Of course, MySQL supports more than just a few types of tables.
Let's look at how to view and set the data table type.
operations on the storage engine in MySQL1 View the storage engine that the database can support show engines; Displays the storage engine conditions supported by the current database. Desc[ribe] TableName; View the structure of the data table show create TABLE tablename; The CREATE statement for the display table shows the status like ' tablename ' "Setting the code of the storage Engine" CREATE table user (id int not NULL auto_increment, username char () not NULL, sex char (2), primary key (ID)) Engine=merge modify the storage engine, you can use the command ALTER TABLE TableName engine =enginename Differences between storage engines in order to make a decision on which storage engine to choose, we first need to consider
what different core features are available for each storage engine。 This feature allows us to differentiate between different storage engines. We generally divide these core functions into four categories: supported fields and data types, lock types, indexes, and processing. Some engines have unique features that can lead you to make decisions, and we'll look at these specific issues in a moment. fields and data types although all of these engines support common data types such as Integer, Real, and character, not all engines support other field types, especially blogs (binary large objects) or text text types. Other engines may only support a limited number of character widths and data sizes. These limitations can directly affect the data you can store, and may have an indirect effect on the type of search you implement or the indexes you create for that information. These differences can affect the performance and functionality of your application because you have to choose the capabilities of the storage engine that you want to store based on the type of data you are storing. The locking feature in the lock database engine determines how access and updates to information are managed. When an object in the database is locked for information updates, other processing cannot modify the data (which in some cases is not allowed to read) until the update is complete. Locking affects not only how many different applications update the information in the database, but also the query for that data. This is because the query may be accessing data that is being modified or updated. In general, this delay is very small. Most locking mechanisms are primarily designed to prevent multiple processes from updating the same data. Because of the need to lock in both information and update information, you can imagine that multiple applications can have a significant impact on the same database.
different storage engines support locking at different object levels, and these levels affect the information that can be accessed at the same time. There are three levels of support available:
table lock, block lock, and row lock。 The most supported are table locks, which are provided in MyISAM. When the data is updated, it locks the entire table. This prevents many applications from updating a specific table at the same time. This has a big impact on applying many multiuser databases because it delays the process of updating. Page-level locking uses the Berkeley DB Engine and locks data based on the uploaded information page (8KB). There is no problem with this lock when updates are made in many parts of the database. However, with the addition of a few lines of information to lock the last 8KB of the data structure, when the need to add a large number of rows, especially a large number of small data, will cause problems. Row-level locking provides the best parallel access capability, with only one row of data locked in a table. This means that many applications can update data from different rows in the same table without causing locking problems.
only the InnoDB storage engine supports row-level locking。 Index indexing can significantly improve performance when searching and recovering data in a database. Different storage engines provide different techniques for indexing. Some techniques may be better suited to the type of data you store. Some storage engines do not support indexing at all, either because they use a base table index (such as the merge engine) or because the data is stored in a way that does not allow indexing (such as federated or Blackhole engines). The transaction processing feature provides reliability by providing the ability to update and insert information into a table. This reliability is achieved by allowing you to update the data in the table, but only accept your changes to the table when all relevant operations for the application are complete. For example, in accounting processing, each accounting entry processing will include changes to the debit and credit account data, and you will need to use the transactional functionality to ensure that the data changes to the debit and credit accounts are completed successfully before you accept the modifications. If any of the operations fail, you can cancel the transaction and the modifications will not exist. If the transaction process is complete, we can confirm the operation by allowing this modification.
InnoDB Storage Engine
