Wetts's blog

Stay Hungry, Stay Foolish.

0%

数据库-索引优化策略.md

发表于 分类于

转自：https://blog.csdn.net/zzti_erlie/article/details/103665903

索引的种类

众所周知，索引类似于字典的目录，可以提高查询的效率。

索引从物理上可以分为：聚集索引，非聚集索引

从逻辑上可以分为：普通索引，唯一索引，主键索引，联合索引，全文索引

索引优化策略

不要在索引列上进行运算或使用函数

在列上进行运算或使用函数会使索引失效，从而进行全表扫描。如下面例子在 publish_time，id 列上分别加上索引，publish_time 为 datetime 类型，id 为 int 类型

1
2
-- 全表扫描
select * from article where year(publish_time) < 2019
1
2
-- 走索引
select * from article where publish_time < '2019-01-01'
1
2
-- 全表扫描
select * from article where id + 1 = 5
1
2
-- 走索引
select * from article where id = 4

小心隐式类型转换

假设 id 为 varchar 类型

1
2
-- 全表扫描
select * from article where id = 100
1
2
-- 走索引
select * from article where id = '100'

为什么呢？

1
2
3
select * from article where id = 100
-- 等价于
select * from article where CAST(id AS signed int) = 100

上一条规则说过，不要在索引列上使用函数，隐式类型转换在索引字段上做了函数操作，因此会全表扫描

那么如果 id 是 int，执行下面这个语句是否会导致全表扫描呢？

1
select * from article where id = '100'

答案是会用到索引，我们来分析一下为什么会用到索引

我们先来做一个实验，看一下数据库中字符串和数字做比较的时候，是怎么转换的？

这里有个简单的方法执行select “10” > 9即可

  • 如果结果是1，则是把字符串转成数字，然后进行比较
  • 如果结果是0，则是把数字转成字符串（因为字符串比较是从高位到低位按照asciss码来逐位比较，“1”比“9”小，所以为0），然后进行比较
    1
    2
    3
    4
    5
    6
    mysql> select "10" > 9;
    +----------+
    | "10" > 9 |
    +----------+
    |        1 |
    +----------+

结果为 1 表明当字符串和数字进行比较的时候，是把字符串转成数字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select "a" = 0;
+---------+
| "a" = 0 |
+---------+
|       1 |
+---------+
1 row in set, 1 warning (0.00 sec)

mysql> select "123abc" = 123;
+----------------+
| "123abc" = 123 |
+----------------+
|              1 |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select "  123abc456" = 123;
+---------------------+
| "  123abc456" = 123 |
+---------------------+
|                   1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)

从实验结果中可以看到，当字符串不含有数字时，会转成 0，否则转成字符串中第一段连续的数字

我们接着来分析上面的例子，为什么一会会用到索引，一会不会用到

1
2
3
4
5
6
7
8
-- id列上有索引，id为varchar，不会走索引
-- id是字符串时，数据库中的id都要转成数字，转成的值不确定（例如id='12ab'会被转成12，不可能从索引上找到12这个值的）
-- 所以得全表扫描
select * from article where id = 100

-- id列上有索引，id为int，会走索引
-- id是int时，'100'会被转成数字100，所以能走索引
select * from article where id = '100'

前导模糊查询不会使用索引

1
2
-- 全表扫描
select * from article where author like '%李'

%李,%李%都会导致全表扫描，非前导模糊查询可以使用索引

1
2
-- 走索引
select * from article where author like '李%'

联合索引最左前缀原则

mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立（a,b,c,d）顺序的索引，d 是用不到索引的，如果建立（a,b,d,c）的索引则都可以用到，a,b,d 的顺序可以任意调整

  1. 将区分度最高的字段放在最左边

当不需要考虑排序和分组时，将区分度最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查找

如果在 a b 列上建立联合索引，该如何建立，才能使查询效率最高

1
select count(distinct a) / count(*), count(distinct b) / count(*), count(*) from table

