56,678
社区成员
发帖
与我相关
我的任务
分享Mysql大表维护问题
1、mysql有几种存储引擎 比如MyISAM、InnoDB等
创建表时定义这几种存储引擎对数据和表有什么影响？对表的大小(比如表的行数方面）有什么限制吗？
2、对于mysql中大表如何维护？进行表分区吗？如何操作？（表可能有上亿条数据）
3、对于mysql方面如何部署才能达到最大优化？
希望能有专家的回答。最好详细一点。不胜感谢。也可以联系我的QQ：231559723
非常感谢！
创建表时定义这几种存储引擎对数据和表有什么影响？对表的大小(比如表的行数方面）有什么限制吗？
2、对于mysql中大表如何维护？进行表分区吗？如何操作？（表可能有上亿条数据）
3、对于mysql方面如何部署才能达到最大优化？
希望能有专家的回答。最好详细一点。不胜感谢。也可以联系我的QQ：231559723
非常感谢！
...全文
请发表友善的回复…
发表回复
paula2008 2010-07-08
- 打赏
- 举报
学习官方文档
小小小小周 2010-06-10
- 打赏
- 举报
[Quote=引用 3 楼 yananguo_1985 的回复:]
还有个问题请教下：如果对于一个表有批量的insert或者删除、更新等操作，如果才能达到最佳性能。
可能这些操作只有一种类型（insert、delete、update）在存储过程中。
[/Quote]
还有个问题请教下：如果对于一个表有批量的insert或者删除、更新等操作，如果才能达到最佳性能。
可能这些操作只有一种类型（insert、delete、update）在存储过程中。
[/Quote]
alter table table_name disable keys
ACMAIN_CHM 2010-06-08
- 打赏
- 举报
如果对于一个表有批量的insert或者删除、更新等操作，如果才能达到最佳性能。
使用 LOAD DATA INFILE
[Quote]7.2.16. INSERT语句的速度
插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1x记录大小）
插入索引：（1x索引）
关闭：(1)
这不考虑打开表的初始开销，每个并发运行的查询打开。
表的大小以logN (B树)的速度减慢索引的插入。
加快插入的一些方法：
· 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。参见5.3.3节，“服务器系统变量”。
· 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。参见13.2.4节，“INSERT语法”。
· 用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。
· 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节，“LOAD DATA INFILE语法”。
· 当表有很多索引时，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程：
有选择地用CREATE TABLE创建表。
执行FLUSH TABLES语句或命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
用LOAD DATA INFILE把数据插入到表中，因为不更新任何索引，因此很快。
如果只想在以后读取表，使用myisampack压缩它。参见15.1.3.3节，“压缩表特性”。
用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
执行FLUSH TABLES语句或mysqladmin flush-tables命令。
请注意如果插入一个空MyISAM表，LOAD DATA INFILE也可以执行前面的优化；主要不同处是可以让myisamchk为创建索引分配更多的临时内存，比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。
也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name，使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用这种方式，还可以跳过FLUSH TABLES。
· 锁定表可以加速用多个语句执行的INSERT操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。
对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。
锁定也将降低多连接测试的整体时间，尽管因为它们等候锁定最大等待时间将上升。例如：
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用锁定，2、3和4将在1和5前完成。如果使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的，通过为在一行中多于大约5次连续不断地插入或更新的操作加锁，可以获得更好的整体性能。如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。
INSERT装载数据比LOAD DATA INFILE要慢得多，即使是使用上述的策略。
· 为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通过增加key_buffer_size系统变量来扩大 键高速缓冲区。参见7.5.2节，“调节服务器参数”。
[/Quote]
使用 LOAD DATA INFILE
[Quote]7.2.16. INSERT语句的速度
插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：
连接：(3)
发送查询给服务器：(2)
分析查询：(2)
插入记录：（1x记录大小）
插入索引：（1x索引）
关闭：(1)
这不考虑打开表的初始开销，每个并发运行的查询打开。
表的大小以logN (B树)的速度减慢索引的插入。
加快插入的一些方法：
· 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节bulk_insert_buffer_size变量，使数据插入更快。参见5.3.3节，“服务器系统变量”。
· 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。参见13.2.4节，“INSERT语法”。
· 用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。
· 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节，“LOAD DATA INFILE语法”。
· 当表有很多索引时，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程：
有选择地用CREATE TABLE创建表。
执行FLUSH TABLES语句或命令mysqladmin flush-tables。
使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
用LOAD DATA INFILE把数据插入到表中，因为不更新任何索引，因此很快。
如果只想在以后读取表，使用myisampack压缩它。参见15.1.3.3节，“压缩表特性”。
用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
执行FLUSH TABLES语句或mysqladmin flush-tables命令。
请注意如果插入一个空MyISAM表，LOAD DATA INFILE也可以执行前面的优化；主要不同处是可以让myisamchk为创建索引分配更多的临时内存，比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。
也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name，使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用这种方式，还可以跳过FLUSH TABLES。
· 锁定表可以加速用多个语句执行的INSERT操作：
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
UNLOCK TABLES;
这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。
对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。
锁定也将降低多连接测试的整体时间，尽管因为它们等候锁定最大等待时间将上升。例如：
Connection 1 does 1000 inserts
Connections 2, 3, and 4 do 1 insert
Connection 5 does 1000 inserts
如果不使用锁定，2、3和4将在1和5前完成。如果使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。
INSERT、UPDATE和DELETE操作在MySQL中是很快的，通过为在一行中多于大约5次连续不断地插入或更新的操作加锁，可以获得更好的整体性能。如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。
INSERT装载数据比LOAD DATA INFILE要慢得多，即使是使用上述的策略。
· 为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通过增加key_buffer_size系统变量来扩大 键高速缓冲区。参见7.5.2节，“调节服务器参数”。
[/Quote]
ACMAIN_CHM 2010-06-08
- 打赏
- 举报
[Quote]能对于分区详细点吗？最好有个例子。[/Quote]
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
18. 分区
18.1. MySQL中的分区概述
18.2. 分区类型
18.2.1. RANGE分区
18.2.2. LIST分区
18.2.3. HASH分区
18.2.4. KEY分区
18.2.5. 子分区
18.2.6. MySQL分区处理NULL值的方式
18.3. 分区管理
18.3.1. RANGE和LIST分区的管理
18.3.2. HASH和KEY分区的管理
18.3.3. 分区维护
18.3.4. 获取关于分区的信息
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
18. 分区
18.1. MySQL中的分区概述
18.2. 分区类型
18.2.1. RANGE分区
18.2.2. LIST分区
18.2.3. HASH分区
18.2.4. KEY分区
18.2.5. 子分区
18.2.6. MySQL分区处理NULL值的方式
18.3. 分区管理
18.3.1. RANGE和LIST分区的管理
18.3.2. HASH和KEY分区的管理
18.3.3. 分区维护
18.3.4. 获取关于分区的信息
rucypli 2010-06-08
- 打赏
- 举报
分区
http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
http://dev.mysql.com/doc/refman/5.1/zh/partitioning.html
yananguo_1985 2010-06-07
- 打赏
- 举报
能对于分区详细点吗？最好有个例子。
还有个问题请教下：如果对于一个表有批量的insert或者删除、更新等操作，如果才能达到最佳性能。
可能这些操作只有一种类型（insert、delete、update）在存储过程中。
还有个问题请教下：如果对于一个表有批量的insert或者删除、更新等操作，如果才能达到最佳性能。
可能这些操作只有一种类型（insert、delete、update）在存储过程中。
ACMAIN_CHM 2010-06-07
- 打赏
- 举报
关于各种引擎的限制，文档中均有描述。
[Quote]如下是MyISAM存储引擎的一些特征：
· 所有数据值先存储低字节。这使得数据机和操作系统分离。二进制轻便性的唯一要求是机器使用补码（如最近20年的机器有的一样）和IEEE浮点格式（在主流机器中也完全是主导的）。唯一不支持二进制兼容性的机器是嵌入式系统。这些系统有时使用特殊的处理器。
先存储数据低字节并不严重地影响速度；数据行中的字节一般是未联合的，从一个方向读未联合的字节并不比从反向读更占用更多的资源。服务器上的获取列值的代码与其它代码相比并不显得时间紧。
· 大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。
· 当把删除和更新及插入混合的时候，动态尺寸的行更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。
· 每个MyISAM表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。
· 最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的的键块被用上。
· BLOB和TEXT列可以被索引。
· NULL值被允许在索引的列中。这个占每个键的0-1个字节。
· 所有数字键值以高字节为先被存储以允许一个更高地索引压缩。
· 当记录以排好序的顺序插入（就像你使用一个AUTO_INCREMENT列之时），索引树被劈开以便高节点仅包含一个键。这改善了索引树的空间利用率。
· 每表一个AUTO_INCREMEN列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。（当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置。
· 如果数据文件中间的表没有自由块了，在其它线程从表读的同时，你可以INSERT新行到表中。（这被认识为并发操作）。自由块的出现是作为删除行的结果，或者是用比当前内容多的数据对动态长度行更新的结果。当所有自由块被用完（填满），未来的插入又变成并发。
· 你可以把数据文件和索引文件放在不同目录，用DATA DIRECTORY和INDEX DIRECTORY选项CREATE TABLE以获得更高的速度，请参阅13.1.5节，“CREATE TABLE语法”。
· 每个字符列可以又不同的字符集，请参阅第10章：“字符集支持”。
· 在MyISAM索引文件里又一个标志，它表明表是否被正确关闭。如果用--myisam-recover选项启动mysqld，MyISAM表在打开得时候被自动检查，如果被表被不恰当地关闭，就修复表。
· 如果你用--update-state选项运行myisamchk，它标注表为已检查。myisamchk --fast只检查那些没有这个标志的表。
· myisamchk --analyze为部分键存储统计信息，也为整个键存储统计信息。
· myisampack可以打包BLOB和VARCHAR列。
MyISAM也支持下列特征：
· 支持true VARCHAR类型；VARCHAR列以存储在2个字节中的长度来开始。
· 有VARCHAR的表可以有固定或动态记录长度。
· VARCHAR和CHAR列可以多达64KB。
· 一个被搞乱的已计算索引对可对UNIQUE来使用。这允许你在表内任何列的合并上有UNIQUE。（尽管如此，你不能在一个UNIQUE已计算索引上搜索）。
对MyISAM存储引擎，有一个更详细的论坛在http://forums.mysql.com/list.php?21。
[/Quote]
[Quote]如下是MyISAM存储引擎的一些特征：
· 所有数据值先存储低字节。这使得数据机和操作系统分离。二进制轻便性的唯一要求是机器使用补码（如最近20年的机器有的一样）和IEEE浮点格式（在主流机器中也完全是主导的）。唯一不支持二进制兼容性的机器是嵌入式系统。这些系统有时使用特殊的处理器。
先存储数据低字节并不严重地影响速度；数据行中的字节一般是未联合的，从一个方向读未联合的字节并不比从反向读更占用更多的资源。服务器上的获取列值的代码与其它代码相比并不显得时间紧。
· 大文件（达63位文件长度）在支持大文件的文件系统和操作系统上被支持。
· 当把删除和更新及插入混合的时候，动态尺寸的行更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块来自动完成。
· 每个MyISAM表最大索引数是64。 这可以通过重新编译来改变。每个索引最大的列数是16个。
· 最大的键长度是1000字节。这也可以通过编译来改变。对于键长度超过250字节的情况，一个超过1024字节的的键块被用上。
· BLOB和TEXT列可以被索引。
· NULL值被允许在索引的列中。这个占每个键的0-1个字节。
· 所有数字键值以高字节为先被存储以允许一个更高地索引压缩。
· 当记录以排好序的顺序插入（就像你使用一个AUTO_INCREMENT列之时），索引树被劈开以便高节点仅包含一个键。这改善了索引树的空间利用率。
· 每表一个AUTO_INCREMEN列的内部处理。MyISAM为INSERT和UPDATE操作自动更新这一列。这使得AUTO_INCREMENT列更快（至少10%）。在序列顶的值被删除之后就不能再利用。（当AUTO_INCREMENT列被定义为多列索引的最后一列，可以出现重使用从序列顶部删除的值的情况）。AUTO_INCREMENT值可用ALTER TABLE或myisamch来重置。
· 如果数据文件中间的表没有自由块了，在其它线程从表读的同时，你可以INSERT新行到表中。（这被认识为并发操作）。自由块的出现是作为删除行的结果，或者是用比当前内容多的数据对动态长度行更新的结果。当所有自由块被用完（填满），未来的插入又变成并发。
· 你可以把数据文件和索引文件放在不同目录，用DATA DIRECTORY和INDEX DIRECTORY选项CREATE TABLE以获得更高的速度，请参阅13.1.5节，“CREATE TABLE语法”。
· 每个字符列可以又不同的字符集，请参阅第10章：“字符集支持”。
· 在MyISAM索引文件里又一个标志，它表明表是否被正确关闭。如果用--myisam-recover选项启动mysqld，MyISAM表在打开得时候被自动检查，如果被表被不恰当地关闭，就修复表。
· 如果你用--update-state选项运行myisamchk，它标注表为已检查。myisamchk --fast只检查那些没有这个标志的表。
· myisamchk --analyze为部分键存储统计信息，也为整个键存储统计信息。
· myisampack可以打包BLOB和VARCHAR列。
MyISAM也支持下列特征：
· 支持true VARCHAR类型；VARCHAR列以存储在2个字节中的长度来开始。
· 有VARCHAR的表可以有固定或动态记录长度。
· VARCHAR和CHAR列可以多达64KB。
· 一个被搞乱的已计算索引对可对UNIQUE来使用。这允许你在表内任何列的合并上有UNIQUE。（尽管如此，你不能在一个UNIQUE已计算索引上搜索）。
对MyISAM存储引擎，有一个更详细的论坛在http://forums.mysql.com/list.php?21。
[/Quote]
ACMAIN_CHM 2010-06-07
- 打赏
- 举报
1、mysql有几种存储引擎 比如MyISAM、InnoDB等
创建表时定义这几种存储引擎对数据和表有什么影响？对表的大小(比如表的行数方面）有什么限制吗？
参考手册中说明了很多。你可以参考一下。
15. 存储引擎和表类型
15.1. MyISAM存储引擎
15.1.1. MyISAM启动选项
15.1.2. 键所需的空间
15.1.3. MyISAM表的存储格式
15.1.4. MyISAM表方面的问题
15.2. InnoDB存储引擎
15.2.1. InnoDB概述
15.2.2. InnoDB联系信息
15.2.3. InnoDB配置
15.2.4. InnoDB启动选项
15.2.5. 创建InnoDB表空间
15.2.6. 创建InnoDB表
15.2.7. 添加和删除InnoDB数据和日志文件
15.2.8. InnoDB数据库的备份和恢复
15.2.9. 将InnoDB数据库移到另一台机器上
15.2.10. InnoDB事务模型和锁定
15.2.11. InnoDB性能调节提示
15.2.12. 多版本的实施
15.2.13. 表和索引结构
15.2.14. 文件空间管理和磁盘I/O
15.2.15. InnoDB错误处理
15.2.16. 对InnoDB表的限制
15.2.17. InnoDB故障诊断与排除
15.3. MERGE存储引擎
15.3.1. MERGE表方面的问题
15.4. MEMORY (HEAP)存储引擎
15.5. BDB (BerkeleyDB)存储引擎
15.5.1. BDB支持的操作系统
15.5.2. 安装BDB
15.5.3. BDB启动选项
15.5.4. BDB表的特性
15.5.5. 修改BDB所需的事宜
15.5.6. 对BDB表的限制
15.5.7. 使用BDB表时可能出现的错误
15.6. EXAMPLE存储引擎
15.7. FEDERATED存储引擎
15.7.1. 安装FEDERATED存储引擎
15.7.2. FEDERATED存储引擎介绍
15.7.3. 如何使用FEDERATED表
15.7.4. FEDERATED存储引擎的局限性
15.8. ARCHIVE存储引擎
15.9. CSV存储引擎
15.10. BLACKHOLE存储引擎
2、对于mysql中大表如何维护？进行表分区吗？如何操作？（表可能有上亿条数据）
一般是分区
3、对于mysql方面如何部署才能达到最大优化？
这个要和你的实际数据分布而定。
创建表时定义这几种存储引擎对数据和表有什么影响？对表的大小(比如表的行数方面）有什么限制吗？
参考手册中说明了很多。你可以参考一下。
15. 存储引擎和表类型
15.1. MyISAM存储引擎
15.1.1. MyISAM启动选项
15.1.2. 键所需的空间
15.1.3. MyISAM表的存储格式
15.1.4. MyISAM表方面的问题
15.2. InnoDB存储引擎
15.2.1. InnoDB概述
15.2.2. InnoDB联系信息
15.2.3. InnoDB配置
15.2.4. InnoDB启动选项
15.2.5. 创建InnoDB表空间
15.2.6. 创建InnoDB表
15.2.7. 添加和删除InnoDB数据和日志文件
15.2.8. InnoDB数据库的备份和恢复
15.2.9. 将InnoDB数据库移到另一台机器上
15.2.10. InnoDB事务模型和锁定
15.2.11. InnoDB性能调节提示
15.2.12. 多版本的实施
15.2.13. 表和索引结构
15.2.14. 文件空间管理和磁盘I/O
15.2.15. InnoDB错误处理
15.2.16. 对InnoDB表的限制
15.2.17. InnoDB故障诊断与排除
15.3. MERGE存储引擎
15.3.1. MERGE表方面的问题
15.4. MEMORY (HEAP)存储引擎
15.5. BDB (BerkeleyDB)存储引擎
15.5.1. BDB支持的操作系统
15.5.2. 安装BDB
15.5.3. BDB启动选项
15.5.4. BDB表的特性
15.5.5. 修改BDB所需的事宜
15.5.6. 对BDB表的限制
15.5.7. 使用BDB表时可能出现的错误
15.6. EXAMPLE存储引擎
15.7. FEDERATED存储引擎
15.7.1. 安装FEDERATED存储引擎
15.7.2. FEDERATED存储引擎介绍
15.7.3. 如何使用FEDERATED表
15.7.4. FEDERATED存储引擎的局限性
15.8. ARCHIVE存储引擎
15.9. CSV存储引擎
15.10. BLACKHOLE存储引擎
2、对于mysql中大表如何维护？进行表分区吗？如何操作？（表可能有上亿条数据）
一般是分区
3、对于mysql方面如何部署才能达到最大优化？
这个要和你的实际数据分布而定。
