标签归档:MySQL FAQ

FAQ系列 | table id问题导致主从复制失败

0、导读

主从复制环境中,IO、SQL线程都很正常,也没设置过滤规则,但数据就是无法复制到slave上,什么原因?

1、问题描述

事实上,这个案例发生已经有一阵子了,一直拖到现在我才整理。

发现一个主从环境中,slave上的io_thread、sql_thread状态均正常,relay log也正常接收来自master的event,但slave上却无法正常应用这些event,个别表数据没有复制过来。而且slave上的binlog也没有记录这些表上的操作。

2、原因分析

接到现场后,第一反应是是先检查是否设置了ignore/do规则,发现并不是这个原因引起的。

我自己手动测试创建了个新的测试表,写了几条数据,发现在slave上这个表能被创建,但写入的测试数据仍旧无法复制过来。这说明,slave上的复制并不是完全失效的,只是有特殊情形下才会失效。

结合上面的问题,想到了可能是因为binlog format以及事务隔离级别等原因导致失效的,于是做了下面的尝试。

//首先修改事务隔离级别为RR(此前是RC),尽可能保证主从数据一致性

root@imysql [mydb]> set session transaction isolation level repeatable read;

//测试写入2条数据

root@imysql [mydb]> insert into z select 5,5;

root@imysql [mydb]> insert into z select 6,6;

经过观察,这2条数据不可以复制到slave上。

//修改binlog format为statement(此前是row),再写入2条数据

root@imysql [mydb]> set session binlog_format=’statement’;

root@imysql [mydb]> insert into z select 7,7;

root@imysql [mydb]> insert into z select 8,8;

经过观察,这2条数据则可以复制到slave上。

现在至少表面上看起来,是由于binlog format+事务隔离级别综合因素引起的,所以我们来对比下不同binlog format下的binlog有什么区别吧。

tableid1
这些日志中,前两条是row模式下的日志,后两条则是statement模式下的。我们注意到红框中内容是:table_id: 24874588093,正是由于这个原因导致了slave无法正常复制数据。

正常情况下,row模式下的binlog event应该是这样的:

tableid2
在上面的日志中,我们看到的是:table_id: 108,这种情况下就可以正常复制了。

现在问题很明确了,就是由于binlog中table id异常导致无法复制。那么,到底什么原因导致table id出现异常呢。

3、案例建议

搜索了一些资料,发现也有别人遇到同样的问题。我就不多啰嗦了,大家可以看下方参考文章详细了解下。简言之,发生这中问题的原因,主要是因为table cache不够了,导致要频繁打开、关闭table,导致table id急剧增长,因而导致主从数据复制失败。

解决办法有几个:

  1. 加大 table_cache_size,或者 table_open_cache 值,以及 table_definition_cache 选项。一般设置不低于总table数量的1.5倍,更严谨的话,要看 Open_tables 和 Opened_tables 这两个status值。Open_tables 表示当前正被打开的table数量,而 Opened_tables 表示历史上反复打开table的总次数。如果 Opened_tables 值特别高,表明 table cache 很可能不够用所致。
  2. 择机重启主库实例,让table id的值再次从0开始计数。
  3. 临时解决方案:把binlog format改成statement,并且把事务隔离级别改成RR,尽量避免数据不一致的风险。

本文参考:

1. 杨奇龙《【MySQL】再说MySQL中的 table_id 》,http://blog.itpub.net/22664653/viewspace-1158547/

2. yuyue2014《MySQL table_id原理及风险分析》,http://www.cnblogs.com/yuyue2014/p/3721172.html

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

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

FAQ系列 | 写新数据时某列值总是被自动修改

0、导读

往表里写入新数据时,却一直报告主键冲突,某列值一直被重置为一个固定值,疑似被黑,啥情况?

1、问题描述

某朋友的线上数据库,怀疑被侵入了。具体表象是:INSERT的时候,某列值总被自动改成一个固定值。

他们先自查了 TRIGGER 和 EVENT,都是空的,确定不是因为这两种原因引起,实在想不出是哪里被动了手脚。

问题的现象:

MariaDB [information_schema]> use bbs9;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [bbs9]> INSERT INTO cdb_mythreads_latest (uid,username,tid,fid,subject,special,dateline) VALUES (‘1239009‘,’yayv’,’13482713‘,’815′,’bbs5 …………….’,’0′,’1459569279′);

ERROR 1062 (23000): Duplicate entry ‘1239009-8388607’ for key ‘PRIMARY’

可以看到,tid列的值被从 13482713(原始值) 自动替换成了 8388607(篡改值)。

更让人奇怪的是,这条SQL在mysql client端手动执行手,也会报告同样的错误。究竟是什么黑客这么牛逼呢,百思不得其解~~~

2、原因分析

单从现象来看,好像还真是被黑了的意思。

but,但是,可是,你如果足够细心,就会发现端倪。

为什么这么说呢,因为 8388607 这个数值是不是看起来挺眼熟的?嗯,没错,你才对了,这个值是 MEDIUMINT 类型的最大值,而 MEDIUMINT UNSIGNED 的最大值是 16777215

当然了,你再认真看一眼表的名字是什么:cdb_mythreads_latest,我又要呵呵了,你懂得的。

