• 欢迎访问速搜资源吧,如果在网站上找不到你需要的资源,可以在留言板上留言,管理员会尽量满足你!

【速搜问答】InnoDB是什么

问答 admin 1个月前 (04-19) 46次浏览 已收录 0个评论

汉英对照:
Chinese-English Translation:

InnoDB是MySQL的数据库引擎之一,为MySQL的默认存储引擎,为MySQL AB发布binary的标准之一与传统的ISAM与MyISAM相比,InnoDB的最大特色就是支持了ACID兼容的事务功能,类似于PostgreSQL。

InnoDB is one of the database engines of MySQL, the default storage engine of MySQL, and one of the standards for publishing binary for MySQL ab. compared with traditional ISAM and MyISAM, the biggest feature of InnoDB is that it supports acid compatible transaction functions, similar to PostgreSQL.

InnoDB 是 MySQL 的数据库引擎之一,现为 MySQL 的默认存储引擎,为 MySQL AB 发布 binary 的标准之一。InnoDB 由 Innobase Oy 公司所开发,2006 年五月时由甲骨文公司并购。与传统的 ISAM 与 MyISAM 相比,InnoDB 的最大特色就是支持了 ACID 兼容的事务(Transaction)功能,类似于 PostgreSQL。

InnoDB is one of the database engines of MySQL. Now it is the default storage engine of MySQL and one of the standards for publishing binary for MySQL ab. InnoDB was developed by innobase oy and acquired by Oracle in May 2006. Compared with traditional ISAM and MyISAM, the biggest feature of InnoDB is that it supports the acid compatible transaction function, similar to PostgreSQL.

目前 InnoDB 采用双轨制授权,一个是 GPL 授权,另一个是专有软件授权。

At present, InnoDB adopts two track licensing system, one is GPL licensing, the other is proprietary software licensing.

简介

brief introduction

事务型数据库的首选引擎,支持 ACID 事务,支持行级锁定。InnoDB 是为处理巨大数据量时的最大性能设计。InnoDB 存储引擎完全与 MySQL 服务器整合,InnoDB 存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB 存储它的表&索引在一个表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与 MyISAM 表不同,比如在 MyISAM 表中每个表被存在分离的文件中。InnoDB 表可以是任何尺寸,即使在文件尺寸被限制为 2GB 的操作系统上。InnoDB 默认地被包含在 MySQL 二进制分发中。Windows Essentials installer 使 InnoDB 成为 Windows 上 MySQL 的默认表。

The preferred engine for transactional databases, supports acid transactions and row level locking. InnoDB is designed for maximum performance when processing large amounts of data. InnoDB storage engine is fully integrated with MySQL server. InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its table & indexes in a table space that can contain several files (or raw partitions). This is different from the MyISAM table, for example, in which each table exists in a separate file. InnoDB tables can be any size, even on operating systems where the file size is limited to 2GB. InnoDB is included by default in the MySQL binary distribution. Windows essentials installer makes InnoDB the default table for MySQL on windows.

InnoDB 给 MySQL 提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)、多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行级锁(locking on row level),提供与 Oracle 类似的不加锁读取(non-locking read in SELECTs)。InnoDB 锁定在行级并且也在 SELECT 语句提供一个 Oracle 风格一致的非锁定读。这些特色增加了多用户部署和性能。没有在 InnoDB 中扩大锁定的需要,因为在 InnoDB 中行级锁定适合非常小的空间。InnoDB 也支持 FOREIGN KEY 强制。在 SQL 查询中,你可以自由地将 InnoDB 类型的表与其它 MySQL 的表的类型混合起来,甚至在同一个查询中也可以混合。这些特性均提高了多用户并发操作的性能表现。在 InnoDB 表中不需要扩大锁定(lock escalation),因为 InnoDB 的行级锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

InnoDB provides MySQL with transaction safe (acid compliant) type tables with transaction, rollback, crash recovery capabilities, and multi version concurrency control. InnoDB provides locking on row level and non locking read in selections similar to Oracle. InnoDB is locked at the row level and also provides an Oracle style non locked read in the select statement. These features add to multi-user deployment and performance. There is no need to expand locking in InnoDB, because row level locking is suitable for very small space in InnoDB. InnoDB also supports foreign key enforcement. In SQL queries, you can freely mix tables of InnoDB type with other MySQL table types, even in the same query. These features improve the performance of multi-user concurrent operations. There is no need to expand the lock escalation in the InnoDB table, because the row level locks of InnoDB are suitable for very small space. InnoDB is the first table engine on MySQL to provide foreign key constraints.

在技术上,InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 把数据和索引存放在表空间里,可能包含多个文件,这与其它的不一样,举例来说,在 MyISAM 中,表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小,可也可以每个表使用各自独立的表空间,只需要启用选项 innodb_file_per_table。

