表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语句的执行顺序。
一、普通索引这是最基本的索引,它没有任何限制。有以下几种创建方式:
1.创建索引
代码如下:
CREATE INDEX indexName ON mytable(username(length))
如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。
2.修改表结构
代码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定。
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) )
-- 删除索引的语法:
DROP INDEX [indexName] ON mytable
二、唯一索引
它与前面的普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) )
三、主键索引
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) )
当然也可以用 ALTER 命令。记住:一个表只能有一个主键。
四、组合索引
为了形象地对比单列索引和组合索引,为表添加多个字段:
代码如下:
CREATE TABLE mytable( ID INT NOT NULL,username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL )
为了进一步榨取MySQL的效率,就要考虑建立组合索引。
二:使用索引的注意事项
使用索引时,有以下一些技巧和注意事项:
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O *** 作。
3.索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序 *** 作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4.like语句 *** 作
一般情况下不鼓励使用like *** 作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
select * from users where YEAR(adddate)<2007
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:
select * from users where adddate<‘2007-01-01'
6.不使用NOT IN和<> *** 作。
三:sql优化原则
常见的简化规则如下:
1.不要有超过5个以上的表连接(JOIN)
2.考虑使用临时表或表变量存放中间结果。
3.少用子查询
4.视图嵌套不要过深,一般视图嵌套不要超过2个为宜。
5.连接的表越多,其编译的时间和连接的开销也越大,性能越不好控制。
6.最好是把连接拆开成较小的几个部分逐个顺序执行。
7.优先执行那些能够大量减少结果的连接。
8.拆分的好处不仅仅是减少SQL Server优化的时间,更使得SQL语句能够以你可以预测的方式和顺序执行。
如果一定需要连接很多表才能得到数据,那么很可能意味着设计上的缺陷。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)