MySQL21 - 索引
索引
什么是索引
- 索引相当于一本书的目录,为了缩小扫描范围而存在、提高查询效率的一种数据结构
- 一张表的一个字段可以添加索引,多个字段也可以联合起来添加索引
- 在任何数据库中,主键都会自动添加索引,另外在MySQL中,一个字段如果有 UNIQUE 约束,也会自动添加索引
- 优点
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本
- 缺点
- 索引列也要占用空间 -- (空间便宜)
- 降低更新表的速度,对表进行 DML操作时效率低 -- (DML语句比例小,主要是查询)
- 在任何数据库中,任意一张表的任意一条记录在硬盘上都有一个硬盘的物理存储编号
- 在MySQL中,索引是一个单独的对象,在不同的存储引擎中以不同的形式存在,无论何种引擎,索引都以 数 的形式存储,结构是B+Tree
- 在InnoDB存储引擎中,索引存储在一个逻辑名称叫做 tablespace 的表空间中
- 在MyISAM存储引擎中,索引存储在 .MYI 文件中
- 在MEMORY存储引擎中,索引被存储在内存中
索引的结构
- B+Tree索引:最常见的索引类型,大部分引擎都支持B+树索引
- 相对于二叉树,层级更少,搜索效率更高
- 对于B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,导致一页中存储的键值减少,指针也减少,要同样保存大量数据,只能增加树的高度,导致性能降低
- MySQL对B+Tree作了优化,叶子节点间不再是单向链表而是双向链表
- Hash索引:底层数据结构由哈希表实现,只有精确匹配索引列的查询才有效,不支持范围查询
- R-Tree
- Full-text
索引的分类
- 主键索引
- 关键字:PRIMARY KEY
- 针对表中主键创建的索引,默认自动创建,只能有一个
- 唯一索引
- 关键字:UNIQUE
- 避免同一表中某列的数据重复,可以有多个
- 常规索引
- 用于快速定位数据,可以有多个
- 全文索引
- 关键字:FULLTEXT
- 全文索引查找的是文本中关键词,而不是索引的值,可以有多个
InnoDB存储引擎中,索引又分一下两种
- 聚集索引
- 必须有且只有一个
- 将数据存储与索引放在了一块,索引结构的叶子节点保存了数据
- 如果存着主键,主键索引就是聚集索引
- 如果不存在主键,使用第一个唯一 UNIQUE 索引作为聚集索引
- 如果没有主键也没有唯一索引,InnoDB会自动生成一个 rowid 作为隐藏的聚集索引
- 叶子节点存放每一条完整记录
- 二级索引
- 将数据和索引分来存储,索引结构的叶子节点关联的是对应的主键,可以存在多个
- 叶子节点存放每一条记录的聚集索引列的值
- 查询二级索引时,先通过二级索引的叶子节点获取聚集索引的值,再通过聚集索引查询完整记录 => 也就是说,通过二级索引查询记录必然需要通过聚集索引查询 -- 这个过程称为:回表查询
索引的语法
-
创建索引
-
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name, ... );
-
-
查看索引
-
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_sta ON tb_user(profession, age, statue);
-
联合索引会按字段序编号
为email建立合适的索引 -- 常规索引
-
CREATE INDEX idx_user_email ON tb_user(email);
删除email索引
-
DROP INDEX idx_user_email ON tb_user;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析