In technology, InnoDB is a complete database system in MySQL background. InnoDB establishes its special buffer pool in main memory to cache data and index. InnoDB stores data and indexes in the table space, which may contain multiple files, which is different from others. For example, in MyISAM, tables are stored in separate files. The size of InnoDB table is only limited by the file size of the operating system. Each table can also use its own independent table space. Only the InnoDB file per table option needs to be enabled.

在 MySQL 的源代码中,从 3.23.34a 开始包含 InnoDB 表引擎,并在 MySQL -Max 的二进制版本中激活。

In the source code of MySQL, the InnoDB table engine is included from 3.23.34a and activated in the binary version of mysql-max.

性能技巧

Performance skills

1.如果 Unixtop 或 Windows 任务管理器(Task Manager)显示服务的 CPU 占用率小于 70%,(shows that the CPU usage percentage with your workload is less than 70 %,)你的系统瓶颈可能在磁盘读写上。或许你提交了大量的事务,或者是缓冲池(buffer pool)太小了。将缓冲池设大点会有所帮助,但一定要注意不能大于物理内存的 80%。

1. If unixtop or Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your system bottleneck may be on disk reading and writing. Maybe you commit a lot of transactions, or the buffer pool is too small. It helps to make the buffer pool larger, but be sure it is not larger than 80% of the physical memory.

2.在一个事务中包含几个修改。如果事务对数据库进行了修改,那么在这个事务提交时 InnoDB 必须刷新日志到磁盘上。因为硬盘的旋转速度通常至多为 167 转/秒,那么只要磁盘不欺骗操作系统,提交的事务数目限止也同样为 167 次/秒·用户。

2. Include several modifications in a transaction. If the transaction modifies the database, InnoDB must refresh the log to disk when the transaction is committed. Since the rotation speed of the hard disk is usually at most 167 rpm, as long as the disk does not cheat the operating system, the limit of the number of transactions submitted is also 167 times / sec · user.

3.如果掉失最近的几个事务无所谓的话,可以在 my.cnf 文件中将参数 innodb_flush_log_at_trx_commit 设置为 0。InnoDB 无论如何总是尝试一秒刷新(flush)一次日志,尽管刷新并不能得到保证。

3. If it doesn’t matter to lose the latest transactions, you can set the parameter InnoDB? Flush? Log? At? TRX? Commit to 0 in my.cnf file. InnoDB always tries to flush the logs once a second, regardless, although refreshing is not guaranteed.

4.将日志文件(log files)设大一点,使日志文件的总和正好与缓冲池(buffer pool)一样大。当 InnoDB 用光日志文件的空间时,它不得不在一个时间点上将缓冲池内修改过的内容写到磁盘上。 小的日志文件可能引起不必要的磁盘写操作。但是大的日志文件的缺点就是在数据恢复时将占用较长的时间。

4. Set the log files a little larger so that the sum of the log files is exactly as large as the buffer pool. When InnoDB uses up the space of log files, it has to write the modified contents of buffer pool to disk at a point in time. Small log files can cause unnecessary disk writes. But the disadvantage of large log files is that it will take a long time to recover data.

5.同样 log buffer 尽量设大点,比如说 8 MB。

5. Set the log buffer as large as possible, for example, 8 MB.

6.如果要存储变长的字符串或字段可能会包含大量的 NULLs,请使用 CHAR 型字段代替 VARCHAR。一个 CHAR(n)字段总是使用 n bytes 来存储数据,即使这个字符串很短或是一个 NULL 值。较小的表更加适合缓冲池同时能够减少磁盘 I/O 。

6. If storing a variable length string or field may contain a large number of nulls, use char type fields instead of varchar. A char (n) field always uses n bytes to store data, even if the string is short or a null value. Smaller tables are more suitable for buffer pools while reducing disk I / O.

7.(适合从 3.23.41 以上版本) 在某些版本的 Linux 和 Unixes 中,使用 Unixfsync 或其它类似的方法将文件刷新到磁盘是异常地慢的。InnoDB 默认的方法就是 fsync。如果你对数据库系统的磁盘写性能不能感到满意,你可以尝试在 my.cnf 中将 innodb_flush_method 设置为 O_DSYNC,尽管 O_DSYNC 选项在多数的系统上看起来比较慢。

7. (suitable for versions above 3.23.41) in some versions of Linux and UNIX, using UNIX sync or other similar methods to refresh files to disk is abnormally slow. The default method of InnoDB is fsync. If you are not satisfied with the disk write performance of the database system, you can try to set InnoDB? Flush? Method to o? Dsync in my.cnf, although the o? Dsync option looks slow on most systems.

8.在向 InnoDB 导入数据时,请确认 MySQL 没有打开 autocommit=1。否则每个插入语句都要将 log 刷新到磁盘。在你的 SQL 导入文件的第一行加入

8. When importing data to InnoDB, please make sure that MySQL does not open autocommit = 1. Otherwise, each insert statement will refresh the log to disk. Add on the first line of your SQL import file

set autocommit=0;并在最后一行加入 commit;

Set autocommit = 0; and add commit in the last line;

如果使用 mysqldump 选项–opt,你将会得到一个快速导入 InnoDB 表的转储(dump)文件,甚至可以不再使用上面所提的 set autocommit=0; … commit;。

