Tag Archives: mysql

一份非常完整的MySQL规范

一、数据库命令规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)
  • 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符
  • 临时库表必须以tmp_为前缀并以日期为后缀,备份表必须以bak_为前缀并以日期(时间戳)为后缀
  • 所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)

二、数据库基本设计规范

1、所有表必须使用Innodb存储引擎

没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5.6以后默认的为Innodb)Innodb 支持事务,支持行级锁,更好的恢复性,高并发下性能更好

2、数据库和表的字符集统一使用UTF8

兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效

3、所有表和字段都需要添加注释

使用comment从句添加表和列的备注 从一开始就进行数据字典的维护

4、尽量控制单表数据量的大小,建议控制在500万以内

500万并不是MySQL数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题

可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小

5、谨慎使用MySQL分区表

分区表在物理上表现为多个文件,在逻辑上表现为一个表 谨慎选择分区键,跨分区查询效率可能更低 建议采用物理分表的方式管理大数据

6、尽量做到冷热数据分离,减小表的宽度

MySQL限制每个表最多存储4096列,并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO) 更有效的利用缓存,避免读入无用的冷数据 经常一起使用的列放到一个表中(避免更多的关联操作)

7、禁止在表中建立预留字段

预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型,所以无法选择合适的类型 对预留字段类型的修改,会对表进行锁定

8、禁止在数据库中存储图片,文件等大的二进制数据

通常文件很大,会短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量的随机IO操作,文件很大时,IO操作很耗时 通常存储于文件服务器,数据库只存储文件地址信息

9、禁止在线上做数据库压力测试

10、禁止从开发环境,测试环境直接连接生成环境数据库

三、数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型

  • 原因

列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的IO次数也就越多, 索引的性能也就越差

  • 方法

1)将字符串转换成数字类型存储,如:将IP地址转换成整形数据。

mysql提供了两个方法来处理ip地址:

inet_aton 把ip转为无符号整型(4-8位)

inet_ntoa 把整型的ip转为地址

插入数据前,先用inet_aton把ip地址转为整型,可以节省空间。显示数据时,使用inet_ntoa把整型的ip地址转为地址显示即可。

2)对于非负型的数据(如自增ID、整型IP)来说,要优先使用无符号整型来存储

因为:无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647

UNSIGNED INT 0~4294967295

VARCHAR(N)中的N代表的是字符数,而不是字节数

使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存

2、避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据

  • 建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。

而且对于这种数据,Mysql还是要进行二次查询,会使sql性能变得很差,但是不是说一定不能使用这样的数据类型。

如果一定要使用,建议把BLOB或是TEXT列分离到单独的扩展表中,查询时一定不要使用select * 而只需要取出必要的列,不需要TEXT列的数据时不要对该列进行查询。

  • TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的,所以TEXT类型只能使用前缀索引,并且TEXT列上是不能有默认值的。

3、避免使用ENUM类型

  • 修改ENUM值需要使用ALTER语句
  • ENUM类型的ORDER BY操作效率低,需要额外操作
  • 禁止使用数值作为ENUM的枚举值

4、尽可能把所有列定义为NOT

原因:

  • 索引列需要额外的空间来保存,所以要占用更多的空间;
  • 进行比较和计算时要对值做特别的处理

5、使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

TIMESTAMP 占用4字节和INT相同,但比INT可读性高

超出TIMESTAMP取值范围的使用DATETIME类型存储。

经常会有人用字符串存储日期型的数据(不正确的做法):

  • 缺点1:无法用日期函数进行计算和比较
  • 缺点2:用字符串存储日期要占用更多的空间

6、同财务相关的金额类数据必须使用decimal类型

  • 非精准浮点:float,double
  • 精准浮点:decimal

Decimal类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

四、索引设计规范

1、限制每张表上的索引数量,建议单张表索引不超过5个

索引并不是越多越好!索引可以提高效率同样可以降低效率。

索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。

因为mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,同样会降低查询性能。

2、禁止给表中的每一列都建立单独的索引

5.6版本之前,一个sql只能使用到一个表中的一个索引,5.6以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好

3、每个Innodb表必须有个主键

Innodb是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。

每个表都可以有多个索引,但是表的存储顺序只能有一种 Innodb是按照主键索引的顺序来组织表的。

不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引) 不要使用UUID、MD5、HASH、字符串列作为主键(无法保证数据的顺序增长)。

主键建议使用自增ID值。

五、常见索引列建议

  • 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
  • 包含在ORDER BY、GROUP BY、DISTINCT中的字段

    并不要将符合1和2中的字段的列都建立一个索引,通常将1、2中的字段建立联合索引效果更好

  • 多表join的关联列

六、如何选择索引列的顺序

建立索引的目的是:希望通过索引进行数据查找,减少随机IO,增加查询性能 ,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。

  • 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数);
  • 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO性能也就越好);
  • 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。

七、避免建立冗余索引和重复索引

因为这样会增加查询优化器生成执行计划的时间。

  • 重复索引示例:primary key(id)、index(id)、unique index(id)
  • 冗余索引示例:index(a,b,c)、index(a,b)、index(a)

八、优先考虑覆盖索引

对于频繁的查询优先考虑使用覆盖索引。

覆盖索引:就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

覆盖索引的好处:

  • 避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的,对于Innodb来说,二级索引在叶子节点中所保存的是行的主键信息,

如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了IO操作,提升了查询效率。

  • 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的,对于IO密集型的范围查找来说,对比随机从磁盘读取每一行的数据IO要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

九、索引SET规范

尽量避免使用外键约束

  • 不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引;
  • 外键可用于保证数据的参照完整性,但建议在业务端实现;
  • 外键会影响父表和子表的写操作从而降低性能。

十、数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划,减少SQL编译所需要的时间,还可以解决动态SQL所带来的SQL注入的问题 只传参数,比传递SQL语句更高效 相同语句可以一次解析,多次使用,提高处理效率。

2、避免数据类型的隐式转换

隐式转换会导致索引失效。如:select name,phone from customer where id = 111;

3、充分利用表上已经存在的索引

  • 避免使用双%号的查询条件。

如a like %123%,(如果无前置%,只有后置%,是可以用到列上的索引的)

  • 一个SQL只能利用到复合索引中的一列进行范围查询

如:有 a,b,c列的联合索引,在查询条件中有a列的范围查询,则在b,c列上的索引将不会被用到,在定义联合索引时,如果a列要用到范围查找的话,就要把a列放到联合索引的右侧。

  • 使用left join或 not exists来优化not in操作

