MySQL 索引优化

作者: 杨仕杰 | 来源:发表于2018-08-14 17:40 被阅读0次

一. 最左前缀原理

以 MySQL 官方示例数据库 employees 的 titles 表的 <emp_no, from_date> 主键索引来进行说明。

(一). 全值匹配

当按照索引中所有列进行精确匹配 ("=" 或 "IN" 匹配) 时，索引可以被用到。理论上索引对顺序是敏感的，但是 MySQL 查询优化器会自动调整 WHERE 子句的条件顺序以使用适合的索引。

EXPLAIN SELECT * 
FROM employees.titles 
WHERE emp_no='10001' AND title='Senior Engineer' AND from_date='1986-06-26'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 59
          ref: const,const,const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

(二). 最左前缀匹配

当查询条件精确匹配索引的左边连续一个或几个列时，索引可以被用到，但是只能用到一部分，即条件所组成的最左前缀。

EXPLAIN SELECT * FROM employees.titles WHERE emp_no='10001'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
1 row in set (0.00 sec)

(三). 全值匹配，但是中间某个条件未提供

此时索引使用情况和 "最左前缀匹配" 相同，因为 title 未提供，所以查询只用到了索引的第一列，而后面的 from_date 虽然也在索引中，但是由于 title 不存在而无法和左前缀连接，因此需要对结果进行扫描过滤 from_date。

EXPLAIN SELECT * 
FROM employees.titles WHERE emp_no='10001' AND from_date='1986-06-26'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec

如果想让 from_date 列也使用索引而不是使用 WHERE 过滤，可以增加一个辅助索引 <emp_no, from_date>。除此之外，还可以使用一种被称之为 "隔离列" 的优化方法，将 emp_no 与 from_date 之间的 "坑" 填上。

SELECT DISTINCT(title) FROM employees.titles;
+--------------------+
| title              |
+--------------------+
| Senior Engineer    |
| Staff              |
| Engineer           |
| Senior Staff       |
| Assistant Engineer |
| Technique Leader   |
| Manager            |
+--------------------+
7 rows in set (0.20 sec)

EXPLAIN SELECT * 
FROM employees.titles 
WHERE emp_no='10001' 
  AND title IN (
    'Senior Engineer', 
    'Staff', 
    'Engineer', 
    'Senior Staff', 
    'Assistant Engineer', 
    'Technique Leader', 
    'Manager') 
  AND from_date='1986-06-26'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 59
          ref: NULL
         rows: 7
        Extra: Using where
1 row in set (0.00 sec)

SHOW PROFILES\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.00032611
   Query: SELECT * FROM employees.titles 
          WHERE emp_no='10001' AND from_date='1986-06-26'
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00031092
   Query: SELECT * FROM employees.titles
          WHERE emp_no='10001'
            AND title IN (
                'Senior Engineer', 
                'Staff', 
                'Engineer', 
                'Senior Staff', 
                'Assistant Engineer', 
                'Technique Leader',
                 'Manager')
            AND from_date='1986-06-26'
2 rows in set (0.00 sec)

但只有在这种成为 "坑" 的列值比较少的情况下，才可以考虑用 "IN"来填补这个 "坑" 从而形成最左前缀。

(四). 匹配某列的前缀字符串

此时可以用到索引。如果如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀。

EXPLAIN SELECT * 
FROM employees.titles WHERE emp_no='10001' AND title LIKE 'Senior%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 56
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

(五). 范围查询

范围列可以用到索引 (必须是最左前缀)，但是范围列后面的列无法用到索引。索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

EXPLAIN SELECT * 
FROM employees.titles WHERE emp_no < '10010' and title='Senior Engineer'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 14
        Extra: Using where
1 row in set (0.00 sec)

MySQL 有一个非常有意思的地方：仅用 EXPLAIN 可能无法区分范围索引和多值匹配，因为在 type 中这两者都显示为 range。同时用了 "between" 并不意味着就是范围查询。

下面的查询中，看起来是用了两个范围查询，但作用于 emp_no 上的 "BETWEEN" 实际上相当于 "IN"，也就是说 emp_no 实际是多值精确匹配。可以看到这个查询用到了索引全部三个列。因此在 MySQL 中要谨慎地区分多值匹配和范围匹配，否则会对 MySQ L的行为产生困惑。

EXPLAIN SELECT * 
FROM employees.titles
WHERE emp_no BETWEEN '10001' AND '10010'
AND title='Senior Engineer'
AND from_date BETWEEN '1986-01-01' AND '1986-12-31'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 59
          ref: NULL
         rows: 15
        Extra: Using where
