MySQL知识点总结（三）索引为什么使用B+Tree而不是，线性数据Hash,AVL,B-Tree

最新推荐文章于 2024-03-21 10:53:12 发布

黄山路443号

最新推荐文章于 2024-03-21 10:53:12 发布

阅读量357

点赞数

分类专栏：高性能MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/dong_W_/article/details/104696388

版权

高性能MySQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

2. MySQL索引优化

2.1 MySQL性能下降的原因

索引失效，join太多，服务器调优及各个参数设置

2.2创建高性能的索引

什么是索引？索引是帮助MySQL高效获取数据的数据结构。索引是数据结构！

索引分类

单值索引一个索引只包含单个列，如主键
唯一索引索引列的值必须唯一，允许空值
复合索引

#创建索引
CREATE [UNIQUE] INDEX indexName ON mytable(columname(length));
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));
#ALTER 添加索引
ALTER TABLE tb_name ADD PRIMARY KEY (column_list);#添加主键
ALTER TABLE tb_name ADD UNIQUE index_name(column_list);#唯一索引，可为NULL
ALTER TABLE tb_name ADD INDEX index_name(column_list);#普通索引，索引值可出现多次
ALTER TABLE tb_name ADD FULL TEXT index_name(column_list);#全文索引
#删除索引
DROP INDEX [indexName] ON mytable;
#查看索引
SHOW INDEX FROM table_name\G;

2.3 索引的类型

B-Tree /B+Tree MyISAM InnoDB
哈希索引 Memory
空间数据索引R-Tree MyISAM
全文索引

B+Tree索引的特点，与有序数组，Hash，AVL-Tree，B-Tree的优势

B+树中，非叶节点不保存数据相关信息，只保存关键字和子节点的引用。所有记录节点存放在叶子节点上，且是顺序存放，由各叶子节点指针进行连接。如果从最左边的叶子节点开始顺序遍历，能得到所有键值的顺序排序。

B+树的高度一般为2-4层，叶子节点存储的数据通常是一页（默认16k）或一页的整倍数（MySQL每次读取都是读一页）。
范围查找时，能通过叶子节点的指针获取数据。例如查找大于等于3的数据，当在叶子节点中查到3时，通过3的尾指针便能获取所有数据，而不需要再像二叉树一样再获取到3的父节点。

有序数组则是节点存储数据无限大的树，数据量大时太耗内存，而树可以一个节点一个节点加载。
Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以：
- 哈希索引适合等值查询，但是无法进行范围查询
- 哈希索引没办法利用索引完成排序
- 哈希索引不支持多列联合索引的最左匹配规则
- 如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
相对于AVL树，B+Tree一个节点可以存储多个元素，相对于完全平衡二叉树所以整棵树的高度就降低了，磁盘IO效率提高了
B树不管叶子节点还是非叶子节点，都会保存数据，并且B树由于叶子节点之间没有链表连接，范围查询时会有局部中序遍历、跨层遍历

表中的数据删除后，索引上对应的索引值是不会删除的，特别是在一性次删除大批量数据后，会造成大量的dead leaf挂到索引树上。

2.4 聚簇索引和非聚簇索引/主键索引和辅助索引/覆盖索引

聚簇索引，就是指主索引文件和数据文件为同一份文件，聚簇索引在Innodb存储引擎中存在。
MYISAM中B+Tree索引叶子节点的数据区域存储的是数据记录的地址， MyISAM存储引擎在使用索引查询数据时，会先根据索引查找到数据地址，再根据地址查询到具体的数据。并且主键索引和辅助索引没有太多区别。
在 InnoDB 里，主键索引B+ Tree的叶子节点存储了整行数据，辅助索引存储的是主键值（都不是地址）。Innodb的一个表一定要有主键索引，如果一个表没有手动建立主键索引，Innodb会查看有没有唯一索引，如果有则选用唯一索引作为主键索引，如果连唯一索引也没有，则会默认建立一个隐藏的主键索引（用户不可见）。另外，Innodb的主键索引要比MyISAM的主键索引查询效率要高（少一次磁盘IO），并且比辅助索引也要高很多。所以，我们在使用Innodb作为存储引擎时，我们最好：
1. 手动建立主键索引
2. 尽量利用主键索引查询
覆盖索引,即索引包含有所需要的数据

下一篇
MySQL知识点总结（四）MySQL性能分析，explain与索引优化