数据库设计时的一些注意事项

表结构设计

1. 在创建数据表名、字段名时要通俗易懂，易于理解，看到你的字段名就能猜到这个字段的含义
2. 在设计表，以及字段是都务必写上备注（要不然谁知道，那个表，那个字段表示什么意思）
3. 在设计表时，一定要有主键，而且每张表都必须要有自增主键。为什么呢？

主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的减少索引的磁盘空间，提高索引的缓存效率
无主键的表删除，在row模式的主从架构，会导致备库夯住

值得注意的是mysql中binlog的row模式是个重点，大家不妨可以去看看
先来解释前两条：为什么能提高插入性能呢，避免page分页又是怎么回事？

这就得说一下聚集索引了

聚集索引

一个聚集索引定义了表中数据的物理存储顺序。如何理解聚集索引呢，好比一个电话本，比如一个电话本是按照姓氏排序，并且电话号码紧跟着后面。因为聚集索引决定了表中数据的物理存储顺序，那么一个表则有且只有一个聚集索引。一个聚集索引可以包含多个列。好比一个电话本是基于名字，姓氏同时排序。

Innodb如何选择一个聚集索引

对于Innodb，主键毫无疑问是一个聚集索引。但是当一个表没有主键，或者没有一个索引，Innodb会如何处理呢。请看如下规则

如果一个主键被定义了，那么这个主键就是作为聚集索引
如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。
还有一个需要注意的是：
次级索引的叶子节点并不存储行数据的物理地址。而是存储的该行的主键值。
所以：一级索引包含了两次查找。一次是查找次级索引自身。然后查找主键（聚集索引）

总结

Innodb中的每张表都会有一个聚集索引，而聚集索引又是以物理磁盘顺序来存储的，自增主键会把数据自动向后插入，避免了插入过程中的聚集索引排序问题。聚集索引的排序，必然会带来大范围的数据的物理移动，这里面带来的磁盘IO性能损耗是非常大的。
而如果聚集索引上的值可以改动的话，那么也会触发物理磁盘上的移动，于是就可能出现page分裂，表碎片横生。

但是特殊情况下例外

当数据量大，但长时间不会被更新的；
新生成的数据的索引本来就是按照自增的顺序增加的等等。

好了按照上面的继续说

4. 在设计表时应注意，每个字段都必须是 NOT NULL 为什么呢

为什么一定使用 NOT NULL

索引不会包括NULL值。影响索引的统计信息，影响优化器的判断。
复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
所以我们在数据库设计时不要让字段的默认值为NULL。
这个还是比较好理解的，我们继续

5. 明确规定以下几点

 1.  一定使用innodb 引擎，不允许用myisam。
 2. 业务表设计时必须包含日期字段，createitme和lastupdatetime
 3. 每个表的update 语句，必须包含对 UpdateTime的更新
 4. 所有字段建议为not null  , 时间类型必须为not  null
 5. 必须要有主键，主键尽量用自增字段类型，推荐类型为 INT或者 BIGINT类型。
 6.  需要多表 join的字段，数据类型保持绝对一致。
 7. 当表的字段数较多时，将表分成两张表，一张作为条件查询表，一张作为详细内容表。
 8. 当字段的类型为枚举型或布尔型时，建议使用 tinyint类型。（不用char或者varchar）

6. 字段类型越短越好，为什么

 1. 更少的存储空间
 2. 更少的磁盘IO
 3. 更少的网络IO
 4. 更少的MySQL计算空间
 5. 更少的APP计算空间
 6. 整形存储和运算代价比字符型小
 7. 尽量使用tinyint代替char（1）

7. 有text字段的，最好请分表，表字段要少而精（不是mysql不适合存储text,而是在太多的情况下我们期望mysql能够更加高效的提供小数据查询/事务处理）
8. int(10)和int(2)区别，有别于char(x)和varchar(x)，int(x)中的x表示的是整型(tinyint smallint mediumint int bigint)在添加了zerofill描述符后的显示宽度，所以在不添加zerofill描述符的时候，int(1) 和int(10) int(100) 都没什么区别。
9. 如何存储ip呢：用int unsigned来代替char(15),varchar(15)
10. 不可乱用地段类型

 1. 所有字符串都用varchar(255)------------>给合适长度就好
 2. 所有的数字都用bigint------->给合适类型，比如tinyint、smallint等
 3. 所有小数都用decmal-------->不精确值，可以使用其他浮点型，或者用整形来代替