1 row in set (0.00 sec)

(六). 查询条件中含有函数或表达式

如果查询条件中含有函数或表达式，则 MySQL 不会为这列使用索引。

EXPLAIN SELECT * 
FROM employees.titles WHERE emp_no='10001' AND left(title, 6)='Senior'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: titles
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

二. 索引选择性

索引虽然加快了查询速度，但也是有代价的。索引文件本身要消耗存储空间，同时索引会增加插入、删除和修改记录时的开销。另外 MySQL 在运行时也要消耗资源维护索引，因此索引并不是越多越好。

一般 3 种情况下不建议使用索引：

第一种情况是表记录比较少。没必要建索引，让查询做全表扫描就好。根据记录数不超过 2000 可以考虑不建索引，超过 2000 条可以酌情考虑索引
第二种情况是表记录过多，建立和使用索引的代价将会增加。这种情况下，需要一种技术可以直接区分出查询需要的一组数据，而不是一条一条记录地匹配。可以使用分区技术
最后一种情况是索引的选择性较低。所谓索引的选择性是指不重复的索引值与表记录数的比值。显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的

三. 前缀索引

前缀索引是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。

对于 BLOB、TEXT 或者很长的 VARCHAR 的列，必须使用前缀索引。MySQL 不允许索引这些列的完整长度。

前缀索引兼顾索引大小和查询速度，缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于索引覆盖扫描 (即当索引本身包含查询所需全部数据时，不再访问数据文件本身)。

EXPLAIN SELECT * 
FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 300252
        Extra: Using where

SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
1 row in set (0.13 sec)

