实践建议

  • 1，字段避免NULL

    • 很难进行查询优化
    • NULL列加索引，需要额外空间
    • 利用0，空串等设置默认值

  • 2，存储时间（精确到秒） 建议使用DATETIME类型，两者区别：

    • MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) default the applied timezone is the server’s timezone.
    • timestamp到2039年将被耗尽。

  • 3，使用VARBINARY存储变长字符串，考虑编码/大小写等

  • 4，存储年使用YEAR类型，存储日期使用DATE类型

  • 5，VARCHAR(N)，N尽可能小

  • 6，字段-将字符串转化为数字

    • 数字VS字符串
    • 更高效
    • 查询等更快
    • 占用空间较小

  • 7，字段-少用大对象字段

    • TEXT处理性能低于VARCHAR
    • IO/跨页存储
    • 强制生成硬盘临时表
    • VARCHAR(65535)->64K
    • 尽量不用或少用TEXT/BLOB字段
    • 若必须使用尽量拆分到单独表
    • 尽量不要用数据库存图片、文件类数据

  • 8，VARCHAR MySQL的VARCHAR字段理论允许65535字节,限制方面令人满意,但是事情并没有那么简单… VARCHAR(N)中N指字符长度,编码类型对字符数量有影响.

    • gbk,每个字符最多占2个字节,最大长度不能超过32766
    • utf8,每个字符最多占3个字节,最大长度不能超过21845
      VARCHAR理论最大存储65535字节,实际65532字节,如果超过,根据SQL_MODE设置,将会报错或自动转为TEXT类型. 其实65535是指行内所有VARCHAR字段长度总和.
      对于InnoDB引擎,实际中就算是TEXT/BLOB也有可能不进行溢出,同样,即使是VARCHAR也可能发生溢出,进入LOB页. InnoDB作为聚簇表,要求一个叶子页至少有两个行.

  • 9，索引

    • 依据WHERE查询条件建立索引
    • 索引字段的顺序根据区分区排，区分度大的放在前面
    • 合理创建联合索引，避免冗余(a,b,c)?(a),(a,b),(a,b,c)
    • ORDER BY，GROUP BY，DISTINCT字段添加索引
    • 过长VARCHAR字段建立索引时，添加crc32或者MD5 Hash字段，对Hash字段建立索引
    • 使用EXPLAIN检查，避免Using File Sort，Using Temporary
    • 控制索引个数及索引内字段数目
    • 如果要给字符字段建立索引，尽量只对其前缀建索引
    • 减小索引大小，IO更加高效
    • 索引字段应该有默认值,尽可能不要让含NULL的字段进入组合索引.

  • 10，InnoDB主键：聚簇索引，二级索引存储主键值

    • 推荐独立于业务的自增列或全局ID做主键
    • 若不指定，内部会自动生成主键
    • 尽量不用字符串做主键
    • 一定要控制主键长度
    • 尽量避免持续插入”随机”主键
    • 主键值尽量少更新
    • 尽量不用外键，外部程序保证约束

  • 11，SQL语句

    • 尽量短小简单
    • 高并发在线OLTP，不建议多表JOIN
    • 线上库禁止执行统计类等大SQL语句，一条大SQL可能会把整个数据库堵死
    • 将大SQL语句拆分为多个小SQL
    • 简单SQL缓存命中更高
    • 减少锁表时间，特别是MyISAM
    • 提供CPU利用率
    • 线上系统少用或不用存储过程/触发器
    • 尽量不用SELECT *，而只取需要的列
    • SQL语句中IN包含的值不应过多
    • 同一字段，改写OR为IN
    • IN的效率往往比OR高
    • 控制OR的个数，建议小于200
    • 不同字段，改写OR为UNION
    • InnoDB尽量不用或少用count(*)

隐式类型转换

  • 当操作符左右两边的数据类型不一致时，会发生隐式转换。
  • 当where查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
  • 当where查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
  • 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

分页查询优化

id范围

主从复制

基于binlog，复制策略：SBR（基于语句 5.6），RBR（基于行），MBR（混合
基于GTIDs（事务复制）
此时从的数据可以修改，但是风险高！