高性能MySQL - 创建高性能的索引（上）（B-Tree，Hash）

最新推荐文章于 2024-07-23 07:39:24 发布

Fhang1996

最新推荐文章于 2024-07-23 07:39:24 发布

阅读量908

点赞数

分类专栏：数据库文章标签： mysql 高性能索引数据结构存储

本文链接：https://blog.csdn.net/Return_True_hang/article/details/63252319

版权

本文深入探讨MySQL中的索引，重点介绍了B-Tree索引的工作原理和适用场景，包括全值匹配、最左前缀等查询策略。同时，文章讨论了哈希索引的优势和限制，指出其只适用于精确匹配查询。全文还涉及全文索引和其他类型的索引，以及如何创建高性能的索引策略，强调了索引列顺序和前缀索引的重要性。

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

前言

索引在Mysql中也叫作‘键（key）’。
基本功能是用于存储引擎快速找到记录的一种数据结构。

Question：使用ORM，是否还需要关心索引
即使使用对象关系映射（ORM）工具，仍然要理解索引。除非只是生产非常基本的查询（例如仅是根据主键查询），否则它很难生成适合索引的查询。

Mysql中，索引是在存储引擎层而不是服务层实现的。所以没有统一的索引标准。

Mysql支持的索引

1. B-Tree 索引

如果没有特别指明类型，多半是说B-Tree索引。
存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值上限和下限。

假设有如下数据表：

CREATE TABLE Poople(
    last_name  varchar(50)   not null,
    first_name varchar(50)   not null,
    dob        date          not null,
    gender     enum('m','f') not null,
    key(last_name,first_name,dob)
);

索引包括last_name,first_name,dob, 该索引存储形式：

这里写图片描述

上述索引对如下类型的查询有效：
1. 全值匹配：
全值匹配指的是和索引中的所有列进行匹配。即key的值一并给出，然后查找。
2. 匹配最左前缀
前面提到的索引可用于查找所有姓位Allen的人，即只使用索引的第一列。
3. 匹配列前缀
也可以只匹配某一列的值的开头部分。如查找所有以J开头的姓的人。
4.匹配范围值
可用于查找姓在Allen和Barrymore之间的人。
5.精确匹配某一列并范围匹配另外一列
6.只访问索引的查询
索引树的节点是有序的，所以除了按值可查找之外，索引还可以用于查询中的ORDER BY。

(重点)B-Tree索引的限制：
· 如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的例子，无法用于查找名字为Bill的人，也无法查找某个特定的生日的人，因为这两列都不是最左数据列。
· 不能跳过索引中的列。
· 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。例如查找