SELECT count(DISTINCT(last_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0055 |
+-------------+

SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity 
FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+
1 row in set (0.36 sec)

SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) 
  AS Selectivity 
FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+
1 row in set (0.36 sec)

ALTER TABLE employees.employees 
  ADD INDEX `first_name_last_name4` (first_name, last_name(4));

EXPLAIN SELECT * 
FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
         type: ref
possible_keys: first_name_last_name4
          key: first_name_last_name4
      key_len: 22
          ref: const,const
         rows: 1
        Extra: Using index condition; Using where
1 row in set (0.00 sec)

SHOW PROFILES\G
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.13175585
   Query: SELECT * 
          FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'
*************************** 15. row ***************************
Query_ID: 2
Duration: 0.00034243
   Query: SELECT * 
          FROM employees.employees WHERE first_name='Eric' AND last_name='Anido'

四. 覆盖索引

如果一个索引包含 (或者覆盖) 所有要查询的字段的值，就称其为 "覆盖索引"。当发起一个被索引覆盖的查询时，在 EXPLAIN 的 Extra 列可以看到 "Using index" 的信息。

覆盖索引具有如下优点：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么 MySQL 就会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花在数据的拷贝上。覆盖索引对 I/O 密集型的应用同样有帮助，因为索引比数据更小，更容易全部放在内存中
因为索引是按照列值顺序存储的 (至少在单页内是如此)，所以对于 I/O 密集型的范围查询会比随机从磁盘读取每一行数据的 I/O 要少得多。对于某些存储引擎，例如 MyISAM，甚至可以通过 OPTIMIZE 命令使得索引完全顺序排列，这让简单的范围查询能使用完全顺序的索引访问
一些存储引擎如 MyISAM 在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调用占用数据访问中的最大开销的场景
由于 InnoDB 的聚簇索引，覆盖索引对 InnoDB 表特别有用。InnoDB 的二级索引在叶子节点保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询

索引覆盖查询有很多陷阱可能导致无法实现优化。MySQL 查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设所有覆盖了 WHERE 条件中的字段，但不是整个查询设计的字段。如果条件为假，MySQL 5.5 和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过可以更进一步优化 InnoDB。InnoDB 的二级索引的叶子节点都包含了主键的值，这意味着 InnoDB 的二级索可以有效的利用这些 "额外" 的主键列来覆盖查询。

五. 多列索引

在多个列上建立独立的单列索引在大部分情况下并不能提高 MySQL 的查询性能。MySQL5.0 和更新版本引入了 "索引合并" 策略，一定程度上可以使用表上的多个单列索引来定位指定的行。查询能够同时使用多个单列索引进行扫描，并将结果进行合并。这种算法有三个变种：OR 条件的联合、AND 条件的相交、组合前两种情况的联合及相交。

索引合并策略有时候是一种优化，但更多的时候说明了表上的索引建的很糟糕：

当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引
当出现服务器对多个索引做联合操作时，通常需要耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候
优化器不会把这些计算到 "查询成本" 中，优化器只关心随机页面读取。这会使得查询成本被低估

如果在 EXPLAIN 中看到有索引合并，应该好好检查下查询和表的结构是不是已经是最优的。也可以通过参数 optimizer_switch 来关闭所有合并功能。同时也可以使用 IGNORE INDEX 提示让优化器忽略掉某些索引。

五. 索引顺序

索引列的正确顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。在一个包含多列的索引中，索引列的顺序意味着所有首先按照最左列进行排序，其次是第二列，等等。索引可以按照升序或降序进行扫描，以满足精确符合列顺序的 ORDER BY、GROUP BY 和 DISTINCT 等子句的查询要求。

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化 WHERE 条件的查询。在这种情况下，这样设计的索引确实能够最快地过滤出需要的行，对于在 WHERE 子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能并不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就是和值的分布有关。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

六. 索引排序

MySQL 有两种方式可以生成有序的结果：通过排序操作、通过索引顺序扫描。如果 EXPLAIN 的 type 列的值为 "index"，则说明 MySQL 使用了索引扫描来做排序。

扫描索引本身是很快的，因为只需从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不扫描一条索引记录都回表查询一次对应的行。这基本上都是随机 I/O 操作，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在 I/O 密集型的工作负载时。

只有当索引的列顺序和 ORDER BY 子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL 才能够使用索引来对结果进行排序。如果查询需要关联多张表，则只有当 ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY 子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL 都需要执行排序操作，而无法利用索引排序。

有一种情况下的 ORDER BY 子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果 WHERE 子句或者 JOIN 子句中对这些列指定了常量，就可以 "弥补" 索引的不足。

CREATE TABLE rental(
    PRIMARY KEY (rental_id),
    UNIQUE KEY rental_data (rental_data, inventory_id, customer_id)
);

SELECT rental_id, staff_id 
FROM rental
WHERE rental_data = '2005-05-25'
ORDER BY inventory_id, customer_id

六. 哈希索引

如果存储引擎不支持哈希索引，可以在 B-Tree 基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用 B-Tree 进行查找，但它使用哈希值而不是键值进行索引查找。需要做的就是在查询的 WHERE 子句中手动指定哈希函数。这样实现的缺陷是需要维护哈希值 (可以手动维护，也可以用触发器实现)。

-- 创建如下表
CREATE TABLE pseudohash(
    id int unsigned NOT NULL auto_increment,
    url varchar(255) NOT NULL,
    url_crc int unsigned NOT NULL DEFAULT 0,
    PRIMARY KEY(id)
);

-- 创建触发器 (先临时修改一下语句分隔符，这样可以在触发器定义中使用分号)
DELIMITRE //

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN
SET NEW.url_crc=crc32(NEW.url);
END;
//

DELIMITER ;

如果采用自定义哈希索引的方式，不要使用 SHA1() 和 MD5() 作为哈希函数。因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间。如果数据表非常大，CRC32() 会出现大量的哈希冲突，则可以考虑自己实现一个简单的 64 位哈希函数。这个自定义的函数要返回整数，而不是字符串。一个简单的办法可以使用 MD5() 函数返回值的一部分来作为自定义函数。

当使用哈希索引进行查询时，要避免哈希冲突的问题，必须在 WHERE 条件中带入哈希值和对应列值。如果不是想查询具体值，例如只是统计记录数则可以不带入列值，直接使用 CRC32() 的哈希值查询即可。

五. 主键选择

使用 InnoDB 存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。从数据库索引优化角度看，使用 InnoDB 引擎而不使用自增主键绝对是一个糟糕的主意。

InnoDB 使用聚簇索引，数据记录被存放于主键索引的叶子节点上。这要求同一个叶子节点内的各条数据记录按主键顺序存放。每当有一条新的记录插入时，MySQL 会根据其主键将其插入适当的节点和位置。如果页面达到装载因子 (默认为15/16)，则开辟一个新的页 (节点)。

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构，近似顺序填满。每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键，每次插入主键的值近似于随机，因此新增的纪录都要被插到现有索引页得中间某个位置。此时 MySQL 不得不为了将新记录插到合适位置而移动数据，甚至目标页可能已经被回写到磁盘上而从缓存中清掉。此时又要从磁盘上读回来，这增加了很多开销。同时频繁的移动、分页操作造成了大量的碎片，使得索引结构不够紧凑，不得不通过 OPTIMIZE TABLE 来重建表并优化填充页面。