1.
集
选择 1 2. 主键 1
3. 外键 2
4. 索引 2
4.1. 以下情况适合于创建索引 2
4.2. 以下
情况下不适合创建索引 3 4.3. 联合索引 3
4.4. 索引长度 4
5. 特殊字段 4
5.1. 冗余字段 4
5.2. 分割字段 4
5.3. BLOB和CLOB 5
6. 特殊 5
6.1. 表格分割 5
6.2. 使用非事务表类型 5
1.
集选择 如果确认全部是中文
不会使用多语言以及中文无法表示那么GBK是首选
采用UTF-8编码会占用3个字节
而GBK只需要2个字节 2. 主键
尽可能使用长度短
主键 系统
自增类型AUTO_INCREMEN, 而不是使用类似uuid
等类型如果可以使用外键做主键则更好比如1:1关系使用主表id作为从表主键 主键
字段长度需要根据需要指定 tiny
从 27次方-1 :-128 到 127 small
从 215次方-1 :-32768 到 32767 medium
表示为 223次方-1: 从 -8388608 到8388607 表示为 231次方-1 big
表示为 263次方-1 在主键上无需建单独
索引
系统内部为主键建立了聚簇索引 允许在其它索引上包含主键列
3. 外键
外键会影响插入和更新性能
对于批量可靠数据插入建议先屏蔽外键检查 对于数据量大
表建议去掉外键改由应用
进行数据完整性检查 尽可能用选用对应主表
主键作作为外键避免选择长度很大主表唯
键作为外键 外键是默认加上索引
4. 索引
创建索引
要在适当表适当列创建适当数量适当索引在查询优先和更新优先的间做平衡 4.1. 以下情况适合于创建索引
在经常需要搜索
列上可以加快搜索速度 在作为主键
列上强制该列唯性和组织表中数据排列结构 在经常用在连接
列上这些列主要是些外键可以加快连接速度 在经常需要根据范围进行搜索
列上创建索引索引已经排序其指定范围是连续 在经常需要排序
列上创建索引索引已经排序这样查询可以利用索引排序加快排序查询时间 在经常使用在WHERE子句中
列上面创建索引加快条件判断速度 4.2. 以下
情况下不适合创建索引 对于那些在查询中很少使用或者参考
列不应该创建索引这是既然这些列很少使用到因此有索引或者无索引并不能提高查询速度相反由于增加了索引反而降低了系统维护速度和增大了空间需求 对于那些只有很少数据值
列也不应该增加索引这是由于这些列取值很少例如人事表性别列在查询结果中结果集数据行占了表中数据行很大比例即需要在表中搜索数据行比例很大增加索引并不能明显加快检索速度 对于那些定义为text, image和bit数据类型
列不应该增加索引这是这些列数据量要么相当大要么取值很少 当修改性能远远大于检索性能时
不应该创建索引这是修改性能和检索性能是互相矛盾 如果表数据很少
比如每个省按市做汇总表般低于2000且数据量基本没有变化此时增加索引无助于查询性能却会极大影响更新性能 当增加索引时
会提高检索性能但是会降低修改性能当减少索引时会提高修改性能降低检索性能因此当对修改性能要求远远大于检索性能时不应该创建索引 4.3. 联合索引
在特定查询里
联合索引效果高于多个单索引当有多个索引可以使用时MySQL只能使用其中个 在查询里
同时用到了联合索引包含前几个列名都会使用到联合索引否则将部分或不会用到比如我们有个firstname、 lastname、age列上多列索引我们称这个索引为fname_lname_age当搜索条件是以下各种列组合时MySQL将使用 fname_lname_age索引: firstname
lastnameage firstname
lastname firstname
从另
方面理解它相当于我们创建了(firstnamelastnameage)、(firstnamelastname)以及(firstname)这些列组合上索引 4.4. 索引长度
对于CHAR或者Varchar
列索引可以根据数据分布情况用列部分参和创建索引 create index idx_t_
on t_(name(3)); 这里就是指定name
前 3个参和索引而不是全部 最大允许
长度为1000个字节对已GBK编码则是500个汉字 5. 特殊字段
5.1. 冗余字段
就是用空间换取时间
如果大表查询里经常要join某个基础表且这个数据基本不变比如人姓名城市名字等旦基础表发生变动则需要更新所有涉及到冗余表 5.2. 分割字段
如果经常出现以某个字段
某个局部进行检索和汇总(sub
)可以考虑将这部分独立出来 比如统计姓名里
每种姓氏人数可以考虑实现就按照姓和名分别保存而不是个字段 还有就是某些上下级结构
实现也可以考虑将区别级别放在区别字段里 5.3. BLOB和CLOB
此类字段
般数据量很大建议设计上数据库可以只保存其外部连接而数据以其它方式保存比如系统文件 6. 特殊
6.1. 表格分割
如果
个表有许多列但平时参和查询和汇总列却并不是很多此时可以考虑将表格拆分成2个表个是常用字段另个是很少用到字段 6.2. 使用非事务表类型
MySQL支持多种表类型
其中InnoDB类型是支持事物而MyISAM类型是不支持但MyISAM速度更快对于某些数据比如地理行政划分民族等不可能参和事务数据可以考虑用MyISAM类型表格 但InnoDB
表将无法用MyISAM表数据做外键约束了 MyISAM表参和
事务其InnoDB表可以正常提交和回滚但不影响MyISAM表
最新评论