MYSQL的索引使用注意

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

索引并不是时时都会生效的,比如以下几种情况,将导致索引失效 

最左前缀法则

如果使用了联合索引,要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效( 后面的字段索引失效 ) 。查看tb_user 表所创建的索引 。 这个联合索引涉及到三个字段,顺序分别为:profession,age,status。
show index from tb_user;

MYSQL的索引使用注意,mysql面试题,mysql,数据库对于最左前缀法则指的是,查询时,最左变的列,也就是profession必须存在,否则索引全部失效。

 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

SQL 查询时,存在 profession 字段,最左边的列是存在的,索引满足最左前缀法则的基本条
件。但是查询时,跳过了 age 这个列,所以后面的列索引是不会使用的,也就是索引部分生效,所以索引的长度就是47
explain select * from tb_user where profession = '软件工程' and status = '0';
MYSQL的索引使用注意,mysql面试题,mysql,数据库

思考 

当执行 SQL 语句 : explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程 ' ; 时,是否满足最左前缀法则,走不走联合索引,
MYSQL的索引使用注意,mysql面试题,mysql,数据库
可以看到,是完全满足最左前缀法则的,索引长度 54 ,联合索引是生效的。注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段( 即是第一个字段) 必须存在,与我们编写 SQL 时,条件编写的先后顺序无关。

范围查询

联合索引中,出现范围查询 (>,<) ,范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0' ;

MYSQL的索引使用注意,mysql面试题,mysql,数据库

当范围查询使用 > < 时,走联合索引了,但是索引的长度为 49 ,就说明范围查询右边的 status
段是没有走索引的。
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

当范围查询使用 >= <= 时,走联合索引了,但是索引的长度为 54,就说明所有的字段都是走索引的。 所以,在业务允许的情况下,尽可能的使用类似于 >= <= 这类的范围查询,而避免使用 > <

索引列运算

不要在索引列上进行运算操作, 索引将失效。在tb_user表中,除了前面介绍的联合索引之外,还有一个索引,是phone字段的单列索引。

当根据 phone 字段进行等值匹配查询时 , 索引生效。
explain select * from tb_user where phone = '17799990015';
MYSQL的索引使用注意,mysql面试题,mysql,数据库

当根据phone字段进行函数运算操作之后,索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

字符串不加引号

字符串类型字段使用时,不加引号,索引将失效。
字符串类型的字段,加单引号
 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

 字符串类型的字段,不加单引号

 explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

我们会明显的发现,如果字符串不加单引号,对于查询结果,没什么影响, 但是数据库存在隐式类型转换,索引将失效。

模糊查询

如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
模糊查询时, % 加在关键字之后
explain select * from tb_user where profession like '软件%';
MYSQL的索引使用注意,mysql面试题,mysql,数据库
模糊查询时, % 加在关键字之前
explain select * from tb_user where profession like '%工程';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

我们发现,在 like 模糊查询中,在关键字后面加 % ,索引可以生效。而如果在关键字
前面加了 % ,索引将会失效。

or连接条件

or 分割开的条件, 如果 or 前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
explain select * from tb_user where id = 10 or age = 23;
MYSQL的索引使用注意,mysql面试题,mysql,数据库

由于age没有索引,所以即使idphone有索引,索引也会失效。所以需要针对于age也要建立索引。

create index idx_user_age on tb_user(age);

 MYSQL的索引使用注意,mysql面试题,mysql,数据库

再次执行上述的SQL语句

MYSQL的索引使用注意,mysql面试题,mysql,数据库 or连接的条件,左右两侧字段都有索引时,索引才会生效。

数据分布影响

如果 MySQL 评估使用索引比全表更慢,则不使用索引。
explain select * from tb_user where phone >= '17799990005';
explain select * from tb_user where phone >= '17799990015';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

MySQL 在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃
索引,走全表扫描。 因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不如走全表扫描来的快,此时索引就会失效。

 SQL提示

SQL 提示,是优化数据库的一个重要手段,简单来说,就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。

use index

建议 MySQL 使用哪一个索引完成此次查询(仅仅是建议, mysql 内部还会再次进行评估)
explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

 ignore index

忽略指定的索引。
explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

MYSQL的索引使用注意,mysql面试题,mysql,数据库

force index

强制使用索引。

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

MYSQL的索引使用注意,mysql面试题,mysql,数据库

覆盖索引

尽量使用覆盖索引,减少 select * 。 那么什么是覆盖索引呢? 覆盖索引是指 查询使用了索引,并
且需要返回的列,在该索引中已经全部能够找到 。

查询id,profession,age, status字段

explain select id,profession,age, status from tb_user where profession = '软件工程' and age = 31 and status = '0' ;

 查询id,profession,age, status,name字段

explain select id,profession,age, status,name from tb_user where profession = '软件工程' and age = 31 and status = '0' \G;
因为,在 tb_user 表中有一个联合索引 idx_user_pro_age_sta ,该索引关联了三个字段profession、 age status ,而这个索引也是一个二级索引,所以叶子节点下面挂的是这一行的主键id 。 所以当我们查询返回的数据在 id profession age status 之中,则直接走二级索引 直接返回数据了。 如果超出这个范围,就需要拿到主键 id,再去扫描聚集索引,再获取额外的数据了,这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值,很容易就会造成回表查询(除非是根据主键查询,此时只会扫描聚集索引)

