mysql执行器优化器

ゝ一世哀愁。 2023-10-11 19:04 10阅读 0赞

### mysql 存储过程执行太慢怎么优化 ###

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\*FROMUSERWHEREUSERNAME=tobyANDPASSWORD=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.如果开了查询缓存,则返回结果给客户端,并且查询缓存也放一份。

![mysql执行器优化器\_mysql执行器优化][mysql_mysql]

### 如何提高mysql脚本执行速度 ###

看了一个优化案例觉的挺有代表性,这里记录下来做一个标记，来纪念一下随便的字段定义的问题。

回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例.

查询语句:

?1SELECTSQL\_NO\_CACHEchannel,COUNT(channel)ASvisitorsFROMxxx\_sourcesWHEREclient\_id=1301GROUPBYclient\_id,channel;该表(client\_id,channel)是一个组合索引.

?1234567mysql>SELECTSQL\_NO\_CACHEchannel,COUNT(channel)ASvisitorsFROMxxx\_sourcesWHEREclient\_id=1301GROUPBYclient\_id,channel;+---------+----------+|channel|visitors|+---------+----------+|NULL|0|+---------+----------+1rowinset(11.69sec)实际执行的情况非常的糟糕.传通的想法,这个执行从索引上执行计划上看非常完美了,好象和MySQL没什么关系了.在去看一下表的设计会发现client\_id也是设计成了varchar(255).看到这里不防可以使用下面的方法试一下:

再来看实际执行:

看到这里在想什么呢,记住这个案例,嗯,不错,以后还可以加引号优化一下.那为什么不问一下,能不能在优化了,为什么会这样呢?

我们先来看一下第一个问题：

能不能在优化了?

答案是当然可以了.从索引的长度上来看258还是一个非常大的数据,对于client\_id这个字段从名字上来看,也只会存数据型的值,那为什么不用的一个intunsigned去存呢,索引的长度马上会从258降到4。这样不是又节省了很多吗？

接下来看一下第二个问题，为什么会这样呢？

原因有两点，同时基于一个原则，基于成本的优化器。对于client\_id在表的定义时定义成了字符型的值，在查询时传入了数值型的值，需要经过一个数值转换，悲剧的开始，最终导致MySQL选择了一个完成的索引去扫描。

从这个案例上，我们需要注意什么呢？

合理的选择数据类型，基本工太重要了，就这叫赢在起跑线，一切都不能随便了，别把一个表定义成了降了主建外其它全是Varchar(255)。对数据库的double/float这种字段做索引时一定要小心。

### MySQL性能如何优化？ ###

六：结语

这是活动目录学习系列的其中一篇，现在越发感觉活动目录的强大与高深，我会继续学习，争取给大家分享有用的东西。过年这段时间由于线上数据库经常压力过大导致响应非常缓慢甚至死机，咬咬牙下大决心来解决效率不高的问题!

首先是由于公司秉承快速开发原则，频繁上线，导致每次忽视了性能问题!日积月累，所以导致系统越来越慢，所以如果你的系统查询语句本来就优化的很好了可能参考意义不大!

提取慢查询日志文件，应该在你的DataDir目录下面

通过程序处理慢查询文件，将文件格式的慢查询导入到数据库中：

1 mysql> desc slow\_query;

2 +---------------+-------------+------+-----+---------+-------+

4 +---------------+-------------+------+-----+---------+-------+

6 | user | varchar(64) | NO | | | |

7 | host | varchar(64) | NO | | | |

9 | query\_time | int(11) | NO | | | | 查询所用时间，直接性能指标

10 | lock\_time | int(11) | YES | | 0 | | 等待锁定的时间

11 | rows\_sent | int(11) | YES | | 0 | | 返回的结果行数

