mysql优化学习

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的顺序就意味着，强制地改变了表的读取(扫描)顺序。