mysql进阶（二）14-30讲

àì夳堔傛蜴生んèń 2022-05-19 14:21 114阅读 0赞

## **1.mysql索引：** ##

索引是帮助mysql高效获取数据的数据结构；（排好序的快速查找数据结构）

![70][]![70 1][]

如果索引字段是char类型，那么在使用索引时，char类型的值必须加引号；如果索引字段时blob等大数据类型，必须指定长度。

![70 2][]![70 3][]![70 4][]

# #

## **2.mysql索引结构：** ##

BTree 索引    Hash索引  full-text索引   R-Tree索引

## **3.哪些情况需要创建索引** ##

![70 5][]

## **4.不适合建立索引的情况** ##

（1）表的记录太少

（2）经常需要增删改的表：提高查询的速度，同时会降低更新表的速度，因为更新表时，mysql不仅保存数据，还要保存索引文件。

（3）数据重复且分布均匀的表字段（比如性别，国籍等字段），因此应该只为最经常查询和最经常排序的数据建立索引。因为某个数据列包含许多的重复内容，为它建立索引效果不大。

## **5.性能分析：** ##

（1）mysql查询优化分析器

![70 6][]

（2）mysql的主要瓶颈：

![70 7][]

服务器查看机器性能：[https://www.cnblogs.com/ftl1012/p/vmstat.html][https_www.cnblogs.com_ftl1012_p_vmstat.html]

（3）explain（查看执行计划）：可以模拟优化器执行SQL查询语句，从而指导Mysql是如何处理SQL的；从而分析查询语句或者是性能瓶颈。

## **6.explain(查看执行计划)** ##

### （1）执行计划的功能？ ###

![70 8][]

### （2）执行计划包含的信息 ###

![70 9][]

# ①id: #

select查询的序列号，包含一组数据，表示查询中执行select子句或者操作表的顺序。id列为null的就表是这是一个结果集，不需要使用它来进行查询。包含三种情况：

第一种：id相同

![70 10][]

第二种：id不同

![70 11][]

第三种：id不同以及相同，同时出现。 derived2表示衍生的id为2的表，即先执行s1表，再执行t2；

![70 12][]

### ②select\_type ###

主要包含simple ， primary ,subquery ,derived , union,union result;查询的类型，主要是用于区普通查询，联合查询，子查询等复杂查询。

![70 13][]

![70 14][]

### ③table ###

显示这一行数据是关于哪一张表的

**备注：**

**partitions:该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。**

**filtered:使用explain extended时会出现这个列，5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。**

### ④type ###

访问类型排列，显示查询使用了何种类型，从最好到最差依次是：system>const>eq\_ref>ref>range>index>all

![70 15][]

![70 16][]

例子1：const 因为表中只匹配一行，

![70 17][]

例子2：eq\_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。当只能匹配一行的情况就会出现，下面的情况。

![70 18][]

例子3.非唯一性索引扫描，返回某个单独值的所有行。

![70 19][]

例子4.range只检索给定范围的行，使用一个索引来选择行。

![70 20][]

例子5.index,full index scan，index与al区别为index类型只遍历索引树。这通常比ALL要快，因为索引文件比数据文件小（即index是从索引中读取的，而all是从硬盘中读取的）

![70 21][]

### ⑤possible\_keys ###

显示可能应用在这张表中的索引，一个或者多个，查询涉及到的字段上若存在索引，则索引将被列出来，但不一定被查询实际使用。

### ⑥key ###

实际用到的索引，如果为null,则没有使用索引；**查询中若使用了覆盖索引，则该索引仅出现在key列表中**

### ⑦key\_length ###

表示**索引中使用的字节数**，可以通过该列的计算查询中使用的索引长度。在不损失精度的情况下，长度越短越好。key\_len显示的值为索引字段最大可能长度，**并非实际使用长度**，即key\_len是根据表定义计算而得，不是通过表内检索出来的。

![70 22][]

### ⑧ref ###

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或者常量被用于查找索引列上的值

![70 23][]

### ⑨rows ###

根据表统计信息及索引选用情况，大致估算出找到所需的记录行数

![70 24][]

### ⑩extra ###

包含不适合在其他列显示但十分重要的额外信息。

**using filesort**:说明mysql会对数据使用一个外部是索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为文件排序。

前者使用了useing filesort，而后者没有使用

![70 25][]

**using temporay:**使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by;

第一个没有覆盖索引,使用了using tempoary，第二个覆盖了。，性能上面的sql大于下面的sql

![70 26][]

**using index:**表示相应的select操作中使用了覆盖索引，避免访问表的数据行，效率较高！如果同时出现using where ,表明索引用来执行索引键值的查找。如果没有出现using where，表明索引用来读取数据而非执行查找动作。

![70 27][]

**覆盖索引**

![70 28][]  
**using where:**表明使用了where过滤

**using join buffer:** 使用连接缓存

**impossible where**：where 子句的值总是false,不能用来获取任何数据

**select tables optimaized away**；没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎count(\*) 操作，不必等到执行阶段在进行计算，在执行计划生成阶段就完成优化。

**distinct** :优化distinct,在找到第一个匹配的元组之后即停止找同样值的动作

总结：查看sql的执行计划，理解各个字段的含义

![70 29][]

[70]: https://img-blog.csdn.net/20180726135040142?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3ODYyMzM1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 1]: /images/20220519/3f0056d80ac5448db34e73b96bd52e8f.png
[70 2]: https://img-blog.csdn.net/20180906211317926?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3ODYyMzM1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 3]: https://img-blog.csdn.net/20180726135739125?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI3ODYyMzM1/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 4]: /images/20220519/2a33b1176a11474d85457ed08c0522cc.png
[70 5]: /images/20220519/46e6e737ef2948ea93028ea4b4848ca9.png
[70 6]: /images/20220519/4eb239d0cc294a04a8e4312517eddbd8.png
[70 7]: /images/20220519/f371ee14ed8348a4a16ee6f0c6029d26.png
[https_www.cnblogs.com_ftl1012_p_vmstat.html]: https://www.cnblogs.com/ftl1012/p/vmstat.html
[70 8]: /images/20220519/c3fa6fe399d443068de50cc32f01bcdc.png
[70 9]: /images/20220519/344285da9bcd48ba9168406ebf2712d7.png
[70 10]: /images/20220519/cb6af0e09df74aacba88e20ee5a602ee.png
[70 11]: /images/20220519/48d64faf818141c8bd261dedba7dcf90.png
[70 12]: /images/20220519/fec8949d35574d46bb570ef9ee8201a3.png
[70 13]: /images/20220519/7ba7980315704f56b8bdd2cba233d7c6.png
[70 14]: /images/20220519/53009d30324d4b5aa88e1d3b38a986ec.png
[70 15]: /images/20220519/d283ddbd6e294cd8abdc1a4a6ad4ac75.png
[70 16]: /images/20220519/4a584b3a661141bda3b064a2dc1f408d.png
[70 17]: /images/20220519/4747a800e6674cc28ca005b1168d1b1f.png
[70 18]: /images/20220519/124da1143de44f62b3ed236298d7f5d0.png
[70 19]: /images/20220519/5a7921de468547e0afaae24b19b3447a.png
[70 20]: /images/20220519/2867495283594d00a8a1ae042e72621e.png
[70 21]: /images/20220519/2194ce662acb4b76b103471eb8f90e46.png
[70 22]: /images/20220519/041beed6d9dd49e0a34660df82ca2719.png
[70 23]: /images/20220519/c64f65f50d5440129c4f7fce6bea8802.png
[70 24]: /images/20220519/8ef8f729641c4b57a6e89745b4e22983.png
[70 25]: /images/20220519/2ff3ba8645884d63b863922202416e42.png
[70 26]: /images/20220519/837376dfa8ac40bf827e9257f367cefc.png
[70 27]: /images/20220519/df3bd74ce7e3410a92e5f01f76056620.png
[70 28]: /images/20220519/70535fd5991b4a8b9c8bc5b9c13f6b67.png
[70 29]: /images/20220519/36f32e31865147569ec593b2b64b927f.png