MYSQL查询优化心得..docx

查询优化学习心得Mysql性能优化有两个方面，一个是逻辑查询，一个是物理查询。逻辑查询主要从连接的等价转换，连接消除，条件下推等方式对语句进行简化。子查询是逻辑优化过程中需要重点关注的对象，子查询用的不好，往往会造成过多的表扫描。关于子查询的优化，需要注意以下几点：１，在执行计划中出现subQuery, Depented SubQuery的查询方式，说明子查询语句不能优化，查询语句按默认从内外往执行。先执行子查询语句，得到一个中间结果集，再执行外层语句。这种情况主要出现于非spj的子查询语句中，比如含有distinct ,group by 聚合函数，order by limit,union.和相关子查询。２，可以进行优化的子查询语句表现为 嵌套循环Nested Loop算法,连接方式可以是内联接(inner join)或者是半联接(anti-semi-join)如果说在mysql中，内联接的Nested Loop属于子查询展开的话，那么半联接类型的子查询一般是不能够展开的，属于半联接的运算符有exists,not exists，in,not in,半联接是一种查找算法，只要查找到等值数据立刻退出本次循环，not exists,not in则不同，需要全表扫描。对于嵌套循环Nested Loop，mysql的一种优化方式是Materialized,对子查询语句先进行物化，再与外表进行Nested Loop在执行计划中，看到derived的查询类型，说明在Nested Loop中，该表作为驱动表，根据Nested Loop运算符，先遍历驱动表的每一行，针对每一行记录，再到内表中查找对应的记录。驱动表为全表扫描的外表。3,对于循套嵌套Nested loop算法，驱动表的索引并不重要，但查找的内表字段最好有索引，如果有索引，子查询将会优化成简单查询（simple），也许，这就是子查询最优的解法吧（转成了简单查询）.4,all,any,some运算符虽然是ansi联接标准，但是由于这些运算符在语义上并不直观，而且，这些运算符在查询优化阶段也会被转换成对应max,min运算符。所以实际并不常用。所以这类运算符也不能被优化。=all,=some,=any 属于等值联接，它们可以被优化。在sqlserver中，子查询都转换为嵌套循环Nested Loop.其性能受Nested Loop算法约束。5，在实际生产环境中，最常见的子查询优化是not in,not exists子查询，一般会将其转换成left join + where column is null。再一个就是注意子查询和父查询的相应索引的使用。当然，并非子查询就是不好的:对于两个表都比较小，使用子查询，较低的查找开销比联结更优胜.匹配只返回一个值，使用子查询，与必须联结整张表相比，只找录一条记录并替换它花费的开销要少很多匹配只返回相当少的值，而且查询列上没有索引，使用子查询，通常，单独的一次或者甚至数次查找所花费的开销都比散列联结少另，子查询也是必不可少，很多业务逻辑必须要用子查询来实现。一些实验的例子：相关子查询select * from t4 where t4.id4 = any ( select t5.id5 from t5 where t5.b5 = t4.b4);非相关子查询 select * from t1 where a1 = any (select a2 from t2 where t2.a2 = 10); IN 操作符 SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)select * from t4 where t4.id4 in (1,3); ALLANYSOME操作符select * from t5 where t5.id5 any ( select id4 from t4 where t4.id45);select * from t5 where t5.id5 ALL ( select id4 from t4 where t4.id45);EXISTS操作符select * from t5 where exists ( select * from t4 where t4.id4t5.id5);转化成同义的IN语句： select * from t5 where id5 in ( select id5 from t4 where t4.id4t5.id5); SPJ查询：选择：select * from t4 where t4.id4=1; 投影: select id4, b4 from t4; 连接：select * from t4, t5 whe