企业网站定制开发一文搞懂 MySQL 索引

一文搞懂 MySQL 索引



1、MySQL 索引 简介

1.1、MySQL 索引 是什么？

 企业网站定制开发索引是一个单独的、存储在 磁盘 上的 企业网站定制开发数据库结构 ，企业网站定制开发包含着对数据表里 企业网站定制开发所有记录的 引用指针。


1.2、 MySQL 索引 企业网站定制开发的存储类型有哪些？

 MySQL企业网站定制开发中索引的存储类型有两种，即 BTree 和 Hash。


1.3、MySQL 索引 企业网站定制开发在哪里实现的？

 企业网站定制开发索引是在存储引擎中实现的。（MySQL 企业网站定制开发的存储引擎有：InnoDB、MyISAM、Memory、Heap）

  • InnoDB / MyISAM 只支持 BTree 索引
  • Memory / Heap 都支持 BTree 和 Hash 索引

1.4、存储引擎 是什么？

 企业网站定制开发存储引擎就是指 表的类型 以及 企业网站定制开发表在计算机上的存储方式。


1.5、索引 企业网站定制开发的优缺点有哪些？

优点：

  • 提高数据的查询的效率（类似于书的目录）
  • 可以保证数据库表中每一行数据的唯一性（唯一索引）
  • 减少分组和排序的时间（使用分组和排序子句进行数据查询）
    • 被索引的列会自动进行分组和排序

缺点：

  • 占用磁盘空间
  • 降低更新表的效率（不仅要更新表中的数据，还要更新相对应的索引文件）


2、MYSQL 索引 的分类

1、普通索引 和 唯一索引

  • 普通索引：MySQL 中的基本索引类型，允许在定义索引的列中插入 重复值 和 空值

  • 唯一索引：要求索引列的值必须 唯一，但允许 有空值

    • 如果是组合索引，则列值的组合必须 唯一
    • 主键索引是一种特殊的唯一索引，不允许 有空值

2、单列索引 和 组合索引

  • 单列索引：一个索引只包含单个列，一个表可以有多个单列索引
  • 组合索引：在表的 多个字段 组合上 创建的 索引
    • 只有在查询条件中使用了这些字段的 左边字段 时，索引才会被使用（最左前缀原则）

3、全文索引

  • 全文索引 的类型为 fulltext
  • 在定义索引的 列上 支持值的全文查找，允许在这些索引列中插入 重复值 和 空值
  • 全文索引 可以在 char、varchar 和 text 类型的 列 上创建

4、空间索引

  • 空间索引 是对 空间数据类型 的字段 建立的索引

  • MySQL中的空间数据类型有4种，分别是 Geometry、Point、Linestring 和 Polygon

  • MySQL 使用 Spatial 关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引

  • 创建空间索引的列，不允许为空值，且只能在 MyISAM 的表中创建。

5、前缀索引

  • 在 char、varchar 和 text 类型的 列 上创建索引时，可以指定索引 列的长度


3、MySQL 索引 的

 MySQL 索引 的数据结构可以分为 BTree 和 Hash 两种，BTree 又可分为 BTree 和 B+Tree。


Hash：使用 Hash 表存储数据，Key 存储索引列，Value 存储行记录或行磁盘地址。

 Hash 只支持等值查询（“=”，“IN”，“<=>”），不支持任何范围查询（原因在于 Hash 的每个键之间没有任何的联系），Hash 的查询效率很高，时间复杂度为 O(1)。


BTree：属于多叉树，又名多路平衡查找树。

性质：

  • BTree 的节点存储多个元素（ 键值 - 数据 / 子节点 的地址）
  • BTree 节点的键值按 非降序 排列
  • BTree 所有叶子节点都位于同一层（具有相同的深度）

查询过程，例如：Select * from table where id = 6;

BTree 的不足：

  • 不支持范围查询的快速查找（每次查询都得从根节点重新进行遍历）
  • 节点都存储数据会导致磁盘数据存储比较分散，查询效率有所降低

B+Tree：在 BTree 的基本上，对 BTree 进行了优化：只有叶子节点才会存储 键值 - 数据，非叶子节点只存储 键值 和 子节点 的地址；叶子节点之间使用双向指针进行连接，形成一个双向有序链表。

等值查询，例如：Select * from table where id = 8;

范围查询，例如：Select * from table where id between 8 and 22;

B+Tree 的优点：

  • 保证了等值查询和范围查询的快速查找
  • 单一节点存储更多的元素，减少了查询的 IO 次数


4、MySQL 索引 的实现

4.1、MyISAM 索引

  • MyISAM 的 数据文件（.myd） 和 索引文件（.myi） 是分开存储的
  • MyISAM（B+Tree）叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址
  • MyISAM 的 主键索引（Primary key）和 辅助索引（Secondary key）在结构上没有任何区别，只是 主键索引 要求 键值唯一，而 辅助索引 键值 可以重复


