MySQL进阶-----前缀索引、单例与联合索引

 目录

前言

一、前缀索引

1. 语法 

2. 如何选择前缀长度

3. 前缀索引的查询流程

二、单列索引与联合索引

三、索引设计原则


前言

        本期是MySQL进阶篇当中索引的最后一期内容,这里我们主要接着上一期继续讲解前缀索引、单例与联合索引。(上一期链接:MySQL进阶-----SQL提示与覆盖索引-CSDN博客

一、前缀索引

当字段类型为字符串( varchar text longtext 等)时,有时候需要索引很长的字符串,这会让
索引变得很大,查询时,浪费大量的磁盘 IO , 影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。

1. 语法 

create index idx_xxxx on table_name(column(n)) ;

 示例:

tb_user 表的 email 字段,建立长度为 5 的前缀索引。
create index index_email on tb_user(email(5));

2. 如何选择前缀长度

可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高, 唯一索引的选择性是1 ,这是最好的索引选择性,性能也是最好的。

 下面这里我们看一下案例:

select count(distinct email)/count(*) from tb_user;

 可以看到上面显示的是1,也就是说所有的email字段的数据都没有出现重复,下面我们去从email字段数据去截取前5个字符比较试试看:

select count(distinct substring(email,1,5)) / count(*) from tb_user ;

这里我们可以看出出现重复了,但是非重复率还是有0.9583的,如果我们截取前4个或者前6个字符再试试看重复率:

#截取前四个
select count(distinct substring(email,1,4)) / count(*) from tb_user ;

#截取前6个
select count(distinct substring(email,1,6)) / count(*) from tb_user ;

上面这两个对比就知道,截取前4个的话重复率变大了,而截取前6个的话重复率不变 ,故最优解就是截取前面前5个即可。

3. 前缀索引的查询流程

前缀索引的查询流程基本上跟前面讲到过的是差不多的,这里会通过我们选择好的前缀去建立一个辅助索引,在辅助索引上面去找到相对应的索引目标,如果出现重复的话就会先找到第一个重复的索引数据,然后再去进行回表查询,如果确定完整的字段能够匹配成功的话就为当前字段,反正继续遍历下一个重复的结果。

二、单列索引与联合索引

这个的话我们前面几期的内容就接触过了。

单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
我们先来看看 tb_user 表中目前的索引情况:
在查询出来的索引中,既有单列索引,又有联合索引。

 接下来,我们来执行一条SQL语句,看看其执行计划:

explain select id,phone,name from tb_user where phone='17799990000' and name='吕布';

通过上述执行计划我们可以看出来,在 and 连接的两个字段 phone name 上都是有单列索引的,但是最终mysql 只会选择一个索引,也就是说,只能走一个字段的索引,此时是会回表查询的。
紧接着,我们再来创建一个 phone name 字段的联合索引来查询一下执行计划。
create unique index idx_user_phone_name on tb_user(phone,name);

 此时,查询时,就走了联合索引,而在联合索引中包含 phonename的信息,在叶子节点下挂的是对应的主键id,所以查询是无需回表查询的。

如果查询使用的是联合索引,具体的结构示意图如下:

在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引

三、索引设计原则

  1. 针对于数据量较大,且查询比较频繁的表建立索引。
  2. 针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索 引。
  3. 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
  4.  如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
  5.  尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间, 避免回表,提高查询效率。
  6. 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
  7.  如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询

以上就是本期的全部内容,我们下次见。

分享一张壁纸: 

相关推荐

  1. MySQL联合索引

    2024-04-04 01:10:03       25 阅读
  2. Mysql联合索引

    2024-04-04 01:10:03       8 阅读
  3. MySQL查询篇(1)-索引的类型创建

    2024-04-04 01:10:03       21 阅读

最近更新

  1. 一文了解什么是RESTful风格

    2024-04-04 01:10:03       0 阅读
  2. SQL Server详细使用教程

    2024-04-04 01:10:03       0 阅读
  3. js实现快速拖拽(定时器版本)

    2024-04-04 01:10:03       0 阅读
  4. Flink CDC 整库 / 多表同步至 Kafka 方案(附源码)

    2024-04-04 01:10:03       0 阅读
  5. 从事数据分析相关工作技术总结

    2024-04-04 01:10:03       0 阅读
  6. FFT快速傅里叶变换音频分析

    2024-04-04 01:10:03       0 阅读
  7. 基于单片机雨天自动关窗器的设计

    2024-04-04 01:10:03       0 阅读
  8. 基础矩阵和本质矩阵

    2024-04-04 01:10:03       0 阅读
  9. 水气表CJ/T188协议学习及实例

    2024-04-04 01:10:03       0 阅读
  10. 基于springboot的教学资源库源码数据库

    2024-04-04 01:10:03       0 阅读

热门阅读

  1. Collection中常用方法

    2024-04-04 01:10:03       5 阅读
  2. redis

    2024-04-04 01:10:03       3 阅读
  3. Yocto理论基础之layer

    2024-04-04 01:10:03       4 阅读
  4. 网络安全专业术语

    2024-04-04 01:10:03       5 阅读
  5. 蓝桥杯刷题--python-36

    2024-04-04 01:10:03       2 阅读
  6. how to Optimize mysql select clause

    2024-04-04 01:10:03       4 阅读