什么是索引?
索引相当于书本的目录
按照一定规律存放在一个数据结构, 可以快速定位数据
索引的数据结构包括:
- b+tree
- hash tree
- 全文索引
索引的好处和坏处
好处:
加快mysql数据页的查找
坏处:
索引可能让数据库的增删改变的非常的慢, 在多写少读的情况下效率比较慢
B+Tree
是什么?
是一个平衡查找树, 为磁盘或其他直接存取辅助设备设计的数据结构
所有记录节点都是按照键值大小的顺序存放在同一层的叶子节点上, 由各个叶子节点进行连接
而叶子节点使用的是双向循环链表连接的
说白了, 就是为了查找使用, b+tree的特点是树的分支多, 层数低, 叶子节点才存放数据, 非叶子节点存放索引
一般三到四层就够了
面试题: b+tree三层可以存放多少数据?
按照前面章节我们了解到 数据页的大小是 16kb
Q: 为什么要从
16kb开始算呢?A: 因为
b+tree第一层肯定只有一个页, 而一个页默认只有16kb
非叶子节点存放的是主键值与指针, 主键类型是bigint, 也就是 8b大小, 而一个指针的大小在mysql中为 6b, 那么我们可以知道根节点(页)可以存放 16kb / (8 + 6)b = 1170
第一层
也就是说第一层非叶子节点就有 1170 个记录
Q: 前面不是说了还有
infimum和supermum虚拟记录么?A: 那是数据页才有的, 非叶子节点并没有这个
第二层
1170 * 1170 = 1,368,900个叶子节点
第三层
第三层比较特殊, 是数据层, 但是我知道前面两层计算出来的是数据页的数量, 而一个数据页是16kb大小
所以可以计算出
1170 * 1170 * 16 = 21,902,400kb
我们计算出来的是三层b+tree所占用的磁盘空间
现在我们需要假设, 假设一个row, 大致是 1kb 大小
那么21,902,400kb / 1kb = 21,902,400个
可以有21,902,400个row
如果一个
row大小是2kb的话, 那就是21,902,400kb / 2kb = 10,951,200个
核心要点: 记住数据页是
16kb, 非叶子节点需要主键+指针,mysql指针大小是6byte, 需要假设row占用内存大小是多少
聚集索引
是一个b+tree结构, 叶子节点是数据页, 存放了表的数据
而且是按照主键的顺序进行排序的
这种索引的好处在于可以直接获得数据
又因为是有序的, 就可以查找范围
这里对标
hash tree, 它速度很快是4~5倍, 但是不能进行范围查找
书中原文: 许多数据库的文档会这样告诉读者:聚集索引按照顺序物理地存储数据。如果看图5-14，可能也会有这样的感觉。但是试想一下，如果聚集索引必须按照特定顺序存放物理记录，则维护成本显得非常之高。所以，聚集索引的存储并不是物理上连续的，而是逻辑上连续的。这其中有两点:一是前面说过的页通过双向链表链接，页按照主键的顺序排序﹔另一点是每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。
Q: "每个页中的记录也是通过双向链表进行维护的，物理存储上可以同样不按照主键存储。"上面这句话的意思是数据页可以不按照主键顺序?
A: 聚集索引并不是按照索引的顺序把数据行物理地存储在一起，而是通过一种逻辑上的顺序把数据行连接起来。这样做的好处是，当插入或删除数据时，不需要移动大量的数据行，只需要修改一些指针就可以了
具体来说，聚集索引的每个页都有一个页号，这个页号就是页在磁盘上的物理位置。每个页中的数据行也有一个行号，这个行号就是行在页中的物理位置。但是，聚集索引并不是按照页号和行号来组织数据的，而是按照逻辑页号和逻辑行号来组织数据的。逻辑页号和逻辑行号就是按照索引的顺序给每个页和每个行分配的编号。这样，聚集索引就可以通过一个双向链表把所有的页和行按照逻辑顺序连接起来
Q: 那逻辑行号是什么? 是主键么?
A: 逻辑行号不一定是主键，但是一般情况下，MySQL会把主键作为聚集索引的键值，这样可以提高数据的检索效率。如果没有定义主键，MySQL会选择一个唯一非空索引作为聚集索引的键值，如果没有这样的索引，MySQL会自动生成一个隐藏的行标识符作为聚集索引的键值。
逻辑行号的意思，是指按照聚集索引的键值的顺序来排序数据行，而不是按照主键的顺序。如果主键就是聚集索引的键值，那么逻辑行号就是主键的顺序。如果主键不是聚集索引的键值，那么逻辑行号就不是主键的顺序
辅助索引
叶子节点并不包含数据部分, 只有一个书签(一般是主键id), 这样辅助索引匹配上后, 可以拿着主键ID回表去查询聚集索引的叶子节点中的数据
而且每张聚集索引表可以存在多个辅助索引
如果聚集索引和辅助索引的高度都是3, 那么根据辅助索引查找的数据, 将经历6次磁盘IO
CardDinality值
对什么样的列添加索引?
列的取值范围广, 几乎没有重复值的情况添加索引, 比如如果列是性别, 只有男女, 那么不推荐添加索引, 因为索引只能过滤掉一半, 比如账户就很合适添加索引, 因为账户基本不重复, 但是账户一般都是唯一索引, 不需要添加
那么有其他评判指标么?
可以通过 show index指令, 查看上面的 cardinality指标
cardinality指标: 索引不重复记录数的预估值, 但是他是预估的, 不能说他是准确的,
正常情况下这个值 / row数量 应该接近 1 , 那么这个索引加的很好
它只要越小, 说明你这个索引加的并不好
如图所示. 这张对 idx_name 添加的索引比对 idx_age 添加索引效果要好
由于建表生成数据的时候,
age忘记添加年龄上限为150, 导致现在是1000岁, 无语了
Q: 为什么
cardinality不准确?A:
cardinality是通过采样完成的, 如果每次insert和update都统计cardinality那么效率将会很慢
索引的运用
联合索引
对表中多个列添加辅助索引
我们对表添加了 name,age 联合索引
在旧版本的mysql中(5.x), 联合索引需要关注到索引的先后顺序, 否则可能导致索引失效
select * from users where age = 13 在旧版本中该索引可能失效
但是在 mysql 8.x 中, 该索引生效
索引的效果不是那么好就是了
EXPLAIN SELECT SQL_NO_CACHE * from users where name = 'Ng Tsz Ching' 索引将是
不知道是什么原因, 也懒得去看了
联合索引的另一个好处是对第二个列进行了排序, 比如你想对同一个名字的用户按照age进行排序, 那么将减少一次回表查询的情况
mysql explain怎么看?
这里面我先先看 type , 看看走的那种类型的索引, 比如 system const eq_ref fulltext ref range index ALL 等, 从最好的最差情况
再去看 rows, 行数越少越好
最后推荐一款工具: soar: github.com/XiaoMi/soar , 借助它可以辅助你优化sql, 当然还可以使用 chatgpt 帮你, 最好还是 chatgpt, 但是你需要给全你的信息
覆盖索引
直接从辅助索引就能查询到记录, 就是索引覆盖
换句话说它不需要回表到聚集索引中再次查询
在前面的例子中如果辅助索引需要查询3次, 聚集索引需要3次, 那么在覆盖索引技术下, 只需要查询辅助索引就行了
要怎么使用上这门技术?
比如上面我们对 name,age 加了联合索引
select name, age from users where name='zhazha' and age = 13
这样就用上了索引覆盖技术
但是如果是
select * from users where name='zhazha' and age = 13
将会回表查询聚集索引, 把所有数据查全了
索引提示
你可以提示优化器, 使用哪个索引
select * from users index(idx_name) where name='zhazha';
select * from users force index(idx_name) where name='zhazha';
什么情况下使用索引提示?
- 索引使用错误
- 索引太多, 优化器需要"考虑"很久, 索引主动添加索引提示
MRR优化(mutil-range read)
将随机的IO磁盘访问, 变为较为顺序的IO访问, 极大提高速度
只能使用在 range eq_ref ref 类型辅助索引的查询
ICP优化(index condition pushdown)
在查询辅助索引前, 判断下是否已经可以使用where条件
全文检索
索引只能支持 关键字+%的查询, 不能支持%+关键字+%的模糊查询
所以使用倒排索引
倒排索引
如果全文是"hello world. hello shanghai"
经过倒排索引处理之后将出现新的索引:
| Number | Text | Documents |
|---|---|---|
| 1 | hello | 0, 13 |
| 2 | world | 6 |
| 3 | shanghai | 19 |
步骤就很简单: 分词, 统计
在使用的时候, 做下检索就行了
需要使用分词器 ngram 否则不支持中文
然后
SELECT * FROM 表名 WHERE Match(列名1,列名2) Against (检索内容1 检索内容2);
还是用
elasticsearch吧