3、其他建议

既然原因已经清楚了,那么解决起来也就简单了,只需要把tid列类型改成INT UNSIGNED,甚至BIGINT UNSIGNED都行。

MEDIUMINT和INT两种类型,也只是差了1个字节,何必呢。与其在这个地方节约1个字节,还不如在别的CHAR/VARCHAR/TEXT列调整下,其优化效果要好的多得多。

4、相关案例

FAQ系列 | 添加自增列失败

 

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

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

FAQ系列 | 防范SQL注入风险

0、导读

在MySQL里,如何识别并且避免发生SQL注入风险

1、关于SQL注入

互联网很危险,信息及数据安全很重要,SQL注入是最常见的入侵手段之一,其技术门槛低、成本低、收益大,颇受各层次的黑客们所青睐。

一般来说,SQL注入的手法是利用各种机会将恶意SQL代码添加到程序参数中,并最终被服务器端执行,造成不良后果。

例如,我们访问接口 http://imysql.com/user.php?userid=123 来根据userid获取用户信息,假设程序中是这么处理的:

$sql = “SELECT * FROM user WHERE userid = $_GET[userid]“;

上面这段代码看起来既low有很xx对吧,尤其是在双引号里面还可以直接引用数据类型变量,所以说php是世界上最好的语言一点不为过,哈哈(其实我早期也写过几年php的)。

这时候,如果我们传递进去的参数改成这样:http://imysql.com/user.php?userid=123 or 1=1,这就会导致SQL条件永远成立,所有的数据都会被读取出来。又或者可以传递这样的参数:http://imysql.com/user.php?userid=123 or if(now()=sysdate(),sleep(5),1),这时候不但所有的数据都会被读取到,也会让这个SQL执行完毕后再等待5秒才能返回,黑客可据此来判断这个SQL注入探测是否成功。

在上面这个例子中,其实我们只需要对用户输入的参数进行简单的类型判断和控制,即可快速避免被注入的风险,例如改成下面这样就可以了:

$userid = intval(strim($_GET[‘userid’]));

$sql = “SELECT * FROM user WHERE userid = “ . mysql_real_escape_string($userid);

可见,至少基础的SQL注入并不难防范,只要在各个层面都做足工作就可以。而简单的SQL盲注(就是乱拳打死老师傅的玩法)已经可以采用sqlmap之类的辅助工具来做了,完全不需要人工执行。

2、如何防范

上面提到过sqlmap,它既可以作为SQL盲注的工具,也可以在新项目上线前内部扫一次,提前发现潜在漏洞,及时修补,反过来为我们所用。其他可以检测sql注入漏洞的知名扫描工具有:SQLIer、SQLID、SQL Power Injector、SQLNinja

我们也可以自己通过频繁扫描当前执行的SQL列表,根据一些关键字来判断是否发生了SQL注入或潜在风险,常见的关键字有:

  • SLEEP() — 一般的SQL盲注都会伴随SLEEP()函数出现,而且一般至少SLEEP 5秒以上
  • MID()
  • CHAR()
  • ORD()
  • SYSDATE()
  • SUBSTRING()
  • DATABASES()
  • SCHEMA()
  • USER()
  • VERSION()
  • CURRENT_USER()
  • LOAD_FILE()
  • OUTFILE/DUMPFILE
  • INFORMATION_SCHEMA
  • TABLE_NAME
  • fwrite()/fopen()/file_get_contents() — 这几个是PHP文件操作函数

我们可以以较高频率检查当前的活跃SQL命令,一旦发现上述关键字,可以立即记录下来并触发告警,通知管理员及时人工确认处理,甚至也可以先直接自动杀掉这些SQL查询(可以用 pt-kill 工具来做到这点,也可以自行开发),以防万一,少给黑客留机会。

还有,我们建议把选项 safe-update/sql_safe_updates 设置为 1,防止没有任何 WHERE 条件的误操作更新,将全表数据都写错

3、其他建议

防范SQL注入只是数据安全保护工作很小的一部分,只要做好基本功就可以防住至少80%以上的SQL注入探测。

在app server层,以PHP开发语言为例,除了上面提到的规范用户输入类型外,还可以改成用 sprintf() 函数来格式化构造 SQL 语句,也可以一定程度防范SQL注入。还可以修改 php cgi 程序的运行属主为普通用户,最起码不能使用 root 用户,避免因为代码层不严谨导致被黑客上传可执行 php 程序代码文件。还可以把php中的远程文件调用权限关闭,把选项 allow_url_fopen、allow_url_include 均设置为 off,并限定php可以打开的文件目录,不允许跨区域访问敏感文件。

除了在代码层面做好数据类型判断、用户输入判断外,还可以在web server层加上过滤策略,比如在nginx上启用WAF插件。或者,也可以购买IDC运营商、云主机提供商提供的商业解决方案。对于重视数据安全的企业来说,花点钱保平安更为重要。

4、附录

下面是一些常见SQL注入参考案例:

案例1:SELECT * FROM t WHERE a LIKE ‘%xxx%’ OR (IF(NOW=SYSDATE(), SLEEP(5), 1)) OR b LIKE ‘1=1‘;

