数据库三范式

• 第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。（属性不可分）
• 第二范式：如果关系模式R满足第一范式，并且R的所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。（非主键属性，完全依赖于主键属性）
• 第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.（非主键属性无转递）

Innodb和MyAsm的比较

• 事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
• 并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
• 外键：InnoDB 支持外键。
• 备份：InnoDB 支持在线热备份。
• 崩溃恢复：MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢。
• 其它特性：MyISAM 支持压缩表和空间数据索引。

MySql逻辑架构

• 

InnoDB的逻辑存储结构

• InnoDB存储引擎的逻辑存储结构和Oracle大致相同，所有数据都被逻辑地存放在一个空间中，我们称之为表空间（tablespace）。表空间又由段（segment）、区（extent）、页（page）组成。页在一些文档中有时也称为块（block），1 extent = 64 pages,InnoDB存储引擎的逻辑存储结构大致如图所示。

• 

• 段：常见的段有数据段、索引段、回滚段，数据段为B+树的叶子节点，索引段为B+树的非索引节点

• 区：由连续的页组成的空间，在任何情况下每个区的大小都为1MB。一个区中有64页

• 页：每个页的大小为16kb,各个数据页之间可以组成双向链表。可以通过参数innodb_page_size将页的大小修改为4k,8k,16k。

基础文件

• .frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
• .ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息
• .myd文件：主要用来存储使用MyISAM存储引擎的表数据信息。
• .myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树。

InnoDB的存储结构

InnoDB的表空间

• 系统表空间
  • 主要存储MySQL内部的数据字典数据，如information_schema下的数据。
• 用户表空间
  • 当开启innodb_file_per_table=1时，数据表从系统表空间独立出来存储在以table_name.ibd命令的数据文件中，结构信息存储在table_name.frm文件中。
• Uodo表空间
  • 存储Undo信息，如快照一致读和flashback都是利用undo信息。
• 查询某个表属于哪个表空间
  • select * from information_schema.innodb_tablespace where name=’数据库名/表名字’;

段/Segment

• innodb把逻辑上有关联的区/簇归属为一个段。
• 段是表空间的逻辑组成部分，用来存储具有相同意义的数据，如：B+对中的非叶子节点或B+树中的叶子节点。常见的段有数据段、索引段、回滚段等。

区/簇/Extent

• 一个区/簇是物理上连续分配的一段空间,extent又被划分成连续的页，以存储同一逻辑单元的数据（如下面的索引段、数据段）。一个区/簇，默认由64个连续的页（Page）组成，每个页默认大小为16K。

页/page

• 页的大小默认是16KB
• 当page size为4、8、16KB时，对应一个extent的page数量同步变化，以保证extent(区/簇)大小保持1M不变。当page size为32KB或64KB时，extent内的page数量保证不变，extent同步变为2M和4M;
• 每个页都有一个对应的从0开始的编号，这个编号叫做页号。因为表空间的数据文件会被划分成大小相等的页，所以知道页号，再根据文件的初始位置，就可以计算出页在磁盘中的准确地址。

行/row

索引

• 索引的优点
  • 大大减少了服务器需要扫描的数据量
  • 帮助服务器避免排序和临时表
  • 将随机IO变为顺序IO

B树和B+树

B树

• B Tree 指的是 Balance Tree，也就是平衡树。平衡树是一颗查找树，并且所有叶子节点位于同一层

B+树

• B+树是B树的一个升级版，相对于B树来说B+树更充分的利用了节点的空间，让查询速度更加稳定，其速度完全接近于二分法查找。为什么说B+树查找的效率要比B树更高
• B+树的层级更少：相较于B树B+每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
• B+树查询速度更稳定：B+所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
• B+树天然具备排序功能：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
• B+树全节点遍历更快：B+树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

B+树和红黑树的比较

• B+ 树有更低的树高
• 磁盘访问原理
  • 操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。
  • 如果数据不在同一个磁盘块上，那么通常需要移动制动手臂进行寻道，而制动手臂因为其物理结构导致了移动效率低下，从而增加磁盘数据读取时间。B+ 树相对于红黑树有更低的树高，进行寻道的次数与树高成正比，在同一个磁盘块上进行访问只需要很短的磁盘旋转时间，所以 B+ 树更适合磁盘数据的读取。
• 磁盘预读特性
  • 为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

索引类型

• FULLTEXT
  • 即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率较低的问题。
• HASH
  • 由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
• BTREE
  • BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。一般为2到4层
  • B+Tree对于BTree来说：把非叶子节点冗余一下，提高了范围查找的效率
  • B+树中一个节点为一页或页的倍数最为合适。
  • 演变历程
    • 二叉树
    • BST树(二叉查找树):进行排序操作,满足左子树值<=根值<=右子树
    • AVL树：
• RTREE
  • RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找

索引种类

