MySQL性能优化

目录

1、优化简介

2、优化查询

2.1、分析查询语句:

2.2、索引对查询速度的影响:

2.3、使用索引查询:

2.4、优化子查询:

3、优化数据库结构

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

3.2、增加中间表:

3.3、增加冗余字段:

3.4、优化插入记录的速度:

3.5、分析表、检查表和优化表:

4、优化MySQL服务器

4.1、优化服务器硬件:

4.2、优化MySQL的参数:

5、总结:

5.1、是不是索引建立得越多越好?

5.2、为什么查询语句中的索引没有起作用?

5.3、如何使用查询缓冲区?


1、优化简介

优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。MySQL优化,一方面是找出系统的瓶颈,提高MySQL数据库整体的技能;另一方面,需要合理的结构设计和参数调整,以提高用户操作的响应速度。同时还要尽可能的节省系统资源,以便系统可以提高更大负荷服务。

MySQL数据库优化是多方面的,原则上减少系统瓶颈,减少资源的占用,增加系统的反映速度。例如,通过优化文件系统,提高磁盘的I/O读写速度;通过优化操作系统调度策略,提高MySQL在高负载情况下的负载能力;优化表结构、索引、查询语句等使查询响应更快。

在MySQL中,可以使用show status语句查询一些MySQL数据库的性能参数。show status 语句语法如下:

show status like 'value';

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

  • Connections:连接MySQL服务器的次数。
  • Uptime:MySQL服务器的上线时间。
  • Slowqueries:慢查询的次数。
  • Con_select:查询操作的次数。
  • Con_insert:插入操作的次数。
  • Con_update:更新操作的次数。
  • Con_delete:删除操作的次数。

如果查询的MySQL服务器的连接次数,可以执行如下语句:

show status like 'Connections';

如果查询MySQL服务器的慢查询次数,可以执行如下语句:

show status like 'Slow_queries';

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

2、优化查询

2.1、分析查询语句:

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

explain语句的基本语法如下:

EXPLAIN[EXTENDED] SELECT select_options;

使用explain关键字,explain语句将产生附加信息。select_options是select语句的查询选项,包括from where字句等。

执行该语句,可以分析explain后面的select语句的执行情况,并且能够分析出所查询的表的一些特征。

下面对查询结果进行解释。

  • id:select识别符。这是select的查询序列号。
  • select_type:表示select语句的类型。它可以是以下几种取值: SIMPLE表示简单查询,其中不包括连接查询和子查询。PRIMARY表示主查询,或者是最外层的查询语句。UNION表示连接查询的第2个或后面的查询语句。DEPENDENT UNION,连接查询中的第2个或后面的select语句,取决于外面的查询。UNION RESULT,连接查询的结果。SUBQUERY,子查询的第1个select语句。DEPENENT SUBQUERY,子查询中的第一个select语句,取决于外面的查询。DERIVED,导出表的select(from字句的子查询)。
  • table:表示查询的表。
  • type:表示表的连接查询。下面是安装最佳类型到最差类型的顺序给出的各种连接查询。

1> system

该表是仅有的一行的系统表。这是const连接类型的一个特例。

2> const

数据表最多只有一个匹配行,它将在查询开始时被读取,并在余下的查询优化中作为常量对待。const表查询速度很快,因为他们只读取一次。const用于使用常量值比较PRIMARY KEY或UNION索引的所有部分的场合。

3> eq_ref

对于每个来自前面的表的行组合,从该表中读取一行。当一个索引的索引 部分都在查询中使用并且索引是UNIQUE或PRIMARY KEY时,即可使用这种类型。

eq_ref可以用于使用"="操作符比较带索引的列。比较值可以为常量或一个在该表前面所读取的表的列的表达式。

4> ref

对于来自前面的表的任一行组合,将从该表中读取所有匹配的行。这种类型用于索引既不是UNION也不是PRIMARY KEY的情况,或者在查询中使用了索引列的左子集,即索引中左边的部分列组合。ref可以用于使用=或<=>操作符的带索引的列。

5> ref_or_null

该连接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行,在解决子查询中经常使用该连接类型的优化。

6> index_range

该连接查询类型表示使用了索引合并优化方法。在这种情况下,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。

10> index

该连接类型与all相同,除了只扫描索引树。这通常比all快,因为索引文件通常比数据文件小。

11> all

对于前面的表的任意行组合,进行完整的表扫描。如果表是第一个没标记的const的表,这个不好,并且在其他情况下很差。通常可以增加更多的索引来避免使用all连接。

  • possible_keys:possible_key列指出MySQL能使用哪个索引在该表中找到行。如果该列是NULL,则没有相关索引。在这种情况下,可以通过检查where子句看它是否引用某些列或适合索引的列来提高查询性能。如果是这样,可以创建适合的索引来提高查询的性能。
  • key:表示查询实际使用到的索引,如果没有选择索引,该类的值是NUL。要想强制MySQL使用或忽视possible_keys类中的索引,在查询中使用force index、use index或者ignore index。
  • key_len:表示MySQL选择的索引字段按字节计算的长度,如果键是NULL,则长度为NULL,注意通过key_len值可以确定MySQL将实际使用一个多列索引中的几个字段。
  • ref:表示使用哪个列或常数与索引一起来查询记录。
  • rows:显示MySQL在表中进行查询时必须检查的行数。
  • Extra:该列MySQL在处理查询时的详细信息。

