【Mysql调优】3、索引

最新推荐文章于 2024-09-12 12:42:46 发布

一飘微草

最新推荐文章于 2024-09-12 12:42:46 发布

阅读量187

点赞数

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

本文链接：https://blog.csdn.net/qq_45473962/article/details/126755139

版权

Mysql调优专栏收录该内容

9 篇文章 1 订阅

订阅专栏

本文详细解释了索引在数据库中的作用，包括BTree索引（如InnoDB和MyISAM的区别）、不同类型的索引（如BTree、hash和全文索引）及其实现原理。重点讨论了为何在ID列上使用自增长主键，以及如何利用索引来提高查询效率，涵盖了Hash索引的特性和适用场景。

摘要由CSDN通过智能技术生成

1、什么是索引

生活中的索引

新华字典当中的目录，通过偏旁部首、拼音等查询汉字；
图书馆通过书名、书号等查询出图书在图书馆的摆放位置。

数据库表的索引

索引就是为表（table）建立的“目录”
索引的目的就是为了防止全表扫描（Full Scan）
索引的存储型式是有存储引擎决定的（不同存储引擎有不同的实现方式）

2、索引分类

按照存储结构划分

（1）BTree索引：采用B-Tree或B-Tree等树的形式
（2）hash索引：为每一行数据建立一个数据指纹，也就是hash值，通过hash值快速检索
（3）full-index全文索引：例如文本库等数据可建立全文索引，进行文本的快速检索
（4）R-tree索引：多维度索引，通常用在GIS系统

按照应用层次划分

 （1）普通索引：最普通的索引，并不保证唯一性
 （2）唯一索引：保证每一列数据绝对不能出现重复，执行效率是所有索引当中速度最高的
 （3）复合索引：也称多列索引，把多个字段放在一起组成一个索引，常用在多条件查询的场景

按照数据的物理顺序和键值的逻辑（索引）顺序划分

（1）聚集索引：数据和索引存储在一块
（2）非聚集索引：数据和索引分隔开

3、MySQL中的常用索引

1）B + Tree索引

适用于范围查找
InnoDB与MyISAM采用的就是B+Tree索引
B+Tree索引采用属性链表结构建立数据“目录”

案例讲解（InnoDB存储引擎为例讲解B+Tree索引）

假设有一张学生表，id为主键；
此时我们需要查找ID为3-5的数据，如果没有索引，mysql会加载所有的数据到内存，依次进行检索，读取磁盘次数较多，则进行的是全表扫描；
但是当我们在id上使用B+Tree索引后，将数据采用树的型式存储索引数据，而B+树的高度一般在2-4层，最多只需要读取2-4次磁盘，当查询获取最下一层数据后，因为数据是按照链表结构进行物理存储的，所以只需要获取范围查找的首尾使用索引条件即可获取到范围数据，查询速度大大提升，能够更快的查找数据。
当我们多个字段（非联合多联合索引）使用B+Tree索引后，单一使用非id的其他字段进行查找时，Mysql又是怎么去进行查找的呢？

如下图，除了id字段也给name字段加了B+Tree索引，使用name字段将数据按照B+Tree的排序顺序后，最下端链表节点存储的数据是索引字段（name）的属性值，使用索引字段向数据的id上去靠拢获取主键，然后根据主键直接获取到数据，完成数据查找。

所以不管单一索引字段还是联合索引字段，索引只包含非id字段，B+Tree索引的查找逻辑均为如此。

在这里插入图片描述

为什么官方推荐在ID列上使用自增长主键？

之前的文章【Mysql调优】1、基本概念了解提到过自然主键和代理主键，自增长主键属于代理主键，与业务无关、无意义的数字序列值，在数据库表设计中数据使用逻辑删除，使用索引或联合索引后不会出现主键冲突；
自增长主键上使用索引，在表数据发生增删改操作时，索引的结构能够以尽可能小的程度上进行变化，计算量较小，例如增加操作只需要在书结构后增加一个数据即可（索引虽然能够提高查询效率，但是增加了维护成本，降低了增删改操作的速度）。

MyISAM存储引擎中的B+Tree索引

MyISAM当中的B+Tree存储结构的索引被称为非聚集索引，与InnoDB当中的聚集索引不同，索引和数据分隔开来，B+Tree的叶子结点存储的是数据的物理磁盘地址（InnoDB当中叶子结点存储id和物理数据），通过数据的物理地址的指针查询到数据。
注意：MYD类型的数据文件当中的数据可以不按照id顺序存储，就是因为MyISAM引擎当中，索引文件和数据文件不放在一起。

在这里插入图片描述

B - / + Tree 的区别

从简来说，他们的树形结构还是一致的，大致的区别就是叶子结点，B- 的叶子结点是单一的个体，B+ 的叶子结点相邻之间进行连接形成了单向链表，范围查询只需要查询首尾数据皆可，B- 虽然不是全表扫描，但是范围查询来讲还是比B+慢。

