0%

MySQL索引

MySQL索引

索引就是用来帮助mysql快速查找数据的数据结构,这种数据结构以某种方式指向数据,以此种数据结构的基础上实现高级的查找算法,就可以快速的查找数据

MySQL默认使用的是B-Tree索引(结构为B+树)

索引分类

  • 普通索引:最基本的索引,没有任何约束
  • 唯一索引:与普通索引类似,但具有唯一性约束,允许有空值,但是空值只能有一个
  • 主键索引:特殊的唯一索引,不允许有空值
  • 复合索引:将多个列组合在一起创建索引,可以覆盖多个列
  • 外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作
  • 全文索引:MySQL 自带的全文索引只能用于 InnoDB(5.6之后支持,5.7之后使用ngram插件开始支持中文)、MyISAM ,一般使用全文索引引擎(ES,Solr)

索引语法

1
2
3
4
5
6
7
-- 创建索引
create [unique] index 索引名 on 表名(列名);
alter 表名 add [unique] index 索引名 on 列名;
-- 删除索引
drop index 索引名 on 表名;
-- 查看索引
show index from 表名;

下列索引的讲述是按照Innodb中的B-Tree索引来讲述的,因为该索引使用的最多

Cardinality

在使用show index from 表名;查询索引时有一个Cardinality列,表示索引中不重复记录数量的预估值,该值可以用来判断是否有必要创建这个索引,Cardinality/表的行数应该尽可能的接近1,如果值太小,说明有太多的重复值,不太适合作为索引

索引的条件

什么时候适合创建索引

  • 主键自动创建主键索引
  • 频繁作为查询条件的字段适合创建索引
  • 外键关系创建索引
  • 用来排序的字段创建索引可以提高排序速度,否则会使用文件内部排序算法进行排序,using filesort
  • 查询中统计或分组的字段可以用索引来提升速度

什么时候不适合创建索引

  • 频繁更新的字段不适合创建索引
  • 不会用来做查询条件的字段不适合创建索引
  • 表记录少不需要创建索引
  • 字段值为常数且大多重复平均分配的列不需要创建索引

索引失效

  • 建立复合索引,在查询时要复合最佳前缀法则,即按照建立的复合索引顺序进行查询,否则会造成索引失效

    复合索引

    在使用该索引查询时,要按照顺序,如果查询条件中没有使用class_id则该索引不会生效,而且在使用class_id时最好不好跳过name直接使用age条件

  • 在索引列上进行计算、函数、自动或手动类型抓换也会造成索引失效

  • 使用范围查询匹配右边列会导致索引失效

    范围查找

    class_id使用大于进行范围匹配,使得class_id的索引变成了排序而非检索,使得name的索引失效

  • 尽量使用覆盖索引

  • 使用!=或者<>会导致索引失效,全表扫描

    不等于

  • 使用is null 或者is not null会导致索引失效

  • 使用not in 或者 not exists会导致索引失效

    notin

  • like以%前缀开头会导致索引失效(如果一定要用%%来进行匹配,可以使用覆盖索引)

    like查询

    注意:如果%在右边,该复合索引中使用like的字段后的索引列也可以生效

    like右模糊

    此时age索引列也生效了

  • 字符串不加单引号索引会失效,

    类型转化

  • 使用or连接条件会导致索引失效

    or连接

5.6之后为了减少回表次数,mysql引入了索引下推,即对索引中包含的字段先进行判断,过滤掉不符合条件的记录,减少回表次数

口诀总结

全值匹配我最爱,最左前缀要遵守
带头大哥不能死,中间兄弟不能断
索引列上不计算,范围之后全失效
LIKE符号写最右,覆盖索引不写*
不等空值还有or,索引失效要少用
字符单引不能丢,SQL高级也不难
分组之前必排序,一定要上索引啊

查看索引

可以使用information_schema.STATISTICS来查看索引

优缺点

优点

  • 减少了服务器需要扫描的数据量
  • 提高了检索效率,降低了数据库的IO成本,将随机IO变为了顺序IO
  • 降低了数据排序的成本,降低了CPU消耗

缺点

  • 索引保存了主键和索引字段,并指向实体表记录,所以索引列是占用磁盘空间的
  • 索引虽然提高了查询速度,但是会降低更新表的速度,在进行增删改的时候,不仅要对数据进行修改,还是对索引文件进行修改