索引

1. BTREE索引

1.1. 结构

1.1.1. 数据结构：平衡多路查找树

1.1.1.1. 高度低、自动层次控制

1.1.1.2. 查找与写入性能 O(log2N)

1.1.1.3. 插入时节点可能分裂，删除时可能合并

1.1.2. 叶子节点存储：索引列值 + rowid

1.2. 三大特点

1.2.1. 高度低

1.2.1.1. e.g. 对索引列做等值查询时，50w与500w数据量几乎无区别（若在索引树中高度相同，执行效率完全一样）

1.2.1.2. e.g. 数百亿数据量在索引里高度可能才6层

1.2.1.3. IO次数估算*

1.2.2. 存储列值

1.2.2.1. 只查询索引列字段不必回表

1.2.2.2. COUNT利用索引的条件

1.2.2.2.1. 指定列非空：比如 sql的where中，带比较符号 = or < or > ，自然排除了null值

1.2.2.2.2. 或者索引列本身有not null约束

1.2.2.3. SUM/AVG利用索引条件

1.2.2.3.1. 同COUNT

1.2.2.3.2. 技巧：SUM AVG 和COUNT可以写在一起，一次索引扫描即可查出来

1.2.3. 索引有序

1.2.3.1. Min/Max可直接利用索引

1.2.3.1.1. 索引列是否有null不影响

1.2.3.1.2. 查找时，直接找向索引树的最左/最右端，逻辑读很低

1.2.3.1.3. 陷阱：对同一索引字段同时查最小值和最大值，不会走索引 select min(a), max(a) from table

1.2.3.2. 排序相关语句可通过索引优化

1.2.3.2.1. order by

1.2.3.2.2. distinct

1.2.3.2.3. union

1.3. 组合索引

1.3.1. 组合列返回结果越少越高效

1.3.2. 第一列可作为单列索引

1.3.3. 避免回表

1.3.3.1. 典型案例: 若频繁使用select id, name from xxx 可以将(id, name)作为组合索引，避免name字段的回表读

1.3.4. 建立原则

1.3.4.1. 优先照顾第一列

1.3.4.2. 重复记录少的字段在前（利于范围查询）

1.3.4.3. 一列等值一列范围查找: 等值查找的列在前

1.3.4.4. 索引列不宜过多，建议<=3

1.4. 聚合因子

1.4.1. 定义：表明有多少临近的索引条目指到不同的数据块（类比windows磁盘碎片整理）

1.4.2. 影响回表查询的速度

1.4.3. 可通过重组表记录优化

2. 位图索引

2.1. 结构

2.1.1. 位图片段<key, start rowid, end rowid, bitmap>

2.1.2. 一"种"key对应多行

2.1.3. 与B树扫描方式不同，位图索引核心操作是位运算

2.2. 特点

2.2.1. 支持null值

2.2.2. COUNT AND OR操作效率极高，多个索引列可共同作用

2.2.3. 占用空间很小

2.2.4. 对DML极不友好，锁等待

2.2.4.1. insert into Table(sex) values('男')，事务提交前会阻塞所有其他sex='男'的插入/更新;

2.2.4.2. 从key1 update到key2甚至会阻塞所有涉及到key1 key2的插入/更新

2.2.5. 是一种适应范围比较窄，但是特效针对性很强的索引类型

2.3. 应用场景

2.3.1. 列的基数少(重复率高)、可枚举

2.3.1.1. 典型应用: 性别、婚否、状态

2.3.2. 索引列数据极少更新

2.3.3. 适合DSS, OLAP系统

3. 函数索引

3.1. 前置知识

3.1.1. 对索引列做运算让索引失效

3.1.2. 尽量避免列运算(通过转换SQL写法)

3.2. 特点

3.2.1. 性能介于BTREE索引与全表扫描