因为not in 也通常会使用索引失效。

4、数据库设计时,应该要对以后扩展进行考虑

5、程序连接不同的数据库使用不同的账号,进制跨库查询

  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免权限过大而产生的安全风险

6、禁止使用SELECT * 必须使用SELECT <字段列表> 查询

原因:

  • 消耗更多的CPU和IO以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响

7、禁止使用不含字段列表的INSERT语句

如:insert into values (a,b,c);

应使用insert into t(c1,c2,c3) values (a,b,c);

8、避免使用子查询,可以把子查询优化为join操作

通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因:

  • 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响;
  • 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;
  • 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。

9、避免使用JOIN关联太多的表

对于Mysql来说,是存在关联缓存的,缓存的大小可以由join_buffer_size参数进行设置。

在Mysql中,对于同一个SQL多关联(join)一个表,就会多分配一个关联缓存,如果在一个SQL中关联的表越多,所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作,同时join_buffer_size设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说,会产生临时表操作,影响查询效率Mysql最多允许关联61个表,建议不超过5个。

10、减少同数据库的交互次数

数据库更适合处理批量操作 合并多个相同的操作到一起,可以提高处理效率

11、对应同一列进行or判断时,使用in代替or

in的值不要超过500个in操作可以更有效的利用索引,or大多数情况下很少能利用到索引。

12、禁止使用order by rand 进行随机排序

会把表中所有符合条件的数据装载到内存中,然后在内存中对所有数据根据随机生成的值进行排序,并且可能会对每一行都生成一个随机值,如果满足条件的数据集非常大,就会消耗大量的CPU和IO及内存资源。

推荐在程序中获取一个随机值,然后从数据库中获取数据的方式

13、WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引。

  • 不推荐:

where date(create_time)=20190101

  • 推荐:

where create_time >= 20190101 and create_time < 20190102

14、在明显不会有重复值时使用UNION ALL而不是UNION

  • UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
  • UNION ALL不会再对结果集进行去重操作

15、拆分复杂的大SQL为多个小SQL

  • 大SQL:逻辑上比较复杂,需要占用大量CPU进行计算的SQL
  • MySQL:一个SQL只能使用一个CPU进行计算
  • SQL拆分后可以通过并行执行来提高处理效率

十一、数据库操作行为规范

1、超100万行的批量写(UPDATE、DELETE、INSERT)操作,要分批多次进行操作

  • 大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟,大批量的写操作一般都需要执行一定长的时间,而只有当主库上执行完成后,才会在其他从库上执行,所以会造成主库与从库长时间的延迟情况

  • binlog日志为row格式时会产生大量的日志

大批量写操作会产生大量日志,特别是对于row格式二进制数据而言,由于在row格式中会记录每一行数据的修改,我们一次修改的数据越多,产生的日志量也就会越多,日志的传输和恢复所需要的时间也就越长,这也是造成主从延迟的一个原因。

  • 避免产生大事务操作

大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞,阻塞会对MySQL的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接,这会使生产环境中的其他应用无法连接到数据库,因此一定要注意大批量写操作要进行分批。

2、对于大表使用pt-online-schema-change修改表结构

  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎,会造成严重的锁表操作,尤其是生产环境,是不能容忍的。

pt-online-schema-change它会首先建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器。

把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原来的表删除掉。

把原来一个DDL操作,分解成多个小的批次进行。

3、禁止为程序使用的账号赋予super权限

当达到最大连接数限制时,还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。

4、对于程序连接数据库账号,遵循权限最小原则

程序使用数据库账号只能在一个DB下使用,不准跨库 程序使用的账号原则上不准有drop权限。

一、数据库命令规范

  • 所有数据库对象名称必须使用小写字母并用下划线分割
  • 所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时,需要将其用单引号括起来)
  • 数据库对象的命名要能做到见名识意,并且最后不要超过32个字符
  • 临时库表必须以tmp_为前缀并以日期为后缀,备份表必须以bak_为前缀并以日期(时间戳)为后缀
  • 所有存储相同数据的列名和列类型必须一致(一般作为关联列,如果查询时关联列类型不一致会自动进行数据类型隐式转换,会造成列上的索引失效,导致查询效率降低)

二、数据库基本设计规范

1、所有表必须使用Innodb存储引擎

没有特殊要求(即Innodb无法满足的功能如:列存储,存储空间数据等)的情况下,所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam,5.6以后默认的为Innodb)Innodb 支持事务,支持行级锁,更好的恢复性,高并发下性能更好

2、数据库和表的字符集统一使用UTF8

兼容性更好,统一字符集可以避免由于字符集转换产生的乱码,不同的字符集进行比较前需要进行转换会造成索引失效

3、所有表和字段都需要添加注释

使用comment从句添加表和列的备注 从一开始就进行数据字典的维护

4、尽量控制单表数据量的大小,建议控制在500万以内

500万并不是MySQL数据库的限制,过大会造成修改表结构,备份,恢复都会有很大的问题

可以用历史数据归档(应用于日志数据),分库分表(应用于业务数据)等手段来控制数据量大小

5、谨慎使用MySQL分区表

分区表在物理上表现为多个文件,在逻辑上表现为一个表 谨慎选择分区键,跨分区查询效率可能更低 建议采用物理分表的方式管理大数据

6、尽量做到冷热数据分离,减小表的宽度

MySQL限制每个表最多存储4096列,并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率(表越宽,把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO) 更有效的利用缓存,避免读入无用的冷数据 经常一起使用的列放到一个表中(避免更多的关联操作)

7、禁止在表中建立预留字段

预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型,所以无法选择合适的类型 对预留字段类型的修改,会对表进行锁定

8、禁止在数据库中存储图片,文件等大的二进制数据

通常文件很大,会短时间内造成数据量快速增长,数据库进行数据库读取时,通常会进行大量的随机IO操作,文件很大时,IO操作很耗时 通常存储于文件服务器,数据库只存储文件地址信息

9、禁止在线上做数据库压力测试

10、禁止从开发环境,测试环境直接连接生成环境数据库

三、数据库字段设计规范

1、优先选择符合存储需要的最小的数据类型

  • 原因

列的字段越大,建立索引时所需要的空间也就越大,这样一页中所能存储的索引节点的数量也就越少也越少,在遍历时所需要的IO次数也就越多, 索引的性能也就越差

  • 方法

