高性能mysql--高性能的索引策略（上）

独立的列作为索引

最简单的就是使用独立的列作为索引了，主键，外键，唯一值的属性都是默认的索引。

没有使用索引的情况

以university列为索引

很明显，对所需要查的单独的列明显加快了查询时间。

 

但是独立的列作为索引使用不好也是有问题的。比如我在表中的year字段上加上索引

SELECT * FROM student WHERE YEAR = 2018。查询的分析结果如下

当我查询同样的年份数据但是采用另一种sql形式来查询

SELECT * FROM student WHERE YEAR + 1 = 2019

 

所以在进行查询时，一定要养成把单独索引列单独放在判断符的一边。

前缀索引与索引选择性

在数据库表中，可能有些字段存储的数据很长，要将其整个内容作为索引显然是不明智的选择，所以要使用前缀索引，并选择出前缀的长度是多少。

在计算这个长度值时，使用一个比例公式来计算出其前缀的选择长度接近于完整列。下面显示如何计算完整列的完整性。

 

Select count(Distinct 列的前缀)/count(*) from 表名。通常来说计算出来的值接近于一个数值趋于稳定，基本上就可以了。

 

前缀索引是一种能使索引更小、更快的一种有效方式，但另一方面也有其缺点：mysql无法使用前缀索引做order by 和 group by，也无法使用前缀索引做覆盖扫描。

 

一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。例如基于mysql的应用在存储网站的会话时，需要在一个很长的十六进制字符串上创建索引。此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

 

注：mysql是不支持反向索引的，所以如果要实现反向索引的话，需要将字段进行反向存储再进行索引。

多列索引

为每个列创建一个独立的索引或者按照错误的顺序创建多列索引是一种错误的使用索引情况。

为每个列创建一个索引是最不明智的想法，在最好的情况下也只能达到“一星索引”

注：通过星级来评判索引的好坏

一星：索引将相关的列放到一起，即在一系必要的列上建立索引，不必为在where条件里面的列都建立索引。

二星：索引中的数据列顺序和查找中排列顺序一致。通常将选择性最高的列放到索引的最前列。

三星：索引中的列包含了查询中需要的全部列。索引包含查询所需要的数据列，不再进行全表查表（聚簇索引、覆盖索引）

 

示例：假设有个电影演员表（film_id,actor_id）。使用语句select film_actor, actor_id from film_actor where actor_id = 1 or film_id = 1这个语句在较老的mysql版本中，mysql对这个查询会使用全表查询。除非把查询改写成union的形式。在mysql的后续更新版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并。Or条件的联合和and条件的相交。

 

索引合并策略是一个优化的结果但是更多的时候说明了表上的索引建的很糟糕。

1. 当服务器出现多个索引做相交操作时（通常有多个and条件）。这意味着需要多个列组成的多列索引而不是多个单列索引。
2. 当服务器需要多多个索引做联合操作时（通常含有多个or条件），通常需要耗费大量cpu和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描大量的数据的时候
3. 优化器不会将优化的成本计算到查询成本中，所以查询成本会被低估，可能会导致优化计划还不如全表扫描。所以有些时候还不如使用union来进行查询快

 

如果explain中有索引合并，应该好好看下索引是不是最优的，还可以关闭索引合并的功能