01 Sql 执行顺序
(1) FROM 子句 组装来自不同数据源的数据
(2)WHERE 子句 基于指定的条件对记录进行筛选
(3) GROUP BY 子句 将数据划分为多个分组
(4)使用聚合函数进行计算
(5)使用 HAVING 子句筛选分组
(6)计算所有的表达式
(7)使用 ORDER BY 对结果集进行排序
(8)select 获取相应列
(9) limit 截取结果集
1.1 相应 Case
需求: 查询今日增长数据(根据 video_id 去重)
错误: select * from tb where date(created_at) = current_date() group by video_id
正确: select * from (select * from tb group by video_id ) as tb1 where tb1.date(created_at) = current_date()
错误原因: group by 操作在 where 后执行. 所以, 第一个语句 是查询今日去重后数据, 去重是在今日抓取的数据中去重. 而且我们的需求是, 对表中所有数据去重, 然后获取今日新增长的的数据
02 索引基础
2.1 BTree 索引
MySQL 默认存储引擎 innodb 只显式支持 B 树索引, 对于频繁访问的表, innodb 会透明建立自适应 hash 索引,
即在 B 树索引基础上建立 hash 索引, 可以显著提高查找效率, 对于客户端是透明的, 不可控制的, 隐式的.
B Tree:[更为细致的B树索引讲解请参考文末4.2延伸阅读 ]
m 阶 B 树的特性: 树中每个节点至多包含 m 棵子树 若根节点不是叶子节点, 则至少包含两颗子树 除根以外的所有非终点节点至少有 (m/2)向上取整棵子树 支持范围查询, 前缀匹配查询, 等值查询, 可以避免排序, 例如 order by index 相关的列, 排序会非常快, 因为该列本身就是 有序存储的, 查找时间复杂度 log m N(m 为底, N 的对数, N 为总记录数)
2.2 Hash 索引特性
只支持包括 "=" "in" 在内的等值查询, 不支持范围, 前缀匹配查询
Hash 索引是通过 hash 函数将, 键值直接映射为物理存储地址, 使时间复杂度降低到 O(1). 本身存储是无序的, 所以不能通过 hash 索引避免排序
2.3 MySQL 聚簇索引
B - 树和 B + 树的区别在于 B + 树所有键值全部保存在叶子节点, 而 B - 树则不然, B - 树的键值根据树的结构分布在整个树上. 而 MySQL 为什么要采用 B + 树索引呢?
1. 遍历方便. B + 树可以将键值保存在 (线性表[数组或链表] ) 中, 遍历线性表比索引树要快, 因为保存在线性表中数据存储更加密集, B-Tree 分散的
存储会导致更多的随机 I/O, 对于磁盘访问, 随机 I/O 是比顺序 I/O 慢很多的, 因为随机 I/O 需要额外的磁头寻道操作. 顺序 I/O 有效减少寻道的次数
2. 插入更新索引树时可以避免移动节点.
3. 遍历任何节点的时间复杂度相同, 即访问路径总是从根节点到叶子节点. 相比 B - 树, 访问时间略长. 所以某些高频访问的搜索采用 B - 树, 即访问频率越高
使其距离根节点越近.
4.(也许是最重要的)范围查找方便. 对于 [A,B] 区间的范围查找, B - 树索引可以直接找到 A,B 对应的线性表中节点, 只需要返回区间的所有节点
即为目标结果. 而 B - 树则稍显麻烦需要继续遍历索引树.
聚簇索引: 将表中一条记录存储在索引的叶子节点中 (也可能保存记录的物理地址[可能是磁盘或者扇区号也可能是文件名及对应偏移量] 的指针, 如果在内存中即为内存地址). 一般情况下 MySQL 中使用主键 做聚簇索引
一个表只能有一个聚簇索引.(一条记录物理存储只有一份)
非聚簇索引中叶子节点的记录中需要保存主键, 如需访问记录中其他部分还需要, 通过主键回表查询. 即两次索引查找? 有人疑问非聚簇索引中为什么不保存记录项的物理地址呢? 当然可以记录物理地址, 但是主键索引更新操作带来的索引分裂合并会改变其物理地址, 这样索引的维护代价比较大, 而即使回表查询, 主键查找速度一般较快, 影响不大. 另外也可以通过覆盖索引[即索引项覆盖了 select 中的项] 避免回表查询
5. 访问聚簇索引速度应该保证足够快, 主键不宜选择过大存储需求的字段, 例如 UUID, 另外非聚簇索引需要额外保存主键, 主键太长存储需求较大.
也不宜选择字符串: 一. 字符串比较速度较数字慢, 二. 字符串插入时更加无序, 索引树分裂合并相对更加频繁, 出现更多磁盘碎片 . 当有字符串和数字都能满足代理主键[该主键与业务无关只是添加一列主键保证记录唯一性] 需求时, 应当优先选择数字
做主键, 但是如果逻辑主键[业务中有作为主键的列, 也可选为主键, 即为逻辑主键] 是字符串类型, 那也应该选择其作为主键, 因为字符串相比数字性能差别不是很大.
03 索引常见优化方法
3.1 不能使用索引, 不建议使用索引等常见误区
1. 数据类型为 Text,Blob 等大对象不能建立索引, 也不适合建立索引, 另外字段太长的字段不适合建立索引. 例如超长字符串. 会使索引树过大, MySQL 可能无法将其放入内存, 访问索引会带来过多的磁盘 I/O. 效率低下
2. 查询表达式索引项上有函数. 例如 date(created_at) = 'XXXX'等. 字符处理等. MySQL 将无法使用相应索引[查询表达式相应列不能使用函数, 但是右边的值可以使用函数, 例如 created_at <now() and created_at> current_date()
3. 一次查询 (简单查询, 子查询不算) 只能使用一个索引,
例如 where column1 = xxx and column2 = xxx order by column3
如果 column1 ,column2 列各有一个索引, 那么只能使用其中之一的索引,
具体使用哪个索引, 要看 MySQL 的统计信息, MySQL 执行计划中包括索引的选择, 具体的选择要看哪个的索引选择率更高[唯一值 / 总记录数 = 选择率, 0 < 选择率<=1 选择率越大, 说明给定一个值可以过滤更多的行, 即过滤性更高] . 但是 MySQL 的统计信息不是精确实时的. 所以可能存在使用 "错误的索引" 的情况, 这时可以强制使用某个索引
select * from tb1 as t use index(index_name) ......
但是强烈不推荐使用这种方式, 可以将其作为临时方案使用, 应该首先考虑优化索引设计, 例如, 上述 Case 就应该建立 (column1,column2,column3) 或(column2,column1,column3) 联合索引
where 后的查询表达式顺序不能决定使用哪个索引. 如 column1 =xxx and column2 = xxx, 但并不代表优先使用 column1 在前, column2 在后的联合索引. 使用哪个索引由相应索引项的选择率决定, 最终判定标准是: 扫描最少的行. 使用索引过滤尽可能多的行. 然后使用 where 中其他条件对 索引过滤后的结果集 一行行地判断 完成 where 条件过滤.
5. 修改过于频繁的列使用索引要慎重. 1s 几十次的修改就要注意了, 过于频繁的更新对于索引负担太重, 磁盘负载过重, 另外更新操作可能会锁住相关记录, 有死锁和事务超时可能. 但是该使就使. 这些问题可以通过分区分表或者缓存解决
6. 选择率低的列不适合建立索引. 如果索引项对应 cardinality 较小, 例如小于 10, 那么使用索引时就需要考虑是否有必要. 因为访问索引的代价可能比全表扫描还要高. 索引需要访问索引文件, 然后访问叶子节点, 拿到主键回表查询, 如果结果集比较大, 这个代价极可能大于全表扫描[全表扫描是顺序 I/O, 索引访问会涉及更多随机 I/O, 随机 I/O 比顺序 I/O 慢多了] . 业务中常见的状态列, 在设计之初, 这一列的 cardinality 基数[唯一值的个数] 即是固定的, 随着记录数增加, 选择率会越来越低, 索引效率反而越来越低. 可以考虑不建索引, 或者将其作为联合索引的第一项
7.MySQL 中对于唯一性检查即声明 unique 的列, 自动建立唯一性索引, 不需要再额外建立索引
8. 不应该对 where 中每一个查询条件都建立上索引, MySQL 只会使用其中一个索引, 过多的索引带来冗余, 导致一些索引被 "浪费", 同时 MySQL 在生成执行计划时, 需要考虑更多的索引, 给查询优化带来更多工作, 过多的索引还会给更新操作带来更沉重的索引维护代价. 应该简化索引设计. 同时利用联合索引满足多项条件的查询
9.Order By ,Group By 可以利用索引避免排序. 但是 存在 where 语句下 只能使用 where 查询中使用的索引, 例如 where 中使用了 (A,B,C,D) 联合索引的 A,B 项, 如果 order by , 或者 group by 中存在 C, 或者 (C,D) 即可使用联合索引, 如果 where 中没有使用索引, 那么即使 order by,group by 列中有索引也不能使用. 即优先根据 where 查询使用索引, 然后根据 where 中使用的索引再决定, order by,group by 是否可以 使用到索引
10. 当数据量达到千万级别以上, 索引本身就很大, 无法装入内存, 访问索引带来的磁盘随机 I/O 开销很大, 索引性能下降较快, 当并发量不大情况下, 建立分区表可有效提高速度, 因为分区表的索引结构是互相独立的, 可单独装入内存, 减少磁盘访问.
11. 更新删除时指定索引列[事务特性, 及隔离级别不熟悉同学请参考 延伸阅读 4.1] ,MySQL 在默认的事物隔离级别是序列化解决了幻读, 并且通过间隙锁, 多并发版本读提高了并发访问性能, 幻读是指: 一个事务中, 当用户查询一个范围中的结果时, 另一个事务执行了相应的插入删除操作, 导致两次查询结果不同, 少了或多了一些行, 就像幻象一样. MySQL 解决幻读有两种方案: 一. 对于查询 select 操作只是针对本事务开启时刻的 "镜像" 查询. 例如本事务开启后, 其他事务插入删除了相关数据并提交, 本事务是无法察觉的. 实现方式为 版本控制. 二. 更新删除[包括 select ............ for update ] 等写操作涉及到范围更新时, 如果查询条件 where 中存在索引, 即锁住索引树的相关键值段例如 更新 id 主键索引在 1-100 的数据, 那么它会锁住 1- 100 这些记录的 id 索引, 其他事务更新这个范围数据时, 会进入锁等待, 直到拥有锁的事务, 或者等待超时. 如果查询条件中不能使用索引, MySQL 为了实现序列化的隔离级别, 会对全表加锁, 任何写操作不能进行. 当并发写操作多, 事务时间长时, 会出现较多锁等待及等待超时事务. 需要通过添加索引, 及减小事务粒度或者降低 MySQL 默认隔离级别方式解决此类问题.
3.2 索引设计的几个 "原则"
1. 索引的设计应该与业务需求息息相关, 没有完美的索引设计, 只有满足需求的索引设计, 项目前期设计的索引不可能完美的满足后期的需求. 应随时根据业务合理取舍.
2. 索引设计应该优先照顾查询最为频繁, 或业务优先级高, 与用户相关的查询. 如果我们可以忍受, 那么可以不建索引
3. 使用短索引, 索引长度不宜过大, 利用 B Tree 的特性使用最左匹配查找高效利用索引第一列, 对选择率高的列索引, 使用覆盖索引避免回表查询
4. 及时删除不再使用的索引, 例如发现 (A,B) 不满足需求, 新加一项 (A,B,C) 即可删除旧索引(A,B)
3.3 联合索引的顺序问题
1. 联合索引设计时, 索引顺序是很重要的. 当联合索引中, 每一列的查询频率都相差不多时, 可以优先将选择率最高的列作为联合索引第一列, 这样第一列即可过滤更多列, 效率更高. 由于联合索引第一列可以单独使用, 例如联合索引 (column1,column2,column3,column4) 即可满足 where column1 =xxx 也可满足 where column1 = xxx and column2 = xxx and column3 =xxx and column4 = xxx 的需求, 这样不需要为第一列的独立查询额外建立单列索引
2. 使用部分前缀索引键, 按照联合索引声明顺序查询. 例如索引(A,B,C) 只能匹配 where A = x xx ,where A = XXX and B = xxx ,where A = xxx and B = xxx and C = xxx , 不能跳过前一列, 匹配后一列. 例如 where A = xxx and c = xxx 这时虽然可能也使用该索引, 但是只能使用一部分, 匹配 A 列, 而 B,C 列不能匹配.
3. 前缀匹配, 与范围匹配. BTree 索引可以使用前缀匹配, 例如 where A like "xxx%" , 使用前缀索引后, 就不能使用前缀列的后续索引列.
4.group by,order by 本质是对 where 查询出的结果集进行排序操作, 当待排序列匹配 where 中索引顺序时才可避免排序, 直接通过索引即可返回有序结果集, 例如我们需要将查询结果按照评分排名, 那么就可以考虑将 rank 列放在联合索引的最后一列.(X, ...... ,rank). 当查询结果比较大时, 可以考虑这样设计
5.limit 分页查询 .limit 使用时必须排序否则可能出现不同页返回重复数据的风险. limit 返回某一位置的给定偏移量的记录, 但是它的顺序依赖于存储位置顺序, 索引顺序, 所以分页时不同页会有出现重复数据的风险. limit 操作前需要添加 order by 进行排序. 由于访问非聚簇索引时, MySQL 有一个优化操作, 当访问非聚簇索引, 回表查询时, MySQL 会对主键进行排序, 目的是: 聚簇索引是按顺序存储记录, 对主键排序后, 访问聚簇索引可以更加顺序的访问磁盘, 减少随机 I/O, 提高速度, 所以当分页没有特别指定的列时, 指定主键排序即可, 另外不需要在联合索引最后一列添加主键, 因为它本身包含主键 [非聚簇索引不存储完整记录, 通过访问主键索引找到完整记录 ] .
3.4 索引设计优化常见小技巧
以上已经列出较多的误区及注意事项, 理解即可, 更重要的是根据业务对索引取舍的经验. 更多的设计技巧希望同学们在实践中自己总结并分享出来.
1. 数据量较大的表 (千万以上) 考虑是否适合建立分区.
2. 对于较长字符串例如 200 以上, 可以考虑单独增加索引列, 对其整体 hash 或者去其中一部分 hash 后存入其他一列, 这 样将字符串查找变成数字查找, 同时索引长度大大减小, 可有效提高索引速度, 降低索引大小. 但是需要考虑 hash 函数 的 "碰撞" 问题, 选择适合的 hash 函数.
3. 使用 explain 命令查看 sql 的执行计划, 请参考延伸阅读
来源: http://www.jianshu.com/p/87334d57cb2a