每日归档： 2017年4月22日

MYSQL复制的几种模式

Mysql中的复制可以是基于语句（Statement Level）的和基于行的（RowLevael）。
从 MySQL 5.1.12 开始，可以用以下三种模式来实现：
— 基于SQL语句的复制(statement-based replication, SBR)，
— 基于行的复制(row-based replication, RBR)，
— 混合模式复制(mixed-based replication, MBR)。
相应地，binlog的格式也有三种：STATEMENT，ROW，MIXED。

在运行时可以动态的改变binlog的格式，除了以下几种情况：
. 存储过程或者触发器中间
. 启用了NDB(Mysql cluster 主要采取的存储引擎)
. 当前会话使用 RBR 模式，并且已打开了临时表

如果binlog采用了 MIXED 模式，那么在以下几种情况下会自动将binlog的模式由 SBR 模式改成RBR 模式。
. 当DML语句更新一个NDB(Mysql cluster 主要采取的存储引擎)表时
. 当函数中包含 UUID() 时
. 2个及以上包含 AUTO_INCREMENT 字段的表被更新时
. 行任何 INSERT DELAYED 语句时
. 用 UDF (User Definition Function)时
. 视图中必须要求使用 RBR 时，例如创建视图是使用了UUID() 函数

设定主从复制模式的方法非常简单，只要在以前设定复制配置的基础上，再加一个参数：
binlog_format="STATEMENT" 
#binlog_format="ROW"
#binlog_format="MIXED"
当然了，也可以在运行时动态修改binlog的格式。例如
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

两种模式各自的优缺点：

SBR 的优点：

1．技术比较成熟

2. binlog文件较小，log文件可读

3. binlog中包含了所有数据库更改信息，可以据此来审核数据库的安全等情况

4. binlog可以用于实时的还原，而不仅仅用于复制

5. 主从版本可以不一样，从服务器版本可以比主服务器版本高

SBR 的缺点：

1. 不是所有的UPDATE语句都能被复制，尤其是包含不确定操作的时候。

2. 调用具有不确定因素的 UDF 时复制也可能出问题
使用以下函数的语句也无法被复制：
* LOAD_FILE()
* UUID()
* USER()
* FOUND_ROWS()
* SYSDATE() (除非启动时启用了 –sysdate-is-now 选项)

3. 对于有 AUTO_INCREMENT 字段的 InnoDB表而言，INSERT 语句会阻塞其他 INSERT 语句，对于一些复杂的语句，在从服务器上的耗资源情况会更严重，而 RBR 模式下，只会对那个发生变化的记录产生影响。以为需要记录上下文信息所以存储函数(不是存储过程，mysql内部存储的函数)在被调用的同时也会执行一次 NOW() 函数。

4. 确定了的 UDF 也需要在从服务器上执行
数据表必须几乎和主服务器保持一致才行，否则可能会导致复制出错
执行复杂语句如果出错的话，会消耗更多资源
 

RBR 的优点：

1. 任何情况都可以被复制，这对复制来说是最安全可靠的

2. 如果从服务器上的表如果有主键的话，复制就会快了很多

3. 从服务器上采用多线程来执行复制成为可能

4.  不会出现某些特定情况下的存储过程，或function，以及trigger 的调用和触发无法被正确复制的问题。

RBR 的缺点：

1. 相比SBR的binlog 大了很多，还原的时候可能比较慢

2. 复杂的回滚时 binlog 中会包含大量的数据

3  因为RBR是基于行级的，所以如果有alter table 之类的DDL操作产生的数据量非常大。(GRANT，REVOKE，SET PASSWORD等语句建议使用SBR模式记录).

4.  当在非事务表上执行一段堆积的SQL语句时，最好采用 SBR 模式，否则很容易导致主从服务器的数据不一致情况发生
5.  采取RBR模式记录的话，log是不可读的。（经过加密）

注：采用 RBR 模式后，能解决很多原先出现的主键重复问题。

