mysql 性能优化

最新推荐文章于 2024-05-30 14:37:38 发布

书香水墨

最新推荐文章于 2024-05-30 14:37:38 发布

阅读量165

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_27870421/article/details/102521338

版权

数据库专栏收录该内容

118 篇文章 3 订阅

订阅专栏

`一、优化简介`

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

在MySQL中，可以使用SHOW STATUS语句査询一些MySQL数据库的性能参数。SHOW STATUS语句语法如下：

SHOW STATUS LIKE 'value';

其中，value是要査询的参数值，一些常用的性能参数如下：
• Connections:连接MySQL服务器的次数。
• Uptime: MySQL服务器的上线时间。
• Slow queries:慢查询的次数。
• Com_select:查询操作的次数。
• Com_insert:插入操作的次数。
• Com update:更新操作的次数。
• Com_delete:删除操作的次数.

如果査询MySQL服务器的连接次数，可以执行如下语句：
SHOW STATUS LIKE ‘Connections’;

如果查询MySQL服务器的慢查询次数，可以执行如下语句：
SHOW STATUS LIKE ‘Slow_queries’;

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

`二、优化査询`

2.1 分析查询语句

通过对查询语句的分析，可以了解査询语句执行情况，找出查询语句执行的瓶颈，从而优化查询语句。MySQL中提供了 EXPLAIN语句和DESCRIBE语句，用来分析查询语句。

2.1.1 EXPLAIN

-- 使用EXTENED关键字，EXPLAIN语句将产生附加信息。select_options是SELECT语句的查 询选项，包括FROM WHERE子句等。
EXPLAIN [EXTENDED] SELECT select_options

示列：使用EXPLAIN语句来分析1个査询语句

EXPLAIN SELECT * FROM fruits;

结果

|id  | select type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
|1   | SIMPLE      | fruits | ALL  | NULL          | NULL | NULL    | NULL | 16   | 	  |

查询结果解析

id： SELECT识别符。这是SELECT的査询序列号。
selectjype：表示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_part 1=1 AND primary_key_part2=2:
(3) eq_ref
对于每个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在査询中使用并且索引是UNIQUE或PRIMARY KEY时，即可使用这种类型。
eq_ref可以用于使用“=”操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。
在下面的例子中，MySQL可以使用eq_ref连接来处理refjables：
SELECT * FROM refjable,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_tabie.key_column_partl=other_table.column
AND ref_table.key_column_part2=l;
(4) ref
对于来自前面的表的任意行组合，将从该表中读取所有匹配的行。这种类型用于索引既不是 UNIQUE也不是PRIMARY KEY的情况，或者查询中使用了索引列的左子集，即索引中左边的部分列组合。ref可以用于使用=或＜=＞操作符的带索引的列。
在下面的例子中，MySQL可以使用ref连接来处理reliables：
SELECT * FROM refutable WHERE key_column=expr;
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_part 1 =other_table.column
AND ref_table.key_column_part2=l;
(5) ref_or_null
该连接类型如同ref,但是添加了 MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该连接类型的优化。
在下面的例子中，MySQL可以使用ref^or null连接来处理ref^tables：
SELECT * FROM refutable
WHERE key_column=expr-OR key_column IS NULL;
(6) index merge
该连接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单， key_len包含了使用的索引的最长的关键元素。
(7) unique_subquery
该类型替换了下面形式的IN子查询的ref：
value IN (SELECT primary key FROM single_table WHERE some_expr)
unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
(8) index subquery
该连接类型类似于unique_subquery,可以替换IN子査询，但只适合下列形式的子查询中的非唯一索引：
value IN (SELECT key_column FROM single table WHERE some expr)
(9) range
只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。
当使用=、<>、＞、＞=、<、<=、IS NULL、 <=＞、BETWEEN或者IN操作符，用常量比较关键字列时，类型为range。
下面介绍几种检索指定行情况：
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_partl= 10 AND key_part2 IN (1020,30);
(10) index
该连接类型与ALL相同，除了只扫描索引树。这通常比ALL快，因为索引文件通常比数据文件小。
(11) ALL
对于前面的表的任意行组合，进行完整的表扫描。如果表是第一个没标记const的表，这样不好，并且在其他情况下很差。通常可以增加更多的索引来避免使用ALL连接。
possible_keys： possible keys列指出MySQL能使用哪个索引在该表中找到行。如果该列是 NULL,则没有相关的索引。在这种情况下，可以通过检查WHERE子句看它是否引用某些列或适合索引的列来提高査询性能。如果是这样，可以创建适合的索引来提高査询的性能。
key：表示査询实际使用到的索引，如果没有选择索引，该列的值是NULL.要想强制MySQL 使用或忽视possible_keys列中的索引，在査询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX.参见 SELECT 语法。
key_len：表示MySQL选择的索引字段按字节计算的长度，如果键是NULL,则长度为NULL。注意通过key」en值可以确定MySQL将实际使用一个多列索引中的几个字段。
ref：表示使用哪个列或常数与索引一起来查询记录。
rows：显示MySQL在表中进行査询时必须检查的行数。
Extra：该列MySQL在处理査询时的详细信息。

