SQL优化对比与总结

19000000

b表
SELECT * from b  where pid=115  
//没有索引，4.245S
//有索引，0.019S

//有索引查询快



SELECT * from b  where pid=11
//4000000，20.454S

SELECT id,pid from b  where pid=11
//4000000，2.506S  //因为ID已经在PID的索引数据里，所以快


SELECT id,taNo from b  where taNo='ta5'
//taNo,加是索引
//4000000，2.506S  //因为ID已经在PID的索引数据里，所以快



SELECT id,pid,taNo from b  where pid=11
SELECT id,pid,taNo from b  where pid=11
//4000000，27.773S
//taNo,pid加索引，但要回表查询，所以速度会慢点




SELECT id,pid,taNo from b  where id='19660782' AND taNo='ta5'
//1,0.020S



SELECT id,pid,taNo from b  where   pid like '%9%'  AND  taNo ='ta4'
//4000000，35.773S


SELECT id,pid,taNo from b  where   pid like '9%'  AND  taNo ='ta4'





SELECT
b.* ,c.*
FROM
b
LEFT JOIN c ON c.codeName=b.taNo

//139.989S,,b=19000000 ,C=5

SELECT
b.* ,c.*
FROM
c
LEFT JOIN b ON c.codeName=b.taNo
//228.862S,,b=19000000 ,C=5

//大表join小表会好点




SELECT
b.* ,c.*
FROM
b
LEFT JOIN c ON c.codeName=b.taNo
where b.taNo = 'ta1'

//4000000,37.908S,,b=19000000 ,C=5


SELECT b1.* ,c.* from (
SELECT
b.*
FROM
b
where b.taNo = 'ta1'
)b1
LEFT JOIN c ON c.codeName=b1.taNo

//4000000,46.495S,,b=19000000 ,C=5
//join后再条件过滤会快点




SELECT
DISTINCT
b.tano
FROM
b
LEFT JOIN c ON c.codeName=b.taNo
where b.taNo = 'ta1'

//3.223S


SELECT
b.tano
FROM
b
LEFT JOIN c ON c.codeName=b.taNo
where b.taNo = 'ta1'
GROUP BY b.tano
//2.074

//GROUP BY比DISTINCT快点


test