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)[/code]　　　　说明：account 有一个主键索引
　　4.2 创建索引

1. show index from 表名

复制代码

　　　　说明1：如果创建索引的字段是唯一的，值都不重复，可以加unique约束，说明这是一个唯一字段索引
　　　　说明2：fulltext 是全文检索索引，主要针对大的文本字段

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

复制代码

　　　　说明1：这就创建了一个名为name_idx的索引
　　4.3 删除索引

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

复制代码

　　　　示例

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

复制代码

　　　　说明：这就删除了一个索引
 
五、预告

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

来源:https://www.cnblogs.com/Se7eN-HOU/p/17586395.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！