11. 重头戏----索引

  不要修改聚集索引（主键）
  Reason：为了维持B+tree会带来大量的数据移动，所以一般要求使用跟业务不相关的id做一个整形自增主键
  
  索引不是越多越好，尽量合并索引
 1. 索引加快了查询度，但是却会影响写入性能。
 2. 一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并。
 3. 组合索引的原则是，过滤性越好的字段越靠前。

不要给选择性低的字段建单列索引
Reason：MySQL对索引的过滤性有要求，如果过滤性太低MySQL会放弃使用

不要使用外键约束

 1. 对性能损耗特别大。
 2. 让应用程序去维护约束。

字符类型字段尽量使用前缀索引
太长的索引不仅影响写入性能，而且使用效果也差，因此字符串类型字段一般只建前缀索引
alter table test_long_str add index idx_str(str(16));

合理使用复合索引

LIKE查询的索引问题
like只能使用前缀索引，因此 ：
> col like "abc%" 能用上索引
> col like "%abc%" 不能能用上索引
> col like "%abc" 不能能用上索引

那什么情况下无法触发索引呢

 1. 通过索引扫描的记录数超过30%，则会变成全表扫描
 2. 联合索引中，第一个索引列使用单位查询，也会导致全表扫描
 3. 联合索引中，第一查询条件不是最左索引列
 4. 模糊查询条件以‘%’开头
 5. 内存表使用hash索引时，采用范围检索或order by
 6. 两个独立索引，其中一个是检索，另一个是排序
 7. 使用了不同的order by 和group by表达式

索引设计原则

 1. 选择性低的字段不用加索引，例如性别
 2. 常用的字段和选择性高的字段放前面
 3. 需要经常排序的字段，可加入到索引中，列的顺序也按照常用来排序
 4. 对较长的字符串类型的字段，采用前缀索引 例如index（url(64)）
 5. 只创建需要的索引，避免冗余索引，例如 index(a,b) index(a)
 6. 单个索引里包含的列数最好不超过5列
 7. 单表的索引数也不要太多，最好不要超过5个

mysql 索引限制
 1. 不支持表达式，函数索引
 2. 不支持混合顺序
 3. 类型不一致时，会发生yinshi隐式转换
 4. 不支持全模糊匹配
 5. innodb索引支持的最大字节是769字节，myisam索引支持的最大字节是1000字节
 6. 超过30%扫描比例，直接走全表扫描
 7. blob和text类型只能采用前缀索引
 8. join语句中join链接的字段类型不一致时，mysql无法高效实用索引

sql优化和规范

 1. 推荐实用innodb存储引擎
 2. ipv4采用int unsigned 来保存
 3. 尽量不使用，blob和text
 4. 主键和唯一索引能优于普通索引
 5. 复合索引比普通索引更适合
 6. 长字段采用前缀索引
 7. 常用检索，排序字段，需要创建索引
 8. join列类型保持一致
 9. 索引不要创建太多
 10.多用简单sql，少用子查询和复杂查询
 10. 少用复杂join
 11. where条件中不使用函数，避免无法使用索引
 12. 事务快速提交，但不要频繁反复提交
 
 *一条sql可以干崩一个db，不要用复杂sql，能拆小就拆小*
 
 13. 语句尽量简单，不在数据库做复杂运算
 14. 不允许使用select *
 15. 不允许在where后面的字段做运算或者使用函数，会导致无法使用索引
 16. 只用inner join 和left join ，少使用right join 表关联的欧尼必须有索引，只关联需要表和需要列
 17. 复杂查询，拆分简单查询，尽量小批量小语句分段执行
 18. 大事务可以 set auto commit=0 关闭自动提交，拒绝滥用，会导致阻塞
 19. 不在数据库做复杂运算，尽量少使用函数和存储过程
 20. 大消耗性能的sql很容易对其他sql产生影响
 21. 处理复杂sql能力不强，在高并发系统中，复杂sql容易产生锁问题
 22. select的数据长度可以影响order by 排序算法
 23. 增删字段对程序有影响
 24. 不要在索引列做运算和表达式