优化系列 | 实例解析MySQL性能瓶颈排查定位

导读

从一个现场说起,全程解析如何定位性能瓶颈。

排查过程

收到线上某业务后端的MySQL实例负载比较高的告警信息,于是登入服务器检查确认。

1. 首先我们进行OS层面的检查确认

登入服务器后,我们的目的是首先要确认当前到底是哪些进程引起的负载高,以及这些进程卡在什么地方,瓶颈是什么。

通常来说,服务器上最容易成为瓶颈的是磁盘I/O子系统,因为它的读写速度通常是最慢的。即便是现在的PCIe SSD,其随机I/O读写速度也是不如内存来得快。当然了,引起磁盘I/O慢得原因也有多种,需要确认哪种引起的。

第一步,我们一般先看整体负载如何,负载高的话,肯定所有的进程跑起来都慢。
可以执行指令 w 或者 sar -q 1 来查看负载数据,例如:

[yejr@imysql.com:~ ]# w
 11:52:58 up 702 days, 56 min,  1 user,  load average: 7.20, 6.70, 6.47
USER     TTY      FROM              LOGIN@   IDLE   JCPU   PCPU WHAT
root     pts/0    1.xx.xx.xx        11:51    0.00s  0.03s  0.00s w

或者 sar -q 的观察结果:

[yejr@imysql.com:~ ]# sar -q 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
02:51:18 PM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15   blocked
02:51:19 PM         4      2305      6.41      6.98      7.12         3
02:51:20 PM         2      2301      6.41      6.98      7.12         4
02:51:21 PM         0      2300      6.41      6.98      7.12         5
02:51:22 PM         6      2301      6.41      6.98      7.12         8
02:51:23 PM         2      2290      6.41      6.98      7.12         8

load average大意表示当前CPU中有多少任务在排队等待,等待越多说明负载越高,跑数据库的服务器上,一般load值超过5的话,已经算是比较高的了。

引起load高的原因也可能有多种:

  1. 某些进程/服务消耗更多CPU资源(服务响应更多请求或存在某些应用瓶颈);
  2. 发生比较严重的swap(可用物理内存不足);
  3. 发生比较严重的中断(因为SSD或网络的原因发生中断);
  4. 磁盘I/O比较慢(会导致CPU一直等待磁盘I/O请求);

这时我们可以执行下面的命令来判断到底瓶颈在哪个子系统:

[yejr@imysql.com:~ ]# top
top - 11:53:04 up 702 days, 56 min,  1 user,  load average: 7.18, 6.70, 6.47
Tasks: 576 total,   1 running, 575 sleeping,   0 stopped,   0 zombie
Cpu(s):  7.7%us,  3.4%sy,  0.0%ni, 77.6%id, 11.0%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:  49374024k total, 32018844k used, 17355180k free,   115416k buffers
Swap: 16777208k total,   117612k used, 16659596k free,  5689020k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
14165 mysql     20   0 8822m 3.1g 4672 S 162.3  6.6  89839:59 mysqld
40610 mysql     20   0 25.6g  14g 8336 S 121.7 31.5 282809:08 mysqld
49023 mysql     20   0 16.9g 5.1g 4772 S  4.6 10.8   34940:09 mysqld

很明显是前面两个mysqld进程导致整体负载较高。
而且,从 Cpu(s) 这行的统计结果也能看的出来,%us%wa 的值较高,表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上
我们先分析下磁盘I/O的情况。

执行 sar -d 确认磁盘I/O是否真的较大:

[yejr@imysql.com:~ ]# sar -d 1
Linux 2.6.32-431.el6.x86_64 (yejr.imysql.com)     01/13/2016     _x86_64_    (24 CPU)
11:54:32 AM    dev8-0   5338.00 162784.00   1394.00     30.76      5.24      0.98      0.19    100.00
11:54:33 AM    dev8-0   5134.00 148032.00  32365.00     35.14      6.93      1.34      0.19    100.10
11:54:34 AM    dev8-0   5233.00 161376.00    996.00     31.03      9.77      1.88      0.19    100.00
11:54:35 AM    dev8-0   4566.00 139232.00   1166.00     30.75      5.37      1.18      0.22    100.00
11:54:36 AM    dev8-0   4665.00 145920.00    630.00     31.41      5.94      1.27      0.21    100.00
11:54:37 AM    dev8-0   4994.00 156544.00    546.00     31.46      7.07      1.42      0.20    100.00

