mysql设置数据库注意事项

创建数据库

• 创建数据库的语句必须包含字符集子句和校对规则子句。
• 控制单表数据量，建议单库不超过4096个表；

create database if not exists `permission`
default character set utf8 default collate utf8_bin;

创建表：

• 需要添加创建人、创建日期、修改人和修改日期的四个字段；修改表数据时必须修改修改人和修改日期

• 字段设计时，varchar大小尽可能小，因为mysql一个表中所有字段varchar最大长度为65525个字节，进行排序和创建临时表这一类涉及内存时，会根据使用的字节数进行申请内存

create_by varchar(30) not null comment '创建人',
create_date datetime not null comment '创建日期',
update_by varchar(30) not null comment '修改人',
update_date datetime not null comment '修改日期'

数据类型转化：

最为常见的隐式类型转换常见于时间类型与字符串类型之间,建议所有时间类型字段均以时间类型传入,或者以字符串传入然后通过字符串转换时间函数进行转换, 如下:


select * from member where gmt_create = str_to_date('2009­01­01 01:02:03','%Y­%m­%d %H:%i:%s');

sql数据优化：
··不要使用select *
原因：
1. mysql中orderby排序时有两种算法：一是先查询select后面的字段然后对其进行排序，二是先查询需要排序的字段然后排序，再回表中查询其他字段，相当于查询两次，增加了磁盘io数量
如果select 后面字段总长度超过1024字节（参数max_length_sort_data的默认值）或者查询字段包括blob或者text大文本字段时，都会触发第二种排序算法，这样会增加内存使用，磁盘io的读取速度，消耗更多的io
2.join 语句使用 select * 可能导致只需要访问索引即可完成的查询需要回表取数，所以禁止使用

数据库设计：
1.完整性
2.性能

• 设计合理的字段类型和长度，类型确定好后，字段长度尽量短，因为这会涉及到数据库内存的使用和分配

• 选择高效的主键和索引，尽量减少索引的长度，因为查询表数据时直接或间接通过主键和索引进行查询

• 适度的冗余：减少或避免关联查询，减少join的使用（多适用于查询多，修改少的字段）
• 精简表结构：表结构如果太过复杂，会引起业务上处理复杂，同时也可能会引起并发问题。如果根据业务特性拆分成多个表，可以避免高并发下的锁表现象。
  -3.扩展性
  数据库表扩展性主要包含表逻辑结构、功能字段的增加、分表等
  原则：
  一表一实体。如果不同实体之间有关联时，可增加一个单独的表，不会影响以前的功能。
  扩展字段。在表数据较小时增加一个字段可以很快完成，但是在表很大时，增加字段会比较困难。因此在设计时可考虑选择预留扩展字段。
  分表设计。也就是水平切分。在设计阶段应该考虑数据的增长情况，并根据数据特性以及数据之间关系选择合适的切分策略。有关分表的更详细介绍具体可详见章节3.1.5的介绍

数据库拆分：

定义：指根据某种特定条件，将存放在一个数据库的数据分散到多个数据库上，减少单个数据库服务压力负载。

方式：
垂直切分：根据模块划分不同的模块表进行切分，即将表切分到其他数据库上

优点：
 数据库表的切分简单明了，切分规则明确；
 应用程序模块清晰明确，整合容易；
 数据维护方便易行，容易定位；
缺点：
 部分表关联无法在数据库级别完成，需要在程序中进行；
 对于访问极其频繁且数据量超大的表仍然存在性能瓶颈，不一定能满足要求；
 事务处理相对复杂；
 切分达到一定程度之后，扩展性会受到限制；
过度切分可能会导致系统过于复杂而难以维护；

    水平切分：根据表中的逻辑关系，将同一个表中数据按照某种切分规则切分到其他数据库上或者表中
    eg：按照数据范围：1-100万数据，100万到200万数据进行切分；按照年限进行切分；按照地点切分；按照计算公式，id主键进行被2取模,（蜂巢拆库）

优点：
 表关联基本能够在数据库端全部完成；
 不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；
 应用程序端整体架构改动相对较少；
 事务处理相对简单；
 只要切分规则能够定义好，扩展性一般不会受到限制；
缺点：
 切分规则相对复杂，很难抽象出一个能满足整个数据库的切分规则；
 后期的维护难度有所增加，人为手工定位数据较困难。
 应用系统各模块耦合度非常高，可能会对后面数据的迁移切分造成一定的困难。
若切分不合理，会造成数据表的冷热不均现象。