执行如下语句，假设 3 个输出依次为 0.0001,0.373,16049，可以看到 b 列的选择性最高，因此将其作为联合索引的第一列，即建立（b, a）的联合索引

  1. 查询时=可以乱序
    如果建立了联合索引（a, b）。例如下面的2个写法是等价的，因为MySQL会将查询的顺序优化成和联合索引的顺序一致

    1
    select * from table where a = '1' and b = '1'
    1
    select * from table where b = '1' and a = '1'

  2. 优化查询，避免出现 filesort

    1
    select * from table where a = ? and b = ? order by c

    最左前缀原则不仅用在查询中，还能用在排序中。MySQL 中，有两种方式生成有序结果集：

  3. 通过有序索引顺序扫描直接返回有序数据

  4. Filesort排序，对返回的数据进行排序

因为索引的结构是 B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using index。

所有不是通过索引直接返回排序结果的操作都是 Filesort 排序，也就是说进行了额外的排序操作。EXPLAIN 分析查询时，Extra 显示为 Using filesort，当出现 Using filesort 时对性能损耗较大，所以要尽量避免 Using filesort

对于如下sql

1
select * from table where a = ? and b = ? order by c

可以建立联合索引（a, b, c）

如果索引中有范围查找，那么索引有序性无法利用，如

1
select * from table where a > 10 order by b

索引（a，b）无法排序。

放几个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 使用了a列
where a = 3

-- 使用了a b列
where a = 3 and b = 5

-- 使用了a b c列
where a = 3 and c = 4 and b = 5
 
-- 没有使用索引
where b = 3

-- 使用了a列 
where a = 3 and c = 4

-- 使用了a b列 
where a = 3 and b > 10 and c = 7
 
-- 使用了a b 列
where a = 3 and b like 'xx%' and c = 7

union，or，in 都能命中索引，建议使用in

1
2
3
select * from article where id = 1
union all
select * from article where id = 2
1
select * from article where id in (1 , 2)

新版MySQL的or可以命中索引

1
select * from article where id = 1 or id = 2

效率从高到低为union，in，or。in 和 union 的效率差别可以忽略不计，建议使用 in

负向条件索引不会使用索引，建议用 in

负向条件有：!=、<>、not in、not exists、not like 等

1
2
-- 全表扫描
select * from article where id != 1 and id != 2

知道 id 的所有取值范围，可以改为类似如下形式

1
2
-- 走索引
select * from article where id in (0, 3, 4)

建立覆盖索引

众所周知，表数据是放在一个聚集索引上的，而建立的索引为非聚集索引，非聚集索引的叶子节点存放索引键值，以及该索引键指向的主键。一般查找的过程是从非聚集索引上找到数据的主键，然后根据该主键到聚集索引上查找记录，这个过程称为回表，不清楚的看推荐阅读。

如有下面这个sql

1
select uid, login_time from user where username = ? and passwd = ?

可以建立（username, passwd, login_time）的联合索引，由于 login_time 的值可以直接从索引中拿到，不用再回表查询，提高了查询效率

经常更改，区分度不高的列上不宜加索引

更新会变更 B+ 树，更新频繁的字段建立索引会大大降低数据库性能。

“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

一般区分度在80%以上的时候就可以建立索引，区分度可以使用 count(distinct(列名))/count(*) 来计算

明确知道只会返回一条记录，可以加limit 1

当查询确定只有一条记录时，可以加 liimit 1，让 MySQL 停止游标移动，提高查询效率

1
select uid from user where username = ? and passwd = ?

可改为

1
select uid from user where username = ? and passwd = ? limit 1

对文本建立前缀索引

用邮箱登录是一个常见的问题，如果对email整个字段建立索引，会让索引变得大且慢

1
select username from user where email='xxx';

这时我们可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的区分度。索引的区分度是指，不重复的索引值和数据表的记录总数的比值。索引的区分度越高则查询效率越高，因为区分度高的索引可以让MySQL在查找时过滤掉更多的行。

因此我们选择足够长的前缀保证较高的区分度，同时又不能太长（以便节约空间）

可以进行如下实验

1
2
3
4
select count(distinct left(email, 5)) / count(*) as col5,
count(distinct left(email, 6)) / count(*) as col6,
count(distinct left(email, 7)) / count(*) as col7
from user

假设输出依次为0.0305，0.0309，0.0310
查询显示当前缀长度达到7的时候，再增加前缀长度，区分度提升的幅度已经很小了，因此创建email(7)的前缀索引即可

需要注意的一点是，前缀索引不能使用覆盖索引

建立索引的列不为NULL

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。

因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL。