012--Explain实战详解：Extra分析

Paste_Image.png

Extra分析：包含不在其他属性显示，但是又非常重要的信息

1.Using FileSort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，即MySQL无法使用索引完成的排序称为"文件排序"

2.Using temporary：使用了临时表来保存中间结果，MYSQL在对查询结果进行排序的时候使用了临时表，常见于排序OrderBy 和分组查询GroupBy

Using FileSort只是不能按照索引方法进行排序，但是Using temporary会创建一张临时表，将缓存数据存放在临时表中，然后再删除临时表，操作变得更凶险了

3.Using Index：

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错
如果同时出现Using Where，表明索引被用来执行索引键值的查找
如果没有同时出现Using Where，表明索引用来读取数据而非执行查找工作

4.覆盖索引：

select 查询的数据列只用ongoing索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说：索引列要被所建的索引覆盖

如果想使用覆盖索引，在select的时候不能使用select * from t，具体写出对应的字段

5.Using Where：使用where过滤条件

6.Using Join Buffer：使用了连接缓存（join太多个表，配置文件里面的JoinBuffer的值可以调大一点）

7.Impossible Where：where子句的值总是false，不能获取任何元组

8.Select tables optimized away：在没有GroupBy子句的情况下，基于索引优化Min/Max操作或者对于MyISAM存储引擎优化Count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

9.distinct：优化distinct操作，在找到第一个匹配的元组后即停止找同样值的动作

1.主键自动创建唯一索引
2.频繁作为查询的条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不应该创建索引，因为每次更新不单单是更新了记录，还会更新索引，非常耗时
5.where条件中用不到的字段不适合创建索引
6.单键、组合索引的选择问题，（在高并发下倾向于创建组合索引）
7.查询中排序的字段，排序字段若通过索引去访问将大大的提高排序速度
8.查选中统计或分组的字段

索引和排序是有关系的：建完索引之后，排序也要按照索引建立的顺序进行排序，要不然会造成索引失效现象

1.1（A）没有创建关联索引

mysql>  explain SELECT * from t4 ORDER BY t4.id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | t4    | index | NULL          | PRIMARY | 4       | NULL |    8 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.id,t4.`name`;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t4    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.id,t4.`name`,t4.age;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t4    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

1.1（B）关联索引

mysql> explain SELECT * from t4 ORDER BY t4.id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | t4    | index | NULL          | PRIMARY | 4       | NULL |    8 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.id,t4.`name`;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | t4    | index | NULL          | index_id_name_age | 42      | NULL |    8 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.id,t4.`name`,t4.age;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
|  1 | SIMPLE      | t4    | index | NULL          | index_id_name_age | 42      | NULL |    8 | Using index |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-------------+
1 row in set (0.00 sec)

♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟　♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟

1.2（A）没有创建关联索引

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t4    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.id,t4.age;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t4    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.age,t4.id;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t4    | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

1.2（B）创建关联索引

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.id;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t4    | index | NULL          | index_id_name_age | 42      | NULL |    8 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.id,t4.age;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t4    | index | NULL          | index_id_name_age | 42      | NULL |    8 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4 ORDER BY t4.`name`,t4.age,t4.id;
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t4    | index | NULL          | index_id_name_age | 42      | NULL |    8 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+-------------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟　♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟

1.3（A）没有创建关联索引

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id,t4.`name`;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id,t4.`name`,t4.age;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

1.3（B）创建关联索引

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id,t4.`name`;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.id,t4.`name`,t4.age;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟　♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟

1.4（A）没有创建关联索引

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.id;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.id,t4.age;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.age,t4.id;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

1.4（B）创建关联索引

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.id;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.id,t4.age;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain SELECT * from t4  where t4.id = '1' ORDER BY t4.`name`,t4.age,t4.id;
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys             | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | t4    | const | PRIMARY,index_id_name_age | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

2.创建索引代码

CREATE INDEX index_id_name_age ON t4 (id,`name`,age)

♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟　♚　♛　♝　♞　♜　♟　♔　♕　♗　♘　♖　♟

最后编辑于：2017.12.10 04:23:10

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 228,461评论 6赞 532
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 98,538评论 3赞 417
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 176,423评论 0赞 375
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 62,991评论 1赞 312
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 71,761评论 6赞 410
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 55,207评论 1赞 324
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 43,268评论 3赞 441
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 42,419评论 0赞 288
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 48,959评论 1赞 335
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 40,782评论 3赞 354
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 42,983评论 1赞 369
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 38,528评论 5赞 359
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 44,222评论 3赞 347
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 34,653评论 0赞 26
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 35,901评论 1赞 286
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 51,678评论 3赞 392
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 47,978评论 2赞 374

012--Explain实战详解：Extra分析

1.Using FileSort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，即MySQL无法使用索引完成的排序称为"文件排序"

2.Using temporary：使用了临时表来保存中间结果，MYSQL在对查询结果进行排序的时候使用了临时表，常见于排序OrderBy 和分组查询GroupBy

3.Using Index：

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错

如果同时出现Using Where，表明索引被用来执行索引键值的查找

如果没有同时出现Using Where，表明索引用来读取数据而非执行查找工作

4.覆盖索引：

5.Using Where：使用where过滤条件

6.Using Join Buffer：使用了连接缓存（join太多个表，配置文件里面的JoinBuffer的值可以调大一点）

7.Impossible Where：where子句的值总是false，不能获取任何元组

8.Select tables optimized away：在没有GroupBy子句的情况下，基于索引优化Min/Max操作或者对于MyISAM存储引擎优化Count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化

9.distinct：优化distinct操作，在找到第一个匹配的元组后即停止找同样值的动作

1.1（A）没有创建关联索引

1.1（B）关联索引

1.2（A）没有创建关联索引

1.2（B）创建关联索引

1.3（A）没有创建关联索引

1.3（B）创建关联索引

1.4（A）没有创建关联索引

1.4（B）创建关联索引

2.创建索引代码

推荐阅读更多精彩内容