跳至主要內容

MySQL性能优化

棋大约 14 分钟约 4104 字

性能优化是指通过某些有效的方法提升MySQL数据库的性能。性能优化的目的是使MySQL数据运行速度更快、占用的磁盘空间更小。性能优化有很多方面，如优化查询速度、优化更新速度和优化MySQL服务器等。当表中的记录很少时，MySQL的性能差别很小，只有在大量用户使用、记录很大、运行时间很长时，MySQL的性能差别才开始体现出来。若用户在查询或者添加数据时因为MySQL的性能很差而导致响应速度变慢，用户很难喜欢这个网站。因此为了提高MySQL的性能，就要对MySQL进行性能优化。如若大量用户想要进行查询操作，就要对查询语句进行优化；若大量用户想要进行更新操作，就要对更新语句进行优化。下面介绍各种情况如何进行优化。

优化概述

分析MySQL数据库的性能

数据库管理员可以使用SHOW STATUS语句查询MySQL数据库的性能，语法如下。

SHOW STATUS LIKE 'value 参数';

其中，value参数是常用的几个统计参数，具体介绍如下。

  1. Connections：连接MySQL服务器的次数。
  2. Uptime：MySQL服务器的上线时间。
  3. Show_queries：慢查询次数。
  4. Com_select：查询操作的次数。
  5. Com_insert：插入操作的次数。
  6. Com_delete：删除操作的次数。

MySQL中存在查询InnoDB类型的表的一些参数。例如，Innodb_rows_read参数表示SELECT语句查询的记录数；Innodb_rows_inserted参数表示INSERT语句的插入数；Innodb_rows_updated参数表示UPDATE语句更新的记录数；Innodb_rows_deleted参数表示DELETE语句删除的记录数。

如我们查询MySQL服务器的连接数。

show status like 'Connections';
运行结果
运行结果

运行结果如上图，通过这些结果，我们可以知晓什么语句使用得多，如使用SELECT语句的次数大于INSERT语句的次数，我们就可以通过多使用索引来提升查询的速度，而因为插入表的次数少，也不会有太大影响。

使用profile工具分析语句消耗性能

在MySQL命令窗口中，使用SELECT语句查询到记录后，最后一行会显示查询的时间，精度为秒，小数点后两位，若要知晓精度更高的查询速度，可以通过开启profile工具实现。MySQL8.0安装后profile工具是默认不开启的。MySQL是通过profilig参数标记profile工具是否开启的，查看方法如下。

SHOW VARIABLES LIKE '%pro%';

运行结果如下图，可以看到，现在的profile工具处于关闭状态。

运行结果
运行结果

开启profile工具的语法如下。

SET profiling=1;

运行结果如下。

运行结果
运行结果

开启profile工具后，我们就可以通过先执行SELECT语句，再使用profile工具查看执行此语句消耗的时间。语法如下。

select * from db_stu.tb_stu;
show profiles;
运行结果
运行结果

红色框内就是此语句执行的时间，字段Duration表示本次查询耗费的时间，单位为秒。

优化查询

分析查询语句

可使用关键字EXPLAIN分析查询语句，语法如下。

EXPLAIN SELECT 语句;

其中SELECT 语句就是我们平时使用的查询语句，例如下面的例子。

explain select * from db_stu.tb_stu;
运行结果
运行结果

其中主要的字段代表意义如下。

  • id：指出在整个查询中SELECT的位置。
  • table：查找的表名。
  • type：连接类型，该列中存储许多值，范围从const到ALL。
  • possible_keys：指出为了提高查找速度，可以使用的索引。
  • keys：指出实际使用的键。
  • rows：指出MySQL需要在相应表中返回查询结果所检验的行数，为了得到总行数，MySQL必须扫描处理整个查询，再乘以每个表的行值。
  • Extra：包含一些其他信息，如MySQL如何设计查询。

MySQL也支持使用关键字DESCRIBE分析查询，语法就是把EXPLAIN换成DECRIBE，且DESCRIBE可简写成DESC。

索引对查询速度的影响

下面我们通过使用索引和不使用索引，分别分析两次查询然后对比来观察查询的优化情况。

首先我们先查看一下数据表的定义语句以及数据。

运行结果
运行结果

可以看到表中字段price加了索引，其他列无索引。下面开始对比。

首先分析不使用索引的查询情况，我们查询bookname='MySQL从入门到精通'的记录，语法如下。

explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';
运行结果
运行结果

可以看到表中总共查询了6行记录，接下来我们为字段bookname添加索引，并且再查询一次，语法如下。

create index index_bookname on tb_bookinfo;
explain select * from tb_bookinfo where bookname = 'MySQL从入门到精通';
运行结果
运行结果
运行结果
运行结果

可以看到此时只查询了两行，比不使用索引少查询了4 行，所以在查询操作时使用索引不仅可以提高查询效率，还可以降低服务器开销。

