Mysql高级——索引篇

已于 2022-05-31 09:12:15 修改
阅读量1.4k 收藏 8
点赞数 1
分类专栏： mysql 文章标签： mysql 学习 数据结构
于 2022-05-30 19:47:29 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46301906/article/details/125052747
版权

索引

image-20220530190856879

1. 索引的概念

索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

优点缺点
提高数据检索效率，降低数据库的 IO 成本索引列也是要占用空间
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

2.索引的数据结构

索引结构描述
B+Tree最常见的索引类型，大部分引擎都支持 B+Tree 索引
Hash底层数据结构是用哈希表实现，只有精确匹配索引列的查询才有效，不支持范围查询
R-Tree(空间索引)空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-Text(全文索引)是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene、Solr、ES
  • 索引的数据结构和具体存储引擎的实现有关
  • 在 MySQL 中使用较多的索引有 Hash 索引，B+ 树索引 等
  • 而我们经常使用的 InnoDB 存储引擎的默认索引实现为：B+ 树索引。

B-Tree

B-Tree 是一种多叉路衡查找树（AVL树），相对于二叉树，B-Tree 每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为 5 的 B-Tree 为例，那这个 B-Tree 每个节点最多存储 4 个 key，一共 5 个指针。

img

核心：4 个 key 后再添加，就会从中间拿出一个 key，向上裂变。若是双，中间两个拿左边那个。

B+Tree

B+Tree 是 B-Tree 的变种，

我们以一颗最大度数（max-degree）为 4 的 B+Tree 为例，来看一下其结构示意图：

img

我们可以看到两部分：

  • 绿色框框起来的部分，是索引部分，仅仅起到索引数据的作用，不存储数据。
  • 红色框框起来的部分，是数据存储部分，在其叶子节点中要存储具体的数据。

核心：向上裂变时，保留自己在叶子结点中，这样可以直接在叶子结点拿数据。

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能，利于排序。

img

Hash

MySQL 中除了支持 B+Tree 索引，还支持一种索引类型，Hash 索引。

哈希索引就是采用一定的 hash 算法，将键值换算成新的 hash 值，映射到对应的槽位上，然后存储在 hash 表中。 如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了 hash 冲突（也称为hash碰撞），可以通过链表来解决。

优劣：

  • A. Hash索引只能用于对等比较(=，in)，不支持范围查询（between，>，< ，…）
  • B. 无法利用索引完成排序操作
  • C. 查询效率高，通常（不存在 hash 冲突的情况）只需要一次检索就可以了，效率通常要高于 B+tree 索引

在MySQL中，支持 hash 索引的是 Memory 存储引擎。而 InnoDB 中具有自适应 hash 功能，hash 索引是 InnoDB 存储引擎根据B+Tree 索引在指定条件下自动构建的。

3. 面试题：为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?

  1. 相对于二叉树（B树），层级更少，搜索效率高
  2. 对于 B-Tree（二叉平衡树），无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针也跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  3. 相对于 Hash 索引，B+Tree 支持范围匹配及排序操作

4. 面试题：Hash索引和B+树索引有什么区别或者说优劣

首先要知道Hash索引和B+树索引的底层实现原理：

  • hash索引底层就是hash表，进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。
  • B+树底层实现是多路平衡查找树。对于每一次的查询都是从根节点出发，查找到叶子节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。

那么可以看出他们有以下的不同：

  1. hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。
    • 因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+树的的所有节点皆遵循(左节点小于父节点，右节点大于父节点，多叉树也类似)，天然支持范围。
  2. hash索引不支持使用索引进行排序，原理同上。
  3. hash索引不支持模糊查询以及多列索引的最左前缀匹配。原理也是因为hash函数的不可预测。 AAAA和AAAAB的索引没有相关性。
  4. hash索引任何时候都避免不了回表查询数据，而B+树在符合某些条件(聚簇索引，覆盖索引等)的时候可以只通过索引完成查询。
  5. hash索引虽然在等值查询上较快，但是不稳定。 性能不可预测，当某个键值存在大量重复的时候，发生hash碰撞，此时效率可能极差。而B+树的查询效率比较稳
  6. 51定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

