索引
本文将对索引的知识点进行总结归纳
索引的知识总结
1. 索引的概念
索引是一种数据结构,能够帮助我们快速地检索数据库中的数据
2. 索引具体采用哪种数据结构
常见的MySQL主要有两种索引的数据结构,Hash索引和B+ Tree索引,我们能使用的InnoDB引擎,默认的是B+ Tree。
当然了,MyISAM也是使用B+ Tree作为索引的数据结构,至于Hash索引,HEAP/MEMORY引擎才支持。
3. 采用B+ Tree索引和Hash索引对比起来有什么优缺点
- 如果是等值查询,那么哈希索引会有明显优势,因为只需要经过一次算法就可以找到相应的键值;当然了,前提是键值都是唯一的。如果键值不唯一,就需要找到该键的所在位置,然后再根据链表(或其他数据结构)往后扫描,直到找到相应的数据;
- 如果是范围查询检索的话,Hash索引就毫无用武之地了,因为原先有序的键值,经过Hash算法后,有可能会编程不连续的了,就没法进行范围查询检索。而B+ Tree(B Tree进行少许改进,每个叶子节点都有指针连着),由于结构的改进,不仅能够进行范围查询,而且比B Tree查询效率更高;
- 同理,Hash索引也没办法利用索引完成排序,以及
like ‘xxx%’
这样的部分模糊查询(这种部分模糊查询,本质上也是范围查询); - Hash索引也不支持多列联合索引的最左匹配规则;
- B+ Tree索引的关键字检索效率比较平均,不像B Tree那样波动幅度大,在有大量重复键值的情况下,Hash索引的效率也是很低的,因为存在所谓的Hash冲突问题。
4. 最左匹配原则
在MySQL建立联合索引时会遵循最左前缀匹配规则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例:
对列col1、列col2和列col3建立联合索引
alter table `table_name` add index (`col1`,`col2`,`col3`);
联合索引实际建立了 (col1)、(col1,col2)、(col1,col2,col3) 三个索引
5. MyISAM存储引擎与InnoDB存储引擎索引实现的不同(存储结构)
- MyISAM引擎——主键索引
使用B+ Tree作为索引结构,叶节点的data域存放的是数据记录的地址。原理图:
- MyISAM引擎——辅助(非主键)索引
以col2列建立索引,得到的辅助索引结构跟上面的主键索引的结构是相同的。原理图:
- InnoDB引擎——主键索引
我们已经知道InnoDB索引是聚簇索引,它的索引和数据是存放在同一个.idb文件中的,因此它的索引结构是在同一个树节点中同时存放索引和数据,如下图所示最底层的叶子节点有三行数据,对应于数据表中的col1 col2 col3
数据项:
- InnoDB引擎——辅助(非主键)索引
这次我们以数据表中的col3列的字符串数据建立辅助索引,它的索引结构与主键索引的结构有很大差别,我们来看下面的图:
在最底层的叶子节点有两行数据,第一行的字符串是辅助索引,按照ASCII码进行排序,第二行的整数是主键的值:
6. 选择UUID还是整型自增作为主键
UUID 是 通用唯一识别码(Universally Unique Identifier)的缩写,是一种软件建构的标准,亦为开放软件基金会组织在分布式计算环境领域的一部分。其目的,是让分布式系统中的所有元素,都能有唯一的辨识信息,而不需要通过中央控制端来做辨识信息的指定。如此一来,每个人都可以创建不与其它人冲突的UUID。在这样的情况下,就不需考虑数据库创建时的名称重复问题。目前最广泛应用的UUID,是微软公司的全局唯一标识符(GUID),而其他重要的应用,则有Linux ext2/ext3文件系统、LUKS加密分区、GNOME、KDE、Mac OS X等等。另外我们也可以在e2fsprogs包中的UUID库找到实现。
InnoDB推荐使用整型自增主键而不是UUID
- UUID是字符串,比整型消耗更多的存储空间
- 在B+ Tree中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速
- 自增的整型索引在磁盘中会连续存储,在读取一页数据时也是连续的;UUID是随机产生的,读取的上下两行数据是分散的,不适合执行范围查询的条件语句
- 在插入或删除数据时,整型自增主键会在叶子节点的末尾建立新的叶子节点,不会破坏左侧子树的结构;UUID很容易会出现这样的情况,B+ Tree本质上是平衡树,为了维持平衡,可能会进行结构的重构,消耗更多的时间
推荐使用UUID而不是整型自增作为主键的场景
- UUID作为主键在分库分表、合并数据时比较有用
- 但在海量数据面前,UUID性能比整型自增主键性能差
7. 聚集索引和非聚集索引在查询数据的时候有什么区别
- 聚集索引树的叶子节点就是我们要查找的整行数据了
- 非聚集索引树的叶子节点是当前索引和域值为主键的值组成的,查找过程中可能存在回表(当需要查找的数据不在节点上,需要通过主键的值,在聚集索引树再查找一遍)操作
- 并非所有非聚集索引都需要回表,这是因为,如果要查找数据在索引上就能找到,就不需要回表,而是直接得到数据
8. 在创建联合索引的时候,联合索引多个字段的顺序我们该如何选择
- 根据最左前缀匹配原则,我们需要把查询概率较高的字段摆在最左端
9. 查询优化器
一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪一种方案,需要通过优化器进行选择,选择执行成本最低的方案。在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下:
- 根据搜索条件,找出所有可能使用的所有
- 计算全表扫描的代价
- 计算使用不同索引执行查询的代价
- 对比各种执行方案的代价,找出成本最低的那一个