高性能索引

爱码僧 2020-01-03 18:52:47

高性能的索引策略：

1、独立的列：即始终将索隐裂单独放在比较符号的一侧

例如下面这条sql语句就不是独立的列：

select actor_id from test.actor where actor_id + 1 = 5

应当使用独立的列才可以使之走索引：

select actor_id from test.actor where actor_id = 6

2、前缀索引和索引的选择；优点为索引校小，缺点是不能做order by 和group by：

有时候需要索引很长的字符列(某个数据列的字符很大很长，例如存储url的列、blob列、TEXT列)，这会让索引变得大且慢，一个策略是模拟哈希索引，有时候这样做还不够，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样会降低索引的选择性。诀窍是选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

前缀索引选择实例：

selelct count(distinct left(city, 3)) / count(*) as sel from test.city;

sel越大，对应自变量left所选择的字符数3越大，选择性越高；当变化的幅度由大变小时即可定位为选择性最优，这样就兼顾了长前缀且高选择性且索引值最小。

创建缀索引创建实例：

mysql>alter table test.city add key (city(7));

--这里多次尝试只有 left(city, 7)是最优解，所以这里选择7个字符的前缀作为索引。

3、多列索引

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

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

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

对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。