MySQL索引(二)索引优化方案有哪些

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: MySQL索引(二)索引优化方案有哪些

在上一篇文章中,我们介绍了MySQL中常见的索引类型以及每种索引的各自特点,那么这篇文章带你来与我一起看一下聚集索引与二级索引的关系,最后在附上常见的索引优化方案。首先我们还是看一下聚集索引和二级索引的区别

MySQL索引(一)

聚集索引和二级索引区别

首先,每个Innodb引擎的表都有一个聚集索引,用于存储行数据,通常情况下,聚集索引也叫做主键索引。

  • 如果一个表定义了主键,Innodb就使用它作为聚集索引。因此我们要尽可能的为表添加一个主键,如果实在没有一个列是非空且唯一的可以作为主键列,建议添加一个自动递增的列作为主键列
  • 如果表没有主键,Innodb会选择第一个非空且唯一的列作为聚集索引
  • 如果表既没有主键,也没有非空且唯一的索引,则Innodb生成一个隐藏的聚集索引,GEN_CLUST_INDEX 包含rowid值的列,数据行根据rowid排序,rowid是一个6字节的字段,随着数据插入而单调递增,也就是说,数据行根据rowid排序也就是根据插入顺序排序的

在上面,我们知道了主键索引也就是聚集索引,而且我们的日常工作中,查询如果根据主键查询都是很快的,那么聚集索引是如何提升查询效率的呢?

聚集索引如何提升查询效率

通过聚集索引访问一条数据是很快的,这是因为所有的行数据和索引保存在同一个页上。如果表数据特别大,相较于数据和索引保存在不同的页上的存储结构相比,Innodb大大节省了磁盘IO操作

现在我们知道了聚集索引之所以查询的快是因为要查询的行数据和索引都保存在同一个页上,也就减少了去磁盘查找数据的过程,那么二级索引呢,二级索引是如何与聚集索引关联的呢?

二级索引如何与聚集索引关联

聚集索引之外的其它索引全部被称为二级索引。在Innodb中,二级索引中的每条记录都包含主键列以及本身二级索引指定的索引列,在聚集索引中,Innodb使用此主键值查询该行的数据

如果主键较长的话,那么我们二级索引保存主键列时就会占用更多的空间,所以主键尽可能的短是有利的

读到这,我们应该已经知道了,聚集索引中所有记录与索引都保存在同一个页中,所以这也是聚集索引查询快的原因。二级索引没有保存当前记录的数据,只保存了主键列,所以在使用二级索引的时候会涉及到两步操作,即根据二级索引先定位主键列,然后根据主键列在聚集索引中查询数据返回。现在我们也知道了,通过二级索引查找会涉及到多一次交互的问题,那么这个点也是我们后文将要讨论的一个点,也就是所谓的回表。目前我们常用的索引优化方式有覆盖索引、最左前缀、索引下推,现在我们一起来详细看下索引的优化方式是怎么工作的

常用的索引优化方式

覆盖索引

首先我们还是新建一张表t,在k列建立索引,建表语句如下

create table t(

 id int primary key,

 k int not null default 0,

 s varchar(16) not null default '',

 index k(k)

)engine=Innodb;

# 加入测试数据

insert into t values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

在上文中,我们已经知道了聚集索引与二级索引的区别,所以在执行完上面的插入语句之后,数据的存储结构为两棵索引树,一棵主键索引树包含数据,一棵二级索引k的索引树

此处借鉴一下极客时间丁奇老师的索引结构图,此处放个不同数据结构存储图,这个网站不错,可以模拟数据结构的存储过程,给大家推荐一下,这块网站模拟的和丁奇老师的还是不一样,这块原因还不了解,对这块还不是很熟悉,有了解的可以评论区说一下,互相学习一下。本文还是以丁奇老师讲解的图为主

首先我们执行一条查询语句

select * from t where k between 3 and 5;

执行过程是这样的,首先到k索引树取到值为3的到主键索引树获取主键等于300的R3记录返回,然后取k索引树下一值5的主键500到主键索引树取500的记录R5,然后取k索引树下一个值6的主键600到主键树查询,发现6不符合条件,不再去主键树查询,返回结果

通过上面的分析,那么什么是覆盖索引呢,很简单,我们看下面两条sql语句

1、explain select * from t where k between 3 and 5;

2、explain select id from t where k between 3 and 5;

两条语句的执行分析结果如下

两条sql唯一的区别就是第一条sql会获取所有的字段,而第二条sql只获取id这个字段,而k索引树上已经保存了id的值,所以此时也就不用回表查询了,这种方式就是覆盖索引。因为覆盖索引可以大大减少搜索树的次数,所以使用覆盖索引是常用的优化手段

下面我们在看另一个使用覆盖索引的例子首先还是创建一张用户表,建表语句如下

CREATE TABLE `tuser` (

 `id` int(11) NOT NULL,

 `id_card` varchar(32) DEFAULT NULL,

 `name` varchar(32) DEFAULT NULL,

 `age` int(11) DEFAULT NULL,

 `ismale` tinyint(1) DEFAULT NULL,

 PRIMARY KEY (`id`),

 KEY `id_card` (`id_card`),

 KEY `name_age` (`name`,`age`)

) ENGINE=InnoDB

# 加入数据