前缀索引

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

语法

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

前缀长度

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

MYSQL的索引使用注意,mysql面试题,mysql,数据库

创建前缀索引

create index idx_email_5 on tb_user(email(5));

单列索引与联合索引

  • 单列索引:即一个索引只包含单个列。
  • 联合索引:即一个索引包含了多个列。

我们先来看看 tb_user 表中目前的索引情况, 在查询出来的索引中,既有单列索引,又有联合索引。

MYSQL的索引使用注意,mysql面试题,mysql,数据库文章来源地址https://www.toymoban.com/news/detail-706013.html

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

总结

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

 

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

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

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

相关文章

  • MySQL数据库唯一索引

    创建索引是指在某个表的一列或多列上建立一个索引,以便提高对表的访问速度。创建索引有3种方式,分别是1.创建表的时候创建索引、2.在已经存在的表上创建索引和使用3.ALTER TABLE语句来创建索引。 本文福利, 莬 费领取Qt开发学习资料包、技术视频,内容包括(C++语言基

    2024年02月08日
    浏览(64)
  • MySQL数据库索引机制

    MySQL是一款有客户端和服务端的网络应用,mysql是它的客户端,mysqld是它的服务端。服务端本质就是一个进程,它存在于内存当中。而我们存储在MySQL中的数据是保存在磁盘上的,当我们对MySQL中数据进行增删查改操作时,不可能是直接在磁盘上进行操作,而是将对应的数据加

    2024年02月12日
    浏览(75)
  • 简单认识MySQL数据库索引

    提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档 ●索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。 ●使用索引后可以不用扫描全表来定位某行的

    2024年02月16日
    浏览(66)
  • MySQL数据库索引的数据结构

    数据库索引的功能就是让查找更加的高效,所以索引的数据结构应该是能够加速查找的数据结构。 MySQL的innoDB存储引擎的索引的数据结构就是多叉搜索树中的b+树,这可以说是为索引量身定做的一个数据结构。 首先,索引可以通过主键,unique修饰创建,也可以直接使用sql语句

    2024年02月10日
    浏览(54)
  • 【MySql系列】深入解析数据库索引

    MySQL索引是数据库中一个关键的概念,它可以极大地提高查询性能,加快数据检索速度。但是,要充分发挥索引的作用,需要深入理解它们的工作原理和使用方式。 在本文中,我们将深入解析MySQL索引,探讨它们的重要性、类型、创建、维护以及最佳实践。 在数据库中,索引

    2024年02月08日
    浏览(72)
  • 【MySQL数据库 | 第十七篇】索引以及索引结构介绍

    目录 前言: 索引简介:  索引结构:           二叉树索引结构         Tree(普通二叉树)         B-Tree(多路平衡查找树)         B+Tree          哈希索引数据结构 总结: 在实际生活中,我们对SQL语句进行优化实际上有很大一部分都是对索引进行优化,因此对索引

    2024年02月09日
    浏览(72)
  • MySQL数据库索引的种类、创建、删除

    目录 一:MySQL 索引 1、MySQL 索引介绍 2、 索引的作用  3、索引的副作用 4、 创建索引的原则依据  二、索引的分类和创建 1、 普通索引 (1) 直接创建索引 (2) 修改表方式创建 (3) 创建表的时候指定索引 2、 唯一索引 (1) 直接创建唯一索引 (2) 修改表方式创建

    2024年02月09日
    浏览(208)
  • 【Mysql系列】——详细剖析数据库“索引”【上篇】

        😎博客昵称:博客小梦 😊最喜欢的座右铭:全神贯注的上吧!!! 😊作者简介:一名热爱C/C++,算法,数据库等技术、喜爱运动、热爱K歌、敢于追梦的小博主! 😘博主小留言:哈喽! 😄各位CSDN的uu们,我是你的博客好友小梦,希望我的文章可以给您带来一定的帮

    2024年02月02日
    浏览(54)
  • MySQL数据库的ID列添加索引

    要为MySQL数据库的ID列添加索引,可以使用以下语法: 其中, table_name 是要添加索引的表名, index_name 是索引的名称, id 是要添加索引的列名。 例如,如果要为名为 users 的表的 id 列添加索引,可以执行以下语句: 这将在 users 表的 id 列上创建名为 idx_id 的索引。 需要注意的

    2024年02月07日
    浏览(51)
  • B+树:MySQL数据库索引的实现

    作为一个软件开发工程师,你对数据库肯定再熟悉不过了。作为主流的数据存储系统,它在我们的业务开发中,有着举足轻重的地位。在工作中,为了加速数据库中数据的查找速度,我们常用的处理思路是,对表中数据创建索引。那你是否思考过,数据库索引是如何实现的呢

    2024年02月09日
    浏览(58)

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

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

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

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

二维码1

领取红包

二维码2

领红包