MYSQL性能优化

  张一帆   05/10/17


优化简介

  我们可以通过show status语句查询mysql数据库的性能。

show status like 'value';

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

  • connections:连接mysql服务器的次数;
  • uptime:mysql服务器的上线时间;
  • slow_queries:慢查询的次数;
  • Com_select:查询操作的次数;
  • Com_insert:插入操作的次数;
  • Com_update:更新操作的次数;
  • Com_delete:删除操作的次数;

  mysql中存在查询InnoDB类型的表的一些参数:

  • Innodb_rows_read:select语句查询的记录数;
  • Innodb_rows_inserted:insert插入的记录数;
  • Innodb_rows_update:update更新的记录数;
  • Innodb_rows_deleted:delete删除的记录数;

  通过这些参数可以分析mysql的性能,根据分析结果,进行相应的性能优化。

优化查询

  通过对查询语句的分析,可以了解查询语句的执行情况,我们可以通过EXPLAIN和DESCRIBE来分析查询语句

explain select 语句

  结果中:

  • id:表示select语句的编号;
  • select_type:表示select语句的类型,这个参数有几个常用的取值,simple表示简单查询,其中不包括连接查询和子查询;primary表示主查询,或者是最外层的语句;union表示连接查询的第二个或后面的查询语句;
  • table:表示查询的表;
  • type:表示标的连接类型。这个参数有几个常用的取值,system表示表中只有一条记录;const表示表中有多条记录,但只从表中查询一条记录;all表示对表进行了完整的扫描;eq_ref表示多表连接时,后面的表使用了unique或者primary key;ref表示多表查询时,后面的表使用了普通索引;unique_subquery表示子查询中使用了unique或者primary key;index_subquery表示子查询中使用了普通索引;range表示查询语句中给出了查询范围;index表示对表中的索引进行了完整的扫描;
  • possible_keys:表示查询中可能使用的索引;
  • key:表示查询使用到的索引;
  • key_len:表示索引字段的长度;
  • ref:表示使用哪个列或常数于索引一起来查询记录;
  • rows:表示查询的元组个数;
  • filtered:表示按照条件过滤的元组个数 的百分比;
  • extra:表示查询过程的附件信息。这个参数有几个常用的取值,using where表示查询使用了where语句来处理的;using temporary表示使用了内部临时(基于内存)表;using filesort表示这是order by语句的结果;using index表示使用到了索引,没有直接访问整个表数据;using join buffer表示在连接查询时没有使用索引,并且需要连接缓冲区来存储中间的结果;impossible where表示where条件时没有找到符合的数据;select tables optimized away表示仅通过使用索引从聚合函数中返回一行数据;distinct表示找到一行数据后就停止了搜索;index merges表示使用了超过一个的索引时候,并以以下格式中的一个展现出来:
    • using sort_union(…)
    • using union(…)
    • using intersect(…)

以上三个的区别我会在以后的测试中展现出来。

  DESCRIBE语句(DESC)的使用方法与EXPLAIN语句是一样的。这两者的分析结果也是一样的。DESCRIBE语句的形式如:

describe select 语句;

  在使用索引进行查询的过程中,有些时候即使查询时使用的是索引,但是索引并没有起作用。所以我们需要注意到几点情况:

  • 使用like的时候,如果匹配字符串的第一个字符为%,索引是不会被使用的,如果不在第一个位置,索引就会被使用
  • 在使用多列索引时,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用
  • 如果查询语句只有OR关键字时,如果OR前后的两个条件都是索引时,查询中将使用索引。如果前后有一个条件的列不是索引,那么查询中将不使用索引

优化插入记录的速度

1.禁用索引

  插入记录时,mysql会根据表的索引对插入的记录进行排序。如果插入大量数据时,这些排序会降低插入记录的速度。为了解决这个情况,再插入记录之前应该先禁用索引。

alter table 表名 disable keys;

  当数据都被插入以后,应该重新开启索引

alter table 表名 enbale keys;

  这块知识的应用,我已经在前边的文章有了对这块知识的应用:《如何向MYSQL插入大量数据,千万级数据以及亿级数据的方法和性能》

2.禁用唯一性检查

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

set unique_checks=0;

  重新开启唯一性检查

set unique_checks=1;

3.优化insert语句

  一般在批量插入数据的时候,会优先想到用多个insert语句去插入数据。但是,其实如果将所有的数据汇聚成一条insert语句,将会减少与数据库的连接等操作。

分析表、检查表和优化表

  分析表主要作用是分析关键字的分布。

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

  检查表主要作用是检查表是否存在错误。

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

  option参数有5个参数:

  1. quick
  2. fast
  3. changed
  4. medium
  5. extended

  这5个参数的执行效率依次降低。option选项只对MyISAM类型的表有效,对Innodb类型的表无效。同时还要注意这个执行过程会给表叫上只读锁。

  优化表主要作用是消除删除或更新造成的空间浪费。

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

  如果一个表使用了TEXT或者BLOB这样的数据类型,那么更新、删除等操作就会造成磁盘空间的浪费。

优化mysql的参数

  mysql中比较重要的配置参数都在my.cnf或者my.ini文件的[mysqld]组中。下面对几个重要的参数进行详细介绍:

  • key_buffer_size:表示索引缓存的大小。这个值越大,使用索引进行查询的速度越快。
  • table_cache:表示同时打开的表的个数。这个值越大,能够同时同时打开的表的个数越多。这个值不是越大越好,因为同时打开的表太多会影响操作系统的性能。
  • query_cache_size:表示查询缓存区的大小。使用查询缓存区可以提高查询的速度,这种方式只适用于修改操作少且经常执行相同的查询操作的情况;其默认值为0.当取值为2时,只有select语句中使用了sql_cache关键字,查询缓存区才会使用。例如,select sql_cache * from score
  • query_cache_type:表示查询缓存区的开启状态。其取值为0时表示关闭,取值为1时表示开器,取值为2时表示按要求使用查询缓存区。
  • max_connections:表示数据库的最大连接数。这个连接数不是越大越好,因为这些连接会浪费内存的资源。
  • sort_buffer_size:表示排序缓存区的大小。这个值越大,进行排序的速度越快。
  • read_buffer_size:表示为每个线程保留的缓冲区的大小。当线程需要从表中连续读取记录时需要用到这个缓冲区。set session read_buffer_size=n可以临时设置该参数的值。
  • read_rnd_buffer_size:表示为每个线程保留的缓冲区的大小,与read_buffer_size相似。但主要用于存储按特定顺序读取出来的记录。也可以用set session read_rnd_buffer_size=n来临时设置该参数的值。
  • innodb_buffer_pool_size:表示innodb类型的表和索引的最大缓存。这个值越大,查询的速度就会越快。但这个值太大了会影响操作系统的性能。
  • innodb_flush_log_at_trx_commit:表示何时缓冲区的数据写入日志文件,并且将日志文件写入磁盘中。该参数有3个值,分别是0、1和2.值为0时表示每隔1秒将数据写入日志文件并将日志文件写入磁盘;值为1时表示每次提交事务时将数据写入日志文件并将日志文件写入磁盘;值为2时表示每次提交事务时将数据写入日志文件,每隔1秒将日志文件写入磁盘。该参数的默认值为1.这个默认值是最全最合理的。

  合理的配置这些参数可以提高mysql服务器的性能。除以上参数外,还有innodb_log_buffer_size、innodb_log_file_size等参数。如果想要了解更多的参数的话,应该去手册中进行查看。

本文参考: