mysql怎么执行查询 mysql执行查询sql语句

简介mysql之mysql语句执行流程

1.一条查询语句如何执行？

成都创新互联主要从事网页设计、PC网站建设（电脑版网站建设）、wap网站建设（手机版网站建设）、响应式网站、程序开发、网站优化、微网站、小程序开发等，凭借多年来在互联网的打拼，我们在互联网网站建设行业积累了丰富的成都网站建设、做网站、网站设计、网络营销经验，集策划、开发、设计、营销、管理等多方位专业化运作于一体。

2.一条更新语句如何执行？

3.innodb的redolog是什么？

4.什么是写缓冲

5.写缓冲一定好吗？

6.什么情况会引发刷脏页

关于一条mysql查询语句在mysql中的执行流程

如select name from test where id=10;

1.连接器---先与mysql服务端连接器建立连接，若查询缓存命中则直接返回 （查询缓存的弊端：查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。）

2.分析器---词法分析告诉服务端你要干什么（我要找 test表中id为10的名字） ( 其中sql语法错误在这块暴露 )

3.优化器---服务端会思考该怎么执行最优(索引的选择)

4.执行器---检查用户对库对表的权限

5.存储引擎--存储数据，提供读写接口

以update a set name=1 where id=1;

主要区别在于在查询到数据之后(select name from a where id=1)，如果是innodb引擎它会进行日志的两阶段提交:

1.开启事务，写入redolog（innodb引擎特有），并更新内存

3.写入binlog，提交事务，commit

我们知道mysql数据存储包含内存与磁盘两个部分，innodb是按数据页(通常为16k)从磁盘读取到内存中的(剩余操作在内存中执行)，当要更新数据时，若目标数据的数据页刚好在内存中，则直接更新。不在呢？

将这个更新操作(也可能是插入) 缓存在change buffer中 (redolog也会记录这个change buffer操作)等到下一次查询要用到这些数据时，再执行这些操作，改变数据(称为合并操作记录称为merge)。

innodb_change_buffer_max_size

innodb_change_buffering

先介绍两个概念

因为redolog是环形日志，当redolog写满时，就需要“擦掉”开头的一部分数据来达到循环写，这里的擦掉指，指将redolog日志的checkpoint位置从 CP推进到CP‘ ，同时将两点之间的脏页刷到磁盘上(flush操作)，此时系统要停止所有的更新操作（防止更新操作丢失）

1.系统内存不足。当要读取新的内存页时就要淘汰一些数据页，如果淘汰的正好是脏页，就要执行一次flush操作

2.Mysql认为系统处于“空闲状态”

3.正常关闭Mysql

上述后两者场景(系统空闲和正常关闭)对于性能都没太大影响。

当为第一种redolog写满时，系统无法执行更新操作，所有操作都会堵塞

当为第二种内存不够用时，如果淘汰脏页太多，影响mysql响应时间

后两者刷脏页会影响性能，所以Mysql需要有刷脏页控制策略，可以从以下几个设置项考虑

1.设置innodb_io_capacity告诉innodb所在主机的IO能力

如何利用ClickHouse高速汇总查询MySQL数据

直连到MySQL的功能现在已被添加到ClickHouse中。所以可以直接从ClickHouse查询MySQL表中的数据。

ClickHouse是一个超高性能的海量数据快速查询的分布式实时处理平台，在数据汇总查询方面(如GROUP BY)，ClickHouse的查询更快，因此通常情况下在MySQL上进行聚合。

下面是在自己的虚拟环境中做的测试记录。既然是虚拟环境非生产环境，请看参考应用级别，而不是严格的测试。

正确准备和测试大约1000万个数据项。

执行脚本以创建样本数据。这是约500 MB。

在MySQL中创建一个表并导入它。

由于似乎字符串数据比数字值更容易汇总，所以内容是数字的，但它们是可变的类型。

然后执行计算操作。

用BLOB类型做GROUPBY似乎很慢。

您可以在ClickHouse上使用MySQL数据，只需在下面的表格中指定它，而不是表名。

启动ClickHouse客户端并进行测试。

如果您在启动时不添加--multiline选项，则不会放置多行查询，因此请继续。

SELECT 

data1, 

COUNT(*)

FROM mysql('localhost','mikage','testdata','mikage','') 

GROUP BY data1

┌─data1─┬─COUNT()─┐

│    4│1999013│

