09 数据库——索引
一、索引概念
1.什么是索引：
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件（比如书没目录就只能随机翻），也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据（永远就是按照索引去找，不用正着翻反着翻了）。
补充：innodb引擎只有表结构和数据两个文件夹，不像myisam三个文件有单独的索引文件，表结构中不可能放别的，所以索引是和数据放在一个文件中的
2.三种索引（键）：
-
primary key
-
unique key
-
index key
注意foreign key不是用来加速查询用的，不在我们研究范围之内,
上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询
-
在表中有大量数据的前提下，创建索引速度会很慢（例如一本书特别厚，你需要一页一页去查看有什么内容一页一页建目录，所以如果数据量特别大时你命令敲完之后，终端会卡很长一段时间去疯狂的建索引）
-
在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低（假如一个表数据量很大，好不容易建好了索引，你又想去插入一条数据，此时索引并不能也插一下就完事，而是从头再来，那就有可能会卡好长一段时间，并不是你插入数据慢而是插入后去建索引了）
二、B+树 索引结构
浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
###b+树的查找过程
如图所示，如果要查找数据项29，
第一步：首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计
第二步：通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针
第三步：通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
知识点：
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据，如17和35中是没有真实数据的
查询次数由树的层级决定，层级越低次数越少
为什么索引速度快呢：每次循环减半，时间复杂度为O(log n) 仅仅低于O(1)
### 怎样让树的层级尽可能低？（为什么推荐使用int类型的id作为主键？）
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿能存放的数据量越多，树的层级才会越少，
我们应该给我们一张表里面的什么字段建立索引才能够尽可能降低树的层级高度呢？>>> 主键id字段（整形最省内存）
比如一个磁盘块可以存10M,一个字符串占1M一个整形占用1K，如果我们采用字符串当主键也就是索引，就白白浪费1M空间，所以我们每个表中都有一个整形的id，并且我们默认用id来当主键，节省更多空间，磁盘块就可以存储更多数据，树的层级就会尽可能的少
三、
数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index），
聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。
聚集索引与辅助索引不同的是：叶子结点存放的是否是一整行的信息
1.聚集索引
聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。
聚集索引的好处之一：它对主键的排序查找和范围查找速度非常快，叶子节点的数据就是用户所要查询的数据。
聚集索引的好处之二：范围查询（range query），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可
特点:叶子结点放的一条条完整的记录
2、
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值，还需要通过主键再去查找真实数据(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
select name from user where name='jason';
覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据（找的就是name索引还是name，不用往下找了）
select age from user where name='jason';（找的是age，索引是name，往叶子节点找，找到主键，再利用主键找到真实数据）
非覆盖索引：虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，但只能有一个聚集索引。
当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶子级别的指针获得主键索引的主键，然后再通过主键索引来找到一个完整的行记录。
举例来说，如果在一棵高度为3的辅助索引树种查找数据，那需要对这个辅助索引树遍历3次找到指定主键，如果聚集索引树的高度同样为3，那么还需要对聚集索引树进行3次查找，最终找到一个完整的行数据所在的页，因此一共需要6次逻辑IO访问才能得到最终的一个数据页。
四、测试
#1. 准备表 create table s1( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明 分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1\G #4. 调用存储过程 call auto_insert1();
# 表没有任何索引的情况下 select * from s1 where id=30000; # 避免打印带来的时间损耗 select count(id) from s1 where id = 30000; select count(id) from s1 where id = 1; # 给id做一个主键 alter table s1 add primary key(id); # 速度很慢 select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级 select count(id) from s1 where name = 'jason' # 速度仍然很慢 """ 范围问题 """ # 并不是加了索引，以后查询的时候按照这个字段速度就一定快 select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多 select count(id) from s1 where id >1 and id < 3; select count(id) from s1 where id > 1 and id < 10000; select count(id) from s1 where id != 3; alter table s1 drop primary key; # 删除主键 单独再来研究name字段 select count(id) from s1 where name = 'jason'; # 又慢了 create index idx_name on s1(name); # 给s1表的name字段创建索引 select count(id) from s1 where name = 'jason' # 仍然很慢！！！ """ 再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分 那这个树其实就建成了“一根棍子” """ select count(id) from s1 where name = 'xxx'; # 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了 select count(id) from s1 where name like 'xxx'; select count(id) from s1 where name like 'xxx%'; select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性 # 区分度低的字段不能建索引 drop index idx_name on s1; # 给id字段建普通的索引 create index idx_id on s1(id); select count(id) from s1 where id = 3; # 快了 select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算 drop index idx_id on s1; select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件 create index idx_name on s1(name); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速 drop index idx_name on s1; # 给name，gender这种区分度不高的字段加上索引并不难加快查询速度 create index idx_id on s1(id); select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段 drop index idx_id on s1 create index idx_email on s1(email); select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 如果上述四个字段区分度都很高，那给谁建都能加速查询 # 给email加然而不用email字段 select count(id) from s1 where name='jason' and gender = 'male' and id > 3; # 给name加然而不用name字段 select count(id) from s1 where gender = 'male' and id > 3; # 给gender加然而不用gender字段 select count(id) from s1 where id > 3; # 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间 create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放 select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
总结:上面这些操作大概需要十几个小时运行（不要笑，也就三百万条），你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了
