分类 数据库 下的文章

主服务器开启binlog
从库服务器通过IO线程读取binlog到relaylog 然后从库通过读取relaylog来从放

步骤:
配置主从参数
配置复制的数据库账号

  • 超100万行的批量写操作,要分批多次进行操作。

    • 大批量操作可能会造成严重的主从延迟
    • binlog日志为row格式时会产生大量的日志。
    • 避免产生大事务操作
  • 对大表使用pt-online-schema-change修改表结构

    • 原理是新建一个新表 然后把旧表数据复制到新表上, 然后 在旧表上新建一个触发器,在旧表上新建数据会在新表上新建。
  • 程序使用的账号原则上不准有drop权限。

  • 避免数据类型的隐式转换(隐式转换会导致索引失效)
  • 避免使用双%号的查询条件如 a like '%123%'
  • 组合索引查询的各种场景
    兹有 Index (A,B,C) ——组合索引多字段是有序的,并且是个完整的BTree 索引。

    • 下面条件可以用上该组合索引查询:

      • A>5
      • A=5 AND B>6
      • A=5 AND B=6 AND C=7
      • A=5 AND B IN (2,3) AND C>5
    • 下面条件将不能用上组合索引查询:

      • B>5 ——查询条件不包含组合索引首列字段
      • B=6 AND C=7 ——查询条件不包含组合索引首列字段
    • 下面条件将能用上部分组合索引查询:

      • A>5 AND B=2 ——当范围查询使用第一列,查询条件仅仅能使用第一列
      • A=5 AND B>6 AND C=2 ——范围查询使用第二列,查询条件仅仅能使用前二列
        所以把范围查询的列放到最右边。
  • 程序连接不同的数据库使用不同的账号,禁止跨库查询。 方便以后的数据库扩充
  • 禁止使用SELECT * 必须使用SELECT <字段列表> 查询。

    • 消耗更多的CPU和IO以及网络带宽资源。
    • 无法使用覆盖索引
    • 可减少表结构变更带来的影响
  • 禁止使用不含字段列表的INSERT语句

    • insert into t values('a', 'b', 'c');
    • insert into t(c1,c2,c3) values('a', 'b', 'c');
  • 避免使用子查询,可以把子查询优化为join查询。

    • 子查询的结果集无法使用索引。
    • 子查询会产生临时表操作,如果子查询数据量大则严重影响效率。
    • 消耗过多的CPU及IO资源。
  • 避免使用JOIN关联太多的表

    • 每Join一个表会占用一部分内存(join_buffer_size)
    • 会产生临时表操作,影响查询效率。
    • MYSQL最多允许关联61个表,建议不超过5个。
  • 减少同数据库的交互次数

    • 数据库更适合处理批量操作
    • 合并多个相同的操作到一起,可以提高处理效率。
  • 禁止使用order by rand() 进行随机排序
  • where 从句中禁止对列进行函数转换和计算

    • 对列进行函数转换或计算会导致无法使用索引
  • 在明显不会有重复值时用UNION ALL而不是UNION

    • UNION会把所有数据放到临时表后进行去重操作
    • UNION ALL不会再对结果集进行去重操作
  • 拆分复杂的大SQL为多个小SQL

    • 一个SQL只能使用一个CPU进行计算
    • SQL拆分后可以并行

  • 优先选择符合存储需要的最小数据类型
  • 比如ip地址可以通过INET_ATON('255.255.255.255') = 4294967295 转换为整数型
  • 比如时间 2019年7月16日15:23:25 可以转换为时间戳格式
  • 对于非负数据采用无符号整型存储
  • 使用UTF8存储汉字varchar(255) = 765个字节(过大的长度会消耗更多的内存)
  • 避免使用TEXT、BLOB数据类型 如果查询包含此类字段会导致查询条数很多,导致查询会走磁盘表而不走内存表,还需要进行回表操作增加额外延时。如果一定需要此类字段则建议分离到单独的拓展表中。
  • 避免使用ENUM数据类型,存储的时候是以字符串存储的。 修改ENUM值要使用ALTER语句,有误操作的风险,并且操作时会导致表锁。ENUM类型的ORDER BY操作效率低,需要额外操作。
  • 不要用字符串存储日期型的数据

    • 缺点1:无法用日期函数进行计算和比较。
    • 缺点2:用字符串(16KB)存储日期要占用更多的空间。 时间戳只需要8KB
    • 建议:使用TIMESTAMP(4KB-最多存储到2038-01-19)或DATETIME(8KB)类型存储时间
  • 非精准浮点:

    • float double
  • 精准浮点(同财务相关的金额类数据,必须使用decimal类型来进行存储可以保证浮点计算时不丢失精度)

    • decimal(占用空间由定义的宽度决定、可用于存储比bigint更大的整数数据。)

  • 区分度最高的列放在联合索引的最左侧。 (能够筛掉最多数据的字段)
  • 尽量把字段长度小的列放在联合索引的最左侧
  • 使用最频繁的列放在联合索引的左侧
  • 对于频繁的查询优先考虑使用覆盖索引(避免innodb表进行索引的二次查找,可以把随机IO变为顺序IO加快查询效率)
  • 尽量避免使用外键 因为外键会影响父表、子表的写操作从而降低性能。(参考地址:https://blog.csdn.net/qq_15037231/article/details/87891683)