数据库设计

  • 尽可能符合数据库三大范式
  • 具有可用性
  • 尽量使用NOT NULL列，以便优化查询。NULL值难以被数据库优化。
  • 为经常作为查询条件的列创建索引，以优化速度。
  • 唯一索引可以优化查询速度。
  • 不要把表的主键作为业务使用

数据库列名

  • ID 作为主键和外键使用，与业务无关
  • NO 作为序号，标记使用，对外提供，一般有唯一性质
  • NUM 单纯的数值
  • TYPE 类型，一般用数值(byte)或枚举类型，使用数值考虑新建一张表提供对应的释义
  • STATE/STATUS 状态 一般用数值或枚举类型（state所指的状态，一般都是有限的、可列举的，status则是不可确定的。）

表优化和列类型选择

表优化
  1. 变长与定长字段分离（核心且常用字段宜建成定长字段放在一张表中）
  2. 常用与不常用字段分离
  3. 在需要一对多关联统计字段加冗余统计字段

列类型选择
  1. 字段类型优先级（整型 > date,time > enum,char > varchar > blob,text）
  2. 定长字段长度满足需求前提下尽可能小，大的字段浪费内存，影响速度。
  3. 尽量少用NULL，NULL不利于索引，需使用特殊字节标注。

字段类型特点
  1. 整型：定长，无国家／地区，字符集差异
  2. time：定长，速度快，节省空间，考虑时区
  3. enum：能起到约束值的目的，内部使用整形存储，但使用char联查时，内部要经历char与整型转换
  4. char：定长，需考虑字符集与校对集（排序）
  5. varchar：不定长，需考虑字符集与校对集（排序）
  6. text，blob，无法使用内存临时表，排序等操作只能在磁盘进行

索引类型（MYSQL）

  • btree索引：默认使用，可以提高查询，排序，分组速度。
  • hash索引：内存模式默认使用，速度快，利率时间复杂度为O(1)，缺点，Hash冲突，无法对范围查找进行优化，无法利用前缀索引，排序无法优化。

btree索引使用误区

  1. 在where条件常用列都加上索引（独立的索引同时只能使用一个）
  2. 多列建立索引后，where查询其中任何列都能使用索引（使用索引需满足左前缀要求，必须有前一个的查询条件才能使用后面列的索引）

聚簇索引和非聚簇索引（MYSQL）
  • 聚簇索引（数据和索引不分离 innodb的主索引文件，直接存放该行数据，次级索引指向对主键的引用）
  • 非聚簇索引（数据和索引分离 myisam的主索引和次级索引，都指向物理行（磁盘位置））

索引覆盖
  • 如果查询的行恰好是索引的一部分，那么查询只需要在索引树上进行，不需要回行到磁盘文件中查找。

理想的索引
  1. 查询频繁
  2. 区分度高（区分度越高，效果越好）
  3. 长度小（长度越小速度越快）
  4. 尽量能覆盖常用查询字段
  5. 多列索引需根据具体业务决定索引列顺序

博客

数据库优化

mysql优化实践

博客

explain

http://blog.csdn.net/xiaolyuh123/article/details/53116168

联合查询驱动表

  • 小结果集驱动大结果集
  • JOIN 语句 后面一张表是驱动表
  • 如果对大表不加任何约束条件，则数据库一般会自动优化，选择小表作为驱动表。

mysql临时表

http://blog.csdn.net/xiaolyuh123/article/details/53286033

  • 以下情况会产生临时表
  1. UNION查询；
  2. 用到TEMPTABLE算法或者是UNION查询中的视图；
  3. ORDER BY和GROUP BY的子句不一样时；
  4. 表连接中，ORDER BY的列不是驱动表中的；
  5. DISTINCT查询并且加上ORDER BY时；
  6. SQL中用到SQL_SMALL_RESULT选项时；
  7. FROM中的子查询；
  8. 子查询或者semi-join时创建的表；
  • 以下几种情况下，会创建磁盘临时表：
  1. 数据表中包含BLOB/TEXT列；
  2. 在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列（或者超过 512字节的 二进制类型列，在5.6.15之前只管是否超过512字节）；
  3. 在SELECT、UNION、UNION ALL查询中，存在最大长度超过512的列（对于字符串类型是512个字符，对于二进制类型则是512字节）；
  4. 执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令，因为它们的执行结果用到了BLOB列类型。

mysql索引

http://blog.csdn.net/xiaolyuh123/article/details/53116193

  • union总是产生临时表

sql优化建议

  • order by的列应使用驱动表中的列
  • group by 的列要有索引，并且应和order by的列一致
  • 尽量使用union all而不使用union（union会进行排序去重，产生临时表）
  • 慎用in和not in，会导致无法使用索引，连续条件使用between或者< and >
  • 避免在where条件中使用or连接条件，会导致无法使用索引
  • 不要在where条件中使用!=或<>操作符，会导致无法使用索引
  • 不要在where条件用 IS NULL 或 IS NOT NULL， 会导致无法使用索引
  • 使用索引，要遵循左前缀原则
  • 避免在where条件中对字段进行表达式操作，会导致无法使用索引
  • 索引只有对连续的条件才有用处，非连续的条件难以使用索引

Oracle优化实践

执行计划查看

  • 可以通过以下语句查看执行计划
  1. explain plan for select * from table_name;
  2. select * from table(dbms_xplan.display);;
  • 可以使用数据库工具查看

数据恢复

mysql日志文件

  • 通过binlog文件生成sql文件
  1. # 示例语句
  2. mysqlbinlog --base64-output=decode-rows -v -v  mysql-bin.000598 > out.sql

数据库优化方案

字段加索引

优化sql

加缓存

读写分离

  • 数据量增大

数据库拆分

  • 垂直拆分，根据业务功能拆分，拆列
  • 水平拆分，拆行，根据某些业务键取模，如用户。

分库分表解决方案

应用层

proxy

  • 社区：mycat
  • 数字：Atlas
  • 百度：heinsberge
  • youtube：vitess
  • 金山：kingshard
  • 商业版：Oneproxy

总结

  • 数据库读多写少用缓存，写多读少用队列。