SQL索引，原理及其优缺点

目录

一、概念

二、复合索引最左特性（原则）

三、原理

四、索引及其扫描类型

五、索引的优缺点

---

一、概念

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。同时，大多数索引都采用BTree树方式构建。

索引包括：单值索引：一个索引只包括一个列，一个表可以有多个列

唯一索引：索引列的值必须唯一，但允许有空值

复合索引（联合索引）：一个索引同时包括多列

二、复合索引最左特性（原则）

在Mysql建立复合索引时有最左前缀的原则。当建立一个复合索引(col1,col2,col3)时，实际上建立了三个索引，即(col1)、(col1,col2)、(col1,col2,col3)。

三、原理

复合索引的结构是B+树，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex。当age或者sex字段缺失时，b+树就不知道第一步该查哪个节点，所以只能把名字name的数据都找到，即索引的最左匹配特性。

B+树的实现原理：

如果我们要查询的数字为13：首先读取页1进行一次IO，使其进入内存中：判断13位于17到35的左边；然后读取页二进行第二次IO，进入内存后：判断13位于8到12的右边；最后读取页7，进行IO后找到所要查找的数据：13（注：只有叶子节点的数据才是真正存储的数据）

四、索引及其扫描类型

索引类型：

ALL: 全表扫描，没有优化，最慢的方式 index: 索引全扫描 range: 索引范围扫描，常用语<，<=，>=，between等操作 ref: 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中 eq_ref : 类似ref，区别在于使用的是唯一索引，使用主键的关联查询 const : 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理 null: MySQL不访问任何表或索引，直接返回结果 System: 表只有一条记录(实际中基本不存在这个情况) 性能排行： System > const > eq_ref > ref > range > index > ALL

五、索引的优缺点

优点:

索引是数据库优化 表的主键会默认自动创建索引 大量降低数据库的IO磁盘读写成本，极大提高了检索速度 索引事先对数据进行了排序，降低查询是数据排序的成本，降低CPU的消耗

缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间 索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的” 虽然索引大大提高了查询的速度，但反向影响了增、删、改操作的效率。如表中数据变化之后，会造成索引内容不正确，需要更新索引表信息，如果数据量非常巨大，重新创建索引的时间就大大增加 随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引