再利用 iotop 确认到底哪些进程消耗的磁盘I/O资源最多:

[yejr@imysql.com:~ ]# iotop
Total DISK READ: 60.38 M/s | Total DISK WRITE: 640.34 K/s
  TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
16397 be/4 mysql       8.92 M/s    0.00 B/s  0.00 % 94.77 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
 7295 be/4 mysql      10.98 M/s    0.00 B/s  0.00 % 93.59 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14295 be/4 mysql      10.50 M/s    0.00 B/s  0.00 % 93.57 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14288 be/4 mysql      14.30 M/s    0.00 B/s  0.00 % 91.86 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320
14292 be/4 mysql      14.37 M/s    0.00 B/s  0.00 % 91.23 % mysqld --basedir=/usr/local/m~og_3320/mysql.sock --port=3320

可以看到,端口号是3320的实例消耗的磁盘I/O资源比较多,那就看看这个实例里都有什么查询在跑吧。

2. MySQL层面检查确认

首先看下当前都有哪些查询在运行:

[yejr@imysql.com(db)]> mysqladmin pr|grep -v Sleep
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| Id |User| Host     | db |Command|Time | State        | Info                                                                                          |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+
| 25 | x | 10.x:8519 | db | Query | 68  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404612 order by Fvideoid) t1 |
| 26 | x | 10.x:8520 | db | Query | 65  | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>484915 order by Fvideoid) t1 |
| 28 | x | 10.x:8522 | db | Query | 130 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>404641 order by Fvideoid) t1 |
| 27 | x | 10.x:8521 | db | Query | 167 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324157 order by Fvideoid) t1 |
| 36 | x | 10.x:8727 | db | Query | 174 | Sending data | select max(Fvideoid) from (select Fvideoid from t where Fvideoid>324346 order by Fvideoid) t1 |
+----+----+----------+----+-------+-----+--------------+-----------------------------------------------------------------------------------------------+

可以看到有不少慢查询还未完成,从slow query log中也能发现,这类SQL发生的频率很高。
这是一个非常低效的SQL写法,导致需要对整个主键进行扫描,但实际上只需要取得一个最大值而已,从slow query log中可看到:

Rows_sent: 1  Rows_examined: 5502460

每次都要扫描500多万行数据,却只为读取一个最大值,效率非常低。

经过分析,这个SQL稍做简单改造即可在个位数毫秒级内完成,原先则是需要150-180秒才能完成,提升了N次方。
改造的方法是:对查询结果做一次倒序排序,取得第一条记录即可。而原先的做法是对结果正序排序,取最后一条记录,汗啊。。。

写在最后,小结

在这个例子中,产生瓶颈的原因比较好定位,SQL优化也不难,实际线上环境中,通常有以下几种常见的原因导致负载较高:

  1. 一次请求读写的数据量太大,导致磁盘I/O读写值较大,例如一个SQL里要读取或更新几万行数据甚至更多,这种最好是想办法减少一次读写的数据量;
  2. SQL查询中没有适当的索引可以用来完成条件过滤、排序(ORDER BY)、分组(GROUP BY)、数据聚合(MIN/MAX/COUNT/AVG等),添加索引或者进行SQL改写吧;
  3. 瞬间突发有大量请求,这种一般只要能扛过峰值就好,保险起见还是要适当提高服务器的配置,万一峰值抗不过去就可能发生雪崩效应;
  4. 因为某些定时任务引起的负载升高,比如做数据统计分析和备份,这种对CPU、内存、磁盘I/O消耗都很大,最好放在独立的slave服务器上执行;
  5. 服务器自身的节能策略发现负载较低时会让CPU降频,当发现负载升高时再自动升频,但通常不是那么及时,结果导致CPU性能不足,抗不过突发的请求;
  6. 使用raid卡的时候,通常配备BBU(cache模块的备用电池),早期一般采用锂电池技术,需要定期充放电(DELL服务器90天一次,IBM是30天),我们可以通过监控在下一次充放电的时间前在业务低谷时提前对其进行放电,不过新一代服务器大多采用电容式电池,也就不存在这个问题了。
  7. 文件系统采用ext4甚至ext3,而不是xfs,在高I/O压力时,很可能导致%util已经跑到100%了,但iops却无法再提升,换成xfs一般可获得大幅提升;
  8. 内核的io scheduler策略采用cfq而非deadline或noop,可以在线直接调整,也可获得大幅提升。

 

