mysql_query优化之explain&Profiling详解

╰半橙微兮° 2024-03-27 10:27 85阅读 0赞

## 1、explain ##

> 要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。 MySQL本身的功能架构分为三个部分，分别是 应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。

> 应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。  
> 逻辑层，主要负责查询处理、事务管理等其他数据库功能处理

> 以查询为例。  
> 首先接收到查询SQL之后，数据库会立即分配一个线程对其进行处理，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划，然后交由计划执行器来执行。  
> 计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

> 物理层，实际物理磁盘上存储的文件，主要有分文数据文件，日志文件。

> 通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方便我们进行查看以及为作为性能分析的依据。

![在这里插入图片描述][24beee0a68334c10851185810fa6775a.png]

### 1.1、 id列 ###

> id表示查询语句的序号，自动分配，顺序递增，值越大，执行优先级越高。  
> ![在这里插入图片描述][ae72788f9f724fc09bead2666d926572.png]  
> id相同时，优先级由上而下。  
> ![在这里插入图片描述][281f9b7f7fb74c4685a26e0074d942e3.png]

### 1.2、 select\_type列 ###

> select\_type表示查询类型，常见的有SIMPLE简单查询、PRIMARY主查询、SUBQUERY子查询、UNION联合查询、UNION  
> RESULT联合临时表结果等。  
> ![在这里插入图片描述][77b7de8d85fb4817bd5a2de8b5ce6314.png]

### 1.3、 table列 ###

> table表示SQL语句查询的表名、表别名、临时表名。  
> ![请添加图片描述][6c07c7071b1d41248371b93617cea6c0.jpeg]

### 1.4、 partitions列 ###

> partitions表示SQL查询匹配到的分区，没有分区的话显示NULL。  
> ![在这里插入图片描述][e5c467c7d8554358a765a81d93126485.png]

### 1.5、type列 ###

> type表示表连接类型或者数据访问类型，就是表之间通过什么方式建立连接的，或者通过什么方式访问到数据的。 具体有以下值，性能由好到差依次是：system > const > eq\_ref > ref > fulltext > ref\_or\_null > index\_merge >unique\_subquery > index\_subquery > range > index > ALL  
> system  
> 当表中只有一行记录，也就是系统表，是 const 类型的特列。  
> ![在这里插入图片描述][f763aefa8f2b48d79e0e5be8d3ba09f7.png]  
> const  
> 表示使用主键或者唯一性索引进行等值查询，最多返回一条记录。性能较好，推荐使用。  
> ![在这里插入图片描述][2da97e8bdc604545beacda3ff26465b2.png]  
> eq\_ref  
> 表示表连接使用到了主键或者唯一性索引，下面的SQL就用到了user表主键id。  
> ![在这里插入图片描述][271ed018b9394e1f93efc64e92645761.png]  
> ref  
> 表示使用非唯一性索引进行等值查询。  
> ![在这里插入图片描述][506757538e434beda73c7cd81ba08cac.png]  
> ref\_or\_null  
> 表示使用非唯一性索引进行等值查询，并且包含了null值的行。  
> ![在这里插入图片描述][23dc92d4836a4d8197a7a60f94362e72.png]  
> index\_merge  
> 表示用到索引合并的优化逻辑，即用到的多个索引。  
> ![在这里插入图片描述][89fa773f257b4ca8b9c87cb9ba0deff6.png]  
> range  
> 表示用到了索引范围查询。  
> ![在这里插入图片描述][98772876d07b424a977f2bb5e002db66.png]  
> index  
> 表示使用索引进行全表扫描。  
> ![在这里插入图片描述][382ed030cb234028906e916097d20802.png]  
> ALL  
> 表示全表扫描，性能最差。  
> ![在这里插入图片描述][936b1cabf8d741c5a3f8ac5d2739e758.png]

### 1.6、possible\_keys列 ###

> 表示可能用到的索引列，实际查询并不一定能用到。  
> ![在这里插入图片描述][e9051dd70ba54fdf9be6e04208e9f774.png]

### 1.7、 key列 ###

> 表示实际查询用到索引列。  
> ![在这里插入图片描述][75440c88290548bb8a812c69d2b15814.png]

### 1.8、key\_len列 ###

> 表示索引所占的字节数。  
> ![在这里插入图片描述][b06f44d2da844663b92bc590fb656dda.png]  
> 每种类型所占的字节数如下：  
> ![在这里插入图片描述][c359897500464b4ab295d7a291c4c102.png]

### 1.9、 ref列 ###

> 表示where语句或者表连接中与索引比较的参数，常见的有const（常量）、func（函数）、字段名。 如果没用到索引，则显示为NULL。  
> ![在这里插入图片描述][398b606d16e342f694642530731bfadc.png]  
> ![在这里插入图片描述][cbd53284c8a54b1cb030147ad60daabd.png]  
> ![在这里插入图片描述][ad0dfeebf42842008b6ddcac2a5bc3d7.png]

### 1.10. rows列 ###

> 表示执行SQL语句所扫描的行数。  
> ![在这里插入图片描述][f9c778f7013e40c993e6dfe1e704b50e.png]

### 1.11. filtered列 ###

> 表示按条件过滤的表行的百分比。  
> ![在这里插入图片描述][cdbb2a2ffd29471189546ff4ac2e6a10.png]  
> 用来估算与其他表连接时扫描的行数，row x filtered = 252004 x 10% = 25万行

### 1.12、Extra列 ###