1)将字符串转换成数字类型存储,如:将IP地址转换成整形数据。

mysql提供了两个方法来处理ip地址:

inet_aton 把ip转为无符号整型(4-8位)

inet_ntoa 把整型的ip转为地址

插入数据前,先用inet_aton把ip地址转为整型,可以节省空间。显示数据时,使用inet_ntoa把整型的ip地址转为地址显示即可。

2)对于非负型的数据(如自增ID、整型IP)来说,要优先使用无符号整型来存储

因为:无符号相对于有符号可以多出一倍的存储空间

SIGNED INT -2147483648~2147483647

UNSIGNED INT 0~4294967295

VARCHAR(N)中的N代表的是字符数,而不是字节数

使用UTF8存储255个汉字 Varchar(255)=765个字节。过大的长度会消耗更多的内存

2、避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据

  • 建议把BLOB或是TEXT列分离到单独的扩展表中

Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,在排序等操作时,就不能使用内存临时表,必须使用磁盘临时表进行。

而且对于这种数据,Mysql还是要进行二次查询,会使sql性能变得很差,但是不是说一定不能使用这样的数据类型。

如果一定要使用,建议把BLOB或是TEXT列分离到单独的扩展表中,查询时一定不要使用select * 而只需要取出必要的列,不需要TEXT列的数据时不要对该列进行查询。

  • TEXT或BLOB类型只能使用前缀索引

因为MySQL对索引字段长度是有限制的,所以TEXT类型只能使用前缀索引,并且TEXT列上是不能有默认值的。

3、避免使用ENUM类型

  • 修改ENUM值需要使用ALTER语句
  • ENUM类型的ORDER BY操作效率低,需要额外操作
  • 禁止使用数值作为ENUM的枚举值

4、尽可能把所有列定义为NOT

原因:

  • 索引列需要额外的空间来保存,所以要占用更多的空间;
  • 进行比较和计算时要对值做特别的处理

5、使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

TIMESTAMP 占用4字节和INT相同,但比INT可读性高

超出TIMESTAMP取值范围的使用DATETIME类型存储。

经常会有人用字符串存储日期型的数据(不正确的做法):

  • 缺点1:无法用日期函数进行计算和比较
  • 缺点2:用字符串存储日期要占用更多的空间

6、同财务相关的金额类数据必须使用decimal类型

  • 非精准浮点:float,double
  • 精准浮点:decimal

Decimal类型为精准浮点数,在计算时不会丢失精度。占用空间由定义的宽度决定,每4个字节可以存储9位数字,并且小数点要占用一个字节。可用于存储比bigint更大的整型数据。

四、索引设计规范

1、限制每张表上的索引数量,建议单张表索引不超过5个

索引并不是越多越好!索引可以提高效率同样可以降低效率。

索引可以增加查询效率,但同样也会降低插入和更新的效率,甚至有些情况下会降低查询效率。

因为mysql优化器在选择如何优化查询时,会根据统一信息,对每一个可以用到的索引来进行评估,以生成出一个最好的执行计划,如果同时有很多个索引都可以用于查询,就会增加mysql优化器生成执行计划的时间,同样会降低查询性能。

2、禁止给表中的每一列都建立单独的索引

5.6版本之前,一个sql只能使用到一个表中的一个索引,5.6以后,虽然有了合并索引的优化方式,但是还是远远没有使用一个联合索引的查询方式好

3、每个Innodb表必须有个主键

Innodb是一种索引组织表:数据的存储的逻辑顺序和索引的顺序是相同的。

每个表都可以有多个索引,但是表的存储顺序只能有一种 Innodb是按照主键索引的顺序来组织表的。

不要使用更新频繁的列作为主键,不适用多列主键(相当于联合索引) 不要使用UUID、MD5、HASH、字符串列作为主键(无法保证数据的顺序增长)。

主键建议使用自增ID值。

五、常见索引列建议

  • 出现在SELECT、UPDATE、DELETE语句的WHERE从句中的列
  • 包含在ORDER BY、GROUP BY、DISTINCT中的字段

    并不要将符合1和2中的字段的列都建立一个索引,通常将1、2中的字段建立联合索引效果更好

  • 多表join的关联列

六、如何选择索引列的顺序

建立索引的目的是:希望通过索引进行数据查找,减少随机IO,增加查询性能 ,索引能过滤出越少的数据,则从磁盘中读入的数据也就越少。

  • 区分度最高的放在联合索引的最左侧(区分度=列中不同值的数量/列的总行数);
  • 尽量把字段长度小的列放在联合索引的最左侧(因为字段长度越小,一页能存储的数据量越大,IO性能也就越好);
  • 使用最频繁的列放到联合索引的左侧(这样可以比较少的建立一些索引)。

七、避免建立冗余索引和重复索引

因为这样会增加查询优化器生成执行计划的时间。

  • 重复索引示例:primary key(id)、index(id)、unique index(id)
  • 冗余索引示例:index(a,b,c)、index(a,b)、index(a)

八、优先考虑覆盖索引

对于频繁的查询优先考虑使用覆盖索引。

覆盖索引:就是包含了所有查询字段(where,select,ordery by,group by包含的字段)的索引

覆盖索引的好处:

  • 避免Innodb表进行索引的二次查询

Innodb是以聚集索引的顺序来存储的,对于Innodb来说,二级索引在叶子节点中所保存的是行的主键信息,

如果是用二级索引查询数据的话,在查找到相应的键值后,还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中,二级索引的键值中可以获取所有的数据,避免了对主键的二次查询 ,减少了IO操作,提升了查询效率。

  • 可以把随机IO变成顺序IO加快查询效率

由于覆盖索引是按键值的顺序存储的,对于IO密集型的范围查找来说,对比随机从磁盘读取每一行的数据IO要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。

九、索引SET规范

尽量避免使用外键约束

  • 不建议使用外键约束(foreign key),但一定要在表与表之间的关联键上建立索引;
  • 外键可用于保证数据的参照完整性,但建议在业务端实现;
  • 外键会影响父表和子表的写操作从而降低性能。

十、数据库SQL开发规范

1、建议使用预编译语句进行数据库操作

预编译语句可以重复使用这些计划,减少SQL编译所需要的时间,还可以解决动态SQL所带来的SQL注入的问题 只传参数,比传递SQL语句更高效 相同语句可以一次解析,多次使用,提高处理效率。

2、避免数据类型的隐式转换

隐式转换会导致索引失效。如:select name,phone from customer where id = 111;

