mysql设计表时注意事项

有text字段的，最好请分表。（本质上说,不是mysql不适合存储text,而是在太多的情况下我们期望mysql能够更加高效的提供小数据查询/事务处理）

表字段数要少而精

5、

为什么必须有自增整形主键，一般该字段没有业务意义；少用唯一键。

6、

为什么尽量不要使用default null ?

1> 索引不会包括NULL值。影响索引的统计信息，影响优化器的判断。

2>复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。

3> 所以我们在数据库设计时不要让字段的默认值为NULL。

字段统一加上not null default ‘合理默认值’

7、

常见问题一——int(10)和int(2)区别

有别于char(x)和varchar(x)，int(x)中的x表示的是整型(tinyint smallint mediumint int bigint)在添加了zerofill描述符后的显示宽度，所以在不添加zerofill描述符的时候，int(1) 和int(10) int(100) 都没什么区别。

8、

常见问题二——怎么存储IP

用什么字段类型存储IP，char(15),varchar(15)还是int unsigned？

9、

常见问题三——乱用字段类型

1>所有字符串都用varchar(255)------------>给合适长度就好

2>所有的数字都用bigint------->给合适类型，比如tinyint、smallint等

3>所有数字都用decmal-------->不精确值，可以使用其他浮点型，或者用整形来代替

1、

开发人员要考虑到常用什么sql，一定要加上index。不能只是一个表只有一个主键。

● 不要修改聚集索引（主键）

为了维持B+tree会带来大量的数据移动，所以一般要求使用跟业务不相关的id做一个整形自增主键

● 索引不是越多越好，尽量合并索引

1>索引加快了查询度，但是却会影响写入性能。

2> 一个表的索引应该结合这个表相关的所有SQL综合创建，尽量合并。

3>组合索引的原则是，过滤性越好的字段越靠前。

例如key (a)和key（a,b）存在，那么key（a）可以删除了，对于select ……from tb where a=123；可以用到索引（a，b）

● 不要给选择性低的字段建单列索引

MySQL对索引的过滤性有要求，如果过滤性太低MySQL会放弃使用。

● 不要使用外键约束

1> 对性能损耗特别大。

2>让应用程序去维护约束。

● 字符类型字段尽量使用前缀索引

太长的索引不仅影响写入性能，而且使用效果也差，因此字符串类型字段一般只建前缀索引

alter table test_long_str add index idx_str(str(16));

● 合理使用复合索引

● LIKE查询的索引问题

like只能使用前缀索引，因此 ：

1>col like "abc%" 能用上索引

2>col like "%abc%" 不能能用上索引

3>col like "%abc" 不能能用上索引

2、

三、SQL优化或规范

mysql设计表时注意事项

标签：不能   枚举   pos   允许   boolean   外键   big   tin   null   

小编还为您整理了以下内容，可能对您也有帮助：

急需MySql数据库设计规范

1. 数据表命名规范
模块名缩写_存储信息[_存储信息子类](多个单词用下划线分隔)，全部小写，例如：b2c_goods_type
2. 字段命名规范
存储信息属性(多个单词用下划线分隔)，全部小写，命名规则只来自于业务，尽量表达出列的含义。
例如：goods_id
3. 字段类型规范。
规则:用尽量少的存储空间来存 数一个字段的数据.
比如能用int的就不用char或者varchar
能用tinyint的就不用int
能用 varchar(20)的就不用varchar(255)
时间戳字段尽量用int型，如 created:表示从 '1970-01-01?08:00:00'开始的int秒数，采用英文单词的过去式；gmtCreated:表示datetime类型的时间，即形如 '1980-01-01?00:00:00'的时间串，Java中对应的类型为Timestamp
日期：用date
时间：用time
数字格式的用：int、tinyint、mediumint、smallint、bigint根据实际情况选择
字符串：用char、varcahr；
文本：用text
金额：用float

急需MySql数据库设计规范

