数据库优化 理解这些就够了

数据库优化无非是以下整理的这些项, 理解和牢记就够玩了

1. sql语句分类
DDL: 数据定义语言(create, drop, alter)
DQL: 数据查询语言(select)
DML: 数据操纵语言(insert, update, delete)
DCL: 数据控制语言(grant, revoke, commit, rollback)

2. sql约束
主键约束
外键约束
唯一约束(表中的一列数据没有相同的值, 一个表可以又多个唯一约束)
默认约束(插入时, 某些字段默认赋值)
check约束(通过逻辑表达式判断插入有效性)

3. 连接查询
左外连接
右外连接
内连接
交叉连接(笛卡尔积)

4. in和exists的区别
使用in, 先进行子查询, 子查询结果与主表做笛卡尔积, 然后按照条件筛选
使用exists, 先对主表做查询, 根据查询结果对外表进行遍历, 匹配条件

子查询小, 主查询大且有索引 用in
子查询大且有索引, 主查询小 用exists
(in和exists造成了驱动顺序的改变, 这是性能变化的主要因素. 我们以驱动表快慢为性能指标)
not in 内外表都进行全表扫描, 没有用到索引, not exists 的子查询依然可以使用索引, 所以 not exists 比 not in 快

5. varchar和char的却别
varchar 表示变长 插入字符长度相同, 不足补空格, 查询较快
char    表示固定 插入字符长度不同, 不填充空格, 查询较慢

6. drop, delete和truncate的区别
       drop         delete        truncate
速度:    快           慢             较快
类型:    DDL          DML            DDL
回滚:    不可回滚     可回滚          不可回滚
删除:    整表         表内数据        全部数据

7. union 和 union all 的区别
得到两个结果的并集
union会进行去重 排序, union all不做任何处理, 性能较好

8. 临时表
Mysql在执行sql语句的时候会临时创建一些表来存储中间结果
临时表只对当前连接可见, 连接关闭后会释放空间
内存临时表
磁盘临时表
使用到临时表的情况:
  form中的子查询
  disdinct查询 + order by
  order by 和 group by 的子句不一样时
  使用union
  
9. 大数据查询优化
索引优化
sql优化
水平拆分
垂直拆分
中间表
缓存技术
固定长度的表
越小的列访问越快

10. 慢查询
用来记录执行时间超过某个临界值的SQL语句的日志
优化:
  查看索引是否命中
  优化数据库结构
  优化LINIT分页
  
11. 自增ID 和 UUID
自增id
  字段长度较小
  按顺序存放, 利于检索
  无需担心主键重复
  
  数据迁移, 表合并非常麻烦
  高并发环境中, 竞争自增锁会降低数据库的吞吐能力
UUID
  唯一标识码
  在应用层生成id, 提高数据库的吞吐能力
  生成id时影响插入速度
  UUID之间比较大小很慢, 影响查询速度
  
  Mysql中推荐使用自增主键, 主键索引是一种聚簇索引.
  主键索引的B+输的叶节点按照顺序存储了主键值及数据, 如果主键索引是自增ID, 只需要顺序排列即可.
  如果是UUID, 在插入时会造成大量的数据移动, 产生大量的内存碎片, 性能下降
  
12. 字段设置成NOT NULL
NULL和空值是有区别的, 空值不占用空间, 而NUll是占用空间的, 所有设置为NOT NULL还能继续插入空值

NULL会影响一些函数统计, count, B树不存储NULL, 所以索引用不到NULL
NOT IN 子查询在NULL的情况下返回的结果都是空值

13. 优化查询
正确使用索引, 尽量做到索引覆盖
分页处理
只返回需要的字段
合理使用排序
减少比较操作
复杂运算在服务端进行
数据库并行处理
大查询拆分为小查询
分解关联查询, 使用缓存的效率更高
LINIT偏移量较大的时候 可以记录每次去除的最大ID, 下次利用ID进行查询

14. 优化union查询
如果不需要去重和排序, 可以使用union all

15. 优化where子句
不适用 !=, <> 避免全表扫描
不适用null或空值判断, 尽量将字段设置为not null
尽量使用union all 代替or
在where和order by涉及的列建立索引
减少使用in或not in, 会进行全表扫描
在where子句中使用参数会导致全表扫描
避免表达式或者函数操作, 会导致索引失效

16. sql语句执行顺序
from, on, join, where, group by, having, select, distinct, order by

17. 大表优化
限定数据查询范围
读写分离, 主库负责写, 从库负责读
垂直分表, 字段拆分成多表
水平分表, 数据拆分到多个表中
对单表进行优化, 对字段, 索引, 查询语句进行优化
添加缓存

18. 垂直分表, 垂直分库, 水平分表, 水平分库
垂直分表: 将一个表按照字段拆分成多表(表结构)
垂直分库: 按照业务进行分表, 部署到不同的数据库上(表结构)
水平分表: 将一个表中的数据拆分到多个表中(表数据)
水平分库: 把一个表的数据按照一定规则拆分到不同数据库中(表数据)

19. 分库分表中ID处理
需要一个全局ID:
  UUID(不重复, 占空间大, 不适合索引)
  数据库自增ID(需要一个专门生成主键的库, 高并发存在瓶颈)
  Redis生成ID(性能好)
  Twitter的snowflake算法
  
20. Mysql复制原理
保证主从服务器的数据一致性
在向主服务器插入数据后, 从服务器会自动将插入的数据同步过来
主从复制原理: 
  三个线程: binlog线程, I/O线程, SQL线程
    binlog线程: 负责将主服务器上的数据更改写进二进制日志中
    I/O线程: 负责从主服务器上读取二进制日志, 并写入到从服务器的中继日志(Relay log)中
    SQL线程: 负责取中继日志, 解析日志并执行主服务器上的数据更改
主从复制的作用:
  高可用和故障转移
  负载均衡
  数据备份
  升级测试

------------- 再次加深印象 ------------
1. 选取最适合的字段属性
在满足业务需求的基础上, 尽可能的将字段的宽度设置的小一点

2. 数据库索引
索引应建立在where判断和order by排序的字段上

3. 尽量边牧使用子查询, 可以使用JOIN连接查询代替
因为使用子查询会建立临时表

4. union all能满足业务需求的情况下, 不使用union

5. where子句避免使用!=, <>操作符

6. 一般情况下不建议使用LIKE操作

7. WHERE子句中避免对字段进行表达式操作

8. 不使用 * 进行全字段查询

9. 分页查询: 使用ID索引进行分页, 或使用上一页查询结果中ID最大值进行查询

10. 某些情况下使用exists代替in