3、充分利用表上已经存在的索引

  • 避免使用双%号的查询条件。

如a like %123%,(如果无前置%,只有后置%,是可以用到列上的索引的)

  • 一个SQL只能利用到复合索引中的一列进行范围查询

如:有 a,b,c列的联合索引,在查询条件中有a列的范围查询,则在b,c列上的索引将不会被用到,在定义联合索引时,如果a列要用到范围查找的话,就要把a列放到联合索引的右侧。

  • 使用left join或 not exists来优化not in操作

因为not in 也通常会使用索引失效。

4、数据库设计时,应该要对以后扩展进行考虑

5、程序连接不同的数据库使用不同的账号,进制跨库查询

  • 为数据库迁移和分库分表留出余地
  • 降低业务耦合度
  • 避免权限过大而产生的安全风险

6、禁止使用SELECT * 必须使用SELECT <字段列表> 查询

原因:

  • 消耗更多的CPU和IO以网络带宽资源
  • 无法使用覆盖索引
  • 可减少表结构变更带来的影响

7、禁止使用不含字段列表的INSERT语句

如:insert into values (a,b,c);

应使用insert into t(c1,c2,c3) values (a,b,c);

8、避免使用子查询,可以把子查询优化为join操作

通常子查询在in子句中,且子查询中为简单SQL(不包含union、group by、order by、limit从句)时,才可以把子查询转化为关联查询进行优化。

子查询性能差的原因:

  • 子查询的结果集无法使用索引,通常子查询的结果集会被存储到临时表中,不论是内存临时表还是磁盘临时表都不会存在索引,所以查询性能会受到一定的影响;
  • 特别是对于返回结果集比较大的子查询,其对查询性能的影响也就越大;
  • 由于子查询会产生大量的临时表也没有索引,所以会消耗过多的CPU和IO资源,产生大量的慢查询。

9、避免使用JOIN关联太多的表

对于Mysql来说,是存在关联缓存的,缓存的大小可以由join_buffer_size参数进行设置。

在Mysql中,对于同一个SQL多关联(join)一个表,就会多分配一个关联缓存,如果在一个SQL中关联的表越多,所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作,同时join_buffer_size设置的也不合理的情况下,就容易造成服务器内存溢出的情况,就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说,会产生临时表操作,影响查询效率Mysql最多允许关联61个表,建议不超过5个。

10、减少同数据库的交互次数

数据库更适合处理批量操作 合并多个相同的操作到一起,可以提高处理效率

11、对应同一列进行or判断时,使用in代替or

in的值不要超过500个in操作可以更有效的利用索引,or大多数情况下很少能利用到索引。

12、禁止使用order by rand 进行随机排序

会把表中所有符合条件的数据装载到内存中,然后在内存中对所有数据根据随机生成的值进行排序,并且可能会对每一行都生成一个随机值,如果满足条件的数据集非常大,就会消耗大量的CPU和IO及内存资源。

推荐在程序中获取一个随机值,然后从数据库中获取数据的方式

13、WHERE从句中禁止对列进行函数转换和计算

对列进行函数转换或计算时会导致无法使用索引。

  • 不推荐:

where date(create_time)=20190101

  • 推荐:

where create_time >= 20190101 and create_time < 20190102

14、在明显不会有重复值时使用UNION ALL而不是UNION

  • UNION会把两个结果集的所有数据放到临时表中后再进行去重操作
  • UNION ALL不会再对结果集进行去重操作

15、拆分复杂的大SQL为多个小SQL

  • 大SQL:逻辑上比较复杂,需要占用大量CPU进行计算的SQL
  • MySQL:一个SQL只能使用一个CPU进行计算
  • SQL拆分后可以通过并行执行来提高处理效率

十一、数据库操作行为规范

1、超100万行的批量写(UPDATE、DELETE、INSERT)操作,要分批多次进行操作

  • 大批量操作可能会造成严重的主从延迟

主从环境中,大批量操作可能会造成严重的主从延迟,大批量的写操作一般都需要执行一定长的时间,而只有当主库上执行完成后,才会在其他从库上执行,所以会造成主库与从库长时间的延迟情况

  • binlog日志为row格式时会产生大量的日志

大批量写操作会产生大量日志,特别是对于row格式二进制数据而言,由于在row格式中会记录每一行数据的修改,我们一次修改的数据越多,产生的日志量也就会越多,日志的传输和恢复所需要的时间也就越长,这也是造成主从延迟的一个原因。

  • 避免产生大事务操作

大批量修改数据,一定是在一个事务中进行的,这就会造成表中大批量数据进行锁定,从而导致大量的阻塞,阻塞会对MySQL的性能产生非常大的影响。

特别是长时间的阻塞会占满所有数据库的可用连接,这会使生产环境中的其他应用无法连接到数据库,因此一定要注意大批量写操作要进行分批。

2、对于大表使用pt-online-schema-change修改表结构

  • 避免大表修改产生的主从延迟
  • 避免在对表字段进行修改时进行锁表

对大表数据结构的修改一定要谨慎,会造成严重的锁表操作,尤其是生产环境,是不能容忍的。

pt-online-schema-change它会首先建立一个与原表结构相同的新表,并且在新表上进行表结构的修改,然后再把原表中的数据复制到新表中,并在原表中增加一些触发器。

把原表中新增的数据也复制到新表中,在行所有数据复制完成之后,把新表命名成原表,并把原来的表删除掉。

把原来一个DDL操作,分解成多个小的批次进行。

3、禁止为程序使用的账号赋予super权限

当达到最大连接数限制时,还运行1个有super权限的用户连接super权限只能留给DBA处理问题的账号使用。

4、对于程序连接数据库账号,遵循权限最小原则

程序使用数据库账号只能在一个DB下使用,不准跨库 程序使用的账号原则上不准有drop权限。

from:https://www.cnblogs.com/duaimili/p/10277515.html

Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

连接MySQL提示:

1
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

这是由于库文件初始化连接MySQL时连接失败引起的。

导致此错误的原因有:

  • mysql设置文件中“bind-address”值的问题;
  • 访问权限限制问题;
  • 防火墙、杀毒软件阻拦的问题(特别是Windows);
  • 负载过大、最大连接限制了访问(特别正式提供服务的mysql)

1、mysql设置文件的问题:
在mysql数据库一些版本,为了保证安全,mysql安装后,默认只监听本机ip的“127.0.0.1”,如下:

reading_initial_communication_packet.png

