在上一篇文章中我补充说明了下索引的相关知识，这篇文章就会说明如何高效的而是用索引。

1.独立的列

在MySQL中，如果查询中的列不是独立的，就不会使用索引。“独立”指的是索引列不能是表达式的一部分，也不能是函数的参数。比如如下SQL，在age列创建索引，但查询时利用索引

select name from student where age+1=5

2.前缀索引

有时候，索引列可能是很长的字符串，会使得索引变得大且慢。通常可以索引开始的一部分字符，可以大大提高索引效率，但也会降低索引的选择性（不重复的索引值和数据表的记录总数的比值）。所以，重点就在于要选择足够长的前缀来保持较高的选择性，但是为了节约空间又不能太长。

在联合索引中，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找，这就是最左匹配原则

假设，对（a，b）建立索引

如图所示他们是按照a来进行排序，在a相等的情况下，才按b来排序。

因此，我们可以看到a是有序的1，1，2，2，3，3。而b是一种全局无序，局部相对有序状态!

实例：

SELECT * FROM table WHERE a = 1 and b = 2 and c = 3;

对(a,b,c)或者(c,b,a)或者(b,a,c)建立索引都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。

例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候，优化器会帮我们调整where后a,b,c的顺序，让我们用上索引。

题型2
```
SELECT * FROM table WHERE a > 1 and b = 2; 
```
对(b,a)建立索引。如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。
题型3
```
SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3; 
```
(b,a)或者(b,c)都可以，要结合具体情况具体分析。
题型4
```
SELECT * FROM `table` WHERE a = 1 ORDER BY b;
```
一看就是对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！
题型5
```
SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1; 
```
还是对(a，b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

聚簇索引不能算是一种单独的索引类型，而是一种数据存储方式。在MySQL中，InnoDB的聚簇索引指的是在同一个存储空间中保存了索引的数据行，InnoDB通过主键聚集数据，如果没有定义主键，会选择一个唯一的非空索引代替。

聚簇索引的叶子节点存储的是表中的数据，而与之对应的非聚簇索引，也叫二级索引，叶子节点存储的是对应行的主键值，通过二级索引查询时，存储引擎需要先找到二级索引的叶子节点对应的主键值，再通过主键值去聚簇索引中查找对应的行，等于做了重复的工作，即回表。

一个典型的优化例子就是大批量分页问题：

select * from table where status = xx limit 10000,10

首先，由于B+树的性质，叶子节点会组成链表，找到第N个大的数，时间复杂度为o(n)

即使前10000个会扔掉，mysql也会通过二级索引上的主键id,去聚簇索引上查一遍数据，这可是10000次随机io，自然会慢很多。这是和mysql的分层有关系，limit offset 只能作用于引擎层返回的结果集。换句话说，引擎层也很无辜，他并不知道这10000个是要扔掉的。

优化方案如下，利用覆盖索引（索引包含所有需要查询字段的值），当二级索引查询的数据，只有id和二级索引本身，那么就不必再去查聚簇索引。

select * from table in (select id from table where second_index = xxx limit 10 offset 10000)

如上，先从条件查询中，查找数据对应的数据库唯一id值，因为主键在辅助索引上就有，所以不用回归到聚簇索引的磁盘去拉取。再通过这些已经被limit出来的10个主键id，去查询聚簇索引。这样只会十次随机io。

由于索引的高性能，在设计索引时，如果一个索引既能满足排序，又能用于查找，这是最理想的情况。

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引对结果进行排序；如果查询需要关联多张表，则只有当order by子句对应的字段全部为第一个表时，才能使用索引做排序。

order by子句仍然需要满足索引最左前缀匹配的原则，并且如果前面的列为常量，比如在where子句中对前导列制定了常量，也可以不用满足索引最左前缀匹配。

假设有学生信息表student，创建了（age,name,sex）的索引，如下sql语句是使用索引来排序的，尽管order by子句不满足索引最左前缀匹配，但在where子句中，索引的第一列已经被制定为常量，组合之后仍然满足索引最左前缀匹配原则。

select * from student where age=18 order by name,sex

下面列举一些不符合的情况：

使用不同的排序方向

select * from student where age=18 order by name desc,sex asc

索引列的第一列使用了范围条件

select * from student where age>18 order by name,sex

order by子句中使用不在索引中的列

select * from student where age=18 order by id