MySQL学习笔记（五）—MySQL索引详解

水墨之白

已于 2022-10-12 11:15:01 修改

阅读量524

点赞数

分类专栏： MySQL 文章标签：索引 MySQL

于 2019-04-28 09:30:44 首次发布

本文链接：https://blog.csdn.net/LJJZJ/article/details/89631596

版权

MySQL 专栏收录该内容

15 篇文章 1 订阅

订阅专栏

注：本文基于InnoDB引擎

一、数据库索引简介

加快数据库查询速度而建立的目录

二、创建索引语法

首先创建一个表：create table t1 (id int primary key,username varchar(20),password varchar(20));
创建单个索引的语法：CREATE INDEX 索引名 on 表名（字段名）
索引名一般是：表名_字段名
给id创建索引：CREATE INDEX t1_id on t1(id);
创建联合索引的语法：CREATE INDEX 索引名 on 表名（字段名1，字段名2）
给username和password创建联合索引：CREATE index t1_username_password ON t1(username,password)
其中index还可以替换成unique，primary key，分别代表唯一索引和主键索引
删除索引：DROP INDEX t1_username_password ON t1
alter 表名 add index 索引名（field1，filed2）这种写法也可以创建索引

三、索引数据结构

MySQL数据索引使用的是B+树，多路平衡查找树。

特性：

由于磁盘预读，数据库每次读取行数据时会读取该行所在的页
设二叉树的阶数为m，则m/2 <= k <= m

B+树的节点存储元素数量为k - 1，所以树节点的元素数量等于数据库的页大小，默认16k

B+树的叶子节点都在同一层，按顺序形成一个有序链表,所以表会按照主键索引自增排序

问题1：为什么使用B+树，不使用二叉查找树？

假设有1亿页数据，二叉树有1亿节点，该二叉树太高，查询效果差，而使用B+树存储， 100阶的B+树，高度为3，最多3次IO即可查到数据

B+树每一个节点，可以存放多个元素，二叉查找树每个节点只能保存一个数据，B+树的IO次数在数据量较大情况下会远远小于二叉查找树，数据比较次数相差不大

B+树中间节点不保存数据，只保存索引，同样大小的节点可以存储更多数据，树会更加矮胖

问题2：为什么不使用B树,散列表？

1）B树查找性能不稳定，不需要遍历到叶子节点可能就查询到数据

2）索引需要支持范围查找，在B树中范围查找只能使用中序遍历，还需要跨层，而B+树在查到最小值只需要遍历链表即可，同理散列表不支持范围查询

四、索引分类

主键索引

每张表有且只有一个主键索引树
主键索引B+树中，叶子节点存储所有数据，中间节点只保存索引

聚集索引

聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。主键索引默认是聚集索引，如果表中没有定义主键，InnoDB会选择一个唯一的非空索引代替，这个一般是InnoDB自动创建的隐式主键

辅助索引(联合索引，二级索引)

辅助索引可以是表的其它列，可以是一个也可以是多个
辅助索引树叶子节点的data域存储的是主键，中间节点存储的是索引

所以再通过辅助索引查询时会先遍历辅助索引树，再遍历主键索引树，即先根据辅助索引查询主键再查询具体数据，这个过程称为回表

回表是一个随机读取的过程，而随机读取非常耗时

索引下推：在遍历索引过程中，根据where条件对索引中包含的字段优先进行判断，减少回表次数

一个注意点：

联合索引(a,b,c),在索引树中，叶子节点有三个索引完整数据 + 主键数据，中间节点是三个索引+页号+主键值，非叶子节点称为内节点

在查询过程中会先根据a索引找到对应叶子节点，叶子节点的排序是先根据a索引排序，再根据b索引，再根据c索引，所以此时只需要遍历叶子链表即可找到指定的行数据。

覆盖索引：查询列全部包含在辅助索引中，不需要回表根据主键在查询数据，直接返回结果，这种索引也被称为宽索引，反之称为窄索引

建立辅助索引时，如果索引字段很长，可以使用部分匹配，比如在身份证号码上面建索引，为了节省空间可以使用身份证前6位作为索引，但是可能会造成多次回表，同时不能利于覆盖索引。

根据唯一性分类：

唯一索引
主键索引是只能有一个，并且索引列值不能为空，唯一索引可以建多个，索引列值可以为空，但是只能有一行，一般将唯一索引列设为Not Null
普通索引
普通索引会使用change buffer，在写多读少的情况下，性能稍好（下一篇文章中会详细说明）

五、最左前缀原则

CREATE TABLE `test` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

索引ca可以去掉，因为c和主键ab，和ca和主键ab相同

数据库走索引的原则会走联合索引的第一个索引列,进行匹配

 select * from test where b= ?
 不走索引，

select * from test where a= ? and c=?
先走索引c再走主键索引(a,b)

select * from test where c between 2 and 6

执行流程：