因此，在大多数情况下，直接选择B+树索引可以获得稳定且较好的查询速度。而不需要使用hash索引。

5. 索引的分类

分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词，而不是比较索引中的值可以有多个FULLTEXT

在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种

分类含义特点
聚集索引（Clustered Index）将数据存储与索引放一块，索引结构的叶子节点保存了行数据必须有，而且只有一个
二级索引（Secondary Index）将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键可以存在多个

6. 什么是聚合索引

聚集索引的规则如下：

  1. 如果存在主键，主键索引就是聚集索引
  2. 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
  3. 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

找到合适的聚集索引后，剩下的索引都为二级索引。

如下图，聚集索引的叶子节点下挂的是这一行的数据。而二级索引的叶子节点下挂的是该字段值对应的主键值。

img

  • 当我们执行如下的 SQL 语句时，具体的查找过程如下图。

img

  1. 由于是根据 name 字段进行查询，所以先根据 name=‘Arm’ 到 name 字段的二级索引中进行匹配查找。
  2. 在二级索引中只能查找到 Arm 对应的主键值 10。
  3. 由于查询返回的数据是 *，所以此时，还需要根据主键值 10，到聚集索引中查找 10 对应的记录，最终找到 10 对应的行 row，这一步称作回表查询。
  4. 最终拿到这一行的数据，直接返回。

回表查询：先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

7. 面试题：以下两条SQL语句，那个执行效率高？为什么？

A. select * from user where id = 10;

B. select * from user where name = 'Arm' ;

备注：id 为主键，name 字段有索引；

答：A 语句的执行性能要高于B 语句。 因为 A 语句直接走聚集索引，直接返回数据。 而 B 语句需要先查询 name 字段的二级索引，然后再查询聚集索引，也就是需要进行回表查询。

8. 面试题：使用索引查询一定能提高查询的性能吗？为什么

通常，通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。

索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。 这意味着每条记录的INSERT，DELETE，UPDATE将为此多付出4，5 次的磁盘I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。

使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:

  1. 基于一个范围的检索，一般查询返回结果集小于表中记录数的30%
  2. 基于非唯一性索引的检索

9. 索引规则与失效的场景

image-20220530144533525

环境准备

create table tb_user(
    id int primary key auto_increment comment '主键',
    name varchar(50) not null comment '用户名',
    phone varchar(11) not null comment '手机号',
    email varchar(100) comment '邮箱',
    profession varchar(11) comment '专业',
    age tinyint unsigned comment '年龄',
    gender char(1) comment '性别 , 1: 男, 2: 女',
    status char(1) comment '状态',
    createtime datetime comment '创建时间'
) comment '系统用户表';

INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1', '6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33, '1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1', '2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54, '1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23, '2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2', '0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24, '2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38, '1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43, '1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1', '0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价', 44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43, '1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40, '2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31, '2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35, '2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1', '1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易', 30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51, '2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52, '1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19, '1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20, '1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status, createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29, '1', '4', '2003-05-26 00:00:00');

索引创建及测试

-- name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引
create index idx_user_name on tb_user(name);
-- phone 手机号字段的值非空，且唯一，为该字段创建唯一索引
create unique index idx_user_phone on tb_user (phone);
-- 为 profession, age, status 创建联合索引
create index idx_user_pro_age_stat on tb_user(profession, age, status);
-- 为 email 建立合适的索引来提升查询效率
create index idx_user_email on tb_user(email);
-- 删除索引
drop index idx_user_email on tb_user;
-- 完成上述的需求之后，查看 tb_user 表的所有的索引数据
show index from tb_user;