使用索引查询

虽然索引可以提高查询的速度，但并不能充分发挥其作用，因此可以通过关键字或者其他方式优化查询处理。

应用关键字like优化索引查询

应用EXPLAIN语句执行以下查询。

explain select * from tb_bookinfo where bookname like '%Java Web';
运行结果
运行结果

可以看到查询的行数仍为6条，这是因为若匹配字符串第一个字符就是百分号“%”，则索引不会被使用，若“%”不在匹配字符串的第一位，则索引会被正常使用。

如我们再次使用查询，语法如下。

explain select * from tb_bookinfo where bookname like 'Java Web%';
运行结果
运行结果
查询语句中使用多列索引

多列索引是在表的多个字段上创建一个索引。只有在查询条件中使用了这些字段中的一个字段时，索引才会被正常使用。

应用多列索引在表的多个字段中创建一个索引，其命令如下。

CREATE INDEX index_name ON table_name (col_name1,[col_name2...]);

如拿此语句：create index index_name on table_name (c1,c2);来说，在应用字段c2时，索引不能正常使用。这就意味着索引并未在MySQL优化中起到任何作用，故必须使用第一字段c1，索引才可以被正常使用。具体可看下图。

运行结果
运行结果
查询语句中使用关键字OR

在MySQL中，查询语句包含OR关键字时，要求查询的两个字段必须同为索引，若所搜索的字段有一个不为索引，则在查询中不会应用索引进行查询。下面我们通过一个实例来对比使用索引和不使用索引的性能差异。

  1. 首先查看tb_bookinfo表的结构。我们发现字段price有索引，我们把此索引删除。

    运行结果
    运行结果

  2. 然后我们使用EXPLAIN语句分析使用OR关键字查询，语法如下。

    explain select * from tb_bookinfo 
where bookname = 'MySQL从入门到精通' or price = 89;
    运行结果
    运行结果

    可以看到它扫描了整个表（6行），并且一个索引也没有使用。

  3. 我们重新添加索引，然后再次查找，语法如下。

    create index index_price on tb_bookinfo (price);
explain select * from tb_bookinfo 
where bookname = 'MySQL从入门到精通' or price = 89\G
    运行结果
    运行结果

    可以看到它成功使用了两个索引，且共扫描了4行。

优化数据库结构

数据库结构是否合理，需要考虑是否存在冗余，对表的查询和更新速度如何、表中字段的数据类型是否合理等多方面的内容。本节将介绍优化数据库结构的方法。

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

有些表在设计时设置了就包含很多字段，其中有的字段使用频率很低，那么当这个表的数据量很大时，查询数据的速度就会变慢，对于这种字段特别多的表，就可以将使用频率低的字段分解成多个新的表。

增加中间表

有的时候经常查询两个表中的好几个字段，若经常进行连表查询，会降低MySQL的查询速度。这种情况下，可以建立中间表来提高查询速度。我们需要先分析最可能同时查询表中的哪几个字段，然后将这些字段建立为一个中间表，并将原来的几个数据插入中间表中，之后就可以使用中间表进行查询和统计。

如我们查看以下学生表tb_student和班级表tb_classes的结构，结果如图。

运行结果
运行结果

实际应用中，我们常常查询学号、姓名和班级。根据这个情况，可以创建一个temp_student表，temp_student表中存储三个字段，分别是id、name和classname。创建以及插入记录的语句如下。

create table temp_student(
id int not null,
    name varchar(50) not null,
    classname varchar(45)
);
insert into temp_student select s.id,s.name,c.name from tb_student as s,tb_classes as c where s.class_id = c.id;
select * from temp_student;
运行结果
运行结果

优化插入记录的速度

索引、唯一性检验都会影响插入数据的速度，是因为每次都要进行排列以及检验，而且一次插入和多次插入的速度是不一样的。

禁用索引

若索引开启，则插入数据的速度会变慢，若插入大量数据时应先关闭索引，待插入完成后再次开启索引，关闭索引的语法如下。

ALTER TABLE table_name DISABLE KEYS;

重新开启索引的方法如下。

ALTER TABLE table_name ENABLE KEYS;

对于新创建的表，可以先不创建索引，等到记录都导入以后再创建索引，这样可以提高插入数据的速度。

禁用唯一性检查

唯一性检查开启时，MySQL会对插入的数据进行检验，这也会降低插入记录的速度。可以在插入记录前禁用唯一性检查，待记录完成后再开启，禁用唯一性检查的语法如下。

SET UNIQUE_CHECKS = 0;

重新开启唯一性检查的语法如下。

SET UNIQUE_CHECKS = 1;

优化INSERT语句

在插入多条语句时，若要插入多条记录，则将这些数据放在一条INSERT语句中的方式要比多条INSERT语句，每条INSERT语句只包含一个记录的方式要快。如下面。

