MySQL-索引详解(五)

这篇具有很好参考价值的文章主要介绍了MySQL-索引详解(五)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

♥️作者:小刘在C站

♥️个人主页: 小刘主页 

♥️努力不一定有回报,但一定会有收获加油!一起努力,共赴美好人生!

♥️学习两年总结出的运维经验,以及思科模拟器全套网络实验教程。专栏:云计算技术

♥️小刘私信可以随便问,只要会绝不吝啬,感谢CSDN让你我相遇!

前言

本次MySQL—索引章节比较多,分为多篇进行发布,本章继续,链接—上一章

目录

6.5 SQL提示

索引情况如下:

 6.6 覆盖索引

6.7 前缀索引

1). 语法

示例:

 2). 前缀长度

3). 前缀索引的查询流程

6.8 单列索引与联合索引

7.索引设计原则


6.5 SQL提示

目前 tb_user 表的数据情况如下 :
MySQL-索引详解(五)

 

索引情况如下:

MySQL-索引详解(五)

 把上述的 idx_user_age, idx_email 这两个之前测试使用过的索引直接删除。

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;
A. 执行 SQL : explain select * from tb_user where profession = ' 软件工程 ';

MySQL-索引详解(五)

 查询走了联合索引。

B. 执行 SQL ,创建 profession 的单列索引: create index idx_user_pro on
tb_user(profession)

MySQL-索引详解(五)

 C. 创建单列索引后,再次执行A中的SQL语句,查看执行计划,看看到底走哪个索引。

MySQL-索引详解(五)

测试结果,我们可以看到, possible_keys idx_user_pro_age_sta,idx_user_pro 这两个
索引都可能用到,最终 MySQL 选择了 idx_user_pro_age_sta 索引。这是 MySQL 自动选择的结果。
那么,我们能不能在查询的时候,自己来指定使用哪个索引呢? 答案是肯定的,此时就可以借助于 MySQL SQL 提示来完成。 接下来,介绍一下 SQL 提示。
SQL 提示,是优化数据库的一个重要手段,简单来说,就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
1). use index : 建议 MySQL 使用哪一个索引完成此次查询(仅仅是建议, mysql 内部还会再次进
行评估)。
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';
2). ignore index : 忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';
3). force index : 强制使用索引。
explain select * from tb_user force index(idx_user_pro) where profession = '软件工
程';
示例演示:
A. use index
explain select * from tb_user use index(idx_user_pro) where profession = '软件工
程';

MySQL-索引详解(五)

 B. ignore index

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工
程';

MySQL-索引详解(五)

 C. force index

explain select * from tb_user force index(idx_user_pro_age_sta) where profession =
'软件工程';

MySQL-索引详解(五)

 6.6 覆盖索引

尽量使用覆盖索引,减少 select * 。 那么什么是覆盖索引呢? 覆盖索引是指 查询使用了索引,并
且需要返回的列,在该索引中已经全部能够找到 。
接下来,我们来看一组 SQL 的执行计划,看看执行计划的差别,然后再来具体做一个解析。
explain select id, profession from tb_user where profession = '软件工程' and age =
31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'
and age = 31 and status = '0' ;
explain select id,profession,age, status, name from tb_user where profession = '软
件工程' and age = 31 and status = '0' ;
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';

上述这几条SQL的执行结果为:

MySQL-索引详解(五)

 

从上述的执行计划我们可以看到,这四条 SQL 语句的执行计划前面所有的指标都是一样的,看不出来差异。但是此时,我们主要关注的是后面的 Extra ,前面两天 SQL 的结果为 Using where; Using
Index ; 而后面两条 SQL 的结果为 : Using index condition
MySQL-索引详解(五)

 因为,在tb_user表中有一个联合索引 idx_user_pro_age_sta,该索引关联了三个字段

profession age status ,而这个索引也是一个二级索引,所以叶子节点下面挂的是这一行的主键 id 。 所以当我们查询返回的数据在 id profession age status 之中,则直接走二级索引 直接返回数据了。 如果超出这个范围,就需要拿到主键 id,再去扫描聚集索引,再获取额外的数据 了,这个过程就是回表。 而我们如果一直使用 select * 查询返回所有字段值,很容易就会造成回表 查询(除非是根据主键查询,此时只会扫描聚集索引)。
为了大家更清楚的理解,什么是覆盖索引,什么是回表查询,我们一起再来看下面的这组 SQL 的执行过程。
A. 表结构及索引示意图 :
MySQL-索引详解(五)MySQL-索引详解(五)

 

 

id 是主键,是一个聚集索引。 name 字段建立了普通索引,是一个二级索引(辅助索引)。
B. 执行 SQL : select * from tb_user where id = 2;
MySQL-索引详解(五)

 

根据 id 查询,直接走聚集索引查询,一次索引扫描,直接返回数据,性能高。
C. 执行 SQL selet id,name from tb_user where name = 'Arm';
MySQL-索引详解(五)

 

虽然是根据 name 字段查询,查询二级索引,但是由于查询返回在字段为 id name ,在 name 的二级索引中,这两个值都是可以直接获取到的,因为覆盖索引,所以不需要回表查询,性能高。
D. 执行 SQL selet id,name,gender from tb_user where name = 'Arm';
MySQL-索引详解(五)

 

由于在 name 的二级索引中,不包含 gender ,所以,需要两次索引扫描,也就是需要回表查询,性能相较差一点。 

6.7 前缀索引

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

1). 语法

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

示例:

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

MySQL-索引详解(五)

 2). 前缀长度

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

3). 前缀索引的查询流程

MySQL-索引详解(五)

 

6.8 单列索引与联合索引

