江苏龙网

  1. 首页 > 人文 > >

索引一文带你你搞懂索引如何优化( 三 )

13、使用短索引（前缀索引）

  • 对列进行索引 ， 如果可能应该指定一个前缀长度 。 例如 ， 如果有一个CHAR(255)的列 ， 如果该列在前10个或20个字符内 ， 可以做到既使得前缀索引的区分度接近全列索引 ， 那么就不要对整个列进行索引 。 因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作 ， 减少索引文件的维护开销 。 可以使用count(distinct leftIndex(列名, 索引长度))/count(*) 来计算前缀索引的区分度 。
  • 但缺点是不能用于 ORDER BY 和 GROUP BY 操作 ， 也不能用于覆盖索引 。
  • 不过很多时候没必要对全字段建立索引 ， 根据实际文本区分度决定索引长度即可 。
14、利用延迟关联或者子查询优化超多分页场景
  • MySQL 并不是跳过 offset 行 ， 而是取 offset+N 行 ， 然后返回放弃前 offset 行 ， 返回 N 行 ， 那当 offset 特别大的时候 ， 效率就非常的低下 ， 要么控制返回的总页数 ， 要么对超过特定阈值的页数进行 SQL 改写 。
  • 示例如下 ， 先快速定位需要获取的id段 ， 然后再关联:
selecta.* from 表1a,(selectid from 表1 where条件 limit100000,20) b wherea.id=b.id；
15、如果明确知道只有一条结果返回 ， limit 1 能够提高效率
  • 比如如下 SQL 语句：
select* from user wherelogin_name=?
  • 可以优化为：
select* from user wherelogin_name=? limit 1
  • 自己明确知道只有一条结果 ， 但数据库并不知道 ， 明确告诉它 ， 让它主动停止游标移动 。
16、超过三个表最好不要 join
  • 需要 join 的字段 ， 数据类型必须一致 ， 多表关联查询时 ， 保证被关联的字段需要有索引 。
  • 例如：left join是由左边决定的 ， 左边的数据一定都有 ， 所以右边是我们的关键点 ， 建立索引要建右边的 。 当然如果索引在左边 ， 可以用right join 。
17、单表索引建议控制在5个以内 18、SQL 性能优化 explain 中的 type：至少要达到 range 级别 ， 要求是 ref 级别 ， 如果可以是 consts 最好
  • consts：单表中最多只有一个匹配行（主键或者唯一索引） ， 在优化阶段即可读取到数据 。
  • ref：使用普通的索引（Normal Index） 。
  • range：对索引进行范围检索 。
  • 当 type=index 时 ， 索引物理文件全扫 ， 速度非常慢 。
19、业务上具有唯一特性的字段 ， 即使是多个字段的组合 ， 也必须建成唯一索引
  • 不要以为唯一索引影响了 insert 速度 ， 这个速度损耗可以忽略 ， 但提高查找速度是明显的 。 另外 ， 即使在应用层做了非常完善的校验控制 ， 只要没有唯一索引 ， 根据墨菲定律 ， 必然有脏数据产生 。
20.创建索引时避免以下错误观念
  1. 索引越多越好 ， 认为需要一个查询就建一个索引 。
  2. 宁缺勿滥 ， 认为索引会消耗空间、严重拖慢更新和新增速度 。
  3. 抵制惟一索引 ， 认为业务的惟一性一律需要在应用层通过“先查后插”方式解决 。
  4. 过早优化 ， 在不了解系统的情况下就开始优化 。
索引选择性与前缀索引

上一篇：以太坊|出ETH进XRP，接下来留意这几个山寨

下一篇：牛股|牛股的六大特征，你的股票有几个？