SQL优化

这周，公司的DBA为我们做了SQL优化的简单培训，听了觉得受益匪浅，这里记录了主要内容。

 

1. 不用select *, 只select需要的字段

 

2. >,<,>=,<=

   >=3 的效率要高于>2

 

3. where email like 'A%' 的效率要高于 email like '%A%'

 

mysql> explain select count(*) from User where email like 'A%';
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | User  | range | IDX_User_email | IDX_User_email | 258     | NULL |  112 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> explain select count(*) from User where email like '%A%';
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | User  | index | NULL          | IDX_User_email | 258     | NULL | 3745 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

 

4. UNION 与 UNION ALL
     UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
     UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
如果明确知道两个查询不会有交集，使用UNION All

 

5. IS NULL和 IS NOT NULL  在查询中将不会使用index

 

mysql>  select  sql_no_cache  count(*) from Interview i where   TRIM(i.Email) != '' and i.Email IS NOT NULL;
+----------+
| count(*) |
+----------+
|      409 |
+----------+
1 row in set (2.07 sec)

mysql>  select  sql_no_cache  count(*) from Interview i where  i.Email>'';
+----------+
| count(*) |
+----------+
|      409 |
+----------+
1 row in set (0.00 sec)

Other example ：and length(trim(c.Address1)) > 0 and v.confid is not null and v.confid != 0

| ConfID             | int(11)      | YES  |     | NULL    |                |

and c.Address1 > '' and v.confid>0

 

6. Expression

 mysql> explain select * from User where id+1=59938552;

 mysql> explain select * from User where id=59938551;

 where salary/2=5000

 

7. Function

 where Year(v.VisitDate) = Year(now()) and Month(v.VisitDate) = Month(now())
 VisitDate>=DATE_FORMAT(now(),'%Y-%m-01 00:00:00') and VisitDate< DATE_FORMAT(date_add(now(), interval 1 month),'%Y-%m-01 00:00:00');

 

8. IN，EXISTS和 Inner Join

 mysql>   explain select id from Business where NodeGroup in (select value from NodeGroup where name='NodeGroup');

 

 mysql> explain select id from Business where   exists (select 1 from  NodeGroup where Business.NodeGroup =value and name='NodeGroup');

 

Exists can use for subquery get large result set。

 

mysql> explain  select b.id from Business b inner join NodeGroup n on b.NodeGroup =n.value
            where  n.name='NodeGroup';