数据库--从数据库层面看索引

索引

​ 索引用于提高查询效率，实现方式有hash表、红黑树、B+树、跳表等，一张表可以根据查询需要建立多种索引

​ 关于这四种数据结构底层实现，参考之前写的数据结构专栏(跳表、hash，轻松玩转红黑树，B+树看它就够了，实战应用等四篇文章)

​ 关系型数据库中表根据主键顺序以索引形式存放，InnoDB采用B+树索引模型，所以数据都存放在B+树中(对于B+树叉数，在mysql5.6后可通过page大小来间接控制，因为叶子节点是page)

​ Ps：节点page内部为有序数组，通过二分法定位到行数据

主键索引和非主键索引

​ 按主键构建的索引叫主键索引，叶子节点存记录

​ 按非主键构建的索引叫非主键索引(也叫二级索引)，叶子节点只存主键

​ 相比主键索引，非主键索引查询需要多扫描一次索引树(先搜索非主键索引得到主键，再搜索主键索引得到数据，这个过程称为回表)，因此我们尽量使用主键查询。对于没有主键索引的表，innodb会默认创建一个Rowid唯一标识作为主键索引

​ 比如学生成绩数据(学生id(主键)、成绩、姓名)构建的主键索引和按成绩排序的普通索引：

create table T(id int primary key, 
               grade int NOT NULL DEFAULT 0, 
               name varchar(16),
               index (k)) engine=InnoDB;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CiTMx2BS-1584277393690)(http://qiniuyun.ivan-zcy.top/FqWaq1Rx96LMWvJKfprVZIkyXvd9)]

自增索引

​ 系统会获取当前最大值+1作为下条记录的id值，也就是说每次插入都是追加操作，不涉及挪动(即B+节点的分裂和合并操作，不了解看数据结构专栏)，因此性能非常高，除此之外由于非叶子节点只存储自增id(int型4字节，当然数据量大可采用bigint unsigned)，因此内存占用少

​ 自增主键可在建表语句中加NOT NULL PRIMARY KEY AUTO_INCREMENT定义

覆盖索引

​ 覆盖索引是指非主键索引中已包含所要查询的内容，进而减少树的搜索次数来提升性能

​ 比如上面例子查询99~100分所有学生信息select * from T where grade between 95 and 100需执行5步：

1. 在非主键索引取下一个值grade=99，取得id=9
2. 在主键索引找到id=9，取得Joy的信息
3. 在非主键索引取下一个值grade=100，取得id=3
4. 在主键索引找到id=3，取得Elvin的信息
5. 在非主键索引取下一个值，取不到结束

而查询99~100分所有学生ID select id from T where grade between 95 and 100只需3步：

1. 在非主键索引取下一个值grade=99，取得id=9
2. 在非主键索引取下一个值grade=100，取得id=3
3. 在非主键索引取下一个值，取不到结束

联合索引

​ 为避免每种查询都需设计一个索引，mysql以一定顺序引用多个字段的索引。它减少了磁盘开销(本来需要建a，ab，abc三个索引，可通过(a,b,c)一个联合索引替代)，提高了效率(对select * from T where a = 1 and b = 2，若采用单值索引需先筛出a=1的数据，再回表找b=2的数据；而联合索引可直接筛出a=1且b=2的数据)

​ 字段相同但顺序不同是两种联合索引，例如(a,b,c)和(b,c,a)，主键索引也可以为联合索引

​ 它采用最左匹配原则，即对于(a,b,c)，能匹配a，ab，abc三种索引，因此要把最常用字段放最左侧

​ Ps：联合索引的实质就是覆盖索引，比如上述根据学生成绩查询姓名，可通过建立联合索引(grade,name)，直接查询联合索引而无需回表

索引重建

​ 索引可能在使用中因为删除、页分裂等原因导致数据页有空洞，重建索引可使页面利用率最高，从而节省内存

​ 比如要重建非主键索引grade：

alter table T drop index k;
alter table T add index(k);

​ 重建主键索引：

 alter table T engine=InnoDB;

​ 重建主键索引不采用alter table T drop primary key; alter table T add primary key(id);，是因为无论删除还是创建主键都会将整个表重建(也就是说导致其它索引失效)

避免索引重复

​ 索引重复会造成磁盘空间占用，应仔细检查筛除

例如：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

​ 在上表中索引c和索引ca实质上相同，可取消一个。因为由于ab为主键，所以c索引实质存储cab(ab主键为内容)，ca所以实质存储cab(b主键为内容)

减少搜索次数

​ 比如对于以下两种语句：

select * from T where k in(1,2,3,4,5)
select * from T where k between 1 and 5	

​ 应当采用between…and，因为第一个需要搜索树5次，而第二个只需搜索一次(搜索一次后通过双向链表向后查找直到不符合，如果不懂去看数据结构的B+树底层实现)