高性能mysql-索引策略

高性能的索引策略

1、独立的列

• 如果查询中的列不是对立的，则mysql不会使用索引，“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。
• mysql无法自动解析这个方程式，这完全是用户行为。我们应养成简化where条件的习惯，始终将索引列单独放在比较符的一侧。

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

索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但是也降低了索引的选择性。

索引选择性是指，不重复的索引值（也称为基数）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为索引选择性提高的索引可让 mysql在查找是过滤更多的行， 唯一索引的选择性是1.这是最好的索引选择性 ，也是性能最好的。

但是对于BOLB、TEXT或者更长的varchar类型的列，必须使用前缀索引，因为mysql不允许索引这些列的完整长度。

优点好处：1、保证了较高的选择性 2、节约磁盘存储空间 3、基数更趋向于完整列的基数

缺点注意：1、前缀索引使用后，mysql无法用order by 和group by ，也无法使用前缀索引做覆盖扫描 2、有时后缀索引也有用途，例如找到某个域名的电子邮件地址，msql原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可通过触发器来维护这种索引

简单示例：

1、查找常见的城市列表

2、查找最频繁出现的城市前缀，先从3个前缀字母开始

3、增加前缀长度为7，直到这个前缀的选择性接近完整列的选择性

4、计算合适前缀长度计算完整列的选择性，并使前缀选择性接近于完整列的选择性。

5、意味着前缀索引选择性越接近0.031，基本上就可用，针对不同前缀长度进行计算，对大表非常有用。根据下面的比例找出最合适建立前缀索引的长度为7

3、多列索引

show create table 查看创建sql语句

忽略索引合并，检查下查询和表的结构，通过参数optimizer_switch 来关闭索引合并功能，也可以使用IGNORE INDEX提示优化器忽略掉某些索引

如何设计完成最优索引，“三星”索引：

那么不如忽略where字句，集中精力优化索引列的顺序，或者创建全覆盖索引。

1. 在多个列上建立单列索引大部分情况下不能提高mysql的查询性能。可以使用索引合并的策略，一定程度上可使表上的多个单列索引来定位指定的行。

例子：

​ 两个字段分别创建单列索引，以下sql并不是最好的选择，

索引合并策略有时候是优化对的结果，但是实际上更多说明表上的索引建的很糟糕：

1、当出现服务器多个索引做相交操作是，（通常有多个and条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。

2、当出现服务器多个索引做相交操作是，（通常有多个or条件），通常消耗大量cpu和内存资源在算法的缓存、排序和合并操作上，特别是索引的选择性不高。需要合并和扫描返回大量数据的时候

4、选择合适的索引列顺序

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

创建高效索引的经验法则：

1、选择性最高的列放在索引的最前列

在不考虑排序和分组的情况下，创建多列索引

5、聚簇索引

并不是单独的索引类型，而是一种数据存储方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。

当表中有聚簇索引时，它的数据行实际上存放在索引的叶子页中，表示数据行和相邻的键值紧凑地存储在一起，无法同时把数据行存放在两个不同的地方，所以一个表中只能有一个聚簇索引，（不过，覆盖索引可以模拟多个聚簇索引的情况下）

6、覆盖索引

7、使用索引扫描来做排序

mysql 有两种方式可以生成有序结果，1、通过排序操作 2、按索引顺序扫描

如果EXPLIAN 出来的type列的值是“index”，则说明mysql使用了索引扫描来做排序

扫描索引本身很快，因为只需要从一条索引记录移动到紧接着下一条记录，如果索引不能覆盖所需的全部列，就不得不每扫描一条索引记录都回表查询一次对应的行。

mysql可以使用同一个索引满足排序，又可用于查询行。 设计索引应同时满足这两种任务

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

2、order by 子句和查找型查询限制是一样的：需要满足索引的最左前缀的要求；否则mysql都需要执行排序操作，而无法使用索引排序。但是有一种情况可以不满足索引的最左前缀要求，就是先导列为常量时，如果where子句或者join子句中对这些列指定了常量，就可以“弥补”索引的不足。

第一种情况如下：

第二种情况如下：

第三种情况如下：

8、压缩（前缀压缩）索引 MyISAM

9、冗余和重复索引

mysql是允许相同列创建多个索引的，无论是有意还是无意。mysql都需要单独维护重复索引，并且优化器在优化查询的时候也需要逐个的考虑，影响性能。

重复索引：在相同列上按照相同的顺序创建相同类型的索引，应避免创建重复索引，发现以后请尽快移出。错误代码如下：

经验不足的用户可能想创建一个主键，先加上唯一限制，然后在加上索引以供查询使用。事实上，mysql的唯一限制和主键都是通过索引实现的 ，因此该相同列上创建三个重复索引。通常并没有理由这样做，除非在同一列上创建 不同类型的索引满足不同的查询需求。

冗余索引：

1、创建了多列索引（A,B），在创建索引（A）就是冗余索引，因为这只是前一个索引的前缀索引。（只是B-Tree索引来说）

如果在创建多列索引（B,A），就不是冗余索引；

索引（B）也不是，因为索引B不是（A,B）的最左前缀列。

大多数情况下，都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但是也有时候处于性能的考虑需要冗余索引，因为扩展以后的索引会导致其变得太大，从而影响其他使用该索引的查询性能。

10、未使用索引

11、索引和锁