Mysql 语句性能优化

2017-04-22  本文已影响0人  _Henry_

主要从一下两点去优化

  1. explain mysql(mysql语句解释)
  2. slow query log(慢查询日志)

explain mysql

结果集中重要性比较高的几个关键字
1.select_type
select类型,它有以下几种值
1.1simple 它表示简单的select,没有union和子查询
1.2 primary 最外面的select,在有子查询的语句中,最外面的select查询就是primary,上图中就是这样
1.3 union union语句的第二个或者说是后面那一个.现执行一条语句,explain select * from uchome_space limit 10 union select * from uchome_space limit 10,10

2.type
连接类型。有多个参数,先从最佳类型到最差类型介绍 重要且困难,最好到最差的连接类型为const、eq_reg、ref、range、index和ALL.
2.1 system
表仅有一行,这是const类型的特列,平时不会出现,这个也可以忽略不计
2.2 const
表最多有一个匹配行,const用于比较primary key 或者unique索引。因为只匹配一行数据,所以很快
记住一定是用到primary key 或者unique,并且只检索出两条数据的 情况下才会是const,看下面这条语句
explain SELECT * FROM `asj_admin_log` limit 1,
结果是


虽然只搜索一条数据,但是因为没有用到指定的索引,所以不会使用const.继续看下面这个
explain SELECT * FROM `asj_admin_log` where log_id = 111

log_id是主键,所以使用了const。所以说可以理解为const是最优化的。
2.3 eq_ref

对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

2.4 ref
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。

2.5 index

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

当查询只使用作为单索引一部分的列时,MySQL可以使用该联接类型。

2.6 all
对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。
...

** 3.keys **
MYSQL使用的索引,简单且重要
** 4.rows **
显示MYSQL执行查询的行数,简单且重要,数值越大越不好,说明没有用好索引

slow query log (慢查询日志)

slow_query_log :是否开启慢查询日志,1表示开启,0表示关闭。
log-slow-queries :旧版(5.6以下版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file:新版(5.6及以上版本)MySQL数据库慢查询日志存储路径。可以不设置该参数,系统则会默认给一个缺省的文件host_name-slow.log
long_query_time :慢查询阈值,当查询时间多于设定的阈值时,记录日志。
log_queries_not_using_indexes:未使用索引的查询也被记录到慢查询日志中(可选项)

修改my.cnf文件,增加或修改参数slow_query_log 和slow_query_log_file后,然后重启MySQL服务器,如下所示
slow_query_log =1
slow_query_log_file=/tmp/mysql_slow.log

日志分析工具 ** mysqldumpslow **
在生产环境中,如果要手工分析日志,查找、分析SQL,显然是个体力活,MySQL提供了日志分析工具mysqldumpslow。

-t, 是top n的意思,即为返回前面多少条的数据;
-g, 后边可以写一个正则匹配模式,大小写不敏感的;
比如
得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

上一篇下一篇

猜你喜欢

热点阅读