`

mysql优化(InnoDB)

阅读更多
转自:http://www.freelamp.com/1015398272/index_html
http://www.freelamp.com/1015470737/index_html
InnoDB 介绍(一)
InnoDB 是 MySQL 上第一个提供外键约束的引擎,除了提供事务处理外,InnoDB 还支持行锁,提供和 Oracle 一样的一致性的不加锁读取,能增加并发读的用户数量并提高性能,不会增加锁的数量。
InnoDB 的设计目标是处理大容量数据时最大化性能,它的 CPU 利用率是其他所有基于磁盘的关系数据库引擎中最有效率的。

InnoDB 是一套放在 MySQL 后台的完整数据库系统,InnoDB 有它自己的缓冲池,能缓冲数据和索引,InnoDB 还把数据和索引存放在表空间里面,可能包含好几个文件,这和 MyISAM 表完全不同,在 MyISAM 中,表被存放在单独的文件中,InnoDB 表的大小只受限于操作系统文件的大小,一般为 2GB。

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

如果你使用源码版本下载,自己编译的话,需要采用 --with-innodb 选项:

./configure --with-innodb

在 MySQL-Max-3.23 上使用 InnoDB 时,必须在 my.cnf 或者 my.ini 文件中的 [mysqld] 小节指定启动参数:

innodb_data_file_path=ibdata:30M

InnoDB 以 GNU GPL 版本 2 的许可发布。

MySQL-Max-3.23: 这个产品可以用于生产环境使用。
MySQL-4.0: 这个是开发版本,和 3.23 相比增加了多表删除,查询结果缓冲,SSL 通信,4.0.1 为 Beta 版本。

在 3.23 中,必须至少指定 innodb_data_file_path 的大小,但是在 MySQL-4.0 中这个值默认为 64MB,文件名为 ibdata1。

为了得到好的性能,必须指定 InnoDB 参数,例如:
对于一台 128MB 内存, 10GB 硬盘的 Windows NT 机器的配置如下:

[mysqld]

innodb_data_home_dir = c:\ibdata
# 数据文件必须能容下数据和索引
innodb_data_file_path = ibdata1:2000M;ibdata2:2000M
# 设置缓冲池大小为内存的 50 - 80 %
set-variable = innodb_buffer_pool_size=70M
set-variable = innodb_additional_mem_pool_size=10M
innodb_log_group_home_dir = c:\iblogs
# log_arch_dir 必须和 log_group_home_dir 一样
innodb_log_arch_dir = c:\iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
# 设置日志文件大小为缓冲池的 15%。
set-variable = innodb_log_file_size=10M
set-variable = innodb_log_buffer_size=8M
# 如果丢失最近的几个事务影响不大的话,设置 flush_log_at_trx_commit 为 0。
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

注意 InnoDB 不会自己建立目录,你必须使用操作系统命令建立目录,并注意 MySQL 的权限。

第一次运行 MySQL 时,建议使用命令行方式。在 Windows 下,用命令行开启:

C:\MYSQL>mysqld-max --standalone --console

在 Windows 下设置配置文件的原则是:
my.cnf 和 my.ini 只能是一个有效。
my.cnf 放在 C: 根目录下, my.ini 文件放在 WINDIR 目录下,C:\WINDOWS 或者 C:\WINNT。

在 Linux 下面,初始配置文件按下面的顺序搜索:

/etc/my.cnf 全局选项
COMPILATION_DATADIR/my.cnf 服务器范围的选项
defaults-extra-file 采用 --defaults-extra-file=.... 的文件
~/.my.cnf 用户指定的选项

假设你有一台 512MB 内存,三台 20GB 硬盘(路径分别为:"/","/dr2","/dr3“)的服务器,设置的例子为:

[mysqld]
innodb_data_home_dir = /
innodb_data_file_path = ibdata/ibdata1:2000M;dr2/ibdata/ibdata2:2000M
set-variable = innodb_buffer_pool_size=350M
set-variable = innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
innodb_log_arch_dir = /dr3/iblogs
innodb_log_archive=0
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
#innodb_flush_method=fdatasync
#innodb_fast_shutdown=1
#set-variable = innodb_thread_concurrency=5

为了提升性能,我们把日志文件和数据文件放在不同的磁盘,另外还可以采用裸的磁盘分区来存储数据以提升性能。

需要提醒的是,在 Linux x86 上不要把内存设置太高,glibc 会把进程堆增长到线程堆栈之上,能把服务器给搞垮,下面的值接近于 2GB 时就很危险:

innodb_buffer_pool_size + key_buffer +
max_connections * (sort_buffer + record_buffer) + max_connections * 2 MB

每个线程将使用 2MB(二进制版本为 256 KB) 的堆栈,在最坏的情况下,还会使用 sort_buffer + record_buffer 的附加内存。

后续的文章,我们还将介绍 InnoDB 表的建立,MyISAM 到 InnoDB 的转换,外键约束的使用,自动增长列的操作,InnoDB 数据库的备份和恢复,事务的使用,性能调优等内容。

请继续关注,FreeLAMP.com 为您提供的企业级的开放源码数据库解决方案。

InnoDB 介绍(二)
上一节我们简要介绍了 InnoDB 及其基本的配置参数,本文要讲述的是性能调优的一些参数配置以及安全,备份恢复等。MySQL 要进入企业级的运作,对于一个 DBA 而言,这些是必备的知识。因此,本文将详细叙述性能调优方面的知识。

性能优化的一些主要参数:

set-variable = max_connections=200
set-variable = record_buffer=1M
set-variable = sort_buffer=1M
# 如果为 MyISAM 表的话,设置 key_buffer 为内存的 5 - 50 % ,
# 但是对于 InnoDB 来说,必须保证 key_buffer+ InnoDB 缓冲池小于内存的 80%。
set-variable = key_buffer=...

另外值得注意的几个参数是:

innodb_file_io_threads 文件 i/o 的线程数,一般为 4,但是在 Windows 下,可以设置得较大。

innodb_fast_shutdown InnoDB 缺省在关闭之前清理缓冲,这个过程可能需要几分钟,在极端的情况下可能需要几个小时,如果这个参数设置为 1 的话,InnoDB 就略过这个过程,从 3.23.50 开始这个参数的缺省值为 1 。

innodb_thread_concurrency InnoDB 努力把操作系统线程保留下来,缺省的值是 8,如果你的性能比较低, innodb_monitor 显示很多线程等待信号,应该把这个值设置得小一些,如果你的系统有很多处理器和磁盘则可以调高这个值。建议的值为处理器数目加上磁盘数。

配置好 my.cnf 或者 my.ini 的参数之后,就可以启动 MySQL 建立 InnoDB 了,第一次建立之前,建议你手工启动,这样你就可以看到 InnoDB 的建立过程:
heikki@donna:~/mysql-3.23.48/sql> mysqld
020204 23:17:12 InnoDB: The first specified data file /dr2/tmp/heikki/data/ibdata1
did not exist:
InnoDB: a new database to be created!
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata1 size to 20 MB
InnoDB: Database physically writes the file full: wait...
020204 23:17:16 InnoDB: Data file /dr2/tmp/heikki/data/ibdata2 did not exist: new
to be created
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata2 size to 200 MB
InnoDB: Database physically writes the file full: wait...
020204 23:17:41 InnoDB: Data file /dr2/tmp/heikki/data/ibdata3 did not exist: new
to be created
InnoDB: Setting file /dr2/tmp/heikki/data/ibdata3 size to 1000 MB
InnoDB: Database physically writes the file full: wait...
020204 23:21:37 InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 10 MB
InnoDB: Database physically writes the file full: wait...
020204 23:21:39 InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
020204 23:21:41 InnoDB: Log file ./ib_logfile2 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile2 size to 10 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
020204 23:21:45 InnoDB: Started
mysqld: ready for connections

如果采用 mysqladmin shutdown 命令关闭时,显示:

020204 23:34:45 mysqld: Normal shutdown

020204 23:34:45 InnoDB: Starting shutdown...
020204 23:34:47 InnoDB: Shutdown completed
020204 23:34:47 mysqld: Shutdown Complete

进入日志文件所在的目录,我们可以看到 ib_arch_log_0000000000 这样的文件。

下面我们来建立一个 InnoDB 表,假设我们用 mysql test 进入:

CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A)) TYPE = InnoDB;

查看状态:
SHOW TABLE STATUS FROM test LIKE 'CUSTOMER'

需要注意的是,不要手工删除 InnoDB 数据库目录下的 .frm 文件,而是采用 create table 和 drop table 的方法。

把 MyISAM 转化为 InnoDB

*注意* MySQL 的系统表, user 或者 host 等不能转化为 InnoDB 格式,他们必须采用 MyISAM 格式!!

如果你想以后建立的所有表都为 InnoDB 格式,在配置文件中加入:
default-table-type=innodb

InnoDB 没有一个特殊的索引建立优化机制,因此不能采取 export/import ,然后建立索引的办法来转化,最快的办法是把表类型改变为 InnoDB ,然后直接插入数据:

ALTER TABLE ... TYPE=INNODB 或者建立一个空的具有同样结构的 InnoDB 表,然后插入数据:
INSERT INTO ... SELECT * FROM ....

为了更好的控制数据插入进程,最好把很大的表分批插入:

INSERT INTO newtable SELECT * FROM oldtable WHERE yourkey > something
AND yourkey <= somethingelse;

等数据全部插入完毕后,可以重新命名表名。

在插入过程中,要把 InnoDB 缓冲池设置得大点,以减少磁盘 I/O。同时还应该把日志文件和日志缓冲加大。

需要注意的是,不要让表空间用尽,如果 ALTER TABLE 用完了表空间,将会回滚,如果磁盘不够的话,这个过程会持续几个小时。


外键约束

从 MySQL 3.23.43b 开始, InnoDB 就支持外键约束,作为数据引用完整性的重要特色,InnoDB 在外键支持上开创了 MySQL 各种表类型的先河。

建立外键的方法:

例子:

CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)) TYPE=INNODB;

两表都必须为 InnoDB 类型,外键和被引用的键,必须是索引中的一个列,InnoDB 不会自动为外键建立索引,必须明确指明。

如果 CREATE TABLE 语句发生 1005 号错误,错误信息字符串引用 150 号错误,那么就是外键约束起了作用。

只有在父行中包含 NULL 的列,才允许在子行中包含 NULL。

目前任何 ALTER TABLE 操作都会删除外键约束,因此,建议采用 DROP TABLE 然后 CREATE TABLE 来修改 schema。因为 ALTER TABLE 在内部使用 RENAME TABLE 操作,这样就能混淆外键的引用,同样的 CREATE INDEX 也是作为 ALTER TABLE 来处理的,也不能用于外键约束的表。

InnoDB 允许你 drop 任何表,即使这样会打破外键,这样操作的结果就是约束也被 drop 了。

查看表 T 中的外键约束状态:

SHOW TABLE STATUS FROM yourdatabasename LIKE 'T'

InnoDB 还不支持 CASCADE ON DELETE 或者其特殊的约束选项。(我们知道 Oracle 里面有这个东东了)

处理自动增量的列的时候需要注意的问题是:自动增量的列不参加锁机制和事务处理,因此回滚时,会造成序列中间的空档。

增加 InnoDB 数据和日志文件
不能增大 InnoDB 数据文件的长度,增加表空间的办法是新添加数据文件:
innodb_data_file_path,然后重新启动 MySQL 。

同样的,目前你还不能删除数据文件,为了减小数据文件的长度,需要导出数据,建立新数据库,然后再导入。

如果需要改变日志文件的大小,可以先关闭 MySQL ,然后把旧的日志文件移动到一个备份目录,修改 my.cnf ,重新启动 MySQL ,确信 InnoDB 正常启动后,可以把旧的日志文件删除了。


InnoDB 数据库的备份:
关闭 MySQL ,并确保正常关闭。
复制所有的数据文件。
复制所有的 InnoDB 日志文件。
复制 my.cnf 配置文件。
复制所有 InnoDB 表的 .frm 文件。

目前还没有热备份或者增量备份的工具。对于要求高可靠的服务器而言,可以采用复制的形式。
数据恢复的过程,就是把前面备份的这些文件重新考回原来的位置。这种情况只有在数据文件损坏的情况下,才有必要。一般的数据库瘫痪,重新启动 MySQL ,InnoDB 会恢复事务。

如果确实有表空间不能回复的话,可以采用 SELECT INTO OUTFILE 来恢复数据。这个时候需要在 my.cnf 中设置:
set-variable = innodb_force_recovery = 4

InnoDB 的数据文件在所有平台上二进制兼容,因此只要只要两台机器的浮点数格式一致,你就可以在这两台机器之间移动文件。

锁机制的叙述和解释比较繁杂,这里跳过原文第八章( http://www.innodb.com/ibman.html )关于锁机制的描述,我们直接讲述性能调优的几个提示:

1. 如果 CPU 资源利用率小于 70% 的话,你的负载可能是由于磁盘引起的,可能有太多的事务提交,或者缓冲池太小。建议增加缓冲池。

2. 把多个修改包在一个事务里面, InnoDB 每次提交事务需要刷新日志到磁盘,而磁盘的速度和 CPU 相比一般都很慢,因此,建议不要频繁提交事务。

3. 如果你能承受最近几个事务的丢失,建议设置 innodb_flush_log_at_trx_commit 为 0 ,InnoDB 每秒刷新一次日志。

4. 加大日志文件,组合起来的所有日志文件大小应该和缓冲池一样大,很小的日志文件将会导致很多不必要的磁盘写操作,大日志文件的缺陷是恢复时间会加长。

5. 同样需要加大日志缓冲,例如 8 MB。

6. 采用 VARCHAR 类,而不是 CHAR 类型,小的表更适合缓冲池,从而较少了磁盘 I/O。
7. 在 Linux 和一些 Unix 系统上,采用 Unix fsync 命令刷新文件,但是有些情况下,可能会很慢,可以修改 innodb_flush_method 为 O_DSYNC 来提高写性能。

8. 导入数据时,要确保 autocommit=1 没有打开,在导入的 SQL 文件最前面加入:

set autocommit=0;
commit;
如果采用 mysqldump --opt 选项,你得到的 dump 文件能快速导入 InnoDB 表。

9. 留意大批量插入时的回滚操作。
10. 留意那些消耗磁盘资源的操作,采用 DROP TABLE 或者 TRUNCATE 来清空表,而不是DELETE FROM 。.

11.如果要插入多行时, 使用多行 INSERT ,来减轻客户端和服务器端的通信量。

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

上面的多行(原文为 multi-line,我认为应该为 multi-row)。这条提示不仅仅适用于 InnoDB ,对于其他表类型也适用。

从 3.23.42 开始,InnoDB 包括了 InnoDB 监视器,能输出InnoDB 内部状态的信息,这些数据在性能调优时十分有用,当打开时,监视器能每15秒在服务器端输出数据到标准输出,如果不是从命令行启动的话,这些输出会写入到 .err 文件中,在 Windows 平台上,必须在 DOS 命令行下以 --standalone --console 参数启动。

主要信息包括:

每个激活的事务锁住的表以及记录
事务的锁等待
线程的信号等待
延迟的文件 i/o 请求
缓冲池统计以及删除和插入缓冲合并的活动

启动的命令为:

CREATE TABLE innodb_monitor(a int) type = innodb;

停止的语法是:
DROP TABLE innodb_monitor;

如果数据库关闭时,监视器还在运行,下次启动监视器前必须先关闭。

用同样的语法你可以启动 innodb_lock_monitor 以及 innodb_tablespace_monitor

除了锁机制以外,本文没有讲述的内容还有表和索引的结构,记录的物理结构,文件空间的管理,错误处理,InnoDB 表的限制等,请继续关注本站的后续报道。

在后面的报道中,您将会了解到 InnoDB 的一些物理设计仿照了 Oracle ,因此,能够获得和 Oracle 可以类比的性能并不足以为奇。
分享到:
评论

相关推荐

    MYSQL优化innodb干货讲解PPT教案学习.pptx

    MYSQL优化innodb干货讲解PPT教案学习.pptx

    MySQL内核:InnoDB存储引擎 卷1.pdf.zip

    深入学习《MySQL内核:InnoDB存储引擎 卷1》,读者可以了解到InnoDB的内部工作机制,如如何处理B+树索引、事务的提交与回滚、锁的实现以及内存管理等内容,这对于优化数据库性能、解决并发问题、设计高效的数据模型...

    MySQL Innodb 索引原理详解

    ### MySQL Innodb 索引原理详解 #### 1. 各种树形结构 在深入探讨MySQL Innodb索引之前,我们先了解几种基本的树形数据结构,包括二叉搜索树、B树、B+树以及B*树。...对于理解和优化MySQL数据库的性能至关重要。

    MySQL 和 InnoDB 性能

    ### MySQL与InnoDB性能分析 #### MySQL架构概览 MySQL是一种关系型数据库管理系统,由瑞典MySQL AB公司开发,目前由Oracle公司维护。MySQL的核心组成部分包括服务器端、存储引擎以及一系列支持服务。 - **服务器...

    MySQL InnoDB 查询优化实现分析

    MySQL InnoDB 查询优化实现分析,欢迎下载,1111111111111

    mysql 5.6 新特性-innodb

    ### MySQL 5.6 新特性-InnoDB:深入剖析 #### MySQL 5.6 版本简介及重要性 MySQL 5.6 是 MySQL 的一个重要版本,相比于之前的版本(如 MySQL 5.1 和 5.5),它在多个方面进行了显著的改进。这些改进不仅提升了...

    何登成 - 管中窥豹——MySQL(InnoDB)死锁分析之道

    在实际中,解决MySQL(InnoDB)的死锁问题需要深入了解死锁产生的原因、锁的类型以及加锁策略,进而对业务逻辑进行优化或调整数据库配置和代码逻辑以减少死锁的可能性。 ### 个人简介 何登成作为阿里巴巴DBA团队的...

    MySQL+innodb+8.0+新特性.pdf

    在 MySQL 8.0 中,InnoDB 存储引擎引入了许多新的性能优化特性,例如并行查询、索引优化等。这些特性可以大大提高数据库的性能和响应速度。 MySQL 8.0 InnoDB 的新特性和改进可以提高数据库的性能、灵活性和安全性...

    MySQL优化之InnoDB优化

    总而言之,MySQL的InnoDB优化是一个多维度的过程,涉及到缓存、索引、事务、日志等多个层面。通过对这些核心组件的调优,可以显著提高MySQL在大数据量下的处理能力和响应速度,从而满足高并发、高性能的应用需求。在...

    MySQL性能优化InnoDB buffer pool flush策略

    MySQL 性能优化 InnoDB buffer pool flush 策略 MySQL 中 InnoDB 存储引擎使用 buffer pool 来缓存从磁盘读取到内存的数据页。Buffer pool 通常由数个内存块加上一组控制结构体对象组成。内存块的个数取决于 buffer...

    MySQL技术InnoDB存储引擎_姜承尧_第2版

    《MySQL技术内幕_InnoDB存储引擎_姜承尧_第2版》是一本深入探讨MySQL数据库中InnoDB存储引擎的专业书籍。作者姜承尧是数据库领域的知名专家,他的这部著作详细阐述了InnoDB引擎的核心概念、设计原理以及实际操作技巧...

    A001-MySQL安装、MySQL优化、innodb 锁问题监控处理、MySQL binlog日志、MySQL 基础

    A002-MySQL优化 A003-innodb 锁问题监控处理 A004-MySQL binlog日志 A005-MySQL 基础 A006-SQL语法 A007-MySQL 修改密码 特点和功能: 开源性: MySQL是开源软件,可以免费获取和使用。它的开放性质使得它...

    MySQL体系结构及原理(innodb)图文完美解析

    ### MySQL体系结构及原理(innodb)图文完美解析 #### 宏观认识 在深入探讨MySQL的体系结构及其核心组件InnoDB之前,我们先来理解几个基础概念。 1. **MySQL简介** MySQL是一种开源的关系型数据库管理系统(RDBMS)...

    MySQL5.6版InnoDB引擎深入剖析

    MySQL5.6版InnoDB深度介绍。

    mysql优化笔记+资料

    以下是一份详细的MySQL优化笔记,涵盖了多个方面: 一、查询优化 1. 使用索引:为经常用于搜索的列创建索引可以显著加快查询速度。B树和哈希索引是最常见的类型,适用于不同的查询场景。 2. 避免全表扫描:尽量使用...

    MySQL技术内幕 InnoDB存储引擎.pptx

    本书还详细介绍了InnoDB的内部存储格式、物理存储结构、日志系统、事务处理、恢复机制等高级话题,以及如何优化InnoDB的性能,从而更好地利用MySQL进行数据库操作。 本书适合于MySQL DBA和开发者阅读,也适合于对...

    MYSQL-innodb性能优化学习总结.pdf

    MySQL-InnoDB性能优化是数据库管理中的重要环节,它直接影响到系统的响应速度、并发处理能力和数据安全性。以下是对一些关键参数的详细解读: 1. **innodb_buffer_pool_size**:这是InnoDB存储引擎缓存数据和索引的...

    MySQL Innodb 参数详解与优化实践

    ### MySQL Innodb 参数详解与优化实践 #### 一、引言 MySQL作为一款广泛使用的开源关系型数据库管理系统,其InnoDB存储引擎因其高可靠性和事务处理能力而备受青睐。为了充分发挥InnoDB的优势并针对特定场景进行性能...

Global site tag (gtag.js) - Google Analytics