开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL高性能索引策略 -> 正文阅读

[大数据]MySQL高性能索引策略

文章目录

高性能索引策略

高性能索引策略

正确地创建和使用索引是实现高性能查询的基础。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。

独立的列

我们通常会看到一些查询不当地使用索引，或者使得 MySQL 无法使用已有的索引。如果查询的列不是独立的，则 MySQL 就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面查询无法使用索引

select actor_id from where sakila.actor where actor_id + 1 =5

前缀索引

有时候需要索引很长的字符列，这会让所有编的大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率，但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称基数 cardinality）和数据表的记录总数（# T）的比值，范围从 1/# 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于 blob 、text 或者很长的 varchar 类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度

例如现在有一个地区表 sys_area

| area | code |
|–|–|–|
| 上海市 | 1 |
| 北京市 | 2 |
| 张家口市 | 3 |
| … | n |

SELECT * FROM sys_area WHERE name = '北京市'

创建前缀索引

alter table sys_area add index(name(2))

在这里插入图片描述

前缀索引是一种能使索引更小、更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY 和GROUP BY ，也无法使用前缀索引做覆盖扫描。

多列索引

在我们开发中一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引

CREATE TABLE `test`  (
  `c1` int NULL DEFAULT NULL,
  `c2` int NULL DEFAULT NULL,
  `c3` int NULL DEFAULT NULL,
  INDEX `idx_c1`(`c1`) USING BTREE,
  INDEX `idx_c2`(`c2`) USING BTREE,
  INDEX `idx_c3`(`c3`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

这种索引策略，一般都是把 where 条件里面的列都建立索引，这样以来最好的情况也只能使用1个索引。

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL 的查询性能。MySQL 5.0和更新版本引入了一种叫“索引合并”（index merge）的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的MySQL只能使用其中某一个单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的

在 MySQL 5.0更新版本中，查询能够同时使用这两个单列索引进行扫描，并将结果进行合并，下面的查询就是使用了两个索引扫描的联合，通过 EXPLAIN 的 Extra 可以看出

mysql> EXPLAIN SELECT *  FROM test WHERE  c1 = 1 or c2 = 2;

***********************************************************

+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key           | key_len | ref  | rows | filtered | Extra                                   |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
|  1 | SIMPLE      | test  | NULL       | index_merge | idx_c1,idx_c2 | idx_c1,idx_c2 | 5,5     | NULL |    2 |   100.00 | Using union(idx_c1,idx_c2); Using where |
+----+-------------+-------+------------+-------------+---------------+---------------+---------+------+------+----------+-----------------------------------------+
1 row in set (0.03 sec)

MySQL 会使用这类技术优化复杂查询，所以在某些语句的 Extra 列中还可以看到嵌套操作。

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

当出现服务器多个索引做相交操作时（and）通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当服务器需要对多个索引做联合操作（or），通常需要耗费大量的cpu和内存资源在算法的缓冲，排序和合并的操作上。特别是当其中有些索引的选择性不高。需要合并扫描返回大量数据的时候
更重要的是，优化器不会把这些计算到 “查询成本” （cost）中，优化器只关心随机页面读取。这会使得查询的成本被 “低估” ，导致该执行计划还不如全表扫描，这样不但会消耗更多的 cpu 和内存资源，还可能会影响查询的并发性，但如果是单独允许这样的查询则往往会忽略堆并发性的影响。

如果在 Explain 语句中看到索引合并，应该好好检查一下查询和表的结构。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE_INDEX提示让优化器忽略掉某些索引。

选择合适的索引列顺序

对于如何选择索引的列顺序有个经验： 将选择性最高的列放在索引最前列

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

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分

Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

在这里插入图片描述

聚簇索引的优点：

可以把相关数据保存在一起。例如实现电子邮件时，可以根据用户 ID 来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户全部邮件。如果没有使用聚簇索引，可能每封邮件都可能导致一次磁盘I/O
数据访问更快。因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
使用覆盖索引扫描的查询可以直接使用页节点的主键值

缺点：

聚簇数据最大限度地提高了 I/O 密集型应用性能，但如果数据全部放在内存中，则访问顺序就没那么重要了，优势就不存在了
插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
更新主键的代价很高，因为将会导致被更新的行移动
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏的时候，由于页分裂导致数据存储不连续的时候
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

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

好处：

索引条目通常远小于数据行大小，所有如果只需要读取索引，那么MySQL 就会极大地减少数据访问量
索引按照列值存储的，所以对于 I/O 密集型的范围查询比随机从磁盘读取每一行数据少的多。
由于InnoDB的聚簇索引，覆盖索引对 InnoDB 表特别有用。

如何实现覆盖索引？

在explain查询计划优化章节，即explain的输出结果Extra字段为Using index时，能够触发索引覆盖

mysql> EXPLAIN SELECT c1 FROM test WHERE  c1 = 1 ;

*************************************************

+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_c1        | idx_c1 | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set (0.07 sec)

在select返回列较少或列宽较小的时候，我们可以通过建立复合索引来实现覆盖索引

mysql> EXPLAIN SELECT c1,c2 FROM test WHERE  c1 = 1 ;


*************************************************

+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_union        | idx_union | 5       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------------+
1 row in set (0.05 sec)

使用索引扫描来做排序

mysql有两种方式可以生成有序的结果：通过排序操作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢

mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。

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

mysql> EXPLAIN SELECT c1,c2 FROM test  order by c1,c2 desc;


*************************************************


+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | test  | NULL       | index | NULL          | idx_c1 | 10      | NULL |    8 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------+
1 row in set (0.05 sec)