MySQL索引原理
2021-08-23 17:35:56 0 举报AI智能生成
登录查看完整内容
MySQL索引原理
作者其他创作
大纲/内容
索引原理
类型
介绍
索引可以提升查询速度，会影响where、以及order by排序
分类
从存储结构
B树索引、Hash索引、FULLTEXT索引、R树索引
从应用层次
普通索引、唯一索引、复合索引
从键值类型
主键索引，辅助索引（二级索引）
从数据存储和索引键值逻辑关系
聚集索引、非聚集索引
1. 普通索引
最基本的索引类型，基于普通字段建立的索引，没有任何限制
操作
Create index [name] on tablename(fieldname);
Alter table tablename add index[name](fieldname);
Drop index [name] on tablename;
2. 唯一索引
与普通索引类似，不同就是：索引字段的值必须唯一，但允许有空值
创建或修改表时追加唯一约束，就会自动创建对应的唯一索引
Create unique index [name] on tablename(fieldname);
Alter table tablename add unique index[name](fieldname);
3. 主键索引
一种特殊的唯一索引，不允许有空值
在创建或修改表时追加主键约束即可，每个表只能有一个主键
Alter table tablename Add Primary key [fieldname];
4. 复合索引
单一索引是指索引列为一列的情况，可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）
复合索引在数据库操作期间所需的开销更小，可以代替多个单一索引
概念
窄索引
指索引列为1-2列的索引
宽索引
索引列超过2列的索引
设计索引的一个重要原则就是能用窄索引就不用宽索引，因为窄索引往往比宽索引更有效
注意事项
复合索引字段是有顺序的，在查询时要按照索引字段的顺序使用
何时使用复合索引，要根据where条件建立索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响
如果建立了组合索引，就没有必要建立单一索引，反之可以建立，对于查询有一定提高
5. 全文索引
数据量较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。使用全文索引，查询速度会比like快很多
MySQL5.6之前，只有MyISAM引擎支持全文索引，5.6开始MyISAM和InnoDB都支持
Create Fulltext index [name] on tablename(field);
Alter table tablename add Fulltext [name] (field);
和常用的like模糊查询不同，全文索引有自己的语法格式，使用match和against关键字
全文索引必须在字符串、文本字段上建立
全文索引字段值必须在最小字符和最大字符之间的才会有效
innodb：3-84
myisam：4-84
全文索引字段值要进行切词处理，按syntax字符进行切割
如b+aaa，切分为b和aaa
全文索引匹配查询，默认使用的是等值匹配
如a不会匹配ab、ac
想匹配可使用布尔模式搜索‘a*’
原理
MySQL官方对索引的定义
是存储引擎用于快速查找记录的一种数据结构，需要额外开辟控件和数据维护工作
索引是物理数据页存储，在数据文件中，利用数据页存储
索引可以加快间缩速度，但是同时也会降低增删改操作速度，索引维护需要代价
二分查找法
也叫折半查找法，是在有序数组中查找指定数据的搜索算法
优点
等值查询、范围查询性能优秀
缺点
更新数据、新增数据、删除数据维护成本高
Hash索引
非常适合根据Key查找value，也就是单个key查询，或者说等值查询
Hash索引对于范围查询需要全表扫描，效率不高
Hash索引在MySQL中Hash结构主要应用在Memory原生的Hash索引、InnoDB自适应Hash索引
自适应Hash索引
InnoDB自适应哈希索引是为了提升查询效率，存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问非常频繁时，会在内存中基于B+树索引再创建一个哈希索引，使内存中的B+书索引具备Hash索引的功能，即能够快速定值访问频繁访问的索引页
它的建立使得InnoDB存储引擎能够自动根据索引页访问的频率和模式自动为某些热点页建立哈希索引来加速访问
该功能用户只能选择开启或关闭，无法人工干涉
B+树索引
MySQL数据库采用的是B+树结构，在B-树结构上做了优化改造
B-树结构
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
B+树结构
非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能
B-树搜索
从根节点开始，对节点的索引值序列采用二分法查找，如果命中就结束查找
没有命中会进入子节点重复查找过程，直到所对应的节点指针为空，或已经是叶子节点了才结束
相比B-树，B+树进行范围查找
只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可
B树需要遍历范围内所有的节点和数据，显然B+树效率高
聚集索引
是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建B+树结构
B+树的叶子节点就是行记录，行记录和主键值紧凑的存储在一起，这意味着InnoDB的主键索引就是数据表本身，它按照主键顺序存放了整张表的数据，占用空间就是整张表数据量的大小
通常的主键索引就是聚集索引
InnoDB表要求必须要有聚集索引
如果表定义了主键，则主键索引就是聚集索引
如果表没有定义主键，则第一个非空unique列作为聚集索引
否则InnoDB会建一个隐藏的row-id作为聚集索引
辅助索引
InnoDB辅助索引，也叫做二级索引，是根据索引列构建B+树结构
但在叶子节点中只存放了索引列和主键的信息
二级索引占用的空间会比聚集索引小很多，通过创建辅助索引就是为了提升查询效率
一张表只能创建一个聚集索引，但可以创建多个辅助索引
非聚集索引
MyISAM数据表的索引文件和数据文件是分开的，被称为非聚集索引结构
MySQL
0 条评论
回复 删除
下一页
