一、索引概述  1.1 索引的介绍

    索引index:是帮助 Mysql高效获取数据有序的数据结构,在数据之外,数据库系统维护着的满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引

  1.2 索引的优缺点

    • 优点1:提高数据检索的效率,降低数据库的IO成本
    • 优点2:通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗
    • 缺点1:索引列也要占磁盘空间。
    • 缺点2:索引大大提高了查询效率,同时却也降低了更新表的速度,如对表进行insert,update,delete时,效率降低

二、索引结构  2.1 Mysql的索引常见结构

    Mysql的索引是在储存引擎层实现的,不同的存储引擎有不同的结构,主要包含一下几种 

    • B+树:最常见的索引类型,大部分引擎都支持B+树索引
    • Hash索引:底层数据结构是用哈希表实现的,只有精确匹配索引的查询才有效,不支持范围查询 

  2.2 Mysql常见索引对不同引擎的支持

    • B+树:InnoDB(支持)、MyISAM(支持)、Memory(支持)
    • Hash索引:InnoDB(不支持)、MyISAM(不支持)、memory(支持)

  2.3 二叉树实现索引的弊端

    

    说明1:实际中的索引是没有使用二叉树的,因为二叉树具有一下的弊端  

    说明2:当顺序插入时,会形成一个链表,查询性能大大降低,大数据量的情况下,层级较深,检索速度慢。

    说明3:特殊二叉树红黑树当做索引是,大数据量情况下,层级比较深,检索速度慢

  2.4 B树实现索引的弊端

    以一个最大度数(max-degree)为5(5阶)的b树为例(每个节点最多储存4个key,5个指针)

    

    说明:B树的数据会存在每个节点上,而节点存在页(2.6 Mysql索引对B+树的优化有说明)上面,每页的大小为16K,这样每个页能存放的索引就比较少,导致同样数据体积小,层级要比B+树深。

  2.5 B+树实现索引

    以一个最大度数(max-degree)为4(4阶)的b+树为例

     

    说明:对比较与B树

      1、所有的数据都会出现在叶子节点上

      2、叶子节点形成一个单向链表

  2.6 Mysql索引对B+树的优化

    Mysql索引数据结构对经典的B+树进行了优化,在原来的B+树基础上,增加了一个指向相邻叶子节点的链表指针,就行了带有顺序指针的B+树,提高了区间访问的性能

    

    说明:每页在InnoDB中默认16K

  

  2.7 hash索引

    哈希索引就是采用一定的hash算法,将键值换成新的hash值,映射到对应的槽位上,然后储存在hash表中

     

    说明:如果两个(或者多个)键映射到同一个槽位上,他们就产生了hash冲突,也称hash碰撞,可以通过链表来解决

  

  2.8 hash索引特点

    • hash索引只能用于对等比较(=,in),不支持范围查询(between,>,<)
    • 无法利用索引完成排序操作
    • 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+树索引
    • 在Mysql中,支持hash索引的事Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+树索引在指定条件下自动构建的

  2.9 InnoDB引擎选择B+树的优势

    • 相对于二叉树,层级更少,搜索效率高
    • 对于B树,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,同样保存大量数据,只能增加树的高度,导致性能降低。
    • 相对于hash索引,B+树支持范围匹配及排序操作

三、索引分类  3.1 主键索引

    针对于表中主键创建的索引,默认自动创建,只能有一个, 关键字:primary

  3.2 唯一索引

    避免同一个表中某数据列中的值重复,可以有多个,关键字:unique

  3.3 常规索引

    快速定位特定数据,可以有多个,

  3.4 全文索引

    全文索引查找的是文本中的关键字,而不是比较索引中的值,可以有多个,fulltext

  3.5 聚集索引

    在InnoDB中,根据索引的储存形式划分的,将数据储存与索引放到一起,索引结构的叶子节点保存了行数据,必须有,而且只有一个

    • 如果存在主键,主键索引就是聚集索引
    • 如果不存在主键,将使用第一个唯一(unique)索引作为聚集索引
    • 如果表没有主键,也没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引

  3.6 二级索引

    在InnoDB中,根据索引的储存形式划分的,将数据与索引分开储存,索引结构的叶子节点关联的是对应的主键,可以存在多个

   

    说明:聚集索引下面存放的是整行的数据,二级索引下面存放的对应的主键,要不然聚集索引下存放了整行数据,二级索引下也放整行数据,就会很冗余

  3.7 回表查询

    

    说明1:首先根据name字段走二级索引

    说明2:找到Arm对应的id=10

    说明3:然后再根据id=10找到对应的数据

    说明4:整个过程也叫做回表查询

四、索引语法  4.1 查看索引

show index from 表名

    示例:

mysql> show index from account;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+1 row in set (0.00 sec)

    说明:account 有一个主键索引

  4.2 创建索引

create [unique | fulltext] index 索引名 on 表名(索引的列名, ..); 

    说明1:如果创建索引的字段是唯一的,值都不重复,可以加unique约束,说明这是一个唯一字段索引

    说明2:fulltext 是全文检索索引,主要针对大的文本字段

mysql> create index name_idx on account(name);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from account;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       || account |          1 | name_idx |            1 | name        | A         |           4 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+2 rows in set (0.01 sec)

    说明1:这就创建了一个名为name_idx的索引

  4.3 删除索引

drop index 索引名 on 表名

    示例

mysql> drop index name_idx on account;Query OK, 0 rows affected (0.00 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> show index from account;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| account |          0 | PRIMARY  |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+1 row in set (0.00 sec)

    说明:这就删除了一个索引

五、预告

  后面的文章会继续介绍索引的使用和设计原则


侯哥语录:我曾经是一个职业教育者,现在是一个自由开发者。我希望我的分享可以和更多人一起进步。分享一段我喜欢的话给大家:”我所理解的自由不是想干什么就干什么,而是想不干什么就不干什么。当你还没有能力说不得时候,就努力让自己变得强大,拥有说不得权利。”