Mysql索引

NewBee_Lxx

已于 2024-09-10 01:52:24 修改

阅读量858

点赞数 24

分类专栏：数据库文章标签： mysql 数据库

于 2024-02-24 20:30:11 首次发布

本文链接：https://blog.csdn.net/Liu___l/article/details/136253086

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

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

优缺点：

优点：

提高数据检索效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

缺点：

索引列也是要占用空间的
索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE

索引结构

B-Tree

红黑树也存在大数据量情况下，层级较深，检索速度慢的问题。

为了解决上述问题，可以使用 B-Tree 结构。
B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）

演示地址：B-Tree Visualization

中间值向上分裂

B+数的理由：

两大好处，查询的速度更快一些，他的叶子节点存储完整的数据，非叶子节点他就会存放索引数据，这样就会带来一个好处就是读取磁盘的数据会更少，查找效率会更高，因为只有叶子节点存放数据所以sql查询的路径都是一样长的，保证查询的稳定性。

B+Tree

每个节点存储了更多的键值（key）和数据（data），并且每个节点拥有更多的子节点，子节点的个数一般称为阶，上述图中的 B 树为 3 阶 B 树，高度也会很低。基于这个特性，B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

演示地址：B+ Tree Visualization

与 B-Tree 的区别：

所有的数据都会出现在叶子节点
叶子节点形成一个单向链表

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

Hash

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

特点：

Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

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

思考题

为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

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

索引分类

聚簇索引(聚集索引) ：叶子节点包含整个行数据，每张表只能拥有一个聚簇索引

回表查询：先走二级索引，找到对应的主键值，根据主键值在聚集索引中拿到这一行的行数据。

聚集索引选取规则：

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

思考题

以下 SQL 语句，哪个执行效率高？为什么？

select * from user where id = 10;
select * from user where name = 'Arm';
-- 备注：id为主键，name字段创建的有索引

答：第一条语句，因为第二条需要回表查询，相当于两个步骤。

语法

创建索引：

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

如果CREATE 后面不加索引类型参数，则创建的是常规索

查看索引：

SHOW INDEX FROM table_name;

删除索引：

DROP INDEX index_name ON table_name;

案例：

-- 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;

最左前缀法则

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

联合索引中，出现范围查询（<, >），范围查询右侧的列索引失效。可以用>=或者<=来规避索引失效问题。

索引失效情况

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

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

字符串类型字段使用时，不加引号，索引将失效。如：
```
explain select * from tb_user where phone = 17799990015;
```
此处phone的值没有加引号
模糊查询中，如果仅仅是尾部模糊匹配，索引不会是失效；如果是头部模糊匹配，索引失效。如：
```
explain select * from tb_user where profession like '%工程';
```
前后都有 % 也会失效。
用 or 分割开的条件，如果 or 其中一个条件的列没有索引，那么涉及的索引都不会被用到。
如果 MySQL 评估使用索引比全表更慢，则不使用索引。

SQL 提示

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

使用索引：

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

不使用哪个索引：

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

必须使用哪个索引：

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

use 是建议，实际使用哪个索引 MySQL 还会自己权衡运行速度去更改，force就是无论如何都强制使用该索引。

覆盖索引&回表查询

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能找到），减少 select *。

explain 中 extra 字段含义：
using index condition：查找使用了索引，但是需要回表查询数据
using where; using index;：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

如果在聚集索引中直接能找到对应的行，则直接返回行数据，只需要一次查询，哪怕是select *；如果在辅助索引中找聚集索引，如select id, name from xxx where name=‘xxx’;，也只需要通过辅助索引(name)查找到对应的id，返回name和name索引对应的id即可，只需要一次查询；如果是通过辅助索引查找其他字段，则需要回表查询，如select id, name, gender from xxx where name=‘xxx’;

1、聚集索引：直接通过通过主键找到对应的行

2、覆盖索引：

3、gender在辅助索引中找不到，回表查询才能找到gender

所以尽量不要用select *，容易出现回表查询，降低效率，除非有联合索引包含了所有字段

面试题：

一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案：
select id, username, password from tb_user where username=‘itcast’;

解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引

前缀索引

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

语法：create index idx_xxxx on table_name(columnn(n));
前缀长度：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
求选择性公式：

select count(distinct email) / count() from tb_user;
select count(distinct substring(email, 1, 5)) / count() from tb_user;
show index 里面的sub_part可以看到接取的长度