445-MySQL(解析索引,索引的操作)

绝地灬酷狼 2022-09-06 01:28 212阅读 0赞

## MySQL索引 ##

**只要是关系型数据库，索引都是其查询最核心的关键**

当表中的数据量到达几十万甚至上百万的时候，SQL查询所花费的时间会很长，导致业务超时出错，此时就需要用索引来加速SQL查询。  
**由于索引也是需要存储成索引文件的**，**因此对索引的使用也会涉及磁盘I/O操作**。如果索引创建过多，使用不当，会造成SQL查询时，**进行大量无用的磁盘I/O操作，降低了SQL的查询效率**，适得其反，因此掌握良好的索引创建原则非常重要！  
索引也是数据结构，要对索引列，存储引擎会对这列的值进行排序，当我们索引太多的话，我们对表增加或者减少或者更新元素的时候，都会涉及到索引文件的改动。索引文件改动也涉及到磁盘I/O操作。

## 索引分类 ##

**索引**是创建在表上的，是对数据库表中一列或者多列的值进行排序的一种结果。**索引的核心是提高查询的速度！**

**不用的时候，存储在磁盘上的。索引是要从磁盘上的文件读出来的。**

**物理上**分为(聚集索引&非聚集索引)  
**逻辑上**(就是下面阐述的)

**索引的优点**： 提高查询效率  
**索引的缺点**： 索引并非越多越好，过多的索引会导致CPU使用率居高不下，由于数据的改变，会造成索引文件的改动，过多的磁盘I/O造成CPU负荷太重

**1、普通索引（二级索引）**：没有任何限制条件，可以给任何类型的字段创建普通索引(创建新表&已创建表都可以创建索引，索引的数量是不限的，但是一张表的一次sql查询只能用一个索引 where a=1 and b=‘M’)  
看where后面的字段。  
**2、唯一性索引**：使用UNIQUE修饰的字段，值不能够重复，主键索引就隶属于唯一性索引  
**3、主键索引**：使用Primary Key修饰的字段会自动创建索引(MyISAM（不会自动添加主键）, InnoDB（没有加主键，会默认增加1个整型字段的主键)）  
**4、单列索引**：在一个字段上创建索引  
**5、多列索引**：在表的多个字段上创建索引 (**uid+cid，多列索引必须使用到第一个列，才能用到多列索引，否则索引用不上**)  
**6、全文索引**：**MyISAM支持**。使用FULLTEXT参数可以设置全文索引，只支持CHAR，VARCHAR和TEXT类型的字段上，常用于数据量较大的字符串类型上，**可以提高查询速度**(线上项目支持专门的搜索功能，给后台服务器增加专门的搜索引擎支持快速高校的搜索 elasticsearch **简称es** 也是C/S模型，有server服务，提供http接口，供外部业务查询搜索的内容放入es进行序列化。waiwC++开源的搜索引擎 搜狗的workflow)， **用于字符串的搜索。**

## 索引创建和删除 ##

**创建表的时候指定索引字段：**

CREATE TABLE index1(id INT, 
    name VARCHAR(20), 
    sex ENUM('male', 'female'), 
    INDEX(id,name));

因为id没有被修饰成索引，是单列索引，普通索引，二级索引。建立索引，对id进行排序。  
还可以这样：  
![在这里插入图片描述][bea59db4dca9456386c3be00855ab55f.png]

**在已经创建的表上添加索引：**

CREATE [UNIQUE] INDEX 索引名 ON 表名（属性名（length） [ASC | DESC]);

**删除索引:**

DROP INDEX 索引名 ON 表名;

**我们看看数据库**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]  
**使用到了主键索引。扫描了1行。从索引树上直接找到了数据，是常量时间，不管顺序，都是直接命中索引树。**

**我们用name去过滤看看。name没有索引。不管顺序，都是整张表扫描。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]  
**我们给name创建索引**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]  
**到时候以name过滤，就是用name索引加速搜索**  
但是，不一定是添加索引了，到时候作为过滤条件就一定能使用到索引，因为MySQL server有优化，它发现使用索引最后得到的数据基本上也是所有数据的大概百分之七八十左右，其实是不会使用索引的，因为如果花费差不多，读索引文件花费磁盘I/O，还要扫描索引树，数据取不完，还要整个数据表上，还不如直接整张表搜索。  
![在这里插入图片描述][794cac046fbf4f2f99530197c2d4cb87.png]  
**ref是搜索索引树。用到了索引。还有一个优化是对于字符串列，在增加索引的时候，会给索引定义长度，前几个字符可以区分出来不同就可以了。**  
**现在是不管顺序都只扫1行就可以了。**