此时该表的索引列

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LUp5Yith-1653911064028)(https://s2.loli.net/2022/05/30/4kHeBiNYy1T2qgs.png)]

type索引类型：

ststem > const > eq_ref > ref > range > index > all

9.1 最左前缀法则

**如果索引了多列（联合索引），要遵守最左前缀法则。**最左前缀法则指的是查询从索引的最左列开始， 并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效（即后面的字段索引失效）

例如：现有一张表中，其中有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession， age，status。对于最左前缀法则指的是，查询时，最左边的列，也就是 profession 必须存在，否则索引全部失效。 而且中间不能跳过某一列，比如 age，否则该列后面的字段索引将失效。

-- 创建联合索引
create index idx_pro_age_status on tb_user(profession, age, status);

-- 索引失效，联合索引最左边的列profession不存在
explain select * from tb_user where status = '0';
-- 索引失效，联合索引最左边的列profession不存在
explain select * from tb_user where age = 31 and status = '0';

-- 索引生效
explain select * from tb_user where profession = '软件工程';
-- 索引生效
explain select * from tb_user where profession = '软件工程' and age = 31;

-- 部分索引失效，profession有效，status无效，因为跳过了左边的age，不满足最左前缀法则
explain select * from tb_user where profession = '软件工程' and status = '0';
-- 索引生效，profession存在，age存在，status存在
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'；

注意：最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段（按照顺序下来的字段）存在即可!!! 与我们编写 SQL 时的先后顺序无关。

9.2 范围查询

联合索引中，出现范围查询（<，>），范围查询右侧的列索引失效。可以用 >=，<= 来规避索引失效问题。也建议把（<，>）写在最后，避免索引失效问题。

-- status不走索引
explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';
-- status依旧不走索引，因为按照联合索引的最左前缀法则，age先走索引，此时>导致后面的索引失效
explain select * from tb_user where profession = '软件工程' and status = '0' and age > 30;
-- 索引都生效
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';
-- 索引都生效
explain select * from tb_user where profession = '软件工程' and status = '0' and age >= 30;

– status依旧不走索引，因为按照联合索引的最左前缀法则，age先走索引，此时>导致后面的索引失效

image-20220530151833799

– 索引都生效

image-20220530152148701

9.3 索引列上有计算

在索引列上进行运算操作，索引将失效。

-- 索引失效。
explain select * from tb_user where substring(phone, 10, 2) = '15';

image-20220530151438969

9.4 字符串索引

字符串类型字段使用时，不加引号，索引将失效。

-- 此处phone是字符串类型，由于值没有加引号导致索引失效
explain select * from tb_user where phone = 17799990015;

-- 索引生效
explain select * from tb_user where phone = "17799990015";

image-20220530152955811

9.5 类型不一致导致的索引失效

phone字段在设计表的时候使用的是vchar类型，在写sql的时候应该用字符串与之对应，而不是使用长整形这样的数据

-- 此处phone是字符串类型，由于值没有加引号导致索引失效,相当于找一个整形与字符型做判断
explain select * from tb_user where phone = 17799990015;

-- 索引生效
explain select * from tb_user where phone = "17799990015";

– 此处phone是字符串类型，由于值没有加引号导致索引失效,相当于找一个整形与字符型做判断

image-20220530193355284

9.6 模糊查询

模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效；前后都有模糊匹配，也会失效。

like左边包含%

-- 索引有效
explain select * from tb_user where profession like '软件%';
-- 索引无效
explain select * from tb_user where profession like '%工程';
-- 索引无效
explain select * from tb_user where profession like '%工%';

9.7 or关键字的使用

用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。

-- 由于age没有索引，所以即使id、phone有索引，索引也会失效。所以需要针对于age也要建立索引。
explain select * from tb_user where id = 10 or age = 23;
-- 索引失效，因为age不是索引，所以整个or条件不会被触发索引
explain select * from tb_user where phone = '17799990017' or age = 23;

-- 索引生效，因为name和phone都是索引
explain select * from tb_user where phone = '17799990017' or name = "嬴政";

– 索引失效，因为age不是索引，所以整个or条件不会被触发索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OBbFiYaV-1653911064036)(https://s2.loli.net/2022/05/30/XSk6eD8q9OxAICT.png)]

– 索引生效，因为name和phone都是索引

image-20220530153747695

9.8 数据分布影响

如果 MySQL 评估使用索引比全表更慢，则不使用索引。

-- 对于表中phone大部分数据都>=17799990005，MySQL认为不走索引直接走全表扫描更快
select * from tb_user where phone >= '17799990005';
-- 对于表中phone仅部分数据>=17799990015，MySQL认为走索引更快
select * from tb_user where phone >= '17799990015'

– 对于表中phone大部分数据都>=17799990005，MySQL认为不走索引直接走全表扫描更快

image-20220530153955279

– 对于表中phone仅部分数据>=17799990015，MySQL认为走索引更快

image-20220530154030644

9.9 NOT IN、NOT EXISTS导致索引失效

在日常中使用比较多的范围查询有in、exists、not in、not exists、between and等。

-- 使用in，索引生效
explain select * from tb_user where id in (10, 20)
explain select * from tb_user where phone in ("17799990016", "17799990013")
-- 使用exists，索引不生效
explain select * from tb_user as t1 where exists (select 1 from tb_user as t2 where t2.phone = "17799990016")
-- 使用exists，索引生效
explain select * from tb_user as t1 where exists (select 1 from tb_user as t2 where t2.phone = "17799990016" and t2.phone =t1.phone)
-- 使用between and，索引生效
explain select * from tb_user where id between 2 and 20

看不走索引的情况：

-- not in，查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。
-- not in 使用其他索引字段，索引不生效
explain select * from tb_user where  phone not in ("17799990016", "17799990013")
-- not in使用主键，索引生效
explain select * from tb_user where id not in (10, 20)

-- not exists，查询条件使用not exists时，索引失效。
explain select * from tb_user as t1 where not exists (select 1 from tb_user as t2 where t2.phone = "17799990016" and t2.phone =t1.phone)
-- not exists，查询条件使用not exists时，索引失效，使用主键一样失效
explain select * from tb_user as t1 where not exists (select 1 from tb_user as t2 where t2.id = 1 and t2.id =t1.id)

查询条件使用not in时，如果是主键则走索引，如果是普通索引，则索引失效。

image-20220530172111352

– 查询条件使用not exists时，索引失效。

image-20220530190112180

– 特别的，not in换成主键，索引生效，not exist不行

image-20220530171906310

9.10 order by的问题

-- 使用order by，索引失效
explain select * from tb_user order by phone

-- 一个特例，就是主键使用order by时，可以正常走索引。
explain select * from tb_user order by id


-- 覆盖索引查询方式，索引生效
explain select id FROM tb_user order by phone
-- 复合索引使用覆盖索引的方式查询，索引生效
explain select id,  `status` FROM tb_user order by profession limit 5
-- 复合索引使用覆盖索引的方式查询，索引生效
explain select id,  `status` FROM tb_user order by profession, age
-- 上述三条SQL语句都是走索引的，也就是说覆盖索引的场景也是可以正常走索引的。

– 使用order by，索引失效

image-20220530160904193

什么是覆盖索引？

– 上述三条SQL语句都是走索引的，也就是说覆盖索引的场景也是可以正常走索引的。

image-20220530165535538

image-20220530165549618

image-20220530165601938

– 一个特例，就是主键使用order by时，可以正常走索引。

image-20220530160815455

总结：当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。

9.11 使用了select *

在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范：

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗，尤其是 text 类型的字段。

虽然在规范手册中没有提到索引方面的问题，但禁止使用select * 语句可能会带来的附带好处就是：某些情况下可以走覆盖索引。

比如，在上面的联合索引中，如果查询条件是age或username，当使用了select * ，肯定是不会走索引的。

但如果希望根据age查询出profession, age, status这三个结果（均为索引字段），明确查询结果字段，是可以走覆盖索引的：

-- 走了索引，因为使用覆盖索引的方式进行了查询，直接在索引树上找到了结果，不需要回表查询
explain select profession, age, `status` FROM tb_user where age = 22

explain结果：

– 走了索引，因为使用覆盖索引的方式进行了查询，直接在索引树上找到了结果，不需要回表查询

image-20220530163906462

无论查询条件是profession还是age，都走了索引，根据key_len可以看出使用了索引的所有列。

第二种索引失效场景：在联合索引下，尽量使用明确的查询列来趋向于走覆盖索引；

这一条不走索引的情况属于优化项，如果业务场景满足，则进来促使SQL语句走索引。至于阿里巴巴开发手册中的规范，只不过是两者撞到一起了，规范本身并不是为这条索引规则而定的。

听风北念
关注
  • 1
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
Java高级编程——MySQL索引实现及优化原理解析
02-25
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：图1这里设表一共有三列，假设我们以Col1为主键，则图1是一个MyISAM表的主索引（Primarykey）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondarykey）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如
mysql数据库in函数查询是否走索引
zgx20130619的博客
09-13 4823
mysql数据库IN函数查询是否走索引
mysql 查询使用索引
sinat_16493273的博客
02-02 923
前面文章介绍了InnoDB的索引结构是B+树。主键索引树上拥有所有行数据记录，使用索引可以提高数据库查询的性能和效率，加快数数据查询速度。
Mysql时间范围查询走不走索引问题
yzx3105的博客
10-26 5523
因此，在进行范围查询时，比如>、< 、>=、
Mysql查询条件为大于时，不走索引失效问题排查
程序新视界
02-24 8653
我们都知道在数据库查询时，索引可以极大的提高查询效率。通常在使用的时候，都会针对频繁查询的关键字段建立索引。 比如，当以交易日期（trans_date）来查询交易记录时，通常会对该字段添加索引，以便在大量数据的情况下提升查询效率。 针对trans_date字段，创建union_idx_query索引，那么在下面以trans_date为查询条件的语句中，毫无疑问是会走索引的： select count(1) from A; // 40000 EXPLAIN select * from A where tra
MySQL中使用函数会使索引失效？
程序员Forlan的博客
10-10 482
主要验证MySQL中使用函数会使索引失效？通过ChatGPT的回答和实践证明答案
MySQL何时使用索引,何时不使用索引
10-21
MySQL何时使用索引,何时不使用索引，归纳的还算详细，可以看看。
面试合集：数据库+数据结构+JVM+网络+JAVA+分布式+操作系统
Qqun954715313的博客
02-05 983
第一个模块：数据库 1.1 腾讯数据库面试问题 解释ACID四大特性 原子性的底层实现 数据库宕机后恢复的过程 如何保证事务的ACID特性 MySQL日志类型 这5个题目相对来说是比较普遍的，这里我就不一一给出答案了，给大家看下我的那个数据库学习笔记，这些题目在笔记里都是能找到的 1.2 我的数据库学习笔记 （1）自己整理的20个高频数据库学习知识点 事务四大特性（ACID）原子性、一致性、隔离性...
mysql索引在范围查询语句_MySql范围查找时索引不生效问题的原因分析
weixin_35823048的博客
01-19 594
1 问题描述本文对建立好的复合索引进行排序,并取记录中非索引字段，发现索引不生效，例如，有如下表，DDL语句为：CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NUL...
MySQL索引
HoldonWithYourGoal的博客
06-26 1255
一、索引类型 MySQL索引是一种特殊的数据结构，InnoDB的索引是表空间的一部分。索引保存着涉及所有记录的引用指针。 1、普通索引（index） 2、唯一索引 主键索引（primary key） 唯一索引（unique） 3、联合索引 联合主键索引：primary key（id, name, age） 联合唯一索引：unique(id, name...
MySQL中有exists关键字吗_Mysql中EXISTS关键字用法、总结
weixin_30744857的博客
02-08 167
在做教务系统的时候，一个学生(alumni_info)有多个教育经历(alumni_education)，使用的数据库是mysql，之前使用左链接查询的，发现数据量才只有几万条时，查询就很慢了，早上想到用子查询in，感觉效率还不是很高，结果想到用exists，效率高了很多。由于是第一次在mysql中使用exists，百度了一番，现将自己的总结如下：1、exists的返回结果是bool型，只有tru...
MySQL高级篇-索引（Index）的数据结构
10-30
MySQL高级篇——介 SQL索引（Index）是帮助MySQL高效获取数据的数据结构
Mysql数据库高级用法之视图、事务、索引、自连接、用户管理实例分析
01-21
本文实例讲述了Mysql数据库高级用法之视图、事务、索引、自连接、用户管理。分享给大家供大家参考，具体如下： 视图 视图是对若干张基本表的引用，一张虚表，只查询语句执行结果的字段类型和约束，不存储具体的数据...
101个MySQL优化技巧和提示
12-15
1. 拥有足够的物理内存来把整个InnoDB文件加载到内存中——在内存中访问文件时的速度要比在硬盘中访问时快的多。 2. 不惜一切代价避免使用Swap交换分区 – 交换时是从硬盘读取的，它的速度很慢。 3. 使用电池供电的...
MySQL-多表查询
qq_57157023的博客
04-25 987
MySQL多表查询
Mysql_数据库事务
最新发布
weixin_69252724的博客
04-26 1180
事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于化学当中的原子（一种不可分割的最小单位）。往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。
88张表-Mysql
2301_80151755的博客
04-25 232
【代码】88张表-Mysql。
问题-MySQL将较大的SQL文件导入MySQL
qq_1905369638
04-25 431
迁移数据的时候，我们有时候会用sqlyog等数据库工具导入到新数据库。可能插入的SQL语句太大，出现导入一半失败的情况。明明代码没错，这让人摸不着头脑。
mysql全量备份及数据恢复实践
东方客的博客
04-25 336
-stream=xbstream xtrabackup自带的压缩软件格式。--no-server-version-check 不做版本检查。gzip 使用gz压缩，缩小空间占用。--backup 执行备份的参数。
mysql高级篇索引优化
09-06
MySQL高级篇索引优化主要涉及MySQL Query Optimizer（查询优化器）和索引的使用。MySQL Query Optimizer是MySQL中负责优化SELECT语句的模块，通过计算分析系统中收集到的统计信息，为客户端请求的Query提供最优的执行计划，即最优的数据检索方式。索引是帮助MySQL高效获取数据的数据结构，它可以提供排序和查询的功能。 在索引优化中，需要考虑以下情况适合建立索引： 1. 主键自动建立唯一索引。 2. 频繁作为查询条件的字段应该创建索引。 3. 查询中与其他表关联的字段，尤其是外键关系，应建立索引。 4. 单键或组合索引的选择问题，通常在高并发情况下倾向于创建组合索引。 5. 查询中排序的字段，通过索引进行访问可以大大提高排序速度。 6. 查询中用于统计或分组的字段。 而以下情况不适合建立索引： 1. Where条件中用不到的字段不需要创建索引。 2. 表记录太少，通常建议超过300万条记录再考虑建立索引。 3. 经常进行增删改操作的表，建立索引可以提高查询速度，但同时会降低更新表的速度。 4. 数据重复且分布平均的字段，对于包含许多重复内容的数据列，建立索引没有太大实际效果。 因此，在索引优化中，应该根据具体情况选择最经常查询和最经常排序的数据列来建立索引，避免对不需要的字段建立索引，以提高查询效率。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* *3* [MySQL高级篇（SQL优化、索引优化、锁机制、主从复制）](https://blog.csdn.net/yuan2019035055/article/details/122310447)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 100%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值