4.2、InnoDB 索引

  • 数据和索引都存储在一个文件中（.ibd）

  • 一般情况下，聚簇索引等同于主键索引；除 聚簇索引 外的所有索引 均称为 辅助索引

  • InnoDB（B+Tree）叶子节点中存储的键值为索引列的值

    • 如果是聚簇索引，数据为整行记录（除了主键值）
    • 如果是辅助索引，数据为该行的主键值

  • 每一张表都有一个聚簇索引

    • 如果表中有定义主键，主键索引用作聚簇索引
    • 如果表中没有定义主键，选择第一个不为 NULL 的唯一索引列用作聚簇索引
    • 如果以上都没有，使用一个 6 字节长整形的隐式字段 ROWID （自增）用作聚簇索引

  • 根据在 辅助索引树 中获取的 主键id，再到 主键索引树 查询数据的过程 称为 回表 查询

  • 组合索引

    • 遵循 最左匹配（最左前缀）原则：
      • 使用 组合索引 查询时，MySQL 会一直向右匹配直至遇到范围查询（>、<、between、like）就停止匹配。
    • 只有第一列是有序的，其它列都是无序的（最左匹配原则的原因）

主键索引（聚簇索引）：

辅助索引：

组合索引：

覆盖索引：

  • 覆盖索引不是一种索引结构，而是一种优化手段
  • 我们只需要查询 组合索引 中的字段，而不需要表中的其它字段，在这过程中不会产生回表现象，这种情况称为 覆盖索引
create index idx on user(name, age, gender);
  • 1
-- 使用覆盖索引explain select name, age, gender from user where name ='万叶' and age = 18 and gender = '0'; 
  • 1
  • 2

-- 未使用覆盖索引explain select * from user where name ='万叶' and age = 18 and gender = '0';
  • 1
  • 2



5、MySQL 索引 的使用

5.1、MySQL 索引 的基本语法

  • 定义 主键约束、外键约束、唯一约束 等约束时 相当于同时在指定列上创建了一个索引

创建表时：

create table table_name(	[col_name data_type] [unique | fulltext | spatial...],    [unique...] [index | key] [index_name] (col_name [length], ...));create table user (    id INT NOT NULL,     name CHAR(30) NOT NULL,     unique index uniqueIdx(id) );
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

表已存在时：

-- 第一种 alter table table_name 	add [unique...] [index | key] [index_name] (col_name [length], ...);	alter table user add unique index uniqueIdx(id);-- 第二种 create [unique...] index index_name on table_name (col_name [length], ...);create unique index uniqueIdx on user(id);-- 删除索引 drop index index_name on table_name;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

5.2、怎么判断要不要加索引？

加索引：

  • 数据本身具有某种的性质，如：唯一性、非空性…
  • 频繁进行 分组或排序 的列；如果待排序的列有多个，可以建立 组合索引

不加索引：

  • 经常更新的列
  • 列 的值类型 很少，如 性别
  • where 条件中用不到的列
  • 参与计算的列
  • 数据量小的表

5.3、只要创建了索引，就一定会生效吗？

 不一定。当使用 组合索引 时，如果没有遵循 最左匹配 原则，索引不生效。

例如，创建 id、name、age 组合索引

  • id、（id、name）、（id、name、age）查询，索引生效
  • age、（age、name）查询，索引不生效

5.4、怎样判断索引是否生效？

 使用 explain 关键字。

  • possible_keys：MySQL 在搜索数据记录时可选用的各个索引
  • key：MySQL 实际选用的索引

例如：

explain select * from user where id = 1;
  • 1


5.5、怎么避免索引失效?

  • 使用组合索引时，遵循 最左匹配 原则
  • 不在索引列上进行任何操作，如：计算、函数、类型转换
  • 尽量使用覆盖索引
  • 索引列 尽量不使用 不等于（!= / <>）条件、通配符开头的模糊查询（like %abc）、or 作为连接条件
  • 字符串加单引号（不加可能会发生索引列的隐式转换，导致索引失效）
网站建设定制开发 软件系统开发定制 定制软件开发 软件开发定制 定制app开发 app开发定制 app开发定制公司 电商商城定制开发 定制小程序开发 定制开发小程序 客户管理系统开发定制 定制网站 定制开发 crm开发定制 开发公司 小程序开发定制 定制软件 收款定制开发 企业网站定制开发 定制化开发 android系统定制开发 定制小程序开发费用 定制设计 专注app软件定制开发 软件开发定制定制 知名网站建设定制 软件定制开发供应商 应用系统定制开发 软件系统定制开发 企业管理系统定制开发 系统定制开发