14. 索引

俄罗斯刺沙蓬
137 阅读12分钟

什么是索引？

类似字典或者电话簿里的目录，可以加速查询。 多数情况下，索引很小，可以放进内存里。

但是索引也有代价，比如增加数据库的大小，因为他们必须永久存储在表旁边；每次添加、修改和删除时，MySQL必须更新对应的索引，这会影响正常操作的性能。

所以我们应该为性能关键的表创建索引，因为索引的最终目的是加速查询，如果基于表的设计为所有表添加索引，是浪费资源的，不要盲目创建索引。

索引在内存中通常表示为二叉树。

创建索引

explain select customer_id from customers where state = 'CA';

执行上面语句后，会返回如下信息：

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersALL101210.00Using where
  • type 表示当前的查询是 ALL，即会在查询的表中遍历所有的记录，全表扫描
  • rows 表示遍历查询总共有 1012 行 显然如果用户数量增加，遍历的查询会越来越慢。
create index idx_state on customers (state);

通过上面的命令可以创建一个索引，此时再去解释查询语句，就会变成下面：

|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| |---|---|---|---|---|---|---|---|---|---|---|---|---| |1|SIMPLE|customers||ref|idx_state|idx_state|8|const|112|100.00|Using where; Using index|

  • type 是 ref，没有再做全表扫描
  • rows 中扫描的记录条数明显减少了
  • possible_keys 表示 MySQL执行查询时可能会考虑的索引，可能会有多个索引
  • key MySQL实际使用的索引

练习

explain select customer_id from customers where points > 1000;
create index idx_points on customers (points);

前 后

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersrangeidx_pointsidx_points4529100.00Using where; Using index

查看索引

show indexes in customers;
TableNon_uniqueKey_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentVisibleExpression
customers0PRIMARY1customer_idA12BTREEYES
customers1idx_state1stateA48BTREEYES
customers1idx_points1pointsA789BTREEYES
  • Key_name 表示索引名称 第一个 PRIMARY 表示聚集索引，每个表自动有的主键默认是聚集索引，每个表也只有一个聚集索引 第二个和第三个都是二级索引 还有一种是外键，也是二级索引，可以快速链接到别的表上
  • Column_name 表示索引在的列上
  • Collation 表示数据在索引中的排序规则，A表示升序，D表示降序
  • Cardinality 表示索引中唯一值的估计数量，不是实际数量，要想获得实际数量，可以先执行analyze table customers
  • Index_type 表示索引类型 BTREE 表示二分树

前缀索引

当索引的列是字符串时（包括 CHAR、VARCHAR、TEXT、BLOG），尤其是当字符串较长时，我们通常不会使用整个字符串而是只是用字符串的前面几个字符来建立索引，这被称作 Prefix Indexes 前缀索引，这样可以减少索引的大小使其更容易在内存中操作

create index idx_lastname on customers (last_name(20))

上面是创建前缀索引的方法，但是如何选取前缀的值呢？

  • 如果值太少，达不到彻底加速查询的目的，相同前缀的结果还是会遍历查找
  • 如果值太多，会造成空间和时间的浪费 所以必须找到一个恰当的值设置为前缀索引长度
select 
	count(distinct left(last_name,1)), -- 25
	count(distinct left(last_name,5)), -- 967
    count(distinct left(last_name,10)) -- 997
from customers;

执行的目的是用找到一个最小的数，通过最少的前缀字符，能够匹配出尽可能多的原文中的记录 显然在这里 5 最优，因为 1 得到的太少，而 10 增长的数据量有限。

全文索引

select *
from posts
where title like '%react redux%' or
	body like '%react redux%';

像这种搜索关键词的查询使用like并不方便，一是没有索引会导致全表扫描，效率低下；二是返回结果只会包含关键词，不符合搜索引擎的效果。

通过建立 全文索引 来实现搜索引擎的搜索 建立全文索引：

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body);

利用全文索引，结合 MATCH 和 AGAINST 进行 google 式的模糊搜索:

select *,match(title,body) against('react redux')
from posts
where match(title,body) against('react redux');

注意：MATCH后的括号里必须包含全文索引 idx_title_body 建立时相关的所有列，不然会报错