12 | rows\_examined | int(11) | YES | | 0 | | 扫描行数(很重要，上万以后就要重点注意了

14 +---------------+-------------+------+-----+---------+-------+

然后发挥您的想象力在这个表中尽力捕捉你想捕捉的，那类型语句压力最大、扫描行数最多、等锁最久……

优化后：

1 mysql> select sum(query\_time)/count(\*),count

2 (\*),sum(query\_time),min(Date),Max(Date) from slow where Date>'2008-02-20 22:50:52' and Date 7233 | 2197 | 12574 | 2008-02-20 22:51:16 | 2008-02-21 17:34:10 |

7 +--------------------------+----------+-----------------+---------------------+---------------------+

8 1 row in set (0。

![mysql执行器优化器\_mysql执行器优化\_02][mysql_mysql_02]

### 如何优化MySQL数据库性能？ ###

使用 UNION 来创建查询的时候，我们只需要用 UNION作为关键字把多个 SELECT 语句连接起来就可以了，要注意的是所有 SELECT 语句中的字段数目要想同。下面的例子就演示了一个使用 UNION的查询。

SELECT Name, Phone FROM client

UNION

SELECT Name, BirthDate FROM author

UNION

SELECT Name, Supplier FROM product

4、事务

尽管我们可以使用子查询(Sub-Queries)、连接(JOIN)和联合(UNION)来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。

### Mysql数据库优化 ###

1适当建立索引

2对表进行水平划分

3选择适当的字段类型，特别是主键

4文件、图片等大文件用文件系统存储，不用数据库

5外键表示清楚，方便建立索引

6宁可集中批量操作，避免频繁读写

7选择合适的引擎

8SQL语句优化。从外在条件来说，优化mysql涉及优化硬件、优化磁盘、优化操作系统、选择应用编程接口等。

二、优化硬件

如果你需要庞大的数据库表(2g)，你应该考虑使用64位的硬件结构，像alpha、sparc或即将推出的ia64。因为mysql内部使用大量64位的整数，64位的cpu将提供更好的性能。

对大数据库，优化的次序一般是ram、快速硬盘、cpu能力。

更多的内存通过将最常用的键码页面存放在内存中可以加速键码的更新。

如果不使用事务安全(transaction-safe)的表或有大表并且想避免长文件检查，一台ups就能够在电源故障时让系统安全关闭。

对于数据库存放在一个专用服务器的系统，应该考虑1g的以太网。延迟与吞吐量同样重要。

![mysql执行器优化器\_mysql执行器优化\_03][mysql_mysql_03]

### mysql 触发器如何优化速度 ###

关于mysql处理百万级以上的数据时如何提高其查询速度的方法

最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法。

由于在参与的实际项目中发现当mysql表的数据量达到百万级时，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。曾经测试对一个包含400多万条记录(有索引)的表执行一条条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。以下是网上流传比较广泛的30种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=10

union all

select 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(索引名)) where num=@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 where substring(name,1,3)=’abc’–name以abc开头的id

select id from t where datediff(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′ and createdate

查看全文

2018-11-16

触发器只有经事件触发后才能执行，上面对时间的判断在触发器里面，没有事件触发是不会运行的，这种情况一般利用mysql的事务调度器(event scheduler)和存储过程比较容易实现。具体如下：

事务调度器

create event \`del\_lclass\_tbl \` on schedule every 1 day starts '2009-05-11 01:00:00' on completion not preserve enable do call sp\_delete\_lclass\_tbl(null);

delimiter $

drop procedure if exists sp\_delete\_lclass\_tbl;$

create procedure sp\_delete\_lclass\_tbl()

begin

delete from lclass\_tbl where lclass\_nstatus in (1,3)

and lclass\_nexecuteid in (select ppi\_nid from planpracticeitem\_tbl where ppi\_sapplicationenddate < now());

end;$

delimiter ;

[mysql_mysql]: https://img0.baidu.com/it/u=1203675681,479220869&fm=26&fmt=auto
[mysql_mysql_02]: https://img1.baidu.com/it/u=3731881900,3007654160&fm=26&fmt=auto
[mysql_mysql_03]: https://img-blog.csdnimg.cn/img_convert/0179f2bad3b3291e40226acf2a75d940.png