扎心,MySQL表分区太多了

导读

之前分享过,表分区数量特别大,当有大事务时,可能会导致主从延迟特别严重。

那么如何减少表分区数量呢?

之前老叶分享的意想不到的MySQL复制延迟原因 中提到,规避问题的方法有:

• 删除不用的历史表分区;
• 将大表拆分;
• 将大事务拆分,不要在一个事务里做大批量更新;

针对第一条方法,今天我们来讲讲怎么做表分区的合并,也就是把历史表分区合并到一起。

首先,检查下哪些表的分区数量最大,优先对它们下手:

yejr@imysql.com [information_schema]>SELECT TABLE_NAME, COUNT(*) AS CNT 
    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
    GROUP BY TABLE_NAME ORDER BY CNT DESC LIMIT 50;
+------------+-----+
| TABLE_NAME | CNT |
+------------+-----+
| t1         | 600 |
...
+------------+-----+

可以看到表 t1 的分区共有 600 个之多。

再来看看这个表的分区情况:

yejr@imysql.com[mydb]> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mydate` date NOT NULL,
...
  PRIMARY KEY (`id`,`mydate`),
....
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
PARTITION BY RANGE  COLUMNS(`mydate`)
(
 PARTITION p20160101 VALUES LESS THAN ('2016-01-01’) ENGINE = InnoDB,
 PARTITION p20160102 VALUES LESS THAN ('2016-01-02’) ENGINE = InnoDB,
...
 PARTITION p20170630 VALUES LESS THAN ('2017-06-30') ENGINE = InnoDB)
1 row in set (0.00 sec)

再看看这个表各个分区的数据量分布:

yejr@imysql.com[information_schema]> SELECT PARTITION_NAME, TABLE_ROWS FROM 
    PARTITIONS WHERE TABLE_NAME = ‘t1’;
+----------------+------------+
| PARTITION_NAME | TABLE_ROWS |
+----------------+------------+
| p20160101      |    8391013 |
| p20160102      |    7749149 |
| p20160103      |    8308234 |
....
| p20170729      |          0 |
| p20170730      |          0 |
+----------------+------------+

每天就要生成700-800万行数据,这个量是够大的了。

现在,我们把2016年的所有分区,按照每月一个分区(之前是每天一个分区),合并在一起。

yejr@imysql.com[mydb]> ALTER TABLE t1 REORGANIZE PARTITION
    p20160101,p20160102...p20160131 into (
    partition p201601 VALUES LESS THAN ('2016-01-31'));
Query OK, 0 rows affected (9 hours 56 min 55.33 sec)
Records: 0  Duplicates: 0  Warnings: 0

亲娘啊,要跑9个多小时,好可怕。

合并完分区后,还记得要执行 ANALYZE TABLE 哟,及时更新统计信息。在有大量表分区的情况下,执行 ANALYZE TABLE 可能会略慢,要有耐心,在另一篇分享里 XX 也介绍过了。

我们可以用下面方法构造批量执行表分区合并的SQL指令:

yejr@imysql.com[information_schema]> SELECT TABLE_NAME,
    CONCAT('ALTER TABLE ', TABLE_SCHEMA,
    '.' ,TABLE_NAME, ' REORGANIZE PARTITION ',
    GROUP_CONCAT(PARTITION_NAME),
    ' INTO (PARTITION P2017 VALUES LESS THAN (''2018-01-01''));')
    FROM PARTITIONS WHERE PARTITION_NAME IS NOT NULL
    GROUP BY TABLE_SCHEMA, TABLE_NAME;

SQL中的条件请自行替换。

当然,除了合并分区,我们还可以利用MySQL 5.7支持 分区 & 物理表 直接交换新特性,将历史分区交换到外部物理表,再归档到历史库里。这个方案对在线数据库影响很小,优先推荐此法。

yejr@imysql.com[mydb]> ALTER TABLE t1 EXCHANGE PARTITION p20160101 WITH TABLE t1_20160101;

备注:一个分区只能交换到一个外部物理表中,不能将多个分区交换到同一个表。

最后,如果哪天boss抽风要求把原来的分区打散,重新变成每天一个分区,咋整呢?

其实不难啊,让老叶手把手教你

yejr@imysql.com[mydb]> ALTER TABLE t1  REORGANIZE PARTITION
    p201707 INTO (partition p20170701 VALUES LESS THAN ('2017-07-02’) ,
    partition p20170702 VALUES LESS THAN ('2017-07-03’) ,
...
    partition p20170731 VALUES LESS THAN ('2017-08-01’));

最后祝大家的表分区越来越多,嘿,别打我,快溜~

FAQ系列 – 调用存储过程时报错 Illegal mix of collations

导读

执行存储过程,提示 ERROR 1267 错误,咋整?

其实这是一篇旧文,只不过最近好几位朋友都遇到了,于是翻出来重发。

问题

调用存储过程时,发生报错,信息如下:

ERROR 1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

很明显嘛,这是字符集方面的问题。

解决

先检查数据表,确认字符集是 gb2312 没错。

再检查连接字符集(character_set_client、character_set_connection),服务器端字符集(character_set_server)也全都是 gb2312。

怀疑是字段的字符集有问题,修改成 gb2312,还是报错。

后来创建一个临时变量,设定其字符集为 gb2312,再调用存储过程,仍然不行

set @tmp_str1 = ‘abcde’ collate gb2312_chinese_ci;

有点糊涂之际,突然想起来数据库(schema)的字符集可能不是 gb2312。

检查了下,果然,schema的字符集是 latin1 的,修改成 gb2312 再执行存储过程就正常了。

ALTER DATABASE `yejr` CHARACTER SET gb2312;

建议

遇到字符集问题时,应从服务器端(character_set_server),到数据库(character_set_database),到数据表(table),然后客户端(character_set_client)、连接层(character_set_connection)、结果层(character_set_results)的字符集都检查一遍,确认是否都一致。

默认地,创建新 schema 时,它的字符集是从 character_set_server 直接继承的。
创建新 table 时从 schema 的字符集直接继承的,table 中的 fields 则从 table 继承,各层逐级继承。

但创建 procedure / function 时,则是从 character_set_database(而非 character_set_server) 直接继承的。个别情况下,我们创建 db 时,可能没注意到这点,就可能遇到本例的问题了。

因此,最好是在所有的环节,从头到尾,全都用同一套字符集,不要混合着用,保不准哪天就玩飞了~

UTF8字符集的表可以直接转UTF8MB4吗

导读

我的小密圈里有读者提到一个线上库是utf8字符集,想问怎样将其转成utf8mb4

问题

我的小密圈“「老叶茶馆」铁粉圈”上有读者提问:金** 提问:叶老师,有一个MySQL库是utf8的,不大,4g多,怎样转成utf8mb4.可以有一定的停机时间。

解读

utf8是utf8mb4的子集,一般情况下,应该是可以直接修改表字符集的。
修改字符集的几种方法

方法一

  1. 修改表默认字符集
mysql> alter table j1 default character set utf8mb4;
  1. 随后再修改所有字符型列的字符集
mysql> alter table j1 modify name varchar(20) character set utf8mb4 not null default '';

方法二

也是执行ALTER TABLE来修改,但有更简单的解法

mysql> alter table j1 convert to character set utf8mb4;

备注

上面两种方法,其实是有区别的。

  • 采用方法一,如果遇到某个列字符集转换完后字节数超限了,会提示错误。
  • 而采用方法二,如果遇到某个列字符集转换完后字节数超限了,则会将这个列数据类型转换成可以容纳更大长度的类型,比如从 TEXT 转成 LONGTEXT 等。

方法三

如果不放心,可以用mysqldump逻辑备份方式,用utf8mb4字符集把数据备份出来,新建表,恢复回去,应该也可以的。

结论

想从小字节数(2字节/3字节)字符集(gb2312、utf8)转换到大字节数(4字节)字符集(utf8mb4),是可以直接转换的。
相反,想从大字节数字符集转成小的,则会有风险,例如字符串被截断等。

案例测试

yejr@imysql.com [test]>set names gb2312;

yejr@imysql.com [test]>show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(2) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

//直接修改字符集
yejr@imysql.com [test]>alter table t1 convert to character set utf8mb4;

//把客户端工具的终端字符集从gb2312改成utf8
//同时修改mysql客户端字符集,重新读取数据
yejr@imysql.com [test]>set names utf8mb4;

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

可以看到,是可以直接转换的。

当然了,生产环境中,如果也想这么做,最好还是先在测试环境进行更严格的测试演练,确保无误后再实施。

参考

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

细说analyze table

导读

本文详细介绍了ANALYZE TABLE的作用及更深入的原理,执行代价估算公式等

ANALYZE TABLE 作用

  • ANALYZE TABLE 会统计索引分布信息,并将结果持久化存储;
  • 对于 MyISAM 表,相当于执行了一次 myisamchk –analyze;
  • 支持 InnoDB、NDB、MyISAM 等存储引擎,但不支持 视图(view);
  • ANALYZE TABLE也可以用在表分区上;
  • 对InnoDB、MyISAM表执行 ANALYZE TABLE 时,会加上读锁(read lock);
  • 执行 ANALYZE TABLE 会记录binlog。(这是合理的,因为索引分析这个操作,在MASTER端执行完后,SLAVE端也是需要的)

索引分布分析都干了啥

若自从上次索引分析后没有数据更新的话,执行 ANALYZE TABLE 并不会再分析一次。
optimizer 会根据索引分析结果来判断表 JOIN 的驱动顺序,以及选用哪个索引。

关于 innodb_stats_persistent 选项

我们可以通过设定该选项,决定索引分析结果是否要持久化存储到磁盘中。

不持久化存储的话,可能需要频繁更新统计信息,并由此引发执行计划反复变化。

这个设置在每个表创建(或后期 ALTER 修改)时都可以自行指定 STATS_PERSISTENT 选项,也可以设置全局选项 innodb_stats_persistent(这个选项设置为 1 时,则表统计信息将持久化存储)。

关于 innodb_stats_persistent_sample_pages 选项

该选项决定了每次统计索引及其他信息时要采集多少个data page,默认值是 20。

增加这个值,可以提高统计信息的精确度,同样也能提高执行计划的准确性,不过也相应增加了在InnoDB表上分析的I/O开销。

备注

  • 增加 innodb_stats_persistent_sample_pages 的值可能导致 ANALYZE TABLE 的耗时增加。可以参考下方公式估算执行 ANALYZE TABLE 的代价。
  • 只有在 innodb_stats_persistent 选项启用后,innodb_stats_persistent_sample_pages 也才能跟着生效,否则的话,只有选项 innodb_stats_transient_sample_pages 才能生效。
  • 选项 innodb_stats_transient_sample_pages 设定的是 动态 统计信息采集的data page数量,默认值是 8。
  • 选项 innodb_stats_persistent_sample_pages 是全局作用的,但如果某个表想单独定义采集的page数目,可以在DDL时自行设定:
CREATE TABLE ... STATS_SAMPLE_PAGES = 30;

ALTER TABLE ... STATS_SAMPLE_PAGES = 30;

ANALYZE TABLE 代价估算

关于执行ANALYZE TABLE 的代价计算公式:

影响代价因素:
– innodb_stats_persistent_sample_pages定义值大小;
– 表中索引数多少;
– 表中分区数多少。

代价粗略估算公式:innodb_stats_persistent_sample_pages * 索引数 * 分区数

而更严谨的计算公式见下:

O(n_sample
  * (n_cols_in_uniq_i
     + n_cols_in_non_uniq_i
     + n_cols_in_pk * (1 + n_non_uniq_i))
  * n_part)

各项指标解释:

  • n_sample,采集的data page数量;
  • n_cols_in_uniq_i,所有唯一索引(不含主键索引)中的列总数;
  • n_cols_in_non_uniq_i,所有普通索引中的列总数;
  • n_cols_in_pk,主键索引中的列总数(若未显式定义主键,则相当于只有一列的ROWID);
  • n_non_uniq_i,非唯一索引数量;
  • n_part,表分区数量。

以下表为例:

CREATE TABLE t (
  a INT,
  b INT,
  c INT,
  d INT,
  e INT,
  f INT,
  g INT,
  h INT,
  PRIMARY KEY (a, b),
  UNIQUE KEY i1uniq (c, d),
  KEY i2nonuniq (e, f),
  KEY i3nonuniq (g, h)
);

我们执行下面的SQL来查询这个表的索引信息:

  SELECT index_name, stat_name, stat_description
  FROM mysql.innodb_index_stats
  WHERE
  database_name='test' AND
  table_name='t' AND
  stat_name like 'n_diff_pfx%';

  +------------+--------------+------------------+
  | index_name | stat_name    | stat_description |
  +------------+--------------+------------------+
  | PRIMARY    | n_diff_pfx01 | a                |
  | PRIMARY    | n_diff_pfx02 | a,b              |
  | i1uniq     | n_diff_pfx01 | c                |
  | i1uniq     | n_diff_pfx02 | c,d              |
  | i2nonuniq  | n_diff_pfx01 | e                |
  | i2nonuniq  | n_diff_pfx02 | e,f              |
  | i2nonuniq  | n_diff_pfx03 | e,f,a            |
  | i2nonuniq  | n_diff_pfx04 | e,f,a,b          |
  | i3nonuniq  | n_diff_pfx01 | g                |
  | i3nonuniq  | n_diff_pfx02 | g,h              |
  | i3nonuniq  | n_diff_pfx03 | g,h,a            |
  | i3nonuniq  | n_diff_pfx04 | g,h,a,b          |
  +------------+--------------+------------------+

上面这个结果看起来有点奇怪是不是,其实没错,先科普几点知识:

  • 所有的普通索引,实际物理存储时,都要包含主键列的,也就是所谓的 index extensions 特性;
  • 统计索引信息时,是根据最左原则,要统计各种组合的。比如(a,b) 索引,要统计(a), (a,b), (a,b,pk) 三种信息,而不是只统计(a,b)这个信息;
  • 不过,在 mysql.innodb_index_stats 中存储统计信息时,是不统计唯一索引后面存储主键列信息的,非唯一普通索引后存储主键列信息则会被统计进去;

因此,上面 mysql.innodb_index_stats 中存储的统计结果是正确的。

我们再回来看下索引统计的代价公式,像下面这样计算:

- n_sample,采集的data page数量,值为 20(默认值);
- n_cols_in_uniq_i,所有唯一索引(不含主键索引)中的列总数,值为 2;
- n_cols_in_non_uniq_i,所有普通索引中的列总数,值为 4;
- n_cols_in_pk,主键索引中的列总数(若未显式定义主键,则相当于只有一列的ROWID),值为 2;
- n_non_uniq_i,非唯一索引数量,值为 2;
- n_part,表分区数量,值为 1(没有表分区,值为1,而不是0)。

那么最终需要扫描的data page数结果就是:

20 * (2 + 4 + 2 * (1 + 2)) * 1 = 240

实际需要读取的字节数则是:

240 * 16384 = 3932160 (即 3.84M)

当然了,要读取的data page,有可能已经在buffer pool中了,因此并不全是物理读。

从中,我们也可以看到,这个代价和表的数据量并无直接关系。
不过,当表数量越大时,聚集索引的 B+ 树也越大,搜索代价肯定也越大。

参考

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

MySQL安全手册

数据是企业核心资产,数据对企业而言是最重要的工作之一。稍有不慎,极有可能发生数据无意泄露,甚至被黑客恶意窃取的风险。每年业界都会传出几起大事件,某知名或不知名的公司被脱裤(拖库的谐音,意思是整个数据库被黑客盗取)之类的。

从数据安全上也可以分为外网安全及内部操作安全,下面分别讨论一下。
内部操作安全策略

1. 是否回收DBA全部权限

试想,如果DBA没权限了,日常DB运维的活,以及紧急故障处理,该怎么实施呢?因此,建议在没有成熟的自动化运维平台前,不应该粗暴的回收DBA的太多权限,否则可能会导致工作效率降低的,甚至DBA有一种不被信任的负面情绪。

2. MySQL层安全策略

• 业务帐号最多只可以通过内网远程登录,而不能通过公网远程连接。
• 增加运维平台账号,该账号允许从专用的管理平台服务器远程连接。当然了,要对管理平台部署所在服务器做好安全措施以及必要的安全审计策略。
• 建议启用数据库审计功能。这需要使用MySQL企业版,或者Percona/MariaDB分支版本,MySQL社区版本不支持该功能。
• 启用 safe-update 选项,避免没有 WHERE 条件的全表数据被修改;
• 在应用中尽量不直接DELETE删除数据,而是设置一个标志位就好了。需要真正删除时,交由DBA先备份后再物理删除,避免误操作删除全部数据。
• 还可以采用触发器来做一些辅助功能,比如防止黑客恶意篡改数据。

3. MySQL账号权限规则

• 业务帐号,权限最小化,坚决不允许DROP、TRUNCATE权限。
• 业务账号默认只授予普通的DML所需权限,也就是select、update、insert、delete、execute等几个权限,其余不给。
• MySQL初始化后,先行删除无用账号,删除匿名test数据库

mysql> delete from mysql.user where user!='root' or host!='localhost'; flush privileges;

mysql> drop database test;

• 创建备份专用账号,只有SELECT权限,且只允许本机可登入。
• 设置MySQL账号的密码安全策略,包括长度、复杂性。

4. 关于数据备份

记住,做好数据全量备份是系统崩溃无法修复时的最后一概救命稻草。
备份数据还可以用来做数据审计或是用于数据仓库的数据源拉取之用。
一般来说,备份策略是这样的:每天一次全备,并且定期对binlog做增备,或者直接利用binlog server机制将binlog传输到其他远程主机上。有了全备+binlog,就可以按需恢复到任何时间点。
特别提醒:当采用xtrabackup的流式备份时,考虑采用加密传输,避免备份数据被恶意截取。
外网安全策略
事实上,操作系统安及应用安全要比数据库自身的安全策略更重要。同理,应用程序及其所在的服务器端的系统安全也很重要,很多数据安全事件,都是通过代码漏洞入侵到应用服务器,再去探测数据库,最后成功拖库。

5. 操作系统安全建议

• 运行MySQL的Linux必须只运行在内部网络,不允许直接对公网暴露,实在有需要从公网连接的话,再通过跳板机做端口转发,并且如上面所述,要严格限制数据库账号权限级别。
• 系统账号都改成基于ssh key认证,不允许远程密码登入,且ssh key的算法、长度有要求以确保相对安全。这样就没有密码丢失的风险,除非个人的私钥被盗。
• 进一步的话,甚至可以对全部服务器启用PAM认证,做到账号的统一管理,也更方便、安全。
• 关闭不必要的系统服务,只开必须的进程,例如 mysqld、sshd、networking、crond、syslogd 等服务,其它的都关闭。
• 禁止root账号远程登录。
• 禁止用root账号启动mysqld等普通业务服务进程。
• sshd服务的端口号建议修改成10000以上。
• 在不影响性能的前提下,尽可能启用对MySQL服务端口的防火墙策略(高并发时,采用iptables可能影响性能,建议改用ip route策略)。
• GRUB必须设置密码,物理服务器的Idrac/imm/ilo等账号默认密码也要修改。
• 每个需要登入系统的员工,都使用每个人私有帐号,而不是使用公共账号。
• 应该启用系统层的操作审计,记录所有ssh日志,或利bash记录相应的操作命令并发送到远程服务器,然后进行相应的安全审计,及时发现不安全操作。
• 正确设置MySQL及其他数据库服务相关目录权限,不要全是755,一般750就够了。
• 可以考虑部署堡垒机,所有连接远程服务器都需要先通过堡垒机,堡垒机上就可以实现所有操作记录以及审计功能了。
• 脚本加密对安全性提升其实没太大帮助。对有经验的黑客来说,只要有系统登入权限,就可以通过提权等方式轻松获得root。

6. 应用安全建议

• 禁用web server的autoindex配置。
• 从制度层面,杜绝员工将代码上传到外部github上,因为很可能存在内部IP、账号密码泄露的风险,真的要上传必须先经过安全审核。
• 尽量不要在公网上使用开源的cms、blog、论坛等系统,除非做过代码安全审计,或者事先做好安全策略。这类系统一般都是黑客重点研究对象,很容易被搞;
• 在web server层,可以用一些安全模块,比如nginx的WAF模块;
• 在app server层,可以做好代码安全审计、安全扫描,防止XSS攻击、CSRF攻击、SQL注入、文件上传攻击、绕过cookie检测等安全漏洞;
• 应用程序中涉及账号密码的地方例如JDBC连接串配置,尽量把明文密码采用加密方式存储,再利用内部私有的解密工具进行反解密后再使用。或者可以让应用程序先用中间账号连接proxy层,再由proxy连接MySQL,避免应用层直连MySQL;

最后我们想说,任何高明的安全策略,都不如内部员工的安全意识来的重要。以前发生过一起案例,公司内有位员工的PC不慎中毒,结果导致内网数据被盗。

安全无小事,每个人都应铭记于心。在数据安全面前,可以适当牺牲一些便利性,当然也不能太过,否则可能得不偿失。

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。