数据库索引
什么是索引
举例:想象一下要在一个图书馆中找一本书,一种情况下:馆内的书本都分门别类放置的,我们根据书本属于哪一类的那我们定位到那一类书本的大致方位,我们大学图书馆就是这种。另一种情况:每一本书在入馆时登记好它的ID,当要找这本书的时候你就能根据ID快速直接的定位到这本书,这个录入一定就得用科技手段,比如二维码扫描。索引它就是这样的一个记录,记录这本书id是多少 位置在哪这样的信息。当然这里只是举了一个生活中的例子,实际上的索引比这是更为复杂的,因为不同的索引他们的结构不一样,作用也不一样。
索引的特性:
- 索引的本质是一种数据结构,在数据库中会占用一定的空间。
- 索引是用于提高数据库查询性能的一种手段,加快数据查询和排序。
- 索引降低了插入,删除,修改等操作的速度。
- 索引是创建在具体的表上的,而不能在某个视图上。
- 索引可以直接创建也可以间接。
语法
- 创建普通索引
CREATE INDEX mycolumn_index ON mytable (myclumn)
- 创建唯一索引:保证在索引列中的全部数据是唯一的,对聚簇索引和非聚簇索引都可以使用
CREATE UNIQUE COUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
- 创建组合索引:包含多个字段名,最多16个字段
CREATE INDEX name_index ON username(firstname,lastname)
- 创建聚簇索引:数据和索引在一块,比如我在创建表的时候指定ID为主键,那么Mysql会自动创建一个聚簇索引
CREATE CLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn) WITH ALLOW_DUP_ROW(允许有重复记录的聚簇索引)
- 创建非聚簇索引:索引和数据分离,Mysql中索引是B+树,就是一种非聚簇索引
CREATE UNCLUSTERED INDEX mycolumn_cindex ON mytable(mycolumn)
索引的数据结构
二叉树 ~ 平衡二叉树 ~ B-树 ~ B+树
数据库的数据是持久化在硬盘的,查询过程中,需要不断在内存和硬盘之间进行数据的传输,而相比cpu和内存,硬盘的处理速度要慢很多的。那么为什么索引它就可以提高查询效率呢,它的底层原理其实是减少了IO操作,查询到想要的数据可以执行更少的IO操作次数。至于它之所以能降低IO还得归功于索引的数据结构 B+树!
操作系统的知识点:因为内存和磁盘之间的处理效率不在一个量级,所以操作系统会在计算机访问一个地址的时候除了访问到当前地址的数据还能访问到其相邻的数据,也就是说一次IO能读取到不止一个地址的数据,被称为一页 一般是 4K或8K
- Mysql的存储引擎InnoDB,它的索引使用的就是B+树这种数据结构。从平衡二叉树上进行演变得来的B+树为什么可以减少磁盘IO的读写动作呢?
每次查找我需要从磁盘去获取一个新的节点数据,而二叉树中一个节点就一个键值,当数据很多自然也就导致IO增加,,所以B-树让每个节点放置了更多的键值,而且可以拥有不止两个节点。而B+树则在此基础上进一步增加了一个规则 就是非叶子节点不存储数据,只有键值,仅在叶子节点存放数据,这样每一页数据可以放置更多的键值 等同进一步减少了IO操作。
执行计划
直接上来就一个sql:查询用户以及对应的角色名
explain select su.*,sr.role_name from sys_user su left join sys_user_role sur on su.user_id = sur.user_id left join sys_role sr on sur.role_id = sr.role_id
id:加载顺序,当id相同则是从上往下加载 所以表加载顺序就是 su→sur→sr , id不同的话则从大到小依次加载(通常子查询里面会出现id不同的情况)
实际执行发现子查询不一定会出现id不同的情况,后续再研究,其实执行顺序的话根据sql语句执行顺序自己也能看出来。
select_type:查询类型,SIMPLE 简单查询
table:查询的表
partitions:查询的分区,没有则为null
type:连接 ALL(全表扫描) ref(对于前一个查询的每一行在当前表的索引中能找到多行) eq_ref(对于前一个查询得到的每一行在当前表的索引中只能找到一行) range(范围,)
possible_keys 和 key 比较好理解,一个是可能用到的索引,一个是实际用到的索引,key_len 标识用到的索引长度比如sur中用到了user_id字段的索引大小为8字节(user_id数据类型为bigint所以为8字节) ref: 因为是连接查询 这里展示的是关联驱动的字段,如果是常值等值判断 这里会显示 const,如果是函数或表达式可能就展示 func
rows: 扫描行数 ,不是很精确,但能大致看出sql的效率
filtered:经过server层后过滤的满足查询的比例
Extra:Using index 仅查询索引树就能得到所需的数据行
索引的失效场景
首先要明白一点索引的创建一定要合理,不是越多越好。
- 最左原则
这里主要指的是组合索引,也就是多个字段 假设有一个组合索引 有A,B,C三个字段,而SQL的where条件只有 A,C字段,跳过了中间的B,此时C就会失效,因为最左原则要求我们不能跳过,必须依个满足,一旦出现跳过的情况,则后面字段就会索引失效。
- 使用了函数或计算
不是直接使用字段原始比较方式,而是通过如 len(column1) 或者 是 column+1 类似操作之后的结果去进行比较进而筛选数据都会导致索引失效
- 类型转换
本来是字符串类型,但你传入的参数确实一个整型,这时也会导致索引失效
- 范围查询
当有一个组合索引 其中包括A B C三个字段,如果你使用的B字段是一个范围筛选,则C的索引会失效
- 不等于
!= 或者 <> 会导致索引失效
- 不为空
is not null 会导致索引失效
- like
like筛选如果%在前面会导致索引失效
- or
在使用or的时候 它前后两个条件都必须要有索引,如果只有其中一个有索引都不会生效