> 表示一些额外的扩展信息，不适合在其他列展示，却又十分重要。  
> Using where  
> 表示使用了where条件搜索，但没有使用索引。  
> ![在这里插入图片描述][cecc41b8bf4b471aab5ff46cc78124ed.png]  
> Using index  
> 表示用到了覆盖索引，即在索引上就查到了所需数据，无需二次回表查询，性能较好。  
> ![在这里插入图片描述][9990a204b44745c7b407fde13a7a9bcc.png]  
> Using filesort  
> 表示使用了外部排序，即排序字段没有用到索引。  
> ![在这里插入图片描述][95dc5441a4884b2b945a9e1280d6423b.png]  
> Using temporary  
> 表示用到了临时表，下面的示例中就是用到临时表来存储查询结果。  
> ![在这里插入图片描述][03e915855466401fae6849233281d8d3.png]  
> Using join buffer  
> 表示在进行表关联的时候，没有用到索引，使用了连接缓存区存储临时结果。  
> 下面的示例中user\_id在两张表中都没有建索引。  
> ![在这里插入图片描述][5aa69ddab49c4796a92f0f774c01911c.png]  
> Using index condition  
> 表示用到索引下推的优化特性。  
> ![在这里插入图片描述][b7d7394e10f9415ea368f7871186e884.png]

## 2、使用Profiling进行query优化 ##

> MySQL 的Query Profiler 是一个使用非常方便的Query 诊断分析工具，通过该工具可以获取一条 Query 在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP 等，以及发生的PAGE FAULTS， CONTEXT SWITCHE 等等，同时还能得到该Query 执行过程中MySQL 所调用的各个函数在源文件中的位置。  
> 下面我们看看Query Profiler 的具体用法。  
> ![在这里插入图片描述][5c99f4fe44c946d18840b34a4e1deda5.png]  
> ![在这里插入图片描述][caca8f6394094554abda4fb0911f917f.png]  
> ![在这里插入图片描述][704eaa2bee54434d81bd44d235b691a4.png]  
> query优化的其他技巧  
> 索引设计依赖谓词表达式（条件表达式或者真值表达式----where后面的语句）  
> 尽量避免Join  
> 数据适当的冗余

[24beee0a68334c10851185810fa6775a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/208f644782844983b1a237d496cfb5e3.png
[ae72788f9f724fc09bead2666d926572.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/20fb1d3d22464f6d815615a44bfd8c14.png
[281f9b7f7fb74c4685a26e0074d942e3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c1430f2ed2a74f80bfc24b261929696e.png
[77b7de8d85fb4817bd5a2de8b5ce6314.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/2574791de7b34715ba5bb0ce6bc05ce9.png
[6c07c7071b1d41248371b93617cea6c0.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8daac8c76a2448cea491b94fefde87a4.png
[e5c467c7d8554358a765a81d93126485.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7042a263499242a7b8cdbfdb0eb01414.png
[f763aefa8f2b48d79e0e5be8d3ba09f7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d6ebbbb6d9db4d6ea353a8fdeb90d7f7.png
[2da97e8bdc604545beacda3ff26465b2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/262381e68e7048cbad57697fa61a1258.png
[271ed018b9394e1f93efc64e92645761.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b6152866f6e646148ff61688439418ea.png
[506757538e434beda73c7cd81ba08cac.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/563f619272cb4bcea1fd67a0c30430eb.png
[23dc92d4836a4d8197a7a60f94362e72.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/6820a149f06747f89ed7f03408b9a5cc.png
[89fa773f257b4ca8b9c87cb9ba0deff6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/1288d40b51b649218a3013d183ebc111.png
[98772876d07b424a977f2bb5e002db66.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/020b8852ff824d01b49a1d988ab3cdfa.png
[382ed030cb234028906e916097d20802.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/bb291b8dc7f9431788f68b474ebed4d7.png
[936b1cabf8d741c5a3f8ac5d2739e758.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8239c65c85cc4ef78843174e2d2bdd6b.png
[e9051dd70ba54fdf9be6e04208e9f774.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8f20d1a935044cafab9847b36eca1ce7.png
[75440c88290548bb8a812c69d2b15814.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/912664c79be84e4fb297aa07dc65f753.png
[b06f44d2da844663b92bc590fb656dda.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b07fabc77ce44dd4a125bffa883c95ee.png
[c359897500464b4ab295d7a291c4c102.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/f906b53a005e46b7b45854247de3278f.png
[398b606d16e342f694642530731bfadc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/db9a830e471d4c9baa159f9fbc260451.png
[cbd53284c8a54b1cb030147ad60daabd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/d33065cf15d5472a8d23ab004a747c3c.png
[ad0dfeebf42842008b6ddcac2a5bc3d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/893c1edbd5424f9f94de1ecd8b26b015.png
[f9c778f7013e40c993e6dfe1e704b50e.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/89e173e2ec6240a1b15b6330efc7318b.png
[cdbb2a2ffd29471189546ff4ac2e6a10.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a96884ef69d8493b8ddc00a40de9816e.png
[cecc41b8bf4b471aab5ff46cc78124ed.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a5b8f68d0cb14de08f1e7eb8c666a661.png
[9990a204b44745c7b407fde13a7a9bcc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/e4b520b2210649feb044e1b0802791df.png
[95dc5441a4884b2b945a9e1280d6423b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/aba19d76f5c64591b597321ca11f93e8.png
[03e915855466401fae6849233281d8d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c0a41da8f1e047b19168408c3a09a059.png
[5aa69ddab49c4796a92f0f774c01911c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/2a14709a38f84e8abeeed562c6c18161.png
[b7d7394e10f9415ea368f7871186e884.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/8201369e08c3465e96131ea454a0297d.png
[5c99f4fe44c946d18840b34a4e1deda5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/496c17a9e9ed4dcaa2b3700050c5c6f0.png
[caca8f6394094554abda4fb0911f917f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/869f108dc29649a2b09a3083f37a1203.png
[704eaa2bee54434d81bd44d235b691a4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/fdcefd4b82c24dc3b21acd10bb6b1b78.png