创建高性能索引

MagicON

前言

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

                                                 --《高性能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分支对应的数据基数)

clipboard.png

# 从结果来看,应该将索引列customer_id放在前面,因为数量更小
SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584; 
# 对于这个customer_id的条件值,对应staff_id的选择性

clipboard.png

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

clipboard.png

#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万条记录
结论:
索引基本没什么用
解决办法:
修改程序代码 区分特殊群组 禁止针对这类群组执行这个查询
阅读 846

黑客与音乐家
音乐爱好者的计算机之旅

正确的方向比努力更重要

562 声望
16 粉丝
0 条评论
你知道吗?

正确的方向比努力更重要

562 声望
16 粉丝
宣传栏