sql 优化基础篇

1. 筛选字段上没有加索引的话，查找存在：用limit 1 代替  count(*)

  优化前方案：select count(*) as  cnt  from  表名  where 顾虑条件

 优化后方案：  select 1 as  cnt  from  表名  where 顾虑条件 limit 1

说明：不用 count，而是用 limit 1，当数据库查到一条就返回，不要再继续查找多少条了

sql 如下：

select * from  boys;
# +--+----+---+---+
# |id|name|sex|age|
# +--+----+---+---+
# |1 |慕容皝 |男  |22 |
# |2 |慕容冲 |男  |33 |
# |3 |慕容垂 |男  |44 |
# |4 |慕容博 |男  |55 |
# +--+----+---+---+
# 普通的sql实现
# 用count 实现

select count(*) from  boys where name = '慕容博';
# +--------+
# |count(*)|
# +--------+
# |1       |
# +--------+
# 在name 上加上索引后，查看执行计划
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+
# |id|select_type|table|partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra      |
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |ref |boys_name_index|boys_name_index|83     |const|1   |100     |Using index|
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+

# 不加索引 查看执行计划（全表扫描，扫了全部表的四行数据）
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |ALL |NULL         |NULL|NULL   |NULL|4   |25      |Using where|
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+


#  不存在
select count(*) from  boys where name = '乔峰';
# +--------+
# |count(*)|
# +--------+
# |0       |
# +--------+

# 优化方案  用 select 1   where  limit 代替
select 1 as cnt from boys where name = '慕容博' limit 1;
# +---+
# |cnt|
# +---+
# |1  |
# +---+
# name上加索引后查看执行计划
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+
# |id|select_type|table|partitions|type|possible_keys  |key            |key_len|ref  |rows|filtered|Extra      |
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |ref |boys_name_index|boys_name_index|83     |const|1   |100     |Using index|
# +--+-----------+-----+----------+----+---------------+---------------+-------+-----+----+--------+-----------+

# name上不加索引后查看执行计划
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |ALL |NULL         |NULL|NULL   |NULL|4   |25      |Using where|
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+

# 问题：如果查询  rownum 为 1 的数据：即 id 为 1 ，name为 慕容皝 的数据
 select 1 as cnt from boys where name = '慕容皝' limit 1;
# 查看执行计划 （name 未加索引）
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |id|select_type|table|partitions|type|possible_keys|key |key_len|ref |rows|filtered|Extra      |
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+
# |1 |SIMPLE     |boys |NULL      |ALL |NULL         |NULL|NULL   |NULL|4   |25      |Using where|
# +--+-----------+-----+----------+----+-------------+----+-------+----+----+--------+-----------+

 

2.  用 not exists 代替 not in （exitst 代替in）

没有女朋友的男生：sql 

not in 

select * from boys where id not in (select boy_id from girls) ;

 

  

select * from boys where not exists (select 1 from girls where girls.boy_id = boys.id) ;