Sql执⾏计划，优化sql必备！
唇妆SQL执⾏计划学习
背景：
实际项⽬开发中，由于我们不知道实际查询的时候数据库⾥发⽣了什么事情，数据库软件是怎样扫描表、怎样使⽤索引的，因此，我们能感知到的就只有sql语句运⾏的时间，在数据规模不⼤时，查询是瞬间的，因此，在写sql语句的时候就很少考虑到性能的问题。但是当数据规模增⼤，如千万、亿的时候，我们运⾏同样的sql语句时却发现迟迟没有结果，这个时候才知道数据规模已经限制了我们查询的速度。所以，查询优化和索引也就显得很重要了。
引出的问题：
当我们在查询前能否预先估计查询究竟要涉及多少⾏、使⽤哪些索引、运⾏时间呢？
答案是可以的，mysql提供了相应的功能和语法来实现该功能。那就是sql执⾏计划！
关键字：explain
什么是Sql执⾏计划？
执⾏计划，简单的来说，是SQL在数据库中执⾏时的表现情况，通常⽤于SQL性能分析，优化等场景。在MySQL使⽤ explain 关键字来查看SQL的执⾏计划。（这⾥我的理解是，存储引擎在执⾏sql的时候，把⼀条sql分解，列出来每⼀步需要⼲什么，并按照步骤依次执⾏，这样我们就能看出来哪个步骤耽误了时间，当然这也是接下来要讲的重点！）
如何查看sql执⾏计划？
1. 查询SELECT * from employee WHERE `name` ='蒋峰1';
地震分为几个等级
2. 查看上述语句的执⾏计划 （加关键字explain）
EXPLAIN SELECT * from employee WHERE `name` ='蒋峰1';
读懂执⾏计划
通过上⾯，我们知道了什么是执⾏计划，也看到了执⾏计划到底是什么东西，现在我们来具体了解⼀下,MySQL执⾏计划中，每个属性代表的是什么意思?
我们⼀⼀来介绍，并说明每个属性有哪些可选值，以及每个可选值的意思。
id
表⽰查询中lect操作表的顺序，按顺序从⼤到依次执⾏（不是表中的⾃增主键！）
id值相同执⾏顺序从上到下。
id值不同时id值⼤的先执⾏。
lect_type
这⼀列显⽰了对应⾏是简单还是复杂SELECT.取值如下:SIMPLE值意味着查询不包括⼦查询和UNION。查询有任何复杂的⼦部分，则最外层标记为PRIMARY.取值如下：
type
该属性表⽰访问类型，有很多种访问类型。
最常见的其中包括以下⼏种: ALL(全表扫描), index(索引扫描),range(范围扫描),ref (⾮唯⼀索引扫描),eq_ref(唯⼀索引扫描,),(const)常数引⽤, 访问速度依次由慢到快。
其中 : range(范围)常见于 between and …, ⼤于 and ⼩于这种情况。
提⽰ : 慢SQL是否⾛索引,⾛了什么索引，也就可以通过该属性查看了。
table
输出数据⾏所在的表的名称
possible_keys
考试卷子模板顾名思义，指出MySQL能使⽤哪些索引来优化查询，查询所涉及的列上的索引都会被列出，但不⼀定会被使⽤，算是个提⽰作⽤！
key
显⽰MySQL实际使⽤的索引，其中就包括主键索引(PRIMARY)，或者⾃建索引的名字。
如果没有可⽤的索引，则显⽰为NULL
key_len
表⽰索引字段的最⼤可能长度，KEY_LEN的长度由字段定义计算⽽来，并⾮数据的实际长度，
当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中 MySQL 会真正使⽤了哪些索引。
连接匹配条件，如果⾛主键索引的话，该值为: const, 全表扫描的话，为null值
表⽰哪些列或常量被⽤于查找索引列上的值
该表中所有符合检索值的记录都会被取出来和从上⼀个表中取出来的记录作联合。ref⽤于连接程序使⽤键的最左前缀或者是该键不是primary key 或 unique索引（换句话说，就是连接程序⽆法根据键值只取得⼀条记录）的情况。当根据键值只查询到少数⼏条匹配的记录时，这就是⼀个不错的连接类型。 ref还可以⽤于检索字段使⽤=操作符来⽐较的时候。以下的⼏个例⼦中，MySQL将使⽤ ref 来处理
ref_table，和eq_ref的区别是-⽤到的索引是否唯⼀性
rows （关键）
扫描⾏数，也就是说，需要扫描多少⾏，才能获取⽬标⾏数,⼀般情况下会⼤于返回⾏数。通常情况下,rows越⼩，效率越⾼, 也就有⼤部分SQL 优化，都是在减少这个值的⼤⼩。
注意: 理想情况下扫描的⾏数与实际返回⾏数理论上是⼀致的，但这种情况及其少，如关联查询，扫描的⾏数就会⽐返回⾏数⼤⼤增加)
Extra
这个属性⾮常重要，该属性中包括执⾏SQL时的真实情况信息，如上⾯所属，使⽤到的是”using where”，表⽰使⽤where筛选得到的值，常⽤的有:
“Using temporary”: 使⽤临时表 “using filesort”: 使⽤⽂件排序
对⼀开始的sql进⾏改造
牛肉胡萝卜饺子
看到这⾥，我们应该已经发现，在第⼀步中，我们的这条SQL
SELECT * from employee WHERE `name` ='蒋峰1';
是没有⾛索引的,⽽且还是全表扫描，在数据量少的情况下，问题还不会特别突出，如果数据量⽐较⼤，这可是个会造成⽣产事故的慢查询哦，现在我们改造⼀下，将name字段添加上索引,
# 添加索引
alter table employee add index idx_name(name);
看看它的执⾏计划是怎样的。
你看，现在已经⾛idx_name索引了，其type从All(全表扫描)到ref(⾮唯⼀索引了)，别看就只有这⼀点点⼩区别，在⼤数据量的时候，可是会起⼤作⽤的哦。
再举⼀个栗⼦
create table Cour(
c_id int PRIMARY KEY,
name varchar(10))
数据100条-------------------------100条（⾃⼰模拟）
学⽣表:
七的英语怎么写
create table Student(
id int PRIMARY KEY,
name varchar(10))
数据70000条-----------------------400条
学⽣成绩表SC
CREATE table SC(
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int)
数据70w条--------------------------30w条
查询⽬的：
查找语⽂考100分的考⽣
查询语句：
lect s.* from Student s where s.s_id in (lect s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
执⾏时间：30248.271s（29.648s）
为什么这么慢，先来查看下查询计划：
EXPLAIN
lect s.* from Student s where s.s_id in (lect s_id from SC sc where sc.c_id = 0 and sc.score = 100 )
发现没有⽤到索引，type全是ALL，那么⾸先想到的就是建⽴⼀个索引，建⽴索引的字段当然是在where条件的字段。先给sc表的c_id和score建个索引
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
再次执⾏上述查询语句，时间为: 1.054s(2.428s)
快了3w多倍，⼤⼤缩短了查询时间，看来索引能极⼤程度的提⾼查询效率，建索引很有必要，很多时候都忘记建
索引了，数据量⼩的的时候压根没感觉，⼤数据量感觉贼爽。
但是2s的时间还是太长了，还能进⾏优化吗，仔细看执⾏计划：
接下来再次优化：这次我们⽤连接查询！（先删除索引）
alter table SC drop index sc_c_id_index;
alter table SC drop index sc_score_index;
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100
再次执⾏上述查询语句，时间为: 0.088s
EXPLAIN SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100再看执⾏计划：
减脂运动有哪些发现⼀个ALL，extra字段中显⽰where雪梅香
所以我们尝试加索引：
CREATE index sc_c_id_index on SC(c_id);
家常红烧排骨简单做法CREATE index sc_score_index on SC(score);
再次执⾏上条sql：
SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.id where sc.c_id=0 and sc.score=100 再次执⾏上述查询语句，时间为: 0.010s
再看sql执⾏计划：
总结：
2.可以将其优化成连接查询
3.连接表时，可以先⽤where条件对表进⾏过滤，然后做表连接
（虽然mysql会对连表语句做优化）
4.建⽴合适的索引，必要时建⽴多列联合索引
5.当然我们最主要的是要学会分析sql执⾏计划，mysql会对sql进⾏优化，所以分析执⾏计划很重要
索引优化
上⾯讲到⼦查询的优化，以及如何建⽴索引，⽽且在多个字段索引时，分别对字段建⽴了单个索引
后⾯发现其实建⽴联合索引效率会更⾼，尤其是在数据量较⼤，单个列区分度不⾼的情况下。
create index sc_c_id_score_index on SC(c_id,score);
时间为: 0.008s(由于数据量有限，效果不明显，数据⼤的时候效率更⾼)
