06 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”，这并不表示就会有文件排序操作。