索引
索引其实也是一种数据结构
索引是帮助MySQL搞笑获取数据的排好序的数据结构
索引数据结构
● 二叉树
● 红黑树
● Hash表
● B-Tree
B-Tree结构
● 叶节点具有相同的深度,叶节点的指针为空
● 所有索引元素不重复
● 节点中的数据索引从左到右递增排序
B+Tree结构
● 非叶子节点不存储data,只存储索引(冗余),可以放更多的索引
● 叶子节点包含所有索引字段
● 叶子节点用指针链接,提高区间访问性能
一个索引页MySQL推荐的大小是16kb,大于可以存储1170个数据 1170117016
Hash索引
特点
● 对索引的key进行一次hash计算就可以定位出数据存储的位置
● 很多时候Hash索引要比B+Tree数索引更搞笑
● 仅能瞒住“=”,“IN”,不支持范围查询
● hash冲突问题
MySQL选择B+Tree的原因
● 非叶子节点存储更多的元素,树的高度会越小,性能越高
索引存储位置
不修改的情况下,就是在data下面存储的
存储引擎
MyISAM
● 索引文件和数据文件是分离的(非聚集)
● 叶子节点只是存储了索引的位置,
存储结构
● frm 表结构信息
● MYD MY是MyISAM的缩写D是data,存储的数据
● MYI 存储的索引文件 Index
selcet * from user where user_id=123
// 其中user_id是索引 MYI文件中会按照B+Tree中存储在其中
索引中存放的是数据所在文件的磁盘地址,可以去MYD文件中去定位到所在的位置
InnoDB
特点
● 表数据文件本身就是按照B+Tree组织的一个索引结构文件
● 聚集索引-叶节点包含了完整的数据记录
● 主键索引就是一个聚集索引
● 整张表只有一个聚集索引,就是主键索引
存储结构
● frm表结构信息
● ibd 数据和索引
问题
● 为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?
○ ibd文件必须要用一个B+Tree来进行组织,如果有主键则使用主键。
○ 假如没有建立主键,则会选择一列数据全都不相等的来组织B+Tree,如果没有选到,MySQL会去建立一个隐藏列,来进行维护B+Tree
○ B+Tree是来进行比对大小,所以整型自带比较,
○ 占用的空间小,节约资源
○ 如果不是自增节点的时候,有的时候需要分裂和平衡
● 为什么非主键索引结构叶子节点存储的是主键值?(一致性和节省存储空间)
○ 叶子节点存放的就是聚集索引的索引值,存放的主键
● 聚集索引和非聚集索引那个查找速度快
○ 聚集索引比较快,不需要跨文件查找,找到叶子节点就能够拿到数据
联合索引
联合索引的存储结构是什么样子?
● 索引最左前缀原理
Explain
explain中的列
EXPLAIN SELECT * FROM des_design WHERE id=2
//其中id是主键
explain select(select 1 from actor where id = 1) from (select * from film where id = 1) der;
select_type列
- primary:复杂查询中的最外层的select
- subquery:包含在select中的子查询(不在from子句中)
- derived:包含在from子句中的子查询,MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
id列
id的值越大越后执行
type列
一种sql的关联类型或者访问类型,就是MySQL中如何查找表中的行,查找数据行记录的大概范围
优先级:
system>const>eq_ref>ref>range>index>ALL 效率逐层递减
一般来说,最好保证查询达到range级别,最好达到ref
const 有疑问?
查询的条件只有一条数据
EXPLAIN EXTENDED SELECT * FROM (SELECT * FROM material_fixed_part WHERE id=2) tmp;
eq_ref
EXPLAIN SELECT * FROM des_design LEFT JOIN des_design_folder ON des_design.folder_id =des_design_folder.folder_id
primary key或者unique key索引的所有部分被连接使用,最多只会返回一条符合条件的记录。这可能是在const之外最好的类型了
ref类型
可能会有多条结果信息,不是使用了唯一索引,而是使用了普通的索引,或者唯一性索引的部分前缀
EXPLAIN SELECT * FROM des_design WHERE folder_id=123
range类型 范围查找
还是走了索引但是是范围的查找,范围扫描,通常出现在in(),between,><=等操作中,使用一个索引来检索给定的范围的行
EXPLAIN SELECT * FROM des_design WHERE id>45
index类型
扫描全索引可以拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接对二级索引的叶子节点遍历和扫描,速度比较快。这种查询一般为使用覆盖索引,二级索引一般比较小(没有全表信息),所以一般比all快一点。
这种情况虽然走了索引但是效率并不高,他其实是全部遍历了二级索引的叶子节点,全部取出。
All
全表扫描,扫描聚簇索引,扫描所有的叶子结点。读取的磁盘IO会比较大
EXPLAIN SELECT * FROM test
key_len
代表使用到的联合索引里面的那些字段
key_len的计算规则
● 字符串
○ char(n):n字节长度
○ varchar(n):如果是utf-8,则长度3N+2,加的2用来存储字符串的长度
● 数值类型
○ tinyint:1字节
○ smallint:2字节
○ int:4字节
○ bigint:8字节
● 时间类型
○ date:3字节
○ timestanp:4字节
○ datetime:8字节
● 如果字段允许为NULL,需要1字节来记录是否为NULL
索引最大长度是768字节,当字符串过长时,MySQL会做一个类似最左前缀索引的处理,将前半部分的字符提取出来做索引
ref列
索引关联的条件是什么
这一列显示的是key列记录的索引中,表查找值所用到的列或者常量
常见的有:const(常量),字段名()
rows列
mysql要读取并检测的行数预估值,不是结果集中的行数
extra列
using index:使用覆盖索引
只需要使用索引就可以查看到结果了,不需要再回表查询
EXPLAIN SELECT folder_id FROM des_design WHERE folder_id=123
using where
使用where语句来处理结果,并且查询的列未被索引覆盖
using index condition
查询的列不完全被索引覆盖,where条件中是一个前导列的范围
using temporary
MySQL使用到了临时表
尽量使用索引进行操作,
索引优化实践
● 全值匹配
● 最左前缀
● 不要在索引列上面做任何的操作(计算,函数,自动or手动类型转换),会导致索引失效全表扫描
○ 索引树中没有存储这些元素
○ 在索引树中没有办法定位到
● 尽量使用覆盖索引