一、索引概述
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: 0 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 | | 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: 0 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)
说明：这就删除了一个索引
五、预告
后面的文章会继续介绍索引的使用和设计原则
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