这时表现为连接“127.0.0.1”或“localhost”可以连通,连接局域网ip和公网IP无法连通。这时我们将“/etc/my.cnf”文件中的“bind-address”行前添加一个“#”号注释掉,并重启mysqld服务即可:

1
2
3
4
5
[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock
#bind-address=127.0.0.1

注:linux下mysql配置文件默认在“/etc/my.cnf”,Windows平台mysql配置文件在安装目录下的“my.ini”。

2、访问权限的问题:
①、默认mysql的账户只允许本机登录的,要给账户授权通过“ip+端口”进行远程访问,需要先在mysql服务器上授权。在服务器桑登录到mysql,执行下面SQL语句授权:

1
2
3
4
5
6
7
8
9
use mysql;
-- 将数据库"aiezu"的所有权限授权给"root"通过密码"123456"在"8.8.8.8"上进行访问;
grant all privileges on aiezu.* to 'root'@'8.8.8.8' identified by '123456';
-- 将数据库"test"的所有权限授权给"aiezu"通过密码"123321"在任何主机上进行访问;
grant all privileges on test.* to 'aiezu'@'%' identified by '123321';
--刷新权限表
flush privileges;

②、 在mysql客户端连接到服务端时,服务端会根据客户端的ip,尝试将客户端的ip解析为“主机名”,再通过“主机名”去检查mysql的权限表,这项功能叫“name-resolv”。由于此功能的某些原因,也会导致我们文章标题中的错误,还有就是会导致连接数据库变慢,所以需要将吃功能禁用。
禁用方法:在mysql设置文件“[mysqld]”选项组下一行“skip-name-resolve”;

1
2
3
4
5
6
[mysqld]
datadir=/storage/aiezu.com/mysql
socket=/storage/aiezu.com/mysql/mysql.sock
#bind-address=127.0.0.1
skip-name-resolve

③、如果是linux,修改“/etc/hosts.allow”文件,在文件的末尾添加下面内容,允许所有主机访问本机的“mysql”服务:

1
2
mysqld : ALL : ALLOW
mysqld-max : ALL : ALLOW

3、防火墙和杀毒软件:
关闭杀毒软件和防火墙试试,windows方面:关闭系统防火墙,中国人常用的QQ电脑管家、360安全卫士、QQ医生、金山毒霸、卡巴斯基等;linux方便,检查iptables、ipfire系统自带防火墙的设置。

4、服务过载的问题:
①、mysql服务过载:

  • 执行“show processlist;”SQL命令看是否当前打开的连接过多;
  • 使用“show variables like ‘%connect_timeout%’;”语句的设置值,改成一个稍大的值,如20:“SET GLOBAL connect_timeout = 20;”
  • 使用“SHOW STATUS like ‘aborted_%’;”语句查询异常终止的连接和失败的连接;

②、系统过载:

  • 使用shell命令“top”观察mysql占有的内存、cpu情况;
  • 使用shell命令“free -h”看内存占有情况;
  • 使用shell命令“uptime”看cpu负载情况;
  • 使用shell命令“df -h”看磁盘空间使用率,特别占用率100%的情况;

from:https://www.aiezu.com/article/mysql_error_at_reading_initial_communication_packet

mysql remote access

Mysql Install Guide

Add User

tomcat install as service 

service.bat install

How to enable remote access to MySQL server on Windows

GRANT ALL PRIVILEGES ON *.* TO ‘username’@’ip’ IDENTIFIED BY ‘password’;
FLUSH PRIVILEGES;

select user,host from mysql.user where user=’root’

虚拟机中MySQL连接问题:Lost connection to MySQL server at ‘reading initial communication packet, system error: 0 以及 host is not allowed to connect mysql

Access to MySQL server via VirtualBox – Server Fault

The root account’s localhost-only in the vast majority of default installations, are you certain you’ve allowed it to log in from the other system? From the MySQL reference manual:

it means that there is no row in the user table with a Host value that matches the client host

So, there’s no % or 10.0.2.2 in the Host column at all. Check your current config:

select user,host from mysql.user where user='root';

You likely want to create a new root entry with the same password as you have now.

create user 'root'@'10.0.2.2' identified by 'yourpassword';
grant all privileges on *.* to 'root'@'10.0.2.2' with grant option;
flush privileges;

Ubuntu10下搭建MySQL Proxy读写分离

一、MySQL-Proxy基础

MySQL Proxy是一个处于你的Client端和MySQL server端之间的简单程序,它可以监测、分析或改变它们的通信。它使用灵活,没有限制,常见的用途包括:负载平衡,故障、查询分析,查询过滤和修改等等。

p_1

(Figure1:MySQL Proxy)

MySQL-Proxy, announced in June, is a binary application that sits between your MySQL client and server, and supports the embedded scripting language Lua. The proxy can  be used to analyze, monitor and transform communication, and supports a wide range of scenarios including:

One of the more powerful features of MySQL Proxy is the ability to do “Read/Write Splitting“. The basic concept is to have a master database handle transactional queries while slaves handle SELECT queries. Replication is used to synchronize the changes due to transactional queries with the slaves in the cluster. 

MySQL-Proxy是处在你的MySQL数据库客户和服务端之间的程序,它还支持嵌入性脚本语言Lua。这个代理可以用来分析、监控和变换(transform)通信数据,它支持非常广泛的使用场景:

  • 负载平衡和故障转移处理
  • 查询分析和日志
  • SQL宏(SQL macros)
  • 查询重写(query rewriting)
  • 执行shell命令

MySQL Proxy更强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询,而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。

 

二、实战过程

测试环境:Ubuntu 10.04.2 LTS + MySQL5.1.41-3ubuntu12.10-log

192.168.1.147  proxy 代理 入口

192.168.1.126  master  主机 只写

192.168.1.145  slaver  从机 只读

程序上只需要链接到192.168.1.147,而192.168.1.126和192.168.1.145对于程序来说是透明的,你完全不需要理会,也不需要知道192.168.1.126和192.168.1.145,你对数据库的所有操作都只对192.168.1.147进行操作。

1.     安装脚本lua

#apt-get install lua5.1

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的,因此需要安装lua。

2.     安装配置MySQL-Proxy

#apt-get  mysql-proxy

当前获取到的版本是:mysql-proxy 0.8.0(查看版本命令:#mysql-proxy -V)

3.     修改rw-splitting.lua

       #vim /usr/share/mysql-proxy/rw-splitting.lua

配置并使用rw-splitting.lua读写分离脚本,脚本目录是 /usr/share/mysql-proxy,修改读写分离脚本rw-splitting.lua,修改默认连接数,进行快速测试,如果不修改连接数的话要达到连接数为4时才会启用读写分离。

— connection pool

if not proxy.global.config.rwsplit then

proxy.global.config.rwsplit = {

min_idle_connections = 1, //默认为4

max_idle_connections = 1, //默认为8

is_debug = false

}

end

这是因为mysql-proxy会检测客户端连接,当连接没有超过min_idle_connections预设值时, 不会进行读写分离, 即查询操作会发生到Master上。

4.     新建文件夹/var/log/mysql-proxy/和文件mysql-proxy.log

       #mkdir /var/log/mysql-proxy

       #vi mysql-proxy.log

5.     执行读写分离

#sudo mysql-proxy –proxy-read-only-backend-addresses=192.168.1.145:3306 –proxy-backend-addresses=192.168.1.126:3306 –proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua >/var/log/mysql-proxy/mysql-proxy.log &

参数说明:

192.168.1.147  proxy 代理 入口

192.168.1.126  master  主机 只写

192.168.1.145  slaver  从机 只读

       当运行sudo mysql-proxy 上面语句后,查询进程没有4040的时候,需要重启mysql ( sudo /etc/init.d/mysql restart) 之后再输入proxy设置。

6.     查看进程端口

#netstat -ant

#netstat –ntl

p_2

(Figure2:端口)

tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN    

tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN    

7.     查看数据库链接

mysql> show processlist\G;

p_3

(Figure3:进程)

可以看到,产生了一个新连接。如果想杀掉某个链接,可以使用mysql>help kill查看kill的帮助信息,杀掉36进程的命令:mysql>kill 36;

8.     测试读写分离

1)     在mysql-proxy机子进入MySQL

#mysql -u gaizai -p -P4040 -h 192.168.1.147

必须指定-h参数,不然报下面错误:

p_4

(Figure4:出错)

2)     显示数据库列表:

mysql> show databases;

如果你是搭建MySQL-Proxy成功的话,你上面查看到的数据库列表应该是192.168.1.145服务器上的数据库列表。(可以在145和126分别创建不同的数据库进行测试)

3)     进入测试数据库:

mysql> use weibo;

4)     查询表记录:

mysql>select * from blog;

5)     插入一条记录:

mysql> INSERT INTO `blog` (`TaskID`, `Content`, `Quote`, `Author`, `Time`, `Url`, `ImageUrl`, `Transmits`, `Comments`, `Hash`, `AddOn`) VALUES(’10’,’fefef’,’fefef’,’efef’,NOW(),’http://www.cnblogs.com/zgx/archive/2011/09/13/2174823.html’,NULL,’0′,’0′,’33333333′,NOW());

6)     查询表记录:

mysql>select * from blog;

对比两次查询表的记录,看记录是否有变化,我们插入了数据(确认插入成功),但两次的数据是没有变化的,这就对了,这就是读写分离了(我们读的是145的数据库,插入的是126的数据库,而我们的145与126又没有设置Replication;如果之前设置了,请先停止后进行测试)

注:有时候mysql_proxy(38)库里会显示出数据,重启系统系统,重新启动mysql后就没有此现象了。

7)     进入主写服务器(192.168.1.126) 查看数据

#mysql -u gaizai -p -h 192.168.1.126

mysql> use weibo;

mysql>select * from blog;

可以查看已经写入了一条记录。

8)     进入从读服务器(192.168.1.145)

#mysql -u gaizai -p -h 192.168.1.145

mysql> use weibo;

mysql>select * from blog;

因为没有数据显示,说明只能读,不能写。

在使用工具SQLyog执行查询时,在Proxy服务器上会自动显示下面的信息:

 p_5

(Figure5:信息)

9.     MySQL-Proxy+Replication

上面的测试只是测试了插入数据后,在没有进行Master与Slave的Replication设置的情况下,读取Master与Slave的数据是不同,如果想达到Figure1的效果,我们还需要设置Master与Slave之间的数据复制(Replication),详情请参考:Ubuntu10下MySQL搭建Master Slave

三、MySQL-Proxy命令

帮助命令:$mysql-proxy –help-all

查看下MySQL Proxy的版本:$ mysql-proxy -V 

编译启动脚本:$vi /etc/init.d/mysql-proxy

启动命令:$ /etc/init.d/mysql-proxy start

停止命令:$ /etc/init.d/mysql-proxy stop

重启命令:$ /etc/init.d/mysql-proxy restart

 

四、注意事项

1.      在启动mysql-proxy的时候,可以把启动命令保存为文件:

建议使用配置文件的形式启动, 注意配置文件必须是660权限, 否则无法启动. 如果有多个Slave的话, proxy-read-only-backend-addresses参数可以配置多个以逗号分隔的IP:Port从库列表。

杀掉mysql-proxy进程:# killall mysql-proxy 

新建一个文件:# vi /etc/mysql-proxy.cnf

在文件中输入两个分隔符中间的内容:

——————————————————

[mysql-proxy]

admin-username=viajarchen

admin-password=123123

admin-lua-script = /usr/share/mysql-proxy//admin-sql.lua 

proxy-backend-addresses=192.168.1.126:3306

proxy-read-only-backend-addresses=192.168.1.145:3306

proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua

log-file=/var/tmp/mysql-proxy.log

log-level=debug

daemon=true

keepalive=true

max-open-files=1024

——————————————————

设置权限:# chmod 660 /etc/mysql-proxy.cnf

或者#chmod +x /etc/init.d/mysql-proxy

设置启动文件:# mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看信息:# ps -ef | grep mysql-proxy | grep -v grep

root      1869     1  0 18:16 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

root      1870  1869  0 18:16 ?        00:00:00 /usr/local/mysql-proxy/libexec/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

查看日志:# tail -50f /var/tmp/mysql-proxy.log

2.      mysql-proxy参数

–admin-address=host:port 指定一个mysqo-proxy的管理端口, 缺省是4041;

-P, –proxy-address=<host:port> 是mysql-proxy 服务器端的监听端口, 缺省是4040;

-r, –proxy-read-only-backend-addresses=<host:port> 只读Slave的地址和端口, 缺省为不设置;

-b, –proxy-backend-addresses=<host:port> 远程Master地址和端口, 可设置多个做failover和load balance, 缺省是127.0.0.1:3306;

