做英文网站公司,合肥网站优化哪家好,珠海东莞网站建设,基础建设年本内容一共分上下两篇 上#xff1a;MySQL必看表设计经验汇总-上(精华版)-CSDN博客 下#xff1a;MySQL必看表设计经验汇总-下(精华版)-CSDN博客 目录
7.定义字段尽可能not null
8.合理添加索引
9.不需要严格遵守3NF#xff0c;通过业务字段冗余来减少表关联
11.避免使用… 本内容一共分上下两篇 上MySQL必看表设计经验汇总-上(精华版)-CSDN博客 下MySQL必看表设计经验汇总-下(精华版)-CSDN博客 目录
7.定义字段尽可能not null
8.合理添加索引
9.不需要严格遵守3NF通过业务字段冗余来减少表关联
11.避免使用MySQL保留字
12.不搞外键关联一般都在代码维护
13.字段要注释
14.时间类型的选择
附加内容
有关SQL编写的一些优化经验 前言
本内容共分上下两篇建议从上篇开始看下篇衔接上篇 7.定义字段尽可能not null
如果没有特殊的理由一般都建议将字段定义为NOT NULL。为什么呢?
首先NOT NULL 可以防止出现空指针问题 其次NULL值存储也需要额外的空间的它世会导致比较运算更为复杂使优化器难以优化SQL。
NULL值有可能会导致索引失效 如果将字段默认设置成一个空字符串或常量值并没什么不同且都不会影响到应用逻辑那就可以将这个字段设置为NOT NULL。 8.合理添加索引
当设计表时需要考虑哪些字段需要加索引可以遵循以下几个原则
1.根据查询条件进行选择(高频使用):如果在查询中使用了某个字段作为查询条件那么这个字段就应该建立索引。例如在用户表中如果需要根据用户的姓名进行查询那么就应该为姓名字段建立索引。
2.区分度高的字段优先如果一字段的取值范围非常小例如性别只有男女两种可能那么这个字段就不适合建立索引。相反如果一个字段的取值范围很大且区分度高例如用户ID那么这个字段就非常适合建立索引。
3.不要建立过多的索引每个表所建立的索引数量应该控制在一个合理的范围内一般不要超过5个。因为过多的索引会导致写入速度变慢并占用更多的存储空间。
4.联合索引优化在某些情况下可以通过联合索引的方式来优化查询速度减少所需的索引数量。例如在用户表中如果需要根据用户姓名和年龄进行查询那么可以将这两个字段组合成联合索引。 假设你有一个订单表包含订单ID、用户ID、订单金额、订单状态等字段。现在需要根据用户ID和订单状态进行查询可以考虑为用户ID和订单状态这两个字段建立联合索引。 9.不需要严格遵守3NF通过业务字段冗余来减少表关联
简单来说就是反范式设计常见形式是在第三范式(3NF)的基础上进一步进行冗余从而减少表关联
数据库三范式(3NF) 第一范式对属性的原子性要求属性具有原子性不可再分解第二范式:记录的唯一性要求记录有唯一标识即实体的唯一性即不存在部分依赖第三范式对字段的冗余性要求任何字段不能由其他字段派生出来它要求字段没有冗余即不存在传递依赖 假设需要设计个产品订单表包含以下字段: 订单ID、用户ID、订单日期、产品名称、产品价格品数量以及订单总价。正常情况下可能会分别设计订单表和产品表并使用外键进行关联例如:
create table orders
(id int(11) not null auto_increment,user_id int(11) not null, -- 用户IDorder_date date not null, -- 订单日期product_id int(11) not null, -- 产品IDquantity int(11) not null, -- 数量primary key (id),foreign key (product_id) references product (id)
);create table product
(id int(11) not null auto_increment,name varchar(256) not null, -- 产品名称price decimal(10, 2) not null, -- 价格primary key (id)
);
这种设计在使用时会比较麻烦你要先查订单表再查产品表通过数量跟单价才可以计算出总价。
这个设计方式符合范式要求但在查询时需要进行表关联操作可能会降低查询效率。为了提高查询效率我们可以使用反范式的设计方式将订单表中的产品名称、产品价格和订单总价冗余存储到订单表中从而避免关联查询。例如:
create table orders
(id int(11) not null auto_increment, -- 订单ID自增类型user_id int(11) not null, -- 用户IDorder_date date not null, -- 下单日期product_name varchar(256) not null, -- 产品名称product_price decimal(10, 2) not null, -- 产品单价保留两位小数quantity int(11) not null, -- 购买数量total_price decimal(10, 2) not null, -- 订单总价保留两位小数primary key (id)
);
通过这种反范式的设计方式我们可以避免表关联操作提高查询效率但也带来了一些缺点如数据冗余数据更新困难等所以在实际应用中根据情况做取舍。 11.避免使用MySQL保留字
如果库名、表名、字段名等属性含有保留字时SQL语句必须用反引号来引用属性名称这将使得SQL语句书写、SHELL脚本中变量的转义等变得非常复杂
如果你需要使用这些保留字作为表名、列名或其他标识符你可以考虑以下方法来避免冲突: 在标识符前或后添加下划线:例如将表名命名为“my_table列名命名为“column_name 使用不同的单词或短语:例如将表名命名为orders_table列名命名为order_status 使用反引号将标识符括起来:例如将表名命名为“table列名命名为column。请注意在使用反引号时要小心确保使用正确的语法和规范 MySQL常见保留字 SELECT、INSERT、UPDATE、DELETE、CREATE、ALTER、DROP、FROM、WHERE、AND、OR、NOT、ORDER BY、GROUP BY 12.不搞外键关联一般都在代码维护
在数据库设计中使用外键关联是一种良好的实践可以确保数据的完整性和一致性。外键关联可以帮助维护表之间的关系防止无效或不一致的数据插入、更新或删除操作。然而在某些情况下也存在些缺点这可能是导致现在不太推荐使用外键关联的原因之一。以下是一些这种情况: 可能会导致性能问题尤其是在对大型数据集进行操作时。这是因为每次插入、更新或删除操作都需要进行约束检查这可能会导致额外的开销和延迟。可能会限制数据库的灵活性和可扩展性。例如如果需要对数据库进行分区或垂直分割外键关联可能会导致额外的复杂性和限制。可能会导致死锁和死循环特别是在进行并发操作时。这可能会导致数据库出现不稳定的状态从而影响系统的性能和可用性。可能会导致数据库的维护和管理成本的增加。这是因为外键关联需要额外的管理和维护工作例如添加、修改或删除外键约束时需要额外的测试和验证。 因此在决定是否使用外键关联时需要考虑实际业务需求和场景并进行权衡和决策。在某些情况下可以采用其他方法来保证数据的完整性和一致性例如使用应用程序逻辑或数据库触发器来实现约束检查和数据操作。同时需要注意数据库设计的基本原则和最佳实践例如避免数据几余、遵循规范化原则和正常化理论等。
13.字段要注释
设计表时每个字段的含义要注释清楚包括枚举类型。比如说 order_status varchar(2) not null comment 订单状态 01:待支付02:已支付03:已发货04:已完成05:已取消
14.时间类型的选择
时间类型的选择一般都要好好考虑因为不同的类型存储的格式不同。
对于MySQL来说主要有datedatetime、time、timestamp 和 year。 date: 表示的日期值格式yyyy-mm-dd,范围1000-01-01 到 9999-12-313字节time:表示的时间值格式 hh:mm:ss范围-838:59:59 到838:59:593字节datetime: 表示的日期时间值格式yyyy-mm-dd hh:mm:ss范围1000-01-01 00:00:00到9999-12-31 23:59:598字节跟时区无关timestamp: 表示的时间截值格式为yyyymmddhhmmss范围1970-01-01 00:00:01到2038-01-19 03:14:074字节跟时区有关year: 年份值格式为yyyy。范围1901到21551字节 推荐优先使用datetime类型来保存日期和时间因为存储范围更大且跟时区无关 附加内容
有关SQL编写的一些优化经验 避免使用SELECT*FROM 语句应该只选择需要的列以减少网络传输和提高查询性能。使用索引来提高查询速度特别是在对大型表进行查询时避免使用外键约束因为它们可能会导致性能问题特别是在对大型表进行插入、更新和删除操作时。使用LIMIT1来限制查询结果只有一条记录。避免在where子句中使用OR来连接条件应使用UNION来连接查询。注意优化LIMIT深分页问题可以使用OFFSET来替代LIMIT.。使用where条件限制要查询的数据避免返回多余的行。尽量避免在索引列上使用MySQL的内置函数这可能导致索引失效。应尽量避免在where子句中对字段进行表达式操作这可能导致索引失效。应尽量避免在where子句中使用!或操作符这可能导致索引失效。使用联合索引时注意索引列的顺序一般遵循最左匹配原则。对查询进行优化应考虑在where及order by涉及的列上建立索引。如果插入数据过多考虑批量插入。在适当的时候使用覆盖索引(查询列都是索引列)避免了回表。使用EXPLAIN 分析你SQL的计划。主要用来看sql走没走期望的索引