案例2:SELECT * FROM t WHERE a > 0 AND b IN(497 AND (SELECT * FROM (SELECT(SLEEP(20)))a));

案例3:SELECT * FROM t WHERE a=1 and b in (1234,(SELECT (CASE WHEN (5=5) THEN SLEEP(5) ELSE 5*(SELECT 5 FROM INFORMATION_SCHEMA.CHARACTER_SETS) END)));

 

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

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

FAQ系列 | 如何避免ibdata1文件大小暴涨

0、导读

遇到InnoDB的共享表空间文件ibdata1文件大小暴增时,应该如何处理?

1、问题背景

用MySQL/InnoDB的童鞋可能也会有过烦恼,不知道为什么原因,ibdata1文件莫名其妙的增大,不知道该如何让它缩回去,就跟30岁之后男人的肚腩一样,汗啊,可喜可贺的是我的肚腩还没长出来,hoho~

正式开始之前,我们要先知道ibdata1文件是干什么用的。

ibdata1文件是InnoDB存储引擎的共享表空间文件,该文件中主要存储着下面这些数据:

  • data dictionary
  • double write buffer
  • insert buffer/change buffer
  • rollback segments
  • undo space
  • Foreign key constraint system tables

另外,当选项 innodb_file_per_table = 0 时,在ibdata1文件中还需要存储 InnoDB 表数据&索引。ibdata1文件从5.6.7版本开始,默认大小是12MB,而在这之前默认大小是10MB,其相关选项是 innodb_data_file_path,比如我一般是这么设置的:

innodb_data_file_path = ibdata1:1G:autoextend

当然了,无论是否启用了 innodb_file_per_table = 1,ibdata1文件都必须存在,因为它必须存储上述 InnoDB 引擎所依赖&必须的数据,尤其是上面加粗标识的 rollback segmentsundo space,它俩是引起 ibdata1 文件大小增加的最大原因,我们下面会详细说。

2、原因分析

我们知道,InnoDB是支持MVCC的,它和ORACLE类似,采用 undo log、redo log来实现MVCC特性的。在事务中对一行数据进行修改时,InnoDB 会把这行数据的旧版本数据存储一份在undo log中,如果这时候有另一个事务又要修改这行数据,就又会把该事物最新可见的数据版本存储一份在undo log中,以此类推,如果该数据当前有N个事务要对其进行修改,就需要存储N份历史版本(和ORACLE略有不同的是,InnoDB的undo log不完全是物理block,主要是逻辑日志,这个可以查看 InnoDB 源码或其他相关资料)。这些 undo log 需要等待该事务结束后,并再次根据事务隔离级别所决定的对其他事务而言的可见性进行判断,确认是否可以将这些 undo log 删除掉,这个工作称为 purge(purge 工作不仅仅是删除过期不用的 undo log,还有其他,以后有机会再说)。

那么问题来了,如果当前有个事务中需要读取到大量数据的历史版本,而该事务因为某些原因无法今早提交或回滚,而该事务发起之后又有大量事务需要对这些数据进行修改,这些新事务产生的 undo log 就一直无法被删除掉,形成了堆积,这就是导致 ibdata1 文件大小增大最主要的原因之一。这种情况最经典的场景就是大量数据备份,因此我们建议把备份工作放在专用的 slave server 上,不要放在 master server 上。

另一种情况是,InnoDB的 purge 工作因为本次 file i/o 性能是在太差或其他的原因,一直无法及时把可以删除的 undo log 进行purge 从而形成堆积,这是导致 ibdata1 文件大小增大另一个最主要的原因。这种场景发生在服务器硬件配置比较弱,没有及时跟上业务发展而升级的情况。

比较少见的一种是在早期运行在32位系统的MySQL版本中存在bug,当发现待 purge 的 undo log 总量超过某个值时,purge 线程直接放弃抵抗,再也不进行 purge 了,这个问题在我们早期使用32位MySQL 5.0版本时遇到的比较多,我们曾经遇到这个文件涨到100多G的情况。后来我们费了很大功夫把这些实例都迁移到64位系统下,终于解决了这个问题。

最后一个是,选项 innodb_data_file_path 值一开始就没调整或者设置很小,这就必不可免导致 ibdata1 文件增大了。Percona官方提供的 my.cnf 参考文件中也一直没把这个值加大,让我百思不得其解,难道是为了像那个经常被我吐槽的xx那样,故意留个暗门,好方便后续帮客户进行优化吗?(我心理太阴暗了,不好不好~~)

稍微总结下,导致ibdata1文件大小暴涨的原因有下面几个:

  • 有大量并发事务,产生大量的undo log;
  • 有旧事务长时间未提交,产生大量旧undo log;
  • file i/o性能差,purge进度慢;
  • 初始化设置太小不够用;
  • 32-bit系统下有bug。

稍微题外话补充下,另一个热门数据库 PostgreSQL 的做法是把各个历史版本的数据 和 原数据表空间 存储在一起,所以不存在本案例的问题,也因此 PostgreSQL 的事务回滚会非常快,并且还需要定期做 vaccum 工作(具体可参见PostgreSQL的MVCC实现机制,我可能说的不是完全正确哈)

