Mysql -- 索引简介篇

快来打我* 2023-07-14 05:54 20阅读 0赞

## Mysql – 简介索引篇 ##

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

## 前言 ##

起源，个人用sql很久了，由于是后端开发，基本跟增删改查打交道。但是我觉得有些东西用久了，就有点忘了起源。所以我单独建立一个模块，准备把mysql小软件做个标签。

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

**1. 什么是索引?**

项目里面，基本听到添加索引，甚至于建立一个字典表，字典表里面存放的都是索引。索引的作用是mysql最低限度的快速找到对应字段，并且加了索引的字段，可以很大提升查询效率，那么索引是什么呢，是一种数据结构。

**2. 索引是个什么样的数据结构呢?**

说了索引的作用跟是什么，那么接下来追溯下去，索引既然是数据结构，哪属于什么数据结构，索引的数据结构是跟具体的存储引擎有关的。比较多的索引为hash结构的索引，B+树索引等，我们常用的mysql InnoDB存储引擎使用的便是B+树索引。

**3. Hash索引和B+树所有有什么区别或者说优劣呢?**

既然知道索引是一种数据结构，方便查询，那么更具存储引擎不同，索引数据结构不同，那么这两种常见索引又有什么区别呢。hash索引不用说大家也知道的，hash类型的底层肯定是hash表。所以hash索引肯定是通过hash函数通过hash表返回回来键值然后再查询数据出来。至于B+树，既然是树类型的，肯定是多节点的，从树根节点去向上查询，查询子分支树获得键值然后再判断是否要查询数据出来

说了这么多，对比一下，有什么区别

第一，一个是通过hash函数查询hash表 查询数据，一个是通过平衡树查询节点，查询数据。

第二，由于hash函数查询是无序的，所以不可能覆盖查询，而树状节点，天然支持覆盖查询。

第三，hash数据查询中如果键值一对，容易出现hash碰撞，相对不稳定，而平衡树，从根部查询到节点，相对稳定。

第四，平衡树，可以存储聚族索引，而hash不行。

**4. 什么是聚簇索引**

聚族索引是以索引为主键作为聚族索引，聚族索引比如存储引擎B+树里面存储的聚族索引。那么查询到这个主键索引键值，就不需要回标通过索引查询数据，而是直接拿这个数据，这个查询数据在聚族索引内。避免重复问题

举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询. 如果命中非主键的索引如果全部命中也不需要回表查询。 如果是select \* from employee where age < 20，那么必然是回表查询。

**5. 建立索引的时候,都有哪些需要考虑的因素呢?**

说了这么多索引的问题，总结一下，索引是为了快速找到数据，优化查询速度的，索引是数据结构，什么数据结构，取决于存储引擎，常见的是B+平衡树，和Hash索引。区别简单了解几点就可以了。InnoDB使用的便是平衡树，什么是聚族索引。 那么我们创建这个索引又需要考虑什么呢。

首先既然是为了优化查询的东西，必然是对某个字段查询比较频繁，比如现在有学校表。学校表id必然对应教师，学生，食堂，书馆，等等关联。在查询这个学校的信息比如按是where 学校等于学校表的某个id，那么这个频繁使用查询的必然要在id字段加上索引。我们正常开发是以判断条件比较常用的作为索引。如果需要建立联合索引的话,还需要考虑联合索引中的顺序。

**7. 联合索引是什么?为什么需要注意联合索引中的顺序?**

在mysql中，MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.

MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引

当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推.

简单一点就是犹如三个高能机器，第一个执行给第二个第二个执行给第三个。

因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.

select name,age,school from t\_school\_list

**8. 那么如果创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?**

这个时候就要用到mysql提供的特殊字段explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息.

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70]

这个表里面没有加索引，所以可以看到type 查询全部，key无，key长度无，rows查询7行。

![在这里插入图片描述][20200313183637756.png]  
添加一个普通索引  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70 2]

查询表示 使用了索引 index\_summ

总结可以通过explain 来查看sql执行计划，进行优化。

**9. 那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?**

这个问题面试官常考这个，其实很简单，

第一，使用不等于这个字段自然不会有这个索引。  
第二，使用like模糊查询不会使用索引  
第三，使用函数不会使用索引。  
第四，使用联合索引，没命中所有索引字段自然不会用这个索引。

稍微想一下便可以记住。

**10，简单说一下索引创建。**

中文是，操作数据库 库名称 增加索引 索引名 索引（）

alter table xxx add index index\_index\_name (ziduan)

其中xxx表示库名

index\_index\_name（）表示索引名括号里面是字段名。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70]: /images/20230529/727148924fda4ac6a437498c7ecd7adc.png
[20200313183637756.png]: /images/20230529/c68d1bd1ba2749f58b2ae32cb4d4e40a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70 1]: /images/20230529/ba29868ff03d43dcae4238e0346861a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80NDU1MDQ5MA_size_16_color_FFFFFF_t_70 2]: /images/20230529/f27dc186a43547298cfb10a2f19d4ba5.png