MySQL索引的那些事

421 阅读3分钟

上个月去面试了,铩羽而归。面试官:讲讲索引。我索引可以分为xxx和xxx两类,xxx索引的特点的是xxx,然后就和面试官面面相觑。面试官内心肯定在想:就这水平还想来面试高级岗位。面试失败以后就需要进行复盘一下

写在前面

这篇文章将介绍:

  • MySQL索引分类
  • 索引的创建
  • explain命令

MySQL索引分类

MySQL索引类型从不同的角度可以划分为不同的类

物理存储角度

  • 聚集索引 => 表记录的排列顺序和索引的排列顺序一致,简单 来说就是主键创建创建的索引
  • 非聚集索引 => 表记录的排列顺序和索引的排列顺序不一致,简单的来说就是非主键创建的索引 聚集索引和非聚集索引的区别在于聚集索引在叶子节点存储的是表中的数据,而非聚集索引在叶子节点存储的是主键和索引列

数据结构角度

  • B-Tree索引
  • Hash索引
  • 全文索引

逻辑角度

  • 主键索引 => 不允许重复且不允许为null
  • 普通索引也叫作单列索引
  • 多列索引也叫作复合索引
  • 唯一索引或非唯一索引,唯一索引只需保证不重复即可,允许为null

如何创建索引

创建索引需要从两方面来进行考虑:一是索引命中,二是索引效率,三是索引存储空间

索引命中

创建索引是为了提高查询效率,如果创建了索引而查询的时候未走索引,那等于这个索引白创建了还浪费存储空间得不偿失,而命中索引需要满足以下几个原则:

  • 最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4,如果建立(a,b,c,d)顺序的索引,d是用不到索引的,而如果sql语句是b = 2 and c = 3 and d = 4,那么(a,b,c,d)索引将失效
  • 避免在where语句中引入函数、计算,如sql:select count(*) from t where unix_timestamp(sync_dt) >= 1539101010而 unix_timestamp为mysql内置函数,这样将会导致索引失效。除了使用函数会导致索引失效,普通的计算也有可能会导致索引失效,如sql:select * from t where a = a + 1
  • like中避免以通配符开头,如果建立了索引a,但sql为select * from t where a = '%x',那么索引将失效
  • 避免使用!= 和<>,如果建立了索引a,但sql为select * from t where a != 'x',那么索引将失效

索引效率

尽量选择区分度高的列作为索引,即重复字段值较少,区分度可以使用公式count(distinct col)/count(*)进行计算,表示字段不重复的比例,比例越大扫描的记录数越少,如果区分度过小索引优化器会认为走索引查询的效率和全表扫描的效果差不多而使索引失效

索引存储空间

  • 尽量的扩展索引,不要新建索引。如果表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引修改为(a,b)索引即可,因为(a,b)索引包括了两个索引,a索引和(a,b)索引
  • 主键索引字段尽可能的小,因为在InnoDB存储引擎中非主键索引的叶子节点存储的是主键索引的值,主键长度越小,非主键索引的叶子节点就越小,非主键索引索引占用的存储空间也就越小

explain命令

创建完索引,尽量使用explain去看下查询索引是否命中了,扫描的行数等,未雨绸缪避免在线上出现慢查询