《高性能MySQL》笔记（2）——创建高性能索引

2018-03-19  本文已影响66人  esrever

创建高性能索引

索引是什么？有什么作用？

索引是存储引擎用于快速找到记录的一种数据结构

如书的目录索引一般，数据库中的索引也是另外存储一些数据表的关键信息，在查询的时候可以更方便定位到想要找的那一些内容页

在数据量小、负载低的时候，索引的作用可能并不明显。但一旦到达一定量级，正确的索引往往可以轻易将性能提升几个数量级。所以，索引优化是查询性能优化最有效的手段

大多数框架都实现了ORM，而ORM可能很难兼顾到 每个开发者、每个业务 专门定制化的索引。但这并不意味着使用框架和ORM就不需要理解索引！最好的方法是：

简单的、数据量较小的查询可以直接使用ORM，当某些复杂的查询出现性能问题时，考虑舍弃ORM，采用原生的SQL语句去优化性能

索引的类型

索引是在存储引擎层上实现的，而非服务器层。所以每个引擎支持的索引类型、实现方法都有所不同。在使用上需要有所注意。这里主要讨论的是InnoDB引擎的索引

B-Tree 索引

最常见的索引类型，绝大多数MySQL引擎都支持。一般没有特殊说明，索引即B-Tree索引（这类索引采用类似B-Tree的结构来存储数据，如 B树、B+树 等）

InnoDB使用B+ Tree的结构存储索引

B+树

参考链接：理解B+树算法和Innodb索引

为什么通过索引查找数据，可以加快访问的速度？

B-Tree 结构存储索引，通常意味着所有数据按照一定的顺序存储。在查找的时候，引擎就可以从根结点比较节点页的值和要查找的值，找到合适的指针进入下层子节点，不断重复比较最终找到相应值。避免了使用全表扫描

特别的，在B+ Tree结构中，所有的非叶子节点都用作“查找”，叶子节点则指向具体的数据。如：有100条记录，对于一个索引树，有且仅有100个叶子节点，它们分别指向每一条记录，这些记录本身是有序且用指针相连

所以，索引在按照索引顺序去查找范围数据的时候，效率也非常高

索引对多个值进行排序的记录顺序

如，组合索引key(last_name, first_name, birth)，这个索引对应的记录的顺序是，先按照last_name排序，如果相同，则按first_name，最后按birth。

这就是为什么条件的顺序必须与组合索引的顺序一致

order by也可以使用索引

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的order by操作

可以使用B-Tree 索引的查询类型

假设有一索引，key(last_name, first_name, birth)

B-Tree 索引的查询限制

哈希索引

自定义(伪)哈希索引

不支持哈希索引的引擎，可以通过新增一个被索引的hash列，用crc32等算法做哈希，可以优化查询

例子：需要存储并查询一个url列

select * from tbl where url = 'http://xx.com/xx/yy/?zz=aa&bb=cc'

上面的语句明显在比较上非常吃力，如果对url列设置索引，需要比较大的代价。如果引入一个被索引的hash列，在查找的时候，性能会变得很高

select * from tbl where url_crc = CRC32('URL...') and url = 'URL...'

注意：

全文索引

一种特殊类型的索引，查找的是文本中的关键词，而不是直接比较索引中的值。它更类似于搜索引擎做的事情。另外，在列上同时创建全文索引和B-Tree索引，不会有冲突

索引的优点

另外需要注意的是，索引的建立和使用需要一些空间和额外工作。所以，如果表本身很小且可控，全表扫描或许更快

高性能的索引策略（如何在SQL语句中使用索引）

独立的列

如果列本身是表达式的一部分，或是函数的参数，这种“不独立的列”无法使用此列索引。如：select * from tbl where id + 1 < 5

前缀索引

有时需要索引一个较长的字符列，这样会使索引变得大且慢，解决方案可以是之前说的哈希索引，但有时这样仍然不够好。这时候需要考虑使用前缀索引。

对于TEXT、BLOB和较长的VARCHAR，如果确实需要索引，则必须创建前缀索引，设置方法如下：

alter table tbl add key (city(7))

这样就设置了一个针对city字段前7个字符做索引的前缀索引。

前缀索引是一种能使索引更小、更快的有效方法。但缺点是不支持order by, group by和覆盖扫描

难点：如何确定一个较好的前缀值？(上面的例子是7)

-- 首先确定所有记录中，distinct的记录占比
select count(distinct city)/count(*) from tbl;

-- 假设上面得到的结果值为0.3，则表示城市平均重复2次左右
-- 为了前缀索引有良好的区分度，要求使用索引之后，占比较接近实际的0.3
select count(distinct left(city, 3))/count(*) as city3,
       count(distinct left(city, 4))/count(*) as city4,
       count(distinct left(city, 5))/count(*) as city5,
       ...
from tbl;
-- 最后取一个接近0.3，且前缀值不太大的一个值，作为最后的前缀值

多列索引

一个错误的索引设置方案：WHERE条件的列都建立一个单独的索引

示例： create table t (c1 int, c2 int, c3 int, KEY(c1), KEY(c2), KEY(c3));

导致的结果是在多条件查询的时候，无论怎么选择，都只能使用其中一个索引，而之后的条件则没有索引可以走

在新版本的MySQL中，查询会被优化成多条走索引的语句，然后联合或相交得出结果。但这样做相当于多次查询，明显不是最优的索引策略

应该采用的方式是，根据查询，设置组合索引

选择合适的索引列顺序

我们已知查询的顺序会影响到索引列顺序的设置

针对第三个情况的例子：

假设有一张表有customer_id和staff_id，这两列经常作为条件用于筛选。此时可以通过实际情况或SQL进行查询，不同的值更多的列可以考虑放在前面

select
count(distinct staff_id)/count(*) as staff_id_selectivity,
count(distinct customer_id)/count(*) as customer_id_selectivity,
count(*)
from payment;

-- res:
-- staff_id_selectivity: 0.0001, customer_id_selectivity: 0.0373
-- 此时应该选择 KEY(customer_id, staff_id)

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。对于InnoDB来说，聚簇索引实际上是同一个结构中保存了B-Tree索引和数据行

一张表只能有一个聚簇索引，因为一份数据只能保存在一个地方。在InnoDB中，不能直接选择某一列成为聚簇索引，而是直接通过主键作为聚簇索引

聚簇索引的优点：

聚簇索引的缺点：

对于InnoDB引擎的表来说

所以通常情况下，保证InnoDB的主键是顺序插入的，减少不必要的调整和页分裂，性能更优

在高并发的时候，顺序的主键可能会造成争用，这种情况下不使用顺序的主键

覆盖索引

如果一个索引包含（覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”

好处：索引中已经包含了全部数据，不需要回表查询。通常来说，索引没有太多多余的数据，规模小，所以查起来速度很快（MySQL中只有B-Tree 索引可以做覆盖索引）

当发起一个覆盖索引的查询时，在EXPLAIN的Extra列可以看到“Using index”的信息

一则简单明了的例子：

有一张products表，有主键prod_id，以及关联其他表的的两个id：aid、bid，这两个id形成一个key(aid, bid)，然后还有其他的一些产品信息

select aid, bid from products;

上述语句就是最简单的覆盖索引查询，涉及的列只有索引列

复杂一些的例子：

products表中有一个key(actor, title)，但是现在要查出满足条件的所有数据项

select * from products where actor='abc' and title like '%abc%';

上述语句并不能覆盖索引。且title前面也有%，故最终只能使用actor索引数据，然后回表查满足条件的title，再返回整行数据

解决方案：

建立索引key(actor, title, prod_id)

select * from products join (
  select prod_id from products where actor='abc' and title like '%abc%'
) t1 on (t1.prod_id = products.prod_id);

上述语句的子查询满足覆盖索引的查询（一个索引包含所有需要查询的字段的值）。另外，where条件也可以匹配最左前缀actor

如果在上述子查询中，过滤掉了大多数的不符合条件的products，且本身此表的规模有一定大小，则优化效果会很好，否则可能适得其反