关于MySQL的方方面面大家想了解什么,可以直接留言回复,我会从中选择一些热门话题进行分享。 同时希望大家多多转发,多一些阅读量是老叶继续努力分享的绝佳助力,谢谢大家 :)

最后打个广告,运维圈人士专属铁观音茶叶微店上线了,访问:http://yejinrong.com 获得专属优惠

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

我们使用EXPLAIN解析SQL执行计划时,如果有下面几种情况,就需要特别关注下了:

首先看下 type 这列的结果,如果有类型是 ALL 时,表示预计会进行全表扫描(full table scan)。通常全表扫描的代价是比较大的,建议创建适当的索引,通过索引检索避免全表扫描。此外,全索引扫描(full index scan)的代价有时候是比全表扫描还要高的,除非是基于InnoDB表的主键索引扫描。

再来看下 Extra 列的结果,如果有出现 Using temporary 或者 Using filesort 则要多加关注:

Using temporary,表示需要创建临时表以满足需求,通常是因为GROUP BY的列没有索引,或者GROUP BY和ORDER BY的列不一样,也需要创建临时表,建议添加适当的索引。

Using filesort,表示无法利用索引完成排序,也有可能是因为多表连接时,排序字段不是驱动表中的字段,因此也没办法利用索引完成排序,建议添加适当的索引。

Using where,通常是因为全表扫描或全索引扫描时(type 列显示为 ALLindex),又加上了WHERE条件,建议添加适当的索引。

暂时想到上面几个,如果有遗漏,以后再补充。

其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。

更多详情请看官方文档:8.8.2 EXPLAIN Output Format

[MySQL FAQ]系列 — processlist中哪些状态要引起关注

MySQL FAQ
插图来自网络并作简单加工,如果觉得不当还请及时告知 :)

一般而言,我们在processlist结果中如果经常能看到某些SQL的话,至少可以说明这些SQL的频率很高,通常需要对这些SQL进行进一步优化。

今天我们要说的是,在processlist中,看到哪些运行状态时要引起关注,主要有下面几个:

状态 建议
copy to tmp table 执行ALTER TABLE修改表结构时建议:放在凌晨执行或者采用类似pt-osc工具
Copying to tmp table 拷贝数据到内存中的临时表,常见于GROUP BY操作时建议:创建适当的索引
Copying to tmp table on disk 临时结果集太大,内存中放不下,需要将内存中的临时表拷贝到磁盘上,形成 #sql***.MYD、#sql***.MYI(在5.6及更高的版本,临时表可以改成InnoDB引擎了,可以参考选项default_tmp_storage_engine建议:创建适当的索引,并且适当加大sort_buffer_size/tmp_table_size/max_heap_table_size
Creating sort index 当前的SELECT中需要用到临时表在进行ORDER BY排序建议:创建适当的索引
Creating tmp table 创建基于内存或磁盘的临时表,当从内存转成磁盘的临时表时,状态会变成:Copying to tmp table on disk建议:创建适当的索引,或者少用UNION、视图(VIEW)、子查询(SUBQUERY)之类的,确实需要用到临时表的时候,可以在session级临时适当调大 tmp_table_size/max_heap_table_size 的值
Reading from net 表示server端正通过网络读取客户端发送过来的请求建议:减小客户端发送数据包大小,提高网络带宽/质量
Sending data 从server端发送数据到客户端,也有可能是接收存储引擎层返回的数据,再发送给客户端,数据量很大时尤其经常能看见备注:Sending Data不是网络发送,是从硬盘读取,发送到网络是Writing to net

建议:通过索引或加上LIMIT,减少需要扫描并且发送给客户端的数据量

Sorting result 正在对结果进行排序,类似Creating sort index,不过是正常表,而不是在内存表中进行排序建议:创建适当的索引
statistics 进行数据统计以便解析执行计划,如果状态比较经常出现,有可能是磁盘IO性能很差建议:查看当前io性能状态,例如iowait
Waiting for global read lock FLUSH TABLES WITH READ LOCK整等待全局读锁建议:不要对线上业务数据库加上全局读锁,通常是备份引起,可以放在业务低谷期间执行或者放在slave服务器上执行备份
Waiting for tables,Waiting for table flush FLUSH TABLES, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, OPTIMIZE TABLE等需要刷新表结构并重新打开建议:不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行
Waiting for lock_type lock 等待各种类型的锁:• Waiting for event metadata lock• Waiting for global read lock• Waiting for schema metadata lock• Waiting for stored function metadata lock• Waiting for stored procedure metadata lock• Waiting for table level lock• Waiting for table metadata lock• Waiting for trigger metadata lock建议:比较常见的是上面提到的global read lock以及table metadata lock,建议不要对线上业务数据库执行这些操作,可以放在业务低谷期间执行。如果是table level lock,通常是因为还在使用MyISAM引擎表,赶紧转投InnoDB引擎吧,别再老顽固了

