mysql知识集

索引原理

myisam存储引擎将一张表分为表结构文件、索引文件、数据文件三个文件存储

innodb存储引擎分两个文件：表结构文件、索引及数据文件

innodb使用b+树作为索引结构，对于主键索引，根节点不存放数据，只存放索引值，叶子节点会存完整的行数据，根节点和叶子节点的数据会冗余，根节点包含索引值和指向下一级的指针，下一级是左闭右开的区间（包含父节点左边的索引值，不包含右边的）；非主键索引，叶子节点存放的值是该行数据的主键

叶子节点的索引的指针会指向下一个相邻的叶子节点，提升范围查找的效率。hash索引精确查找效率高，但范围查询不好。

innodb的最小存储单元为页，一页为16k,假设主键为8Byte的bigint类型，指针长度为6Byte，则一页可以存放的索引数是16*1024/(8+6)，约为1170，为千级的量级，如果为三级索引树，第三级会存完整的行数据，假定一行数据的大小为1k,那么一页大小可以存放16条数据，那么整个三级索引树可以存放的数据为1170*1170*16，约为2千万，为千万量级。

二叉树：如果数据只在根节点的一遍，极端情况下会退化成链表，树的深度很深。

红黑树：平衡二叉树，是一 棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。解决了二叉查找树退化成链表的问题。依然会有很深的深度

skiplist（调表）：多层的有序链表 [参考文章](SkipList(跳跃表)详解_才不是本人的博客-优快云博客_skiplist)

跳表与B+树优势对比：[参考文章](Mysql 的索引为什么使用 B+ 树而不使用跳表？)

存放相同的记录数（如2千万），B+树三层，skipList 24层。B+树是自平衡的多分树，插入时需要自平衡，旋转树结构，skiplist依赖随机算法，写性能高，读性能差。redis使用跳表来实现ZSET，实现简单，内存操作，不考虑磁盘io。

B-树和B+树都是多分树。

B-树：根节点和叶子节点都存数据，叶子节点的指针为空。每一层能存的数据条数少，存相同行数的数据，B-树需要更深的树结构。

B+树：根节点只存索引值，叶子节点才存数据，相同的树层次可以存放更多行数据。

索引类型

1. 主键索引、唯一索引、普通索引、全文索引(低版本MySQL只有myisam引擎支持，对文本做分词后，解决模糊查询不走索引的问题，有限制，中文支持不好)、联合索引

2. 聚簇索引、非聚簇索引。innodb引擎一张表只会有一个聚簇索引，通常是主键，叶子节点存放着一行记录的所有字段；非聚簇索引的叶子节点存放的是主键值，非聚簇索引无法命中覆盖索引时会造成两次b+树的搜索

3. 覆盖索引，从辅助索引中就能获取到需要的记录，而不再需要再次遍历聚簇索引。覆盖索引因为不包含像聚簇索引的所有字段，因此可以减少大量io操作

4. MySQL分区表暂不支持全局索引，仅支持本地索引。（MySQL分区是全局索引还是本地索引_mysql分区索引和全局索引-优快云博客）

oracle索引类型

1. 按索引存储的数据结构分：B-tree索引（不指定时默认），位图（bitmap）索引，hash索引等

2. 按索引的唯一性分： 唯一索引（UNIQUE）、普通索引

3. 根据索引的物理存储分（本地索引（local）和全局索引(global)）:一般是对于分区表的，对于普通表也可以建分区索引（不常用）

[参考文章](Oracle 本地索引(local)与全局索引(global)的区别_local索引和global索引-优快云博客)

        * 分区索引分为本地索引(local index)和全局索引(global index)。

        * 对于local索引，每一个表分区对应一个索引分区，当表的分区发生变化时，索引的维护由Oracle自动进行。

        * 对于global索引，可以选择是否分区，而且索引的分区可以不和表分区相对应。当对分区进行维护操作时，通常会导致全局索引的INVALID,必须在执行完操作后REBULID。

        * 本地索引分为有前缀(PREFIX)的本地索引和无前缀(NOPREFIX)的本地索引。而全局索引目前只支持有前缀的索引。

-- 本地索引创建

create  table student1 (id number,stu_no varchar2(100))
partition by RANGE (id)
(
partition p1 values less than (100) tablespace users1,
partition p2 values less than (200) tablespace users2,
partition p3 values less than (maxvalue) tablespace users3
);

--因为id是分区键，所以这样就创建了一个有前缀的本地索引
create index stu_prefix_index on student1(id) local;

--因为stu_no不是分区键，所以这样就创建了一个无前缀的本地索引
create  index stu_noprefix_index on student1(stu_no) local;

-- 全局索引举例

-- 建表

CREATE  TABLE ORDERS(
       ORDER_NO   NUMBER,
       PART_NO    VARCHAR2(40),
       ORD_DATE   DATE
)
PARTITION BY RANGE(ORD_DATE)
(PARTITION Q1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION Q2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION Q3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION Q4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION Q5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION Q6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION Q7 VALUES LESS THAN (TO_DATE('2014-07-01','YYYY-MM-DD'))
);

-- 创建全局索引，且索引分区键和表分区键相同

CREATE INDEX ORDERS_GLOBAL_1_IDX ON ORDERS(ORD_DATE) GLOBAL
PARTITION BY RANGE(ORD_DATE)
(PARTITION GLOBAL1 VALUES LESS THAN (TO_DATE('2014-01-01','YYYY-MM-DD'))
,PARTITION GLOBAL2 VALUES LESS THAN (TO_DATE('2014-02-01','YYYY-MM-DD'))
,PARTITION GLOBAL3 VALUES LESS THAN (TO_DATE('2014-03-01','YYYY-MM-DD'))
,PARTITION GLOBAL4 VALUES LESS THAN (TO_DATE('2014-04-01','YYYY-MM-DD'))
,PARTITION GLOBAL5 VALUES LESS THAN (TO_DATE('2014-05-01','YYYY-MM-DD'))
,PARTITION GLOBAL6 VALUES LESS THAN (TO_DATE('2014-06-01','YYYY-MM-DD'))
,PARTITION GLOBAL7 VALUES LESS THAN (TO_DATE('2014-