MySQL高性能索引创建

1、高性能索引创建策略

1.1 索引列的类型尽量小

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有tinyint、smallint、mediumint、int、bigint，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，这是因为数据类型越小，在查询时进行的比较操作越快（CPU层次)数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘/0带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。尤其适用于主键，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的I/0。

1.2 如何选择索引

2.1 创建索引时，我们应选择选择性/离散性高的列。索引的选择性/离散性是指：不重复的索引值和数据表的记录总数（N)的比值，范围从1/N到1之间。索引的选择性/离散性越高则查询效率越高，因为选择性/离散性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

很差的索引选择性就是列中的数据重复度很高，如性别字段：一般只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。

2.2 如何计算索引的选择性/离散性

SELECT count(DISTINCT 需要建立索引的字段名)/count()  FROM 表名; 

1.3 创建前缀索引

1.3.1 针对blob、text、很长的varchar字段，mysql不支持索引他们的全部长度，需建立前缀索引。

创建前缀索引SQL：Alter table tableName add key/index (column(X))，注意：前缀索引是一种能使索引更小、更快的有效办法，但是MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。有时候后缀索引 ，也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。

1.3.2 如何确定前缀索引的长度

我们可以通过不同计算不同索引长度的离散型来确定合适的索引长度，如

SELECT 
COUNT(DISTINCT LEFT(字段名,1))/COUNT(*) AS s1,
COUNT(DISTINCT LEFT(字段名,2))/COUNT(*) AS s2,
COUNT(DISTINCT LEFT(字段名,3))/COUNT(*) AS s3,
COUNT(DISTINCT LEFT(字段名,4))/COUNT(*)AS s4,
COUNT(DISTINCT LEFT(字段名,5))/COUNT(*) AS s5,
COUNT(DISTINCT LEFT(字段名, 6))/COUNT(*) As s6,
COUNT(DISTINCT LEFT(字段名, 7))/COUNT(*) As s7,
COUNT(DISTINCT LEFT(字段名, 8))/COUNT(*) As s8,
COUNT(DISTINCT LEFT(字段名, 9))/COUNT(*) As s9,
COUNT(DISTINCT LEFT(字段名, 10))/COUNT(*) As s10,
COUNT(DISTINCT LEFT(字段名, 11))/COUNT(*) As s11,
COUNT(DISTINCT LEFT(字段名, 12))/COUNT(*) As s12,
COUNT(DISTINCT LEFT(字段名, 13))/COUNT(*) As s13,
COUNT(DISTINCT LEFT(字段名, 14))/COUNT(*) As s14,
COUNT(DISTINCT LEFT(字段名, 15))/COUNT(*) As s15,
COUNT(DISTINCT 字段名)/COUNT(*) As total
FROM 表名;

1.4 创建多列索引

日常工作中大多数人常见的错误就是，为每个列都创建独立的索引，或者按照错误的顺序创建多列索引。

1.4.1 索引列的顺序。

正确的多列索引的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。反复强调过，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，依次类推。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求，因此多列索引的列顺序至关重要。

1.4.2 如何选择索引的列顺序

将选择性最高的列放到索引最前列。当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。

然而，性能不只是依赖于索引列的选择性，也和查询条件的有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，比如排序和分组，让这种情况下索引的选择性最高。

同时，在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

1.5 三星索引

对于一个查询而言，一个三星索引，可能是其最好的索引。

三星满足满足的条件如下：

• 索引将相关的记录放到一起则获得一星，简而言之即为让索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好，最好将索引片收缩到一个较窄的连续范围内

• 如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星），即当查询需要排序，group by、 order by，查询所需的顺序与索引是一致的（索引本身是有序的）；从而达到存储引擎不用再另外排序了，一般来说排序可是影响性能的关键因素。

• 如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星），即覆盖索引，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

其中最重要的是第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。