│    3│1998988│

│    2│1999993│

│    5│2001553│

│    1│2000453│

└───────┴─────────┘

5 rows in set. Elapsed: 2.685 sec. Processed 10.00 million rows,40.00 MB(3.72 million rows/s.,14.90 MB/s.) 

SELECT 

data2, 

COUNT(*)

FROM mysql('localhost','mikage','testdata','mikage','') 

GROUP BY data2

┌─data2─┬─COUNT()─┐

│6    │  999786│

│8    │1001805│

│9    │1001438│

│3    │1000357│

│2    │1000648│

│4    │  998349│

│5    │  998889│

│10    │  999424│

│1    │1000530│

│7    │  998774│

└───────┴─────────┘

10 rows in set. Elapsed: 2.692 sec. Processed 10.00 million rows,101.00 MB(3.71 million rows/s.,37.52 MB/s.) 

SELECT 

data3, 

COUNT(*)

FROM mysql('localhost','mikage','testdata','mikage','') 

GROUP BY data3

-- 结果省略

100000 rows in set. Elapsed: 5.236 sec. Processed 10.00 million rows,138.89 MB(1.91 million rows/s.,26.52 MB/s.) 

SELECT 

data1, 

uniqExact(data5)

FROM mysql('localhost','mikage','testdata','mikage','') 

GROUP BY data1

┌─data1─┬─uniqExact(data5)─┐

│    4│          1811674│

│    3│          1812072│

│    2│          1812503│

│    5│          1814106│

│    1│          1813005│

└───────┴──────────────────┘

5 rows in set. Elapsed: 12.944 sec. Processed 10.00 million rows,198.89 MB(772.55 thousand rows/s.,15.37 MB/s.) 

-- ClickHouse有一个函数来粗略计算一个唯一的数字，所以让我们来计算一下。

在MySQL中，相当耗时的查询也可以在很短的时间内处理。

重复统计时，最好将数据复制到ClickHouse一次。

如果您复制它，后续查询将更快。

建议暂时使用StripeLog引擎。

如果您有一个主键，您可能还想要使用MergeTree表。这是在ClickHouse中最常用的引擎。

有必要用ORDER BY指定数据的排序顺序（即使有重复也没有问题）。

我会尝试以前的查询。以下是StripeLog引擎的测试结果。

测试耗时总结如下:

我认为这个错误很大，因为它在VM环境下仅测试了一次

从左边开始为，(1)MySQL中的时间 (2) ClickHouse从MySQL读取和处理数据的时间 (3) 在ClickHouse上处理复制数据的时间。

执行查询 MySQL处理时间 (秒) MySQL-ClickHouse处理时间 (秒) ClickHouse处理时间 (秒)

groupby(data1) 3.22 2.685 0.071

groupby(data2) 4.01 2.692 0.177

groupby(data3) 212.82 5.236 0.779

groupby(data1)+uniq(data5) 183.56 12.944 1.725

groupby(data1)+uniq(data5)概算 (无此功能) 6.026 0.285

当引用MySQL数据时，如果没有对应于ClickHouse的类型，它似乎是String类型。

没有相应的类型如Decimal类型，所以它也是String类型。

如果你想把它作为一个数值，精度将会改变，但是似乎有必要在MySQL端保持Double类型。

而且，Date和DateTime类型在MySQL和ClickHouse之间的范围也是不同的。

ClickHouse日期类型是1970 - 2038年之间。

如果有超出范围的数据，可能需要使其成为字符串类型，按年份，月份，日期分列，并将其作为数值复制。

参照源码如下：

MySQL类型 ClickHouse 类型 参考

tinyint    UInt8 / Int8

smallint    UInt16 / Int16

int / mediumint    UInt32 / Int32

bigint    UInt64 / Int64

float    Float32

double    Float64

dateDate    有可以表达的范围差异

datetime    DateTime有可以表达的范围差异

timestamp    DateTime

binaryFixed    String

除上述以外    String

MySql中Sql的执行过程

如果查询缓存没有命中，那么SQL请求会进入分析器，分析器是用来分辨SQL语句的执行目的，其执行过程大致分为两步：

表1 语法分析关键字然后再通过语法规则解析，判断输入的SQL 语句是否满足MySQL语法，并且生成图5的语法树。由SQL语句生成的四个单词中，识别出两个关键字，分别是select 和from。根据MySQL的语法Select 和 from之间对应的是fields 字段，下面应该挂接username；在from后面跟随的是Tables字段，其下挂接的是userinfo。

