索引定义MySQL的索引是一种数据库可以帮助数据库高效查询、更新数据索引通过一定规则排列数据表中记录使得对表的查询可以一个索引搜索加快速度。索引应该选择哪种数据结构1.hash时间复杂度:HashMap平均时间复杂度 O (1)发生哈希冲突时JDK1.7 链表最坏 O (n)JDK1.8 链表超过 8 转为红黑树最坏 O (logn)。但是对于数据库查询不适合用哈希表因为哈希表不支持高效的范围查询例如我们要查1-10的数据无法快速查询。2.二叉搜索树其中序遍历是一个有序数组索引支持范围查找理想平衡时 O (logn)。但是如果插入有序数据会退化成单边斜树复杂度退化 O (n)。,同时节点过多就无法保证树高度而且访问节点过多也会约束 性能因为数据库数据都是存在磁盘每次访问子节点都会发生磁盘IO约束数据库性能主要原因3.N叉树每个节点可以超过两个字节点解决树高问题,即通过可以降低访问子节点次数就能找到目标节点提高数据库效率时间复杂度为OlogN)4.B树B树是经常用于数据库和文件系统的平衡查找树时间复杂度为OlogN),MySQL索引采用的数据结构以4阶B树为例Mysql的B树B 树由大量 16KB 页组装而成分为分支节点非叶子索引页、叶子节点数据页非叶子索引页只存分界 key 子页号不存完整业务数据用于树的跳转。叶子节点存放完整真实行数据叶子页之间维护双向链表专门用于范围查询。B树B树vsB树1.B树的可以通过叶子节点访问到兄弟节点但是B树不可以MySQL在组织叶子节点时用的是双向链表2.B树非叶子节点的值保存在叶子节点中MySQL非叶子节点中存的是节点的引用叶子节点存的才是真实数据。3.对于B树来说在相同树高情况下任何元素时间复杂度一样性能均衡。索引优点大大减少服务器扫描的数据量B 树高度很低几百万数据树高一般 3‑4 层。查询时不用扫描全表顺着 B 树从根节点快速定位叶子节点只读取少量数据页避免全表扫描。帮助避免排序、避免临时表B 树叶子节点的数据本身就是有序排列。如果 order by、group by 的字段刚好建立索引直接读取有序叶子链表不需要数据库再做 filesort 排序也减少临时表生成。把随机 IO 变成顺序 IO通过范围查询B 树先定位到第一个目标叶子数据页。 在这个数据页内部利用页目录的槽快速定位本页第一条满足条件的数据行 在当前页内遍历本页记录拿数据 如果条件匹配的数据不止当前页就依靠叶子节点之间的双向链表跳到下一个叶子页继续读取形成磁盘顺序 IO。如果没有索引到处找数据就是随机 IO顺序 IO 效率远高于随机 IO。MySQL中的页.idb文件最重要的数据结构就是页页是内存与磁盘交互的最小单元默认大小16KB每次内存与磁盘交互至少读一页每个页内部内存是连续的这是因为下次使用的数据很大概率与当前访问的数据空间是相近的所有一次从磁盘读取一页的数据放入内存下次查询数据还在页中就可以从内存直接读取从而减少磁盘I/O提高性能。每一个页中即使没有数据也会使用16KB的存储空间与索引的B树节点对应页内主要信息InnoDB 数据存放在.ibd独立表空间文件最小存储单元是页固定 16KB。页结构页头 File Header、数据行记录区、空闲区、页尾 File Trailer。页头里面保存上一页、下一页页号实现页之间双向链表。页内部两条核心机制帮助快速查找记录next‑record 单向链表页内记录物理存储可以乱序依靠next‑record地址偏移量把记录串成单向链表有两个哨兵最小行 Infimum、最大行 Supremum。页目录槽 slot将页内记录分组每组最多 8 条槽保存每组最后一条记录的主键 偏移。拿到页之后对槽数组做二分快速定位目标记录所在分组槽只能定位组组内再顺着 next‑record 遍历找真实记录避免遍历整个链表。查找主键id5完整流程磁盘把根索引页 1 加载到 Buffer Pool 内存本页使用页目录槽二分定位分组组内 next‑record 遍历判断57找到子页索引页 2加载索引页 2同样用本页槽二分命中 key5拿到对应叶子数据页 2加载叶子数据页 2叶子页的槽二分定位分组组内遍历 next‑record找到 id5 完整行查询结束。索引分类1.主键索引当在一个表定义一个主键自动创建索引索引的值就是主键列的值InnoDB使用它作为聚集索引聚簇索引2.普通索引最基本索引类型无唯一限制为了提升查询效率工作中经常把查询频繁的列创建索引并且此列重复度不高例如学生表的姓名3.唯一索引当在表中定义一个唯一键UNiQuiE自动创建唯一索引但是不允许有重复值4.全文索引用于全文搜索仅MyISAM和InnoDB引擎⽀持。5.聚集索引每个表一定有且有一个聚集索引。如果表定义主键值主键索引就是聚集索引。如果表没有定义主键值InnoDB使用第一个UNIQUE和NOT NULL列作为聚集索引。如果表中没有PRIMARY KEY或合适的UNIQUE索引InnoDB会为新插⼊的⾏⽣成⼀个⾏号并⽤6字节的ROW_ID字段记录ROW_ID单调递增并使⽤ROW_ID做为索引。6.非聚集索引二级索引聚集索引以外索引为非聚集索引二级索引每条二级索引每条记录都包含该行的主键值非聚集索引的查询过程1.通过二级索引查询到我们要查询记录对应的主键值通过记录的主键值去主键索引树找到对应完整记录。回表查询如果说我们要查询的列包含在二级索引中就不要回表查询称为索引覆盖select name,id from t where namexxx;需要的列name、id全部在二级索引条目内覆盖索引不用回表select name from t where id 10;这个不能走 index (name) 这个二级索引主键只是附带存储该索引 B 树是按照索引键name)排序不是按主键排序。使用索引1.创建索引-- 主键索引 -- 创建表时候指定主键 create table t_pk1( id bigint primary key auto_increment, name varchar(20) ); -- 创建表时单独指定主键 create table t_pk2( id bigint AUTO_INCREMENT, name varchar(20), primary key (id) ); -- 修改表中的列为主键 create table t_pk3( id bigint, name varchar(20) ) alter table t_pk3 add primary key (id); alter table t_pk3 modify id bigint auto_increment; -- 唯一索引 create table t_uk( id bigint primary key auto_increment, name varchar(20) unique ); create table t_uk2( id bigint primary key auto_increment, name varchar(20), unique(name) ); create table t_uk3( id bigint primary key auto_increment, name varchar(20) ); alter table t_uk3 add unique(name); -- 普通索引 create table t_index( id bigint primary key auto_increment, name varchar(20) UNIQUE, sno varchar(10), index(sno) ); -- 修改表中列为普通索引 create table t_index2( id bigint primary key auto_increment, name varchar(20) unique, sno varchar(20) ); alter table t_index2 add index(sno); -- 单独创建索引并指定索引名 create table t_index3( id bigint primary key auto_increment, name varchar(20) unique, sno varchar(20) ); create index indexname on t_index3 (sno); -- -- 创建复合索引 create table t_index4( id bigint primary key auto_increment, name varchar(20), sno varchar(20), index(sno,name) ); -- 修改表中列为普通索引 create table t_index5( id bigint primary key auto_increment, name varchar(20) , sno varchar(20), Class_id bigint ); alter table t_index5 add index(name,sno); -- 单独创建索引并指定索引名 create table t_index6( id bigint primary key auto_increment, name varchar(20) , sno varchar(20), Class_id bigint ); create index idx_sno_name on t_index6 (name,sno);2.删除索引-- 删除索引 -- 删除主键索引 -- 如果是自增列必须改为非自增 alter table t_index5 modify id bigint; alter table t_index5 drop primary key; show index from t_index5; -- 删除其他索引 alter table t_index5 drop index idx_sno_name; show index from t_index5;3.查看索引各种索引查询后结果4.怎么查看自己的查询有没有走sql可以查询执行计划使用指令explain不加条件查询所有通过主键索引查询使用联合查询