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;
    

posted @   LaViez  阅读(43)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
点击右上角即可分享
微信分享提示