2.1.1 DESCRIBE

DESCRIBE语句的使用方法与EXPLAIN语句是一样的，并且分析结果也是一样的。
DESCRIBE 语句的语法形式如下：DESCRIBE SELECT select options
DESCRIBE可以缩写成DESC。

`三、索引对查询速度的影响`

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

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

3.1 使用LIKE关键字的查询语句

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

3.2 使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引可以包括16个字段。对于多列索引，只有査询条件中使用了这些字段中第1个字段时，索引才会被使用。

3.3 使用OR关键字的查询语句

査询语句的査询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

`五、优化子查询`

MySQL从4.1版体开始支持子查询，使用子查询可以进行SELECT语句的嵌套査询，即一个 SELECT査询的结果作为另一个SELECT语句的条件。子査询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子査询虽然可以使査询语句很灵活，但执行效率不高。执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。
在MySQL中，可以使用连接(JOIN)查询来替代子査询。连接査询不需要建立临时表，其速度比子査询要快，如果査询中使用索引的话，性能会更好。连接之所以更有效率，是因为MySQL 不需要在内存中创建临时表来完成査询工作。

`六、优化数据库结构`

一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使査询速度更快。数据库结构的设计，需要考虑数据冗余、査询和更新的速度、字段的数据类型是否合理等多方面的内容。

6.1 将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

6.2 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合査询的数据插入到中间表中，然后将原来的联合査询改为对中间表的査询，以此来提高查询效率。

6.3 增加冗余字段

设计数据库表时应尽量遵循范式理论的规约，尽可能减少冗余字段，让数据库设计看起来精致、优雅。但是，合理地加入冗余字段可以提高査询速度。

6.4 优化插入记录的速度

插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。根据这些情况，可以分别进行优化。

6.5 分析表、检查表和优化表

MySQL提供了分析表、检査表和优化表的语句。分析表主要是分析关键字的分布；检査表主要是检査表是否存在错误；优化表主要是消除删除或者更新造成的空间浪费。

6.5.1 分析表

MySQL中提供了 ANALYZE TABLE语句分析表，ANALYZE TABLE语句的基本语法如下：

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[,tbl_name]...

LOCAL关键字是NO_WRITE_TO_BINLOG关键字的别名，二者都是执行过程不写入二进制日志，
tbl_name为分析的表的表名，可以有一个或多个。

使用ANALYZE TABLE分析表的过程中，数据库系统会自动对表加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ANALYZE TABLE语句能够分析InnoDB、BDB和 MylSAM类型的表。

示列

mysql> ANALYZE TABLE message;
| Table      | Op      | Msg_type | Msg_text |
| test.fruits| analyze | status   | OK       |
1 row in set （0.18 sec）

上面结果显示的信息说明如下：
• Table:表示分析的表的名称。
• Op:表示执行的操作。analyze表示进行分析操作.
• Msg_type:表示信息类型，其值通常是状态（status ）.信息（info ）.注意（note ）.警告（warning ）和错误（error）之一。
• Msg_text:显示信息。

6.5.2 检查表

MySQL中可以使用CHECK TABLE语句来检査表。CHECK TABLE语句能够检査InnoDB和 MylSAM类型的表是否存在错误。对于MylSAM类型的表，CHECK TABLE语句还会更新关键字统计数据。而且，CHECK TABLE也可以检査视图是否有错误，比如在视图定义中被引用的表己不存在。该语句的基本语法如下：

CHECK TABLE tbl_name [, tbl_name]... [option]...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

tbl_name 是表名；
option参数有5个取值，分别是QUICK、FAST、MEDIUM、EXTENDED 和CHANGED。各个选项的意义分别是：
• QUICK:不扫描行，不检查错误的连接•
• FAST:只检查没有被正确关闭的表.
• CHANGED:只检查上次检查后被更改的表和没有被正确关闭的表。
• MEDIUM:扫描行,以验证被删除的连接是有效的.也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点.
• EXTENDED:对每行的所有关键字进行一个全面的关键字查找.这可以确保表是100%— 致的，但是花的时间较长。

option 只对MylSAM类型的表有效，对InnoDB类型的表无效。CHECK TABLE语句在执行过程中也会给表加上只读锁。

6.5.3 优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。该语句对InnoDB和MylSAM类型的表都有效。但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR, BLOB或TEXT类型的字段。 OPTILMIZE TABLE语句的基本语法如下：

OPTIMIZE [LOCAL | NO_WR1TE_TO_BINLOG] TABLE tbl_name [, tbl_name]...

LOCAL I NO_WRITE_TO_BINLOG关键字的意义和分析表相同，都是指定不写入二进制日志；
tbl_name是表名。

通过OPTIMIZE TABLE语句可以消除删除和更新造成的文件碎片。OPTIMIZE TABLE语句在执行过程中也会给表加上只读锁。

一个表使用了TEXT或者BLOB这样的数据类型，如果已经删除了表的一大部分，或者已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更新，则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片在多数的设置中，根本不需要运行OPTIMIZE TABLE。即使对可变长度的行进行了大量的更新，也不需要经常运行，每周一次或每月一次即可，并且只需要对特定的表运行。