• 普通索引（INDEX）：仅仅加速查询
• 唯一索引（UNIQUE）：加速查询且列值唯一（可以有null)
• 主键索引（PRIMARY KEY）：加速查询且列值唯一（不可以有null)+表中只有一个
  • 每个表都有主键索引，如果不存在，则InnoDB引擎回首先判断表中是否有非空的唯一索引，如果有则将建表时第一个定义的作为主键索引（此处的顺序为定义索引的顺序而不是建表的列的顺序），如果不符合上述规则，则自动创建一个6字节大小的指针作为主键索引
• 组合索引：多列值组合为一个索引，专门用于组合搜索，其效率大于索引合并
  • 最左匹配：先查第一个列，在查第二个列，按照顺序查询才能进行组和索引查询，不按顺序的会优化
  • 索引下推(ICP)
    • 在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
    • 索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。
    • 例如：SELECT * from user where name like ‘陈%’ and age=20，当有多个请求时，进行最左前缀索引查询，如果age为索引列，会在遍历索引中直接处理age=20的条件，而不用再回表查询一次来判断
• 全文索引（FULLTEXT）：对文本的内容分词进行搜索

聚簇索引和非聚簇索引

聚簇索引

• 按照每个表的主键构造一棵B+树，同时叶子节点中存放的数据即为整张表的行记录数据
• 每个表只能有一个聚集索引，查询优化器倾向于聚集索引，聚集索引的存储在物理上不是连续的，在逻辑上是连续的
• 优点
  • 可以把相关的数据保存在一起
  • 数据访问更快
  • 可以使用覆盖索引查询
• 缺点
  • 聚簇索引针对的情景是IO密集型应用，对于内存，聚簇索没有优势
  • 插入速度严重依赖于插入顺序，按照主键插入是最快的方式
  • 更新聚簇索引列的代价很高，会强制将每个被更新的行移动到新的位置
  • 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题
  • 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候

非聚簇索引（辅助索引）

• 叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以为，每个叶子节点中的索引行中还包含了一个书签。该书签用来告诉InnoDB索引引擎那里可以找到与索引相关的行数据。

• 

• 索引覆盖：从辅助索引就可以查询到数据，不需要再查询聚集索引中的记录。使用辅助索引来查询获得主键的数据

索引的匹配方式

• 全值匹配：和索引中所有列进行匹配
  • select * from people where name = ‘liming’ and age = 18 and sex = ‘1’ 查询三项为组合索引
• 匹配最左前缀：只匹配前面几列
  • select * from people where name = ‘liming’ and age = 18
• 匹配列前缀：匹配某一列的值的开头部分
  • select * from people where name like ‘liming%’
• 匹配范围值：查找某一范围的数据
  • select * from people where age > 18
• 精确匹配某一列并范围匹配另外一列：查询第一列的全部和第二列的部分
  • select * from people where name = ‘liming’ and age > 18
• 只访问索引的查询：覆盖索引
  • select id from people whre age > 18

索引注意事项

创建索引的注意事项

• 索引并不是越多越好，要根据查询有针对性的创建，考虑在WHERE和ORDER BY命令上涉及的列建立索引，可根据EXPLAIN来查看是否用了索引还是全表扫描
• 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描
• 值分布很稀少的字段不适合建索引，例如”性别”这种只有两三个值的字段
• 字符字段只建前缀索引，使用前缀索引就不能使用索引覆盖，并且无法做Order by和Group by
• 字符字段最好不要做主键
• 不用外键，由程序保证约束
• 尽量不用UNIQUE，由程序保证约束
• 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引

那些列适合作为索引

• 查询频繁的列，在where，group by，order by，on从句中出现的列
• where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
• 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好
• 离散度大（不同的值多）的列，放在联合索引前面。查看离散度，通过统计不同的列值来实现，count越大，离散程度越高

索引不生效的原因

• 索引列是表示式的一部分，或是函数的一部分
• 隐式类型转换
• 隐式编码转换
• 使用 order by 造成的全表扫描

视图

视图的概念

• 视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询，他们包含不是数据而是根据需要检索数据的查询。
• 视图提供了一种封装SELECT语句的层次，可用来简化数据处理，重新格式化或保护基础数据

应用范围

• 重用sql
• 简化复杂的sql操纵，在编写查询后，可以方便的重用它而不必知道其基本查询细节
• 使用表的一部分而不是整个表
• 保护数据，可以属于用户访问表的特定部分的权限，而不是整个表的访问权限
• 更改数据格式和表示视图可返回与底层表的表示和格式不同的数据

视图的优缺点

• 优点：
  • 对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
  • 用户通过简单的查询可以从复杂查询中得到结果。
  • 维护数据的独立性，试图可从多个表检索数据
  • 对于相同的数据可产生不同的视图
• 缺点
  • 性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么就无法更改数据