一.什么是索引
1.数据库为了提高检索效率的一种数据结构
2.存储在磁盘的文件中,占用物理空间
二.构建索引的数据结构需要满足哪些条件
1.支持高效查询(减少IO)
2.支持范围查询
3.支持插入更新等操作
三.索引分类
3.1数据结构维度
B+树索引:所有数据存储在叶子节点,复杂度为O(logn),适合范围查询。
哈希索引: 适合等值查询,检索效率高,一次到位。
全文索引:MyISAM和InnoDB中都支持使用全文索引,一般在文本类型char,text,varchar类型上创建。
R-Tree索引: 用来对GIS数据类型创建SPATIAL索引
3.2物理存储维度
聚集索引:聚集索引就是以主键创建的索引,在叶子节点存储的是表中的数据。(Innodb存储引擎)
非聚集索引:非聚集索引就是以非主键创建的索引,在叶子节点存储的是主键和索引列。(Innodb存储引擎)
3.3逻辑维度
主键索引:一种特殊的唯一索引,不允许有空值。
普通索引:MySQL中基本索引类型,允许空值和重复值。
联合索引:多个字段创建的索引,使用时遵循最左前缀原则。
唯一索引:索引列中的值必须是唯一的,但是允许为空值。
空间索引:MySQL5.7之后支持空间索引,在空间索引这方面遵循OpenGIS几何数据模型规则。
四.索引结构(B+树)
B+树是一种基于B树的索引结构,它的叶子节点存储了所有的索引记录,在叶子节点之间使用指针连接起来,因此可以支持范围查询和按序遍历。 以下是一张B+树的示意图:
+--------------------------------------------------+
| |
+--------------+ +--------------+
| 30 | 40 | -------> | 70 | 80 |
+--------------+ +--------------+
/ \ \
+---------------+ +---------------+ +---------------+ +--------------+
| 10 | 20 | | 30 | 40 | | 50 | | 70 | 80 |
+---------------+ +---------------+ +---------------+ +--------------+
在这个示意图中,每个节点都有指向其它节点的指针。
所有的叶子节点都包含索引条目(也就是真正的数据),但是没有子节点指针;
非叶节点仅包含索引条目,并且有指向下一层子节点的指针;
叶节点相互连接,形成链表;非叶节点相互连接,形成索引树。
在B+树中,搜索通常从根节点开始逐层向下进行。当搜索到叶子节点时,检索到的值便出现在该节点上了。
总体来说,B+树的索引结构方便查找和范围查询,这是因为B+树的叶子节点之间都是通过指针链接起来的。
五.MySQL索引为什么要采用B+树数据结构
5.1为什么其它数据结构不合适?
1.哈希表存在哈希冲突,同时不支持高效的范围查找
2.跳表层级高的时候,查询不高效,需要多次IO
3.树结构中,搜索二叉树容易退化为链表,不能支持高效查找
4.树结构中,B-tree范围查找不是很高效,且因为每个结点都会存储数据,当数据量大的时候,树高会变大,从而增加IO次数,效率变低
5.2为什么B+Tree结构就合适?
1.B+树只在叶子结点存储数据,非叶子节点存储索引键值,从而可以保证树的高度稳定。
2.叶子结点通过指针形成链表,支持高效范围查找;
3.叶子结点会冗余一部分非叶子节点的数据,在增删数据的时候,不会存在复杂的结构变换(比如平衡树的旋转变换)
六.在什么情况下要建立索引
1.使用WHERE子句过滤大量数据时需要使用索引。
2.在ORDER BY子句中进行排序操作时,可以使用索引来加快查询速度。
3.通过JOIN连接表时,使用索引可以加快连接操作的速度。
七.索引失效的情况
1.使用 SELECT * 进行查询;
2.创建了组合索引,但查询条件未遵守最左匹配原则;
思考:具体的执行过程?
3.在索引列上进行计算、函数、类型转换等操作,及使用(!= 或者 < >);
思考:使用(!= 或者 < >)什么时候失效?
4.以 % 开头的 LIKE 查询比如 like ‘%abc’;
思考:具体的执行过程?
5.查询条件中使用 or,且 or 的前后条件中有一个列没有索引,涉及的索引都不会被使用到;
思考:哪些情况生效? 哪些情况不生效?为什么?
6.发生隐式转换;
7.左右连接,关联的字段编码格式不一样
思考: 未建立主键索引,普通索引会失效吗?
八.EXPLAIN的使用
九.MySQL各版本中对索引大小限制(InnoDB引擎)
1.联合索引
MySQL 3.23:最大联合索引长度为1000字节。
MySQL 4.0.x/4.1.x/5.0.x:最大联合索引长度为1000字节。
MySQL 5.1.x:最大联合索引长度为3072字节(InnoDB存储引擎),或1000字节(MyISAM存储引擎)。
MySQL 5.5.x/5.6.x/5.7.x:最大联合索引长度为3072字节(InnoDB存储引擎),或1000字节(MyISAM存储引擎)。
MySQL 8.0.x:最大联合索引长度为3072字节(InnoDB和MyISAM存储引擎)。
2.单列索引
MySQL 5.6 及之前版本: InnoDB 存储引擎中单列索引的大小限制为767字节。这个限制是由于InnoDB存储引擎默认使用UTF-8字符集,而UTF-8编码中一个字符可能占用3个字节的存储空间,所以InnoDB将限制单列索引不能超过255个字符(因为255*3=765)。
MySQL 5.7:InnoDB 存储引擎中单列索引的大小限制已经被扩展到3072字节。这个改变主要是为了适应更长的JSON列索引。
MySQL 8.0:InnoDB 存储引擎中单列索引的大小限制依然是3072字节。
参阅:
https://juejin.cn/post/7193682380077400122
https://juejin.cn/post/6850037271233331208#heading-24
https://juejin.cn/post/7081065180301361183(为什么要使用B+树结构)
https://javaguide.cn/database/mysql/mysql-query-execution-plan.html#%E5%A6%82%E4%BD%95%E8%8E%B7%E5%8F%96%E6%89%A7%E8%A1%8C%E8%AE%A1%E5%88%92(执行计划)