MySQL性能分析及explain的使用说明

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，这就要看是先过滤再排序划算，还是先排序再过滤划算。

关于MySQL性能分析及explain用法的知识就介绍到这里了，希望本次的介绍能够对您有所收获！

EXPLAIN列的解释：

id:选定的执行计划中查询的序列号。表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行。id相同，执行顺序由上至下。

select_type：查询类型 说明

1、SIMPLE：简单的select查询，不使用union及子查询
2、PRIMARY：最外层的select查询
3、UNION：UNION中的第二个或随后的select查询，不依赖于外部查询的结果集
4、DEPENDENT UNION：UNION中的第二个或随后的select查询，依赖于外部查询的结果集
5、UNION RESULT： UNION查询的结果集SUBQUERY子查询中的第一个select查询，不依赖于外部查询的结果集
6、DEPENDENT SUBQUERY:子查询中的第一个select查询，依赖于外部查询的结果集DERIVED用于from子句里有子查询的情况。
MySQL会递归执行这些子查询，把结果放在临时表里。
7、UNCACHEABLE SUBQUERY:结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估
8、UNCACHEABLE UNION:UNION中的第二个或随后的select查询，属于不可缓存的子查询

table：显示这一行的数据是关于哪张表的
type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
all: full table scan ;mysql将遍历全表以找到匹配的行；
index ： index scan; index 和 all的区别在于index类型只遍历索引；
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，< ,>等查询；
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
const，system：当mysql对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，mysql就能将该查询转化为一个常量。
possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：MYSQL认为必须检查的用来返回请求数据的行数
Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。