1/ISAM
ISAM is a well-defined and time-tested approach to data table management that is designed with the database being queried much more often than it is updated. As a result, ISAM performs reads quickly and does not take up a lot of memory and storage resources. The two main drawbacks of ISAM are that it does not support transaction processing and is not fault tolerant: if your hard drive crashes, then the data files cannot be recovered. If you are using ISAM for mission-critical applications, you must always back up all your real-time data, and MySQL can support such backup applications thanks to its replication feature.
2/InnoDB
It provides transaction control capability function, which ensures that a set of commands are executed successfully, or when any command error occurs, the result of all commands is rolled back, it can be imagined that transaction control capability is very important in e-banking. Support for COMMIT, ROLLBACK, and other transaction features. The latest version of Mysql is already planning to remove support for BDB in favor of InnoDB.
MyIASM is a new version of IASM tables with the following extensions:
Portability at the binary level.
NULL column index.
There are fewer fragments for variable rows than ISAM tables.
Support for large files.
Better index compression.
Is it better to have a statistical distribution?
Better and faster auto_increment processing.
Here are some details and specific implementation differences:
1. InnoDB does not support indexes of type FULLTEXT.
2. Tables are not saved in InnoDB
specific rows, that is, execute select count(*) from
table, InnoDB scans the entire table to calculate how many rows there are, but MyISAM simply reads out the saved rows. Note that when the count(*) statement contains
where condition, the operation of both tables is the same.
3. For fields of AUTO_INCREMENT type, the index with only that field must be included in InnoDB, but in the MyISAM table, it is possible to create a federated index with other fields.
4.DELETE
FROM table, InnoDB does not recreate the table, but deletes it line by row.
5.LOAD TABLE FROM
The MASTER operation does not work for InnoDB, the solution is to change the InnoDB table to MyISAM table first, import the data, and then change it to the InnoDB table, but it is not applicable to tables that use additional InnoDB features (such as foreign keys).
In addition, the row lock of an InnoDB table is not absolute, if MySQL cannot determine the scope to be scanned when executing a SQL statement, the InnoDB table will also lock the entire table, such as update
table set num=1 where name like “�a%”
No table is omnipotent, only by choosing the appropriate table type for the business type can we maximize the performance advantages of MySQL.
MySQL
When Administrator creates a database, the table defaults to the InnoDB type.
InnoDB，MyISAM
What is the difference between the two types: The MyISAM type does not support advanced processing such as transaction processing, while the InnoDB type does.
Tables of the MyISAM type emphasize performance, which performs several degrees faster than the InnoDB type, but does not provide transactional support, while InnoDB provides advanced database features such as transactional support, foreign keys, etc.
MyISAM type binary data files can be migrated across different operating systems. That is, it can be copied directly from Windows system to linux system.
Modify the engine type of the table:
ALTER
TABLE tablename ENGINE = MyISAM ；
MyISAM:,它是基于传统的ISAM类型,ISAM是Indexed
Sequential Access Method
It is the standard method for storing records and files. Compared to other storage engines, MyISAM has most tools for checking and repairing tables.
MyISAM tables can be compressed, and they support full-text search. They are not transactionally secure and do not support foreign keys. If something is rolled back, it will cause incomplete rollback and is not atomic. If a large number of executions are carried out
TheSELECT, MyISAM is the better choice.
InnoDB:这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑，应该使用InnoDB表,
For InnoDB types that support things, the main reason that affects speed is that the AUTOCOMMIT default setting is turned on and the program does not explicitly call BEGIN
Start a transaction, resulting in an automatic commit for each insertion, which seriously affects the speed. You can call start before SQL is executed, and multiple SQL forms one thing (even if autocommit hits
can also be turned on), which will greatly improve performance.
1. View the table information, including the type of engine used, character encoding, table structure, etc
Use this command
mysql>
show create table t1; --t1 is the table name
2.
The following command can be executed to switch non-transactional tables to transactions (data is not lost), innodb tables are more secure than myisam tables:
alter table t1
type=innodb; --t1 is the table name
3.
The innodb table cannot be table_name with the repair table command and myisamchk -r
But you can use a check table
t1, and mysqlcheck [OPTIONS] database [tables]
4.
The following parameters have been added to the command line to start the mysql database to make all newly released mysql data tables default to using transactions (
It only affects the create statement. ）
--default-table-type=InnoDB
5.
Temporarily changing the default table type can be done by:
set table_type=InnoDB;
MyISAM
Advantages: fast speed, less disk space occupied; The disk usage of a database or table can be found either by checking the size of the corresponding file (folder) by the operating system or by using the SQL statement SHOW TABLE STATUS
Cons: No data integrity mechanism, i.e., no support for transactions and foreign keys
InnoDB
Advantages: Support transactions and foreign keys, and complete data integrity mechanism. You can use SHOW TABLE STATUS to check the disk occupancy of a library or table
Disadvantages: ultra-slow speed, large disk space occupied; All libraries are stored in one (usually) or several files, and the operating system cannot tell how much space a library or table has
BDB
Advantages: Support transactions, do not support foreign keys, because on the basis of transaction support, foreign keys can be implemented indirectly on the client side of the database (which may be the server side of the end customer, such as PHP), so data integrity is still guaranteed;
Disadvantages: slow speed, high disk usage; You cannot query the space occupancy of a database or table through SHOW TABLE STATUS. The operating system can understand the size of the corresponding folder of the library or the corresponding file of the table, but because the BDB table always has to generate a log file, and the actual disk occupancy should include the log file, the size of a library or table is always less than the actual space occupied by the operating system.
|
Posted on 7/16/2017 11:04:57 AM
|
|
|
Landlord