mysql索引类型

　　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　　③排序性能提升　　

　　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;