玩转索引

最新推荐文章于 2024-01-13 11:48:06 发布

励志重写JDK

最新推荐文章于 2024-01-13 11:48:06 发布

阅读量149

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/ajax_yan/article/details/105038930

版权

数据库专栏收录该内容

30 篇文章 1 订阅

订阅专栏

1、了解IO和磁盘交互机制
2、关闭AHI的优缺点
3、数据库每页查询大小设置
4、联合索引为什么要用？既然都是去叶子节点找主键回表查询
5、全表字段联合索引可以吗？

mysql
索引是帮助MYSQL高效获取数据的数据结构，在关系数据库中，索引是存储在硬盘中
索引方法：hash btree，奇怪的是选不了hash,说明mysql 支持hash,mysql底层
innodb引擎支持hash,但是是自动优化，手工无法干预
关键字 EXPLAIN：在查询语句前面加关键字 EXPLAIN 通过type可以看到查询的类型

范围查询
AHI（自适应哈希索引)）有一个致命缺点，会造成误判，如果你的数据库是一个统计库，语句中LIKE 、join如果占查询的 80%，可能会给系统造成负担，建议手动关闭它 set global innodb adaptive_hash_index=ON,OFF

比如自增Id 1,2,3 如果转换hash可能是100000,223232,2333030，这样无法完成查询，所以hash被淘汰了

二叉树：一个根生成2个节点，小的放左边，大的放右边,可能会形成一个单边（都在右边，或左边）
平衡二叉树 AVL，面对百万数据，数据层次太高，导致IO次数太多，决定了查询速度会太慢
所以，以上两种树无法达到性能巅峰

MySQL读取磁盘数据的时候，是以page(页)为基本单位，比如获取一条数据，它并不是精确命中这条数据，而是获取一个磁盘块（IO和磁盘的交互机制）。每次获取的磁盘块大小默认16K，假设我们要的数据只有20byte,那么就会：
造成了比较大的浪费
命中的有效数据太少

B-tree(多路搜索树、多叉平衡查找树)
多路、多叉，命中的有效数据大大提高

建立表字段够用就行
每次加载默认16K，假设字段10占400 20字段占800
那么每次加载 16/400,16/800=加载的行数，是不一样的。

show variables like 'innodb_page_size';获取数据库查询每页大小，默认是16K，可以修改

不能再常变得字段建立索引，从Btree得裂变来看，插入一个索引，BTREE会发生很多的节点变化，如果索引字段经常变化，那么用来维护索引的消耗可能会大于索引带来的优化时间

B+tree

排序能力更强
基于索引的扫库、扫表的能力强
btree IO次数不定，B+tree 都要IO三次（返回数据时间稳定），用户体验性会好点（稳定）

B+tree特点
1、非叶子节点不保存数据库相关信息，只保存关键字和子节点的引用
2、所有数据都保存在叶子节点中
3、左闭合的数据区间
4、叶子节点的数据天然有序，并且和相邻节点有顺序引用关系

这是为什么MySQL会用B+tree作为数据库底层结构的原因

show variables like 'datadir';查看数据文件目录

myisam引擎里面的索引没有主次之分的
id name都是索引
select * from user where id=? and name=?
用两个索引吗？
只用一个索引，除了or，一个sql语句只会用一个索引（mysql会判断哪一个索引更好用,MySQL引擎会进行底层优化，也就说，假如name,id条件顺序调换，也会只用一个id）

Innodb引擎
一个表中只能有一个聚集索引，因为表的物理顺序只能有一种情况（行中的数据物理顺序和索引的顺序相同的叫聚集索引，id和id代表的一条信息顺序是相同的）
聚集索引有着更快的检索速度
在innodb中，只有主键是聚集索引，其他的索引都是非聚集索引
什么意思呢？就是在innodb中，除了主键的叶子节点存放的是数据（name,age,sex..）
其他的索引的叶子节点存放的都是主键的值，name作为索引，查询的过程是先去叶子节点获取主键的值，再根据主键去获取内容，返回（回表查询操作）

如果你的表没有自己建立索引，mysql的innodb引擎会自己为你加上主键。再mysql力有一个rowid ,6位int类型

myisam对事物是不安全的，如果只是select一类的语句，可以选择
Innodb对事物的支持比较好，所以用的广泛

假如有name age 区号几个字段
那么选择哪一个作为索引比较好
可以用公式 count(distinct col（字段）):count(col) 这一列不重复的值：这一列总共的值
select count(distinct col（字段）) from table :select count(col) from table
比值越大越好

b+tree使用工具、https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

什么是好的索引？离散型越高越好，重复性越少越好
重复性太多，甚至相当于全表扫描了，那这样的就是无效索引

select *from user where name like ='%petter'

如果name 分别是 jim petter json lilin ，肯定是走索引的
如果name分别是 petter1 petter2 petter3....那还走索引吗？答案是不走
所以这条sql走不走索引，要看情况可能走，也可能不走

单列索引一个关键字建立的索引（特殊的联合索引）
create index idx_name(name)
联合索引一个以上关键字
create index idx_name_ph(name,phoneNum,age)
问题
select * from user where name="Perter" and phoneNum>"13333333" and age=18
查询的时候用到了哪些索引？

索引特点：范围之后全失效
phoneNum>"13333333" 拿到的是一个范围数据

这里用到了2个索引 name age

以下2条sql如何选择索引
select * from user where name=
select *from user where name=? and phoneNum=?

create index idx_name(name)

create index idx_name_ph(name,phoneNum)

选择第二个联合索引

覆盖索引
通过索引项的信息可以直接返回所查询的列

表user innodb引擎

索引PK id

联合索引
idx name,phoneNum,
unique唯一索引 userNum

下面哪些sql用到了覆盖索引？
select userNum from user where userNum=?
这里通过userNum查询返回userNum,是不需要通过非聚集索引先去叶子节点获取主键的值，再去主键的叶子节点获取查询信息
而是通过索引直接返回查询信息，所以是覆盖索引

select *from user where name=?
这里查询的是*，也就是所有字段，通过联合索引name去查询所有字段，肯定要借住id主键去获取，也就说name索引找到叶子节点里的id值，然后通过id值回表操作，再去主键叶子节点获取其他所有字段信息返回。所以没有用到覆盖索引