数据库编码注意事项

数据库编码注意事项

• 不要依靠外键保证参照完整性约束，在业务端实现参照完整性的要求。外键使得表之间相互耦合，影响update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈
• 优先为表中的每一列选择符合存储需要的最小的数据类型，如tinyint替代int,tinyint/int/bigint，分别会占用1/4/8字节

字段设计规范

• • 列的字段类型越大，建立索引占据的空间就越大，导致一个页中的索引越少，造成IO次数增加，影响性能。
  • 使用unsigned存储非负值。
  • 每个字段必须具有NOT NULL属性

尽量避免NULL

• • 很多表都包含可为NULL（空值）的列，即使应用程序并不需要保存NULL也是如此，这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL，除非真的需要存储NULL值。
  • 如果查询中包含可为NULL的列，对MySql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySql里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MyISAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。
  • 通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中查找并修改掉这种情况，除非确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。
  • 当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据有很好的空间效率。但这一点不适用于MyISAM。

为什么数据库字段要使用NOT NULL？_丨落叶的博客-CSDN博客_数据库notnull

大表处理

SQL大量数据表的优化方案 - 代码先锋网 (codeleading.com)

• • 尽量不要用in和not in，连续的就用between，不连续的做判断就用exists

count（*)和count(1)的区别

(146条消息) COUNT(1)和COUNT() 的区别_dd-moeny的博客-CSDN博客_count(1)与count()区别
总结： 有主键使用count（主键列名）最好；如果没有主键或者索引count(1)>count()；只有一种情况就是列只有一列那么count()的效率最高；使用流程：如果表有主键就使用count（主键列名），如果没有主键使用count（1），如果列只有一列就使用count（），count（1）和count（）都计数为null的数据，count(列名）不计数null的数据

主键和索引的区别

• • 主键唯一且不能为空
  • 唯一索引不唯一且能为空（唯一索引可以有多个）
  • 索引可以提高查询的速度
  • 主键和索引都是键，主键是逻辑键，索引是物理键

Q&A

• INTM，M值代表什么含义?

注意数值类型括号后面的数字只是表示宽度而跟存储范围没有关系，比如INT(3)默认显示3位，空格补齐，超出时正常显示。比如INT(5) 赋值为32 实际存储为00032 ，用0补齐

• 什么是覆盖索引

什么是覆盖索引_liaowenxiong的博客-CSDN博客_覆盖索引
总结：一般来说查找非主键索引需要先在非聚集索引中查找得到主键索引然后再在聚集索引中查找，但是这样效率偏低；覆盖索引能创建一个覆盖索引树，其中内容就包括了所覆盖的组合索引，索引直接能从覆盖索引树中查找不必再回表查找

• 大量数据做增删改需要分批多次进行操作
  • 避免主从延迟过大
    • 主库修改时间过长，从库不能访问
    • 修改的row格式binlog日志太多
  • 大批量数据修改涉及锁的锁定从而造成大量的阻塞，长时间的大量阻塞会占满数据库链接导致其他应用无法链接

数据库字段类型和Java的对应关系

• • 数据库decimal对应java什么类型_数据库字段类型和Java的对应关系

预编译的作用

(1)预编译阶段可以优化 sql 的执行
预编译之后的 sql 多数情况下可以直接执行，DBMS 不需要再次编译，越复杂的sql，编译的复杂度将越大，预编译阶段可以合并多次操作为一个操作。可以提升性能。
(2)防止SQL注入
使用预编译，而其后注入的参数将不会再进行SQL编译。也就是说其后注入进来的参数系统将不会认为它会是一条SQL语句，而默认其是一个参数，参数中的or或者and 等就不是SQL语法保留字了。

varchar和char的区别

• char类型的长度是固定的，varchar的长度是可变的

这就表示，存储字符串’abc’，使用char(10)，表示存储的字符将占10个字节（包括7个空字符）
使用varchar(10),则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储

• char占用固定空间但是效率高， varchar占用可变空间（设定了空间上限)空间占用少但是效率低，还有一个问题是可变的varchar容易造成“行迁移”，让I/O花销变大
  • char（1）只占用一个字节，但是varchar（1）占用两个字节【其中有一个字节用来记录长度】