Matthew Note

MySQL Note

|
MySQL 优化

基本命名和约束规范

  • 表字符集选择UTF8 ，如果需要存储emoj表情，需要使用UTF8mb4(MySQL 5.5.3以后支持)
  • 存储引擎使用InnoDB
  • 变长字符串尽量使用varchar varbinary
  • 不在数据库中存储图片、文件等
  • 单表数据量控制在1亿以下
  • 库名、表名、字段名不使用保留字
  • 库名、表名、字段名、索引名使用小写字母，以下划线分割 ，需要见名知意
  • 库表名不要设计过长，尽可能用最少的字符表达出表的用途

字段规范

  • 所有字段均定义为NOT NULL ，除非你真的想存Null
  • 字段类型在满足需求条件下越小越好，使用UNSIGNED存储非负整数 ，实际使用时候存储负数场景不多
  • 使用TIMESTAMP存储时间
  • 使用varchar存储变长字符串 ，当然要注意varchar(M)里的M指的是字符数不是字节数；使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ，这种方式只能存储IPv4，存储不了IPv6
  • 使用DECIMAL存储精确浮点数，用float有的时候会有问题
  • 少用blob text

关于为什么定义不使用Null的原因

  • 浪费存储空间，因为InnoDB需要有额外一个字节存储
  • 表内默认值Null过多会影响优化器选择执行计划

索引规范

  • 单个索引字段数不超过5，单表索引数量不超过5，索引设计遵循B+ Tree索引最左前缀匹配原则
  • 选择区分度高的列作为索引
  • 建立的索引能覆盖80%主要的查询，不求全，解决问题的主要矛盾
  • DML和order by和group by字段要建立合适的索引
  • 避免索引的隐式转换
  • 避免冗余索引
  • 关于索引规范，一定要记住索引这个东西是一把双刃剑，在加速读的同时也引入了很多额外的写入和锁，降低写入能力，这也是为什么要控制索引数原因。之前看到过不少人给表里每个字段都建了索引，其实对查询可能起不到什么作用。
  • 注意索引引起的死锁问题，会导致性能严重下降，可以通过组合索引来解决这个问题
  • 控制索引前缀长度可以有效的提高效率

冗余索引例子

1
2
3
4
5
idx_abc(a,b,c) 
idx_a(a) 冗余 
idx_ab(a,b) 冗余

隐式转换例子

1
2
3
4
5
6
7
字段:remark varchar(50) NOT Null 
MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec) 
MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)
字段定义为varchar，但传入的值是个int，就会导致全表扫描，要求程序端要做好类型检查

SQL类规范

  • 尽量不使用存储过程、触发器、函数等
  • 避免使用大表的JOIN，MySQL优化器对join优化策略过于简单
  • 避免在数据库中进行数学运算和其他大量计算任务
  • SQL合并，主要是指的DML时候多个value合并，减少和数据库交互
  • 合理的分页，尤其大分页
    UPDATE、DELETE语句不使用LIMIT ，容易造成主从不一致

表设计

  • NULL 表示允许是空值，空值是不同于0，‘’这样的值，所以允许空值会降低索引的效率，通常都设置成NOT NULL
  • 在非STRICT模式下，插入记录时候没有显示的给出一个没有defalut值得时候，不会报错，会默认设置一个默认值，STRICT模式下回直接报错

Log

  1. error 日志：记录启动、运行或停止 mysqld 时出现的问题，默认开启。
  2. general 日志：通用查询日志，记录所有语句和指令，开启数据库会有 5% 左右性能损失。
  3. binlog 日志：二进制格式，记录所有更改数据的语句，主要用于 slave 复制和数据恢复。
  4. slow 日志：记录所有执行时间超过 long_query_time 秒的查询或不使用索引的查询，默认关闭。
  5. Innodb日志：innodb redo log、undo log，用于恢复数据和撤销操作。

Tips

  1. innodb不仅会打印出事务和事务持有和等待的锁，而且还有记录本身，不幸的是，它可能超过innodb为输出结果预留的长度(只能打印1M的内容且只能保留最近一次的死锁信息)，如果你无法看到完整的输出，此时可以在任意库下创建innodb_monitor或innodb_lock_monitor表，这样innodb status信息会完整且每15s一次被记录到错误日志中。如：create table innodb_monitor(a int)engine=innodb;，不需要记录到错误日志中时就删掉这个表即可。

Commands

  • SHOW ENGINE INNODB STATUS