Mysql索引

基本概念

索引是一种数据结构，通过索引可以快速检索数据，避免全表查询。

暂时无法在飞书文档外展示此内容

优点：

  • 提高查询效率

  • 降低数据排序成本

缺点：

  • 占空间

  • 降低更新表的速度

索引的结构

1.概述

image-20221128224345137

  • B+树

    • 最常见，大部分引擎支持

  • Hash

    • 哈希表实现，只支持精准查询，不支持范围查询

  • R-tree

    • MyISAM引擎的特殊索引类型，用于地理空间类型

  • full-text

    • 倒排索引快速匹配文档

2.二叉树

二叉树存在退化现象，退化成链表时查询性能大大降低。红黑树解决了平衡问题但是层数还是较深。

image-20221127183426446

image-20221127183650815

3.B树

多路平衡查找树，例如max-degree为5阶，每个节点存储4个key 5个指针

image-20221127183702967

  • 节点中的key超过度数时向上分裂

  • 非叶子节点和叶子节点都会存放数据

  • 每一页中存储的key减少，指针减少，保存同样量级的数据，树的高度会更高

4.B+树

image-20221127183714254

  • 所有的数据都出现在叶子节点

  • 叶子节点形成一个单向链表

  • 非叶子节点只起到数据索引的作用

Mysql 优化后的B+树，增加了一个指向相邻节点的链表指针，利于排序，提高区间访问性能

image-20221127183737213

image-20221127183745873

5.哈希索引

image-20221127183758828

  • 不支持范围查询，只支持对等比较（=， in），不支持范围查询（between， > , <, ..）

  • 查询效率更高

5.总结：为什么Innodb使用B+树索引

1.相对于二叉树，层级更少

2.相对于B-树，一页存储的key值会更多，树的高度更低

3.相对于hash索引，支持范围查询和排序

索引分类

类型分类

存储形式分类

聚簇索引的选取规则：

  • 有主键则主键

  • 无主键则fist unique key

  • 都无，自动生成rowid，作为隐藏的聚集索引

image-20221127183814351

image-20221127183823424

问题1：

以下两条SQL语句，那个执行效率高? 为什么?

A. select * from user where id = 10 ;

B. select * from user where name = ‘Arm’ ;

备注: id为主键，name字段创建的有索引；

image-20221127184305022

问题2：

假设:

一行数据大小为1k，一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空

间，主键即使为bigint，占用字节数为8。

高度为2：

n * 8 + (n + 1) * 6 = 16*1024 , 算出n约为 1170

1171* 16 = 18736

也就是说，如果树的高度为2，则可以存储 18000 多条记录。

高度为3：

1171 * 1171 * 16 = 21939856

也就是说，如果树的高度为3，则可以存储 2200w 左右的记录

索引语法

  • 创建索引
1
2
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( 
index_col_name,... ) ;
  • 显示索引
1
SHOW INDEX FROM table_name ;
  • 删除索引
1
DROP INDEX index_name ON table_name ;

索引使用

体验：

image-20221127183831341

image-20221127183836241

建立索引

1
create index idx_sku_sn on tb_sku(sn);

image-20221127183847538

image-20221127183857815

最左前缀法则

建立联合索引后，查询从索引的最左列开始，不跳过中间的索引列，如果跳过了，则后面的索引无效

image-20221127183916029

1
create index idx_user_pro_age_sta on tb_user(profession, age, status);

image-20221127183922983

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

image-20221127183929218

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

image-20221127183936405

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

image-20221127183945143

profession key_len = 47, status key_len 5， age key_len 为 2

image-20221127183949508

索引失效

  • 范围查询

联合索引，出现范围查询右侧索引失效

image-20221127183959495

  • 索引列参与运算
1
create index idx_user_phone on tb_user(phone);

image-20221127184006244

image-20221127184012430

  • 模糊查询，头部模糊匹配

image-20221127184020283

  • or 连接条件

or左右两侧都有索引时，索引才生效

image-20221127184028790

  • 数据分布影响

mysql 评估使用索引比全表慢则不使用索引

image-20221127184036056

image-20221127184044675

SQL提示

如果既创建了联合索引又创建了单列索引，可以通过SQL提示建议或强制mysql使用某一索引查询数据

  • use index

  • ignore index

  • force index

image-20221127184053053

覆盖索引

当select 返回的字段 全在索引中包含后，避免了回表查询，可以提高查询效率

image-20221127184105841

image-20221127184110931

image-20221127184120740

image-20221127184127740

因此尽量避免select * 操作

前缀索引

对于字符串列（varchar、text、longtext）直接建立索引，索引很大，浪费大量磁盘IO，影响查询效率。这时候可以截取字符串，取前缀做索引。

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

前缀取多长由选择性来判断，选择性越高越好。

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

image-20221127184135692

image-20221127184140912

image-20221127184148279

区分度越高越好，区分度越高，确定的范围越小查询越快

image-20221128224429175

image-20221127184157130

image-20221127184202479

单列&&联合索引

image-20221127184207910

索引设计原则

1.数据量大，查询频繁的表需要建立索引

2.针对常常作为查询、排序、分组条件的字段建立索引

3.选择区分度高的字段建立索引

4.尽量使用联合索引、减少单列索引，联合索引可以覆盖索引，避免回表，提高查询效率

6.控制索引数量，索引会影响增删改的效率