提高MySQL中InnoDB表BLOB列的存储效率的教程_Mysql

首先，介绍下关于InnoDB引擎存储格式的几个要点：
1、InnoDB可以选择使用共享表空间或者是独立表空间方式，建议使用独立表空间，便于管理、维护。启用 innodb_file_per_table 选项，5.5以后可以在线动态修改生效，并且执行 ALTER TABLE xx ENGINE = InnoDB 将现有表转成独立表空间，早于5.5的版本，修改完这个选项后，需要重启才能生效；
2、InnoDB的data page默认16KB，5.6版本以后，新增选项 innodb_page_size 可以修改，在5.6以前的版本，只能修改源码重新编译，但并不推荐修改这个配置，除非你非常清楚它有什么优缺点；
3、InnoDB的data page在有新数据写入时，会预留1/16的空间，预留出来的空间可用于后续的新纪录写入，减少频繁的新增data page的开销；
4、每个data page，至少需要存储2行记录。因此理论上行记录最大长度为8KB，但事实上应该更小，因为还有一些InnoDB内部数据结构要存储；
5、受限于InnoDB存储方式，如果数据是顺序写入的话，最理想的情况下，data page的填充率是15/16，但一般没办法保证完全的顺序写入，因此，data page的填充率一般是1/2到15/16。因此每个InnoDB表都最好要有一个自增列作为主键，使得新纪录写入尽可能是顺序的；
6、当data page填充率不足1/2时，InnoDB会进行收缩，释放空闲空间；
7、MySQL 5.6版本的InnoDB引擎当前支持COMPACT、REDUNDANT、DYNAMIC、COMPRESSED四种格式，默认是COMPACT格式，COMPRESSED用的很少且不推荐（见下一条），如果需要用到压缩特性的话，可以直接考虑TokuDB引擎；
8、COMPACT行格式相比REDUNDANT，大概能节省20%的存储空间，COMPRESSED相比COMPACT大概能节省50%的存储空间，但会导致TPS下降了90%。因此强烈不推荐使用COMPRESSED行格式；
9、当行格式为DYNAMIC或COMPRESSED时，TEXT/BLOB之类的长列（long column，也有可能是其他较长的列，不一定只有TEXT/BLOB类型，看具体情况）会完全存储在一个独立的data page里，聚集索引页中只使用20字节的指针指向新的page，这就是所谓的off-page，类似ORACLE的行迁移，磁盘空间浪费较严重，且I/O性能也较差。因此，强烈不建议使用BLOB、TEXT、超过255长度的VARCHAR列类型；
10、当InnoDB的文件格式（innodb_file_format）设置为Antelope，并且行格式为COMPACT 或 REDUNDANT 时，BLOB、TEXT或者长VARCHAR列只会将其前768字节存储在聚集索页中（最大768字节的作用是便于创建前缀索引/prefix index），其余更多的内容存储在额外的page里，哪怕只是多了一个字节。因此，所有列长度越短越好；
11、在off-page中存储的BLOB、TEXT或者长VARCHAR列的page是独享的，不能共享。因此强烈不建议在一个表中使用多个长列。

综上，如果在实际业务中，确实需要在InnoDB表中存储BLOB、TEXT、长VARCHAR列时，有下面几点建议：
1、尽可能将所有数据序列化、压缩之后，存储在同一个列里，避免发生多次off-page；
2、实际最大存储长度低于255的列，转成VARCHAR或者CHAR类型（如果是变长数据二者没区别，如果是定长数据，则使用CHAR类型）；
3、如果无法将所有列整合到一个列，可以退而求其次，根据每个列最大长度进行排列组合后拆分成多个子表，尽量是的每个子表的总行长度小于8KB，减少发生off-page的频率；
4、上述建议是在data page为默认的16KB前提下，如果修改成8KB或者其他大小，请自行根据上述理论进行测试，找到最合适的值；
5、字符型列长度小于255时，无论采用CHAR还是VARCHAR来存储，或者把VARCHAR列长度定义为255，都不会导致实际表空间增大；
6、一般在游戏领域会用到比较多的BLOB列类型，游戏界同行可以关注下。

下面是测试验证过程，有耐心的同学可以慢慢看：

