MySQL - 实战 (4) - 索引(下)

MySQL - 实战 (4) - 索引(下)

1 覆盖索引

1.1 回表问题

分析select * from T where k between 3 and 5执行几次树操作，扫描多少行

k为普通索引

在 k 索引树上找到 k=3 的记录，取得 ID
再到 ID 索引树查到该ID 对应的整条数据
在 k 索引树取下一个值 k=5，取得 ID
再回到 ID 索引树查到该ID 对应的整条数据
在 k 索引树取下一个值 k=6，不满足条件结束

查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5）

回表了两次（步骤 2 和 4）。

1.2 覆盖索引优化

1.2.1 查询语句

如果是select ID from T where k between 3 and 5语句

因为该语句只需要查询ID,而ID是k索引树的值，所以不需要回表
因为索引k已经覆盖了ID的查询需求，所以叫做覆盖索引

覆盖索引可以减少索引树的搜索过程，提升查询性能

1.2.2 扫描行数

索引k其实在k索引树读了3行记录（3，5，6），但对于MySQL server层找引擎拿到了两条记录（R3，R5），索引MySQL认为扫描行数为2

1.2.3 联合索引

市民信息表，身份证号已有普通索引，是否还需要建立身份证号和姓名的联合索引

如果有个高频请求根据身份证号查询姓名，那么此联合索引就可以用覆盖索引，不需要再回表查询整行信息
空间消耗，索引字段维护代价，需要权衡

2 最左前缀原则

2.1 B+树索引结构

问题：如果为每种查询都添加索引，索引就太冗余了，但是对于有的需求比如根据身份证号查询家庭地址回表扫描效率低

2.2 联合索引的最左前缀原则

不一定需要满足索引的全部定义，只要查询满足最左前缀，就可以利用索引来加速

最左前缀可以是：

联合索引的最左N个字段
联合索引的最左M个字符

比如：

根据（name，age）联合索引查询，当查询名字为张三的人和名字以张开头的人时都可以使用（name，age）联合索引来加速查询

当查到第一个名字为张三的记录和里面存储的主键值，向后遍历知道不满足条件为止
当查到第一个名字以张开头的记录和里面存储的主键值，向后遍历知道不满足条件为止

2.3 建立联合索引如何安排字段顺序

第一原则：通过调整顺序，可以少维护一个索引，优先考虑采用

复用能力
因为支持最左前缀，有了（a，b）索引一般不需要单独为a建立单独索引

第二原则：考虑空间占用

联合索引（a，b），索引a，b，通过第一原则可以减少一个单独索引，即（a，b）和 b 或（b，a）和 a
此时就要考虑字段大小
比如name和age字段推荐建立联合索引（name，age），因为字段name比字段age大，再单独维护索引age

3 索引下推

3.1 不符合最左前缀的部分

联合索引（name，age）

需求：查询出名字以张开头，年龄为10岁的男孩

select * from tuser where name like '张%' and age=10 and ismale=1;

索引向右匹配到范围索引（like，＞，＜，between）就会停止向右继续匹配，即范围查询

通过索引树，根据最左前缀，找到第一个名字以张开头的记录

MySQL 5.6 之前：

从该条记录开始一条一条回表，得到整行数据后再判断后面的字段值

MySQL 5.6之后：

索引下推（index condition pushdown）：

Index Condition Pushdown，简称 ICP。
Mysql 5.6版本引入的技术优化。
旨在在“仅能利用最左前缀索的场景”下（而不是能利用全部联合索引），对不在最左前缀索引中的其他联合索引字段加以利用——在遍历索引时，就用这些其他字段进行过滤(where条件里的匹配)。
过滤会减少遍历索引查出的主键条数，从而减少回表次数，提示整体性能。
如果查询利用到了索引下推ICP技术，在Explain输出的Extra字段中会有“Using index condition”。即代表本次查询会利用到索引，且会利用到索引下推。
索引下推技术的实现——在遍历索引的那一步，由只传入可以利用到的字段值，改成了多传入下推字段值

在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不满足条件的记录，减少回表次数

本问题中，当在索引树中查询名字以张开头的记录时，索引下推会判断age字段是否满足条件，不满足则不需要回表

4 练习和问题

4.1 多字段主键索引

联合主键索引（a，b）

普通索引 c，联合索引（c，a），（c，b）

InnoDB会把主键字段放到索引定义字段后面，
当然同时也会去重。
所以，当主键是(a,b)的时候，
定义为c的索引，实际上是（c,a,b);
定义为(c,a)的索引，实际上是(c,a,b)
定义为(c,b）的索引，实际上是（c,b,a)

4.2 索引重建

通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理

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);

为何要重建索引？

索引因为删除，或页分裂等原因，导致数据页有空洞
重建索引会创建一个新的索引，把数据按顺序插入，页面利用率最高
索引更紧凑、更省空间

重建索引 k合理，可以省空间。但是，重建主键不合理。删除主键还是创建主键将重建表。所以连着执行这两个语句的话，第一个语句就白做了

解决方案

alter table T engine=InnoDB

4.3 索引的使用

表T两个字段id, uname
id主键
uname普通索引

SELECT * FROM T WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'

模糊查询like后面四种写法都可以用到uname的普通索引

添加一个age字段
SELECT * FROM T WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'

like后面的'%j'/ '%j%' 这两种情况用不到索引

select id / select uname / select id,uname FROM T WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'

like后面'j'/ 'j%' / '%j'/ '%j%' 这四种情况又都可以用到uname普通索引

建立uname,age的联合索引
select id / select uname / select id,uname FROM T WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'

会用到uname的普通索引

SELECT * FROM T WHERE uname LIKE 'j'/ 'j%' / '%j'/ '%j%'

uname,age的联合索引

5 总结

覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据
最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

posted @ 2020-11-04 14:22 Pengc931482 阅读(78) 评论(0) 收藏举报

刷新页面返回顶部

Pengc931482

MySQL - 实战 (4) - 索引(下)