mysql优化学习

  1. 云栖社区>
  2. 博客>
  3. 正文

mysql优化学习

wangtaotao 2011-02-22 22:21:00 浏览563
展开阅读全文

mysql手册sql优化学习:

BENCHMARK(1000000,1+1)

的理解:

执行右边表达式(1+1)1000000次。

该函数执行所返回的结果永远是0

英文参考理解:表达式必须是标量表达式。比如select 返回的是很多行,是一个结果集。那么执行该函数的时候并没有任何结果显示。失败了。返回的结果必须是单列或者是单行。


一、使用explain工具显示信息:

explain语句结果信息的使用:

id     select_type table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE        fanwe_goods     index     NULL          PRIMARY     4      
      NULL     267     Using index

一一解释如下:

key项目的值,就表示使用到了那个索引。上面。使用到了名为primary的索引,就是主键索引。

rows:rows列显示MySQL认为它执行查询时必须检查的行数。那么我可以理解:这个值的数越小越好。表示需要扫描的行数越少。
      从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。

      尽管我尝试使用了limit进行限制。但是explain查看这个结果还是267。说明:还是进行了267行检查。我以为不会检查这么多行数了。

type 表示联接的类型。值是index,表示用到了索引。all表示进行了全盘扫描。通过这个项目的值能够看出你的sql在执行了什么操作,可以评价性能如何

       type列内的index_merge该联接类型表示使用了索引合并优化方法

select_type 表示查询的类型。上面值是simple,表示只是简单的查询,没有用到联合查询。



Extra:Using where MySQL解决查询的详细信息



总的来说,要想使一个较慢速SELECT ... WHERE更快,应首先检查是否能增加一个索引

从explain可以看出mysql查询使用索引的规则:mysql查询每次只能使用一个索引






tt.AssignedPC = et_1.EMPLOYID 这两个表中的字段类型要保持一样。这样可以减少搜索的行数。通过explain看,rows项目中就是少了一个乘积。这样做所依据的原理是:

MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。也就是类型相同,尺寸相同比如都是char类型。长度都是10。这样,联合查询的时候就是使用索引。
注:在mysql查询优化中。VARCHAR和CHAR看成是相同的类型。接下来只要保证其长度相同就行了。



通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。


理解:如果是多个表join查询。可以查看每行显示的rows值,将他们乘积,这样就大概判断出你这个查询检查了多少行。










二、怎么优化where子句。


去除不必要的括号:
1.
                        ((a AND b) AND c OR (((a AND b) AND (c AND d))))
               -> (a AND b AND c) OR (a AND b AND c AND d)



实际测验:
0.0024 秒 都一样。没有太大区别。

体会:减少括号原理上的知识是,减少mysql查询优化器的解析括号的工作量。这与select使用索引的技巧是不同层面上的东西。

可以这样分类sql的优化工作:一是去除导致增加查询负担不必要的语法。二是,优化表,使之更加适合高效sql






索引合并方法的理解:
将多个结果并成一个。取交集或者取并集。这是mysql优化器算法上的东西。有好几种算法。索引合并交集访问算法。一种语句的不同,已经告诉了mysql查询优化器决定使用了何种查询算法。
。比如表现语句:

WHERE key_part1 = 10 OR key_part2 = 20;  将两个结果取并集

下面语句,一个是使用索引优化方式。一个是使用范围扫描方式。

1.(goodkey1 < 10 OR goodkey2 < 20)

2.badkey < 30


三、
DELETE语句的速度:
如果想要删除一个表的所有行,使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name




left join看着手册,可能翻译不那么准确。没有明确提到怎么做。猜出大概方向是将内连接尽量转化为普通连接的形式。这样就提高查询效率。

手册上提到:MySQL可以进行下面的LEFT JOIN优化:如果对于产生的NULL行,WHERE条件总为假,LEFT JOIN变为普通联接。
通过看上面提到的一个转换实例,将left join 类型的连接转化成了自连接形式(也就是去掉join形式,只有where)。

从这里是不是可以猜测,转化原则是下面的:
联合查询能够不用的,尽量不用。有些是可以使用where子句的形式得到同样的关联表的效果的。还记得,之前的选题系统查询都关联了3-5个表。没有使用join的形式,也达到的查询目标。



下面是将join查询转化为普通的where查询的实践报告:


1.使用left join:SELECT o.memo, o.create_time, u.user_name
FROM fanwe_order o, fanwe_user u, fanwe_order_goods g
WHERE o.user_id = u.id
AND o.id = g.order_id
AND g.rec_id =352
LIMIT 0 , 10
查询花费 0.0005 秒)


2.使用where的方式:
SELECT o.memo, o.create_time, u.user_name
FROM fanwe_order o, fanwe_user u, fanwe_order_goods g
WHERE o.user_id = u.id
AND o.id = g.order_id
AND g.rec_id =352
LIMIT 0 , 10
查询花费 0.0006 秒


结论:where的方式查询时间多了。这是小量数据查询。如果很大量的话,差异就会更大了。如果没弄清楚,都将join查询方式改为where去实现。效果变慢都不知道原因了。在没有把握确定之前,最好是自己测试两种方式,哪种的执行速度更快。
细节:join查询的方式,使用explain显示查询过程。显示的查询类型也是simple。


主要还是要理解left join的工作内部机制。这样就知道怎么去优化

调换两个表之间的顺序目的是减少对表的全盘扫描。带着这个目标去优化。它所依据的原理在手册上这样提到了:LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少

使用right join的比较少。宁愿将左右连接的两个表之间换个顺序,这样使用 left join方式进行代替。
根据手册上的示范进行理解如下sql:
a,b left join c 与 b,a left join c。显示的结果一样。但是效率不一样。手册上的大概意思是,第一种sql会在读取c表之前对b表进行全盘扫描。那么,第二种sql是不是使a表在c表之前扫描了呢?

从这里推测出一个规则:调换b的顺序就意味着,强制地改变了表的读取(扫描)顺序。

网友评论

登录后评论
0/500
评论
wangtaotao
+ 关注