#
# 测试案例：InnoDB中长列存储效率
# 测试场景描述：
# 在InnoDB表中存储64KB的数据，对比各种不同存储方式# 每个表写入5000行记录，观察最后表空间文件大小对比
#

#表0：所有数据存储在一个BLOB列中
CREATE TABLE `t_longcol_0` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol` blob NOT NULL COMMENT 'store all data in a blob column',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

#相应的数据写入存储过程：mysp_longcol_0_ins()
CREATE PROCEDURE `mysp_longcol_0_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_0(longcol) select repeat('a',65535);
set @i = @i + 1;
end while;
end;

#表1：将64KB字节平均存储在9个列中
CREATE TABLE `t_longcol_1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store all data in 9 blob columns',
`longcol2` blob NOT NULL,
`longcol3` blob NOT NULL,
`longcol4` blob NOT NULL,
`longcol5` blob NOT NULL,
`longcol6` blob NOT NULL,
`longcol7` blob NOT NULL,
`longcol8` blob NOT NULL,
`longcol9` blob NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_1(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9) select
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',7500),
repeat('a',5535);
set @i = @i + 1;
end while;
end;

#表2：将64KB数据离散存储在多个BLOB列中
CREATE TABLE `t_longcol_2` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol2` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol3` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol4` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol5` blob NOT NULL COMMENT 'store 100 bytes data',
`longcol6` blob NOT NULL COMMENT 'store 255 bytes data',
`longcol7` blob NOT NULL COMMENT 'store 368 bytes data',
`longcol8` blob NOT NULL COMMENT 'store 496 bytes data',
`longcol9` blob NOT NULL COMMENT 'store 512 bytes data',
`longcol10` blob NOT NULL COMMENT 'store 640 bytes data',
`longcol11` blob NOT NULL COMMENT 'store 768 bytes data',
`longcol12` blob NOT NULL COMMENT 'store 912 bytes data',
`longcol13` blob NOT NULL COMMENT 'store 1024 bytes data',
`longcol14` blob NOT NULL COMMENT 'store 2048 bytes data',
`longcol15` blob NOT NULL COMMENT 'store 3082 bytes data',
`longcol16` blob NOT NULL COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` blob NOT NULL COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_1_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_2(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表3：将64KB数据离散存储在多个CHAR、VARCHAR、BLOB列中
CREATE TABLE `t_longcol_3` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` char(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_3_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_3(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表4：将64KB数据离散存储在多个VARCHAR、BLOB列中，对比t_longcol_3中几个列是CHAR的情况
CREATE TABLE `t_longcol_4` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(100) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_4_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_4(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#表5：将64KB数据离散存储在多个VARCHAR、BLOB列中，和t_longcol_4相比，变化在于前面的几个列长度改成了255，但实际存储长度还是100字节
CREATE TABLE `t_longcol_5` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_5_ins()
CREATE PROCEDURE `mysp_longcol_1_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_5(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol13,longcol14,longcol15,longcol16,longcol17,longcol18,longcol19,longcol20) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',1024),
repeat('a',2048),
repeat('a',3082),
repeat('a',4096),
repeat('a',8192),
repeat('a',16284),
repeat('a',20380),
repeat('a',5977);
set @i = @i + 1;
end while;
end;

#从下面开始，参考第3条建议进行分表，每个表所有列长度总和
#分表1，行最大长度 100 + 100 + 100 + 100 + 100 + 255 + 368 + 496 + 512 + 640 + 768 + 912 + 3082 = 7533 字节
CREATE TABLE `t_longcol_51` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol1` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol2` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol3` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol4` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol5` varchar(255) NOT NULL DEFAULT '' COMMENT 'store 100 bytes data',
`longcol6` varchar(256) NOT NULL DEFAULT '' COMMENT 'store 255 bytes data',
`longcol7` varchar(368) NOT NULL DEFAULT '' COMMENT 'store 368 bytes data',
`longcol8` varchar(496) NOT NULL DEFAULT '' COMMENT 'store 496 bytes data',
`longcol9` varchar(512) NOT NULL DEFAULT '' COMMENT 'store 512 bytes data',
`longcol10` varchar(640) NOT NULL DEFAULT '' COMMENT 'store 640 bytes data',
`longcol11` varchar(768) NOT NULL DEFAULT '' COMMENT 'store 768 bytes data',
`longcol12` varchar(912) NOT NULL DEFAULT '' COMMENT 'store 912 bytes data',
`longcol15` varchar(3082) NOT NULL DEFAULT '' COMMENT 'store 3082 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表2，行最大长度 1024 + 2048 + 4096 = 7168 字节
CREATE TABLE `t_longcol_52` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol13` varchar(1024) NOT NULL DEFAULT '' COMMENT 'store 1024 bytes data',
`longcol14` varchar(2048) NOT NULL DEFAULT '' COMMENT 'store 2048 bytes data',
`longcol16` varchar(4096) NOT NULL DEFAULT '' COMMENT 'store 4096 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表3，行最大长度 8192 字节
CREATE TABLE `t_longcol_53` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol17` blob NOT NULL COMMENT 'store 8192 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表4，行最大长度 16284 + 20380 = 36664 字节
CREATE TABLE `t_longcol_54` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol18` blob NOT NULL COMMENT 'store 16284 bytes data',
`longcol19` blob NOT NULL COMMENT 'store 20380 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#分表5，行最大长度 5977 + 4 = 5981 字节
CREATE TABLE `t_longcol_55` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`longcol20` varchar(5977) NOT NULL DEFAULT '' COMMENT 'store 5977 bytes data',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#相应的数据写入存储过程：mysp_longcol_51_ins()
CREATE PROCEDURE `mysp_longcol_51_ins`( in cnt int )
begin
set @i = 1;
while @i < cnt do
insert into t_longcol_51(longcol1,longcol2,longcol3,longcol4,longcol5,longcol6,longcol7,longcol8,longcol9,longcol10,
longcol11,longcol12,longcol15) select
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',100),
repeat('a',256),
repeat('a',368),
repeat('a',496),
repeat('a',512),
repeat('a',640),
repeat('a',768),
repeat('a',912),
repeat('a',3082);

insert into t_longcol_52(longcol13,longcol14,longcol16) select
repeat('a',1024),
repeat('a',2048),
repeat('a',4096);

insert into t_longcol_53(longcol17) select repeat('a',8192);

insert into t_longcol_54(longcol18,longcol19) select
repeat('a',16284),
repeat('a',20380);

insert into t_longcol_55(longcol20) select repeat('a',5977);

set @i = @i + 1;
end while;
end;

上述各个测试表都写入5000行记录后，再来对比下其表空间文件大小，以及重整表空间后的大小，观察碎片率。详细对比见下：

最后一种分表方式中，5个子表的表空间文件大小总和是 40960 + 40960 + 98304 + 286720 + 40960 = 507904 字节。
可以看到，这种方式的总大小和原始表大小差距最小，其他几种存储方式都比这个来的大。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索mysql
mysql innodb存储结构、mysql innodb存储引擎、mysql提高查询效率、mysql存储过程效率、mysql 提高count效率，以便于您获取更多的相关知识。

时间： 2024-09-13 22:46:56

提高MySQL中InnoDB表BLOB列的存储效率的教程_Mysql的相关文章

mysql中innodb表中count()优化

起因:在innodb表上做count(*)统计实在是太慢了,因此想办法看能不能再快点. 现象:先来看几个测试案例,如下 一. sbtest 表上的测试 show create table sbtest＼G *************************** 1. row *************************** Table: sbtest Create Table: CREATE TABLE `sbtest` ( `aid` bigint(20) unsigned NOT NU

MySQL中修改表结构时需要注意的一些地方_Mysql

MySql 在修改表结构的时候可能会中断产品的正常运行影响用户体验,甚至更坏的结果,丢失数据.不是所有的数据库管理员.程序员.系统管理员都非常了解Mysql能避免这种情况.DBA会经常碰到这种生产中断的情况,当升级脚本修改了应用层和数据库层,或者缺乏经验的管理员.开发在不是很了解Mysql内部工作机制的情况下修改了规范文件. 真相是: 直接修改表结构的过程中会锁表(在5.6版本之前) 在线的数据定义语言在5.6版本不总是在线的而且也会锁表 就算使用Percona工具包(在线修改定义文件)也会有若

MySQL中利用索引对数据进行排序的基础教程_Mysql

MySQL中,有两种方式生成有序结果集:一是使用filesort,二是按索引顺序扫描.利用索引进行排序操作是非常快的,而且可以利用同一索引同时进行查找和排序操作.当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时,可以使用索引来排序.如果查询是连接多个表,仅当ORDER BY中的所有列都是第一个表的列时才会使用索引.其它情况都会使用filesort. MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度.

在MySQL中使用JOIN语句进行连接操作的详细教程_Mysql

到目前,我们已经学习了从一个表中获取数据.这是简单的需要,但在大多数现实MySQL的使用,经常需要将数据从多个表中的一个单一的查询. 可以使用多个表中的单一SQL查询.在MySQL中联接(join)行为是指两个或多个表到一个表中可以使用连接在SELECT,UPDATE和DELETE语句中加入MySQL表.我们将看到一个例子LEFT JOIN简单的MySQL连接.在命令提示符使用联接: 假设我们两个表的教程tcount_tbl和tutorials_tbl的完整列表如下: 例子: 试试下面的例子:

MySQL中Distinct和Group By语句的基本使用教程_Mysql

MySQL Distinct 去掉查询结果重复记录DISTINCT 使用 DISTINCT 关键字可以去掉查询中某个字段的重复记录. 语法: SELECT DISTINCT(column) FROM tb_name 例子: 假定 user 表有如下记录: uid username 1 小李 2 小张 3 小李 4 小王 5 小李 6 小张 SQL 语句: SELECT DISTINCT(username) FROM user 返回查询结果如下: username 小李 小张 小王 提示 使用 D

mysql中多表删除其中ID相同记录的方法_Mysql

  问题源自一个5年前的百度知道回答,刚看到这个问题时我刚上班21天,遂根据自己的那一丁点经验和临时操作检验做出了当时认为正确的回答:   但很快就被人否定,我猜主要原因就是我说的最后一句话过于绝对,但由于自己太相信自己了,而且有一大堆理由支撑着,所以没有深入研究. 最近又有网友评论了我的这个回答,我才开始查了一下百度,发现Mysql4.0以上版本已经可以通过一条delete删除多个表的数据了.看来我确实在误人子弟了,只能说声抱歉,好在楼主没有选我的答案,要不然罪过大了. 最后总结一下: MyS

MySQL中二进制与重做日志文件的基本概念学习教程_Mysql

二进制日志二进制日志记录了所有对数据库执行更改的操作,二进制主要有以下两种作用: 1.恢复(recovery)2.复制(replication) 二进制日志的启动:配置参数log-bin[=name],如果不指定name,则默认二进制日志文件名为主机名,后缀名为二级制日志的序列号,所在路径为数据库所在目录. 以index为后缀的文件为二进制日志的索引文件,用来存储过往生产的二进制日志. 和二进制日志相关的参数: max_binlog_size.binlog_cache_size.sync_bin

MySQL中union和join语句使用区别的辨析教程_Mysql

union和join是需要联合多张表时常见的关联词,具体概念我就不说了,想知道上网查就行,因为我也记不准确. 先说差别:union对两张表的操作是合并数据条数,等于是纵向的,要求是两张表字段必须是相同的(Schema of both sides of union should match.).也就是说如果A表中有三条数据,B表中有两条数据,那么A union B就会有五条数据.说明一下union 和union all的差别,对于union如果存在相同的数据记录会被合并,而union all不会合

MySQL中几种数据统计查询的基本使用教程_Mysql

统计平均数SELECT AVG() FROM 语法用于从数据表中统计数据平均数. 语法: SELECT AVG(column) FROM tb_name 该 SQL 语法用于统计某一数值类型字段的平均数,AVG() 内不能是多个字段,字符串等类型虽然可以执行,但无意义. 例子: SELECT AVG(uid) FROM user 得到查询结果: 2.5000 当然在此统计 uid 的平均数是无实际生产意义的,只是为了演示 AVG() 语法的用法. 统计数据之和SELECT SUM() FROM