数据库（表强化 sql优化）

数据库系统概论知识总结，包括数据库模型、发展过程，关系数据库、SQL语言、并发控制、数据库恢复技术、数据库设计等内容。

数据库 （表强化 sql优化）

• 表强化
  • 6约束
    • 非空约束 not null
    • 唯一约束 unique
    • 主键约束 primary key
    • 外键约束 foreign key
    • 默认约束 default
    • 检查约束 check
  • 表关联
    • 表的关系分为四种
      • 一对一 one to one QQ和QQ邮箱，员工和员工编号
      • 一对多 one to many 最常见，部门和员工，用户和订单
      • 多对一 many to one 一对多反过来，员工和部门，订单和用户
      • 多对多 many to many 老师和学生，老师和课程
• SQL优化
  • 查询SQL尽量不要使用select *，而是具体字段
    • 理由：
      • 字段多时，大表能达到100多个字段甚至达200多个字段 只取需要的字段，节省资源、减少网络开销 select * 进行查询时，很可能不会用到索引，就会造成全表扫描
  • 避免在where子句中使用or来连接条件
    • 理由：
      • 使用or可能会使索引失效，从而全表扫描
  • 使用varchar代替char
    • 理由：
      • varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间 char按声明大小存储，不足补空格 其次对于查询来说，在一个相对较小的字段内搜索，效率更高
  • 尽量使用数值替代字符串类型
  • 查询尽量避免返回大量数据
    • 如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长
  • 使用explain分析你SQL执行计划
    • SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳，主要看是否使用了索引
  • 是否使用了索引及其扫描类型
    • ALL 全表扫描，没有优化，最慢的方式
    • index 索引全扫描
    • range 索引范围扫描，常用语<，<=，>=，between等操作
    • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
    • eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
    • const/system 单条记录，系统会把匹配行中的其他列作为常数处理，如主键或唯一索引查询
    • null MySQL不访问任何表或索引，直接返回结果
  • 创建name字段的索引
  • 优化like语句
    • 模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效
  • 字符串怪现象
    • 字符串未加单引号时，索引会不起作用，这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
  • 索引不宜太多，一般5个以内
    • 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
    • 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
    • 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
    • insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
    • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要
  • 索引不适合建在有大量重复数据的字段上
    • 如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
  • where限定查询的数据
    • 数据中假定就一个男的记录
  • 避免在where中对字段进行表达式操作
  • 避免在where子句中使用!=或<>操作符
    • 否则引擎将放弃使用索引而进行全表扫描
  • 去重distinct过滤字段要少
    • 带distinct的语句占用cpu时间高于不带distinct的语句
  • where中使用默认值代替null
  • 批量插入性能提升
    • 批量处理是一次事务开启和提交。自然速度飞升
  • 批量删除优化
    • 避免同时修改或删除过多数据，因为会造成cpu利用率过高，会造成锁表操作，从而影响别人对数据库的访问。
  • 伪删除设计
  • 提高group by语句的效率
    • 可以在执行到该语句前，把不需要的记录过滤掉
  • 复合索引最左特性
  • 排序字段创建索引
    • 原则就是where和order by中常出现的字段就创建索引。
  • 删除冗余和重复的索引
  • 不要有超过5个以上的表连接
    • 关联的表个数越多，编译的时间和开销也就越大 每次关联内存中都生成一个临时表 应该把连接表拆开成较小的几个执行，可读性更高 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了 阿里规范中，建议多表联查三张表以下
  • inner join 、left join、right join，优先使用inner join
    • 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优
  • in子查询的优化