共计 4491 个字符，预计需要花费 12 分钟才能阅读完成。
1. 什么是索引
索引是一种数据结构，会对增加索引的字段的值进行排序寄存，进步查问效率；一张表中能够增加多个索引；innodb 存储引擎默认应用的是 b +tree 索引构造，也反对哈希、全文索引。
2. 索引的优缺点
2.1 索引的长处
①进步数据库查问效率
②缩小锁期待和死锁的产生（行锁是基于索引创立的）
③缩小主从复制从库的延迟时间（sql thread 回放 sql 时会利用索引）
2.2 索引的毛病
①索引保护老本高（可通过 insert buffer,change buffer 晋升 DML 语句效率）
②占用更多的存储空间（磁盘和内存）
③索引过多会造成优化器累赘
3.b+tree 数据结构
3.1 b+tree 数据结构概括：b+tree 是均衡多叉树的数据结构，是基于页进行治理数据；
3.2b+tree 高度：2（个别为 2 - 4 层）；
影响索引树高度因素：
。索引长度
。记录数
索引树高度不同耗费工夫不同：
如 sata 磁盘（ssd 固态硬盘同样计算）：300iops，0.0033 /io
2 层：0.0033*2 单位是秒
3 层：0.0033*3 单位是秒
3.3 非叶子节点
保留键值(增加索引的字段的值) 和指针
3.4 指针
指针与数据页是一种映射的关系，通过指针就能够找到对应的数据页
3.5 叶子节点
用于保留数据，保留所有记录的值，并通过排序
3.6 双向指针（双向链表）
用于保留相邻页的指针，晋升范畴查问效率
4.b+tree 治理
4.1 b+tree 插入操作：（页旋转操作）
状况一：b+tree 插入数据时，叶子节点没有满
直接插入到对应的数据页
状况二：b+tree 插入数据时，叶子节点已满（产生页决裂 split）
先取出两头值，寄存到上一层非叶子节点中；
状况三：b+tree 插入数据时，叶子节点和上一层的非叶子节点都已满（产生两次页决裂操作）
4.2 b+tree 删除操作
当叶子节点小于填充因子 50%，就会产生页合并操作
5. 相辅相成的汇集索引和辅助索引
5.1 b+tree 索引：索引的实质就是 b +tree 在数据库中的实现
5.2 索引的分类：从物理存储角度分类
汇集索引和辅助索引
5.3 汇集索引的抉择：
①优先选择显示创立的主键索引来作为汇集索引
②如没有主键索引就会抉择第一个创立的非空的惟一索引作为汇集索引
③零碎主动创立一个实例级 rowid 作为汇集索引（6 字节）
5.4 汇集索引的特点：
①叶子节点寄存的是整行数据
②一张表只能有一个汇集索引，因为理论的数据页只能按一颗 b +tree 进行排序
③汇集索引的程序决定表数据行的物理程序
5.5 汇集索引的劣势：
①不必回表查问就能够间接找到数据，节俭更多的查问工夫
②范畴查问性能失去晋升 where 4<o_orderkey<10
③排序性能晋升
5.6 辅助索引：外部也是 b +tree
①辅助索引寄存的是键值和主键值
②每张表中能够寄存多个辅助索引
6. 笼罩索引与回表查问
6.1 回表查问
指回到汇集索引构建的 b +tree 去搜寻的过程，就称为回表；回表查问要多经验几次 io，耗费工夫更多，性能绝对较差
6.2 笼罩索引
在一个查问语句中，应用到的 b +tree 下面的数据就笼罩我要查问需要，称为笼罩索引；能够缩小对 b +tree 的搜寻次数（缩小 io 的耗费，不必回表查问）
7. 创立高性能的主键索引
7.1 主键索引创立的准则
①应用自增列作为主键 id int/bigint auto_increment primary key;
②主键与业务不相干，不受业务变动影响
③主键尽量不要批改、删除
7.2 主键索引的特点
①值不能为空，也不能反复
②一张表只能有一个主键
③创立辅助索引时，会隐式的将主键值保留，（name,pk）5.7 自动识别外面的主键
where name=? and pk=?
where name=? order by pk
7.3 为什么倡议应用自增列作为主键
①读；显示创立的主键会被作为汇集索引，在数据页上存整行数据，无论读记录任何的列，咱们都不必回表查问，间接在主键构建的 b +tree 就能够找到。
②写；写性能十分高，程序获取页；离散获取页；insert buffer，change buffer
③节俭更多的内存
8. 惟一索引与一般索引的性能差距
8.1 惟一索引特点：
- 值不能反复，能够为空
- 一张表能够创立多个惟一索引
- 如果表中已有数据，增加惟一索引时，该字段的值，不能反复，如果有反复的，就会报错
select count(b) from t;
select count(distinct b) from t;
8.2 一般索引特点
- 值能够反复，能够为空
- 一张表能够创立多个一般索引
8.3 惟一索引与一般索引的性能差距
- 读性能差距：
惟一索引：因为唯一性束缚，查找到第一个满足条件的记录后，就会进行持续匹配
一般索引：值不是惟一，可能会有反复值，须要持续查找
总结：在读性能上，惟一索引的性能高于一般索引，性能差距十分小
- 写性能差距：
惟一索引：在进行写操作时要判断这个操作是否违反了唯一性束缚。这个判断必须是在将页加载到内存后，能力进行判断，无奈应用 change buffer
一般索引：在进行写操作时，如果数据页不在内存中，会将写操作放到 change buffer
总结：一般索引的写性能高于惟一索引
9. 前缀索引带来的性能影响
9.1 前缀索引作用：
- 索引长度影响 b +tree 高度，索引长度越短越好
- 节俭磁盘空和内存空间
- 建前缀索引时，如果正当的定义前缀索引的长度，会对查问性能带来好的影响
9.2 前缀索引长度创立不合理会带来哪些不好的影响？
- 不合理的长度，会带来更多回表查问
总结：建前缀索引时，最重要的就是指定正当的长度
9.3 正当长度判断：
select count(distinct a) from t; 去除反复的值，总共有多少条记录
select count(distinct left(a, 3)) from t;
80%-90% 就是正当的
9.4 前缀索引毛病：
- 无奈应用笼罩索引
- 无奈进行 order by 和 group by，会产生额定排序和产生长期表
10. 生产中索引的治理
①建表时创立索引
主键索引
create table t1(id int auto_increment primary key);
create table t2(
id int auto_increment,
primary key(id)
);
惟一索引
create table t1(name varchar(10) not null unique);
create table t2(
name varchar(10) not null,
unique key i_name(name)
);
前缀索引
create table t1(
name varchar(10) not null,
key i_name(name(5))
);
联结索引
create table t2(
name varchar(10) not null,
o_date datetime,
key i_name_date(name,o_date)
);
一般索引
create table t1(
name varchar(10) not null,
key i_name(name)
);
②建表后创立索引
create table t1(
id int ,
name varchar(10) not null,
o_date datetime,
title varchar(30) not null
);
建表后创立索引
主键索引
alter table t1 add primary key(id);
惟一索引
alter table t1 add unique index i_name(name);
前缀索引
alter table t1 add index i_title(title(10));
联结索引
alter table t1 add index i_name_date(name, o_date);
一般索引
alter table t1 add index i_o_date(o_date);
删除索引：
alter table t1 drop index 索引名称
查看索引：
show create table t1;
show index from t1;
11.SQL 语句无奈应用索引的状况
①where 条件：
列进行计算：
explain select * from orders where o_custkey=o_custkey+1;
列应用函数：
explain select * from orders where o_custkey=ceil(o_custkey);
列进行隐式转换：
explain select * from emp where ename=007;
②联结索引：用到范畴查问，只能用到局部索引
③联表查问：
关联条件字符集不同，不走索引
关联条件的列类型不同，不走索引
④其余状况：
。select * from emp;
。查问后果集大于数据量的 30%，不走索引
explain select * from emp where empno > 7000;
。索引自身生效
。like ‘%s’
explain select * from emp where ename like ‘%s’;
。not in(111,9999) 一般索引，如果是主键索引，会被优化为范畴查问，能够利用索引
explain select * from emp where empno not in(111, 9999);
。!=
explain select * from emp where empno != 9999;