9条回答

1楼 · 2021-05-24 16:06.采纳回答

MySql优化的一般步骤：

1.通过show status 命令了解各种sql的执行效率

　　SHOW STATUS提供msyql服务器的状态信息

　　一般情况下，我们只需要了解以”Com”开头的指令

　　show session status like ‘Com%’：显示当前的连接的统计结果

　　show global status like ‘Com%’ ：显示自数据库上次启动至今的统计结果

　　注：默认是session级别的

　　其中Com_XXX表示XXX语句所执行的次数。
重点注意：Com_select,Com_insert,Com_update,Com_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

　　另外，还有几个参数需要注意下：

　　show status like ‘Connections’// 试图连接MySQL服务器的次数

　　show status like ‘Uptime’//服务器工作的时间（单位秒）

　　show status like ‘Slow_queries’//慢查询的次数 (默认是10秒中就当做是慢查询，如下图所示)

　　a) 如何查询mysql的慢查询时间

　　　　Show variables like 'long_query_time';

　　b) 修改mysql 慢查询时间

　　　　set long_query_time=2//如果查询时间超过2秒就算作是慢查询

2. 定位执行效率较低的SQL语句（dql出现问题的概率较dml的大）

　　问题是：如何在一个项目中，找到慢查询的select语句？

　　答案：mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件，默认情况下，慢查询记录是不开启的。

　　开启慢查询记录的步骤：

　　打开 my.ini ,找到 [mysqld] 在其下面添加

　　long_query_time = 2

　　log-slow-queries = D:/mysql/logs/slow.log #设置把日志写在那里，可以为空，系统会给一个缺省的文件

例子：我们数据表中有1千万条的数据量

　　DQL语句：SELECT * FROM order_copy WHERE id=12345;

　　查询耗时：19s>2s，所以mysql会将该条select语句记录到慢查询日志中

　　SELECT * FROM order_copy WHERE id=12345的执行时间：

　　添加索引前：19s

　　添加索引后：0.08s

3.通过explain分析低效率的SQL语句的执行情况

　　使用explain分析该dql语句：

EXPLAIN SELECT * FROM order_copy WHERE id=12345
会产生如下信息：
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型(system和const为佳)
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描的行数
Extra:执行情况的描述和说明

注意：要尽量避免让type的结果为all，extra的结果为：using filesort

4.确定问题并采取相应的优化措施

常用的优化措施是添加索引。添加索引，我们不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。

例如：给字段id添加索引：

ALTER TABLE order_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时： 428秒（7分钟）

EXPLAIN SELECT * FROM order_copy WHERE id=12345

正是因为给id添加了索引，才使得rows的结果为1

但是索引并不是可以随便添加的，以下几种情况需牢记在心：

较频繁的作为查询条件字段应该创建索引

　　 select * from order_copy where id = $id

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

　　 select * from order_copy where sex=’女’

更新非常频繁的字段不适合创建索引

　　 select * from order_copy where order_state=’未付款’

不会出现在WHERE子句中字段不该创建索引

索引的类型：

PRIMARY 索引 => 在主键上自动创建
INDEX 索引 => 就是普通索引
UNIQUE 索引 => 相当于INDEX + Unique
FULLTEXT => 只在MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多，在全英文网站用多(英文词独立). 中文数据不常用，意义不大国内全文索引通常使用 sphinx 来完成.

索引的使用

建立索引 create [UNIQUE|FULLTEXT] index index_name on tbl_name (col_name [(length)] [ASC | DESC] , …..);
alter table table_name ADD INDEX [index_name] (index_col_name,...)

　　添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

删除索引 DROP INDEX index_name ON tbl_name;
alter table table_name drop index index_name;
删除主键(索引)比较特别: alter table t_b drop primary key;
查询索引(均可) show index from table_name;
show keys from table_name;
desc table_Name;

tiu

2楼 · 2021-05-21 17:34

1.当我们请求mysql服务器的时候,MySQL前端会有一个监听,请求到了之后,服务器得到相关的SQL语句,执行之前(虚线部分为执行),还会做权限的判断

2.通过权限之后,SQL就到MySQL内部,他会在查询缓存中,看该SQL有没有执行过,如果有查询过,则把缓存结果返回,说明在MySQL内部,也有一个查询缓存.但是这个查询缓存,默认是不开启的,这个查询缓存,和我们的Hibernate，Mybatis的查询缓存是一样的,因为查询缓存要求SQL和参数都要一样,所以这个命中率是非常低的（没什么卵用的意思）。