insert into tuser values(1,'123456789012345678','test1','18',1),(2,'123456879012345677','test2','19',1),(3,'123456987012345676','test3','20',1),(4,'123456986012345675','user1','21',0),(5,'123456985012345674','user2','22',1),(6,'123456787012345673','user3','23',1),(7,'123456788012345672','admin1','24',0);

现在我们在表上建立了两个索引,身份证上一个索引,姓名和年龄一个索引。如果我们根据身份证获取用户信息,那么一个身份证索引就可以满足了,但是要是有一个高频的请求,根据身份证获取用户姓名,那么我们可以再创建一个身份证和姓名的联合索引,这样这些所有的根据身份证获取用户名的请求都可以在身份证姓名的联合索引上使用覆盖索引,那么这个索引也就是有意义的。

explain select * from tuser where id_card = '123456789012345678';

下面是根据身份证索引查询信息的执行结果

借助name,age的联合索引我们来分析一下最左前缀和索引下推

最左前缀

在上面的测试数据中,如果我们有以下查询语句

1、explain select * from tuser where name like 'test%';

2、explain select * from tuser where name = 'test1';

第一条语句会在查询时获取name,age索引树上test开头的数据,是可以用到name_age的联合索引的(possible_keys),但是在这次查询中优化器没有选择使用索引(key是null)

第二条语句也是可以走name,age索引树的,而且选择走name_age的联合索引(possible_keys和key都是name_age)

 

通过上面两条sql语句,我们可以知道,不只是索引的全部定义,只要满足索引的最左前缀也是可以加速访问的。这个最左前缀可以是字符串索引的前n个字节,也可以是联合索引的 最左n个字段。

索引下推

开启关闭索引下推功能,默认情况下是开启的

SET optimizer_switch = 'index_condition_pushdown=off';

SET optimizer_switch = 'index_condition_pushdown=on';

那么什么是索引下推呢,还是上SQL语句

SET optimizer_switch = 'index_condition_pushdown=off';

explain select * from tuser where name like 'test%' and age = '18';

SET optimizer_switch = 'index_condition_pushdown=on';

explain select * from tuser where age = '18' and name like 'test%';

那么怎么看是否使用了索引下推呢,引用下官网 的一句话,Extra列显示Using index condition即使用了索引下推

  • EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using index because that does not apply when full table rows must be read.

所以,到底什么是索引下推呢,来看我分析

  • 关闭索引下推优化时
    首先或者所有name是test开头的数据,然后回表,判断age等于18的数据,然后返回数据
  • 开启索引下推优化时
    获取name是test开头的数据,并判断age等于18 的数据,然后剩余的数据拿到主键索引树回表查询返回

两者的区别就是回表的次数明显变少,在索引下推优化关闭时,会拿所有的主键去主键树获取数据,而开启之后,提前做判断,减少回表次数,这就是索引下推,也是工作中常用到的优化方式

_rowid 查看示例

非空,唯一,主键

create table test(a int primary key,b varchar(5));

insert into test values(1,'a'),(2,'b'),(3,'c'),(4,'c'),(5,'d');

select _rowid from test;

原文链接

参考链接

MySQL :: MySQL 8.0 Reference Manual :: 15.6.2.1 Clustered and Secondary Indexes

有不同索引类型之间的描述

MySQL :: MySQL 8.0 Reference Manual :: 13.1.15 CREATE INDEX Statement

b树索引与hash索引的对比

MySQL :: MySQL 8.0 Reference Manual :: 8.3.9 Comparison of B-Tree and Hash Indexes

index

MySQL :: MySQL 8.0 Reference Manual :: MySQL Glossary

geek

05 | 深入浅出索引(下)-极客时间

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
186
分享
相关文章
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL底层概述—7.优化原则及慢查询
本文主要介绍了:Explain概述、Explain详解、索引优化数据准备、索引优化原则详解、慢查询设置与测试、慢查询SQL优化思路
MySQL底层概述—7.优化原则及慢查询
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
MySQL底层概述—5.InnoDB参数优化
本文介绍了MySQL数据库中与内存、日志和IO线程相关的参数优化,旨在提升数据库性能。主要内容包括: 1. 内存相关参数优化:缓冲池内存大小配置、配置多个Buffer Pool实例、Chunk大小配置、InnoDB缓存性能评估、Page管理相关参数、Change Buffer相关参数优化。 2. 日志相关参数优化:日志缓冲区配置、日志文件参数优化。 3. IO线程相关参数优化: 查询缓存参数、脏页刷盘参数、LRU链表参数、脏页刷盘相关参数。
MySQL底层概述—5.InnoDB参数优化
MySQL和SQLSugar百万条数据查询分页优化
在面对百万条数据的查询时,优化MySQL和SQLSugar的分页性能是非常重要的。通过合理使用索引、调整查询语句、使用缓存以及采用高效的分页策略,可以显著提高查询效率。本文介绍的技巧和方法,可以为开发人员在数据处理和查询优化中提供有效的指导,提升系统的性能和用户体验。掌握这些技巧后,您可以在处理海量数据时更加游刃有余。
24 9
从MySQL优化到脑力健康:技术人与效率的双重提升
聊到效率这个事,大家应该都挺有感触的吧。 不管是技术优化还是个人状态调整,怎么能更快、更省力地完成事情,都是我们每天要琢磨的事。
58 23
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
102 42
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等