MySQL索引和优化

最新推荐文章于 2024-08-03 21:25:25 发布

JavaBigData1024

最新推荐文章于 2024-08-03 21:25:25 发布

阅读量727

点赞数

分类专栏： MySQL 文章标签： mysql 索引优化执行计划

本文链接：https://blog.csdn.net/weixin_44516305/article/details/126737501

版权

MySQL索引和优化

1 索引
2 优化
3 执行计划

1 索引

1.1 索引算法

1.1.1 顺序查找

如果要在一组数据中找到对应的记录，通常是一个一个地扫描，直到找到对应的记录。

1.1.2 二分查找

二分查找是将记录顺序排列，查找时先将序列的中间元素作为比较对象。如果要找的元素的值小于该中间元素的值，那么只需要在前一半元素中继续查找；如果要找的元素的值等于该中间元素的值，则匹配成功，查询完成；如果要找的元素的值大于该中间元素的值，那么只需要在后一半元素中继续查找。

1.1.3 二叉查找树

二叉查找树是将一组无序的数据构造成一查有序的树，其设计思想与二分查找的设计思想类似。二叉查找树有如下几个重要的特性：

每个节点最多有两个子节点。
每个节点都大于自已的左子节点。
每个节点都小于自己的右子节点。

1.1.4 平衡二叉树

平衡二叉树是二叉查找树的改进版本，除了要满足二叉查找树的定义，还必须满足任意节点的平衡因子（两棵子树的高度差）的绝对值最大为1。

1.1.5 B树

B树可以理解为平衡二叉树的拓展，也是一棵平衡树，但是是多叉的。也可以把B树看成1个节点可以拥有多于2个子节点的多叉查找树。B树有如下几个特点：

B树的每个节点存储的都是数据。
B树的查询效率与键在B树的位置有关，最大时间复杂度与B+树的相同（数据在叶子节点上），最小的时间复杂度为1（数据在非叶子节点上）

1.1.6 B+树

B+树是B树的变体，其定义与B树的定义基本一致，与B树相比，B+树的具有以下的不同点

B+树的键都出现在叶子节点上，可能在非叶子节点上重复出现。
B+树的非叶子节点存储的都是键值，叶子节点才存储键值对应的数据。
B+树的非叶子节点不存储数据，所以B+树比B树占的空间更多，但是B+树的非叶子节点具有索引的作用，所以B+树的查询效率比B树的查询效率更高。

1.2 索引类型

1.2.1 B+树索引

InnoDB引擎默认使用B+树索引，B+树索引是基于B+树发展起来的，通常在InnoDB上对某个字段添加索引，就是对这个字段构建一查B+树。

B+树索引，所有的数据都保存在叶子节点上，且叶子叶节是有序的，B+树索引使用指针把相临的叶子节点连接起来，最终所有的叶子节点形成了一个环形的双向链表，从而支持范围查找。

B+树索引，所有的数据都保存在叶子节点上，所以B+树索引的查询效率很稳定，所有数据的查询效率都是一样的。

B+树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。

B+树索引支持以下类型的查找：

全值匹配：全值匹配指的是和索引中的所有列进行匹配。
匹配最左前缀：
匹配列前缀：也可以只匹配某一列的值的开头部分。
匹配范围值：
精准匹配某一列并范围匹配另外一列：

B+树索引也有如下的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。
不能跳过索引中的列。
如果查询中有某个列的范围查询，则期右边所有列都无法使用索引优化查找。

1.2.2 哈希索引

Memory引擎默认使用哈希索引，哈希索引是基于哈希表实现的，只有精确匹配索引所有的列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码（hash code），哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

哈希索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用列的全部内容来计算哈希值。
哈希索引只支持等值比较查询，包括=、IN()、<>，也不支持任何范围查询。
访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突时，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。
如果哈希冲突很多的话，一些索引维护操作的代码也会很高。

1.3 索引策略

1.3.1 聚簇索引

聚簇索引一般是指主键索引，也称为一级索引，聚簇索引的叶子节点存储的是完整的数据行。

InnoDB通过主键聚簇数据，如果没有定义主键，那么InnoDB会选择第一个非空的唯一索引代替，如果没有非空的唯一索引，那么InnoDB会隐式定义一个主键来作为聚簇索引。

聚簇索引占用的空间最大，因为它保存了全部数据。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

1.3.2 辅助索引

辅助索引一般是指非主键索引，也称为二级索引，辅助索引的叶子节点存储的是索引字段的值和主键ID。

在使用二级索引时，因为它只存储了索引字段的值和主键ID，所以需要查询其它列的数据时，就需要先通过二级索引中的值找到对应的主键，再通过主键找到聚簇索引中的其它列的数据，这个过程称为回表。