更多详情可参考官方手册:8.14.2 General Thread States

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

首先,介绍下关于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行记录后,再来对比下其表空间文件大小,以及重整表空间后的大小,观察碎片率。详细对比见下:
mysql-optimization-case-blob-stored-in-innodb-optimization

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

[MySQL优化案例]系列 — 索引、提交频率对InnoDB表写入速度的影响

本次,我们来看看索引、提交频率对InnoDB表写入速度的影响,了解有哪些需要注意的。

先直接说几个结论吧:

1、关于索引对写入速度的影响:
a、如果有自增列做主键,相对完全没索引的情况,写入速度约提升 3.11%;
b、如果有自增列做主键,并且二级索引,相对完全没索引的情况,写入速度约降低 27.37%;

因此,InnoDB表最好总是有一个自增列做主键。

2、关于提交频率对写入速度的影响(以表中只有自增列做主键的场景,一次写入数据30万行数据为例):

a、等待全部数据写入完成后,最后再执行commit提交的效率最高;
b、每10万行提交一次,相对一次性提交,约慢了1.17%;
c、每1万行提交一次,相对一次性提交,约慢了3.01%;
d、每1千行提交一次,相对一次性提交,约慢了23.38%;
e、每100行提交一次,相对一次性提交,约慢了24.44%;
f、每10行提交一次,相对一次性提交,约慢了92.78%;
g、每行提交一次,相对一次性提交,约慢了546.78%,也就是慢了5倍;

因此,最好是等待所有事务结束后再批量提交,而不是每执行完一个SQL就提交一次。
曾经有一次对比测试mysqldump启用extended-insert和未启用导出的SQL脚本,后者比前者慢了不止5倍。
重要:这个建议并不是绝对成立的,要看具体的场景。如果是一个高并发的在线业务,就需要尽快提交事务,避免锁范围被扩大。但如果是在非高并发的业务场景,尤其是做数据批量导入的场景下,就建议采用批量提交的方式。

下面是详细的测试案例过程,有兴趣的同学可以看看:

DROP TABLE IF EXISTS `mytab`;
CREATE TABLE `mytab` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(11) NOT NULL DEFAULT ‘0’,
`c2` int(11) NOT NULL DEFAULT ‘0’,
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT ”,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DELIMITER $$$
DROP PROCEDURE IF EXISTS `insert_mytab`;

CREATE PROCEDURE `insert_mytab`(in rownum int, in commitrate int)
BEGIN
DECLARE i INT DEFAULT 0;

SET AUTOCOMMIT = 0;

WHILE i < rownum DO INSERT INTO mytab(c1, c2, c3,c4) VALUES( FLOOR(RAND()*rownum),FLOOR(RAND()*rownum),NOW(), REPEAT(CHAR(ROUND(RAND()*255)),200)); SET i = i+1; /* 达到每 COMMITRATE 频率时提交一次 */ IF (commitrate > 0) AND (i % commitrate = 0) THEN
COMMIT;
SELECT CONCAT(‘commitrate: ‘, commitrate, ‘ in ‘, I);
END IF;

END WHILE;

/* 最终再提交一次,确保成功 */
COMMIT;
SELECT ‘ALL COMMIT;’;

END; $$$

#测试调用
call insert_mytab(300000, 1); — 每次一提交
call insert_mytab(300000, 10); — 每10次一提交
call insert_mytab(300000, 100); — 每100次一提交
call insert_mytab(300000, 1000); — 每1千次一提交
call insert_mytab(300000, 10000); — 每1万次提交
call insert_mytab(300000, 100000); — 每10万次一提交
call insert_mytab(300000, 0); — 一次性提交

测试耗时结果对比:
mysql-optimization-case-how-index-and-commit-rate-affect-innodb-insert