这一章节我们来聊一聊面试高频考点——索引这也是我们实际开发中经常用到的。索引是个什么东西我们小时候都用过字典吧当我们要查一个字的时候我们选择怎么做比如“昕”这个字假设在231页我们难道要从第一页翻起一页一页查找这个字直到231页我们找到吗这样太慢了所以关于字典的第一节课老师会教我们查字典。怎么查目录我们可以用部首查字法“昕”是日字旁先找日然后就知道“昕”在第几页了。那么索引对于数据库也是起到这个作用。其实索引我们已经见过很多次了我们写过很多次建表语句在 id 的后面我们通常会加一个 primary key 表示主键主键是一个约束innoDB 会基于主键自动创建索引。索引能够极大加快我们查询的速度靠的什么数据结构 二叉树吗用二叉树存索引数据一多树就会变得非常高查询数据库可是从磁盘里读文件 IO 次数太多哈希表吗我们写 sql 经常会写范围查询而哈希表只能命中 key 才行不支持范围查询。我来告诉你用的是 B树。在讲 B树前我先看一下 B树。B树是一颗 N叉搜索树长这样 id 并不连续仅举例 可以看到最上面的节点里有2个值然后分出3棵子树然后第二层每个节点也有两个值再分出3棵子树然后就是叶子节点。我们再和 B 树比较一下可以看到和 B树一样B树的一个节点里有 k 个值也会分出 k 1 棵子树但最直观的是在 B树中父节点的值会再次出现在子节点里这就意味着在最后的叶子节点中会出现全集然后用双向链表将叶子节点串起来这也就是为什么 B树更适合范围查询和排序。下面我们来说一下存储的内容有什么不同B树的每一个节点会存列值数据的指针以及子节点的指针。而 B树的内部节点只做路由内部节点也叫做索引页只存列值以及子节点的指针不存放数据的指针所以相同的磁盘页B树的内部节点可以放更多的列值会比 B树更矮文件 IO 次数更少。那数据放在哪放在叶子节点叶子节点也叫做数据页。那么就意味着每次查询都会查到叶子节点来获取数据相比于 B树可以在内部节点就获得数据来说虽然稍慢但是很稳定而且内部节点占用空间小还可以暂时存进内存进一步加快速度。说到叶子节点存储的数据其实不同的索引存放的数据也不同。索引分两类聚簇索引和二级索引。innoDB 会根据主键创建聚簇索引主键非空且唯一。但是如果没有主键那innoDB 就会选择一个非空且唯一的字段来创建聚簇索引。如果都没有innoDB 会隐式生成一个6字节的 row_id 来创建聚簇索引。根据主键创建出来的 B树叶子节点存放的是几个 id 以及 id 对应的一整行数据。二级索引就是非聚簇索引比如唯一索引就是加 unique 关键字的还有普通索引等等这些索引在叶子节点并不会存对应的一整行数据而是会存列值以及对应行的主键值innoDB 拿到这个主键后去聚簇索引的 B树中再查一遍将完整的一行查出来再查一遍的这个动作叫做回表。我们举一个例子来加强一下理解select * from user where name 张三如果 name 列没有索引那么就会遍历表一条一条看 name 是否匹配就会很慢。如果给 name 加上索引那么在 mysql 里就会以这个列创建一棵 B树查询的时候就会去查这棵树。因为这个索引是二级索引索引这棵树的叶子节点存储的是 张三以及这条数据的主键 id 比如是35然后拿着35再去主键的B树查一遍聚簇索引的叶子节点存储的是35和这一整行数据例如张三的年龄电话等等。这里查的是 * 所以 innoDB 要去回表那要是 select id 或者 select name这两个字段在查二级索引时就可以得到不用回表了这个叫做覆盖索引。创建索引的 sql 语句就不过多赘述了再讲几个注意点吧。我们知道索引其实是 innoDB 创建的 B树创建一个索引后在增删改查的时候就要维护这个索引我们不能在表中有很多数据的时候给某一个列创建一个索引创建这棵大树会消耗大量的时间和资源。我们可以新建一个有这个字段索引的数据库然后将数据拷贝过来。还有就是索引遇到几个情况会失效我们一一列举一下1.在模糊查询中like 孙%可以命中索引但是 like %孙索引就会失效只可以按字符串前缀进行匹配。2. 查询条件中如果对字段进行表达式计算或者函数计算也会失效。3.查询条件中用 or 连接时有一边的字段没有加索引那么另一边的索引会失效 and 不会。4.对于字段性别来说只有男和女即使创建了索引优化器也会认为全表扫描更快从而不走索引。我们刚刚只是说对于一个列加索引然后查询条件只有这一个字段不过对于这条 select * from user where height 175 and status 在职员工如果在开发中经常会将 height 和 status 一起进行查询那我们就可以建立一个联合索引也叫复合索引。在联合索引的 B树中节点中存的列值是由这些列组成的组合键例如height , status innoDB 创建树排序的时候会按照定义的顺序逐列比较例如先比较 height 进行排序然后相同的 height 再根据 status 进行排序。既然有先后顺序那么对于查询条件的先后也有说法叫做最左前缀匹配。举一个简单的例子对 a,b,c 建立联合索引那么对于查询条件 where a 1 and b 2 and c 3 索引就会生效但是对于 where b 2 and c 3 来说就会失效因为没有 a 不能对 a 进行比较就无从谈起之后的 b,c 了对于 where a 1 and c 3 来说只能找到 a 1的区间 因为没有 b 没有比较 b 就不能去比较 c 只能在此区间内依次遍历 c 了。只是以毫米向前感谢阅读~