MySQL-性能优化_mysql <> 性能优化

---

前言

性能优化是通过某些有效的方法提高mysql数据库的性能。性能优化的目的是为了使mysql数据库运行速度更快、占用的磁盘空间更小。性能优化包括很多方面例如优化查询速度、优化更新速度和优化mysql服务器等。

一、优化简介

优化mysql数据库是数据库管理员的必备技能。通过不同的优化方式达到提高mysql数据库性能的目的。

数据库管理员可以使用show status语句查询mysql数据库的性能。语法形式如下

show status like 'value';

其中value参数是常用的几个统计参数。这些常用参数介绍如下。

connections连接mysql服务器的次数
uptimemysql服务器的上线时间
slow_queries慢查询的次数
com_select查询操作的次数
com_insert插入操作的次数
com_update更新操作的次数
com_delete删除操作的次数。

二、优化查询

查询是数据库中最频繁的操作。提高了查询速度可以有效的提高mysql数据库的性能。

1、分析查询语句

通过对查询语句的分析可以了解查询语句的执行情况。mysql中可以使用explain语句和describe语句来分析查询语句。

explain语句的基本语法如下

explain select 语句

通过explain关键字可以分析后面的select语句的执行情况。并且能够分析出所查询的表的一些内容。

2、索引对查询速度的影响

索引可以快速的定位表中的某条记录。使用索引可以提高数据库查询的速度从而提高数据库的性能。

如果查询时不使用索引查询语句将查询表中的所有字段。这样查询的速度会很慢。如果使用索引进行查询查询语句只查询索引字段。这样可以减少查询的记录数达到提高查询速度的目的。

3、使用索引查询

索引可以提高查询的速度。但是有些时候即使查询时使用的是索引但索引并没有起作用。

1、查询语句中使用like关键字

在查询语句中使用like关键字进行查询时如果匹配字符串的第一个字符为“%”时索引不会被使用。如果“%”不是在第一个位置索引就会被使用。

2、查询语句中使用多列索引

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

3、查询语句中使用or关键字

查询语句只有or关键字时如果or前后的两个条件的列都是索引时查询中将使用索引。如果or前后有一个条件的列不是索引那么查询中将不使用索引。

4、优化子查询

很多查询中需要使用子查询。子查询可以使查询语句很灵活但子查询的执行效率不高。子查询时mysql需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕后mysql需要撤销这些临时表。因此子查询的速度会受到一定的影响。如果查询的数据量比较大这种影响就会随之增大。在mysql中可以使用连接查询来替代子查询。连接查询不需要建立临时表其速度比子查询要快。

三、优化数据库结构

数据库结构是否合理需要考虑是否存在冗余、对表的查询和更新的速度、表中字段的数据类型是否合理等多方面的内容。

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

有些表在设计时设置了很多的字段。这个表中有些字段的使用频率很低。当这个表的数据量很大时查询数据的速度就会很慢。

对于这种字段特别多且有些字段的使用频率很低的表可以将其分解成多个表。

2、增加中间表

有时需要经常查询某两个表中的几个字段。如果经常进行联表查询会降低mysql数据库的查询速度。对于这种情况可以建立中间表来提高查询速度。

3、增加冗余字段

设计数据库表时尽量让表达到三范式。但是有时为了提高查询速度可以有意识地在表中增加冗余字段。

4、优化插入记录的速度

插入记录时索引、唯一性校验都会影响到插入记录的速度。而且一次插入多条记录和多次插入记录所耗费的时间是不一样的。根据这些情况分别进行不同的优化。

1、禁用索引

插入记录时mysql会根据表的索引对插入的记录进行排序。如果插入大量数据时这些排序会降低插入记录的速度。为了解决这种情况在插入记录之前先禁用索引。等到记录都插入完毕后再开启索引。禁用索引的语句如下

alter table 表名 disable keys;

重新开启索引的语句如下

alter table 表名 enable keys;

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

2、禁用唯一性检查

插入数据时mysql会对插入的记录进行唯一性校验。这种校验也会降低插入记录的速度。可以在插入记录之前禁用唯一性检查。等到记录插入完毕后再开启。禁用唯一性检查的语句如下

set unique_checks=0;

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

set unique_checks=1;

3、优化insert语句

插入多条记录时可以采取两种写insert语句的方式。第一种是一个insert语句插入多条记录。

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

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

1、分析表

mysql中使用analyze table语句来分析表该语句的基本语法如下

analyze table 表名1 [,表名2...]

使用analyze table分析表的过程中数据库系统会对表加一个只读锁。在分析期间只能读取表中的记录不能更新和插入记录。analyze table语句能够分析innodb和myisam类型的表。

2、检查表

mysql中使用check table语句来检查表。check table语句能够检查innodb和myisam类型的表是否存在错误。而且该语句还可以检查视图是否存在错误。该语句的基本语法如下

check table 表名1 [,表名2...][option];

其中option参数有5个参数分别是quick、fast、changed、medium和extended。这5个参数的执行效率依次降低。option选项只对myisam类型的表有效对innodb类型的表无效。check table语句在执行过程中也会给表加上只读锁。

3、优化表

mysql中使用optimize table语句来优化表。该语句对innodb和myisam类型的表都有效。但是optilmize table语句只能优化表中的varchar、blob或text类型的字段。optilmize table语句的基本语法如下

optimize table 表名1 [,表名2...];

通过optimize table语句可以消除删除和更新造成的磁盘碎片从而减少空间的浪费。optimize table语句在执行过程中也会给表加上只读锁。

四、优化mysql服务器

优化mysql服务器可以从两个方面来理解。一个是从硬件方面来进行优化另一方面是从mysql服务的参数进行优化。通过这些优化方式可以提供mysql的运行速度。但是这部分的内容很难理解一般只有专业的数据库管理员才能进行这一类的优化。

1、优化服务器硬件

服务器的硬件性能直接决定者mysql数据库的性能。例如增加内存和提高硬盘的读写速度可以提高mysql数据库的查询、更新的速度。

2、优化mysql的参数

内存中会为mysql保留部分的缓存区。这些缓冲区可以提高mysql数据库的处理速度。缓存区的大小都是在mysql的配置文件中进行设置的。

五、总结

这里的相关内容还没有整理完毕文章后面持续更新建议收藏。

文章中涉及到的命令大家一定要像我一样每个都敲几遍只有在敲的过程中才能发现自己对命令是否真正的掌握了。

可以微信搜索【小奇JAVA面试】第一时间阅读回复【资料】获取福利回复【项目】获取项目源码回复【简历模板】获取简历模板回复【学习路线图】获取学习路线图。