十一、索引

2024-03-12 16:38:44

什么是索引

索引类似于一本书中的目录,起到优化查询，加快查询速度的作用。

索引的分类(算法)

面试题会出现。

B树      #MySQL默认使用的索引算法
R树      #用的很少，了解即可
Hash     #也叫自适应hash索引，存储引擎自动做的优化，不需要人为管理
FullText #全文索引，跟搜索引擎有关
GIS      #存储地理位置信息，如谷歌地图，百度地图使用的算法

B树索引算法演变

由二分法演变而来

什么是二分法？
举例说明:
猜1-100之间的数字，砍掉一般，猜此数是否在1-50或者50-100之间，以此类推。
缺点: 该数在边缘时，如该数为1时，很耗费时间。

B tree分类
初级: B- tree
中级: B+ tree
高级: B* tree (目前mysql使用的算法)

B- tree示例图如下

如查找27这个数，发现大于5小于28，则从5开始往下查找。
27大于20，从20继续往下走。
最后在叶子节点发现27.

特点
根节点、枝节点、叶子节点每一个数据块都占用16kb大小,也就是一个数据页。
根节点存储的是每一个枝节点的最小值，枝节点存储的是每一个叶子节点的最小值。
P1,P2,P3代表指针，指向对应位置
注意该树叶子节点没有Q，此图不规范

B+ tree示例图如下

对于范围的查找做了优化，因为叶子节点中都是相邻的数据，可以通过Q(双向指针)来跳跃到相邻的数据上去，

B* tree在B+ tree的基础上进一步优化了范围查找功能。

索引分类

Btree索引从功能上的分为

辅助索引(可细分为以下三类)
1.1 单列辅助索引(即一个列用来创建索引)
1.2 联合索引(覆盖索引)
1.3 唯一索引(创建的索引列值是唯一不重复的，如果没有主键列会被升级为聚集索引)
聚集索引

参考资料: 什么是联合索引

辅助索引

提取索引列的所有值,进行排序
将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
在叶子节点中的值,都会对应存储主键ID

示例
在student表中，sname被设置为辅助索引，sname列会被单独提取出来进行排序，然后存放在叶子节点中，然后在反向生成枝节点跟根节点。

当我们使用sql语句查找zhang3时

select * from student where sname = 'zhang3';

则会通过根节点找到枝节点，然后找到zhang3这个名字，zhang3同时还包含一个索引值，通过该索引值最后找到整条数据。

聚集索引
聚集索引就是上述zhang3包含的索引值，通过他找到最终的整条数据，此时的整行数据就在叶子节点，id即索引值在根节点，通过id查找整条数据。
辅助索引辅助聚集索引。
强制用户输入数据的时候有序的排列。如student表中的sno列。
主键列是mysql自动维护的，只需要创建主键列，通常列值都为数字，自增。

特点

MySQL会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏主键(mysql5.5之后有此功能)
MySQL进行存储数据时,会按照聚集索引列值得顺序,有序存储数据行
聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列(即ID列)向上生成枝和根

覆盖索引
直接通过辅助索引找到整条数据行，而不再需要通过辅助索引再去查询聚集索引最后得到整条数据，但是很难实现。

聚集索引跟辅助索引的联系
辅助索引相当于通过知识点去找目录所在页码(即聚集索引的id值)，然后通过页码去找数据。

聚集索引和辅助索引的区别

表中任何一个列都可以创建辅助索引,只要索引名不同即可
在一张表中,聚集索引只能有一个,一般是主键
辅助索引,叶子节点只存储索引列的有序值+聚集索引列值
聚集索引,叶子节点存储的时有序的整行数据
MySQL 的表数据存储是聚集索引组织表

索引树高度

索引树高度应当越低越好,一般维持在3-4最佳。

数据行数较多时也就是表存储较大时怎么办?
分表 : parttion 也就是将单表分成多个小表，如今用的比较少。
分片 : 分布式架构,将表分离成多个小表，存放在多个数据库中进行查询。

影响索引树高度的因素
1、字段长度
当业务允许时,尽量选择字符长度短的列作为索引列
当业务不允许时,采用前缀索引

2、数据类型
作为索引的列，最好使用varchar，因为char不足时会以空格填充，这会增加索引长度。
能使用enum时可以使用enum作为辅助索引列。

索引命令操作

查询索引

mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| sno   | int(11)             | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)         | NO   | MUL | NULL    |                |
| sage  | tinyint(3) unsigned | NO   |     | NULL    |                |
| ssex  | enum('f','m')       | NO   |     | m       |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

#Key: 是否为索引
#PRI: 主键
#MUL: 辅助索引
#UNI: 唯一索引

查看索引详细信息

Key_name: 索引名
Column_name: 列名

也可以竖行显示

创建索引
主键是建表时创建，所以一般创建的是辅助索引等。

#创建辅助索引
mysql> alter table student add index idx_name(sname);
#idx_name: 索引名字，可以随便起，但是要让别人知道这是一个索引
#sname: 需要定义索引的列

#创建多列联合索引
mysql> alter table student add index idx_sa_ss(sage,ssex);
mysql> desc student;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| sno   | int(11)             | NO   | PRI | NULL    | auto_increment |
| sname | varchar(20)         | NO   | MUL | NULL    |                |
| sage  | tinyint(3) unsigned | NO   | MUL | NULL    |                |
| ssex  | enum('f','m')       | NO   |     | m       |                |
+-------+---------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

创建唯一索引
1、要确定列值没有重复值

#创建报错，说明列有重复值
mysql> alter table score add unique index uidx_sc(score);
ERROR 1062 (23000): Duplicate entry '40' for key 'uidx_sc'

#使用count跟distinct来确定列是否有重复值
mysql> select count(score) from score;
+--------------+
| count(score) |
+--------------+
|           17 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(distinct score) from score;
+-----------------------+
| count(distinct score) |
+-----------------------+
|                    14 |
+-----------------------+
1 row in set (0.00 sec)
#可以发现，score行数共有17行，去重复后只有14行，说明有重复值

2、创建唯一索引

mysql> alter table teacher add unique index uidx_tn(tno);

mysql> desc teacher;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| tno   | int(11)     | NO   | PRI | NULL    |       |
| tname | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3、创建前缀索引
注意前缀索引只能针对字符串列。

#将sname的前5个字符作为前缀索引
mysql> alter table student add index idx_sname(sname(5));

删除索引

1、先知道索引名

2、再根据索引名删除索引

mysql> alter table student drop index idx_sname;

学习来自：郭老师博客，老男孩深标DBA课程第四章

码农公寓