数据库索引

数据库系统的抽象层次

• 物理层：描述数据是怎样存储的
• 逻辑层：描述数据库中存储什么数据及这些数据间存在什么关系
• 视图层：只描述整个数据库的某个部分，可能同时存在多个视图

特定时刻存储在 数据库中的信息的集合称作数据库的一个实例（instance）；数据库的整体设计称作数据库模式（schema）。物理模式在物理层描述数据库的设计；逻辑模式则在逻辑层描述数据库的设计；数据库在视图层也可以有几种模式，有时称为子模式。

程序员使用逻辑模式来构造数据库应用程序。应用程序如果不依赖于物理模式，就被称为是具有物理数据独立性，因此即使物理模式发生了改变也无需重写应用程序。

关系数据库的设计

• E-R 模型：E 和 R 分别对应一张表
  • 实体 Entity
  • 关系 Relation
• 分解和合并数据库模式
  • 所有有效的分别都必须是无损的
• 规范化
  • 函数依赖
    • 完全函数依赖
    • 部分函数依赖
    • 传递函数依赖
  • 设计范式
    • 1NF: 列只包含不可分割的单值（scalar），一个表不能包含重复的列
    • 2NF：每一个非主属性必须依赖于整个主键
    • 3NF：每一个非主属性必须只能依赖于整个主键；消除了传递依赖
    • BCNF: 一个非主属性不能依赖于另外一个非主属性
    • 4NF：一个表的多值依赖关系不能超过1个
    • 5NF: 一个表不断被拆分直到不包含冗余
    • DKNF or 6NF: 一个关系的每个约束都只依赖于键约束和值域约束

数据库索引

• 磁盘物理特性
  • 性能度量
  • RAID：独立磁盘冗余阵列，通过冗余提高可靠性，通过并行提高性能
    • RAID 0：无冗余拆分 （1 -> 4)
    • RAID 1：使用块级拆分的磁盘镜像 (4 -> 8)
      • 注意另一种说法：使用 RAID 1+0 或 RAID 10 指代使用拆分的镜像 (4 -> 8)，并且使用 RAID 1 指代不使用拆分的镜像 (1 -> 2)
    • RAID 2：使用内存风格的纠错码达到镜像功能
    • RAID 3：位交叉的奇偶校验
    • RAID 4：块交叉的奇偶校验
    • RAID 5：块交叉的分布奇偶校验
    • RAID 6：P+Q冗余
• 文件组织
• 索引
  • 一些概念
    • 聚集和非聚集索引
      • 聚集索引：包含记录的文件按照某个搜索码决定的顺序排序，这个搜索码对应的索引称为聚集索引，也称主索引。在搜索码上又聚集索引的文件称为索引顺序文件。
      • 非聚集索引：也称辅助索引
      • 区别：聚集索引在叶子节点存储的是表中的数据；非聚集索引在叶子节点存储的是主键和索引列。
    • 多级索引：类似于跳表
    • 稠密索引和稀疏索引
      • 非聚集索引必须使用稠密索引
    • 多码上的索引
      • 索引记录本身存储了多个搜索码值
      • 规则
        • 全列匹配会自动调整查询条件顺序以使用索引
        • 最左前缀法则：即查询从索引的最左前列开始并且不跳过索引中的列
        • 范围查询后面的列无法使用索引
  • 索引失效
    • 深入理解MySQL索引
    • 一张图搞懂MySQL的索引失效
      • 违反最左前缀法则
      • 使用不等于（!=或<>）不会利用索引
      • or 连接多个搜索码不会利用索引（为什么不单独使用索引查找并求交集呢？）
      • 在索引上做一些操作的查询不会利用索引
      • like 匹配以通配符开头不会利用索引（有点类似最左前缀原理）
      • order by 或 group by 违反最左前缀法则
      • …
  • 索引使用的数据结构
    • B+树索引
      • B+树结构可用于索引，也可用于文件内记录的管理
    • 散列索引
      • 静态散列、动态散列
    • 位图索引
  • 主流数据库支持的索引类型
    • MySQL

      • Most MySQL indexes (PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT) are stored in B-trees. Exceptions: Indexes on spatial data types use R-trees; MEMORY tables also support hash indexes; InnoDB uses inverted lists for FULLTEXT indexes.

        大多数 MySQL 索引使用 B 树索引，除了：空间数据使用 R 树；MEMORY 存储引擎另外也支持哈希索引；InnoDB 的全文索引使用倒排列表

      • CREATE INDEX

      • 普通索引

      • 唯一索引 (UNIQUE)

      • 主键索引 (PRIMARY KEY)

      • 组合索引 (multiple-column)

      • 全文索引 (FULLTEXT)

    • PostgreSQL

      • PostgreSQL提供了多种索引类型：B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了一种不同的算法来适应不同类型的查询。默认情况下，CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

      • MySQL 和 PostgreSQL 比较

查询优化

• 慢日志
  • 开启慢查询日志 set global slow_query_log=on;
  • set global log_queries_not_using_indexes=on;
  • show variables like 'long_query_time';
  • set global long_query_time=5;
  • 查看慢查询日志的位置 show variables like 'slow%';
• 通过 Explain 查看SQL的执行计划
• SQL 优化
  • select 别用 *
• 索引优化
  • 选择合适的索引列，选择在where，group by，order by，on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引。
  • 索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )
  • 离散度大得列放在联合索引前面
• 数据表结构优化
  • 分解表
  • 增加冗余字段
  • 增加中间表
• 数据库配置优化
• 硬件优化

OLTP vs OLAP

样本数据下载

• MySQL :: Employees Sample Database

书籍

• 数据库系统概念 (豆瓣)