MySQL中可以有二类用于生成唯一值性质的工具：UUID()函数和自增序列，那么二者有何区别呢？我们就此对比下各自的特性及异同点：

    1.都可以实现生成唯一值的功能；

    2.UUID是可以生成时间、空间上都独一无二的值；自增序列只能生成基于表内的唯一值，且需要搭配使其为唯一的主键或唯一索引；

    3.实现方式不一样，UUID是随机+规则组合而成的，而自增序列是控制一个值逐步增长的；

    4.UUID产生的是字符串类型值，固定长度为：36个字符，而自增序列产生的是整数类型值，长度由字段定义属性决定；

接下来，详细讲解下UUID()函数产生的值：

root@localhost : (none) 06:09:40> SELECT UUID(),LENGTH(UUID()),CHAR_LENGTH(UUID())\G

从上面的执行结果部分的信息看：

1.同一个SQL语句中，多处调用UUID()函数得到的值不相同；

2.得到的随机值由5个部分组成，且分隔符位为：中划线；

3.多次调用或执行得到的后2组值相同，若把mysqld服务器关闭，重新启动之后，会发现第四组的组与未重启前的值发生变化，然后一直不变化，只要重新启动mysqld服务就会发生变化。另外，对于同一台机器，第五组值始终不会发生变化；

4.字符个数为：36，占字节数为：36（注：系统默认字符集编码：utf8）；

针对UUID产生的值组成部分，作如下解说：

1. 前三组值是时间戳换算过来的；

2. 第四组值是暂时性保持时间戳的唯一性。例如，使用夏令时；

3. 第五组值是一个IEE 802的节点标识值，它是空间上唯一的。若后者不可用，则用一个随机数字替换。假如主机没有网卡，或者我们不知道如何在某系统下获得机器地址，则空间唯一性就不能得到保证，即使这样，出现重复值的机率还是非常小的。

UUID函数对复制的支持：

UUID函数属于不确定性函数，为此不支持MySQL 复制的STATEMENT模式，但是支持MIXED、ROW二种模式，大家可以设置2组测试模式，以5.1.系列版本为例。

测试基于命令行模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = STATEMENT

测试基于命令行/混合模式复制：

tx_isolation = REPEATABLE-READ

binlog_format = MIXED  OR ROW

在主服务器上执行同一个SQL语句：

INSERT INTO  test_uuid(username) VALUES(UUID());

然后再比对主从服务器上表中存储的值，会发现基于命令行模式的：主从不一致，基于行/混合模式的：主从数据时一致；

建议：在复制模式下，需要用到UUID()函数，则一定要使用基于行/混合模式复制方式。

名词解释：

对于输入参数相同，且同一时间执行或一个SQL中多处调用，而得到不同值得函数，我们就称其为：不确定性函数。

备注：

在MySQL 5.1.*及更高版本有一个变种的UUID()函数，名称：UUID_SHORT()，生成一个64位无符号的整数，例如：

root@localhost : (none) 02:46:42> SELECT UUID_SHORT()\G

*************************** 1. row ***************************

UUID_SHORT(): 6218676250261585921

1 row in set (0.00 sec)

UUID()函数产生的值，并不适合作为InnoDB引擎表的主键，至于详细的原因，请阅读文章InnoDB引擎表的主键选型。

在mysql中，可以使用uuid 来生成主键，但是用mysql的uuid()函数 ，生成的uuid是36位的，其中包含32个字符以及4个分隔符（-），往往这个分隔符对我们来说是没有用的，可以使用mysql自带的replace函数去掉分隔符

replace(uuid(),'-','')   —->将uuid()中的‘-’，去掉，即替换成空串；

此外

upper(replace(uuid(),'-',''))用于将字符转换为大写

