SQL优化原理及实践

[TOC]

数据库作为系统数据存储组件是应用不可缺少的一部分。对数据库操作的的SQL性能对程序的影响也是很大的。这篇博客主要是综合网上的资料,尝试系统的阐述SQL优化的原则及实践方式。囿于笔者的技术水平,有出入之处还请海涵和指出。文末附有参考文章,感谢作者的付出。

一、优化原则

IO性能

从图上可以看到基本上每种设备都有两个指标:

延时(响应时间):表示硬件的突发处理能力;

带宽(吞吐量):代表硬件持续处理能力。

从上图可以看出,计算机系统硬件性能从高到代依次为:

CPU——Cache(L1-L2-L3)——内存——SSD硬盘——网络——硬盘

所以优化的效果是反向的,减少磁盘的访问量,会显著提升系统性能。

优化原则受益

优化法则 性能提升效果 优化成本
减少数据访问 1~1000
返回更少数据 1~100
减少交互次数 1~20
减少服务器CPU开销 1~5
利用更多资源 @~10

这个优化法则归纳为5个层次:

  1. 减少数据访问
    • 创建并正确的使用索引
    • 优化SQL执行计划
  2. 返回更少的数据
    • 分页
    • 只返回必要的字段(不使用 select * ;拆分大字段)
  3. 减少交互次数
    • 使用JDBC的batch方法处理
    • 用in合并多条sql
    • 适当的使用存储过程
  4. 减少CPU运算
    • 使用绑定变量(JDBC中的PreparedStatement)
    • 谨慎的进行大记录集排序
    • 减少比较操作
      • 用搜索代替like
      • 不能使用索引定位的大量In List
    • 尽量不要在数据库做运算:cpu计算请移至业务层.
  5. 利用更多资源
    • 客户端多进程访问
    • 数据库并行处理

二、实践

表设计

  • 控制单表数据量:单表记录控制在1000w行.(也可以考虑分区)
  • 控制列数量:单表字段数上限控制在20到50之内.字段少而精可以提高并发,IO更高效 (优化InnoDB表BLOB列的存储效率)
  • 平衡范式与冗余:效率优先,提高性能. 适时牺牲范式增加冗余
  • MySQL :使用业务无关的自增主键

尽量使用索引

1 谨慎合理的添加索引

索引能改善查询的效率,但是也会增加额外的开销并减慢更新的速度(更新时会同时更新索引). 索引的数量不是越多越好,能不加的索引尽量不加. InnoDB的secondary index(非主键索引)使用b+tree来存储,因此在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整,过多的索引会减慢更新的速度.

按照目前的业务需求,单表的索引应符合下列要求:

  • 索引数量控制在5个左右,单个索引的字段不超过5个.在设计的时候要结合SQL和需求考虑索引的覆盖.
  • 唯一键由3个以下字段组成,当字段都是整形时,使用唯一键作为主键.
  • 唯一键不和主键重复,即不得在主键上建唯一索引.
  • 较长的字段需加入前缀索引来减少索引长度,提高效率.

例子如下:

1
create table url( address VARCHAR(100) NOT NULLindex idx_url(address(10)));

前缀索引长度依据索引的覆盖率来定,建立索引之前最好查看下对应字段建立索引的概率.MySQL5.6优化了合并索引,也就是说一条SQL上可以使用两个索引了.

2 提高索引的覆盖率

合理利用覆盖索引.

关于覆盖索引:InnoDB 存储引擎中,secondary index(非主键索引)中没有直接存储行地址,而存储主键值. 如果用户需要查询secondary index中所不包含的数据列时,需要先通过secondary index查找到主键值,然后再通过主键查询到其他数据列,因此需要查询两次. 覆盖索引的概念就是查询可以通过在一个索引中完成,覆盖索引效率会比较高,主键查询是覆盖索引.

合理的创建索引以及合理的使用查询语句,当使用到覆盖索引时可以获得性能提升.
比如SELECT email,uid FROM user_email WHERE uid=xxx,可以将索引添加为index(uid,email),以提升性能.

索引字段的顺序需要考虑字段值去重之后的个数,个数多的放在前面.
合理创建联合索引(避免冗余),(a,b,c相当于 (a、(a,b、(a,b,c). 遵循最左原则.
UPDATE、DELETE语句需要根据WHERE条件添加索引.

3 索引使用需要注意的事项

  • 不建议使用%前缀模糊查询,例如LIKE "%xxx",这样会扫全表.

  • 不要在索引列进行数学或者函数计算.

    1
    select * from table where id +1 =10000;

    这样不会使用索引,导致扫全表,改为:

    1
    select * from table where id =10000-1;
  • 使用EXPLAIN判断SQL语句是否合理使用索引,尽量避免extra列出现:Using File SortUsing Temporary.

    下面列出extra列常见的值:

    a. Using Temporary

    为了解决查询,MySQL需要创建一个临时表来容纳结果. 典型情况如查询包含可以按不同情况列出列的GROUP BYORDER BY子句. 使用临时表的开销是比较大的.

    b. Using File Sort

    MySQL需要额外的一次传递,以找出如何按排序顺序检索行. 出现这个说明SQL没有走索引. MySQL通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序.

    c. Using index

    从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息.

    d. Using where

    MySQL使用where条件进行过滤找到匹配行返回客户端.

  • 禁止使用外键. 因为会产生额外开销,并且是逐行进行操作. 最关键的是在高并发的情况下很容易造成死锁.

  • SQL变更需要确认索引是否需要变更,并通知DBA.

尽量使用查询缓存

执行流程

缓存始终是空间换时间的常用手段,对性能提升的效果十分显著。SQL在数据库中执行的流程如上图,请留意第②步,MySQL会检查传入的查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存,在检查一次用户权限后直接返回缓存中的结果。这种情况下,查询不会被解析,也不会生成执行计划,更不会执行。

注意的是,必须语句完全相同才能够命中缓存。如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表,其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间,返回不同的查询结果,再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果,将这样的查询结果缓存起来没有任何的意义。

1
2
3
4
5
6
// 不会开启查询缓存
SELECT username FROM user WHERE signup_date >= CURDATE()

// 使用变量绑定方式,会开启查询缓存
SELECT username FROM user WHERE signup_date >= ?
//setDate(new Date())

其他

  • MySQL选择合适的数据库引擎,参考酷壳文章

    参考


  1. 面向程序员的数据库访问性能优化法则
  2. mysql开发规范
  3. 万字总结:学习MySQL优化原理,这一篇就够了!
  4. 酷壳:MYSQL性能优化的最佳20+条经验
  5. MySQL优化/面试,看这一篇就够了
  6. 这大概是最全的sql优化方案了
  7. 为什么InnoDB表要建议用自增列做主键