DESCRIBE语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。describe语句的语法形式如下:

DESCRIBE SELECT select_options

DESCRIBE可以缩写成DESC。

2.2、索引对查询速度的影响:

MySQL中提高性能的一个最有效的方式就是对数据库设计合理的索引。索引提供了高效访问数据的方法,并且加快查询的速度,因此,索引对数据的速度有着至关重要的影响。使用索引可以快速地定位表中的某条记录,从而提高数据库查询的速度,提高数据库的性能。

如果查询时没有使用索引,查询语句将扫描表中的所有记录。在数据量大的情况下,这样查询的速度会很慢。如果使用索引进行查询,查询语句可以根据索引快速定位到待查询记录。从而减少查询的记录数,达到提高查询速度的的目的。

下面是查询语句中不使用索引和使用索引的对比。首先,分析未使用索引的查询情况。

可以看到,rows列是5,说明查询语句扫描了表中的5条记录。

然后,在user表的nickname字段上加上索引。执行添加索引的语句及结果如下:

现在,再分析上面的查询语句:

结果显示,rows列的值为1。这表示这个查询语句值扫描了表中的一条记录。其查询速度自然比扫描5条记录快。而且possible_keys和key的值都是index_name,这说明查询时使用了index_name索引。

2.3、使用索引查询:

索引可以提高查询的速度。但并不是使用带有索引的字段查询时,索引都会起作用。

使用索引有几种特殊情况,在这些情况下,有可能使用带索引的字段查询时,索引并没有起作用。

全表记录:

  • 使用like关键字的查询语句

在使用like关键字进行查询的查询语句中,如果匹配字符串的第一个字符为"%",索引不会起作用。只有"%"不在第一个位置,索引才会起作用。

查询语句中使用like关键字,并且匹配的字符串中含有"%"字符,explain语句执行如下:

第一个为%:

第一个不为%:

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

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

从第1条语句查询结果可以看出,id='3的记录有1条。第1条语句共扫描了1条记录,并且使用了索引index_id_price。从第2条语句查询结果可以看出,rows列的值是5,说明查询语句工扫描了16条记录,并且key列的值NULL,说明查询语句并没有使用索引。因为饿username字段时多列索引的第2个字段,只有查询条件中使用了id字段才会使index_id_username索引其作用。

  • 使用or关键字的查询语句

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

age字段上没有索引,第1条查询语句没有使用索引,总共查询了5条记录。

2.4、优化子查询:

MySQL从4.1版本开始支持子查询,使用子查询可以实现select语句的嵌套查询,即一个select语句的查询结果作为另一个select语句的条件。子查询虽然可以使查询语句更灵活,但执行效率不高。执行子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表,然后外层查询语句从临时表中查询记录。查询完毕后,再撤销这些临时表。因此,子查询的速度会受到一定的影响。

在MySQL中,可以使用连接(join)查询来替代子查询。连接查询不需要建立临时表,其速度要比子查询要快,如果查询中使用索引的话,性能会更好。连接之所以更有效,是因为MySQL不需要在内存中创建临时表来完成查询工作。

3、优化数据库结构

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

对于字段较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新的表。

3.2、增加中间表:

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

3.3、增加冗余字段:

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

表的规范化程度越高,表与表之间的关键就越多,需要连接查询的情况也越多。

3.4、优化插入记录的速度:

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

  • 禁用索引

对于非空表,插入记录时,MySQL会根据表的索引对插入的记录建立索引。如果插入大量数据,建立索引会降低插入记录的速度。为了解决这种情况,可以在插入记录之前禁用索引,在插入完毕后再开启索引。禁用索引的语句如下:

ALTER TABLE table_name DISABLE KEYS;

其中table_name是禁用索引的表的表名。

重新开启索引的语句如下:

ALTER TABLE table_name ENABLE KEYS;

对于空表批量导入数据,则不需要进行此操作,因为MyISAM引擎的表是在导入数据之后才建立索引的。

  • 禁用唯一性检查

插入数据时,MySQL会对插入的记录进行唯一性校验。这种唯一性校验也会降低插入记录的速度。为了降低这种情况对查询速度的影响。可以在插入之前禁止唯一性检查,等到记录插入完毕后再开启。禁用唯一性检查的语句如下:

SET UNIQUE_CHECKS=0;

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

SET UNIQUE_CHECKS=1;

  • 使用批量插入

插入多条记录是,可以使用一条insert语句插入一条记录;也可以使用insert语句插入多条记录,这样插入速度快。

  • 使用 load date infile 批量导入

