MySql——高性能索引策略

1 独立的列

如果查询的列不是独立的，那么MySQL不会使用索引。“独立索引”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如下面的查询无法使用actor_id列的索引：

肉眼可以看出where表达式其实等价于actor_id=4，但MySQL无法自动解析这个方程式。

2 前缀索引和索引的选择性

参见文章MySQL索引简述--索引选择及优化

3 多列索引

建立索引一个常见的错误是为每个列创建独立的索引，或按照错误的顺序创建多列索引。

在多个列上建立独立索引在大多情况下并不能提高MySQL的查询性能。MySQL5.0版本中引入了“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

索引合并策略使用时机：

• 当服务器需要对多个索引做相交操作（多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
• 当服务器需要对多个索引做联合操作（多个or条件），通常需要消耗大量的CPU和内存资源在算法的缓存、排序记忆合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。

4 选择合适的索引列顺序（适用于B-Tree索引）

正确的顺序依赖于使用该索引的查询，并且需要考虑如何更好的满足排序和分组需求。

在一个多列B-Tree索引中，索引列的顺序意味着所应首先按照最左列进行排序，其次是第二列，等等。所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的order by、group by 和 distinct 等字句的查询需求。

在不考虑排序和分组时，将选择性最高的列放在索引最前列。

以下面的查询为例：

应该创建一个（staff_id，customer_id）的索引还是应该颠倒顺序？可以根据列的选择性来进行排序，两个列的选择性如下：

customer_id的选择性更高，所以答案是将其作为索引列的第一列：

当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。例如，在某些应用中，对于没有登录的用户都将其用户名记录为“guest”，那么在行为会话表和其他记录用户行为的表中“guest”就成为一个特殊用户ID。一旦查询涉及这个账户，那么就会造成扫描大量的数据行。

例如在一个用户分享购买商品的论坛上，这个特殊标的查询就非常慢：

这个查询看似没有建立合适的索引，但是explain的结果如下：

MySQL为这个查询选择了索引（groupId，userId），如果不考虑列的基数，那么这是一个非常合理的选择。但如果考虑一下userId的groupId条件匹配的行数，可能就会有不同的想法了：

从上面的结果可以看出结合groupId的几乎满足表中所有行，符合userId的条件有130万条记录——也就是说索引基本没起到作用。对于这种情况的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止对这类用户和组执行这个查询。

5 使用索引扫描做排序

MySQL有两种方式可以生成有序的结果：通过排序操作以及按索引顺序扫描。

扫描索引本身是很快的，因为是需要从一条索引记录移动到紧接着的吓一跳记录。但如果所以不能查询所需的全部列，那就不得不扫描一条记录就会表查询一次对应的行。

MySQL可以使用同一个索引既满足排序，又用于查招行。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果进行排序。

有一种情况是order by 语句不需要满足最左前缀的要求，就是前导列为常量的时候。

例如，rental表在列（rental_date，inventory_id，customer_id）上有名为rental_data的索引：

MySQL可以使用rental_date索引为下面的查询做排序：

即使order by子句不满足最左前缀的要求，也可以用于查询排序，原因是索引的第一列指定为常数。

可以使用索引做排序的其他情况：

不能使用索引做排序的查询：

• 查询使用了两种不同的排序方向，但索引都是正序排序的：

• order by中使用了不在索引中的列：

• where和order by中的列无法组成索引的最左前缀：

• 索引列的第一列上是范围条件：

• 索引列上有多个等于条件（实质是范围查询）

6 压缩索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以加入到内存中。MyISAM压缩索引的方法是。先保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩为“7，ance”的形式。

7 删除重复索引、冗余索引和从不使用的索引

索引的缺点以及失效条件

索引的缺点

1. 索引加快了检索速度，但是降低了在带索引的数据列里插入、删除、以及修改数据的速度，也就是说索引降低了许多设计写入操作的速度。原因是对数据库进行写操作时不仅要求写入到数据行，而且要求所有的索引都要进行改变。
2. 缩阴需要占据磁盘空间。

索引失效条件

1. 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2. 对于多列索引，不是使用的第一部分，则不会使用索引
3. like查询是以%开头

4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6. 如果索引是表达式的一部分，则索引失效