mysql derived 性能_MySQL性能优化知识总结

最新推荐文章于 2024-04-12 09:54:42 发布

垃圾箱博物馆

最新推荐文章于 2024-04-12 09:54:42 发布

阅读量905

点赞数

文章标签： mysql derived 性能

本文链接：https://blog.csdn.net/weixin_33195162/article/details/113906829

版权

本文总结了MySQL性能优化的关键点，包括查询性能参数的监控，如Connections、Slow_queries等，以及如何通过EXPLAIN和DESCRIBE分析查询语句。强调了索引对查询速度的影响，指出在特定条件下，如LIKE操作、OR关键字和多列索引的使用可能会影响索引效果。此外，提出了SQL语句优化建议，如避免全表扫描、使用EXISTS替代IN等。

摘要由CSDN通过智能技术生成

MySQL性能优化知识总结

 Leefs

 2020-03-19 PM

 272℃

 0条

# MySQL性能优化知识总结

### 一、查询MySQL性能参数

**1. MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。**

SHOW STATUS语句语法如下所示：

```java

SHOW STATUS LIKE 'value';

```

value是要查询的参数值

> Connections:连接MySQL服务器的次数

> Uptime:MySQL服务器的上线时间

> Slow_queries:慢查询的次数

> Com_select:查询操作的次数

> Com_insert:插入操作的次数

> Com_update:更新操作的次数

> Com_delete:删除操作的次数

SQL语句示例：

```sql

-- 查询MySQL服务器的连接次数

show status like 'Connections';

-- 查询MySQL服务器的慢查询次数，慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或查询语句优化

show status like 'Slow_queries';

```

**2. 分析查询语句**

通过对查询语句的分析，可以了解查询语句执行的情况，找出查询语句执行的瓶颈，从而优化查询语句。

mysql中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。

在查询语句前面加上EXPLAIN可以分析出所查询的表的一些特征

例如：

```mysql

explain select * from ep_book;

```

![MySQL性能优化知识总结01.png][1]

**查询结果说明**

+ id:select识别符，select的查询序列号

+ select_type:标识select语句的类型

| 类型名称 | 说明 |

| ---------------------------------------- | --------------------------------------------------------- |

| SIMPLE(simple) | 表示简单查询，其中不包括连接查询和子查询 |

| PRIMARY(primary) | 表示主查询，或者是最外层的查询语句 |

| UNION(union) | 表示连接查询的第2个或者后面的查询语句 |

| UNION RESULT(union result) | 连接查询的结果 |

| SUBQUERY(subquery) | 子查询的第1个select语句 |

| DEPENDENT SUBQUERY(dependent subquery) | 子查询的第1个select,取决于外面的查询 |

| DEPENDENT UNION(dependent union) | 连接查询中的第2个或者后面的select语句，取决于外面的查询。 |

| DERIVED(derived) | 导出表的SELECT(FROM子句的子查询) |

+ table:表示查询的表

+ partitions:如果查询是基于分区表的话，会显示查询将访问的分区

+ type:表示表的连接类型

下面按照从最佳类型到最差类型的顺序给出各种连接类型

| 连接类型 | 说明 |

| --------------- | ------------------------------------------------------------ |

| system | 该表是仅有一行的系统表，这是const连接类型的一个特例 |

| const | 数据表最多只有一个匹配行(通过唯一值为条件进行查询，如：主键id) |

| eq_ref | 对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或者PRIMARY KEY时候，即可使用这种类型。 |

| ref | 对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNION也不是primaey key的情况，或者查询中使用了索引列的左子集，即索引中左边的部分组合。ref可以用于使用=或者<=>操作符的带索引的列。 |

| ref_or_null | 该连接类型如果ref，但是如果添加了mysql可以专门搜索包含null值的行，在解决子查询中经常使用该连接类型的优化。 |

| index_merge | 该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素 |

| unique_subquery | 该类型替换了下面形式的in子查询的ref。是一个索引查询函数，可以完全替代子查询，效率更高。 |

| index_subquery | 该连接类型类似于unique_subquery，可以替换in子查询，但是只适合下列形式的子查询中非唯一索引。 |

| range | 只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。key_len包含所使用索引的最长关键元素。当使用=，<>,>,>=,，between或者in操作符，用常量比较关键字列时，类型为range |

| index | 该连接类型与all相同，除了只扫描索引树 |

| all | 对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没有标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。 |

补充：

eq_ref：可以用于使用“=”操作符比较带索引的列。

比较值可以为常量或者一个在该表前面所读取的表的列的表达式。

例如：

```mysql

EXPLAIN SELECT * FROM user,db_company WHERE user.company_id = db_company.id;

```

+ possible_keys：possible_keys列指出mysql能使用哪个索引在该表中找到行。如果该列是null，则没有相关的索引。在这种情况下，可以通过检查where子句看它是否引起某些列或者适合索引的列来提高查询性能。如果是这样，可以创建适合的索引来提高查询的性能。

+ key：表示查询实际使用到的索引，如果没有选择索引，该列的值是null，要想强制mysql使用或者忽视possible_key列中的索引，在查询中使用force index、use index或者ignore index。

+ key_len：表示mysql选择索引字段按照字节计算的长度，如果健是null，则长度为null。注意通过key_len值可以确定mysql将实际使用一个多列索引中的几个字段

+ ref:表示使用哪个列或者常数或者索引一起来查询记录。

+ rows:显示mysql在表中进行查询必须检查的行数。

**DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。**

### **二、索引对查询速度的影响**

mysql中提高性能的最有效的方式就是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快了查询的速度。因此，索引对查询的速度有着至关重要的影响。使用索引可以快速的定位到表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。如果查询的时候没有使用索引，查询语句将扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。如果使用索引进行查询，查询语句可以根据索引快速定位到待查询的记录，从而减少查询的记录数，达到提高查询速度的目的。

##### **1. 使用索引和不使用索引的区别**

不使用索引rows列的值是26，说明此查询语句扫描了26条记录。

![MySQL性能优化知识总结02.png][2]

使用索引rows列的值是1，说明此查询语句扫描了1条记录。查询速度自然变快了。这种方法也可以判断自己添加的索引是否起了作用。

![MySQL性能优化知识总结03.png][3]

##### **2. 几种索引失效的情况**

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起到作用。下面是几种比较特殊的情况。在这些情况下，有可能使用带有索引的字段查询时，索引并没有起到作用。

**1. 使用like关键字的查询语句**

在使用like关键字进行查询的查询语句中，如果匹配字符串的第一个字符为"%"，索引不会起作用。只有"%"不在第一个位置，索引才会起到作用。

![MySQL性能优化知识总结04.png][4]

使用模糊查询，”%“在后面的结果，显示索引起了作用了的。

![MySQL性能优化知识总结05.png][5]