十一、索引
什么是索引
索引类似于一本书中的目录,起到优化查询,加快查询速度的作用。
索引的分类(算法)
面试题会出现。
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代表指针,指向对应位置
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课程 第四章