**我们继续看**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]  
**我们看看下面问题，加了索引，感觉时间还是比较久**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]  
**但是当我们去创建索引的时候，没有考虑到字符串有多长，越长越不好。**  
**我们要指定索引的长度**  
![在这里插入图片描述][9f6876ee6b064b39bd204bf923103ed3.png]

**显示已经创建了索引，但是为什么没有用到索引呢？**  
还有一个问题是：password是字符串类型，而我们写的时候是默认整型，MySQL在做过滤的时候涉及到了类型转换，把整型转成字符串类型。**如果SQL涉及到类型转换，就不能用到索引了！！！**  
![在这里插入图片描述][4cce322d0bbb4a448700062e8d743a6c.png]  
**现在就OK了！**  
![在这里插入图片描述][ad3b00af2c734a689bbc30ff7468e0b8.png]  
**删除索引**  
![在这里插入图片描述][70bb03df671c40b2b8e8517d6776ba73.png]

## 定义索引的注意问题 ##

1.经常作为where条件过滤的字段考虑添加索引  
2.字符串列创建索引时，尽量规定索引的长度，而不能让索引值的长度key\_len过长  
3.索引字段涉及类型强转、mysql函数调用、表达式计算等，索引就用不上了

## 索引的执行过程 ##

**explain查看执行计划**  
**使用explain查看sql的执行计划，分析索引的执行过程，mysql的user权限表示例如下：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]  
**可以看到使用了主键索引，共扫描1行，Using index表示直接从索引树上查询到结果，不需要回表。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]

## explain结果字段分析 ##

**select\_type**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]  
**table**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]  
**type**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]  
**ref**  
![在这里插入图片描述][318b155480b04cd19a4c45680fd4e65b.png]  
**Extra**  
![在这里插入图片描述][053ab3989d7b4f8da52aab584d6b0d8c.png]

[bea59db4dca9456386c3be00855ab55f.png]: /images/20220829/bd76f0195b77415ea4aebafb417b5d88.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70]: /images/20220829/1b3c1308372b4d35955d481885ba7ab5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220829/0f00b67d10064a3b83488c87ac998ca7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220829/45560a55dccc4e40880d8becac000f03.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220829/dd3ded3e00c4404d957e84c861f9737f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220829/6f4ff43588b044fa943153bc7e5e2803.png
[794cac046fbf4f2f99530197c2d4cb87.png]: /images/20220829/15770350f2ea4c919c3fd3ed41bc7678.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220829/feccd4fe6a644458b45d4b963875eedf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 6]: /images/20220829/7ae3919930684c788bdd916fbb8190f4.png
[9f6876ee6b064b39bd204bf923103ed3.png]: /images/20220829/d53973c25ab94c24b294ab9e63c6ec3e.png
[4cce322d0bbb4a448700062e8d743a6c.png]: /images/20220829/d1cc55bce1ec46c1879289255ae09d46.png
[ad3b00af2c734a689bbc30ff7468e0b8.png]: /images/20220829/7b00e8195d8a44a09c0e3c16b46e0d70.png
[70bb03df671c40b2b8e8517d6776ba73.png]: /images/20220829/1423519341c647628adddcf77b9ef5a3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 7]: /images/20220829/21fd5ea848884ac59b05b755b5824fcb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 8]: /images/20220829/000a8d4e2c364d34a0dcb5b36e8b868f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 9]: /images/20220829/a608a831cb91411cbf64ac6eaf819ea0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 10]: /images/20220829/f0430b8813434a698beab863de863d80.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 11]: /images/20220829/b09e0d2584ed428d8ebd2022af9583cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0xJTlpFWVU2NjY_size_16_color_FFFFFF_t_70 12]: /images/20220829/f6377f0623e74261ba055231a7876c50.png
[318b155480b04cd19a4c45680fd4e65b.png]: /images/20220829/613036b5853c4e7287d0648b91827fe9.png
[053ab3989d7b4f8da52aab584d6b0d8c.png]: /images/20220829/cbc4861613e8479d83f9c495ceec8463.png