创建高性能索引

前言

索引是存储引擎用于快速找到记录的一种数据结构。索引对于高性能来说是必需的，尤其是当数据量越来越大时，愈发显得重要。索引优化是对查询优化最有效的手段，它能轻易将查询性能提升几个数量级。而且值得注意的是一个'最优’的索引有时会比'好的'索引提升两个数量级，有时候需要重写查询。

                                                 --《高性能MySQL》

索引基础

索引策略

独立的列无法使用索引(独立的列是指索引列不能是表达式的一部分，也不能是函数的参数)

无法使用actor_id列的索引,因为MySQL无法自动解析 actor_id + 1 = 5 这个表达式
eg1: select first_name from sakila.actor where actor_id + 1 = 5;  
eg2: select ... where TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <=10;

应该始终将索引列单独放在比较符号的一侧。

前缀索引和索引选择性

生成数据集: 利用示例数据库sakila,从表city中生成一个demo表
CREATE TABLE sakila.city_demo(city VARCHAR(50) NOT NULL);
INSERT INTO sakila.city_demo(city) SELECT city from sakila.city;
INSERT INTO sakila.city_demo(city)  SELECT city FROM sakila.city_demo; # 执行5次
UPDATE sakila.city_demo SET city = (SELECT city from sakila.city ORDER BY RAND() LIMIT 1);

• 1 查找最常见的城市列表

SELECT COUNT(*) as cnt, city from sakila.city_demo GROUP BY city ORDER BY cnt desc LIMIT 10;

cnt city
68    London
51    Ashgabat
50    ostka
47    Faaa
47    Shivapuri
47    Rajkot
46    Bamenda
46    Syrakusa
45    Bhopal
45    San Felipe del Progreso

每个值的出现次数都在45-68次

• 2 查找最频繁出现的城市前缀 (3个)

SELECT COUNT(*) as cnt, LEFT(city, 3) as perf from sakila.city_demo GROUP BY perf ORDER BY cnt DESC LIMIT 10;

cnt perf
473    San
203    Cha
168    Tan
156    Shi
155    Sou
151    Sal
146    Man
132    Hal
131    Sha
129    al-

每个前缀比原来的城市出现的次数更多，因此唯一前缀比唯一城市要少得多。                        

• 3 继续增加前缀长度，直到接近完整列的选择性，发现为7的时候比较合适。

SELECT COUNT(*) as cnt, LEFT(city, 7) as perf from sakila.city_demo GROUP BY perf ORDER BY cnt DESC LIMIT 10;

cnt perf
78    San Fel
68    London
61    Santiag
60    Valle d
51    Ashgaba
50    ostka
47    Faaa
47    Shivapu
47    Rajkot
46    Bamenda

• 4 计算完整列的选择性，并使得前缀的选择性接近完整列的选择性。

SELECT COUNT(DISTINCT city)/COUNT(*) from sakila.city_demo;

COUNT(DISTINCT city)/COUNT(*)
0.0312

通常来说，如果前缀选择性能够接近0.031，就基本可用了，对大表非常有用。

• 计算不同前缀长度的索引选择性

SELECT
    COUNT(DISTINCT LEFT(city, 3)) / COUNT(*) AS sel3,
    COUNT(DISTINCT LEFT(city, 4)) / COUNT(*) AS sel4,
    COUNT(DISTINCT LEFT(city, 5)) / COUNT(*) AS sel5,
    COUNT(DISTINCT LEFT(city, 6)) / COUNT(*) AS sel6,
    COUNT(DISTINCT LEFT(city, 7)) / COUNT(*) AS sel7

FROM
    sakila.city_demo;
    
 sel3    sel4     sel5   sel6    sel7    
0.0239    0.0293    0.0305    0.0309    0.0310

例外情况: 即使接近选择性，也要查看数据是否分布均匀

• 创建前缀索引

ALTER TABLE sakila.city_demo ADD KEY (city(7));

前缀索引优点: 能使索引更小,更快。
缺点:无法做ORDER BY 和GROUP BY ,也无法使用前缀索引做索引覆盖。

多列索引

• 概念错误

1 为每个列创建独立的索引
2 按照错误的顺序创建多列索引
3 把WHERE条件里面的列都加上索引

CREATE TABLE t (
c1 INT,
c2 INT,
c3 INT,
KEY(c1),
KEY(c2),
KEY(c3)
);

• 索引合并策略

EXPLAIN SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1 OR film_id = 1\G;

           id: 1
  select_type: SIMPLE
        table: film_actor
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where
        
结论:
1 对多个索引做相交操作时(多个AND)，意味着需要一个包含所有相关列的多列索引，而不是多个单独的索引。
2 对多个索引做联合操作时(多个OR)，需要耗费大量的CPU,内存，资源在算法的缓存，排序和合并操作上。
3 改成UNION或许会更好。

• 选择合适的索引列顺序

1 不考虑排序和分组时，选择性最高的列放在前面，作用是优化WHERE条件的查找。
2 按照那些运行频率最高的查询来调整索引列的顺序。

select * from payment WHERE staff_id = 2 AND customer_id = 584; 
# 是否创建(staff_id, customer_id)还是颠倒一下顺序?

SELECT SUM(staff_id = 2), SUM(customer_id = 584) from payment;
# searchable argument (各个where分支对应的数据基数)

# 从结果来看，应该将索引列customer_id放在前面，因为数量更小

SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584; 
# 对于这个customer_id的条件值，对应staff_id的选择性

SELECT COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity, COUNT(DISTINCT customer_id)/COUNT(*) as 
customer_id_selectivity, COUNT(*) from payment; 

#customer_id的选择性更高，应该作为索引列的第一列
ALTER TABLE payment ADD KEY(customer_id, staff_id);

考虑特殊情况

使用前缀索引的时候，当某些条件的基数比正常值高的时候
1 游客用户guest
2 明星用户

一个案例

select count(DISTINCT threadId) as count_value from Message where (groupId = 10137) 
and (userId = 1288826) and (anonymous = 0) ORDER BY priority DESC, modifiedDate DESC

EXPLAIN部分结果:

table: Message
key: ix_groupId_userId
rows: 1251162
Extra: Using where

确定下sarg (searchable argument)
select count(*), sum(groupId = 10137), sum(userId = 1288826), sum(anonymous = 0) from message

count(*)             :4142217
sum(groupId = 10137) :4092654
sum(userId = 1288826):1288496
sum(anonymous = 0)   :4141934

可以看到:
1 符合组(groupId)条件几乎满足表中的所有行
2 符合用户(userId)条件的几乎有130万条记录

结论：
索引基本没什么用

解决办法:
修改程序代码 区分特殊群组 禁止针对这类群组执行这个查询