再看索引

说在之前

在此之前，我想描述下我的一个疑惑，假设现在有一个这样的表，里面记录的相应人员的 性别[sex],年龄[age],和名字[name]，现在给这些字段建索引的话，众所周知，可以这样建sex,age,name，当在进行查询的时候，有可能并不限制性别，在这种情况下，如果是为了命中索引，可能会在原来的索引上添加 sex in (male, female)，这样的话，是否还需要继续建立一个新的索引呢? 这个是我的一个疑惑。

前情提要

在平时mysql的使用过程中，在接触中必不可少的便是关于索引的点点滴滴了。甚至于在进行查询优化的过程中，索引优化也同时扮演着十分重要的角色。深入了解mysql的前提，就是走进索引，了解索引，使用索引。

索引类型

索引类型中比较常见的有 BTree索引 和哈希索引（相对于btree更少，但是在一些合适的场景下，它带来的性能提升是巨大的）

• btree 索引：叶子节点中存储着索引的值，并且子节点中可能包含着多个叶子节点。特点是在btree的扩张过程中，总能保持平衡，叶子节点的深度总能保持一致。
• 哈希索引： 建立了哈希索引的表，在进行范围查询的时候比较费劲，因为其对应的有序的，是哈希之后的值，但是对于真正的值的存储是无序的。但是，如果是要进行精准查询的话，其性能是比较高的。另外在进行哈希索引的时候，唯一可能会导致数据查询低下的原因应该是数据的哈希冲突问题。

通过举例子的方式来看数据是否用到了索引吧：
假设目前有一个表：

create table rental {
    XXXXX
    PRIMARY KEY (rental_id),
    UNIQUE KEY rental_date (rental_date, inventory_id, customer_id)
    KEY idx_fk_inventory_id (inventory_id),
    KEY idx_fk_customer_id (customer_id),
    KEY idx_fk_staff_id (staff_id)
    
}

从上面的表中我们可以看到，总共对三个字段进行了索引的创建以及创建了一个唯一的索引（注意索引的顺序）.

因为我们进行需要对结果进行排序，因此通过这个唯一索引，我们来了解下什么时候，排序用上了索引，什么情况下，排序并没有使用上索引。

排序用上了该索引的情况：

where rental_date = "2020-01-01" order by inventory_id desc

where rental_date > "2020-01-01" order by rental_date,inventory_id

上面两个查询因为都满足最左匹配的原则，所以都使用上了索引。

不能使用到该唯一索引的情况：

• 索引排序不一致的情况下

where rental_date = '2020-01-01' aorder by inventory_id DESC, customer_id ASC

• 引用了一个不在索引中的列

 where rental_date = '2020-01-01' order by inventory_id, staff_id

• 无法组成索引的最左前缀

where rental_date = '2020-01-01' order by customer_id

• 在inventory_id 中使用到了in(),这对于排序来说相当于一种范围查询，因此无法使用索引。

 where rental_date = '2020-01-01' and inventory_id in (1,2) order by customer_id

g tv

索引使用的一些小tip

• 不要使用太多的in条件，因为每个in条件，其实是相当于一次排列组合，比如 A in(1,2,3) and B in (2,3,4),那么对于mysql解析来说，共有3*3 = 9 种情况，当值越多的时候，这种排列组合的情况也就越多。
• 由于范围查询之后无法使用范围列之后的索引，所以我们在确定使用范围列的情况下，一定要确认索引的使用顺序。
• 当需要进行分页的时候，我们经常需要使用 limit, offset 之类的东西，我们可以这样去使用，可以避免掉低效的多次查询。e.g： select * from A inner join (select id from A limit 1000,10) tmpA
• 如何保证使用到三星索引：
• • 一星： 将索引相关的列放到一起，只在一个必要的列上建立索引；
• • 二星： 索引中的数据排列顺序与查找中的排列顺序是一致的，通常将选择性最高的列放在索引的最前列。
• • 索引中的列包含了需要查询的所有数据，避免回表。
• 我们在使用explain 去解析一个sql的时候。如果extra列里是 using index，说明这个使用了聚簇索引，并且索引列中包含了所有需要的数据。