当需要批量导入数据时,如果能用load data infile语句,就尽量使用。因为load data infile语句导入数据的速度比insert语句快。


对于innoDB引擎的表,常见的优化方法如下:

1> 禁用唯一性检查

插入数据之前执行set unique_checks=0来禁止唯一索引的检查,数据导入完成之后再执行set unique_checks=1。这个和MyISAM引擎的使用方法一样。

2> 禁用外键检查

插入数据之前执行禁止对外键的检查,数据导入完成后再恢复对外键的检查。禁用外键检查语句如下:

set foreign_key_checks=0;

恢复外键检查的语句如下:

set foreign_key_checks=1;

3> 禁止自动提交

插入数据之前禁止事务的自动提交,数据导入完成之后,执行恢复自动提交操作。禁止自动提交的语句如下:

set autocommit=0;

恢复自动提交的语句如下:

set autocommit=1;


3.5、分析表、检查表和优化表:

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

  • 分析表

MySQL提供了analyze table语句分析表,analyze table语句的基本语法如下:

analyze[local | no_write_to_binlog] table tbl_name...

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

使用analyze table分析表的过程中,数据库系统会自动对表加一个只读锁。在分析过程中,只能读取表中的记录,不能更新和插入记录。analyze table语句能够分析innoDB、BDB和MyISAM类型的表。

使用analyze来分析user表:

Table:表示分析的表的名称。

Op:表示执行的操作,analyze表示进行分析操作。

Msg_type:表示信息类型,其值通常是状态(status)、信息(info)、注意(note)、警告(warning)、(error)之一。

Msg_text:显示信息。

  • 检查表

MySQL中可以使用check table语句来检查表。

CHECK TABLE tbl_name......[option]...

option={quick | fast | medium | extended | changed}

quick:不扫描行,不检查错误的连接。

fast:只检查没有别正确关闭的表。

mudium:扫描行,以验证被删除的连接是有效的。也可以计算各行关键字校验和。

change:值检查上次检查后被更改的表和没有正确关闭的表。

extended:都每行的所有关键字进行一个全面的关键字查找。这可以确保表是100%一致的。但是花的时间较长。

option只对MyISAM类型的表有效,对innoDB类型的表无效。

  • 优化表

MySQL使用optimize table 语句优化表。该语句对innoDB和MyISAM都有效。optimize table语句只能优化表中的varchar、blob或text类型的表。

optimize [local | no_write_to_binlog] table tbl_name.......

通过optimize table语句可以消除删除和更新造成的文件碎片。

4、优化MySQL服务器

4.1、优化服务器硬件:

服务器的硬件性能直接决定着MySQL数据库的性能,硬件的性能瓶颈,直接决定MySQL数据库的运行速度和效率。

需要从以下几个方面考虑:

  1. 配置较大的内存。足够大的内存,是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多,可以增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘的IO。
  2. 配置高速磁盘,比如SSD。
  3. 合理分配磁盘IO,把磁盘IO分散到多个设备上,以减少资源的竞争,提高并行操作能力。
  4. 配置多核处理器,MySQL是多线程的数据库,多处理器可以提高同时执行多个线程的能力。

4.2、优化MySQL的参数:

通过优化MySQL的参数可以提高资源利用率,从而达到提高MySQL服务器性能的目的。

MySQL的配置参数都在my.conf或者my.ini文件的[mysqld]组中,常用的参数如下:

5、总结:

5.1、是不是索引建立得越多越好?

合理的索引可以提高查询的速度,但不是索引越多越好。在执行插入语句的时候,MySQL要为新插入的语句建立索引。所以过多的索引会导致插入操作变慢。原则上只有查询用的字段才建立索引。

5.2、为什么查询语句中的索引没有起作用?

在一些情况下,查询语句中使用了带有索引的字段。但索引并没有起作用。例如,在where条件的like关键字匹配的字符串以"%"开头,这种情况下索引是不会起作用的。又如where条件中使用or关键字连接查询条件,如果有1个字段没有使用索引,那么其他索引也不会起作用。如果使用多列索引,但没有使用多列索引中的第1个字段,那么多列索引也不会起作用。

5.3、如何使用查询缓冲区?

查询缓冲区可以提高查询的速度,但是这种方法只适用于查询语句比较多、更新语句比较少的情况。默认情况下查询缓冲区的大小为0,也就是不可用。可以修改query_cache_size以调整查询缓冲区大小;修改query_cache_type以调整查询缓冲区的类型。在my.ini中修改query_cache_size和query_cache_type的值如下所示:

[mysqlId]

query_cache_size=512M

query_cache_type=1

query_cache_type=1表示开启查询缓冲区。只有在查询语句中包含SQL_NO_CACHE关键字时,才不会使用查询 缓冲区。可以使用FLUSH QUERY CACHE来刷新缓冲区,清理查询缓冲区中的碎片。

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值