概要: 索引(MySQL中的key)是存储引擎用于快速找到记录的一种数据结构
索引文件的存储是独立与存储的数据的一部分 就像是书的目录与内容的关系
索引本身也很大 因此索引往往以索引文件的形式存储的磁盘上 因此索引查找过程中就要产生磁盘I/O消耗 I/O读取直接地影响了数据库的效率 所以索引要尽量减少 查找过程中磁盘I/O的存取次数
索引优点
1.减少了服务器所需要扫描的数据量
2.避免了排序和生成临时表
3.随机的I/O变为顺序I/O
B-Tree索引
说道最基础的当然是B-Tree索引 它使用B-Tree数据结构来存储数据
(1.MySQL将一个节点的大小设为等于一个页 这样每个节点只需要一次I/O就可以完全载入)
B-tree又叫平衡多路查找树 关于他的定义各种百科资料非常全 也如图所示:
简单的概括 一个m阶的B-tree
1.每个节点最多有有m-1个关键字
2.每个节点有m个孩子
3.叶子节点到根的距离相同
4.每个节点最少有floor(m/2)个关键字 最少有ceil(m/2)个孩子
5.如图所示根据关键字来决定插入的位置
叶子结点不包含关键字 存储指针
InooDB的二级索引在叶子节点保存了主键值
..................................................................
( B E G )
/ / \ \
A ( B C D) (E F ) (G............Z)
.....以下略
............................................................................. 就像这样
只有叶子节点是指向被索引的数据的
如果建立一个表有如下索引
create table xxx(
....
key(last_name,first_name,dob)
);
如图所示
此索引对于如下查询类型有效
1.全值匹配
2.匹配最左前缀
3.匹配列前缀
4.匹配范围值
5.精确匹配某一列并范围匹配某一列
6.只访问索引的查询(覆盖查询)
因为索引树中的节点有序,所以可以order by顺序查找
相应的有如下限制
1.必须按照索引最左列开始查找
2.不能跳过索引的列
3.如果某个列是范围查找 右边的列无法使用索引优化查找
B+ Tree是B Tree的变种
和B-Tree基本概念差不多 具体可以看资料
1.非叶子结点的子树指针与关键字个数相同;
2.非叶子结点的子树指针P[i],指向关键字值属于[K[i], K[i+1])的子树
3为所有叶子结点增加一个链指针;
4所有关键字都在叶子结点出现;
B+Tree支持两种搜索
1循叶结点链顺序搜索
2从根结点开始,进行自顶向下,直至叶结点的随机搜索。
Hash索引
(暂略)
索引策略
1.独立的列
where xx= 4 而不是 where xx+1=4 应该把列单独放在符号一侧
2.前缀索引
当查找很长的字符列(hash为佳) 或所以开始的部分字符 (索引的选择性 不重复索引数x/记录总数n)1/n-1 1为最佳
前缀要足够长(保证选择性) 但又不能太长
create table user (user_name varchar(50) not null);
select left(user_name,N) as shu from user 可以通过计算选择性 选择N的值
3.多列索引
....
4.选择合适的索引列顺序
...
5.聚簇索引
聚簇索引不是一种单独的索引类型 而是一种存储方式
最简单的理解就是 聚簇索引的顺序就是数据物理存储的顺序
普通的索引 叶子页是指向数据的位置 而聚簇是数据行存放在所以的叶子页中 (聚簇就是数据行和键值存储在一起)
因为一个表只能以一种顺序存储 所以聚簇索引也只能有一个
因为数据和索引都保存在B-Tree中 所以读取往往比非聚簇要快 (缺点是插入与更新时遇到的问题)
InooDB通过主键聚集数据(不可改变) 如果没有主键 InnoDB会选择一个非空所以代替 如果没有这样的索引 会隐式定义一个主键作为聚簇索引
explain (某某查询语句) 可以得到查询的部分信息
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 30
Extra: Using where; Using index
1 row in set (0.00 sec)
是覆盖查询时,extra会有using index
覆盖查询只需要读取索引 极大减少数据访问量
只需要扫描索引而不需要回表
使用索引扫描来做排序
生成有序结果有两个方法 1通过排序操作 2按索引顺序扫描
只有当索引列的顺序和order by子句属性怒完全一致 并且所有列排序顺序一致(正序或反序)
压缩前缀
...
create table 指定pack_keys参数来控制索引压缩
利用索引排序,order a,b 必须顺序一致 ,如都是asc都是desc
另外对于一些选择性低的索引列 可以添加一个不设限制的默认值
如 key(xx,male,xx) ;
xx=b,male in (男,女) ,xx=c这样 不管性别有没有限制索引都可用