搜索
查询优化学习心得Mysql性能优化有两个方面，一个是逻辑查询，一个是物理查询。逻辑查询主要从连接的等价转换，连接消除，条件下推等方式对语句进行简化。子查询是逻辑优化过程中需要重点关注的对象，子查询用的不好，往往会造成过多的表扫描。关于子查询的优化，需要注意以下几点：１，在执行计划中出现subQuery,DepentedSubQuery的查询方式，说明子查询语句不能优化，查询语句按默认从内外往执行。先执行子查询语句，得到一个中间结果集，再执行外层语句。这种情况主要出现于非spj的子查询语句中，比如含有distinct,groupby聚合函数，orderbylimit,union.和相关子查询。２，可以进行优化的子查询语句表现为嵌套循环NestedLoop算法,连接方式可以是内联接(innerjoin)或者是半联接(anti-semi-join)如果说在mysql中，内联接的NestedLoop属于子查询展开的话，那么半联接类型的子查询一般是不能够展开的，属于半联接的运算符有exists,notexists，in,notin,半联接是一种查找算法，只要查找到等值数据立刻退出本次循环，notexists,notin则不同，需要全表扫描。对于嵌套循环NestedLoop，mysql的一种优化方式是Materialized,对子查询语句先进行物化，再与外表进行NestedLoop在执行计划中，看到derived的查询类型，说明在NestedLoop中，该表作为驱动表，根据NestedLoop运算符，先遍历驱动表的每一行，针对每一行记录，再到内表中查找对应的记录。驱动表为全表扫描的外表。3,对于循套嵌套Nestedloop算法，驱动表的索引并不重要，但查找的内表字段最好有索引，如果有索引，子查询将会优化成简单查询（simple），也许，这就是子查询最优的解法吧（转成了简单查询）.4,all,any,some运算符虽然是ansi联接标准，但是由于这些运算符在语义上并不直观，而且，这些运算符在查询优化阶段也会被转换成对应max,min运算符。所以实际并不常用。所以这类运算符也不能被优化。=all,=some,=any属于等值联接，它们可以被优化。在sqlserver中，子查询都转换为嵌套循环NestedLoop.其性能受NestedLoop算法约束。5，在实际生产环境中，最常见的子查询优化是notin,notexists子查询，一般会将其转换成leftjoin+wherecolumnisnull。再一个就是注意子查询和父查询的相应索引的使用。当然，并非子查询就是不好的:对于两个表都比较小，使用子查询，较低的查找开销比联结更优胜.匹配只返回一个值，使用子查询，与必须联结整张表相比，只找录一条记录并替换它花费的开销要少很多匹配只返回相当少的值，而且查询列上没有索引，使用子查询，通常，单独的一次或者甚至数次查找所花费的开销都比散列联结少另，子查询也是必不可少，很多业务逻辑必须要用子查询来实现。一些实验的例子：相关子查询select*fromt4wheret4.id4=any(selectt5.id5fromt5wheret5.b5=t4.b4);非相关子查询select*fromt1wherea1=any(selecta2fromt2wheret2.a2=10);IN操作符SELECTcolumn_name(s)FROMtable_nameWHEREcolumn_nameIN(value1,value2,...)select*fromt4wheret4.id4in(1,3);ALLANYSOME操作符select*fromt5wheret5.id5any(selectid4fromt4wheret4.id45);select*fromt5wheret5.id5ALL(selectid4fromt4wheret4.id45);EXISTS操作符select*fromt5whereexists(select*fromt4wheret4.id4t5.id5);转化成同义的IN语句：select*fromt5whereid5in(selectid5fromt4wheret4.id4t5.id5);SPJ查询：选择：select*fromt4wheret4.id4=1;投影:selectid4,b4fromt4;连接：select*fromt4,t5wheret4.id4=t5.id5;groupby子查询：SELECTCustomer,SUM(OrderPrice)FROMOrdersGROUPBYCustomerTOP-N:SELECTTOP50PERCENT*FROMCustomers;mysql不支持等价于Mysql中select*fromt4limit10;LIMIT&orderby:select*fromt4orderbyid4desclimit2,5;selectn1,n2,n3fromtainnerjointbonta.n1=ta.n2现有表anamehota12b10c15表b:namehotd12e10f10g8表a左连接表b，查询hot相同的数据selecta.*,b.*fromaleftjoinbona.hot=b.hot查询结果：namehotnamehota12d12b10e10b10f10c15nullnull从上面可以看出，查询结果表a的列都存在，表b的数据只显示符合条件的项目再如表b左连接表a，查询hot相同的数据selecta.*,b.*frombleftjoinaona.hot=b.hot查询结果为：namehotnamehotd12a12e10b10f10b10g8nullnull再如表a右连接表b，查询hot相同的数据selecta.*,b.*fromarightjoinbona.hot=b.hot查询结果和上面的bleftjoina一样优化的另一个方面是物理查询。在物理查询方面，主要影响性能的地方是IO和cpu使用资源。对于优化物理查询，有一些建议：1,在保证实现功能的基础上，尽量减少对数据库的访问次数(可以用缓存保存查询结果，减少查询次数)；2,通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；3,能够分开的操作尽量分开处理，提高每次的响应速度；4,在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；5,算法的结构尽量简单；6,在查询时，不要过多地使用通配符如SELECT*FROMT1语句，要用到几列就选择几列如：SELECTCOL1,COL2FROMT1；7,在可能的情况下尽量限制尽量结果集行数如：SELECTTOP300COL1,COL2,COL3FROMT1,因为某些情况下用户是不需要那么多的数据的。在没有建索引的情况下，数据库查找某一条数据，就必须进行全表扫描了，对所有数据进行一次遍历，查找出符合条件的记录。在数据量比较小的情况下，也许看不出明显的差别，但是当数据量大的情况下，这种情况就是极为糟糕的了。所以，优化查询最重要的就是，尽量使语句符合查询优化器的规则避免全表扫描而使用索引查询。具体要注意的：1.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenumisnull可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：selectidfromtwherenum=02.应尽量避免在where子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。3.应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：selectidfromtwherenum=10ornum=20可以这样查询：selectidfromtwherenum=10unionallselectidfromtwherenum=204.in和notin也要慎用，因为IN会使系统无法使用索引,而只能直接搜索表中的数据。如：selectidfromtwherenumin(1,2,3)对于连续的数值，能用between就不要用in了：selectidfromtwherenumbetween1and35.尽量避免在索引过的字符数据中，使用非打头字母搜索。这也使得引擎无法利用索引。关于物理查询的优化，重点放在索引的创建上。索引对查询的速度有着至关重要的影响，索引也是进行数据库性能调优的起点。比如，数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。当应用程序进行SQL查询速度很慢时，想想是否可以建索引。MySQL支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。(2)简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。(3)尽量避免NULL：应该指定列为NOTNULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。索引的特性：1,如果对多列进行索引(组合索引)，MySQL仅能对索引最左边的前缀进行有效的查找。比如：存在组合索引it1c1c2(c1,c2)，查询语句select*fromt1wherec1=1andc2=2能够使用该索引。查询语句select*fromt1wherec1=1也能够使用该索引。但是，查询语句select*fromt1wherec2=2不能够使用该索引，因为没有组合索引的引导列，即，要想使用c2列进行查找，必需出现c1等于某值。建立索引的原则：1.合理使用索引一个Table在一次query中只能使用一个索引,使用EXPLAIN语句来检验优化程序的操作情况使用analyze帮助优化程序对索引的使用效果做出更准确的预测2.索引应该创建在搜索、排序、归组等操作所涉及的数据列上3.尽量将索引建立在重复数据少的数据列中，唯一所以最好例如：生日列，可以建立索引，但性别列不要建立索引4.尽量对比较短的值进行索引降低磁盘IO操作，索引缓冲区中可以容纳更多的键值，提高命中率如果对一个长的字符串建立索引，可以指定一个前缀长度5.合理使用多列索引如果多个条件经常需要组合起来查询,则要使用多列索引(因为一个表一次查询只能使用一个索引,建立多个单列索引也只能使用一个)6.充分利用最左前缀也就是要合理安排多列索引中各列的顺序，将最常用的排在前面7.不要建立过多的索引只有经常应用于where,orderby,groupby中的字段需要建立索引.8.利用慢查询日志查找出慢查询(log-slow-queries,long_query_time)使用索引的一些建议：1.尽量比较数据类型相同的数据列2.尽可能地让索引列在比较表达式中独立,WHEREmycol4/2使用索引,而WHEREmycol*24不使用3.尽可能不对查询字段加函数,如WHEREYEAR(date_col)1990改造成WHEREdate_col’1990-01-01’WHERETO_DAYS(date_col)-TO_DAYS(CURDATE())cutof