/ db

mysql-index

索引

假设用户有时会按会员的姓氏来检索 humans 表。如果没有索引,MySQL 就需要一行行去搜索。我们可以通过在 SELECT 前添加 EXPLAIN 来确认是否是这样。

EXPLAIN SELECT * FROM birdwatchers.humans
WHERE name_last = '刘' \G
EXPLAIN SELECT * FROM humans WHERE name_last='刘' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: humans
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)
  • type :使用的 JOIN 类型
  • possible_keys:如果有包含主键的索引,可用字段的列表。识别到的索引。
  • key:被优化器选中的主键。实际使用的索引。
  • key_ken:主键(或部分主键)的长度。
  • ref:约束或需要对比的字段。
  • rows:估计要处理的行数。
  • extra:优化器的额外信息。

如果 type 字段为 ALL,那就是在做全表扫描,应该尽量避免,方法是添加索引,或重写查询。类似地,如果 type 字段为 INDEX,则执行全索引扫描,这是非常低效的。

新建索引

在这个例子中,name_last 是没有索引的。因为表中只有几行数据,所以有没有索引都不会带来明显的性能区别。然而,如果有一天表中有了数千行数据,那么索引将大大提示查找人名的效率。

有时,用户不只会按姓来查询 humans 表,他们还会按名字来查,甚至姓名一切查。为了对这些可能性有准备,以及提升数据量上升后的查询性能,我们在这两列上建立索引。

ALTER TABLE humans ADD INDEX human_names (name_last, name_first);
EXPLAIN SELECT * FROM humans WHERE name_last='刘' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: humans
         type: ref
possible_keys: human_names
          key: human_names
      key_len: 103
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

EXPLAIN SELECT * FROM humans WHERE name_last='刘' and name_first='星' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: humans
         type: ref
possible_keys: human_names
          key: human_names
      key_len: 206
          ref: const,const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

索引的作用就是让 MySQL 能快速地定位数据。索引就像一本书的目录。如果没有目录,你想在书中查找一些内容就要从头开始一页一页地查找。

因为索引的体积更小,而且已经组织好以便快速遍历,所以 MySQL 可以通过它快速定位数据,然后直接跳到对应的行的位置,这样数据库会跑得更快。

需要注意的是,索引不是列,尽管它与列有关。查看下索引:

SHOW INDEX FROM birdwachters.human \G

注意其中的 Column_name: human_id,这里的 human_id 不是索引,而是索引的根源。它的名字与索引的名字相同,而且索引也与这一列绑定,但它们绝不等同!

联合索引

chrome_2018-05-16_13-01-55

Q:如何区分字段的离散度呢?
A:通过对类做统计。

SELECT COUNT(DISTINCT customer_id), COUNT(DISTINCT staff_id)
FROM payment;

输出:

+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                       54 |
+-----------------------------+--------------------------+
1 row in set (0.00 sec)

索引的维护和优化

过多的索引,不仅会影响 UPDATE DELETE 也同样会影响 SELECT 性能。所以需要定期查看数据表的索引,删除掉重复及冗余的索引

SHOW INDEX FROM user;

或者:

SHOW CREATE TABLE user \G

例如删除不需要的索引password_reset_token

ALTER TABLE user
DROP INDEX password_reset_token;

索引重命名

如果需要重命名一个被索引了的列,正确步骤应该是:

  1. 先删除掉索引;
  2. 再改列名;
  3. 最后,基于新的列名来建立索引;

例如,将 conservation_status 表的主键 status_id 改名为 conservation_status_id 的语句:

ALTER TABLE conservation_status
DROP PRIMARY KEY,
CHANGE status_id conservation_status_id INI PRIMARY KEY AUTO_INCREMENT;

深入细节

MySQL 中的所有数据都是以文件的形式存储在磁盘上的,而从磁盘上随机访问对应的数据非常耗时,所以数据库程序和操作系统提供了**缓冲池(Database BufferPool)**和内存以提高数据的访问速度。

数据库对数据的读取是以为单位的,并不是行。无论是读取一行还是多行都会将改行或者多行所在的页加载进来,然后再读取对应的数据记录。换句话说,读取的耗时与页数有关,与行数无关。索引或行记录是否在缓存池中极大地影响了访问索引或者数据的成本。

索引的优点

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。

创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

  1. 索引大大减少了需要扫描的数据量。
  2. 索引可以避免排序和临时表。
  3. 索引可以将随机 I/O 变为顺序 I/O。

为 SELECT 语句设计理想的索引

SELECT A, B, D, E  
  FROM ORDERITEM  
 WHERE B BETWEEN :B1 AND :B2   -- (FF = 1...10%)  
   AND C = 1                   -- (FF = 2%)  
   AND E > 0                   -- (FF = 50%)  
   AND F = :F                  -- (FF = 0.1...1%)  
 ORDER BY A, B, C, F  
-- WE WANT 20 ROWS PLEASE  

分析:WHERE子句中有四个谓词条件,分别是:

  1. 一个范围绑定变量谓词B BETWEEN :B1 AND :B2
  2. 一个等值常量谓词C = 1,
  3. 一个范围常量谓词E > 0,
  4. 一个等值绑定变量谓词F = :F。

四个谓词条件的过滤因子分别是FF = 1...10%、FF = 2%、FF = 50%、FF = 0.1...1%。

每次结果集有20条,按照A、B、C、F排序。

范围谓词字段E没有出现在排序中,而排序中出现的字段A没有出现在WHERE子句的谓词中。根据三星索引理论,不可能创建理想的三星索引(第一颗星第二颗星不可兼得),所以候选索引A为第一星+第三星的索引,候选索引B为第二星+第三星的索引。

三颗星索引

《数据库索引设计与优化》 第四章

  1. 如果与一个查询相关的索引行是相邻的,或者至少足够靠近的话,那这个索引就可以被标记上第一颗星。这最小化了必须扫描的索引片的宽度。
  2. 如果索引行的顺序与查询语句的需求一致,则索引可以被标记上第二颗星。这避免了排序操作。
  3. 如果索引行包含查询语句中的所有列,那么索引就可以被标记上第三颗星。这避免了访问表的操作,仅访问索引就可以了。

我们总可以通过增加索引中的列以获得第三颗星。

对于这三颗星,第三颗通常是最重要的。

  • 最小化索引片,例如将 WHERE 中等值谓词(=)的列加入索引以减少需要扫描的行数。
  • 避免排序
  • 避免随机 IO

有索引的列在查询时通过 = 来定义

术语:

  • 过滤因子(Filter Factor)即表中满足谓词条件的记录行数所占全部行数的比例。
  • 等值谓词 =
  • 范围谓词 BETWEEN AND

索引的设计

在设计单表索引时,首先把查询中所有等值谓词全部取出以任意顺序放在索引最前面,在这时,如果索引中同时存在范围索引ORDER BY 的话,就需要权衡取舍:

  • 如果希望最小化扫描的索引片厚度时,应该将过滤因子最小的范围索引列加入索引;
  • 如果希望避免排序就选择ORDER BY 中的全部列,在这之后就只需要将查询中剩余的全部列加入索引了;

通过这种固定的方法和逻辑就可以最快地获得一个查询语句的二星或者三星索引了。

索引的缺点

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

相关文章:

MySQL 索引设计概要
《数据库索引设计与优化》 2015 版