1、表、字段设计

尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

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

• 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

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

• a. 建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
  • MySQL 内存临时表不支持 TEXT、BLOB 这样的大数据类型，如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行。而且对于这种数据，MySQL 还是要进行二次查询，会使 sql 性能变得很差，但是不是说一定不能使用这样的数据类型。
  • 如果一定要使用，建议把 BLOB 或是 TEXT 列分离到单独的扩展表中，查询时一定不要使用 select * 而只需要取出必要的列，不需要 TEXT 列的数据时不要对该列进行查询。
• b. TEXT 或 BLOB 类型只能使用前缀索引
  • 因为MySQL对索引字段长度是有限制的，所以 TEXT 类型只能使用前缀索引，并且 TEXT 列上是不能有默认值的

尽可能把所有列定义为 NOT NULL

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

索引

常见索引字段选择的建议

• 出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
• 包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
• 并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好多
• 表 join 的关联列

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

• 覆盖索引：就是包含了所有查询字段 (where,select,order by,group by 包含的字段) 的索引
• 覆盖索引的好处：
  • 避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
  • 可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。