sql执行过程及常见优化手段

mysql整体的执行过程如下图所示：

 

 1.1:连接器

连接器的主要职责就是:

①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向服务器请求或者服务器向客户端发送数据,而不能同时进行,其中mysql在与客户端连接TC/IP的

②验证请求用户的账户和密码是否正确,如果账户和密码错误,会报错:Access denied for user 'root'@'localhost' (using password: YES)

③如果用户的账户和密码验证通过,会在mysql自带的权限表中查询当前用户的权限:

mysql中存在4个控制权限的表，分别为user表，db表，tables_priv表，columns_priv表,mysql权限表的验证过程为：

1:User表:存放用户账户信息以及全局级别（所有数据库）权限，决定了来自哪些主机的哪些用户可以访问数据库实例
  Db表:存放数据库级别的权限，决定了来自哪些主机的哪些用户可以访问此数据库 
 Tables_priv表：存放表级别的权限，决定了来自哪些主机的哪些用户可以访问数据库的这个表 
 Columns_priv表：存放列级别的权限，决定了来自哪些主机的哪些用户可以访问数据库表的这个字段 
  Procs_priv表：存放存储过程和函数级别的权限

2:先从user表中的Host,User,Password这3个字段中判断连接的ip、用户名、密码是否存在，存在则通过验证。

3:通过身份认证后，进行权限分配，按照user，db，tables_priv，columns_priv的顺序进行验证。即先检查全局权限表user，如果user中对应的权限为Y，则此用户对所有数据库的权限都为Y，将不再检查db, tables_priv,columns_priv；如果为N，则到db表中检查此用户对应的具体数据库，并得到db中为Y的权限；如果db中为N，则检查tables_priv中此数据库对应的具体表，取得表中的权限Y，以此类推

4:如果在任何一个过程中权限验证不通过,都会报错

1.2:缓存

    mysql的缓存主要的作用是为了提升查询的效率，缓存以key和value的哈希表形式存储，key是具体的sql语句，value是结果的集合。如果无法命中缓存,就继续走到分析器的的一步,如果命中缓存就直接返回给客户端 。不过需要注意的是在mysql的8.0版本以后，缓存被官方删除掉了。之所以删除掉,是因为查询缓存的失效非常频繁,如果在一个写多读少的环境中,缓存会频繁的新增和失效。对于某些更新压力大的数据库来说，查询缓存的命中率会非常低,mysql为了维护缓存可能会出现一定的伸缩性的问题,目前在5.6的版本中已经默认关闭了，比较推荐的一种做法是将缓存放在客户端，性能大概会提升5倍左右

1.3:分析器

   分析器的主要作用是将客户端发过来的sql语句进行分析，这将包括预处理与解析过程，在这个阶段会解析sql语句的语义，并进行关键词和非关键词进行提取、解析，并组成一个解析树。具体的关键词包括不限定于以下：select/update/delete/or/in/where/group by/having/count/limit等.如果分析到语法错误，会直接给客户端抛出异常:ERROR:You have an error in your SQL syntax.

比如：select *  from user where userId =1234;

在分析器中就通过语义规则器将select from where这些关键词提取和匹配出来,mysql会自动判断关键词和非关键词，将用户的匹配字段和自定义语句识别出来。这个阶段也会做一些校验:比如校验当前数据库是否存在user表，同时假如User表中不存在userId这个字段同样会报错：unknown column in field list.

1.4:优化器

能够进入到优化器阶段表示sql是符合mysql的标准语义规则的并且可以执行的，此阶段主要是进行sql语句的优化，会根据执行计划进行最优的选择,匹配合适的索引,选择最佳的执行方案。比如一个典型的例子是这样的：

表T,对A、B、C列建立联合索引，在进行查询的时候，当sql查询到的结果是:select xx where  B=x and A=x and C=x.很多人会以为是用不到索引的，但其实会用到,虽然索引必须符合最左原则才能使用,但是本质上,优化器会自动将这条sql优化为:where A=x and B=x and C=X,这种优化会为了底层能够匹配到索引，同时在这个阶段是自动按照执行计划进行预处理,mysql会计算各个执行方法的最佳时间,最终确定一条执行的sql交给最后的执行器

1.5:执行器

 在执行器的阶段,此时会调用存储引擎的API,API会调用存储引擎，主要有一下存储的引擎，不过常用的还是myisam和innodb:

 

 引擎以前的名字叫做:表处理器(其实这个名字我觉得更能表达它存在的意义)负责对具体的数据文件进行操作,对sql的语义比如select或者update进行分析,执行具体的操作。在执行完以后会将具体的操作记录到binlog中,需要注意的一点是:select不会记录到binlog中,只有update/delete/insert才会记录到binlog中。而update会采用两阶段提交的方式,记录都redolog中

二：执行的状态

可以通过命令:show full processlist,展示所有的处理进程,主要包含了以下的状态，表示服务器处理客户端的状态，状态包含了从客户端发起请求到后台服务器处理的过程，包括加锁的过程、统计存储引擎的信息，排序数据、搜索中间表、发送数据等。囊括了所有的mysql的所有状态,其中具体的含义如下图：

 三:sql的执行顺序

 事实上,sql并不是按照我们的书写顺序来从前往后、左往右依次执行的,它是按照固定的顺序解析的,主要的作用就是从上一个阶段的执行返回结果来提供给下一阶段使用，sql在执行的过程中会有不同的临时中间表，一般是按照如下顺序：

例子: select distinct s.id  from T t join  S s on t.id=s.id where t.name="Yrion" group by t.mobile having count(*)>2  order by s.create_time limit 5;

3.1：from

第一步就是选择出from关键词后面跟的表,这也是sql执行的第一步:表示要从数据库中执行哪张表。

实例说明:在这个例子中就是首先从数据库中找到表T

3.2:join on

join是表示要关联的表，on是连接的条件。通过from和join on选择出需要执行的数据库表T和S,产生笛卡尔积,生成T和S合并的临时中间表Temp1。on:确定表的绑定关系,通过on产生临时中间表Temp2.

实例说明：找到表S,生成临时中间表Temp1,然后找到表T的id和S的id相同的部分组成成表Temp2,Temp2里面包含着T和Sid相等的所有数据

3.3:where

where表示筛选,根据where后面的条件进行过滤,按照指定的字段的值(如果有and连接符会进行联合筛选)从临时中间表Temp2中筛选需要的数据,注意如果在此阶段找不到数据，会直接返回客户端,不会往下进行.这个过程会生成一个临时中间表Temp3。注意在where中不可以使用聚合函数，聚合函数主要是(min\max\count\sum等函数)

实例说明:在temp2临时表集合中找到T表的name="Yrion"的数据,找到数据后会成临时中间表Temp3,temp3里包含name列为"Yrion"的所有表数据

3.4:group by 

group by是进行分组，对where条件过滤后的临时表Temp3按照固定的字段进行分组,产生临时中间表Temp4，这个过程只是数据的顺序发生改变,而数据总量不会变化,表中的数据以组的形式存在

实例说明:在temp3表数据中对mobile进行分组,查找出mobile一样的数据,然后放到一起，产生temp4临时表。

3.5:Having

对临时中间表Temp4进行聚合,这里可以为count等计数，然后产生中间表Temp5，在此阶段可以使用select中的别名

实例说明:在temp4临时表中找出条数大于2的数据,如果小于2直接被舍弃掉，然后生成临时中间表temp5

3.6:select

对分组聚合完的表挑选出需要查询的数据,如果为*会解析为所有数据,此时会产生中间表Temp6

实例说明：在此阶段就是对temp5临时聚合表中S表中的id进行筛选产生Temp6,此时temp6就只包含有s表的id列数据,并且name="Yrion"，通过mobile分组数量大于2的数据

3.7:Distinct

distinct对所有的数据进行去重,此时如果有min、max函数会执行字段函数计算，然后产生临时表Temp7

实例说明:此阶段对temp5中的数据进行去重,引擎API会调用去重函数进行数据的过滤,最终只保留id第一次出现的那条数据,然后产生临时中间表temp7

3.8:order by 

会根据Temp7进行顺序排列或者逆序排列，然后插入临时中间表Temp8，这个过程比较耗费资源

实例说明：这段会将所有temp7临时表中的数据按照创建时间(create_time)进行排序,这个过程也不会有列或者行损失

 3.9:limit

limit对中间表Temp8进行分页,产生临时中间表Temp9,返回给客户端。

实例说明:在temp7中排好序的数据,然后取前五条插入到Temp9这个临时表中，最终返回给客户端

ps:实际上这个过程也并不是绝对这样的，中间mysql会有部分的优化以达到最佳的优化效果，比如在select筛选出找到的数据集

 

常用sql优化手段

一、定位慢查询：

必要指令：
show status like ‘uptime’;当前数据库运行多久

show session或global status like ‘com_select’;当前数据库运行多少次查询
show session或global status like ‘com_update’;当前数据库运行多少次更新
show session或global status like ‘com_delete’;当前数据库运行多少次删除
show session或global status like 'com_insert;当前数据库运行多少次查询
默认是session，意思是当前会话的操作次数
global是所有的操作次数
根据数据库的操作类型来决定使用什么存储引擎

show status like ‘connections’;当有多少客户端连接数据库

show status like ‘slow_queries’；查询有多少慢查询

默认情况，mysql认为查询时间到达10秒才是一个慢查询
show variables like ‘long_query_time’;显示慢查询定义时间
set long_query_time=1;将慢查询定义时间设置为1秒
查询方法：
1.停止mysql服务，在计算机管理–>服务–>mysql中停止
2.在默认情况下mysql不记录慢查询日志，需要在启动的时候指定
bin\mysqld.exe - -safe-mode - -slow-query-log [mysql5.5 可以在my.ini指定]
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]
（在bin的上级目录打开cmd再执行）
启用慢查询日志后，默认把日志文件放在my.ini文件中记录的位置
在文件中搜索：“datadir=”；
不要轻易更改此目录，会造成索引失效。
3.set long_query_time=1;将慢查询定义时间设置为1秒
4.show status like ‘slow_queries’；查询有多少慢查询
5.查日志就可以把慢查询揪出来了

二、索引：
索引为毛这么快：
1.没有索引时，需要全表检索，即使很早检索到数据也必须搜完所有表；
2.加完索引后，会建立一个索引文件，这个索引文件会将索引字段的磁盘地址构建成一个二叉树的数据结构进行存储，搜索时会进行二分查找，一旦查找到要查的数据就不需要找更下级的数据了，这样就大大加大了查询速度。
经过计算：检索10次可以检索1024条数据，检索30次可以检索10亿的数据
主键索引：
创建：
1.当一张表，把某个列设置为主键时，则该列就是主键索引
2.alter table 表名 add primary key （列名）；
查询：
1.desc 表名； 该方法缺点是：不能够显示索引名
2.show index（es） from 表名；
3.show keys from 表名；
唯一索引：
1.当表的某列被指定为unique约束时，这列就是一个唯一索引
create table ddd(id int primary key auto_increment , name varchar(32) unique);
这时, name 列就是一个唯一索引.
unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复.
主键字段，不能为NULL,也不能重复.
2.在创建表后，再去创建唯一索引
create table eee(id int primary key auto_increment, name varchar(32));
create unique index 索引名 on 表名 (列表…);
全文索引：
应用场景：针对文本（文件）的检索，全文索引仅针对MyISAM有用

使用：
1.在mysql中fulltext 索引只针对 myisam生效
2.mysql自己提供的fulltext针对英文生效->sphinx (coreseek) 技术处理中文
3.使用方法是 match(字段名…) against(‘关键字’)
4.全文索引一个 叫 停止词, 因为在一个文本中，如果使用常用词创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，称为停止词.
普通索引：

索引删除及修改：
删除
alter table 表名 drop index 索引名;
如果删除主键索引。
alter table 表名 drop primary key [这里有一个小问题]
修改
先删除，再重新创建.
索引的使用方法：
在如下情况添加索引比较合适：
a: 经常作为where条件被使用
b: 该字段的内容不是唯一的几个值(sex)
c: 字段内容不是频繁变化
会导致索引失效的语句：
1、使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like ‘%文’–索引不起作用）
2、使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效
3、使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效。
4、尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。
5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。
6、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
　　select id from t where num/2=100
　　应改为:
　　select id from t where num=100*2
7、尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。
8、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
9、并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引，如一表中有字段
　　sex，male,female几乎个一半，那么即使在sex上建立了索引也对查询效率起不了作用。
10、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
　　因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，
　　若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
11、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
　　这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
12、mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
　　 因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
13、order by 索引 ，不起作用的问题（除了主键索引之外）：
　　1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；

2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！
四种索引的使用场景：
PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。 就是 唯一 且 不能为空。
INDEX 索引，普通的
UNIQUE 唯一索引。 不允许有重复。
FULLTEXT 是全文索引，用于在一篇文章中，检索文本信息的。
举个例子来说，比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段：

会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）
会员备注信息 ， 如果需要建索引的话，可以选择 FULLTEXT，全文搜索。
不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

三、搜索引擎的选择：
1、MYISAM：默认的MySQL存储引擎。如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。重要性不高的数据，如论坛的发帖、回帖。
2、Innodb：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间。重要性较高的数据，如订单表、工资表。
3.Memory：存储在内存当中，速度快，但会占用和数量成正比的内存空间且数据在mysql重启时会丢失。经常变化、查询频繁的临时数据。

四、explain分析SQL:

Explain select * from emp where ename=“zrlcHd”会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引key_len:索引字段的长度rows:扫描出的行数(估算的行数)
Extra:执行情况的描述和说明

五、常用SQL优化：

1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10unionallselect id from t where num=20

5、下面的查询也将导致全表扫描：(不能前置百分号)

select id from t where name like ‘%c%’

若要提高效率，可以考虑全文检索。

6、in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) wherenum=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t wheresubstring(name,1,3)=’abc’–name以abc开头的idselect id from t wheredatediff(day,createdate,’2005-11-30′)=0–’2005-11-30′生成的id

应改为:

select id from t where name like‘abc%’select id from t where createdate>=’2005-11-30′ andcreatedate<’2005-12-1′

10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12、不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table #t(…)

13、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select numfrom b)

用下面的语句替换：

select num from a where exists(select 1from b where num=a.num)

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列，因为clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。

17、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

18、尽可能的使用varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

19、任何地方都不要使用 select *from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

20、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

21、避免频繁创建和删除临时表，以减少系统表资源的消耗。

22、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

23、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

24、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

25、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

26、使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。

27、与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。

28、在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

30、尽量避免大事务操作，提高系统并发能力。

 

 

参考自

https://www.cnblogs.com/mengxinJ/p/14045520.html

https://blog.csdn.net/qq_42695926/article/details/83900198

https://blog.csdn.net/wufaliang003/article/details/79875383