MySQL采用开放可插入式存储引擎架构，提供类似电源插线板的功能，其后接入的存储引擎就类似电器设备，而我们大家常用的存储以MyISAM和InnoDB为主，早期大家主要使用MyISAM引擎支持业务，随MySQL支持业务范围越来越广，存储的数据对企业越来越重要，尤其PC服务器支持的最大内存越来越大，内存的价格也越来越便宜，逐渐采用InnoDB引擎为主.二种风格迥异的存储引擎，各自内部存储算法和数据操纵实现等都竞相不同，另外InnoDB引擎与其他商业数据库产品存储引擎也不太相同，为此我们必须根据使用的存储引擎特点，设计合理的数据存储结构和数据操纵方式。本文将围绕InnoDB存储引擎的主键设计而展开，告诉大家怎样设计表的主键才是合理的做法。

讨论InnoDB引擎表的主键选型的要求之前，我们大家先简单温习下InnoDB引擎表的元数据和索引数据存储结构特点。

InnoDB引擎表的数据和索引都是存储在同一个文件中，InnoDB引擎的页大小默认为16K，且页空间使用率为15/16，为此每页能存储的数据量是有限的。主键和数据的关系是数据存储在簇索引的叶子节点中，接下来我们看下主键和数据的组织结构关系，如图1-1：

对于非簇索引，又是如何存储的呢？通过翻译官方文章，得知：

每个非簇索引的叶子节点存储的数据都包含簇索引的值，然后通过簇索引的值，可以查找到对应的元数据，我们继续看一下非簇索引的存储结构，以及与簇索引之间的关系，如图1-2：

紧接着，我们来理清楚InnoDB引擎表的簇索引为哪三类：

1.  主键也即是我们文章说的簇索引；

2.  若表中无主键，但是存在唯一索引，且字段定义为非空，则作为簇索引；

3.  表无主键，也无非空唯一索引，则内部默认隐含性创建一个长度为6个字节的字段作为簇索引字段；

mysql中每个表都有一个聚簇索引（clustered index ），除此之外的表上的每个非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

通过上述探讨和分析，弄清楚了数据存储页的空间值和利用率、簇索引和非簇索引的存储结构，以及簇索引和元数据、簇索引和非簇索引的存储关系，以及簇索引为哪三类，那么我们建议大家一定要为InnoDB引擎表创建一个主键，没必要搞一个唯一性索引且字段定义属性非空，而不创建主键的表结构，我们再分析优秀主键具备的素质：值域范围够用且存储长度越短、值的唯一性和易比较性、数值的有序性增加，三个素质的各自优点，单独详细分析。

主键的值域够用且长度越短，将产生三个方面的优势

(1).     相同数据行数的表，数据容量越小，占用磁盘空间越少，为此可以节约物理或逻辑IO和减少内存占用；

(2).     普通索引的长度也将更短，可以减少通过普通索引搜索数据的物理或逻辑IO；

(3).     减少索引数据存储的页块裂变，从而提高页的利用率，以及减少物理IO；

主键的唯一性

(1).     主键的值要求必须唯一，且非空；

(2).     主键值每次插入或修改，都必须判断是否有相同值，为此减少索引值需要比对的长度，可以提高性能，或者间接地让其转换成比对数值大小的方式，提高其比对判断的效率；

主键值插入的有序性，将产生二个方面的优势

(1).     主键存储的块与块之间是有序的，然后块内有也是有序的，主键值的有序插入，可以减少块内的排序，节约磁盘物理IO；

(2).     主键值的有序性，可以提高数据舒顺序取速度，提高服务器的吞吐量，也可以节约物理IO；

上述讨论的数据存储结构及关系，以及主键字段要求的素质等理论知识之外，我们还需要考虑实际生产环境的业务、架构等综合因素，我们实际生产环境可能会使用四类属性作为主键：

(1).     自增序列；

(2).     UUID()函数生成的随机值；

(3).     用户注册的唯一性帐号名称，字符串类型，一般长度为：40个字符；

(4).     基于一套机制生成类似自增的值，比如序列生成器；

那么我们接下来，再分析下这四类属性各自作为表主键的优缺点：

