学MySQL的第一座大山---索引

1,158 阅读4分钟

「本文已参与好文召集令活动,点击查看:后端、大前端双赛道投稿,2万元奖池等你挑战!

mysql入门第一课,绝对是索引。工作用的最多的,还是索引。DBA看到慢日志,甩过来让你优化,第一想到的,还是索引。但是,很多人学了很多遍,还是没有真正弄明白。

首先,你要知道索引是怎么实现的。

MySQL为什么要用B+树?

如果看过这篇文章,大概知道Innodb用的是B+树,天然有序,我们查询的时候只需要二分查找,找到叶子节点即可。有了这一步,后面就好理解了。

索引常识

索引分类

按存储结构:

  • btree索引:多路平衡查询树,天然有序,适合范围查询。
  • hash索引:没有顺序关系,适合等值查询,范围查询需要扫描全表。
  • full-index:全文索引。

按应用层:

  • 普通索引:单列索引。
  • 唯一索引:索引列的值必须唯一,但允许有空值。
  • 复合索引:多列索引。

聚簇索引和非聚簇索引

image.png

很多人被这两个字整蒙了,其实很简单,就是一个是主键索引,一个是非主键索引。

前面叶子节点存的是完整数据,后面叶子节点只是存的主键值。

如果语句是select *from T where ID=500,即主键查询方式,则只需要搜索ID这棵B+树;

如果语句是select *from T where k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID 的值为500,再到ID索引树搜索一次。这个过程称为回表

所以:

  • 尽量用自增Int作为主键:追加添加很方便,而如果中途插入,需要所有后移,还可能导致页分配不均,页分裂的问题。
  • 能用主键查询就用主键:减少回表

怎么减少回表?

覆盖索引,即索引就能获取我们需要的值。

select *from T where k between 3 and 5 这个需要回表

select ID from T where k between 3 and 5 因为存的就是ID,所以不用回表。

联合索引

  • 将索引复用率最高的放左边。
  • 将能筛除更多数据的放左边。
  • 将空间小的放左边。 (其实就是从左往右筛选数据的,越早过滤无用数据越好)

索引下推优化(index condition pushdown)

可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

即用索引A筛数据时,直接同时用索引B判断。

比如查询姓张,年龄小于10的。

  • 原来是查出所有姓张的,然后一一回表,判断年龄。
  • 现在是直接判断跳过,会少很多回表次数。

字符串索引

  1. 直接创建完整索引,这样可能比较占用空间;
  2. 创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
  3. 倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
  4. 创建hash字段索引,查询性能稳定,有额外的存储和计算消耗,跟第三种方式一样,都不支持范围扫描。

什么时候索引会失效?

我们知道索引存的都是具体的值,所以匹配的时候会直接按值匹配。 那么,如果加入了运算,则会导致索引失效。

比如:

  • Where后用函数处理了索引,比如日期、数学运算。
  • 索引是字符串类型,where写的整型。(带上了类型转换函数)
  • 遇到null,是大是小不知道了

还有一些可能失效的(请自己看一下执行计划)

  • 不等于
  • like
  • 先范围再等值匹配

怎么优化索引

  • 联合索引:因为索引就是自然有序的,所以如果经常用这几个键排序查询,最好建立联合索引。
  • 尽量用唯一自增做主键。
  • 需要的话,建立覆盖索引。

三星系统:

  • 索引将相关记录放到一起为一星。
  • 索引中的数据顺序和查找的排列顺序一致则两星。(有针对性的联合索引)
  • 索引中的列包含了查询中需要的全部列则三星。(覆盖索引)

结语

如果有不对的地方欢迎指正。

如果有不理解的地方欢迎指出我来加栗子。

如果感觉OK可以点赞让更多人看到它。

相关阅读: