文档章节

mysql 性能分析及explain用法

谢思华
 谢思华
发布于 2017/06/27 15:13
字数 1218
阅读 30
收藏 0

1 使用explain语句去查看分析结果,如
  explain select * from test1 where id=1;
会出现:
id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列

其中,
type=const表示通过索引一次就找到了,
key=primary的话,表示使用了主键 
type=all,表示为全表扫描,
key=null表示没用到索引;
type=ref,因为这时认为是多个匹配行,在联合查询中,一般为REF


2 MYSQL中的组合索引
假设表有id,key1,key2,key3,把三者形成一个组合索引,则
如:
   where key1=....
   where key1=1 and key2=2
   where key1=3 and key3=3 and key2=2
根据最左原则,这些都是可以使用索引的哦

   from test where key1=1 order by key3
用explain分析的话,只用到了normal_key索引,但只对where子句起作用,而后面的order by需要排序


3 使用慢查询分析:
在my.ini中:
long_query_time=1
log-slow-queries=d:\mysql5\logs\mysqlslow.log
把超过1秒的记录在慢查询日志中
可以用mysqlsla来分析之。也可以在mysqlreport中,有如
DMS 分别分析了select ,update,insert,delete,replace等所占的百份比

4 MYISAM和INNODB的锁定
myisam中,注意是表锁来的,比如在多个UPDATE操作后,再SELECT时,会发现SELECT操作被锁定了,必须等所有UPDATE操作完毕后,再能SELECT 
innodb的话则不同了,用的是行锁,不存在上面问题。
 
5 MYSQL的事务配置项
innodb_flush_log_at_trx_commit=1
表示事务提交时立即把事务日志写入磁盘,同时数据和索引也更新

innodb_flush_log_at_trx_commit=0
事务提交时,不立即把事务日志写入磁盘,每隔1秒写一次
innodb_flush_log_at_trx_commit=2
事务提交时,立即写入磁盘文件(这里只是写入到内核缓冲区,但不立即刷新到磁盘,而是每隔1秒刷新到盘,同时更新数据和索引 


explain用法

EXPLAIN tbl_name
或:
EXPLAIN [EXTENDED] SELECT select_options

前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,而今天要讲述的重点是后者。

举例
mysql> explain select * from event;
+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+
1 row in set (0.00 sec)

各个属性的含义
id
select查询的序列号

select_type
select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。

table
输出的行所引用的表。

type
联合查询所使用的类型。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。

possible_keys
指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。

key
显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。

key_len
显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

ref
显示哪个字段或常数与key一起被使用。

rows
这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。

Extra
如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
如果此信息显示Using filesort或者Using temporary的话会很吃力,WHERE和ORDER BY的索引经常无法兼顾,如果按照WHERE来确定索引,那么在ORDER BY时,就必然会引起Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

常见的一些名词解释
Using filesort
MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

Using index
从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

Using temporary
为了解决查询,MySQL需要创建一个临时表来容纳结果。

ref
对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取

ALL
完全没有索引的情况,性能非常地差劲。

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

SIMPLE
简单SELECT(不使用UNION或子查询) 

© 著作权归作者所有

谢思华
粉丝 77
博文 221
码字总数 152858
作品 0
广州
程序员
私信 提问
MySql性能加速分析以及PHPMYADMIN中explain用法

现在和大家分享下如何对MySql性能加速分析以及PHPMYADMIN中explain用法的分析。 1 使用explain语句去查看分析结果,如 explain select * from test1 where id=1; 会出现: id selecttype ta...

prestashop
2012/08/21
277
0
explain用法(转)

explain用法 EXPLAIN tblname或:EXPLAIN [EXTENDED] SELECT selectoptions 前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,而今天要讲述的重点是后者。 举例 mysql>...

年少爱追梦
2016/08/09
22
0
十、MySQL的SQL优化之定位SQL的问题 - 系统的撸一遍MySQL

找出SQL慢的原因 谈到MySQL不得不说的就是大家都会遇到的性能问题,今天就记录一下SQL优化相关的技巧。 检查MySQL状态 通过检查SQL语句的状态有助于MySQL的优化,首先介绍 show status命令 ...

logbird
2016/11/04
492
0
mysql中explain的用法

mysql中explain的用法 最近在做性能测试中经常遇到一些数据库的问题,通常使用慢查询日志可以找到执行效果比较差的sql,但是仅仅找到这些sql是不行的,我们需要协助开发人员分析问题所在,这...

震旦
2014/03/27
33
0
MySQL性能分析及explain的使用(转)

1、使用explain语句去查看分析结果,如 explain select * from test1 where id=1; 会出现: 其中 type=const表示通过索引一次就找到了 key=primary的话,表示使用了主键 type=all表示为全表扫...

easonjim
2017/12/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

centos7 安装 mysql5.7 版本(全)

centos 安装 版本说明 :centos7,mysql5.7 ,不是 centos7 可能有些命令不兼容 安装 mysql-server # 下载并安装 mysql yum wget -i -c http://dev.mysql.com/get/mysql57-community-relea......

sanri1993
39分钟前
4
0
Spring3.x升级到Spring4.x-5.x时关于MappingJacksonHttpMessageConverter的报错问题

在Spring4.x或者以上的版本强使用(不然会报错): org.springframework.http.converter.json.MappingJackson2HttpMessageConverter 如果是Spring4.0获者以下的版本可以使用MappingJacksonH...

code-ortaerc
41分钟前
4
0
OSG 渲染状态污染到其它节点怎么解决?

在根节点补上初始状态

洛克人杰洛
43分钟前
4
0
grid 布局 设置行列间距

本文转载于:专业的前端网站➪grid 布局 设置行列间距 <!DOCTYPE html><html lang="zh"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="widt......

前端老手
55分钟前
4
0
spring-data-elasticsearch 和 Jackson 配合使用的bug

下面先简单描述项目。 项目依赖: dependencies { implementation group: 'org.springframework.boot', name: 'spring-boot-starter-data-elasticsearch', version: '2.1.0.RELEASE'......

Landas
57分钟前
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部