3.如果我们没有开启查询缓存,或者缓存中没有找到对应的结果,那么就到了解析器,解析器主要对SQL语法进行解析

4.解析结束后就变成一颗解析树,这个解析树其实在Hibernate里面也是有的,大家回忆一下,在以前做过Hibernate项目的时候,是不是有个一个antlr.jar。这个就是专门做语法解析的工具.因为在Hibernate里面有HQL，它就是通过这个工具转换成SQL的,我们编程语言之所以有很多规范、语法,其实就是为了便于这个解析器解析,这个学过编译原理的应该知道.

5.得到解析树之后,不能马上执行,这还需要对这棵树进行预处理,也就是说,这棵树,我没有经过任何优化的树,预处理器会这这棵树进行一些预处理,比如常量放在什么地方,如果有计算的东西,把计算的结果算出来等等...

6.预处理完毕之后,此时得到一棵比较规范的树,这棵树就是要拿去马上做执行的树,比起之前的那棵树,这棵得到了一些优化

7.查询优化器，是MySQL里面最关键的东西,我们写任何一条SQL,比如SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1,它会怎么去执行?它是先执行username = toby还是password = 1?每一条SQL的执行顺序查询优化器就是根据MySQL对数据统计表的一些信息,比如索引,比如表一共有多少数据,MySQL都是有缓存起来的,在真正执行SQL之前,他会根据自己的这些数据,进行一个综合的判定,判断这一次在多种执行方式里面,到底选哪一种执行方式,可能运行的最快.这一步是MySQL性能中,最关键的核心点,也是我们的优化原则.我们平时所讲的优化SQL,其实说白了,就是想让查询优化器,按照我们的想法,帮我们选择最优的执行方案,因为我们比MySQL更懂我们的数据.MySQL看数据,仅仅只是自己收集到的信息,这些信息可能是不准确的,MySQL根据这些信息选了一个它自认为最优的方案,但是这个方案可能和我们想象的不一样.

8.这里的查询执行计划,也就是MySQL查询中的执行计划,比如要先执行username = toby还是password = 1

9.这个执行计划会传给查询执行引擎,执行引擎选择存储引擎来执行这一份传过来的计划,到磁盘中的文件中去查询,这个时候重点来了,影响这个查询性能最根本的原因是什么?就是硬盘的机械运动,也就是我们平时熟悉的IO,所以一条查询语句是快还是慢,就是根据这个时间的IO来确定的.那怎么执行IO又是什么来确定的?就是传过来的这一份执行计划.(优化就是制定一个我们认为最快的执行方案,最节省IO,和执行最快)

10.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。

杨晓春

3楼 · 2021-05-21 20:28

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

我的网名不再改

4楼 · 2021-05-23 12:20

一、前言

前一段时间，公司的DBA离职了，这可怎么办呢，临危受命当起了DBA。当然也收到了一部分数据库设计文档，数据库ER图等。但是在后面的开发中，数据库也是随着需求修改，到下面的时候，需要进行SQL优化了，对项目开发人员的SQL进行优化。

二、MySQL

小编项目中用到的是Mysql。

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

三、Sql执行流程

大家先来看一下下面这张有点AV画质的图片：

上面的图，就是我们的Sql 执行的过程：

1. 客户端发送一条查询给服务器；

2. 服务器通过权限检查之后(用户名和密码等),先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

3. 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5. 将结果返回给客户端。

SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；所以,我们说,优化SQL,其实就是让查询优化器根据程序猿的计划选择匹配的执行计划,来减少查询中产生的IO;

名称介绍

【解析器】：检测SQL语法解析是否有错误，解析成为解析树（没有优化的树）

在Hibernate中，的antlr的jar包中，专门作为语法解析的。用于识别自己定义的语言，比如HQL。

Hibernate中有自己定的HQL（Hibernate Query Language）

HQL（Hibernate Query Language）是Hibernate提供的一种面向对象的查询语言，HQL提供了更加丰富灵活的特性、强大的查询能力，HQL提供了更接近传统的SQL语句的查询语法。

【预处理器】

对解析树进行预处理：常量排到什么地方；有计算的地方，把计算得到结果；

得到更加规范的树

【查询优化器】

最关键，任何sql，从多种查询方案中，根据mysql自身的统计信息，选择一种最优的