–defaults-file=<file>配置文件, 可以把mysql-proxy的参数信息置入一个配置文件里;

–daemon mysql-proxy以守护进程方式运行

–keepalive try to restart the proxy if it crashed, 保持连接启动进程会有2个, 一号进程用来监视二号进程, 如果二号进程死掉自动重启proxy。

–log-level=debug定义log日志级别,由高到低分别有

(error|warning|info|message|debug)

–proxy-lua-script=file指定一个Lua脚本程序来控制mysql-proxy的运行和设置,这个脚本在每次新建连接和脚本发生修改的的时候将重新调用。

–max-open-files:指定最大档案开启数为1024,否则会有【could not raise RLIMIT_NOFILE to 8192, Invalid argument (22). Current limit still 1024.】的log讯息出现。

3.      当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时,,需要首先通过 stop slave 来停止从服务器,然后再进行测试读写分离。

4.      MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本,但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本,如果有lua脚本编程基础的话,可以在这个脚本的基础上再进行优化;

5.      MySQL-Proxy实际上非常不稳定,在高并发或有错误连接的情况下,进程很容易自动关闭,因此打开–keepalive参数让进程自动恢复是个比较好的办法,但还是不能从根本上解决问题,因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用,虽然比较低效但却能保证稳定性;

6.      一主多从的架构并不是最好的架构,通常比较优的做法是通过程序代码和中间件等方面,来规划,比如单双server-id号分开写入等方式来实现两个或多个主服务器;

7.      MySQL-Cluster 的稳定性也不是太好;

8.      Amoeba for MySQL 是一款优秀的中间件软件,同样可以实现读写分离,负载均衡等功能,并且稳定性要大大超过MySQL-Proxy,建议大家用来替代MySQL-Proxy,甚至MySQL-Cluster。

9.      mysql proxy不支持old_password。另外也可以通过查看密码长度的方式来判断:select length(password) from mysql.user如果长度为16位则是old_password无疑。

10.   安装了mysql-proxy实现读写分离,有master x 1, slave x 2。为了测试failover,停掉了一个slave,然后mysql-proxy会一直报错,提示无法连接。这个情况比单点的mysql还糟糕,挂掉一个就全挂掉!mysql的工程师给提供了一段代码,替换掉:

src/network-mysqld-proxy.c的NETWORK_MYSQLD_PLUGIN_PROTO函数可以解决这个问题。network-mysqld-proxy-function.c文件。

(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会去读取slave的数据)

11.   如果在mysql-proxy的机器上也安装了mysql的话,新手就会在这个时候混乱了,到底要如何进行测试和链接呢?比如使用命令:#mysql -u gaizai -p -P4040 -h 192.168.1.147是表示登陆本机的4040端口,使用gaizai帐号,这个帐号可以不是本地mysql的帐号,这样就比较容易区分了。

12.   在上述环境中,mysql-proxy、mysql-master、mysql-slave三台服务器均存在单点故障。为了避免mysql-proxy单点隐患有两种方法:一种方法是mysql-proxy配合keepalived做双机,另一种方法是将mysql-proxy和应用服务安装到同一台服务器上;为了避免mysql-master单点故障可以使用DRBD+heartbear做双机;为了避免mysql-slave单点故障可以添加多台mysql-slave,mysql-proxy会自动屏蔽后端发生故障的mysql-slave。

13.   用sysbench (或者super-smack)测试mysql性能:

#sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000 –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –mysql-user=gaizai –mysql-host=192.168.1.126 –mysql-db=weibo –num-threads=15 prepare

#sysbench –test=oltp –mysql-table-engine=innodb –oltp-table-size=1000 –mysql-socket=/tmp/mysql.sock –mysql-password=123456 –mysql-user=gaizai –mysql-host=192.168.1.126 –mysql-db=weibo –oltp-test-mode=complex run

14.   关于mysql-proxy的启动和关闭的shell脚本的编写:

15.   读写分离不能回避的问题之一就是延迟,可以考虑Google提供的SemiSyncReplicationDesign补丁。

16.   MySQL-Proxy缺省使用的是4040端口,如果你想透明的把3306端口的请求转发给4040的话,那么可以:iptables -t nat -I PREROUTING -s ! 127.0.0.1 -p tcp –dport 3306 -j REDIRECT –to-ports 4040如果想删除这条规则,可以把上面例子中的-I换成-D。参考链接

17.   当使用bigint 时,mysql_insert_id()存在问题,详情见手册,不过对于大多数人而言,bigint基本不会遇到,所以你可以无视这个问题)注:对于这两个问题,官方BUG库里有人给出了相应的补丁

 

五、错误

在执行命令的时候出现了下面的错误:

p_6

(Figure6:错误信息)

could not raise RLIMIT_NOFILE to 8192

这个一个警告级别的错误,意思是MySQL Proxy在你的系统上不能把open files limit提升到8192,不过没关系的,MySQL Proxy依然好好的运行在你的电脑上。

可以通过设置启动–max-open-files参数解决

MySQL Proxy安装和使用(一)

mysql proxy master and slave test

加入–max-open-files=8192后报下面的错误:

p_7

(Figure7:错误信息)

 

六、疑问与解答

1.      当slave宕机后,mysql-proxy是如何读取的?(经过测试:我停止掉slave数据库,proxy的查询就会转移到master上,当把slave启动后,proxy依然在读master,当有新的链接进来的时候才会重新去读取slave的数据。有时可能需要重启下mysql-proxy)

2.      如何知道mysql-proxy当前执行的select是在哪台机器上执行的?

3.      当slave宕机一段时间后,如果再次同步master的缺失的数据?

4.      当配置中设置了proxy-read-only-backend-addresses=192.168.1.145:3306

,192.168.1.147:3306类似这样的两个slave,如果两个slave的数据不同步,那么是怎么读取数据的?# tail -50f /var/tmp/mysql-proxy.log测试

5.      生产环境中除了进行程序调试外,其它不要开启mysql查询日志,因为查询日志记录了客户端的所有语句,频繁的IO操作将会导致mysql整体性能下降。如何设置呢?

6.      mysql-proxy.cnf文件中的管理员帐号和密码有什么用?使用命令进入管理

mysql -u viajarchen -p -P 4041 -h 192.168.1.147 密码是123123

mysql> select * from proxy_connections;

mysql> select * from proxy_config;

p_8

(Figure8:信息)

7.      关于mysql-proxy的启动和关闭的shell脚本的编写?测试

