0%

MySQL索引

MySQL索引

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

MySQL默认使用的是B树索引

索引分类

  • 单值索引 一个索引只包含单个列
  • 唯一索引 索引列的值必须唯一,可以为空值
  • 复合索引 一个索引包含多个列

索引语法

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

索引结构

MySQL有四种结构的索引,分别为BTREE索引、hash索引、full-text全文索引、R-Tree索引

BTREE索引

索引的条件

什么时候适合创建索引

  • 主键自动创建主键索引
  • 频繁作为查询条件的字段适合创建索引
  • 外键关系创建索引
  • 用来排序的字段创建索引可以提高排序速度
  • 查询中统计或分组的字段可以用索引来提升速度

什么时候不适合创建索引

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

索引失效

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

    复合索引

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

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

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

    范围查找

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

  • 尽量使用覆盖索引

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

    不等于

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

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

    like查询

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

    like右模糊

    此时age索引列也生效了

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

    类型转化

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

    or连接

优缺点

优点

  • 提高了检索效率,降低了数据库的IO成本
  • 降低了数据排序的成本,降低了CPU消耗

缺点

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