十三、查询优化

from 型子查询

注意：内层from语句查到的是临时表，是没有索引的

所以：from的返回内容要尽量少，需要排序，在内层先排好序

强制索引

select id from areause index(primary)where pid=11 order by id desc limit 1;

count（）优化

误区：

1.myisam的count()查询非常快，但仅限于查询表的所有行，因为myisam对行数进行了存储，但是加了条件的查询，速度就不快了，

尤其是where条件的列上没有索引

select count(*) from goods;

select count(*) from goods where id>1000;较慢

可以优化为下面语句，因为查小于1000的较快

select (select count(*) from goods)-(select count(*) from goods where id<1000);

group by 

注意：

1.分组用于统计，而不用于筛选重复数据

适用于分组统计数据用，比较统计平均分，最高分

不适用于去重，让查询产生N多重复数据，比如1->N连接时，栏目-左连接->商品表，将会产生重复的行，用group去重效率很低

order by 列和group by列要一致，否则也会产生临时表

原因：group by 和 order by 都需要排序，所以如果2者的列不一致，那必须经过只是1次排序

用索引来避免临时表和文件排序

查看临时表

show status like "%tmp%”;

union查询会产生临时表

1.注意union的字句条件要尽量具体，查询更少的行

2.字句的结果在内存里并成结果集，需要去重，去重就需要拍下，而加all之后，不需要去重，

union尽量加all，可以在php中去重

limit翻页优化

limit offset,N,当offset非常大的时候效率及低，

原因是mysql并不是跳过offset行，单取N行。

而是取offset+N行，然后放弃前面offset行，返回N行，效率及低，当offset越大时，效率越低

例如select * from goods limit 10000,5，mysql会查出前面10005条，然后丢弃前面10000条，返回最后5条

优化办法：

1.从业务上去解决，不允许翻过100页

2.不用offset,条件查询，例如：select * from goods limit 10000,5;可以改成select * from goods where id>10000 limit 5;（问题，数据被物理删除过，有空洞，最终查询结果不准确）

3.先用索引覆盖查出id在连表查数据。select * from goods inner join 

(select id from goods limit 10000,5) as tmp on goods.id=tmp.id

in型子查询的误区

select goods_id,cat_id,goods_name from goods where cat_id in(select cat_id from category where parent_id=6);

误区：给我们感觉是，先查到内存的栏目，然后在外层cat_id in(1,4,5);

事实是，先goods表全扫描，并逐行与category表对照看parent_id=6是否成立

原因：mysql查询优化器，针对in型做优化，被改成了exists子查询的执行结果。当goods表越大，查询越慢

改进：改用连接查询代替子查询

select goods_id,g.cat_id,g.goods_name from goods as g 

inner join (select cat_id from category where parent_id=6) as t

using(cat_id) \G;

内层 select cat_id from category where parent_id=6用的parent_id索引返回数据在与外层连表，

虽然里层用到了临时表，但是临时表的数据只有几行