insert into tb values (v1),(v2),...  -- 方式一

insert into tb values v1;  -- 方式二
insert into tb values v2;
...

在上面的例子中，方式一就比方式二快，因为方式一不需和数据库进行多次连接。

当插入大量数据时，建议使用一个INSERT语句插入多条记录的方式。而且若能够使用LOAD DATA INFILE语句就尽量使用该语句，因为该语句比INSERT语句更快。

分析表、检查表和优化表

分析表的主要作用是分析关键字的分布。检查表的主要作用是检查表是否存在错误。优化表的主要作用是消除删除或更新操作带来的空间浪费。

分析表

MySQL中使用ANALYZE TABLE语句来分析表，该语句的语法如下。

ANALYZE TABLE table_name1[,table_name2,...];

使用ANALYZE TABLE语句分析表的过程中，数据库系统会给表添加一个只读锁。在分析期间，只能读取表中的记录，不能更新和插入记录。ALALYZE TABLE语句能分析InnoDB和MyISAM类型的表。如我们分析tb_bookinfo表，语法如下。

analyze table tb_bookinfo;
运行结果
运行结果

其中Table字段表示表的名称；op是operation的缩写，代表执行的操作，analyze代表分析操作，check代表检查查找，optimize代表进行优化操作；Msg_type代表信息类型，其显示的值通常是状态、警告、错误、信息中的一个；Msg_text显示信息。

检查表和优化表之后也会出现这4列信息。

检查表

MySQL使用CHECK TABLE语句检查表。CHECK TABLE语句可以检查InnoDB、MyISAM类型的表是否存在错误，而且还可以检查视图。该语句的语法如下。

CHECK TABLE table_name1[,table_name2,...][option];

其中，参数option有五个可选项：QUICK、FAST、CHANGED、MEDIUM和EXTENTED。这五个选项的执行效率依次降低。option选项只对MyISAM表有效，对InnoDB表无效。CHECK TABLE语句在执行时也会给表添加只读表。

优化表

MySQL中使用OPTIMIZE TABLE语句来优化表。OPTIMIZE TABLE语句对InnoDB和MyISAM表都有效。但是该语句只能优化表中的VARCHAR、BLOB和TEXT字段。OPTIMIZE TABLE语句的语法如下。

OPTIMIZE TABLE table_name1[,table_name2,...];

通过此语句可以消除删除和更新造成的磁盘碎片，从而减少空间的浪费。OPTIMIZE TABLE语句执行时也会给表添加只读表。

若一个表使用了TEXT或BLOB这样的数据类型，那么更新、删除等操作就会造成磁盘空间的浪费，因为更新和删除后，以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句就可以将这些磁盘碎片整理出来，以便以后再次利用。

优化多表查询

在MySQL中，用户可以通过连接来实现多表查询，在查询过程中，用户将一个表中的一个或多个共同字段进行连接，定义查询条件，返回统一的查询结果。这通常用来建立RDBMS常规表的关系。在多表查询中，可以应用子查询来进行优化，即在SELECT语句中嵌套SELECT语句。采用子查询优化多表查询的好处有很多，其中可以将分步查询的结果整合为一个查询，这样就不需要进行多次查询，提高查询的效率。

下面通过一个实例演示如何优化多表查询，如我们查询属于“一年三班”的全部学生姓名，其中学生信息表在tb_student表中，班级名称在tb_classes表中。第一个查询语句是使用连接查询，第二个查询语句是使用子查询。

set profiling = 1;
select s.name from tb_student as s,tb_classes as c
where s.class_id = c.id and c.name = '一年三班';
select name from tb_student as s 
where s.class_id = (select id from tb_classes where name = '一年三班');
show profiles\G
运行结果
运行结果

可以看到，使用子查询的速度要比使用连接查询的速度快。

优化表设计

在MySQL数据库中，提高查询速度还需要思考其他方法。首先，在设计数据表时应优先考虑使用特定长度，后考虑使用变长字段，如在创建数据表时，考虑将某个字段类型设为VARCHAR且长度为255，但是在实际应用时所存储的数据并不能达到这个长度，就造成了过多列宽，不仅浪费资源，也会降低数据表的查询效率。适当调整列宽可以减少磁盘占用空间，还可以在数据处理时使数据的I/O过程减少。将字段尽可能地设计成其可能长度的最大值以充分优化查询效率。改善性能的另一个方法就是使用OPTIMIZE TABLE命令处理用户经常操作的表，频繁操作数据库中的特定表会产生许多磁盘碎片，降低MySQL的速度，故应经常处理经常操作的数据表。最后可以考虑某些表是否可以整合为一个表，若没有使用整合，则再查询过程中可以使用连接，这样若连接的列采用相同的数据类型和长度，同样可以达到优化表的目的。

上次编辑于:
贡献者: QI
2024 © ImQi1