初学数据库使用优化

偏执的太偏执、 2022-04-03 15:54 130阅读 0赞

### 需求分析 ###

1.  了解系统中所要存储的数据
2.  了解数据的存储特点
3.  了解数据的生命周期

> 思路：  
> 实体及实体之间的关系(1对多，1对1，多对多)、  
> 实体所包含的属性、  
> 哪些属性或属性的组合可以唯一标识的一个实体

### 逻辑设计 ###

1.  将需求转化为数据库的逻辑模型
2.  通过ER图的型式对逻辑模型进行展示
3.  同所选用的具体的DBMS系统无关

设计范式概要：

1. 第一范式：数据库中的表都是二维表
    
    2. 第二范式：数据库中的表中不存在非关键字段对任一候选关键字段的部分函数依赖。
    
    3. 第三范式：如果数据表中不存在非关键字段，对任意候选关键字段的传递函数依赖则符合第三范式
    
    4. BC范式：数据库中如果不存在任何字段对任一候选关键字段的传递函数依赖则符合BC范式。也就是说如果是复合关键字，则复合关键字之间也不能存在函数依赖关系。

### 物理设计 ###

1.  选择合适的数据库管理系统
    
    1.  oracle、sqlServer商业数据库:成本
    2.  mysql、PgSql开源数据库:免费、事务成本比oracal高
    3.  mysqyl、PgSql适用于互联网项目, oracle、sqlServer更适合企业级项目
2.  定义数据库、表及字段的命名规范
    
    1.  可读性原则
    2.  表意向原则
    3.  长名原则
3.  根据所选的DBMS系统选择合适的字段类型
    
    1.  优先选择数字类型，其次选择日期或者二进制类型，最后选择字符类型。对于相同级别的数据类型，应该有限选择占用空间小的数据类型。(主要关系到存储空间不同的类型占用不同存储空间)
    2.  对比数据进行比较操作时：同样的数据，字符处理往往比数字处理慢
    3.  数据库中，数据处理以页为单位，列的长度越小，利于性能提升（主要是提升磁盘io效率）
    4.  如果列中要存储的数据长度差不多的一致的，则应该考虑用char;否则应该考虑用varchar
    5.  如果列中的最大数据长度小鱼50Byte，则一般也考虑用char、（当然，如果这个列很少用，则基于节省空间和减少IO的考虑，还是可以选择用varchar）
    6.  一般不宜定义大于50Byte的char类型列
    7.  decimal用于存储精确数据，而float只能用于存储非常精确数据。故精确数据只能选择用decimal类型。
    8.  由于float的存储空间开销一般比decimal小(精确到七位小数只需要4个字节，而精确到15位小数只需要8字节)，故非精确数据优先选择float类型。
    9.  时间类型存储：
        
        1.  使用int存储时间字段：
            
            1.  字段长度比datetime小
            2.  使用不方便要进行函数转换
            3.  只能存储到2038-1-19 11:14:07即2147483648
        2.  需要存储的时间粒度：年月日时分秒周选择不同时间类型
4.  如何选择主键
    
    1.  区分业务主键和数据库主键：业务主键用于标识业务数据，进行与表之间的关联。数据库主键为了优化数据存储(innodb会生成6字节的隐含主键)
    2.  根据数据库的类型，考虑主键是否要顺序增长：有些数据库是按主键的顺序逻辑存储的
    3.  主键的字段类型所占空间要尽可能的小：对于使用聚集索引方式存储的表，每个索引后都会附加主键信息
5.  避免使用外键约束
    
    1.  建立数据导入效率
    2.  增加维护成本
    3.  虽然不建议使用外检约束，但是相关联的列上一定要建立索引
6.  避免使用触发器
    
    1.  降低数据导入效率
    2.  可能会出现意想不到的数据异常
    3.  使业务逻辑变得复杂
7.  预留字段
    
    1.  无法准确的知道预留字段的类型
    2.  无法准确的知道预留字段中所存储的内容
    3.  后期维护预留字段所要的成本，同增加一个字段所需要的成本是相同的
    4.  严禁使用预留字段
8.  反范式化设计（空间换时间）
    
    1.  减少表关联的数量
    2.  增加数据的读取效率
    3.  反范式化一定要适度

### 维护优化 ###

1.  维护数据字典
    
    1.  第三方工具对数据字典进行维护
    2.  利用数据库本身的备注字段来维护数据库字典(可以导出备注)，但是需要注意维护备注
2.  维护索引
    
    1.  如何选择适合的列建立索引
        
        1.  出现在where从句，group by从句，order by 从句的列
        2.  可选择性高的列要放到索引的前面
        3.  索引中的列不要包括太长的数据类型
    2.  索引并不是越多越好
        
        1.  过多的索引不但会降低写效率而且会降低读的效率
        2.  定期维护索引碎片，碎片会影响读取效率
        3.  在sql语句中不要使用强制索引关键字，在后面表修改处理后可能不合适
3.  维护表结构
    
    1.  使用在线变更表结构的工具：pt-online-schema-change，mysql5.6之后本身支持在线表结构的变更
    2.  同时对数据字典进行维护
    3.  控制表的宽度和大小（拆分）
    4.  数据库中适合的操作：
        
        1.  批量操作大于逐条操作
        2.  禁止使用select \* 这样的查询，浪费io及字段名变更可能影响程序的出错
        3.  控制使用用户自定义函数：使用函数不能使用索引
        4.  不要使用数据库中的全文索引：需要另外的所以文件进行维护，对中文支持不友好，可以选择适当的搜索引擎代替