sql优化，就是要使得mysql的查询优化器按照程序员的思路去优化。

【查询执行计划】

执行步骤，第一步干什么，第二步干什么

【查询执行引擎】

Mysql的执行引擎是多种多样的，InnoDB，MyISM，memory，数据库用的什么引擎，就用什么引擎执行。

四、小结

通过对这个sql执行的过程学习，更加熟练了自己对sql的使用。举一个例子，我们都知道阿里的druid解析器可以解析SQL，就是用了解析器类似的东西。

灰机带翅膀

5楼 · 2021-05-23 13:16

执行流程

客户端发送一条查询给服务器；

2. 服务器通过权限检查之后(用户名和密码等),先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

3. 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5. 将结果返回给客户端。

Mysql的sql优化方法

1、选择最合适的字段属性

Mysql是一种关系型数据库，可以很好地支持大数据量的存储，但是一般来说，数据库中的表越小，在它上面执行的查询也就越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度舍得尽可能小

例如：在定义邮政编码这个字段时，如果将其设置为char(255)，显然给数据库增加了不必要的空间，甚至使用varchar这种类型也是多余的，因为char(6)就可以很好地完成了任务。同样的如果可以的话，我们应该是用MEDIUMINT而不是BIGINT来定义整形字段。

2、尽量把字段设置为NOT NULL

在可能的情况下，尽量把字段设置为NOT NULL,这样在将来执行查询的时候，数据库不用去比较NULL值。对于某些文本字段来说，例如“省份”或者“性别”，我们可以将他们定义为ENUM(枚举)类型。因为在MySQL中，ENUM类型被当做数值型数据来处理，而数值型数据被处理起来的速度要比文本类型要快得多。这样我们又可以提高数据库的性能。

3、使用连接(JOIN)来代替子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询。这个技术可以使用select语句来创建一个单例的查询结果，然后把这个结果作为过滤条件用在另一个查询中。例如：我们要将客户基本信息表中没有任何订单的客户删除掉，就可以利用子查询先从销售信息表中将所有发出订单的客户id取出来，然后将结果传递给主查询。

爱学习的小巴

6楼 · 2021-05-24 09:03

1. 客户端发送一条查询给服务器；

2. 服务器通过权限检查之后(用户名和密码等),先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；

3. 服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；

4. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；

5. 将结果返回给客户端。

SQL执行的最大瓶颈在于磁盘的IO，即数据的读取；不同SQL的写法，会造成不同的执行计划的执行，而不同的执行计划在IO的上面临完全不一样的数量级，从而造成性能的差距；所以,我们说,优化SQL,其实就是让查询优化器根据程序猿的计划选择匹配的执行计划,来减少查询中产生的IO

猫的想法不敢猜

7楼 · 2021-06-06 09:43

客户端发送一条查询给服务器；
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器段进行SQL解析、预处理，在优化器生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
将结果返回给客户端。
1、MySQL客户端和服务器通讯
MySQL客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。这种协议让MySQL通信简单快速，但也限制了MySQL。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。
客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，他能做的事情就只是等待结果了。
相反的，一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。
多数连接MySQL的库函数都可以获得全部结果并缓存到内存里，还可以逐行获取所需要的数据。默认一般是获得全部结果并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接受全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放对应的资源。
2、查询缓存
在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。
如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，MySQL会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。
3、查询优化处理
查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql在依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。
语法解析器和预处理：首先MySQL通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。MySQL解析器将使用MySQL语法规则验证和解析查询；预处理器则根据一些MySQL规则进一步检查解析数是否合法。
查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
执行计划：MySQL不会生成查询字节码来执行查询，MySQL生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。
4、查询执行引擎
在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。
MySQL简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询，MySQL只需要重复执行计划中的各个操作，知道完成所有的数据查询。
5、返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，MySQL仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么MySQL在这个阶段也会将结果放到查询缓存中。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让MySQL客户端第一时间获得返回的结果。
结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对MySQL的封包进行缓存然后批量传输。
join 算法简介
1）Nested Loop Join算法
NLJ 算法:将驱动表/外部表的结果集作为循环基础数据，然后循环从该结果集每次一条获取数据作为下一个表的过滤条件查询数据，然后合并结果。如果有多表join，则将前面的表的结果集作为循环数据，取到每行再到联接的下一个表中循环匹配，获取结果集返回给客户端。
Nested-Loop 的伪算法如下:

for each row in t1 matching range {

 for each row in t2 matching reference key {

 for each row in t3 {

 if row satisfies join conditions,

      send to client

    }

  }

 }

因为普通Nested-Loop一次只将一行传入内层循环, 所以外层循环(的结果集)有多少行, 内存循环便要执行多少次.在内部表的连接上有索引的情况下，其扫描成本为O(Rn),若没有索引,则扫描成本为O(Rn*Sn)。如果内部表S有很多记录，则SimpleNested-Loops Join会扫描内部表很多次，执行效率非常差。
2）Index Nested-Loop Join算法
与NLJ算法类似其优化的思路主要是为了减少内层表数据的匹配次数;简单来说Index Nested-Loop Join 就是通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录去进行比较，这样极大的减少了对内层表的匹配次数;从原来的匹配次数=外层表行数 * 内层表行数,变成了外层表的行数 * 内层表索引的高度，极大的提升了 join的性能。
3）Block Nested-Loop Join算法
BNL 算法:将外层循环的行/结果集存入joinbuffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数.
举例来说，外层循环的结果集是100行，使用NLJ 算法需要扫描内部表100次，如果使用BNL算法，先把对Outer Loop表(外部表)每次读取的10行记录放到join buffer,然后在InnerLoop表(内部表)中直接匹配这10行数据，内存循环就可以一次与这10行进行比较, 这样只需要比较10次，对内部表的扫描减少了9/10。所以BNL算法就能够显著减少内层循环表扫描的次数.
前面描述的query, 如果使用join buffer, 那么实际join示意如下:

for each row in t1 matching range {

 for each row in t2 matching reference key {

    store used columns from t1, t2 in join buffer

 if buffer is full {

 for each row in t3 {

 for each t1, t2 combination in join buffer {

 if row satisfies join conditions,

          send to client

        }

       }

      empty buffer

    }

  }

}





if buffer is not empty {

 for each row in t3 {

 for each t1, t2 combination in join buffer {

 if row satisfies join conditions,

      send to client

     }

  }

}

如果t1, t2参与join的列长度只和为s, c为二者组合数, 那么t3表被扫描的次数为
(S * C)/join_buffer_size + 1
扫描t3的次数随着join_buffer_size的增大而减少, 直到join buffer能够容纳所有的t1, t2组合, 再增大join buffer size, query 的速度就不会再变快了。
join语句的优化
1. 用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数；
2. 优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
3. 对被驱动表的join字段上建立索引；
4. 当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size。
来源于知乎：SIEMEN程序员，仅供网友参考

zgzbs

8楼 · 2021-08-19 09:36

DECLARE personPlaneId BIGINT;

DECLARE starttime datetime; #开始时间
DECLARE endtime datetime; #结束时间
DECLARE sumStudyTime INT; #当前学时
DECLARE targetStudyTime INT; #目标学时
DECLARE id1_count BIGINT default 0; #t_stage_info 第一阶段count
DECLARE id2_count BIGINT default 0; #t_stage_info 第二阶段count
DECLARE id3_count BIGINT default 0; #t_stage_info 第三阶段count
DECLARE firstEndTime datetime; #第一阶段结束时间
DECLARE secEndTime datetime; #第二阶段结束时间
DECLARE thirdEndTime datetime; #第三阶段结束时间

果果宝贝

9楼 · 2021-09-17 19:27

怎么结合mysql的执行计划来优化sql
mysql是一种sql关系型数据库管理系统，用处可多了去了，基本上你只要上网、用手机都会有数据库在给你提供服务，只不过你不知道罢了
凡是需要用数据库来存储数据的地方都可以用
比如百度知道这种网站，要随时管理查询大量的数据，后台是要有数据库来存储所有的问题和回答的，这就需要用到数据库
现在的技术环境下，只要有网站，后台肯定就有数据库支撑
用于企业的应用系统，比如办公自动化系统、财务会计系统、顾客管理系统，几乎无一例外需要数据库支撑
还有现在的智能手机，系统和很多app都是需要数据库支撑的

MySQL的执行流程怎么优化

Mysql的sql优化方法

相关问题推荐

等你来答

热门问答

相关文章

MySQL的执行流程怎么优化

Mysql的sql优化方法

相关问题推荐

等你来答

热门问答

相关文章

采纳回答

编辑标签

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

付费偷看金额在0.1-10元之间