在c索引树找到c = 2所在的记录的主键
再到主键索引树找到对应主键索引的数据
再到c索引树找到c = 6所在的记录的主键
再到主键索引树找到对应主键索引的数据
再到c索引树取下一记录c = 7，不满足条件，循环结束

上述范围查询只在边界回表，找到对应行记录的完整数据，然后在主键索引树取出所有在范围内的记录

新建表：
CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` varchar(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(20) NOT NULL,
  `d` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ID_key` (`a`,`b`,`c`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

1. EXPLAIN SELECT * from t WHERE a = ‘20’ and b=10 and c = ‘50’

匹配到联合索引

2. EXPLAIN SELECT * from t WHERE a = '20’and b>=10 AND b<=40 and c =‘50’

尽管中间b是范围匹配，但是还是可以匹配联合索引，范围过滤性能稍差于等值匹配

3. EXPLAIN SELECT * from t WHERE a = ‘20’ and c = ‘30’

最左匹配，注意这里key_len=35，说明只匹配到了a索引，依然可以走联合索引

4. EXPLAIN SELECT a, b, c from t WHERE b = 40 and c = ‘50’

没有联合索引列首列a，不走索引，按道理是这样，可是执行计划确是走索引，大家是不是很疑惑？

注意，这里的索引类型是index，这种类型表示是MySQL会对整个该索引进行扫描，根据索引的顺序一个个进行匹配然后回表取数据，效率非常差，不是通过二分查找法在索引树上找到需要匹配的数据

出现这种情况需要满足两个条件：

where后面的列条件必须是联合索引的一部分
select查询的列也必需是联合索引的一部分

不满足上述条件就会就行全表扫描

5. EXPLAIN SELECT * from t WHERE b = 40 and c = ‘50’

完全按照预期猜想，不匹配联合索引，全表扫描

六、索引的缺点

索引存在磁盘上，虽然能加快读，但是会给写数据库带来性能的下降，首先索引文件过多或者选取的索引列过长都会造成索引数据过大，占据过多磁盘空间，其次在B+树的页中插入数据，页溢出，会造成一系列页分裂，同时删除页中的行，也会造成页的合并，这些操作都会给写数据库带来性能的下降，所以不可以盲目的建索引

创建索引和维护索引要耗费时间，这样就降低了数据的维护速度，这种时间随着数据量的增加而增加

七、索引设计

三星索引：

与查询相关的索引行是相邻的，也就是where后面的等值谓词，可以匹配索引列顺序
索引行的顺序与查询语句需求一致，也就是order by 中的排序和索引顺序是否一致
索引行包含查询语句中所有的列

理解：

第一颗星，其实就是说要最窄化索引片，比如where后面有很多匹配条件，我们优先将这些条件取出来，顺序无所谓，但是必须相邻
第二颗星，其实就是说要尽量避免排序，前面已经说过，索引本身是有顺序的，我们需要将需要排序的列放在联合索引的靠前部分
第三颗星，其实就是说要尽量使用覆盖索引

举例说明一下：

select id, name, sex, age, score from user where sex='b' and score between 80 and 90 order by age;

建立索引(sex, score, age, id, name)

该索引满足：

第一颗星：where 条件相邻，追求最窄索引片
第三颗星：查询的内容都包含在索引中，不需要回表
不满足第二颗星，需要在找出满足sex=‘b’，对score进行范围匹配后，就还需对age进行排序

建立索引(sex, age, score, id, name)

该索引满足：

第二颗星，在匹配到sex=‘b’后的数据无序进行排序
第三颗星，上面解释过
不满足第一颗星，没有追求最窄索引化，只匹配了sex，然后用age排序，没有进行优先范围匹配

索引设计小结：

当一个 SQL 查询中同时拥有 范围谓词和 ORDER BY 时，无论如何我们都是没有办法获得一个三星索引的，我们能够做的就是在这两者之间做出选择，是牺牲第一颗星还是第二颗星，相对而言，第三颗星还是容易达到，但是为了达到这颗星，是否应该把所有的列都设计到索引列里面？这需要我们根据具体情况而定，因为索引过长，也会造成过多的额外数据，页分裂等，影响写入数据库性能

索引设计的本心，是为了方便查询的，而不是只为设计索引，脱离了查询的索引设计，并不高效，三星索引知识指导我们设计理想索引的方式，具体情况还得我们自己把握。

水墨之白

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL学习笔记（五）—MySQL索引详解

注：本文基于InnoDB引擎一、数据库索引简介加快数据库查询速度而建立的目录二、索引数据结构MySQL数据索引使用的是B+树，多路平衡查找树。特性：由于磁盘预读，数据库每次读取行数据时会读取该行所在的页设二叉树的阶数为m，则m/2 <= k <= mB+树的节点存储元素数量为k - 1，所以树节点的元素数量等于数据库的页大小，默认16kB+树的叶子节点都...
复制链接

扫一扫