优化器的作用是对SQL进行优化，生成最有的执行方案。如图6所示，前面提到的SQL解析器通过语法分析和语法规则生成了SQL语法树。这个语法树作为优化器的输入，而优化器（黄色的部分）包含了逻辑变换和代价优化两部分的内容。在优化完成以后会生成SQL执行计划作为整个优化过程的输出，交给执行器在存储引擎上执行。

所处的位置如上图所示，这节的重点在优化器中的逻辑变换和代价优化上。

逻辑变换也就是在关系代数基础上进行变换，其目的是为了化简，同时保证SQL变化前后的结果一致，也就是逻辑变化并不会带来结果集的变化。其主要包括以下几个方面：

这样讲概念或许有些抽象，通过图7 来看看逻辑变化如何在SQL中执行的吧。

如图7所示，从上往下共有4个步骤：

1. 针对存在的SQL语句，首先通过“否定消除”，去掉条件判断中的“NOT”。语句由原来的“or”转换成“and”，并且大于小于符号进行变号。蓝色部分为修改前的SQL，红色是修改以后的SQL。2. 等值传递，这一步很好理解分别降”t2.a=9” 和”t2.b=5”分别替换掉SQL中对应的值。3. 接下来就是常量表达式计算，将“5+7”计算得到“12”。4. 最后是常量表达式计算后的化简，将”9=10”化简为”true”带入到最终的SQL表达式中完成优化。

代价优化是用来确定每个表，根据条件是否应用索引，应用哪个索引和确定多表连接的顺序等问题。为了完成代价优化，需要找到一个代价最小的方案。因此，优化器是通过基于代价的计算方法来决定如何执行查询的（Cost-based Optimization）。简化的过程如下：

这里将配置操作的代价分为MySQL 服务层和MySQL 引擎层，MySQL 服务层主要是定义CPU的代价，而MySQL 引擎层主要定义IO代价。MySQL 5.7 引入了两个系统表mysql.server_cost和mysql.engine_cost来分别配置这两个层的代价。如下：MySQL 服务层代价保存在表server_cost中，其具体内容如下：

由上可以看出创建临时表的代价是很高的，尤其是内部的myisam或innodb临时表。MySQL 引擎层代价保存在表engine_cost中，其具体内容如下：

目前io_block_read_cost和memory_block_read_cost默认值均为1，实际生产中建议酌情调大memory_block_read_cost，特别是对普通硬盘的场景。MySQL会根据SQL查询生成的查询计划中对应的操作从上面两张代价表中查找对应的代价值，并且进行累加形成最终执行SQL计划的代价。再将多种可能的执行计划进行比较，选取最小代价的计划执行。

当分析器生成查询计划，并且经过优化器以后，就到了执行器。执行器会选择执行计划开始执行，但在执行之前会校验请求用户是否拥有查询的权限，如果没有权限，就会返回错误信息，否则将会去调用MySQL引擎层的接口，执行对应的SQL语句并且返回结果。例如SQL：“SELECT * FROM userinfo WHERE username = 'Tom';“假设 “username“ 字段没有设置索引，就会调用存储引擎从第一条开始查，如果碰到了用户名字是” Tom“， 就将结果集返回，没有查找到就查看下一行，重复上一步的操作，直到读完整个表或者找到对应的记录。需要注意SQL语句的执行顺序并不是按照书写顺序来的，顺序的定义会在分析器中做好，一般是按照如下顺序：

如果命中的记录比较多，应用会从MySql Server一批批获取数据

本文从MySQL中SQL语句的执行过程作为切入点，首先介绍了查询请求的执行流程，其中将MySQL的处理分为MySQL Server层和MySQL存储引擎层。通过介绍SQL语句的流转，引出了后面要介绍的5大组件，他们分别是：连接器、查询缓存、分析器、优化器、执行器。后面的内容中对每个组件进行了详细的介绍。连接器，负责身份认证和权限鉴别；查询缓存，将查询的结果集进行缓存，提高查询效率；分析器，对SQL语句执行语法分析和语法规则，生成语法树和执行计划；优化器，包括逻辑变换和代价优化；执行器，在检查用户权限以后对数据进行逐条查询，整个过程遵守SQL语句的执行顺序。

