第16章性能优化

朴灿烈づ我的快乐病毒、 2022-06-14 11:24 182阅读 0赞

学习目标：  
　　MySQL性能优化就是通过合理安排资源，调整系统参数使MySQL运行更快、更节省资源。MySQL性能优化包括查询速度优化、更新速度优化、MySQL服务器优化等。本章将为读者讲解以下几个内容：性能优化的介绍、查询优化、数据库结构优化、MySQL服务器优化。

# 16.1 优化简介 #

优化ＭySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作相应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。本节将为读者介绍优化的基础知识。  
　　MySQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如，通过优化文件系统，提高磁盘I\\O的读写速度；通过优化操作系统调度策略，提高MySQL在高负荷情况下的负载能力；优化表结构、索引、查询语句等使查询响应更快。  
　　在MySQL中，可以使用SHOW STATUS语句查询一些MySQL数据库的性能参数。SHOW STATUS语句语法如下：  
　　> SHOW STATUS LIKE ‘value’;

其中，value是要查询的参数值，一些常用的性能参数如下：

*  Connections：连接MySQL服务器的次数。  
    
    
    > SHOW STATUS LIKE ‘Connections’;

![这里写图片描述][SouthEast]

*  Uptime:MySQL服务器的上线时间。

> SHOW STATUS LIKE ‘Uptime’;

![这里写图片描述][SouthEast 1]

*  Slow\_queries:慢查询的次数。

> SHOW STATUS LIKE ‘Slow\_queries’;

![这里写图片描述][SouthEast 2]

*  Com\_select:查询操作的次数。

> SHOW STATUS LIKE ‘Com\_select’;

![这里写图片描述][SouthEast 3]

*  Com\_insert:插入操作的次数。

> SHOW STATUS LIKE ‘Com\_insert’;

![这里写图片描述][SouthEast 4]

*  Com\_update:更新操作的次数。

> SHOW STATUS LIKE ‘Com\_update’;

![这里写图片描述][SouthEast 5]

*  Com\_delete:删除操作的次数。

> SHOW STATUS LIKE ‘Com\_delete’;

![这里写图片描述][SouthEast 6]

查询其他参数的方法和两个参数的查询方法相同。慢查询次数参数可以结合慢查询日志，找出慢查询语句，然后针对慢查询语句进行表结构优化或者查询语句优化。

# 16.2 优化查询 #

查询是数据库中最频繁的操作，提高查询速度可以有效地提高MySQL数据库的性能。本节将为读者介绍优化查询的方法。

## 16.2.1 分析查询语句 ##

通过对查询语句的分析，可以了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询语句。ＭySQL中提供了EXPLAIN语句和DESCRIBE语句，用来分析查询语句。本小节将为读者介绍使用EXPLAIN语句和DESCRIBE语句分析查询语句的方法。  
　　EXPLAIN语句的基本语法如下：  
　　> EXPLAIN \[EXTENDED\] SELECT select\_options

使用EXTENED关键字，EXPLAIN语句将产生附加信息。select\_options是SELECT语句的查询选项，包括FROM WHERE子句等。  
　　执行该语句，可以分析EXPLAIN后面的SELECT语句的执行情况，并且能够分析出所查询的表的一些特征。  
　　【例16.1】使用EXPLAIN语句来分析1个查询语句，执行语句如下：

EXPLAIN SELECT * FROM fruits;

![这里写图片描述][SouthEast 7]

下面对查询结果进行解释。  
id：SELECT识别符。这是SELECT的查询序列号。  
select\_type:表示SELECT语句的类型。它可以是以下几种取值：SIMPLE表示简单查询，其中不包括连接查询和子查询；PRIMARY表示主查询，或者是最外层的查询语句；UNION表示连接查询的第2个或后面的查询语句；DEPENDENT UNION,连接查询中的第2个或后面的SELECT语句，取决于外面的查询；UNION RESULT,连接查询的结果；SUBQUERY，子查询中的第1个SELECT语句；DEPENDENT SUBQUERY，子查询中的第1个SELECT，取决于外面的查询；DERIVED，导出表的SELECT（FROM 子句的子查询）。  
table：表示查询的表。  
type：表示表的连接类型。下面按照从最佳类型到最差类型的顺序给出各种连接类型：  
（1）system  
该表是仅有一行的系统表。这是const连接类型的一个特例。  
（2）const  
数据表最多只有一个匹配行，它将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它们只读取一次。const用于使用常数值比较PRIMARY KEY或UNIQUE索引的所有部分的场合。  
在下面的查询中，tbl\_name可用于const表:

> SELECT \* FROM tbl\_name WHERE primary\_key=1;  
> SELECT \* FROM tbl\_name WHERE primary\_key\_part1=1 AND primary\_key\_part2=2;

（3）eq\_ref  
　　对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时，即可使用这种类型。  
　　eq\_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。  
　　在下面的例子中，MySQL可以使用eq\_ref连接来处理ref\_tables:

> SELECT \* FROM ref\_table,other\_table  
> WHERE ref\_table.key\_column=other\_table.column;  
> SELECT \* FROM ref\_table,other\_table  
> WHERE ref\_table.key\_column\_part1=other\_table.column  
> AND ref\_table.key\_column\_part2=1;

（4）ref  
　　对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列组合。ref可以用与使用=或<=>操作符的带索引的列。  
　　在下面的列子中，MySQL可以使用ref连续来处理ref\_tables:

> SELECT \* FROM ref\_table WHERE key\_column=expr;
> 
> SELECT \* FROM

[SouthEast]: /images/20220614/39e7be403b214be68ab213a4c4aea67c.png
[SouthEast 1]: /images/20220614/568721b3fc8f4472bc5d69e41dbc2b2a.png
[SouthEast 2]: /images/20220614/28d77ed999044805b4b9dc904186b5bf.png
[SouthEast 3]: /images/20220614/d450a05ccd13415981cea33a5757918b.png
[SouthEast 4]: /images/20220614/881538964f61436a863bbd8e6f51da26.png
[SouthEast 5]: /images/20220614/66af8ed8d4e84af88012044fbb140503.png
[SouthEast 6]: /images/20220614/bf6d0ccef1934f39810840c3abd3036a.png
[SouthEast 7]: /images/20220614/44757e7007d64c089170a21524df2ab3.png