在这里插入图片描述

BTree索引使用技巧

数据表的存储引擎为InnoDB，现只有id主键上的唯一索引

此时我们解析使用id查询的sql，可以看出key使用到的索引名称为PRIMARY主，也就是主键。在这里插入图片描述
当我们对没有索引的字段进行查询的时候，解析出来type现实为ALL，代表全表扫描。（explain具体讲解后边的文章会提到）

然后我们对u_id仅从创建索引，然后再看他的执行计划，可以看到使用到了刚才创建的索引，rows代表索引所影响的行数，也即是查询结果的过程中所扫描的行数，相比创建索引之前全表共14874条数据，扫描了14391行数据，索引查询速度还是很快的。
所以rows的数值越小，从一定程度上可以反映出索引执行的效率越高。
在这里插入图片描述

使用技巧

# 在上述uid上创建了索引
#精准匹配，允许使用btree索引
explain select * from t_content where uid = 12601190
#范围查找，允许使用btree索引
explain select * from t_content where uid > 1260000 and uid < 12700000
#查询优化器会进行类型转换，但建议使用与表字段定义相符的类型，允许使用btree索引
explain select * from t_content where uid = '12601190'


create index idx_share_url on testdb.t_content(share_url)
# 字符串字段Btree索引允许进行"前缀模糊查询"
# 执行之后可以发现明明创建了索引，但是结果却走了全表扫描，是因为mysql的查询优化器觉得走索引不如全表扫描快，所以走了全表扫描（字符串较大且需求较高一般搭建文本服务器如es等等，避免使用mysql）。
explain select * from t_content where share_url like 'http://a.f.budejie.com/share/%'
# 前缀查询和模糊匹配，btree均不支持
explain select * from t_content where share_url like '%http://a.f.budejie.com/share/'
explain select * from t_content where share_url like '%http://a.f.budejie.com/share/%'


# 创建联合索引
create index idx_uid_sid on testdb.t_content(uid,source_id);
# 联合索引查询条件必须包含左侧列
# 查询条件包含uid，走了索引idx_uid_sid
explain select * from t_content where uid = 12601190;
# 查询条件包含uid，走了索引idx_uid_sid
explain select * from t_content where uid = 12601190 and source_id = 13054;
# 查询条件不包含uid，没走索引idx_uid_sid，全表扫描
explain select * from t_content where source_id <> 13054;
# 注意：<> 和 not in 都不会使用索引

2）Hash索引

Hash索引基于哈希表实现
适用于精准查找，但是精准匹配所有列的查询才生效
Hash索引会为每一条数据创建一个HashCode
注意：目前只有Memory的存储引擎当中才可使用hash索引

实现过程

# 数据准备
create table testHash
(
    fname varchar(50) not null,
    lname varchar(50) not null,
    key using hash (fname)
) engine = memory;

insert into testHash (fname, lname)
VALUES ('Arjen', 'Lentz'),
       ('Baron', 'Schwartz'),
       ('Peter', 'Zaitsev'),
       ('Vadim', 'Tkachenko');

数据插入之后，hash索引会自动为fname字段生成hash值，下列值为例子：
fname(‘Arjen’) = 1234
fname(‘Baron’) = 1235
fname(‘Peter’) = 1236
fname(‘Vadim’) = 1237

生产之后并维护了一张hash表，维护数据

hash值插槽（slot）	值（物理地址值）
1234	指向第1行（fname=‘Arjen’）数据的指针
1235	指向第2行（fname=‘Baron’ ）数据的指针
1236	指向第3行（fname=‘Peter’ ）数据的指针
1237	指向第4行（fname=‘Vadim’ ）数据的指针

特点

Hash索引只包含哈希值和行指针（参考上述表格）
支持精准匹配，不支持范围查询、模糊查询、排序
Hash取值速度很快，但是索引选择性很低的时候不建议使用（例如文本列等就不推荐使用，如身份证等信息推荐使用）
目前只有Memory存储引擎当中才支持hash索引

InnoDB当中的Hash索引

InnoDB只支持显示创建BTree索引，但是数据精准匹配的时候，MySQL底层回自动创建HashCode并存入缓存中，如果再次查询该条数据，则会很快进行返回。

4、索引的特点

索引大幅度的提升了数据的检索效率，通过增加索引，防止查询进行全表扫描，以最快的速度获取目标数据
索引在查询的时候，会把零散数据整理成有顺序的，随机IO变成了顺序IO，从计算机层面来讲就是在数据检索的时候，减少了很多磁盘寻址和计算的时间
索引并不是越多越好，在数据表之外额外维护了索引信息，在增删改等操作需要进行索引的重算，降低了数据的写入效率；太多的索引会增加增加查询优化器的选择时间，增加占用cpu的时间；不合理的使用索引，会大幅磁盘空间，例如创建很多使用很少的索引