8.      对于/usr/share/mysql-proxy/rw-splitting.lua脚本中的

local min_idle_connections = 4 local max_idle_connections = 8应该如何理解?min的话就是要达到这个值的时候才会读写分离,那么max的是什么意思呢?最大能有8个链接?

9.      mysqld是什么意思?是mysql的守护进程!

10.   HAProxy和keepalived怎么一起搭建使用?能解决什么问题?

 

七、参考文献

ubuntu 9.04 安装mysql-proxy 成功版

ubuntu10.0.4上配置MYSQL主从复制与读写分离(MySQL-Proxy)

[MySQL管理] mysql主从复制,读写分离 (MySQL Proxy)

MySQL Proxy 安装与读写分离体验

MySQL Proxy(官网)

Getting Started with MySQL Proxy(LUA文件介绍)

Read/Write Splitting with MySQL-Proxy(LUA)

MySQL Proxy learns R/W Splitting(牛人)

MySQL-Proxy负载平衡测试遇到的问题及其分析 && MySQL-Proxy工作机制(Lua)

http://www.lua.org

MySQL Proxy(常用命令)

MySQL Proxy 0.8.2 alpha(下载)

MySQL-Haproxy

mysql-proxy读写分离技术文档整理【原创】(sysbench测试)

用sysbench(或者super-smack)测试mysql性能

mysql-proxy读写分离设置(新建启动文件mysql-proxy.cnf +log)

MySQL主从复制(Master-Slave)与读写分离(MySQL-Proxy)实践(主从复制数据+经验分享)

使用MySQL-Proxy读写分离时的注意事项(查询乱码)

MySQL Proxy快速实现读写分离以及负载均衡(启动脚本)

Mysql-proxy主从搭建读写分离全过程(shell脚本启动)

MYSQL-PROXY测试实例(Lua脚本构建自己的轮询连接算法+管理MySQL Proxy )

from:http://www.cnblogs.com/gaizai/archive/2012/11/21/2780816.html

核心业务系统数据库平台迁移: Oracle -> MySQL

为了对核心技术拥有更多的自主控制能力,为了解决数据库的线性扩展问题,为了尽量减少对商业软件的依赖,为了摆脱对高端硬件的依赖,为了…  基于以上多种原因,2年前,我们计划将公司某核心应用平台进行大手术:数据库平台从软件到硬件全部重构。当然,这其中应用架构的改造也不可避免的进行了大 换血。

这个项目无论是从技术角度还是是业务角度来说,都对我们有着非常大的价值,也必定会带来非常深远的影响。项目历时2年多,分4个阶段才完成:

    • 应用接口统一

这一阶段主要是为了后面真正迁移的时候做准备工作,将该核心应用系统的所有数据访问入口统一到一起,全部以服务化的接口方式呈现给其他有需要的系统,一来方便后续变更的控制,二来也推进了服务化的进程。

    • Oracle数据库中拆分(1拆16)

这个阶段本不是必要的,但是由于项目启动稍微晚了点,数据出现了爆发性增长,导致该系统的数据表太大(单表不带索引过500GB),原  Oracle 数据库已经快撑不住了。为了安全起见,先在 Oracle 中从一个主表以会员ID进行 hash  运算后再进行水平拆分,从1个表分拆成了16个。附表由于访问量稍小,而且全部是根据主键访问,暂时保留原样。

当然,这样的水平拆分,必然会带来数据访问路由以及数据合并的问题。我们专门为此开发了具有分布式数据库路由/数据合并,数据库读写分离,数据库链接管理等功能的数据访问中间层,专门解决拆分后给应用服务器带来的影响,使得应用服务器完全感受不到后端数据库的变化。

这个数据访问中间层,对前端应用服务器来说,就是一个完整的数据库,所有数据请求都从这里实现,以协议的方式和前端应用服务器的jdbc驱动进行交互,以便让数据库对应用服务器彻底透明。

    • Oracle迁移至 MySQL(16拆128)

这个阶段是整个阶段中历时最长,复杂度最高,风险系数最高的,未知因素也最多的一个阶段。虽然 MySQL 数据库已经在互联网行业占据了大片江山,但是对于阿里巴巴来说,却仍然是一个新鲜玩意儿,因为之前我们一直都用 Oracle 来提供所有的业务系统的数据库服务。

在此之前,我们从来没有在如此核心业务系统的数据库上使用过 PC Server 和本地硬盘来承载数据库,一直是使用 IBM  小型机和中高端存储设备来解决高性能和高可靠的问题。在更换成 PC Server 和本地硬盘来承载数据库之后,我们就必须面对 PC Server  本身硬件可能存在的不可靠性所带来的 Crash,所以我们必须有一套完善的 HA 切换机制,要比小型机厂商所提供的商业 HA  管理软件更加高效更加自动化更加可控,才能我们降低了设备本身可靠性之后达到原有的可用性要求。

对于一个需要满足 365 *  24 * 7 的核心业务系统来说,肯定是不可能给我们太多时间来进行数据迁移的,所以我们不得不设计出一个对现有系统影响尽可能小的迁移方案,这势必会造成方案的高度复杂化,带来更多的风险。最后的迁移方案要经历如下4个阶段:

1. Oracle 读/写;;MySQL 初始化并增量写
2. Oracle 读/写; MySQL 写
3. Oracle 写; MySQL 读/写
4. Oracle 停访问; MySQL 读/写

当然,也正式由于有如此复杂的方案,才确保了在整个迁移过程中的的停机时间被控制在了10分钟之类。

    • 附属Detail信息迁移至 MySQL

从项目开始,至完成主表拆分结束,已经接近2年了。这2年时间内,数据量一直都在飞涨,这让即使仅仅只是按照主键访问的附表也快无法承受持续增长的 业务压力,附表的拆分也就成了必行之势。由于在原来主表拆分的过程中,整个项目组已经积累了大量的经验,附表拆分过程非常顺利,基本没有出现任何问题。虽 然附表的拆分过程与主表相比除了 1拆16这个阶段外没有减少其他任何环节,但是整个拆分过程也才2个月就全部搞定了。

这个迁移项目算是彻底完成了,但是我们的迁移之路并不会就此止步,还有很多的系统仍然存在扩展性问题,还有很多的数据库应用等着我们去拆分。

注:同事们还为此送了我们一个虽不太雅但也意思相近的名称 “拆迁队”。

from:http://isky000.com/database/core-databas-system-migration-from-oracle-to-mysql