单列索引:即一个索引只包含单个列。
联合索引:即一个索引包含了多个列。
我们先来看看 tb_user 表中目前的索引情况 :
MySQL-索引详解(五)
在查询出来的索引中,既有单列索引,又有联合索引。
接下来,我们来执行一条 SQL 语句,看看其执行计划:
MySQL-索引详解(五)

 

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

MySQL-索引详解(五)

此时,查询时,就走了联合索引,而在联合索引中包含 phone name 的信息,在叶子节点下挂的是对应的主键 id ,所以查询是无需回表查询的。
此时,查询时,就走了联合索引,而在联合索引中包含 phone name 的信息,在叶子节点下挂的是对应的主键 id ,所以查询是无需回表查询的。
如果查询使用的是联合索引,具体的结构示意图如下:
MySQL-索引详解(五)

 

7.索引设计原则

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

♥️关注,就是我创作的动力

♥️点赞,就是对我最大的认可

♥️这里是小刘,励志用心做好每一篇文章,谢谢大家文章来源地址https://www.toymoban.com/news/detail-483826.html

到了这里,关于MySQL-索引详解(五)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 微信小程序--个人主页的制作

    1. 效果图: 2.页面wxml的布局逻辑 (1)代码: 3.页面wxss的设置 (1)代码: 1. 获取登录信息 (1)Userinfo:获取用户信息 (2)avatarUrl:用户头像 (3)nickName:用户昵称 (4)zh_CN:简体中文 (5)注意:这里是获取两次用户头像,因为背景也用的到头像 (6)样式的设置: 2

    2024年02月09日
    浏览(72)
  • html网页设计小作业(个人主页)

    目录 作品介绍: 效果展示 代码部分: 简易的个人网页小作业,只用了html+css 布局制作,没啥好说的,直接上图!!! Home 页面:  About页面:    Hobbies页面: Home界面: About页面:  Hobbies页面: oKK,就这么多啦。。

    2024年02月11日
    浏览(54)
  • [Android Studio] 个人主页界面的实现

    目录 接上篇:底部导航栏的实现 1.个人主页界面的设计:fragment_blank.xml 2.个人主页功能的实现类:BlankFragment.java 3.每日签到功能的实现 4.实现效果 5.图片素材  补充说明:注意配置AndroidManifest.xml文件 自定义弹窗布局文件:pop_up_signup.xml,用于显示签到成功 累计签到的天数,采

    2024年01月19日
    浏览(95)
  • 基于github制作个人学术网站(主页)

    首先找到一个学术模板,fork到远程仓库。academicpages,如果不是很清楚具体的步骤,可以参考保姆级教程。在github上对该网站代码修改不是很方便,肯定是在本地进行更新后push到远程仓库。 学会下载和安装就行,一路默认,可以先学习一下Git相关的原理及基础操作,可以参考

    2024年02月15日
    浏览(57)
  • 个人Scratch HTML程序合集 主页

    个人Scratch HTML程序合集 主页 此程序是本人制作的Scratch HTML程序合集的主页,使用HTML+CSS编写,整合了本人近期发布的转换为HTML的Scratch程序的内容,可以通过主页内的链接打开相应的程序和博客。主页在本地运行,大家可以在github.com下载主页文件及相关资源。 代码如下

    2024年02月16日
    浏览(50)
  • 详细讲解如何在github上编辑个人主页?

     在 GitHub 上编辑个人主页可以让您展示您的项目、技能和个人信息,以及与其他开发者互动。以下是详细的步骤来在 GitHub 上编辑个人主页: 如果您还没有 GitHub 账户,首先需要注册一个。 使用您的用户名和密码登录到 GitHub。 您需要创建一个特殊的仓库,仓库名必须是 用户

    2024年02月13日
    浏览(42)
  • 抖音web版地址个人主页和视频地址

    https://www.douyin.com/user/MS4wLjABAAAAfLsItSD2WiJrsji1g_iZv-it6W2CcvBFkdUwMjTeSD4 MS4wLjABAAAAfLsItSD2WiJrsji1g_iZv-it6W2CcvBFkdUwMjTeSD4 是 sec_uid。 https://v.douyin.com/h17aA6H https://www.iesdouyin.com/share/user/MS4wLjABAAAAfLsItSD2WiJrsji1g_iZv-it6W2CcvBFkdUwMjTeSD4 MS4wLjABAAAAfLsItSD2WiJrsji1g_iZv-it6W2CcvBFkdUwMjTeSD4 是 sec_uid。 下面的写法

    2024年04月27日
    浏览(51)
  • 【网站】让自己的个人主页能被Google检索

    参考: https://zhuanlan.zhihu.com/p/129022264 这样操作之后,等一天左右,个人主页就能被Google搜索到啦:

    2024年02月07日
    浏览(44)
  • 【Vercel】教你部署imsyy/home个人主页

    本篇博客教你如何部署一个自己的个人主页 项目地址:https://github.com/imsyy/home 本文首发于 慕雪的寒舍 首先我们点击fork,将仓库复刻到自己的账户 随后进入vercel,点击 dashboard-add new-project 选择你复刻的仓库 然后什么都不用改,直接deploy 等待部署成功,点击domains,在这里添

    2024年02月07日
    浏览(119)
  • javaweb个人主页设计(html+css+js)

    目录 1 前言和要求 1.1 前言 1.2 设计要求 2 预览 2.1 主页页面 2.2 个人简介 2.3 个人爱好 2.4 个人成绩有代码,但是图片已省略,可以根据自己情况添加 2.5 收藏夹 3 代码实现  3.1 主页 3.2 个人简介 3.3 个人爱好 3.4 个人成绩(根据自己的情况添加) 3.5 收藏夹 4 可能要用的图片,

    2024年02月09日
    浏览(89)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包