全文检索有两个模式：

  • 自然语言模式，自然语言模式是默认模式，也是上面用到的模式。
  • Boolean模式，可以更明确地选择包含或排除一些词汇（google也有类似功能）
select *,match(title,body) against('react redux')
from posts
where match(title,body) against('react -redux +form' in boolean mode);
  • in boolean mode 表示使用Boolean模式
  • - 表示排除后面的关键词，+表示必须出现后面的关键词，使用双引号 "" 表示必须准确包含这个短语

总结

需要全文搜索的字段，比如描述，文章内容等 不需要全文搜索的字段，比如姓名，地址，使用前缀索引就够了

复合索引

show indexes in customers;

explain select customer_id from customers
where state = 'CA' and points > 1000;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersrefidx_state,idx_pointsidx_state8const11252.27Using index condition; Using where

通过上面的语句的执行结果可以发现，MySQL 只使用了一个state上的索引，所以

  • MySQL 不管一条查询条件会有多少个索引，只会选择一个索引使用
  • 对于索引之外的列，依然是使用全部遍历的方法来查找，但后半部分的数据量非常大的时候，索引的效率会降低。

复合索引可以同时对多列创建索引

create index idx_state_points on customers (state,points);

再次执行上面解释查询语句，可以明显发现查询的数量减少了（rows 从112 到 58），效率提高了

idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersrangeidx_state,idx_points,idx_state_pointsidx_state_points1258100.00Using where; Using index

在 possible_keys 上可以发现有三个索引，当时 MySQL 发现复合索引效率最高，所以自动选择了他。

注意： MySQL 会给每个索引自动加上主键。 所以过多的索引会占用大量储存空间，而且数据每次数据更新都会重建索引，所以过多的索引也会拖慢更新速度 实际中更多的是用到组合索引，一个组合索引最多可组合 16 列，根据实际的查询需求和数据量来考虑复合索引的列数

删除索引

drop index idx_state on customers;
drop index idx_points on customers;

复合索引中列的顺序

有两条基本规则：

  1. 需要对列进行排序，让更频繁使用的列在前面。这有助于缩小查询范围
  2. 把基数更高的列排在前面，基数表示索引中唯一值的数量，可以把表分割成在尽可能小，最大限度地缩小查询数量 这两条规则不是固定的，必要时还是具体问题具体分析。
-- 先给last_name索引，再给state索引
create index idx_lastname_state on customers
(last_name, state);
-- 先给state索引，再给last_name索引
create index idx_state_lastname on customers
(state,last_name);

explain select customer_id from customers
where state = 'CA' and last_name like 'A%';
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersrangeidx_lastname,idx_state_points,idx_lastname_state,idx_state_lastnameidx_state_lastname2107100.00Using where; Using index

MySQL 决定使用第二个索引，它要扫描的数量最少。

强制规定使用那个索引

explain select customer_id from customers
use index (idx_lastname_state)
where state = 'CA' and last_name like 'A%';

高效索引的建议

对于查询语句，虽然where last_name like 'A%' and state = 'CA'和where state = 'CA' and last_name like 'A%'的执行结果类似。like的范围模糊，=的约束性更高

但是后者的第一步查询使用索引可以将范围限制的更小，约束性更高，所以执行的效率更高

explain select customer_id from customers
use index (idx_lastname_state)
where state like 'A%' and last_name like 'A%'; -- 41行

explain select customer_id from customers
use index (idx_state_lastname)
where state like 'A%' and last_name like 'A%'; -- 51行

通过上面的例子可以发现，对于一个查询，应该是约束性越强的放在前面，当 state 从约束性高的=变为约束性较低的like后，执行idx_state_lastname索引的效率不如idx_lastname_state。

所以，高效索引结论是：对于一条有多个不同列条件的查询，约束性高的条件所在列，可以在复合索引中的排序靠前，即优先执行。

无用索引

explain select customer_id from customers
use index (idx_state_lastname)
where last_name like 'A%';

注意，对于复合查询出现的列，如果查询条件没有这一个列，相当于这里有一部分无用索引。 如果有这样的查询条件需求，最好在这一列上单独再创建一个索引。