示例：

  1. abc这个actor的条目有30000条，其中20000条title包含有abc => 这个属于规模大、过滤少的，效果不好
  2. abc这个actor的条目有30000条，其中40条title包含有abc => 这个属于规模大、过滤多的，效果很好
  3. abc这个actor的条目有50条，其中10条title包含有abc => 这个属于规模小，效果不好（子查询需要耗费一些性能）

另外的：

InnoDB引擎的二级索引的叶子节点是包含主键的值的，所以，主键是可以作为查询列覆盖索引的

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果：

当按索引顺序扫描排序时，在EXPLAIN的type列会显示index。这里注意区分之前的覆盖索引，覆盖索引是在Extra列显示“Using index”

扫描索引本身速度很快，但是如果不能覆盖查询的所有列，就不得不每扫描一条索引记录就回表查询一次对应的行，这样会造成随机I/O，这种情况是不如顺序地全表扫描的

所以，只有以下条件同时满足，引擎才会按索引顺序扫描：

有一种情况可以不满足最左前缀，就是前导列为常量的时候。即假设有KEY(a,b,c)，WHERE或JOIN子句对a已经指定了定值，则ORDER BY可以只针对b和c字段

MySQL可以使用同一个索引满足排序和查找行的任务，如果可能，尽量设计这样的索引

例子：假设有KEY(rental_date, inventory_id, customer_id)

以下方式可以实现索引扫描：

以下方式不能实现索引扫描：

压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引放入内存

特点：

重复索引、冗余索引

MySQL允许在相同列创建多个索引，并需要维护重复的索引，在优化器优化查询时，逐个考虑（这种情况是影响性能的。有些没有意义，需要去掉，但有时候是需要的）

重复索引：在相同的列按照相同的顺序创建相同类型的索引。这种是必须去掉的

错误示范：

create table t(
  ID int NOT NULL PRIMARY KEY,
  A  int NOT NULL,
  UNIQUE(ID),
  INDEX(ID)
) ENGINE=InnoDB;

上面三种索引（主键、唯一、普通索引）是同类型的B-Tree 索引，必须去掉（如果索引分别是INDEX和FULLTEXT，它们属于不同类型的索引，也不是重复索引，是完全没问题的）

冗余索引：如果已有KEY(A, B)，这时再创建一个KEY(A)，由于前者的左前缀包含A，所以后者是冗余索引。这种需要按实际情况分析

另外，在InnoDB中，主键默认已经包含在二级索引中，所以 KEY(A)
和 KEY(A, ID) 如果同时存在，也属于冗余索引

大多数情况，冗余索引是不需要的，应该尽量扩展已有的索引而不是建立新的索引。但如果扩展索引会导致索引太大，影响原有的查询，这时就需要考虑冗余索引了

例子：如果在整数列上有一个索引，现在需要额外加一个长的VARCHAR列来扩展索引

假设原来有以下需求，记作Q1。对应有一个索引KEY(state_id)

select count(*) from userinfo where state_id = 5;

现在有另外的新需求，记作Q2。如果引入覆盖索引，对应的索引是KEY(state_id, city, address)

select city, address from userinfo where state_id = 5;

由于MyISAM前缀索引的原因，如果采用直接扩展的方式，性能下降很严重；InnoDB影响不太大。书上例子的测试结果：

只有state_id 只有扩展索引 同时有state_id和扩展索引
MyISAM, Q1 114.96 25.40 112.19
MyISAM, Q2 9.97 16.34 16.37
InnoDB, Q1 108.55 100.33 107.97
InnoDB, Q2 12.12 28.04 28.06

有两个索引的缺点就是，索引成本高，增删改记录的效率变低。书上例子的测试结果：

插入100W行数据（单位：秒） 只有state_id 同时有state_id和扩展索引
InnoDB 80 136
MyISAM（压缩索引） 72 470

在决定删除“认为无用”的冗余索引时，需要检查是否有采用到主键作为二级索引的查询，如... where A = 1 order by id。如果有，当去掉
KEY(A) ，只保留 KEY(A, B)，会导致此查询不能用索引扫描排序

可以使用一些工具检测重复和冗余的索引

未使用的索引

可能会有服务器永远不用的索引，这种也建议删除。同样可以通过工具检测出

上一篇下一篇

猜你喜欢

热点阅读