关于php操作mysql执行数据库查询的一些常用操作汇总

php操作mysql步骤：

1.$connect=mysql_connect('localhost','root','123456')

or

die('数据库连接失败。'mysql_error());链接mysql。

2.mysql_select_db('database',$connect)选择链接的数据库。

3.mysql_query('Set

names

gb2312');$sql

=

"select

*

from

blog_article";准备要查询的数据。

4.$datas

=

mysql_query($sql);执行sql查询。

5.$data

=

mysql_fetch_assoc($datas)得到查询到的缓存在内存中的一条数据。

6.print_r($data);

相同点：三个函数都是返回数据库中查询到的一行数据（说的再清楚点就是一条数据）。

不同点：mysql_fetch_assoc()用的是数据库中相应的字段名作为的key值（也就是数组下标）

如：filed['id']=1;

mysql_fetch_row()用的是自动生成的数字（从0开始依次生成）作为的key值（也就是数组下标）

如：filed[0]=1;

mysql_fetch_array()用的是自动生成的数字（从0开始依次生成）作为的key值（也就是数组下标）,而且它还同时生成数据库中相应的字段名作为的key值（也就是数组下标）

如：

filed[0]=1，filed['id']=1;也就是说，mysql_fetch_array()将mysql_fetch_assoc()和mysql_fetch_row()查询到的结果合为了一体了。

mysql_fetch_object()与mysql_fetch_assoc()差不多。只是mysql_fetch_assoc()返回的是数组。mysql_fetch_object()返回的是object对象。

mysql_insert_id() 取得上一步

INSERT

操作产生的

ID。

mysql_result()

函数返回结果集中一个字段的值。

mysql_num_fields()

函数返回结果集中字段的数目。

mysql_affected_rows();返回前一次

MySQL

操作所影响的记录行数。

mysql_num_rows(mysql_query($sql))获得结果集中行的数目。

mysql_pconnect()

函数打开一个到

MySQL

服务器的持久连接。

mysql_pconnect()

和

mysql_connect()

非常相似，但有两个主要区别：

1.

当连接的时候本函数将先尝试寻找一个在同一个主机上用同样的用户名和密码已经打开的（持久）连接，如果找到，则返回此连接标识而不打开新连接。

2.

其次，当脚本执行完毕后到

SQL

服务器的连接不会被关闭，此连接将保持打开以备以后使用（mysql_close()

不会关闭由

mysql_pconnect()

建立的连接）。

mysql_data_seek(mysql_query($sql),8);获得结果集中的第8条数据。（mysql_num_rows(mysql_query($sql))和mysql_data_seek(mysql_query($sql),8)在mysql_unbuffered_query($sql)不可以使用。）

mysql_unbuffered_query($sql)和mysql_query($sql)效果差不多，但是

mysql_unbuffered_query($sql)不缓存。mysql_query($sql)会缓存查询的结果。

mysql_close();关闭mysql的最近的链接。

mysql_field_flags(mysql_query($sql),6)返回第六个字段的表属性输出如：not_null

primary_key

auto_increment

。

mysql_fetch_lengths(mysql_query($sql))返回该条数据的所有字段的每个字段的长度。返回的是一个数字组成的数组。

mysql_field_name(mysql_query($sql),3)返回第三个字段的字段名。

mysql_field_table(mysql_query($sql),0)返回指定字段所在的表名。

mysql_free_result(mysql_query($sql))

函数释放结果内存。

mysql_get_client_info()

函数返回

MySQL

客户端信息。

mysql_get_host_info()

取得

MySQL

主机信息。

如何查询mysql的执行记录

-- 打开sql 执行记录功能

set global log_output='TABLE'; -- 输出到表

set global log=ON; -- 打开所有命令

执行记录功能general_log, 所有语句: 成功和未成功的.

set global log_slow_queries=ON; -- 打开慢查询 sql 记录

slow_log, 执行成功的: 慢查询语句和未使用索引的语句

set global long_query_time=0.1; -- 慢查询时间限制(秒)

set global log_queries_not_using_indexes=ON; -- 记录未使用索引的sql 语句

-- 查询sql 执行记录

select * from mysql.slow_log order by 1; -- 执行成功的：慢查询语句,和未

使用索引的语句

select * from mysql.general_log order by 1; -- 所有语句： 成功和未成功的.-- 关闭sql 执行记录