跳至主要內容

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的速度,故应经常处理经常操作的数据表。最后可以考虑某些表是否可以整合为一个表,若没有使用整合,则再查询过程中可以使用连接,这样若连接的列采用相同的数据类型和长度,同样可以达到优化表的目的。

上次编辑于:
贡献者: 棋.