三星索引指的是对于一个查询，设立了三个通用的索引条件满足的条件，建立的索引对于特定的查询每满足一个条件就表示该索引得到一颗星，当该索引得到三颗星时就表示该索引对于该查询是一个三星索引。三星索引是对于特定查询的最优索引，建立三星索引的条件如下：
取出所有的等值谓词的列（WHERE COL=…）作为索引开头的列；
将order by中的列加入到索引中；
将查询语句中剩余的列加入到索引中，将易变得列放到最后以降低更新成本。
比如对于如下的查询，索引(first_name, last_name, email)就是一个三星索引：
SELECT
first_name,
last_name,
email
FROM actor5
WHERE first_name = 'hawIPYaXHTSKHlTstt'
ORDER BY last_name;
仔细分析三星索引的创建过程可以发现如下规律：
覆盖等值谓词条件，如first_name，可以过滤大部分的索引片数据；
覆盖order by字段可以避免对结果集的排序，如last_name;
覆盖其余字段可以避免回磁盘读取数据，即使用了覆盖索引扫描，如email。
- 不恰当的索引用法
3.1 无法使用索引的情形
对索引字段使用MySQL函数（可以对等于号后的值使用，不能对字段使用）
select * from actor where lower(first_name)='rmqchuezjthp’;
正确做法：
select * from actor where first_name='rMqChueZJThP';
隐式字符串转换（这里license字段为一个varchar类型字段）
select * from actor where license=6535;
正确做法：
select * from actor where license='6535';
对索引字段使用数学表达式
select * from actor where hash_email + 2 = 4224712734;
正确做法：
select * from actor where hash_email = 4224712732;
总结：从上述无法使用索引的情形可以看出，如果对索引字段进行了任何的表达式运算，那么其都会使索引功能失效，这是因为索引始终是一个B+树，使用其进行索引片过滤的时候是通过“二分查找”实现的，如果进行了计算，那么就无法使用“二分查找”功能，也就使得索引失效了。
3.2 困难谓词
3.2.1 不等式，如>，<，!=等
select A, B, C, D from TABLE where A=a and B>b and C>c;
推荐索引：(A, B, C, D)或(A, C, B, D)
对于上述查询，如果B和C字段的选择性，哪一个高就将其放在索引字段的前面。对于索引(A, B, C, D)，在查询时首先会根据字段A的等值条件和B的不等值条件进行索引片的过滤，然后扫描索引中B字段大于b的数据，在扫描过程中会判断获取到的数据是否满足C>c的条件，并且将符合条件的数据的D字段的值取出来，最后得到的结果集就是最终的结果集。该查询中A和B字段是参与了索引片的过滤的，而C和D字段则参与了索引覆盖扫描。
3.2.2 OR谓词
select A, B, C from TABLE where A>a or B>b;
推荐索引(A)和(B)
对于OR条件查询，由于并不是满足其中一个条件即可，而是两个条件只要满足一个即可。这里推荐索引为建立两个单列索引(A)和(B)，因为MySQL可以通过这两个索引进行“索引合并扫描”，也就是其首先会扫描索引A，获取其符合A>a条件的数据id，然后扫描索引B，获取其符合B>b的数据id，然后将两个扫描结果进行合并，最后通过合并的数据id在磁盘上读取数据。
对于OR谓词的索引合并扫描需要说明的是，如果需要合并的结果集非常大，或者是结果集中重复数据过多，那么进行结果集的合并将是一个非常耗时的操作，有时候效率还不及全表扫描。对于这个问题的另一个解决办法就是新建一个字段，取值1和0，标识其是否符合where条件，这样就只需要对该字段进行查询即可，也可以建立相关的索引。
3.2.3 IN谓词
select A, B, C from TABLE where A in (m, n, p) and B=b;
推荐索引：(A, B)
这里IN谓词严格意义上讲不是一个困难谓词，放在这里是为了借用OR谓词的索引合并扫描进行说明。对于IN谓词后的列表，MySQL会循环列表中的数据，然后分别于后续索引字段联合，比如对于上述查询，其可以拆分为(A=m and B=b) union (A=n and B=b) union (A=p and B=b)。拆分之后MySQL会首先根据A=m and B=b扫描联合索引(A, B)，获取结果集的id，然后根据A=n and B=b再次扫描该索引，依次循环，知道所有IN列表条件都扫描完成。由于IN列表条件是不重复的，因而最后扫描索引片也是不重复的，在进行结果集的合并的时候也就没有类似OR谓词的去重操作，因而查询效率非常的高。总结来说，IN谓词及其后续字段是可以使用到索引的。
- 总结
本文首先讲解了数据的存储方式和索引的结构，然后对各种创建索引的方式进行了深入的讲解，并且讲解了其中需要注意的点，最后我们介绍了一些使用索引时需要注意点和一些困难谓词。
网友评论