为了减少回表次数，可以将语句中经常使用到的所有列以合适的顺序建议一个二级联合索引，这样所有需要的列都被这个二级联合索引覆盖，就不需要回表。

1.3.3 唯一索引

唯一索引是一个不包含重复值的二级索引，一般是指基于唯一键创建的索引。

1.3.4 单列索引

单列索引是指基于单列创建的索引

1.3.5 多列索引

多列索引是指基于多列创建的索引，又称为联合索引

1.3.6 覆盖索引

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

通过覆盖索引使得查询只需要扫描索引无须回表，能够极大地提高性能。覆盖索引的好处如下：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于IO密集型的应用也是有帮助的，因为索引比数据更小，更容易全部放入内存中。
因为索引是按照列值顺序存储的，所以对于IO密集型的范围查询会比随机从磁般读取一行数据的IO要少得多。
由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对聚簇索引的二次查询。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B+树索引做覆盖索引。

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

1.4 索引操作

1.4.1 查看索引

可以通过以下SQL查看数据表上创建了哪些索引：

-- 查看表t_order上创建的索引
show index from t_order;

在执行SQL时，可以在SQL前面加上explain关键字查看SQL语句在执行的时候是否使用索引，以及使用了哪些索引，示例如下：

-- 查看用户10001的所有订单
explain select * from t_order where user_id = '10001';

1.4.2 创建索引

-- 基于create语句创建唯一索引和普通索引
create unique index `uk_order_id` on t_order(`order_id`);
create index `idx_user_id_created_at` on t_order(`user_id`, `created_at`);

-- 基于alter语句创建唯一索引和普通索引
alter table t_order add unique index `uk_order_id`(`order_id`);
alter table t_order add index `idx_user_id_created_at`(`user_id`, `created_at`);

1.4.3 删除索引

-- 基于drop语句删除索引
drop index `uk_order_id` on t_order;

-- 基于alter语句删除索引
alter table t_order drop index `idx_user_id_created_at`;

1.5 索引和锁

InnoDB在二级索引上使用共享（读）锁，在主键索引上使用排他（写）锁。这消除了使用覆盖索引的可能性，并且使用select for update比lock in share mode或非锁定查询要慢很多。

索引可以让查询锁定更少的行。如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销。其次，锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层面能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句，这时已经无法避免锁定行了。

2 优化

2.1 参数调优

2.1.1 参数加载顺序

MySQL的配置文件名称为my.cnf，在单实例支行模式下，配置文件my.cnf的加载顺序为：

第一：/etc/my.cnf
第二：/etc/mysql/my.cnf
第三：SYSCONFDIR/my.cnf
第四：basedir/my.cnf

其中，SYSCONFDIR表示在安装源码时指定的配置文件的选项，如果没有指定就不加载。在启动MySQL服务的过程中，先按照顺序扫描这些位置的my.cnf文件，然后逐个加载这些配置文件，后面的配置文件会覆盖前面的配置文件的配置项。因此，配置完MySQL之后，应该检查这些地方的配置文件，最好只保留/etc/my.cnf这一个位置的配置文件，否则容易导致配置文件的修改失效。

2.1.2 常用参数调优

2.1.2.1 innodb_buffer_pool_size

由于MySQL中的InnoDB缓冲池主要存放InnoDB的数据缓存页面、索引缓存页面、自适应哈希索引、change buffer、数据字典、join buffer、查询缓存数据等，因此，innodb_buffer_pool_size参数对整个MySQL的性能具有非常重要的影响。

MySQL可以在线动态调整innodb_buffer_pool_size参数的大小，使用innodb_buffer_pool_resize_status参数可以查看调整innodb_buffer_pool_size参数的进度和状态。

show status like 'innodb_buffer_pool_resize_status;

set global innodb_buffer_pool_size = 4*1024*1024*1024;

针对单个实例来说，innodb_buffer_pool_size参数分配的内存占总实例内存的70%到80%为宜。调整innodb_buffer_pool_size参数值时必须遵循以下这种倍数关系，如果不满足下面的这种倍数关系，则MySQL会自适应地调整数倍数关系：

-- N为正整数
innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances * N

其中，innodb_buffer_pool_chunk_size 参数的默认值是128MB。当innodb_buffer_pool_instances参数的值大于1时，innodb_buffer_pool_size参数的值必须大于1GB。

通过以下公式计算innodb_buffer_pool_size参数的值设置得是否合理：

performance_read = innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads + innodb_buffer_pool_read_requests) * 100%

其中，innodb_buffer_pool_read_requests参数表示从InnoDB缓冲池中读取的请求数，innodb_buffer_pool_reads参数表示未命中InnoDB缓冲池时从磁盘读取的请求数。如果performance_read < 90%，则可以考虑增加innodb_buffer_pool_size参数的值。