对Mysql中索引的认识

目录

一、认识索引

二、索引的劣优劣

三、什么时候需要使用索引/什么时候不需要

四、索引失效的情况

五、常见的索引种类及创建方法

六、索引的分类

（1）主键索引

（2）唯一索引

（3）普通索引

（4） 全文索引

（5）组合索引

---

有无索引对比

• 无索引

        

• 有索引 

         

一、认识索引

• 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
• 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
• 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

二、索引的劣优劣

优点：

• 建立索引的列可以保证行的唯一性，生成唯一的rowId
• 索引可以提高数据检索的效率，减少I/O次数
• 索引可以加快表与表之间的连接
•  为用来排序和分组的字段建立索引可以加快分组和排序

劣势：

• 索引会占据磁盘空间

• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

三、什么时候需要使用索引/什么时候不需要

必须创建索引的情况：
1．where子句、group子句中出现的字段，要创建索引
2．order by子句的字段，要创建索引
3．统计聚合函数中的字段，要创建索引。比如count(字段) 、max(字段)

不需要创建索引的情况：

1．如果需要取到表中所有记录，则没必要创建索引
2．对非唯一有大量重复值的字段，没必要创建索引，如性别
3．经常进行修改、删除等操作的字段，没必要创建索引
4．记录比较少的表，没必要创建索引

四、索引失效的情况

1.使用<>不等于号
2.单独使用><的时候
3.模糊查询时百分号在前
4.使用符合索引时，第一位置并非索引列
5.字符型字段为数字时在where 条件不加引号
6.当变量采用times变量，而表的字段采用date变量时
7.使用not in， not exists
8.对索引列进行计算时，需要建立函数索引
9.使用or可能会使索引失效，从而全表扫描。

五、常见的索引种类及创建方法

 --如何创建索引
create  index 索引名 on  表名（列名）
create index ind_name on student(name);
 --如何删除索引
drop index ind_name;
--重命名索引：
alter index index_old rename to index_new;--重新命名索引
--查看表的索引：
show index from table_name（表名）
--索引分类：
普通索引：normal
create index 索引名 on 表名（列名）;
create index ind_name on student(name);
--唯一性索引：unique
create unique index 索引名 on 表名（列名）;
create unique index ind_name on school(phone);
--位图（分类）索引：bitmap
--数据量比较大，基数比较小     比如：男/女
create bitmap index 索引名 on 表名（列名）;
create bitmap index ind_sid on student(sid);
--函数索引：
create index ind_email on student(length(email));

六、索引的分类

• 主键索引
• 唯一索引
• 普通索引
• 全文索引
• 组合索引

（1）主键索引

即主索引，根据主键创建的索引，不允许重复，不允许为空值。
如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替。
如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。

 创建表时候，直接创建主键索引

 

 --修改和添加主键

ALTER TABLE test_2 modify uid int PRIMARY key AUTO_INCREMENT

--删除主键索引 注意需要先取消 自增，在删除主键

--先取消自增，修改字段

ALTER TABLE test_2 modify uid int 

--删除主键

ALTER TABLE test_2 drop  PRIMARY key 

（2）唯一索引

用来建立索引的列的值必须是唯一的，可以为空。

 --添加唯一索引  UNIQUE 当前列要求唯一，但要求唯一，但允许为空

ALTER TABLE test_2 add unique u_name(name)

--删除唯一索引 根据当前索引名去进行删除

ALTER TABLE test_2 drop  index u_name

（3）普通索引

创建表时，直接创建普通索引

 -- 添加索引

ALTER TABLE test_2 add in_name(email)

-- 删除索引

drop insex in_name on test_2

（4） 全文索引

ALTER TABLE 'table_name' add FULLTEXT INDEX ft_index('col')

（5）组合索引

-- 添加索引
ALTER table test_2 add index in_x(email,phone,uname);
-- 删除索引
ALTER table test_2 drop index in_x;