If you use the mysqldump option – opt, you will get a dump file that can be quickly imported into the InnoDB table. You can even stop using the set autocommit = 0 Commit;

9.小心 insert 集全的大回滚(roolback):在插入时 InnoDB 使用插入缓冲来减少磁盘 I/O,但在相应的回滚中却没有使用这样的机制。一个 disk-bound rollback 可能会花费相应插入时间的 30 倍。如果发生一个失控的回滚,你可以查看第 6.1 章节的技巧来停止它。

9. Be careful about the large rollback of insert set: InnoDB uses insert buffer to reduce disk I / O when inserting, but it does not use such a mechanism in the corresponding rollback. A disk bound rollback can take up to 30 times the insertion time. If a runaway rollback occurs, you can stop it by looking at the tips in Section 6.1.

10.同样也要小心一个大的 disk-bound 的操作。使用 DROP TABLE 或 TRUNCATE(从 MySQL-4.0 以上) 来清空一个表,而不要使用 DELETE FROM yourtable。

10. Also be careful with a large disk bound operation. Use drop table or truncate (from mysql-4.0 or above) to empty a table instead of delete from yourtable.

11.如果需要插入大量记录行可以使用多行(multi-line)的 INSERT 来减少客户端与服务器端的通信开销:

11. If you need to insert a large number of record lines, you can use multi-line insert to reduce the communication overhead between the client and the server:

INSERT INTO yourtable VALUES (1, 2), (5, 5);

INSERT INTO yourtable VALUES (1, 2), (5, 5);

这个技巧对插入任何表均有效,而不仅仅是 InnoDB。

This technique works for inserting any table, not just InnoDB.

12.如果在辅键上有 UNIQUE 约束,从 3.23.52 和 4.0.3 开始,可以通过在一个导入会话中将唯一键检查(uniqueness check)关闭来提高数据导入速度:

12. If there is a unique constraint on the secondary key, starting from 3.23.52 and 4.0.3, you can speed up data import by turning off the unique check in an import session:

SET UNIQUE_CHECKS=0;一个大的表导入这将减少大量的磁盘 I/O,因为这时 InnoDB 可能使用自身的插入缓冲来分批地记录辅助索引。

Set unique_checks = 0; a large table import will reduce a lot of disk I / O, because InnoDB may use its own insert buffer to record auxiliary indexes in batches.

13.如果在表中有一个子 FOREIGN KEY 约束,从 3.23.52 和 4.0.3 开始,可以通过在一个导入会话中将外键检查(foreign key check)关闭来提高数据导入速度:

13. If there is a foreign key constraint in the table, starting from 3.23.52 and 4.0.3, you can speed up data import by turning off foreign key check in an import session:

SET FOREIGN_KEY_CHECKS=0;

SET FOREIGN_KEY_CHECKS=0;

对一个大的表导入这将减少大量的磁盘 I/O。

Importing a large table will reduce a lot of disk I / O.

注意事项

Matters needing attention

输出信息的某些注意点:

Some notes on output information:

如果 TRANSACTIONS 部分报告锁定等待(lock waits),那么你的应用程序可能有锁争用(lock contention)。输出信息可以帮助跟踪事务死锁的原因。

If the transactions section reports lock waits, your application may have lock contention. Output information can help trace the cause of a transaction deadlock.

SEMAPHORES 部分报告线程等待信号量以及统计出线程需要旋转(spin)或等待(wait)一个互斥(mutex)或 rw-lock 信号量的次数。一个较大的线程等待信号量的次数可能是由于磁盘 I/O 引起,或 InnoDB 内部的争用问题(contention problems)。争用(Contention)可能是由于比较繁重的并发性查询,或操作系统的线程调度的问题。 在这种情形下,可将 innodb_thread_concurrency 设置地小于默认的 8 。

The semaphores section reports thread wait semaphores and counts the number of times a thread needs to spin or wait for a mutex or RW lock semaphore. The number of times a large thread waits for semaphores may be caused by disk I / O, or the internal contention problems of InnoDB. Contention may be caused by heavy concurrent queries, or by the scheduling of threads in the operating system. In this case, InnoDB? Thread? Concurrency can be set to be less than the default 8.

FILE I/O 部分列出了文件 I/O 的等待请求。过大的值就意味着磁盘 I/O 瓶颈。

The file I / O section lists waiting requests for file I / O. Too large a value means a disk I / O bottleneck.

BUFFER POOL AND MEMORY 部分给出了页面读写的统计。通过这些值可以计算出你的查询通常所需的数据文件 I/O 量。

The buffer pool and memory section gives the statistics of page reading and writing. With these values, you can calculate the amount of data file I / O that your queries usually require.


速搜资源网 , 版权所有丨如未注明 , 均为原创丨转载请注明原文链接:【速搜问答】InnoDB是什么
喜欢 (0)
[361009623@qq.com]
分享 (0)
发表我的评论
取消评论
表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址