Mysql

在前一篇，我们开启了Mysql的binlog，当Mysql在进行数据更改操作时会自动记录binlog日志，以便进行主从服务，或者恢复丢失的数据。Binlog的日志内容可以设置不同的格式，以满足不同的需求。 1. Binlog格式 Binlog有三种格式(format)，包括STATEMENT、ROW和MIXED，根据Mysql版本的不同，有着不同的默认值，它们的设置方式和默认值见下表： 属性 值 命令行格式 --binlog-format=format 系统变量 binlog_format 范围 GLOBAL, SESSION 动态 是 类型 列举 默认值（>= 5.7.7） ROW 默认值（⇐ 5.7.6） STATEMENT 有效值 ROWS TATEMENT MIXED 1.1. STATEMENT 基于SQL语句的日志记录，每一条修改数据的sql语句都会被记录在binlog中，但是不会记录数据前后的变化。 优点 只需要记录执行语句的细节和上下文环境，避免了记录每一行的变化，在一些修改记录较多的情况下相比ROW level能大大减少binlog日志量，节约IO，提高性能；还可以用于实时的还原；同时主从版本可以不一样，从服务器版本可以比主服务器版本高 缺点 为了保证sql语句能在slave上正确执行，必须记录上下文信息，以保证所有语句能在slave得到和在master端执行时候相同的结果；另外，主从复制时，存在部分函数（如sleep）及存储过程(procedure、function、及trigger)在slave上会出现与master结果不一致的情况，而相比Row level记录每一行的变化细节，绝不会发生这种不一致的情况 1.2. ROW 基于行的日志记录，不记录SQL语句，仅记录被修改的行的数据信息。 优点 能非常清晰的记录下每行数据的修改细节，不需要记录上下文相关信息，因此不会发生某些特定情况下的procedure、function、及trigger的调用触发无法被正确复制的问题，任何情况都可以被复制，且能加快从库重放日志的效率，保证从库数据的一致性 缺点 由于所有的执行的语句在日志中都将以每行记录的修改细节来记录，因此，可能会产生大量的日志内容，干扰内容也较多；比如一条update语句，如修改多条记录，则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，特别是当执行alter table之类的语句的时候，由于表结构修改，每条记录都发生改变，那么该表每一条记录都会记录到日志中，实际等于重建了表。 row模式生成的sql编码需要解码，不能用常规的办法去生成，需要加上相应的参数(--base64-output=decode-rows -v)才能显示出sql语句; 新版本(5.7.7以后)binlog默认为ROW，且5.6新增了一个参数：binlog_row_image；把binlog_row_image设置为minimal以后，binlog记录的就只是影响的列，大大减少了日志内容 1.3. MIXED STATEMENT和ROW格式的混合，默认使用STATEMENT来记录binlog，当其无法实现主从复制的操作(例如函数操作)时，会切换到ROW格式来记录binlog。 2. 对比 接下来，我们使用mysql5.7.22版本来对比一下三种格式下的日志输出情况。 ...

某些时候，一不小心删除了数据库的数据甚至某些表、库，顿时慌得一P，是不是想死的心都有了？尤其是在小公司或者小项目中，没有遵循严格的规章制度，每个开发人员都有测试数据库甚至生产库的权限，一些没有经验的程序员一顿胡乱操作，导致数据库的数据丢失的情况时有发生。此时，我们就需要用到Mysql的binlog了。 1. Binlog简介 Binlog，即binary log、二进制日志，记录了描述数据库更改的“事件”（EVENT），例如表创建操作或对表数据的更改(DELETE、UPDATE、INSERT)，通俗的说，binlog主要用来记录对mysql数据更新或潜在发生更新的SQL语句(例如DELETE语句没有删除任何行也会记录)，并以”事务”的形式保存在磁盘中Binlog；另外，它还包含有关每个语句获取更新数据的时间长度的信息。 二进制日志有两个重要目的： 数据同步：mysql master上的更改数据，通过binlog发送给slave节点，然后达到主从数据一致的目的 数据恢复：binlog为数据恢复提供了有效的手段，因为binlog记录了数据操作的内容、时间等关键信息，可以根据binlog来恢复数据 Binlog仅记录了DML和DDL，并不包括查询(SELECT和SHOW)语句。Binlog会稍微降低性能，但是对于其数据同步和数据恢复等优势而言，牺牲些许性能是值得的。 2. 开启binlog 可以在启动mysql是添加--log-bin[=base_name] 参数来启用binlog，base_name为日志文件的基础名称（base_name为空则默认使用pid-file选项的名称，该选项默认是主机名），通常建议自己设置base_name而不是使用默认的名称，或者通过修改mysql配置文件来开启。 我们看看如何修改mysql配置文件来开启binlog，环境使用的是ubuntu： 1、查看是否开启binlog 登录mysql，进入mysql命令行，输入如下命令查看： mysql> show variables like 'log_bin%' 结果如下： mysql> show variables like 'log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/log/mysql/mysql-bin | | log_bin_index | /var/log/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | +---------------------------------+--------------------------------+ 5 rows in set (0.00 sec) 如果log_bin为OFF则未开启，ON则为已开启。另外有两个重要的信息： log_bin_basename：binlog存储的文件基础名称，完整的日志文件后缀会递增为形如0000x的格式，例如mysql-bin.00001 log_bin_index: binlog索引文件的名称，该文件存储了所有的binlog日志文件的位置 ...

大批量同步数据到MySQL中，涉及到更新和新增数据，如果仅仅循环一次次插入，不仅耗费过多的网络开销，性能低下，而且频繁的获取数据库连接、操作数据库，增加数据库压力，甚至严重影响数据库性能。因此，此时我们需要批量操作，来减轻数据库压力，提升应用性能。 1. 数据准备 首先创建一张测试表，插入一些初始数据： CREATE TABLE `hello` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('1', 'zhangsan', '30'); INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('2', 'lisi', '18'); INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('3', 'wangwu', '22'); INSERT INTO `test`.`hello` (`id`, `name`, `age`) VALUES ('4', 'zhaoliu', '25'); ...

最近开发微信红包活动，并发量大概在100左右，数据库存储数据几十万条。活动上线后，遇到一个问题，服务启动起来后，在几分钟之内，服务变得很慢，通过分析tcp状态（ss -s）发现，处于timewait状态的TCP达到一千多个，然后从应用前台、后台到数据库逐步分析，最后发现，由于某一张表的数据量达到几十万，而某一个逻辑需要连接该表进行数据查询，导致SQL执行非常缓慢。于是整理下SQL慢查询分析的相关方法，以便查阅。 1. explain语句 explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，explain可以用来查看 SQL 语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。 Explain语法： explain select … from … [where ...] 例如：explain select * from news; 输出： ---- ------------- ------- ------- ------------------- --------- --------- ------- ------ ------- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ---- ------------- ------- ------- ------------------- --------- --------- ------- ------ ------- 下面对各个属性进行了解： 1、id：这是SELECT的查询序列号 2、select_type：select_type就是select的类型，可以有以下几种： SIMPLE：简单SELECT(不使用UNION或子查询等) PRIMARY：最外面的SELECT UNION：UNION中的第二个或后面的SELECT语句 DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询 UNION RESULT：UNION的结果。 SUBQUERY：子查询中的第一个SELECT DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询 DERIVED：导出表的SELECT(FROM子句的子查询) ...