1. 数据表命名规范
模块名缩写_存储信息[_存储信息子类](多个单词用下划线分隔)，全部小写，例如：b2c_goods_type
2. 字段命名规范
存储信息属性(多个单词用下划线分隔)，全部小写，命名规则只来自于业务，尽量表达出列的含义。
例如：goods_id
3. 字段类型规范。
规则:用尽量少的存储空间来存 数一个字段的数据.
比如能用int的就不用char或者varchar
能用tinyint的就不用int
能用 varchar(20)的就不用varchar(255)
时间戳字段尽量用int型，如 created:表示从 '1970-01-01?08:00:00'开始的int秒数，采用英文单词的过去式；gmtCreated:表示datetime类型的时间，即形如 '1980-01-01?00:00:00'的时间串，Java中对应的类型为Timestamp
日期：用date
时间：用time
数字格式的用：int、tinyint、mediumint、smallint、bigint根据实际情况选择
字符串：用char、varcahr；
文本：用text
金额：用float

MYSQL数据库设计数据类型选择需要注意哪些地方

•VARCHAR和CHAR类型，varchar是变长的，需要额外的1-2个字节存储，能节约空间，可能会对性能有帮助。但由于是变长，可能发生碎片，如更新数据；
•使用ENUM代替字符串类型，数据实际存储为整型。
•字符串类型
•要尽可能地避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在MYISAM表上使用字符串标识符。MYISAM默认情况下为字符串使用了压缩索引（Packed Index），这使查找更为缓慢。据测试，使用了压缩索引的MYISAM表性能要慢6倍。
•还要特别注意完全‘随机’的字符串，例如由MD5（）、SHA1（）、UUID（）产生的。它们产生的每一个新值都会被任意地保存在很大的空间范围内，这会减慢INSERT及一些SELECT查询。1）它们会减慢INSERT查询，因为插入的值会被随机地放入索引中。这会导致分页、随机磁盘访问及聚集存储引擎上的聚集索引碎片。2)它们会减慢SELECT查询，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。3）随机值导致缓存对所有类型的查询性能都很差，因为它们会使缓存赖以工作的访问局部性失效。如果整个数据集都变得同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何的优势了。并且如果工作集不能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。
•如果保存UUID值，就应该移除其中的短横线，更好的办法是使用UHEX（）把UUID值转化为16字节的数字，并把它保存在BINARY（16）列中。

MYSQL数据库设计数据类型选择需要注意哪些地方

•VARCHAR和CHAR类型，varchar是变长的，需要额外的1-2个字节存储，能节约空间，可能会对性能有帮助。但由于是变长，可能发生碎片，如更新数据；
•使用ENUM代替字符串类型，数据实际存储为整型。
•字符串类型
•要尽可能地避免使用字符串来做标识符，因为它们占用了很多空间并且通常比整数类型要慢。特别注意不要在MYISAM表上使用字符串标识符。MYISAM默认情况下为字符串使用了压缩索引（Packed Index），这使查找更为缓慢。据测试，使用了压缩索引的MYISAM表性能要慢6倍。
•还要特别注意完全‘随机’的字符串，例如由MD5（）、SHA1（）、UUID（）产生的。它们产生的每一个新值都会被任意地保存在很大的空间范围内，这会减慢INSERT及一些SELECT查询。1）它们会减慢INSERT查询，因为插入的值会被随机地放入索引中。这会导致分页、随机磁盘访问及聚集存储引擎上的聚集索引碎片。2)它们会减慢SELECT查询，因为逻辑上相邻的行会分布在磁盘和内存中的各个地方。3）随机值导致缓存对所有类型的查询性能都很差，因为它们会使缓存赖以工作的访问局部性失效。如果整个数据集都变得同样“热”的时候，那么把特定部分的数据缓存到内存中就没有任何的优势了。并且如果工作集不能被装入内存中，缓存就会进行很多刷写的工作，并且会导致很多缓存未命中。
•如果保存UUID值，就应该移除其中的短横线，更好的办法是使用UHEX（）把UUID值转化为16字节的数字，并把它保存在BINARY（16）列中。

MySQL高性能SQL注意事项简述

数据库作为应用开发中必不缺少的基础设施，其性能直接影响应用的整体运行速度。MySQL是目前最广泛使用的关系型数据库之一，对于开发人员写出性能良好的SQL是必备的基本技能之一。下面简单描述下编写SQL的注意事项。

编写高质量的SQL需要从以下几个方面注意，基本原则、表字段注意事项、索引使用注意事项、SQL注意事项。

基本原则

一、尽量不要在数据库里做运算。如果遇到运算尽可能在应用程序层进行计算。