(1).     自增序列：从小到大 或从大到小的顺序模式增加新值；数据类型也利于进行主键值比较；存储空间占用也相对最小，一般设置为：4个字节的INT类型或 8个字节的BIGINT类型；若是想进行数据水平拆分的话，也可以借助设置mysqld实例的2个参数：auto_increment_increment 和 auto_increment_offset；另外，唯一缺点就是自增序列是一个表级别的全局锁，在5.0系列大规模并发写的时候，因锁释放机制的问题容易出现瓶颈，但是5.1系列做了改进，基本上不存在此问题；

(2).     UUID()函数：值为随机性+固定部分，其值产生是无序的，且同一台服务器上产生的值相同部分为77.8%；产生的值字符个数为36，按utf8编码计算，占用的存储空间为36个字节；对于数据水平拆分支持，无需特殊设置；

(3).     使用用户注册的帐号名称，字符串类型，其值的产生依赖用户输入，为此数据基本上为无序增加，字符串的长度也是不定的，只能通过前段技术控制最短最大长度值的限制，对水平拆分支持，无需做特殊设置；

(4).     序列生成器的架构，类似自增序列，不过需要借助额外的开发工作量，以及提供一个第三方的服务，可以规避自增序列的字增全局锁的问题，提高并发，对数据水平拆分可以更好地支持；

(5).     双主复制架构的概率性碰到的场景：主服务器的数据执行成功，而没有复制到在线备用服务器时，出问题的概率确实存在，其他类型的做法，也必须人工干涉解决，都无简单且合理的自动化办法，以上四种办法都无法规避；

通过四种属性值作为主键的优缺点分析，以及对比前面我们阐述的主键需要的优秀素质，若是不考虑水平拆分的问题，带来额外设置上的麻烦，则自增序列是最佳的主键字段选择；用户的注册帐号本身要求唯一性且非空的场景下，则可以作为主键字段的选择；若是考虑水平拆分的问题，则采用自增序列生成器的架构，非常易用和可靠的实现方式，产生的值是最佳主键字段的选择；

【结束】

使用什么类型的字段属性作为主键，最关键核心的要考虑存储引擎：如何存储元数据、如何检索元数据、如何维护其内部的索引组织结构，以及我们要实现的业务是什么，最后共同决定我们，如何设计一张用于存储数据的表，以及决定操纵数据的SQL语句如何编写，再结合业务特点就决定了我们的索引如何创建，建议大家多关注InnoDB引擎内部实现原理和机制，可以阅读官方提供的一个文档InnoDB引擎内部实现，以及多分析和关注业务特点。

聚簇索引是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致。
一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。

在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的：
聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。

因此，MySQL中不同的数据存储引擎对聚簇索引的支持不同就很好解释了。
下面，我们可以看一下MYSQL中MYISAM和INNODB两种引擎的索引结构。

如原始数据为：

MyISAM引擎的数据存储方式如图：

MYISAM是按列值与行号来组织索引的。它的叶子节点中保存的实际上是指向存放数据的物理块的指针。
从MYISAM存储的物理文件我们能看出，MYISAM引擎的索引文件（.MYI）和数据文件(.MYD)是相互独立的。

而InnoDB按聚簇索引的形式存储数据，所以它的数据布局有着很大的不同。它存储数据的结构大致如下：