4.  在适当的时候对表进行水平拆分或垂直拆分
    
    1.  垂直拆分：列分开成两张表
        
        1.  经常一起查询的列放到一起：优化io减少查询复杂程度
        2.  text、blob等大字段拆分出到附加表中
    2.  水平拆分：一张表数据放到相同结构多张表里
        
        1.  主键hash处理分表

### 个人总结 ###

1.  平常使用中数据库表的设计主要是针对业务需求，不针对实际业务有需求的设计都是流氓设计
2.  针对不同的数据确定其数据的作用、重要性、生命周期、以及查看修改删除插入的频率都应该做出分析。对于朝生暮死的数据尽量不放数据库，生命周期过短，可以考虑使用nosql数据库
3.  反范式的设计工作中主要是字段冗余，这里主要是为了提高查询效率，那么说明这里的数据主要也是查询为主。对于这样的设计一定要注意是否真的冗余到了点上，是否真的提供了效率，在更新主体时是否也需要同步这部分冗余数据
4.  数据字典，在工作中一般都是在数据库中有专门的表设计做数据字典，这里需要注意及时做维护
5.  减少字段查询，减少io也是一种手段。平常其实很多时候为了偷懒直接select \* 这其实是个坏毛病
6.  索引使用前应当知道在查询sql中什么时候使用了索引，哪些写法不会使用索引，这样才真的达到了建立索引的目的。
7.  分表分库个人觉得数据量不到就不要这么搞吧，分了之后对数据的处理又要多不少步骤，不要过度设计吧。
8.  数据量过高可以考虑搜索引擎进行搜索，减小数据库压力，增加搜索效率，搜索引擎可以进行分词对大字段搜索的支持也好得多

### 索引分享 ###

##### SQL什么条件会使用索引？ #####

当字段上建有索引时，通常以下情况会使用索引：

1.  INDEX\_COLUMN = ?
2.  INDEX\_COLUMN > ?
3.  INDEX\_COLUMN >= ?
4.  INDEX\_COLUMN < ?
5.  INDEX\_COLUMN <= ?
6.  INDEX\_COLUMN between ? and ?
7.  INDEX\_COLUMN in (?,?,…,?)
8.  INDEX\_COLUMN like ?||’%’（后导模糊查询）这个面试经常会问到
9.  T1. INDEX\_COLUMN=T2. COLUMN1（两个表通过索引字段关联）

##### SQL什么条件不会使用索引？ #####

查询条件 不能使用索引原因

1.  INDEX\_COLUMN <> ?INDEX\_COLUMN not in (?,?,…,?) 不等于操作不能使用索引
2.  function(INDEX\_COLUMN) = ?INDEX\_COLUMN + 1 = ?INDEX\_COLUMN || ‘a’ = ?  
    经过普通运算或函数运算后的索引字段不能使用索引
3.  INDEX\_COLUMN like ‘%’||?INDEX\_COLUMN like ‘%’||?||’%’  
    含前导模糊查询的Like语法不能使用索引
4.  INDEX\_COLUMN is null B-TREE索引里不保存字段为NULL值记录，因此IS  
    NULL不能使用索引
5.  NUMBER\_INDEX\_COLUMN='12345’CHAR\_INDEX\_COLUMN=12345
    
    Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。  
    a.INDEX\_COLUMN=a.COLUMN\_1 给索引查询的值应是已知数据，不能是未知字段值。  
    注：经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的Select \* from company where name=?Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。

##### 我们一般在什么字段上建索引？ #####

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1.  字段出现在查询条件中，并且查询条件可以使用索引；
2.  语句执行频率高，一天会有几千次以上；
3.  字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：  
    小表(记录数小于10000行的表)：筛选比例<10%；  
    大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16 单条记录长度≈字段平均内容长度之和+字段数*2

###### 例如： ######

1.  需要建立索引字段：外键
    
    1.  PRODUCT\_ID,COMPANY\_ID,MEMBER\_ID,ORDER\_ID,TRADE\_ID,PAY\_ID  
        有对像或身份标识意义字段 HASH\_CODE,USERNAME,IDCARD\_NO,EMAIL,TEL\_NO,IM\_NO
2.  索引慎用字段：需要进行数据分布及使用场景详细评估
    
    1.  日期 GMT\_CREATE,GMT\_MODIFIED
    2.  年月 YEAR,MONTH
    3.  状态标志 PRODUCT\_STATUS,ORDER\_STATUS,IS\_DELETE,VIP\_FLAG
    4.  类型 ORDER\_TYPE,IMAGE\_TYPE,GENDER,CURRENCY\_TYPE
    5.  区域 COUNTRY,PROVINCE,CITY
    6.  操作人员 CREATOR,AUDITOR
    7.  数值 LEVEL,AMOUNT,SCORE
    8.  长字符 ADDRESS,COMPANY\_NAME,SUMMARY,SUBJECT
3.  不适合建索引的字段：描述备注
    
    1.  DESCRIPTION,REMARK,MEMO,DETAIL
    2.  大字段 FILE\_CONTENT,EMAIL\_CONTENT