二、控制数据库表数量、控制单表数据量、控制表的字段数。建议单库不要超过四百张表，建议单表字段不要超过五十个，建议单表的数据量不要超过一千万。

三、不要编写大SQL、不要使用大事务。SQL尽量写的简单点拒绝编写大SQL，可以将大SQL拆分成多个小SQL，在应用层聚合。大事务拆分成多个小事务，快速提交。

表字段注意事项

一、选择合适数值字段类型。能用小字段类型的就用小字段类型，如tinyint就比int(1)在表示小数据时合适。

二、能用数字表示就不要用字符。如可以用无符号INT存储IP而不是字符串表示。

三、避免使用NULL字段。原因NULL字段查询优化难，含NULL复合索引失效。

四、少用或拆分TEXT/BLOB字段。字段太大需要更多的空间，性能低下，如需使用拆分到单独表。

五、不要在表字段中存储图片。

索引使用注意事项

一、合理添加索引。索引添加太多会影响更新速度。能够使用复合索引的避免加多个单独索引。

二、字符字段建立前缀索引。

三、不在索引列做运算。索引列做运算会导致索引失效。

四、尽量不使用外建。

SQL类注意事项

一、 SQL语句尽可能简单。大SQL拆分成多个小SQL。

二、事务编写尽量短小。事务即开即用用完立即关闭。

三、尽量不要使用select *。只取需要的列。

四、改写OR为IN或者改写为UNION操作。OR在数据量大的时候性能低于IN。

五、避免NOT、!=、>、NOT IN、NOT EXISTS、NOT LIKE等查询。

六、避免%前缀模糊查询。

七、能用UNION ALL不要用UNION。

八、GROUP BY中去除排序。自带排序。

九、同类型的字段做比较。字符类和字符类比较，数值类和数值类比较，不要混在一起比较。

十、尽量单表查询，尽量不要多表关联查询。多表关联查询可以拆分成单表查询在应用程序中聚合数据。

十一、复合索引的多列注意最左原则。

上述注意事项能避免很多性能低下的SQL，希望在开发过程中能引起注意。

数据库设计需要遵守的设计规范？

数据库的开发对于后台编程程序员来说是必备能力之一了，而今天我们就一起来了解一下，关于数据库开发的设计规范都有哪些类型，昌平镇北大青鸟希望通过对本文的阅读，大家对于数据库开发有更多的了解。

一、数据库命令规范

所有数据库对象名称必须使用小写字母并用下划线分割

所有数据库对象名称禁止使用mysql保留关键字(如果表名中包含关键字查询时，需要将其用单引号括起来)

数据库对象的命名要能做到见名识意，并且后不要超过32个字符

临时库表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期(时间戳)为后缀

所有存储相同数据的列名和列类型必须一致(一般作为关联列，如果查询时关联列类型不一致会自动进行数据类型隐式转换，会造成列上的索引失效，导致查询效率降低)

二、数据库基本设计规范

1、所有表必须使用Innodb存储引擎

没有特殊要求(即Innodb无法满足的功能如：列存储，存储空间数据等)的情况下，所有表必须使用Innodb存储引擎(mysql5.5之前默认使用Myisam，5.6以后默认的为Innodb)Innodb支持事务，支持行级锁，更好的恢复性，高并发下性能更好

2、数据库和表的字符集统一使用UTF8

兼容性更好，统一字符集可以避免由于字符集转换产生的乱码，不同的字符集进行比较前需要进行转换会造成索引失效

3、所有表和字段都需要添加注释

使用comment从句添加表和列的备注从一开始就进行数据字典的维护

4、尽量控制单表数据量的大小，建议控制在500万以内

500万并不是MySQL数据库的，过大会造成修改表结构，备份，恢复都会有很大的问题

可以用历史数据归档(应用于日志数据)，分库分表(应用于业务数据)等手段来控制数据量大小

5、谨慎使用MySQL分区表

分区表在物理上表现为多个文件，在逻辑上表现为一个表谨慎选择分区键，跨分区查询效率可能更低建议采用物理分表的方式管理大数据

6、尽量做到冷热数据分离，减小表的宽度

MySQL每个表多存储4096列，并且每一行数据的大小不能超过65535字节减少磁盘IO,保证热数据的内存缓存命中率(表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO)更有效的利用缓存，避免读入无用的冷数据经常一起使用的列放到一个表中(避免更多的关联操作)