对Mysql中索引的认识

绝地灬酷狼 2023-10-06 22:42 61阅读 0赞

**目录**

一、认识索引

二、索引的劣优劣

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

四、索引失效的情况

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

六、索引的分类

（1）主键索引

（2）唯一索引

（3）普通索引

（4） 全文索引

（5）组合索引

--------------------

#### 有无索引对比 ####

*  无索引

![90eb4139957d4e3c8a302a0748b9ccb2.png][]

*  有索引

![2b020a17e1a2424d87382e3151cf2f3c.png][]

### 一、认识索引 ###

*  官方介绍索引是帮助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会隐式的定义一个主键来作为聚簇索引。

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

![abb66e83e7224a668ae84615c44b0f38.png][]

\--修改和添加主键

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）唯一索引** ####

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

![517735f349144bf7a8089187955c4e1e.png][]

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

ALTER TABLE test_2 add unique u_name(name)

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

ALTER TABLE test_2 drop  index u_name

#### （3）普通索引 ####

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

![a00409078e7548b780d4005862a08225.png][]

\-- 添加索引

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;

[90eb4139957d4e3c8a302a0748b9ccb2.png]: https://img-blog.csdnimg.cn/90eb4139957d4e3c8a302a0748b9ccb2.png
[2b020a17e1a2424d87382e3151cf2f3c.png]: https://img-blog.csdnimg.cn/2b020a17e1a2424d87382e3151cf2f3c.png
[abb66e83e7224a668ae84615c44b0f38.png]: https://img-blog.csdnimg.cn/abb66e83e7224a668ae84615c44b0f38.png
[517735f349144bf7a8089187955c4e1e.png]: https://img-blog.csdnimg.cn/517735f349144bf7a8089187955c4e1e.png
[a00409078e7548b780d4005862a08225.png]: https://img-blog.csdnimg.cn/a00409078e7548b780d4005862a08225.png