MySQL性能优化最佳实践 - 06 MySQL设计与开发最佳实践

表结构设计的核心思想是什么?

  1. 表及表字段只增不减,即不删除表,不删除字段,只做增加。
  2. 使用innodb存储引擎,支持事务,MVCC,行级锁,更好的恢复性,更高效的IO,更先进的缓存,更先进的写策略。高并发下性能更好,对多核,大内存,SSD等硬件支持更好。
  3. 自增主键,推荐用独立于业务的AUTO_INCREMENT列或全局ID生成器做代理主键。
  4. 拆开宽表,便于运维,加快变更速度,提高查询性能,节省IO和内存。
  5. 控制单表数据量,纯INT不超过1000W,含VARCHAR不超过500w。建议单库不超过300-400个表。
  6. 控制列数据,越短越好,单表不超50个纯INT字段,VARCHAR(10)不超20个字段,单表字段数上限控制在20-50个。
  7. 表和字段加注释。
  8. 使用推荐的字段类型:
    • 时间 timestamp
    • 日期 date
    • IP int unsigned INET_ATON('127.0.0.1') INET_NTOA(ip)
    • 小数 decimal
    • 尽量不用text/blob/char,用varchar
    • 字段只有true or false,用tinyint
    • 非负数值,用unsigned
    • 默认值不能用null,数字类型用default 0,字符类型用defalut ''
  9. 不在数据库里存图片
  10. 禁用外键

建索引时要考虑哪些因素?

有个大表为了一个查询(一天就查2次),领导要你建索引(索引空间大小有500G),你怎么考虑,是建还是不建?

建索引常用的规则如下:

  1. 表的主键、外键必须有索引;
  2. 数据量超过300w的表应该有索引;
  3. 经常与其他表进行连接的表,在连接字段上应该建立索引;
  4. 经常出现在Where子句中的字段,特别是大表的字段,应该建立索引;
  5. 索引应该建在选择性高的字段上;
  6. 索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引;
  7. 复合索引的建立需要进行仔细分析,尽量考虑用单字段索引代替
    • 正确选择复合索引中的主列字段,一般是选择性较好的字段;
    • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中?单字段查询是否极少甚至没有?如果是,则可以建立复合索引;否则考虑单字段索引;
    • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引;
    • 如果复合索引所包含的字段超过3个,那么仔细考虑其必要性,考虑减少复合的字段;
    • 如果既有单字段索引,又有这几个字段上的复合索引,一般可以删除复合索引
  8. 频繁进行数据操作的表,不要建立太多的索引;
  9. 删除无用的索引,避免对执行计划造成负面影响

若有一个大表,建索引空间有500G,且一天就查2次,那我是不建议建索引的。
根据索引空间大小500G,基本可以判断表的记录应该在亿条数据级以上,且应该是一个类似于日志记录性质的表,且日志增涨应该较快。
这么大的表建索引时,首先会给数据库造成较大的影响,并且建索引后会影响表的写入性能。 在一天就查询2次这种业务需求下,建索引的代价是相对较大的。

执行计划中有 filesort 就会进行磁盘文件排序吗(详细说明)?

filesort是在使用explain命令查看一条SQL的执行计划的时候可能会看到在 “Extra” 一列显示的信息。

filesort表示MySQL需要进行实际的排序操作,而不能通过索引获得已排序数据。

实际上,只要一条 SQL 语句需要进行排序操作,都会显示“Using filesort”,这并不表示就会有文件排序操作。

文章目录
  1. 1. 表结构设计的核心思想是什么?
  2. 2. 建索引时要考虑哪些因素?
    1. 2.1. 有个大表为了一个查询(一天就查2次),领导要你建索引(索引空间大小有500G),你怎么考虑,是建还是不建?
  3. 3. 执行计划中有 filesort 就会进行磁盘文件排序吗(详细说明)?