复合索引提高效率的总结

  1. 让更频繁使用的列在前面
  2. 基数更高的列排在前面
  3. 考虑查询条件，具体分析

当索引失效时

例子1

explain select customer_id from customers
where state = 'CA' or points > 1000;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersindexidx_state_points,idx_state_lastnameidx_state_points12101234.72Using where; Using index

上面的结果中可以发现，他比全表扫描快，因为它不需要从磁盘里读取数据，还是只需要在内存中查询。虽然查询的类型是使用索引，但是有接近全表扫描的查询。

这种时候，必须重写查询，以尽可能最好的方式利用索引。

create index idx_points on customers (points); --

explain 
	select customer_id from customers
	where state = 'CA' 
    union -- 自动去重记录
    select customer_id from customers
	where points > 1000;
  • 原本使用的idx_state_points索引可以快速定位 state ，所以可以不需要再单独添加一列索引
  • 添加一个idx_points 再快速找到 points > 1000 的记录
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1PRIMARYcustomersrefidx_state_points,idx_lastname_state,idx_state_lastnameidx_state_points8const112100.00Using where; Using index
2UNIONcustomersrangeidx_state_points,idx_pointsidx_points4529100.00Using where; Using index
3UNION RESULT<union1,2>ALLUsing temporary

从结果可以发现，先前扫描了 1000+ 条记录，但是现在只需要扫描 600+ 条记录，有了不小的提升。

例子2

explain select customer_id from customers
where points + 10 > 2010;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersindexidx_points41012100.00Using where; Using index

因为有了一个表达式，MySQL必须对这张表全表扫描，索引完全失效。

可以直接简化这个表达式。

使用索引排序

explain select customer_id from customers 
order by state;

explain select customer_id from customers 
order by first_name;
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
1SIMPLEcustomersindexidx_state_points121012100.00Using index

第一次：通过 state 列排序，MySQL会自动选择有关于这一列的索引，比如 idx_state_points 。Using index 表示使用到了索引进行排序。

1SIMPLEcustomersALL1012100.00Using filesort

第二次：对 first_name 排序，ALL 就意味着进行了全表扫描，Using filesort 意味着使用了“外部排序（ MySQL 的一种排序算法，很耗费资源的一种操作）”

通过show status like 'last_query_cost' 查看 MySQL 服务器的环境变量，发现第一次的花费是 1000+ ，第二次只有 100+

要使用索引排序的基本规则

explain select customer_id from customers 
order by state,points ;
  1. 不能出现索引中没有出现的列 索引肯定没有办法使用的 order by state,points,first_name
  2. 索引一般都是升序的，使用降序（desc）也会导致全表扫描，因为排序方向不一致。但是可以对单独的列进行降序排序。 order by state,points desc 不行 order by state desc,points desc 可以
  3. 排序列的顺序和索引中出现的一致，前缀也不能少。但是索引前缀列条件满足后可以继续使用索引排序 order by points,state和 order by points都不行，但是有一个例外，可以先通过索引进入到一个 state 里面，然后再通过索引进行排序，例如where state = 'CA' order by state,points可以

覆盖索引

explain select customer_id from customers -- 使用上了索引，100+
order by state;

explain select * from customers -- 全表扫描，索引不够，1000+
order by state;

show status like 'last_query_cost';

使用索引三步

  1. 先查看 where 这种查找语句是否有索引条件
  2. 再查看 order by 排序语句是否有索引条件
  3. 最后查看 select 后选出的列是否有索引条件

如果 SELECT 子句里选择的列在索引中，整个查询就可以在不碰原表的情况下完成，这叫作覆盖索引（covering index），即索引满足了查询的所有需求所以全程不需要使用原表，这是最快的

维护索引

重复索引

例如：（A，B，C）（A，B，C） MySQL不会阻止重复索引创建，所以这回造成资源浪费。

多余索引

例如：（A，B）（A） 因为原来的索引也可以优化包含 A 列的查询

总结

维护索引要去掉重复索引、多余索引和未被使用的索引。 总之，创建索引之前，一定要先查看一下已经有的索引。

avatar
文章
阅读
粉丝