返回首页 2018-12

mysql查询优化(不定时更新)

2018-12-28 23:30:03 评论: 1
  1. 不要select *,不要查询不需要的列

  2. 不要在多表关联返回全部的列

  3. 不要重复查询,应当写入缓存

  4. 尽量使用关联查询来替代子查询

  5. 尽量使用索引优化。如果不使用索引。mysql则使用临时表或者文件排序。如果不关心结果集的顺序,可以使用order by null 禁用文件排序

  6. 优化分页查询,最简单的就是利用覆盖索引扫描,而不是查询所有的列

  7. 应尽量避免在 where 子句中使用 !=或<> 操作符,否则将引擎放弃使用索引而进行全表扫描

  8. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引

  9. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

    select * from user where name is null
  10. 尽量不要使用前缀%,如:

select * from user where name like '%a'
  1. 很多时候用 exists 代替 in 是一个好的选择

  2. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描

  3. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,所以表设计的时候应该尽量避免字段为空,尽可能设默认值,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0;

  4. in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3;

  5. 在数据量很大的情况下,查询单条数据,适当的使用LIMIT 1对查询操作的优化效果还是相当明显的;

索引相关

索引并不是越多越好,索引固然可 以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

索引的优点
  1. 减少服务器扫描表的次数
  2. 避免排序和临时表
  3. 将随机io变成顺序io
建索引的几大原则
  1. 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

  2. =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式

  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录

  4. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

  5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

不能使用索引的情况
  1. 查询使用了两种排序方向

    select * from user where login_time > '2018-01-01' order by id des ,username asc
  2. order by中含有了一个没有 索引的列

    select * from user where name = '11' order by age desc; //age 没有索引
  3. where 和 order by 无法形成最左前缀

    • 索引列的第一列是范围条件
    • 在索引列上有多个等于条件,这也是一种范围。不能使用索引
更多