Mysql索引
什么是索引
举例:想象一下我想要在学校的图书馆中找“人间失格”这本书,正常情况下:馆内的书本都分门别类放置的,我先知道“人间失格”这本书的类别属于文学类,那这时我只需要看文学类所在的几个书架,然后在这几个书架里寻找即可。另类情况:每一本书在入馆时登记好它的唯一ID 就像你的个人身份证一样,那么当我要找这本书的时候就能根据ID去直接定位到这本书在哪个书架第几排的多少号。索引其实就是这样的一个原理。
所以 索引的本质其实是一种数据结构,它会在数据库中占用一定的空间的同时提高我们接下来的查询效率,专业的说法就是以空间换时间。索引它是用于提高数据库查询性能的一种手段,加快数据查询和排序。又是面试官可能会问“既然索引可以提高查询性能,那能不能给所有字段都加上索引?” 根据上面说的我们就知道肯定是不行的,因为它会占用数据库的空间,而且创建索引的过程其实也是需要时间的,总不可能无损。
索引的优缺点
优点:
- 可以大大加快数据的检索速度(减少检索的数据量),降低数据库的IO成本。
- 加快分组和排序的速度。
- 保证行的唯一性,增强数据完整性(唯一索引)。 缺点:
- 创建和维护索引需要耗费一定时间,对数据进行插入、删除、修改时增加了时间开销,降低SQL执行效率。-时间成本
- 索引需要使用物理空间存储即需要占用一定的磁盘空间。-空间成本
举例说明
当username没有索引会进行全表扫描
select * from user where username = "admin"
全表扫描为什么慢?
数据库的数据是持久化在硬盘里的,保证了数据安全的同时也降低了效率,因为在过程中,需要不断在内存和硬盘之间进行数据的传输,所以效率低下。相比cpu和内存,硬盘的处理速度要慢很多的,这是硬件层面的差距无法弥补。
磁盘访问慢?
操作系统:内存和磁盘之间的处理效率不在一个量级,为了缩小差距 操作系统会在计算机访问一个地址的时候除了访问到当前地址的数据还能访问到其相邻的数据,这一次访问被称为一页 一般是 4K或8K
当username有索引 访问快?
通过降低IO访问,减少检索的数据量,
索引的分类
可从不同维度对索引进行分类
- 按 数据结构 分类可分为:B+树索引、Hash索引、全文索引等
- 按 物理存储 分类可分为:聚簇索引(主键索引)、非聚簇索引(二级索引)等
- 按 逻辑特性 分类可分为:主键索引、普通索引、唯一索引、前缀索引、联合索引(多个字段)等
索引的数据结构
选择高效的数据结构:B+树
了解B+树之前先得知道B树的特点:
树的每个节点都存储数据 叶子节点无指针连接
而B+树相对B树的特点:
数据只在叶子节点 所有叶子节点增加了指针
B+树中的叶子节点之间通过指针连接,非常适合进行范围查找,非叶子节点不存数据,在同样范围大小可以存储更多的key,减少了树的高度。
聚集索引和非聚集索引
按照存储方式进行区分,表中的数据存储到磁盘那么其物理位置就固定下来了,而聚集索引中的key值顺序与数据的物理顺序是一致的。因此也就能知道为什么聚集索引的查找效率会更高。在innoDB引擎中默认其实就是主键索引
基于聚集索引的特点,在项目中到底使用自增id还是UUID也就能够确定了,自增能够让key连续,而其对应的数据物理地址也就是连续的,写入性能很好,所以如果自增id已经能满足业务需求,没必要使用uuid。
执行计划
直接上来就一个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 仅查询索引树就能得到所需的数据行
索引的失效场景
索引失效大多是因为我们编写的sql导致的。
条件:条件中使用 or like