注：聚簇索引中的每个叶子节点包含主键值、事务ID、回滚指针(rollback pointer用于事务和MVCC）和余下的列(如col2)。

INNODB的二级索引与主键索引有很大的不同。InnoDB的二级索引的叶子包含主键值，而不是行指针(row pointers)，这减小了移动数据或者数据页面分裂时维护二级索引的开销，因为InnoDB不需要更新索引的行指针。其结构大致如下：

INNODB和MYISAM的主键索引与二级索引的对比：

InnoDB的的二级索引的叶子节点存放的是KEY字段加主键值。因此，通过二级索引查询首先查到是主键值，然后InnoDB再根据查到的主键值通过主键索引找到相应的数据块。而MyISAM的二级索引叶子节点存放的还是列值与行号的组合，叶子节点中保存的是数据的物理地址。所以可以看出MYISAM的主键索引和二级索引没有任何区别，主键索引仅仅只是一个叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不设主键。

参考资料：
高性能MYSQL

5.0开始有字符集，5.1，5.5, 5.6 ,5.7

５.５以后多了一个utf8mb4 支持emuj表情

1. 能根据选择的字符集把数据库的字符集配置正确

2.如果到升级或是迁移乱，能正确的转码（４.０升级5.0 ）

字符集是一套符号和编码的规则，字符串都必须有相应的字符集

校验集是这套符号和编码的校验规则，定义字符排序规则，字符串之间比较的规则

XXX_bin

将字符串中的每一个字符用二进制数据存储，区分大小写

xxx_general_ci

ci为case insensitive

不区大小写

XXX_general_cs　

cs为case sensitive

区分大小写

结果集的字符集

•
字符集支持多层面：服务器层（server）、数据库层（database）、数据表（table）、字段（column）、连接（connection）、结果集（result）

创建的库对应server的字符集

结果集是对应client的字符集

字符集可以对库，表，字段

show create database 

show create table 

连接进来的字符集可以运态的修改：

SET NAMES latin1/gb2312/utf8;

character_set_system永远是utf8的

character_set_filesystem 是binary的

了解一下常用的字符集

latin1,utf8, gbk,gbk2312

• gbk/gb2312
•
gb2312是双字节字符集，不论中、英文字符均使用双字节来表示，为了区分中文，将其最高位都设定成1
•
gb2312是gbk的子集，gbk是gb18030的子集
• gbk包括中日韩字符的大字符集合
• 通常使用gbk字符集足够
•
国际通用性比utf8差，不过utf8占用的数据库比gbk大（三字节字符集）
•
gbk、gb2312等字符集与utf8之间都必须通过Unicode编码才能相互转换：

用gbk是可以节省一个字节的

不可见字符

字符转换

gbk、gb2312 ->Unicode ->utf8
utf8 ->Unicode  ->gbk、gb2312

• utf8
• 8-bit Unicode Transformation Format，三字节字符集
•
是Unicode的一种存储方式，可变长度字符编码，又称万国码
•
UTF-8使用可变长度字节来储存 Unicode字符，例如ASCII字母继续使用1个字节储存，重音文字、希腊字母或西里尔字母等使用2个字节来储存，而常用的汉字就要使用3个字节
•
数据库字符集尽量使用utf8(客户端连接(connect)及结果(result)字符集也采用utf8字符集，最终HTML页面亦是采用utf-8)，是的数据能很顺利的实现迁移及多种终端展示

char(30)存30个字母或是30个汉字占用的长度是一样的吗？

字母是ascii码　一个字节，30个汉字是90个字节

UTF-8使用可变长度字节来储存 Unicode字符，例如ASCII字母继续使用1个字节储存，重音文字、希腊字母或西里尔字母等使用2个字节来储存，而常用的汉字就要使用3个字节

CHAR(30)在UTF-8字符集下，最多可以写入几个英文(ASCII码)？

30个

CHAR(30)，GBK字符集，存储了30个汉字

60个

CHAR(30)，GBK字符集，存储了30个英文，字节数应该是

30个字母都是ascii码

varchar最大长度是65355字节

超过255后2个字节做长度记录

varchar(N) utf8;

(65535-2)/3;

GBK

(65535-2)/2

能多存1W多个汉字

选择合适的字符集

什么都不知道时，采用系统默认的字符集（latin1），因为在存储层，它可兼容任何编码的字节流，并且节省空间，唯一不足是检索结果不够精确

非常肯定只有中文终端用户时，可选择gbk/gb2312

为了方便数据迁移，以及多终端展示，最好是用utf8

中文字段创建索引进行where做比较可能不准 可以转成二进制进行比较

使用中文字符集的： taobao.com , qq.com

使用utf-8的： baidu.com , weibo.com

思考一下项目中如何选择字符集？

如果可以控制网站上的展示形态，可以考虑用gbk或是更小的gb2312字符集（一方面字符集比较小，另一方面占位比较少，传统中比较少带宽）

IDC 1M 　300以上的

如果控制不了用户的输入： 如webibo.com 或是基于用户名（可以有汉字的系统）一定要使用utf8

一哥们可能失恋了 写了个程序修改签名 一分钟几百次

一条记录大量update 把mysql占满了

限制某个用户触发某个指令的次数

对于同一条记录大量的update怎么处理？

偷菜游戏狂点偷菜积分满天飞

更新合并

update server

如果在内存就在内存更新

如果不在内存就用update server拉到内存里

设置更新频率，2分钟做一次更新到库

最后更新的数据 为准

数据库的字符集设置方法

字符集继承

只要设置最上层字符集就可以

default-character-set = gbk

%s/gbk/utf8/g

show [global] variables  '%char%';

set global  xxxx;

全局级对现在会话不受影响，要重启才会起作用

set xxx;

设置当前会话

set global  character_set_server=gbk;

show global variables like "%char%";
+————————–+—————————————————————+
| Variable_name            | Value                                                         |
+————————–+—————————————————————+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | gbk                                                           |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /opt/mysql/mysql-5.6.22-linux-glibc2.5-x86_64/share/charsets/ |
+————————–+—————————————————————+

show variables like "%char%";
+————————–+—————————————————————+
| Variable_name            | Value                                                         |
+————————–+—————————————————————+
| character_set_client     | utf8                                                          |
| character_set_connection | utf8                                                          |
| character_set_database   | utf8                                                          |
| character_set_filesystem | binary                                                        |
| character_set_results    | utf8                                                          |
| character_set_server     | utf8                                                          |
| character_set_system     | utf8                                                          |
| character_sets_dir       | /opt/mysql/mysql-5.6.22-linux-glibc2.5-x86_64/share/charsets/ |
+————————–+—————————————————————+

会话内的并没有改变

字符集转换

latin1转换到utf8

gbk转换到utf8

latin1 或是gbk 我的secureCRT字符集设置成什么？

linux LANG 怎么设置

locale -a |grep gb

export LANG=zh_CN.gbk

转码工具：iconv 

mysqldump –default-character-set=latin1 -hlocalhost -uroot -B my_db –tables old_table &get; old.sql

iconv -f encoding -t encoding inputfile

iconv -f ISO88592 -t UTF8 < input.txt &get; output.txt

iconv -t utf-8 -f gb2312 -c old.sql &get; new.sql数据库按utf-8配置，导回去就行了

iconv -t utf-8 -c old.sql &get;new.sql

iconv -t utf-8 < old.sql &get;new.sql

转完后要检查

set names 
set character_set_client

转完了还会按申明去写入还会把字符集写乱

对于数据库中字符集中其它注意事项
1.
where条件中字符后面比较字段尽量不要出现汉字， 如：
select c1,c2, c3 from tb where UserName=’金磊’;
如果出现需要考虑使用：
select c1, c2, c3 from tb where UserName = (‘金磊’ collate XXX_bin);

2.
在查询中order by 的列里字符最好不要用汉字，如：
select c1, c2, c3 from tb order by UserName;

如果你指定了字符集：

所有的操作请指定字符集

100多Ｇ的库gbk转成utf8怎么做？

这个过程有可能需要６个小时最后发现没成功是不是杯具了？

核心问题想把验证的过程缩短

1.表结构很好搞定吧

2.是不是把某个有中文的表取出来１万行去验证一下

mysqldump –single-transaction  –set-charset=utf8|GBK  DBname TBname –where="id<10000" &get;db_tb_id_lt_1w.sql

iconv -t utf8 -f gbk <db_tb_id_lt_1w.sql &get;utf_db_tb_id_lt_1w.sql

测试

1.建一个utf8的库
2.创建一个表要包含有字符型的

3.写一些数据，要有汉字的

试试弄到gbk库里

能正确的查看到

( … collate UTF-8-bin)