mysql数据库的索引

最新推荐文章于 2024-11-09 21:51:58 发布

dengtuo7157

最新推荐文章于 2024-11-09 21:51:58 发布

阅读量90

点赞数

文章标签：数据库数据结构与算法面试

原文链接：http://www.cnblogs.com/wangsen/p/10864136.html

版权

什么是索引

索引就是一种优化查询的数据结构；

为什么要加索引

因为创建索引可以大大提高系统的查询性能。

怎么提高查询性能的

简单的理解：一张数据量比较大的表格如果没有添加任何索引，那我们在执行查询的时候
就会是进行全表扫描，逐行比对，这样的读取效率肯定很低，如果我们为数据创建了索引
索引的实现方式又是支持快速查询的这样我们只需要先查询索引中符合条件的，
然后再通过索引指向的数据行位置就可以实现快速定位数据了，不用全表扫描了。

索引存储在什么地方

数据库索引是存储在磁盘上，当表中的数据量比较大时，索引的大小也跟着增长，达到几个G甚至更多。
当我们利用索引进行查询的时候，不可能把索引全部加载到内存中，只能加载一部分其他的都要从磁盘中读取后加载到内存。

索引分哪些类型？

主键索引
一个列为设置为主键会默认创建一个主键索引。
唯一索引
严格来说主键索引是唯一索引的一种，这两个的区别是：主键索引不允许为null，唯一索引可以为null;
单列索引
复合索引

磁盘读取的相关知识

什么时候会去读取磁盘
当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，
磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行
内存读取磁盘的单位
上面提到的页我们可以简单理解是内存读取磁盘数据中的最小单位。
后面的索引实现中树中的每个节点大小往往和磁盘中一页的大小相同，这是为了提高读取的效率。
磁盘预读和程序运行的局部原理
根据程序的局部性原理：当一个数据被使用，它附近的数据通常也会马上被使用，所以为了减少磁盘io，
内存读取磁盘数据的时候会顺序向后预先读取一定长度的数据放入内存。（页的整数倍）

为什么哈希表、完全平衡二叉树、B树、B+树都可以优化查询，为何Mysql选择B+树？

哈希表

哈希表的两个缺点：

哈希表可能会出现哈希冲突。
最主要的原因是它不支持范围查询。

完全平衡二叉树

如图如果一个树的高度很大，如果查询的数据刚好在叶子节点那经历的磁盘Io的次数就是这个数的高度。
所以极端情况下平衡二叉树也不是优选。

B-Tree

先说说几个概念：

度（节点的数据存储个数）也就是说B-Tree上的一个节点可以存储多个数据。
叶节点具有相同的深度
叶节点的指针为空
节点中的数据从左到右递增排列

这的确解决了树的高度问题，因为：B-Tree的节点可以存储多个值，高度肯定小于平衡二叉树，磁盘io的次数也会少。
但是在范围查找方面较比B+Tree差点。
题外话：B-Tree和BTree是一种树。

B+Tree

B+Tree是B-Tree的变种，

B+树的表示要比B树要“胖”，原因在于B+树中的非叶子节点会冗余一份在叶子节点中，并且叶子节点之间用指针相连。

mysql的实现中，B+Tree是把非叶子节点中只存储索引，不存储数据，只有叶子节点存储数据，
这样节点中的空间更多的存储了索引，增加了度。最大程度的降低了树的高度
加上一个节点的大小设置成为一页或页的倍数，一次磁盘io就可以读出了一个节点中的很多数据，
加载到内存中再进行查找就很快了

所以最后总结：
使用B+Tree:可以提高查询索引时的磁盘IO效率，并且可以提高范围查询的效率，并且B+树里的元素也是有序的。

MyISAM和InnoDB的B+Tree实现

索引是数据库引擎去实现的，在建立表的时候都会指定，数据库引擎是一种插拔式的，根据自己的选择去决定使用那个！

MyISAM索引实现（非聚集）

MYISAM中叶子节点的数据区域存储的是数据记录的地址。
MyISAM中的主键索引和辅助引是没有区别的，其叶子节点存放的都是数据记录的地址。
【MyISAM的索引文件和数据文件是分离的】

InnoDB的索引实现（聚集）

InnoDB中的叶子节点数据区域存储的内容和主键索引和辅助索引是有区别的：

主键索引存储的就是索引+数据（index+data）

Innodb的主键索引要比MyISAM的主键索引查询效率要高，因为找到主键索引就找到了数据,MyISAM还有通过地址查询一次。
辅助索引存储的是主键的值

因此可以看出InnoDB的辅助索引会发生两次，一次通过辅助索引查询主键索引，一次是通过主键索引查询到数据。
【InnoDB的索引文件是和数据文件放在一起的】

聚集索引和非聚集索引

聚集索引

数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引。

聚集索引的叶子节点存放有对应的数据节点，可以直接获取到对应的数据，
如果不创建索引，系统会自动创建一个隐含列作为表的聚集索引。
最好还是在创建表的时候添加聚集索引
在经常用于查询或聚合条件的字段上建立聚集索引。这类查询条件包括 between, >, <,group by, max,min, count等。
缺点：
插入和更新索引的速度会比较慢，因为将会导致被更新的行移动。

非聚集索引

数据行的物理顺序与列值的逻辑顺序不相同，一个表中可以拥有多个非聚集索引。

叶子节点存放的不是实际数据，而是指向实际数据的指针。
聚集索引以外的索引都是非聚集索引,细分可以分为：普通索引，唯一索引，全文索引

注意：

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引

创建索引的依据

什么样的字段适合建索引

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在具备下述特性的列上创建索引：

第一、在经常搜索的列上
第二、在主键上，强制该列的唯一性和组织表中数据的排列结构；
第三、在被用于连接（内连，外连）的字段上，主要是一些外键
第四、常被进行范围搜索的列上，因为索引已经排序，其指定的范围是连续的；
第五、经常参与排序的字段上，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
第六、经常被用于作为条件查询的字段上（WHERE子句中的列），加快条件的判断速度。
建立索引，一般按照select的where条件来建立，
比如： select的条件是where f1 and f2，那么如果我们在字段f1或字段f2上建立索引是没有用的，只有在字段f1和f2上同时建立索引才有用等。