2020-10-29

SQL优化

• 查询sql尽量不要使用，而是具体字段*

  反例：

  select * from tbl_user;
  

  正例：

  select id,username from tbl_user;
  

• 如果知道查询结果只有一条或者只要最大/最小一条记录，建议用limit 1

  反例：

  select id,username from tbl_user where username='admin';
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fCG5spYw-1603977876002)(img/31.png)]

  正例：

  select id,username from tbl_user where username='admin' limit 1;
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tY73QnaB-1603977876005)(img/32.png)]

  加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高。

• 应尽量避免在where子句中使用or来连接条件

  反例：

  select * from tbl_user where username='admin' or email ='admin@163.com';
  

  正例：

  select id,username from tbl_user where username='admin' 
  union all 
  select id,username from tbl_user where email ='admin@163.com';
  -- 或者分开两条sql语句
  select id,username from tbl_user where username='admin';
  select id,username from tbl_user where email ='admin@163.com';
  

• 优化limit分页

  反例：

  select id,username from tbl_user limit 1000000,100;
  

  正例：

  select id,username from tbl_user where id>1000000 limit 100;
  

• 优化like语句

  反例：

  select * from tbl_user where username like '%admin';
  

  正例：

  select * from tbl_user where username like 'admin%';
  

• 应尽量避免在where子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫描

  反例：

  select id,username from tbl_user where id=10
  

  正例：

  select id,username from tbl_user where id=1
  

• 对查询进行优化，应考虑在where及order by涉及的列上建立索引，尽量避免全表扫描。

  反例：

  select * from tbl_user where username ='admin' order by age;
  

  创建索引：

  create index index_username on tbl_user(username)
  

• 避免在where中对字段进行表达式操作

  反例：

  select id,username from tbl_user where id-1=2;
  

  正例：

  select id,username from tbl_user where id=3;
  

• 子查询优化

  反例：

  select tbl_userinfo.address from tbl_userinfo where user_id=(select id from tbl_user where username='admin')
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kFrJsMr0-1603977876007)(img/34.png)]

  正例：

  使用join查询优化，推荐使用inner join

  select tbl_userinfo.address from tbl_user inner join tbl_userinfo 
  on tbl_user.id=tbl_userinfo.user_id
  where username='admin'
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zeklNolY-1603977876010)(img/35.png)]

• 使用复合索引时，注意索引列的顺序，一般遵循最左匹配原则。

• 不要有超过5个以上的表连接

  • 连表越多，编译的时间和开销也就越大。
  • 把连接表拆开成较小的几个执行，可读性更高。
  • 如果一定需要连接很多表才能得到数据，那么意味着糟糕的设计了。

• 尽量用union all替换union

  union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表

• 索引不宜太多，一般5个以内。

  • 索引并不是越多越好，索引虽然提高了查询的效率，但是也降低了插入和更新的效率。
  • insert或update时有可能会重建索引，所以建索引需要慎重考虑，视具体情况来定。
  • 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否没有存在的必要。

• 索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段。

• 为了提高group by语句的效率，可以在执行到该语句前，把不需要的记录过滤掉。