千万级大表的MySQL优化

 Database
 

SQL语句优化

  • 避免select *
    • 在解析的过程中，会将“*”依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
    • 应该养成一个需要什么就取什么的好习惯

  • ORDER BY语句优化

    • 任何在ORDER BY语句的非索引项或者有计算表达式都将降低查询速度
    • 方法：
      • 重写ORDER BY语句以使用索引
      • 为所使用的列建立另外一个索引
      • 绝对避免在ORDER BY子句中使用表达式

  • GROUP BY语句优化

    提高GROUP BY语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉（如下例，先用where语句过滤掉一部分数据）

    低效：

    1
    2
    3
    4
    5
    SELECT JOB, AVG(SAL)
    FROM EMP
    GROUP by JOB
    HAVING JOB = 'PRESIDENT'
    OR JOB = 'MANAGER'

    高效：

    1
    2
    3
    4
    5
    SELECT JOB, AVG(SAL)
    FROM EMP
    WHERE JOB = 'PRESIDENT'
    OR JOB = 'MANAGER'
    GROUP by JOB

  • 用exists代替in

    很多时候用exists代替in是一个好的选择，如

    1
    select num from a where num in(select num from b)

    用下面的语句替换：

    1
    select num from a where exists(select 1 from b where num=a.num)

  • 尽量使用varchar/nvarchar代替char/nchar

    • 变长字段存储空间小，可以节省存储空间
    • 对于查询来说，在一个相对较小的字段内搜索效率显然要高些

  • 能用DISTINCT的就不用GROUP BY

    1
    SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

    可改为：

    1
    SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

  • 能用UNION ALL就不要用UNION

    UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。（UNION ALL允许重复）

  • 在join表的时候使用相同类型的字段，并将其索引

    • 如果应用程序有很多join查询，应该确认两个表中join的字段是被建过索引的。这样，MySQL内部会启动优化join的SQL语句的机制
    • 被用来join的字段应该具有相同的类型。例如，如果把DECIMAL字段和一个INT字段join在一起，MySQL将无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行（两个表的字符集有可能不一样）

索引优化

  • 在适合建索引的地方建立索引（索引并非越多越好）
    • 适合建索引的情况
      • 主键会自动建立唯一索引
      • 频繁作为 where 条件语句查询的字段
      • 关联字段，例如外键字段
      • 排序字段，因为排序字段若通过索引去访问将大大提高排序速度（索引干两件事：
        检索和排序）
      • 分组字段，因为分组的前提是排序
      • 统计字段，例如 count()、max()
      • 高并发下倾向于创建组合索引，而不是单键索引。组合索引是由多个字段构成的索
        引，组合索引中的字段顺序是非常重要的，越是唯一的字段越是要靠前（根据最左
        前缀准则）。
    • 不适合建索引的情况
      • 表记录太少的情况下
      • 频繁更新的字段、经常增删改的字段，因为修改性能和检索性能是互相矛盾的，每
        次更新不仅会更新记录还会更新索引
      • where 条件中用不到的字段，在查询中很少使用或参考的列
      • 数据重复且分布比较均匀的字段，即唯一性太差的字段，例如性别、真假值
      • 参与计算的列
  • 避免有索引但未被用到的情况（索引失效时，MySQL 会进行全表扫描）
    • 无查询条件
    • 一些关键字会导致索引失效
      • where 语句中使用<>、!=、not in、not exist、is null、is not unll（使用>或
        <会比较高效）
      • where 语句中使用 or（注意：or 中所有的字段都加上索引才会使用索引）
      • 以通配符“%”开头的 like 查询（注意：以“%”结尾的 like 查询会使用索引）
      • 避免 where 条件不符合最左前缀原则，最左前缀原则：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 的顺序可以任意调整（IN 和=可以乱序）。（多列索引没有遵循最左
        匹配原则，未使用第一个）
      • 如果列类型是字符串，那一定要在条件中将数据用引号引起来,否则不使用索引
      • 隐式转换会导致索引失效，比如数据库中的 id 是 number 类型，在查询时却出现“……
        where id=”123””。
      • 对索引字段应用内部函数
      • 对索引字段进行运算
 Previous

Heap