`七、优化MySQL服务器`

优化MySQL服务器主要从两个方面来优化，一方面是对硬件进行优化；另一方面是对MySQL 服务的参数进行优化。

7.1 优化服务器硬件

服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。针对性能瓶颈，提高硬件配置，可以提高MySQL数据库的査询、更新的速度。

(1) 配置较大的内存。足够大的内存，是提高MySQL数据库性能的方法之一。内存的速度比磁盘I/O快得多，可以通过增加系统的缓冲区容量，使数据在内存停留的时间更长，以减少磁盘 I/O。
(2) 配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。
(3) 合理分布磁盘I/O,把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
(4) 配置多处理器，MySQL是多线程的数据库，多处理器可同时执行多个线程。

7.2 优化MySQL的参数

通过优化MySQL的参数可以提高资源利用率，从而达到提高MySQL服务器性能的目的。
MySQL服务的配置参数都在my.cnf或者my.ini文件的［mysqld］组中。下面对几个对性能影响比较大的参数进行详细介绍。

key_buffer_size: 表示索引缓冲区的大小.索引缓冲区所有的线程共享.增加索引缓冲区可以得到更好处理的索引(对所有读和多重写).当然，这个值也不是越大越好，它的大小取决于内存的大小.如果这个值太大，导致操作系统频繁换页，也会降低系统性能.
table_cache: 表示同时打开的表的个数。这个值越大，能够同时打开的表的个数越多。这个值不是越大越好，因为同时打开的表太多会影响操作系统的性能。
query_cache_size: 表示查询缓冲区的大小.该参数需要和query_cache_type配合使用。当 query cache type值是0时，所有的查询都不使用查询缓冲区。但是query_cache_type=O并不会导致MySQL释放query_cache_size所配置的缓.冲区内存.当query_cache_type= 1时，所有的查询都将使用查询缓冲区，除非在查询语句中指定SQL_NO_CACHE,如SELECT SQL_NO_CACHE * FROM tbl_name.当 query_cache_type=2 时，只有在查询语句中使用 SQL_CACHE关键字，查询才会使用查询缓冲区。使用查询缓冲区可以提高查询的速度，这种方式只适用于修改操作少且经常执行相同的查询操作的情况。
sort_buffer_size : 表示排序缓存区的大小。这个值越大，进行排序的速度越快.
read_buffer_size : 表示每个线程连续扫描时为扫描的每个表分配的缓冲区的大小（字节）。当线程从表中连续读取记录时需要用到这个缓冲区。SET SESSION read bu ffer_size=n 可以临时设置该参数的值。
read_md_buffer_size : 表示为每个线程保留的缓冲区的大小，与read_buffer_size相似。但主要用于存储按特定顺序读取出来的记录。也可以用SET SESSION read_md_buffer_size=n 来临时设置该参数的值.如果频繁进行多次连续扫描，可以增加该值。
innodb_buffer_pool_size: 表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。但是这个值太大会影响操作系统的性能。
max_connections: 表示数据库的最大连接数。这个连接数不是越大越好，因为这些连接会浪费内存的资源。过多的连接可能会导致MySQL服务器僵死。
innodb_flush_log_at_trx_commit: 表示何时将缓冲区的数据写入日志文件，并且将日志文件写入磁盘中。该参数对于innoDB引擎非常重要。该参数有3个值，分别为0、1和2。值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘；值为I时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘；值为2时表示每次提交事务时将数据写入日志文件，每隔1秒将日志文件写入磁盘。该参数的默认值为1.默认值1安全性最高，但是每次事务提交或事务外的指令都需要把日志写入（flush）硬盘，是比较费时的； 0值更快一点，但安全方面比较差；2值日志仍然会每秒写入到硬盘，所以即使出现故障，一般也不会丢失超过1〜2秒的更新.
back_log: 表示在mysql暂时停止回答新请求之前的短时间内，多少个请求可以被存在堆栈中。换句话说，该值表示对到来的Tcp/ip连接的侦听队列的大小。只有期望在一个短时间内有很多连接，才需要增加该参数的值。操作系统在这个队列大小上也有限制。设定 back_log高于操作系统的限制将是无效的.
interactive timeout: 表示服务器在关闭连接前等待行动的秒数。
sort_bufTer_size: 表示每个需要进行排序的线程分配的缓冲区的大小。增加这个参数的值可以提高ORDER BY或GROUP BY操作的速度。默认数值是2 097 144 （ 2MB ）.
thread_cache_size: 表示可以复用的线程的数量。如果有很多新的线程，为了提高性能可以增大该参数的值。
wait_timeout: 表示服务器在关闭一个连接时等待行动的秒数。默认数值是28 800。
合理地配置这些参数可以提高MySQL服务器的性能。除上述参数以外，还有innodb_log_ buffer_size. innodb_log_file_size等参数。配置完参数以后，需要重新启动MySQL服务才会生效。

书香水墨

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql 性能优化

一、优化简介        优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化，一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务。本节将为读者介绍优化的基...
复制链接

扫一扫