MySql性能分析 EXPLAIN

亮子zl

已于 2022-11-05 09:48:09 修改

阅读量158

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 mybatis

于 2019-06-08 14:29:10 首次发布

本文链接：https://blog.csdn.net/zhaoliang831214/article/details/91344571

版权

MySQL 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

索引创建
创建： CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
删除： DROP INDEX [indexName] ON mytable;
查看： SHOW INDEX FROM table_name\G

四种方式添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY(column_list);添加一个主键，索引必须是唯一的，不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);创建索引的值必须是唯一的（除了NULL外。NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name(column_list);添加普通索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list);全文索引。
====================================================================
MySql性能分析
EXPLAIN:1、表的读取顺序。2、数据读取操作的操作类型。2、哪些索引可以使用。3、哪些索引被实际使用。4、表之间的引用。5、每张表有多少行被优化器查询。

(ID)(查询类型)(表名)(访问类型)(可能应用索引)(实际索引)(长度)(是否常量)(行数)(包含不显示信息)

------------------------------------------------------------------

*ID

id:id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

------------------------------------------------------------------

*select_type
SIMPLE(简单select查询，查询中不包含子查询或者UNION)
PRIMARY(查询中若包含任何复杂的子部分，最处层查询则被标记为)
SUBQUERY(在select或where列表中包含了子查询)
DERIVED(在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里)
UNION(若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，处层SELECT将被标识为：DERIVED)
UNION RESULT(从UNION表获取结果的select)

------------------------------------------------------------------

*type

system ://表只有一行记录(等于系统表),这是const类型的特列，平时不会出现，这个也可以忽略

const ://表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，mysql就能将该查询转换为一个常量 explain select * from (select * from t1 where id = 1) d1;

eq_ref ://唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
explain select * from t1,t2 where t1.id=t2.id;

ref ://非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，
所以他应该属于查找和扫描的混合体。
mysql> create index idx_col1_col2 on t1(col1,col2);
           mysql> explain select * from t1 where col1='ac';

range ://只检给范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的 where语句中出现了between、< 、>、in等查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
           explain select * from t1 where id between 30 and 60;
           explain select * from t1 where id in(1,2,6);

index ://Full Index Scan, index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
（也就是说虽然ALL和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
explain select id from t1;

all ://Full Table Scan,将遍历全表找到匹配的行
最差全表扫描 select * from tb1_emp;
explain select * from t1 where column_without_index = '';
------------------------------------------------------------------

*type

访问类型
system > const > eq_ref > ref > fulltext >ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all

从最好到最差依次是：type:system->const->eq_ref->ref->range->index->ALL

possible_keys + key
possible_keys: 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key：实际使用的索引。如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len: 表示索引使用的字节数，根据这个值可以判断索引的使用情况，特别是在组合索引的时候，判断该索引有多少部分被使用到非常重要。用到的越少越好。

ref :显示了之前的表在key列记录的索引中查找值所用的列或常量

rows: 根据表统计信息及索引选用情况，大致估算出找到所需要的记录所需要读取的行数（即每张表有多少行被优化器查询），所需读取的行数越少越好。

------------------------------------------------------------------

*Extra
Using filesort(“索引内部排序重新排序” 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为“文件排序”)
Using temporary(使用临时表保存中间结里，MySQL在对查询结果排序时使用的临时表。常见于排序orderBY)
Using index(表示相应的select操作中使用了覆盖索引,避免访问了表的数据行，效率不错!如果同时出现using where，表明索引被用来执行键值的查找.
如果没有同进出现using where，表明索引用来读取数据而非执行查找动作)
Using where(表明使用了where过滤)
Using join buffer(使用了连接缓存)
impossible where（where子句的值总是false，不能用来获取任何元组）
select table optimized away()
distinct()