Mysql高级3-索引的结构和分类
一、索引概述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索引特点
<ul><li ><ul>hash索引只能用于对等比较(=,in),不支持范围查询(between,>, 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 创建索引
show index from 表名 说明1:如果创建索引的字段是唯一的,值都不重复,可以加unique约束,说明这是一个唯一字段索引
说明2:fulltext 是全文检索索引,主要针对大的文本字段
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) 说明1:这就创建了一个名为name_idx的索引
4.3 删除索引
create index 索引名 on 表名(索引的列名, ..); 示例
mysql> create index name_idx on account(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0Duplicates: 0Warnings: 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) 说明:这就删除了一个索引
五、预告
后面的文章会继续介绍索引的使用和设计原则
来源:https://www.cnblogs.com/Se7eN-HOU/p/17586395.html
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!
页:
[1]