3、解决方法建议

看到上面的这些问题原因描述,有些同学可能觉得这个好办啊,对 ibdata1 文件大小进行收缩,回收表空间不就结了吗。悲剧的是,截止目前,InnoDB 还没有办法对 ibdata1 文件表空间进行回收/收缩,一旦 ibdata1 文件的肚子被搞大了,只能把数据先备份后恢复再次重新初始化实例才能恢复原先的大小,或者把依次把各个独立表空间文件备份恢复到一个新实例中,除此外,没什么更好的办法了

当然了,这个问题也并不是不能防范,根据上面提到的原因,相应的建议对策是:

  • 升级到5.6及以上(64-bit),采用独立undo表空间,5.6版本开始就支持独立的undo表空间了,再也不用担心会把 ibdata1 文件搞大;
  • 初始化设置时,把 ibdata1 文件至少设置为1GB以上;
  • 增加purge线程数,比如设置 innodb_purge_threads = 8;
  • 提高file i/o能力,该上SSD的赶紧上;
  • 事务及时提交,不要积压;
  • 默认打开autocommit = 1,避免忘了某个事务长时间未提交;
  • 检查开发框架,确认是否设置了 autocommit=0,记得在事务结束后都有显式提交或回滚。

 

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

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

FAQ系列 | 添加自增列失败

0、导读

想往一个表里添加一个自增列做主键,居然失败报告无法读取,这是怎么回事?

1、问题描述

有位朋友在升级discuz论坛数据库时遇到问题了,想给一个表添加自增列做主键,结果发生下面的报错:

mysql> ALTER TABLE pre_common_credit_log ADD `logid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (logid) ;

ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

2、原因分析

从报错信息来看,第一反应是:数据表损坏了。不过,再仔细想想,肯定不是啊,否则报错也不是这个了,而是像下面这样的了:

1030 Got error -1 from storage engine

再仔细一想,更大的可能性是:该表里的总数据量,超过了mediumint最大值所致。

让朋友把自增列数据类型改成int或者bigint,果真就好了。

3、其他建议

建议使用discuz的同学们可以考虑这么做:

  • 如果启用了抢楼功能,那么就把post相关的表继续保留使用MyISAM引擎。或者自己动手改造代码,把抢楼功能中的楼梯值用redis来存储;
  • 如果没有启用抢楼功能,那么就可以放心的把所有表引擎改成InnoDB了。

顺便,再次吐槽一下discuz的某些功能设计,比如把session表用HEAP引擎,其他表默认引擎还是MyISAM。不过,这并不影响discuz成为国内最优秀的论坛解决方案(没办法,没更好的了,占了先机)。

 

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

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

FAQ系列 | index extensions特性介绍

0、导读

本文介绍MySQL的index extensions特性,以及如何利用这个特性实现SQL查询优化。

1、什么是index extensions

index extensions是MySQL 5.6.9之后的新特性,关于这个特性,手册中的解释是这样的:InnoDB automatically extends each secondary index by appending the primary key columns to it(出处详见手册 8.2.1.7 Use of Index Extensions,原文链接:https://dev.mysql.com/doc/refman/5.6/en/index-extensions.html 简言之就是,InnoDB引擎表中,会把主键所有列值附加存储在辅助索引中

假设有这样一个表:
CREATE TABLE t(
a int not null,
b int not null,
c int not null,
d int not null,
PRIMARY KEY(a, b),
KEY i_c(c)
) ENGINE=InnoDB;

意思是,该表中的辅助索引 i_c 的索引键值,实际上也同时存储了主键中的两个列值,也就是说,i_c 的索引数据结构中,实际上存储的列是:c、a、b 三列的值。

我们可通过 innodb_table_monitor 查看验证下:

TABLE: name test/t, id 681, flags 1, columns 7, indexes 2, appr.rows 0
COLUMNS: a: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; b: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; c: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; d: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4; DB_ROW_ID: DATA_SYS prtype 256 len 6; DB_TRX_ID: DATA_SYS prtype 257 len 6; DB_ROLL_PTR: DATA_SYS prtype 258 len 7;

 INDEX: name PRIMARY, id 1159, fields 2/6, uniq 2, type 3
root page 3, appr.key vals 0, leaf pages 1, size pages 1
  FIELDS:  a b DB_TRX_ID DB_ROLL_PTR c d

 INDEX: name i_c, id 1160, fields 1/3, uniq 3, type 0
root page 4, appr.key vals 0, leaf pages 1, size pages 1
  FIELDS:  c a b

可见,确实是如此。我们顺便也看到 PRIMARY KEY 里包含了所有的列值,以及 DB_TRX_ID、DB_ROLL_PTR 等额外属性(InnoDB引擎独有特性,用于实现InnoDB的事务)。

2、怎么利用index extensions

事实上,辅助索引实际也存储主键值的特性,在InnoDB引擎中一直都是如此,只是从5.6.9版本开始后,在计算执行计划时,查询优化器(optimizer)才能识别到这个特性,并且利用这个特性。而在5.6.9以前,虽然这个特性也存在,但并不被查询优化器识别,也就无法被利用了。

这个特性可适用于 ref, range, and index_merge 等多种索引访问方式,在稀松索引扫描(loose index scan)、联接(join)、排序以及MIN()/MA()等场景下。

我们来看看这个特性怎么被优化器识别并利用的,假设上述测试表中的测试数据有:
SELECT * FROM t;
+—-+—-+—-+—-+
| a | b | c | d |
+—-+—-+—-+—-+
| 1 | 2 | 4 | 2 |
| 1 | 3 | 2 | 2 |
| 1 | 4 | 9 | 2 |
| 1 | 5 | 9 | 2 |
| 1 | 6 | 8 | 2 |
| 2 | 2 | 9 | 2 |
| 3 | 2 | 8 | 2 |
| 4 | 2 | 6 | 2 |
| 5 | 2 | 6 | 2 |
| 6 | 2 | 1 | 2 |
+—-+—-+—-+—-+

MySQL版本:5.6.21-70.0-log Percona Server (GPL), Release 70.0, Revision 688。

假设有下面的查询,看下它的执行计划:
mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: PRIMARY,i_c
key: i_c
     key_len: 8
ref: const,const
rows: 2
Extra: Using index

在5.6.9以前的版本(或者修改优化器开关,关闭 index extensions 特性。如果用5.6.9以后的版本测试,还请记得):
mysql> DESC SELECT a,b,c FROM t WHERE a = 1 AND c = 9\G
id: 1
select_type: SIMPLE
table: t
type: ref
possible_keys: PRIMARY,i_c
key: i_c
     key_len: 4
ref: const
rows: 3
Extra: Using where;
Using index

可执行下面的命令关闭 index extensions 特性:
mysql> SET optimizer_switch = ‘use_index_extensions=off’;

这两个执行计划的区别在于:

  • 前者的key_len是8而后者是4,预示着可以用到的索引不仅是i_c这个索引,还有主键索引;
  • 前者的ref列值是const,const,而后者只有const,预示着前者用到了2个索引部分,而后者只有一个;
  • 前者评估的rows为2,而后者评估的rows为3,因为前者效率更高;
  • 后者的Extra列中多了Using Where,表示后者还需要从结果中再次过滤数据,而不能像前者那样直接利用索引取得结果。

我们还可以根据观察STATUS中的Handler_read_%值差异来对比两个SQL的实际执行代价(执行FLUSH STATUS后,执行查询SQL,再执行SHOW STATUS LIKE ‘Handler_read_%’ 查看):

  • 后者的代价是 Handler_read_next = 3;
  • 前者的代价是 Handler_read_next = 2;
  • 如果数据量更大的话,这个差值也会随之增大。

由此可见,前者的效率确实要比后者来的更高。

3、后记

我们应该经常关注新版本的新特性,利用这些新特性提升SQL效率 :)

 

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

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

FAQ系列 | 修改事务隔离级别的暗门

0、导读

我们知道,MySQL里可以动态修改事务隔离级别(TRANSACTIOIN ISOLATION LEVEL),既可以加 GLOBAL 关键字直接修改全局的设置,也可以加 SESSION 关键字只修改当前会话的设置。那么,如果两个关键字都不加,会出现什么情况呢?

1、调整事务隔离级别

MySQL里,可以直接用 SET 指令调整事务隔离级别,既可以对全局调整,也可以只调整当前会话,其用法见下:

SET [GLOBAL | SESSION] TRANSACTION

[ REPEATABLE READ

| READ COMMITTED

| READ UNCOMMITTED

| SERIALIZABLE ]

我们都知道指定 GLOBAL 关键字是可以修改全局的设置,用这个方式修改完后,已经存在的连接还不会生效,只有对新建立的连接才会起作用。

而指定 SESSION 关键字则是只修改当前连接会话的设置,修改完后立即生效,再次连接后会恢复成全局的设置,对其他连接/会话也不起作用。

另外,如果都不指定 GLOBAL、SESSION 关键字,会发生什么情况呢?官方文档给出的解释是这样的:

Without any SESSION or GLOBAL keyword, the statement applies to the next (not started) transaction performed within the current session. Subsequent transactions revert to using the SESSION isolation level.

原文详见手册:13.3.6 SET TRANSACTION Syntax,出处链接:https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html

意思是说:采用这种方式设置后,本次连接当前事务还未起作用,要到下一个(尚未启动的)新事务才起作用,下一个事务结束后,又将恢复成本次 SESSION中原先的设置

2、实验演示

为了证实上面的说法,我们可以做一个演示,详细过程见下:

T1 T2
select @@tx_isolation;

READ-COMMITTED

select @@tx_isolation;

READ-COMMITTED

set transaction isolation level SERIALIZABLE;

select @@tx_isolation;

+—————-+
| @@tx_isolation |
+—————-+
| READ-COMMITTED |

这种方式调整完后,可以看到隔离级别还是RC

select @@tx_isolation;

READ-COMMITTED

begin;

select * from t;
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

begin;

select * from t;
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

update t set d = 5 where a = 1;

被阻塞了,耐心等待中~

rollback;
Query OK, 1 row affected (13.13 sec)

Rows matched: 1  Changed: 1  Warnings: 0

等到T1执行rollback后才能继续,等了10多秒

rollback;
当前连接再次启动新事务测试
select @@tx_isolation;

READ-COMMITTED

可以看到隔离级别还是RC

select @@tx_isolation;

READ-COMMITTED

begin;

select * from t;

| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

begin;

select * from t;

| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |

update t set d = 20 where a = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

立即完成,没有阻塞

rollback;
rollback;

其中,测试表t的建表DDL:

CREATE TABLE `t` (
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
`d` int(11) NOT NULL,
PRIMARY KEY (`a`,`b`),
KEY `i_c` (`c`)
) ENGINE=InnoDB;

该表中只有一条记录:

SELECT * FROM t;

+—+—+—+—+
| a | b | c | d |
+—+—+—+—+
| 1 | 2 | 3 | 4 |
+—+—+—+—+

3、建议

如果需要全局调整事务隔离级别,最好是在 my.cnf 全局配置文件中直接设置好。

而如果是要在当前会话中临时调整,可以不要加上 SESSION 关键字,这样的话,当前事务结束下一个新事务开始后,会自行恢复 SESSION 的设置,无需人为调整,更加方便。

 

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

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

FAQ系列 | MySQL DBA修炼秘籍

0、导读

本文主要写给那些立志成为MySQL DBA,以及正在学习MySQL的同行们,结合个人及业内其他同行的职业发展经历给大家一些参考,如何成为合格的MySQL DBA。

1、什么是MySQL DBA

首先,DBA是database administrator(数据库管理员)的简称,在一些招聘网站上,也可能会把职位写成数据库[管理]工程师,MySQL DBA是目前互联网企业中最为炙手可热的岗位需求之一,前(钱)景大好,快到碗里来吧。

下面是拉勾网的MySQL DBA招聘需求(若无“MySQL中文网水印”的二维码请勿打开):
20160217-lagou-mysql

2、MySQL DBA的职责

传统意义上的DBA基本上只要管好database system就可以,一般无需关注操作系统、硬件、网络、安全、NOSQL等相关技术细节,在一些传统企业或者操作集成商里的ORACLE DBA就是这样的,最多关注到主机(小机为主)及相应的存储设备。

而MySQL一般在互联网业务中使用,MySQL DBA需要关注的方面也相应更多了,主要就是上面提到的主机硬件、OS、网络、安全、NOSQL,以及一些MySQL运维自动化开发(这不奇怪,一个好平台,通常都要自己亲自开发才顺手)的工作。

此外,随着企业规模的变化,可能在公司初期是由一些比较资深的开发工程师负责所有服务器大小事宜,当然也包括MySQL的管理及优化。随着规模的扩大,可能改由运维工程师来负责这些事了。更进一步的话,就开始需要专职的MySQL DBA了,随着业务发展,形成DBA team,同时负责和数据及存储相关的事务,比如存储设备、NOSQL、日志存储&分析,甚至大数据平台。

在一线的大型互联网公司里,甚至还区分运维DBA开发DBADB架构师等不同岗位,为的就是能做到术业有专攻,让专业的人专注做专业的事

比较理想的MySQL DBA工作状态应该是这样的:

  • 例如MySQL实例安装、备份&恢复、SLAVE搭建、权限管理、DDL&DML变更上线等基础的工作,通过DB平台鼠标点点点即可完成,大概占用10%的时间;
  • 关键业务的SQL审核也可采用DB平台来完成,尤其是一些常规的SQL规范规则,DDL相对好办,DML可能需要进一步完善的评估,个别SQL再采用人工审核,平时经常和业务部门进行沟通,了解下阶段的业务目标,预估DB端可能需要承载的压力,大概占用20-30%的时间;
  • 通过监控系统来完成可用性及性能监控,发现异常时,再进行人工干预处理,一些容易引发性能问题的常见情景,也可以固化到自动化处理机制中,比如自动探测超过N秒的纯SELECT查询,避免这种慢SQL产生连锁反应,或者自动杀掉一些有SQL注入风险的请求,大概占用10%的时间;
  • 数据库主动优化,一个有丰富经验的DBA,看到数据库的一些现场情况时,一般即可预感到是否需要进一步深入优化工作。而SQL开发规范推送也很重要,可以在开发阶段让程序猿做好基本的SQL优化,这样上线后不会手忙脚乱,反复的出现一些低级SQL性能问题,大概占用20-30%的时间;
  • 其余的时间可以用来充电学习,以及圈内的交流扯淡了,扩展知识面。

3、如何成为MySQL DBA

事实上,MySQL DBA的入门并不难,但若想要成为高级、资深的DBA就有一定难度了。

如果是在校生,最起码要先把《数据库概论》那门课程给学一遍,其实如果是已经在职但对数据库还没什么概念的人,最好也要学习下这本书,对数据库基本概念有一定理解。

此外,最好还要对Linux有一定了解,现如今在互联网公司中,如果想从事和技术相关的岗位,你告诉面试官不懂Linux为何物的话,估计直接就被pass了。想学习Linux,可以买本《鸟哥linux私房菜》或者参加专业培训(花钱参加培训并不是什么丢人的事,关键是要找到一个靠谱的机构,靠谱的老师,学习效率会更高,知识也更为系统化,而自学毕竟要消耗更多时间,也可能比较零散,花钱买时间学会后,可以更快获得回报)。

有了基础概念后,可以再买一本MySQL相关的基础入门书籍,比如《MySQL必知必会》、《深入浅出MySQL》等,其实我更建议把MySQL官方手册中的关键章节完整看一遍(关注公众号imysql_wx,发送“章节”获得推荐),并结合里面的案例进行测试,或者自己用wordpress搭一个博客站,平时可以自己做些实践演练。

如果能专注把上面的内容学习完毕,我相信你已经可以成为一个合格的初级MySQL DBA了。接下来就是找到一个合适的工作机会(可以把简历给我,我根据情况可帮忙进行推荐),进行真正的实操,获得正式从业经验。

4、MySQL DBA如何提升

事实上,如果你已经获得了MySQL DBA工作机会之后,如何进行自我提升通常来说已经不需要我来说了,可以参考公司里的同事以及其他同行的提升发展道路模式。

通常来说,在这个阶段需要深入学习的是某些关键知识点,比如数据库原理、并发事务、锁控制、存储引擎、主机硬件优化等知识。有些不错的书可以推荐,比如:《高性能MySQL》、《数据库系统实现/概念》、《数据库与事务处理》、《数据库索引设计与优化》等等。

5、后记

后记一

从目前的行业情况来看,MySQL DBA还是个很热门的职位,现在加入还不算晚。除了自学成才外,还可以考虑参加我和吴炳锡合作的“知数堂MySQL DBA实战优化”培训课程,截止目前已经举办了六期两百多名同学,个别优秀学员成功加入支付宝、京东、去哪儿、畅游、美菜网、37游戏等各大互联网公司。

后记二

关于MySQL DBA求职面试的一些关键知识点,可以查看我的这篇历史分享:MySQL DBA面试全揭秘。扫描二维码直达(若无“MySQL中文网水印”的二维码请勿打开):
20160218-3wedu

此外,也可以关注公众号imysql_wx,发送“新手”获得学习资料推荐。

后记三

此前有朋友让我推荐一些MySQL相关的技术资源,自己收藏吧:

 

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

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

FAQ系列 | SLAVE为什么一直不动了

导读

遇到SLAVE延迟很大,binlog apply position一直不动的情况如何排查?

问题描述

收到SLAVE延迟时间一直很大的报警,于是检查一下SLAVE状态(无关状态我给隐去了):

          Slave_IO_State: Waiting for master to send event
         Master_Log_File: mysql-bin.000605
     Read_Master_Log_Pos: 1194
          Relay_Log_File: mysql-relay-bin.003224
           Relay_Log_Pos: 295105
   Relay_Master_Log_File: mysql-bin.000604
        Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
              Last_Errno: 0
              Last_Error: 
     Exec_Master_Log_Pos: 294959
         Relay_Log_Space: 4139172581
   Seconds_Behind_Master: 10905

可以看到,延迟确实很大,而且从多次show slave status的结果来看,发现binlog的position一直不动。

     Read_Master_Log_Pos: 1194
          Relay_Log_File: mysql-relay-bin.003224
           Relay_Log_Pos: 295105
   Relay_Master_Log_File: mysql-bin.000604
     Exec_Master_Log_Pos: 294959
         Relay_Log_Space: 4139172581

从processlist的中也看不出来有什么不对劲的SQL在跑:

******************** 1. row ******************
     Id: 16273070
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 4828912
  State: Waiting for master to send event
   Info: NULL
********************* 2. row *****************
     Id: 16273071
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 9798
  State: Reading event from the relay log
   Info: NULL

在master上查看相应binlog,确认都在干神马事:

[yejr@imysql.com]# mysqlbinlog -vvv --base64-output=decode-rows -j 294959 mysql-bin.000604 | more

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
**# at 294959**
#160204  6:16:30 server id 1  end_log_pos 295029     **Query    thread_id=461151**    **exec_time=2144**    error_code=0
SET TIMESTAMP=1454537790/*!*/;
SET @@session.pseudo_thread_id=461151/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 295029
# at 295085
# at 296040
# at 297047
# at 298056
# at 299068
# at 300104

上面这段内容的几个关键信息:

# at 294959   — binlog起点
thread_id=461151    — master上执行的线程ID
exec_time=2144    — 该事务执行总耗时

再往下看都是一堆的binlog position信息,通过这种方式可读性不强,我们换一种姿势看看:

[yejr@imysql.com (test)]> show binlog events in 'mysql-bin.000604' from 294959 limit 10;
+------------------+--------+-------------+-----------+-------------+----------------------------+
| Log_name         | Pos    | Event_type  | Server_id | End_log_pos | Info                       |
+------------------+--------+-------------+-----------+-------------+----------------------------+
| mysql-bin.000604 | 294959 | Query       |         1 |      295029 | BEGIN                      |
| mysql-bin.000604 | 295029 | Table_map   |         1 |      295085 | table_id: 84 (bacula.File) |
| mysql-bin.000604 | 295085 | Delete_rows |         1 |      296040 | table_id: 84               |
| mysql-bin.000604 | 296040 | Delete_rows |         1 |      297047 | table_id: 84               |
| mysql-bin.000604 | 297047 | Delete_rows |         1 |      298056 | table_id: 84               |
| mysql-bin.000604 | 298056 | Delete_rows |         1 |      299068 | table_id: 84               |
| mysql-bin.000604 | 299068 | Delete_rows |         1 |      300104 | table_id: 84               |
| mysql-bin.000604 | 300104 | Delete_rows |         1 |      301116 | table_id: 84               |
| mysql-bin.000604 | 301116 | Delete_rows |         1 |      302147 | table_id: 84               |
| mysql-bin.000604 | 302147 | Delete_rows |         1 |      303138 | table_id: 84               |

+—————————+————+——————-+—————-+——————-+——————————————+

可以看到,这个事务不干别的,一直在删除数据。
这是一个Bacula备份系统,会每天自动删除一个月前的过期数据。
事实上,这个事务确实非常大,从binlog的294959开始,一直到这个binlog结束4139169218,一直都是在干这事,总共大概有3.85G的binlog要等着apply。

-rw-rw---- 1 mysql mysql 1.1G Feb  3 03:07 mysql-bin.000597
-rw-rw---- 1 mysql mysql 1.1G Feb  3 03:19 mysql-bin.000598
-rw-rw---- 1 mysql mysql 2.1G Feb  3 03:33 mysql-bin.000599
-rw-rw---- 1 mysql mysql 1.4G Feb  3 03:45 mysql-bin.000600
-rw-rw---- 1 mysql mysql 1.8G Feb  3 04:15 mysql-bin.000601
-rw-rw---- 1 mysql mysql 1.3G Feb  3 04:53 mysql-bin.000602
-rw-rw---- 1 mysql mysql 4.5G Feb  4 06:16 mysql-bin.000603
-rw-rw---- 1 mysql mysql 3.9G Feb  4 06:52 mysql-bin.000604
-rw-rw---- 1 mysql mysql 1.2K Feb  4 06:52 mysql-bin.000605

可以看到上面的历史binlog,个别情况下,一个事务里一次性要删除数据量太大了,导致binlog文件远超预设的1G,最大的达到4.5G之多。

怎么解决

由于这是Bacula备份系统内置生成的大事务,除非去修改它的源码,否则没有太好的办法。

对于我们一般的应用而言,最好是攒够一定操作后,就先提交一下事务,比如删除几千条记录后提交一次,而不是像本例这样,一个删除事务消耗了将近3.9G的binlog日质量,这种就非常可怕了。

除了会导致SLAVE看起来一直不动以外,还可能会导致某些数据行(data rows)被长时间锁定不释放,而导致大量行锁等待发生。

 

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

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

FAQ系列 | B+树索引和哈希索引的区别

导读

在MySQL里常用的索引数据结构有B+树索引和哈希索引两种,我们来看下这两种索引数据结构的区别及其不同的应用建议。

二者区别

备注:先说下,在MySQL文档里,实际上是把B+树索引写成了BTREE,例如像下面这样的写法:

CREATE TABLE t(
aid int unsigned not null auto_increment,
userid int unsigned not null default 0,
username varchar(20) not null default ‘’,
detail varchar(255) not null default ‘’,
primary key(aid),
unique key(uid) USING BTREE,
key (username(12)) USING BTREE此处 uname 列只创建了最左12个字符长度的部分索引
)engine=InnoDB;

一个经典的B+树索引数据结构见下图:
20160106B树索引
(图片源自网络)

B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针相互链接。

在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。

因此,B+树索引被广泛应用于数据库、文件系统等场景。顺便说一下,xfs文件系统比ext3/ext4效率高很多的原因之一就是,它的文件及目录索引结构全部采用B+树索引,而ext3/ext4的文件目录结构则采用Linked list, hashed B-tree、Extents/Bitmap等索引数据结构,因此在高I/O压力下,其IOPS能力不如xfs。

详细可参见:

https://en.wikipedia.org/wiki/Ext4
https://en.wikipedia.org/wiki/XFS

哈希索引的示意图则是这样的:
20160106哈希索引
(图片源自网络)

简单地说,哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快。

从上面的图来看,B+树索引和哈希索引的明显区别是:

  • 如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;
  • 从示意图中也能看到,如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
  • 同理,哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);
  • 哈希索引也不支持多列联合索引的最左匹配规则
  • B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题

后记

在MySQL中,只有HEAP/MEMORY引擎表才能显式支持哈希索引(NDB也支持,但这个不常用),InnoDB引擎的自适应哈希索引(adaptive hash index)不在此列,因为这不是创建索引时可指定的。

还需要注意到:HEAP/MEMORY引擎表在mysql实例重启后,数据会丢失。

通常,B+树索引结构适用于绝大多数场景,像下面这种场景用哈希索引才更有优势:

在HEAP表中,如果存储的数据重复度很低(也就是说基数很大),对该列数据以等值查询为主,没有范围查询、没有排序的时候,特别适合采用哈希索引

例如这种SQL:
SELECT … FROM t WHERE C1 = ?; — 仅等值查询

在大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。

 

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

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