Mysql索引基础详解

最近，跟着教程学习了Mysql索引相关的知识，在这里总结一下：

1索引是什么？

本质上来讲，索引是一种数据结构，目的是为了帮助用户更迅速的取得数据；一般默认的索引都是B+树，当然也有其它形式的索引，比如哈希索引等。

2索引的优势？

使用索引可以更迅速的获取数据，举个例子，在一个学生表里，对学生ID建立了索引，在查询ID为111的学生时，大致的结构图如图所示，当然实际的情况往往比这个复杂得多：

3 索引的劣势

索引是一种数据结构，会占据硬盘空间，当数据量非常大时，索引需要较大内存，这是空间上的代价；另一方面，在对数据进行操作之后，还需要更新维护索引，这是时间上的代价。

4 索引的相关命令

4.1创建索引
    -在创建表时就创建（需要注意的几点）
    create table s1(
    id int ,#可以在这加primary key
    #id int index #不可以这样加索引，因为index只是索引，没有约束一说，
    #不能像主键，还有唯一约束一样，在定义字段的时候加索引
    name char(20),
    age int,
    email varchar(30)
    #primary key(id) #也可以在这加
    index(id) #可以这样加
    );
    -在创建表后在创建
    create index name on s1(name); #添加普通索引
    create unique age on s1(age);添加唯一索引
    alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
    create index name on s1(id,name); #添加普通联合索引
4.2.删除索引
    drop index id on s1;
    drop index name on s1; #删除普通索引
    drop index age on s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
    alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)
 

5什么情况该建索引

索引使用的很广泛，在以下情况可以考虑建立索引：

主键自动建立唯一索引：当我们创建主键时，其实已经创建了索引，所以会经常用到主键查询

频繁作为查询条件的应当建立索引：如果某些字段被经常检索，比如买手机时会先选择品牌，那么品牌应当建立索引

查询中与其他表关联的字段，外键关系应当建立索引：比如常见的员工部门关联表

查询中统计或者分组字段：比如经常查找年龄大于35的员工

 

6什么情况避免建立索引

存在一些情况，索引并不能起到很好的作用，这需要大家自己衡量，比如以下情况：

记录非常少的表：当数据量很小时，并不能反映出索引的优势

经常更新的字段：比如每天新闻的热点

数据重复且字段分布均匀的字段：比如性别字段

 

7哪些情况索引会失效

CREATE TABLE `maven`.`t_student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `age` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`));

create index ts_name_age  on t_syudent(name,age);

7.1最佳左前缀法则

如果索引了多列，则查询必须从索引的最左列开始并且不能跳过中间的索引列：

select  *  from  t_student  where name='ff' ;   索引有效

select  * from t_student  where name='ff'  and  age=23;   索引有效

select  *  from  t_student where age=23; 索引失效

 

7.2   like以通配符%开头会导致索引失效从而扫描全表

还是上面的表格，select  age  where  name like  'ff'；索引有效

                                select  age  where  name  like  '%ff'；  索引失效

原因是若以通配符开头，则索引会匹配所有行，从而索引失效。

 

7.3  如果在索引上使用了计算，函数等，会导致索引失效变为全表扫描

这个不需要详细解释，就是查询条件使用了计算，函数，索引会失效。原因很简单，b+树中存的都是数据表中的字段值，
但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。

 

7.4  存储引擎不能使用索引中范围条件右边的列

当查询条件包含索引范围右侧的列时，会导致索引失效

 

7.5  is null，is  not  null无法使用索引

 

以上就是关于索引部分的内容，最后附上一张总结图：