索引
文章目录
添加索引
create
创建索引时必须指定索引名alter table
创建索引时可不指定索引名- 默认为字段名,主键除外
-- 唯一索引: 在table_name 表中col_name 字段添加唯一索引index_name
create unique index index_name
on table_name (col_name [(length)] [asc / desc])
-- 主键索引: 在table_name 表中col_name 列添加主键索引
alter table table_name add primary key (col_name)
/*
主键索引 和 唯一索引 在指定字段约束时默认为索引,
或建表之后使用以上方式添加
*/
-- 普通索引 (所有索引都可用这两种创建方式)
-- 方式1
create index index_name on tab_name (col_name [(length)] [asc / desc])
-- 方式2
alter table `table_name` add index [index_name](index_col_name)
删除索引
- 删除主键索引 primary key 和唯一索引 unique 会同时删除约束
- 修改索引:删除原字段索引,重新创建
-- 在table_name 表中删除 index_name 索引
drop index index_name on table_name
-- 删除主键索引
-- 从table_name表中删除主键索引
alter table `table_name` drop primary key
查询索引
show index from table_name; -- 方式1
show indexes from table_name; -- 方式2
show keys from table_name; -- 方式3
desc table_name; -- 方式4,在表结构中看索引,但没有前三种索引信息详细
索引种类
# 查询表的索引
show indexes from table_name; -- 查询表 table_name 中所有索引
- 在表结构中 Non_unique:0 是唯一索引,1 是普通索引
主键索引
-
字段定义为主键后自动为主键索引(类型
primary key
)-
字段值唯一且不允许为空
-
id int primary key , -- 主键约束,自动为主键索引
-
唯一索引
-
字段定义为
unique
后自动为唯一索引-
字段值唯一,允许为 null
-
card_id int unique , -- 唯一约束,自动为唯一索引
-
联合索引
-
最左匹配原则
- 以最左边为起点任何连续的索引都能匹配上
- 如果第一个字段是范围查询需要单独建一个索引
- 创建联合索引时,根据业务需求
where
子句中使用最频繁的一列放在最左边- 扩展性比较好
- 例如:
name
经常作为查询条件,salary
不常使用name
放在联合索引的第一位置,即最左边alert table emp add index name_salary (name,salary);
- 通过索引查询时
select * from emp where name = '';
- 满足最左特性,只是部复合索引也生效
select * from where salary = xxx;
- 没有最左边的字段,不满足最左特性,索引失效
slect * from emp where name = '' and salary = xxx;
- 复合索引全使用,按顺序出现 name、salary,索引生效
selct * from emp where salary = xxx and name = '';
- 违背最左特性,但
MySQL
执行SQL时会进行优化,底层进行颠倒优化 - 索引生效
- 违背最左特性,但
- 以最左边为起点任何连续的索引都能匹配上
-
多字段联合添加索引
-
单列索引:节点中关键字[name] 及索引的关键字的值为那么对应的值,比如 张三。
-
联合索引:节点中关键字[name,phoneNum],比如张三,138888888。
-
联合索引列选择原则
- 经常用的列优先 【最左匹配原则】
- 选择性(离散度)高的列优先【离散度高原则】
- 宽度小的列优先【最少空间原则】
-
创建联合索引的时,如:(k1,k2,k3)
-
相当于创建(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。
-
联合索引不满足最左原则,索引一般会失效
-
-
-
索引下推:
select * from table where name = ? and age = ?
-
MySQL5.6 及以后的版本出现
-
原查询过程:先根据 name 在存储引擎中获取数据,后根据 age 在 server 层过滤
-
有索引下推后:根据 name、age 在存储引擎获取数据,返回对应的数据,不再到 server 层过滤
-
使用 Explain 分析SQL语句时出现
index condition pushdown
就是使用了索引下推- 索引下推在组合索引的情况出现几率最大
单列索引
-
单一字段添加索引
- 多个单列索引在多条件查询时优化器会优先选择最优索引策略
- 可能只用一个索引,也可能将多个索引全用上
- 但会创建多个B+索引树,占用空间,也会浪费一定搜索效率
- 如果只有多条件联合查询时建议使用联合索引
- 多个单列索引在多条件查询时优化器会优先选择最优索引策略
-
同时存在联合索引和单列索引(字段有重复)
-
涉及到 MySQL 本身的查询优化器策略
- 当一个表有多条索引可走时,
mysql
根据查询语句的成本来选择使用哪条索引
- 当一个表有多条索引可走时,
-
MySQL
执行优化器会对其进行优化- 当不考虑索引时,
where
条件顺序对效率没有影响,真正有影响的是是否用到了索引!
- 当不考虑索引时,
-
全文索引
- MyISAM 存储引擎支持全文索引
- 用于查找文本中的关键词,而不是直接比较是否相等
- 查找条件使用
MATCH AGAINST
,而不是普通的WHERE
- 全文索引一般使用倒排索引实现,记录着关键词到其所在文档的映射
- InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引
fulltext
- MySQL自带,并不好用
- 一般开发考虑使用全文搜索
Solr
和ElasticSearch
(ES)
聚簇索引
定义
- 聚簇索引、非聚簇索引也被称之为主索引、二级索引
- 数据跟索引存储在一个文件里,就是聚簇索引,否则就是非聚簇索引
- 聚簇索引是将数据跟索引结构放到一块,因此一个表仅有一个聚簇索引
- 聚簇索引默认是主键
- 若表中没有定义主键,
InnoDB
会选择一个唯一的非空索引代替- 如果没有
InnoDB
会隐式定义一个主键作为聚簇索引 InnoDB
只聚集在同一个页面中的记录,包含相邻键值的页面可能相距甚远
- 如果没有
- 如果已经设置了主键为聚簇索引,必须先删除主键,然后添加想要的聚簇索引,最后恢复设置主键即可
- 此时其他索引只能被定义为非聚簇索引
- 若表中没有定义主键,
场景
-
InnoDB
使用聚簇索引将主键组织到一棵B+树中,行数据就储存在叶子节点- 使用
where id = 14
条件查找主键,按照B+树的检索算法即可查找到对应的叶节点,获得行数据 - 对
Name
列进行条件搜索需要两个步骤- 在辅助索引B+树中检索 Name,到达其叶子节点获取对应的主键
- 使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据
- 通过其他键需要建立辅助索引
- 使用
-
MyISAM
使用非聚簇索引- 两棵B+树节点的结构完全一致,只是存储的内容不同
- 主键索引 B+ 树的节点存储主键
- 辅助键索引 B+ 树存储辅助键
- 表数据存储在独立的地方,两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据
- 对于表数据两个键没有任何差别
- 索引树是独立的,通过辅助键检索无需访问主键的索引树
- 两棵B+树节点的结构完全一致,只是存储的内容不同
-
辅助索引:
InnoDB
中在聚簇索引上创建的索引- 辅助索引访问数据总是需要二次查找
- 非聚簇索引都是辅助索引
- 复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不是行的物理位置,而是主键值
优劣
- 优势
- 加载同一页的行数据到 buffer 中,再次访问从内存中而不必进行磁盘 IO
- 主键和行数据一起加载,通过叶子节点就可以直接返回数据
- 按照主键Id组织会更快获得数据
- 辅助索引使用主键作为指针,减少出现行移动或者数据页分裂时辅助索引的维护工作
- 主键值作指针会占用更多的空间,但
InnoDB
移动行时无须更新辅助索引中的指针- 行的位置会随数据的修改而变化
- 前面的 B+ 树节点分裂以及 Page 的分裂
- 行位置在实现中通过 16K 的 Page 来定位
- 前面的 B+ 树节点分裂以及 Page 的分裂
- 聚簇索引保证辅助索引数不受主键B+树的节点变化影响
- 行的位置会随数据的修改而变化
- 主键值作指针会占用更多的空间,但
- 聚簇索引适合用在排序的场合,非聚簇索引不适合
- 取出一定范围数据的时使用聚簇索引
- 二级索引需要两次索引查找,而不是一次才能取到数据
- 存储引擎第一次需要通过二级索引找到索引的叶子节点,从而找到数据的主键
- 第二次在聚簇索引中用主键再次查找索引,再找到数据
- 把相关数据保存在一起
- 例如:实现电子邮箱可以根据用户 ID 来聚集数据
- 只需从磁盘读取少数的数据页就能获取某个用户的全部邮件
- 如果没有使用聚簇索引,则每封邮件都可能导致一次磁盘 I/O
- 例如:实现电子邮箱可以根据用户 ID 来聚集数据
- 加载同一页的行数据到 buffer 中,再次访问从内存中而不必进行磁盘 IO
- 缺点
- 维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(page split)时
- 建议在大量插入新行后,选在负载较低的时间段通过
OPTIMIZE TABLE
优化表- 因为必须被移动的行数据可能造成碎片,使用独享表空间可以弱化碎片
- 建议在大量插入新行后,选在负载较低的时间段通过
- 表因为使用 UUId(随机ID)作为主键,使数据存储稀疏
- 会出现聚簇索引有可能有比全表扫面更慢
- 建议使用 int 的 auto_increment 作为主键
- 维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(page split)时
非聚簇索引
-
将数据存储与索引分开结构
- 索引结构的叶子节点指向了数据的对应行
MyISAM
通过 key_buffer 把索引先缓存到内存中- 需要通过索引访问数据时在内存中直接搜索索引,通过索引找到磁盘相应数据
-
使用聚簇索引和非聚簇索引
-
回表:从非聚簇索引跳转到聚簇索引中查找数据
- 查询的字段为非聚簇索引,但非聚簇索引中没有将需要查询的字段全部包含就是回表
- 例如:id 为主键索引,name 为普通索引
- 查询语句:
select id, name, age from table where name = 'kaka';
- 在 name 的 B+Tree 中寻找到主键 id
- 根据主键 id 的索引获取到数据并返回
- 这个过程就被称为回表
- 非聚簇索引 name 的叶子节点只有 id 没有age,所以跳转到聚簇索引中,根据id在查询整条记录返回需要的字段数据
- 查询语句:
结构
- 索引是在存储引擎层实现的,而不是在服务器层实现的
- 所以不同存储引擎具有不同的索引类型和实现
MySQL
常用两种索引结构(算法)- BTree、Hash
- 两种算法检索方式不一样,对查询的作用也不一样
Hash
Hash
索引的底层由Hash
表实现的- 非常适合以 key-value 的形式查询,即:单个 key 查询,或等值查询
- 哈希索引能以 O(1) 时间进行查找,但失去了有序性
- 无法用于排序与分组
- 使用
BTree
比Hash
多:Hash
本身由于其特殊性,带来了很多限制和弊端Hash
索引仅能满足=
、IN
、<=>
查询,不能使用范围查询- 联合索引中,
Hash
索引不能利用部分索引键查询- 对于联合索引中的多个列,
Hash
要么全部使用,要么全部不使用,不支持 BTree 支持的联合索引的最优前缀
- 对于联合索引中的多个列,
Hash
索引无法避免数据的排序操作Hash
索引中存放的是经过Hash
计算后的值- 且值的大小关系并不一定和
Hash
运算前的键值完全一样- 数据库无法利用索引的数据来避免任何排序运算
Hash
索引任何时候都不能避免表扫描Hash
索引将索引键运算后,运算结果和所对应的行指针信息存放于Hash
表中- 不同索引键可能存在相同
Hash
值- 即使满足某个
Hash
键值的数据的记录条数,也无法从Hash
索引中直接完成查询 - 要通过访问表中的实际数据进行比较,并得到相应的结果
- 即使满足某个
- 遇到大量
Hash
值相等的情况后性能并不一定比BTree
高
InnoDB
存储引擎有特殊的功能:自适应哈希索引- 某个索引值被使用的非常频繁时,在 B+Tree 索引之上再创建一个哈希索引
- 让 B+Tree 索引具有哈希索引的一些优点
- 比如快速的哈希查找
BTree
分类
-
相对平衡二叉树:BTree
- 左右两个子树的高度差的绝对值不超过 1
- B 代表平衡:balance
- 缺点
- 太深:数据处的高/深度决定 IO 操作次数,IO 操作耗时大
- 太小:每一个磁盘块(节点/页)保存的数据量太小
-
多路平衡查找树:B-Tree
- B树 不同于二叉树,节点可以存储多个关键字和多个子树指针
m
阶的 B 树要求除根节点以外,所有的非叶子子节点必须要有[m/2,m]
个子树- 根节点必须只能有两个子树
- 允许根节点一个节点的情况
- 是一个查找二叉树
- 越靠前的子树越小,并且同一个节点内,关键字按照大小排序
- B树的一个节点要求子树的个数等于关键字的个数+1
-
加强版多路平衡查找树:B+Tree;B-Tree plus 版
- 支节点只保存索引列关键字,不保存数据,只有叶子节点才保存数据
- 可用于 =,>,>=,<,<= 和 between 这些比较操作符
- 还可用于 like 操作符,最左侧不能是 %
- 将所有的查找结果放在叶子节点中
- 查找 B+ 树必须到叶子节点才能返回结果
- B+ 树每一个节点的关键字个数和子树指针个数相同
- B+ 树的非叶子节点的每一个关键字对应一个指针
- 关键字则是子树的最大,或者最小值
B+tree
-
基于
BTree
和叶子节点顺序访问指针进行实现- 具有
BTree
的平衡性,且通过顺序访问指针来提高区间查询的性能
- 具有
-
用于查找,还可以用于排序和分组。
-
可指定多个列作为索引列,多个索引列共同组成键
-
适用于全键值、键值范围和键前缀查找
- 键前缀查找只适用于最左前缀查找
-
InnoDB 的 B+Tree 索引分为主索引和辅助索引
- 主索引的叶子节点 data 域记录完整的数据记录,被称为聚簇索引
- 无法把数据行存放在两个不同的地方,所以一个表只能有一个聚簇索引
- 辅助索引的叶子节点的 data 域记录主键的值
- 因此使用辅助索引进行查找时,需要先查找到主键值,再到主索引中进行查找
- 主索引的叶子节点 data 域记录完整的数据记录,被称为聚簇索引
-
-
B+Tree
中一个节点的 key 从左到右非递减排列-
某个指针的左右相邻
key
分别是keyi
和keyi+1
,且不为 null -
该指针指向节点的所有
key
>=keyi
且 <=keyi+1
-
-
查找
-
在根节点进行二分查找,找到一个 key 所在的指针
-
递归地在指针所指向的节点进行查找。直到查找到叶子节点
-
在叶子节点上进行二分查找,找出 key 所对应的 data
- 插入删除操作记录会破坏平衡树的平衡性
- 插入删除操作之后,需要对树进行分裂、合并、旋转等操作来维护平衡性
- 插入删除操作记录会破坏平衡树的平衡性
-
-
B+Tree
与B-Tree
的区别B+
节点关键字搜索采用闭合区间- MYSQL 推崇使用 ID 作为索引
- 由于 ID 是自增的数字类型,只会增大,所以采用向右拓展的方式
- MYSQL 推崇使用 ID 作为索引
B+
非叶节点不保存数据相关信息,只保存关键字和子节点的引用。B+
关键字对应的数据保存在叶子节点中B+
叶子节点是顺序排列的,且相邻节点具有顺序引用的关系
-
为什么选择
B+Tree
?- B+树 是 B-树 的变种,多路绝对平衡查找树,拥有B-树的优势
- B+树 扫库、表能力更强
- B+树 的磁盘读写能力更强
- B+树 的排序能力更强
- B+树 的查询效率更加稳定
机制
检索机制
- 索引相当于目录,通过目录定位查找数据
- 检索方式
- 全局查找
- 索引查找
- 未使用索引时,
select
查询默认全表扫描,找到所有符合条件的记录- 即使已经查找到符合条件的记录仍会继续扫描
- 数据库不知道是否还有其它符合条件的记录
- 即使已经查找到符合条件的记录仍会继续扫描
执行流程
-
数据结构:B + tree
- 索引会将该字段所有记录建立二叉树结构
- 将数据分区存放
- 一侧存放小于某数据的数据,另一侧存放大的
- 两侧继续按此规则建立二叉树,直到没有数据,形成总二叉树
- 索引会将该字段所有记录建立二叉树结构
-
执行流程
-
检索时首先查看条件字段是否有索引
-
有索引对象时通过索引检索
- 索引生成时已经排序
-
通过索引定位数据
- 得到数据的物理地址
-
原
select
语句转为select ... where 物理地址 = 检索到的物理地址
- 直接从硬盘中按物理地址查找数据,不再通过表
-
-
例如:
select ename from emp where ename = 'SMITH';
-
查看 ename 字段,发现该字段有索引
-
通过 ename 字段索引对象检索
-
索引已经对字段进行了排序
- 先检索 S 区,再 M 区 …
-
减少扫描次数,快速定位数据
-
-
查找到记录的物理地址 假如:0x00011
- 数据库中每条记录都有物理地址
- Oracle 中叫做 rowId
sql
语句变形为select ... where 物理地址 = 0x00011
- 此时检索数据不通过表
- 通过物理地址定位硬盘数据
-
-
优、劣
-
优点:优化查询速度
- 缩小了扫描范围,只扫描条件字段
- 且条件字段已经排序
-
缺点
- 索引本身也占用空间
- 在创建索引的表中
- 根据表的存储引擎不同在硬盘或内存中
- 创建索引只对创建索引的字段查询效率有优化
- 对 DML 语句会有影响
- 执行 DML 语句会对二叉树结构造成有影响,要对索引结构维护
- DML 语句执行速度会受影响
- 数据库90%以上操作是select,所以索引很有必要
- 执行 DML 语句会对二叉树结构造成有影响,要对索引结构维护
- 索引本身也占用空间
-
注意事项
使用场景
-
数据量庞大
-
较频繁作为查询条件的字段应创建索引
-
唯一性太差的字段不适合单独创建索引
- 例如:性别
-
DML 操作非常频繁的字段不适合创建索引
- 例如:时间戳、登录次数
-
不会出现在 where 字句的字段不应该创建索引
索引失效
-
like
子查询,%
放前面 -
非空判断
is not null
-
or
语句前后没有同时使用索引- 当 or 左右查询字段只有一个是索引,该索引失效
- 只有当 or 左右查询字段均为索引时,才会生效;
- SQL优化要避免写or语句
-
数据类型出现隐式转化
- 如
varchar
不加单引号的话可能会自动转换为int型,使索引无效,产生全表扫描
- 如
索引优化
- 查询时,索引字段不能是表达式的一部分,也不能是函数的参数,否则无法使用索引
- 多条件查询时,使用多列索引比使用多个单列索引性能更好
- 选择性最强的索引列放在前面
- 选择性: 不重复的索引值和记录总数的比值
- 最大值为 1,每个记录都有唯一的索引与其对应
- 选择性越高,查询效率也越高
- 离散性越高选择性就越好
- 比如:性别字段索引,男为1,女为0 生成索引树:
- 搜索女的数据,在根节点触发两条路可以走
- 从中间走下去可以选择的线路太多会导致搜索引擎懵逼
- 优化器对sql优化索引扫描不如全表扫描
- 导致离散型降低,不利于性能
- 优化器对sql优化索引扫描不如全表扫描
- 比如:性别字段索引,男为1,女为0 生成索引树:
- 选择性: 不重复的索引值和记录总数的比值
- 对于
BLOB
、TEXT
和VARCHAR
类型字段使用前缀索引,只索引开始的部分字符。- 前缀长度根据索引选择性来确定
- 覆盖索引:索引包含所有需要查询的字段的值
- 索引通常远小于数据行的大小,只读取索引能大大减少数据访问量
- 可减少数据库 IO,将随机 IO 变为顺序 IO,可提高查询性能
- 一些存储引擎(例如 MyISAM)在内存中只缓存索引,而数据依赖于操作系统来缓存
- 因此只访问索引可以不使用系统调用(通常比较费时)
- 对于
InnoDB
引擎,若辅助索引能够覆盖查询,则无需访问主索引- 例如:联合索引
create index name_phoneNum on users(name,phoneNum)
- sql:
select name, phoneNum from ... ;
使用覆盖索引
- sql:
- 例如:联合索引
- 索引通常远小于数据行的大小,只读取索引能大大减少数据访问量