avatar

Mysql 索引基础

索引介绍

索引是帮助Mysql高效获取数据的数据结构,就像一本书前面的目录,能加快数据库的查询速度。

优势

  • 可以提高数据检索效率,降低数据库的IO成本 —检索
  • 通过索引列对数据排序,降低数据排序的成本降低CPU的消耗 —排序
    • 被索引的列会自动进行排序,包括【单列索引】、【组合索引】。组合索引的排序要复杂一些
    • 如果是按照索引列的顺序进行排序,对应order by语句,效率就会提升很多
    • where 索引列,在存储引擎层处理,这里使用到了索引下推ICP
    • 覆盖索引 select 字段 字段是索引列,则需要回表查询

劣势

  • 索引会占据磁盘空间
  • 索引提高了查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,Mysql不仅要保存数据,还要保存或者更新对应的索引文件

索引分类

单列索引

  • 普通索引:Mysql中的基本索引类型,允许在定义索引的列中插入重复值和空值,纯粹为了查询速度更快一些
  • 唯一索引:索引列中的必须是唯一的,但是允许空值
  • 主键索引:特殊的唯一索引,不允许空值

组合索引

  • 在表中多个列上创建的索引
  • 组合索引的使用,需要遵循最左前缀原则
  • 建议使用组合索引替代单列索引

全文索引

只有在MyIsam引擎上使用,而且只能在CHAR、VARCHAR、TEXT类型字段才能使用全文索引

空间索引

索引使用

创建索引

创建索引时,可以指定索引列使用多大的长度作为索引,但是数值类型不要指定

  • 单列索引–普通索引

    1
    2
    CREATE INDEX index_name ON table(column(length));  //方法1
    ALTER TABLE table_name ADD INDEX index_name (column(length)); //方法2
  • 单列索引–唯一索引

    1
    2
    CREATE UNIQUE INDEX index_name ON table(column(length));  //方法1
    ALTER TABLE table_name ADD UNIQUE INDEX index_name (column(length)); //方法2
  • 单列索引–全文索引

    1
    2
    CREATE FULLTEXT INDEX index_name ON table(column(length));  //方法1
    ALTER TABLE table_name ADD FULLTEXT INDEX index_name (column); //方法2
  • 组合索引

    1
    ALTER TABLE table_name ADD INDEX index_name (column1(length),column2(length));

删除索引

  • 1
    DROP INDEX index_name ON table

查看索引

  • 1
    SHOW INDEX FROM table  \G

索引原理分析

页、块、扇区

内存这个单位去进行IO读取,一般大小为4k,在Mysql中可以通过innodb_page_size设置大小,一般设置为16k
操作系统这个逻辑单位去操作磁盘,常见为4k
磁盘扇区这个物理最小磁盘单位去存储数据,常见为512Byte

大小查看:getconf PAGE_SIZE,常见为4k
大小查看:stat /boot/|grep "IO Block",常见为4k
扇区大小查看:fdisk -l,常见为512Byte

指针默认长度为6bit,如果key为bigint则是8bit,那么一个索引为8+6=14bit

索引存储结构

  • 索引是在存储引擎中实现的,也就是说不同的存储引擎使用不同的索引
  • MyISAM和InnoDB存储引擎:支持B+ TREE索引,也就是说默认使用BTREE,不能更换
  • Memory存储引擎:支持HASH、BTREE索引

B树和B+树

数据结构演示网站:https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

  • B树

    • B树是为了磁盘或其他存储设备而设计的一种多叉平衡查找树
    • B树的高度一般都在2-4这个高度,树的高度直接影响IO读写次数
    • 如果是三层树结构—可以支撑20G的数据,如果是四层结构—可以支撑几十 T
      B TREE
  • B+ 树
    B TREE

  • 区别

    • B树和B+树的最大区别在于非叶子节点是否存储数据
      1. B树是非叶子节点和叶子节点都会存储数据
      2. B+树叶子节点存储数据,而且存储的数据都是在一行上,这些数据都是有指针指向的,也就是有顺序的。

非聚集索引(MyISAM)

B+树叶子节点只会存储数据行(数据文件)的指针,也即是数据和索引存储在不同的文件就是非聚集索引
非聚集索引包含主键索引辅助索引都会存储指针的值

  • 主键索引
    非聚集主键索引

  • 辅助索引(次要索引)
    非聚集辅助索引

聚集索引(InnoDB)

完整的记录,存储在主键索引中,通过主键索引就可以获取记录所有的列,也就是说数据和索引是在一起
主键索引必须要有,表如果没有设置主键,则系统会默认生成一个隐藏的唯一列,由该列去创建key作为主键
主键最好是int
次要索引存储主键索引的主键key,不存储表数据

  • 主键索引
    聚集主键索引

  • 辅助索引(次要索引)
    聚集辅助索引

    注意聚集索引如果是非主键查询,则需要搜索两次索引树(一次是搜索次要索引树,一次是搜索主键索引树)才能查出数据

    聚集辅助索引

文章作者:
文章链接: https://www.fundodoo.com/zh-CN/2020/04/11/11.html
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 醉探索戈壁
打赏
  • 微信
    微信
  • 支付寶
    支付寶

评论