索引总结

最新推荐文章于 2023-03-17 21:12:53 发布

一拳超人

最新推荐文章于 2023-03-17 21:12:53 发布

阅读量231

点赞数

分类专栏： mysql专栏

本文链接：https://blog.csdn.net/weixin_44731123/article/details/108326223

版权

mysql专栏专栏收录该内容

1 篇文章 0 订阅

订阅专栏

摘要：

索引是什么？加快数据查询的一种数据结构
为什么要使用索引？提高查询数据的效率

一、常见的索引模型:
1.哈希表:
使用场景: 等值匹配、适用于NoSQL引擎
弊端: 范围查询很慢
2.数组:
使用场景: 等值查询、范围查询、一般用在静态存储引擎中（数据一般不会变更）
弊端: 插入数据较慢
3.搜索树:
查询和搜索的速度比较快、适应磁盘的访问模式、（减少单次磁盘访问IO 的次数）

二、InnoDB引擎
1.InnoDB 引擎特点?:

表根据主键顺序以索引的形式存放（索引组织表）、每一个索引在InnoDB中对应一棵树。
2.普通索引和主键索引的区别?：
查询的时候普通索引会回表（普通索引的叶子节点存放的是主键值、通过主键值要去重新扫描主键索引数查询数据【回表】）
3.为什么MySQL建议使用自增主键？:
避免发生频繁的页分裂。
- 递增插入，每次插入数据都是追加操作，不涉及挪动其他记录、从而避免页分裂代来的性能损失
- 页分裂：如果遇到不规则的插入数据、则会造成频繁的页分裂。不规则插入每次插入都会改变数之前的数据状态、从而导致页分裂。
- 页分裂弊端：大量移动数据，会严重影响插入数据的效率；页分裂会降低空间利用率。
使用业务信息作为主键，往往会占用更多的空间（普通索引的叶子节点也会存储主键信息）
4.什么情况下适合用业务字段作为索引：
只有一个索引
该索引必须是唯一索引
5.索引的重建：
重建普通索引
- alter table T drop index k；
- alter table T add index k；
重建主键索引
- alter table T drop primary key；
- alter table T add primary key(id)；（这句指令会使得上句指令直接无效。）
为什么要重建索引
- 索引可能因为删除和页分裂等原因，导致数据页的空洞，重建索引会使得数据按序插入、页面利用率更高、更省空间。
- 重建普通索引是推荐的。可以节省空间
- 无论是删除主键还是、添加主键都会重构整个表（alter table T engine=InnoDB代替）
  6.覆盖索引：
当前需要查询的信息、直接可以通过当前索引树定位到完整数据、不需要回表。
直接减少了搜索树的次数，提示数据查询的性能。
7.最左前缀原则：
最左前缀可以是联合索引（多个索引组合在一起）的最左N个字段，也可以是字符串索引的最左M个字符。【查询条件字段的匹配】
联合索引的建立原则：
- 如果调整顺序，可以少维护一个索引，那么这个顺序就要被优先考虑。
- 空间原则、name age 上建立索引、推荐（name,age）+ age 而不是（name + age） + name【name空间远大于age】
  8.索引下推：
MySQL5.6 引入
索引遍历过程中、对索引中包含的字段先做判断、直接过滤掉不满足的条件，减少回表次数。

三、普通索引和唯一索引的选择
1.查询过程：

模拟查询语句：select id from T where k=5；（k有索引）通过B+树的树根、逐层搜索到叶子节点，（数据页内部可以认为通过二分法来定位记录）。
InnoDB读取数据是以页为单位。当需要读取一条数据，会将整页数据加载到内存中（默认每页16kb）
唯一索引：查到第一个满足条件的记录、则直接返回。
普通索引：查到第一个满足的记录后，需要查找下一个记录，直到碰到一个不匹配当前索引值的数据才返回。
两种索引查询性能的差不微乎其微（查找的数据基本都会在当前页中、整形字段一页可以放近千个key）

2.更新过程：

当前更新数据目标页已经在内存中，则都是直接更新，普通索引和唯一索引基本没有区别
当需要更新一个数据时候，如果数据页在内存中则直接更新；如果不在内存中，则会被缓存到change buffer中，这样就不需要从磁盘中读取数据来直接更新了，下次访问这个数据页的时候，将数据页读入内存，然后执行change buffer中这个页相关的操作。保证数据逻辑正确性。
当前更新数据目标页不在内存中：
- 普通索引，记录到change buffer中，语句执行结束。（记录redo log【包含更新语句、change buffer数据】）
- 唯一索引，将相关数据页读入内存（涉及随机IO访问 - 成本很高），判断是否违背唯一性约束，执行并结束。

3.change buffer

change buffer在内存中有拷贝、数据可以持久化
change bufer中的操作应用到原数据页，得到最新的结果的过程称为merge。
merge的时机：
- 访问这个数据页
- 后台线程定期
- 数据库正常关闭（shutdown）
机制：将更新操作缓存在change buffer中，减少磁盘随机访问IO，且数据入内存需要占用buffer pool，这样还可以减少内存占用，提高内存利用率。

4.change buffer 使用场景：

只限于普通索引
merge之前，change buffer中数据越多（减少随机访问的IO次数越多）收益就越大
适合业务模型：账单类、日志类的系统
不适合在更新之后里面就要访问的数据（会提取触发merge操作）、这种IO次数不仅没减少，还增加了维护change buffer的代价
适用写多读少的环境。

5.索引的选择和实践：

普通索引、唯一索引在查询上来看基本没有区别
主要考虑对更新性能的影响，建议尽量选择普通索引。
如果更新后伴随着对这个记录的查询，那么应该关闭change buffer。其他情况下cahnge buffer都能提升性能。

6.change buffer 和 redo log
redo log 减少了随机写IO的次数
change buffer 减少了随机读IO的次数

四、索引优化器
1.SQL语句执行的快慢

扫描行数越少、访问磁盘次数越少、消耗CPU资源越少
是否使用临时表
是否排序等

2.MySQL如何统计扫描行数

是不准确的（都是估算的）

3.解决MySQL优化器索引选择异常

force index 强制走我们所期望的索引
考虑修改SQL语句，引导MySQL使用我们期望的索引
某些场景，可以新建一个更合适的索引，供优化器做选择，或删除误用的索引。

五、如何给字符串添加索引
1.前缀索引

定义字符串的一部分作为索引，不指定长度默认包含整个字符串
可能会导致查询语句读数据的次数变多（索引key会出现重复）
建立索引使用的数据，区分度越高越好（重复的键值越少越好）
- select count （distinct left（email，4））as L4，
- select count （distinct left （email，5））as L5 from SUser；这里以email索引为例。查看区分度是否达到我们的预期。
使用前缀索引，定义好长度，既可以节省空间，又不用增加太多的查询成本。
但是前缀索引导致不能使用覆盖索引。

2.倒序存储
处理身份证号码：
直接使用前缀索引就不太合适，可能需要长度12以上的前缀索引，才能满足区分度上的要求（占用了太大的空间、效率会严重降低）