MySQL优化之覆盖索引的使用

查看测试表结构:

mysql> show create table im_message \G
*************************** 1. row ***************************
       Table: im_message
Create Table: CREATE TABLE `im_message` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `from_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `from_type` tinyint(1) NOT NULL DEFAULT '0',
  `to_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `to_type` tinyint(1) NOT NULL DEFAULT '0',
  `content` varchar(2048) COLLATE utf8_bin DEFAULT '',
  `create_date` bigint(20) NOT NULL,
  `update_date` bigint(20) NOT NULL,
  `message_id` varchar(40) COLLATE utf8_bin NOT NULL,
  `is_sync` tinyint(1) DEFAULT '0' COMMENT '是否同步   0 未同步    1  已同步',
  `is_read` tinyint(1) DEFAULT '0' COMMENT '是否已读   0 未读    1  已读',
  `is_withdraw` tinyint(1) DEFAULT '0' COMMENT '是否撤回  0 未撤  1  已撤',
  `is_lastest` tinyint(1) DEFAULT '0' COMMENT '是否是最新回话消息 0 不是 1是',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_message_id` (`message_id`),
  KEY `idx_date` (`create_date`),
  KEY `idx_from_id` (`from_id`),
  KEY `idx_to_id` (`to_id`),
  KEY `idx_is_sync` (`is_sync`),
  KEY `idx_update_date` (`update_date`),
  KEY `idx_fid_tid` (`from_id`,`to_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13264365 DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

查看SQL执行效果: 

select max(id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id;
|  8547247 |
|  7152294 |
|  6897129 |
| 12874034 |
| 10011290 |
|  8027198 |
|  7852741 |
|  9960496 |
|  6059399 |
| 10860981 |
|  9963172 |
| 13253445 |
+----------+
27827 rows in set (0.91 sec)

查看执行计划:

mysql> explain select max(id) as “id” from im_message WHERE from_id=’bd29879279c44672a2fdffcb8428b4d1′ or to_id=’bd29879279c44672a2fdffcb8428b4d1′ group by from_id, to_id;

+—-+————-+————+————+————-+———————————–+———————–+———+——+——–+———-+—————————————————————————————+

| id | select_type | table      | partitions | type        | possible_keys                     | key                   | key_len | ref  | rows   | filtered | Extra                                                                                 |

+—-+————-+————+————+————-+———————————–+———————–+———+——+——–+———-+—————————————————————————————+

|  1 | SIMPLE      | im_message | NULL       | index_merge | idx_from_id,idx_to_id,idx_fid_tid | idx_fid_tid,idx_to_id | 122,122 | NULL | 168680 |   100.00 | Using sort_union(idx_fid_tid,idx_to_id); Using where; Using temporary; Using filesort |

+—-+————-+————+————+————-+———————————–+———————–+———+——+——–+———-+—————————————————————————————+

1 row in set, 1 warning (0.00 sec)

创建覆盖索引: 

mysql> alter table im_message add index idx_from_id_id(from_id,id);
Query OK, 0 rows affected (1 min 1.94 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table im_message add index idx_to_id_id(to_id,id);
Query OK, 0 rows affected (1 min 9.79 sec)
Records: 0  Duplicates: 0  Warnings: 0

重新查看SQL执行效果:

select max(id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id;
|  8547247 |
|  7152294 |
|  6897129 |
| 12874034 |
| 10011290 |
|  8027198 |
|  7852741 |
|  9960496 |
|  6059399 |
| 10860981 |
|  9963172 |
| 13253445 |
+----------+
27827 rows in set (0.63 sec)

查看执行计划: 

mysql> explain select max(id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id;
+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+
| id | select_type | table      | partitions | type        | possible_keys                                                 | key                         | key_len | ref  | rows   | filtered | Extra                                                                                  |
+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+
|  1 | SIMPLE      | im_message | NULL       | index_merge | idx_from_id,idx_to_id,idx_fid_tid,idx_from_id_id,idx_to_id_id | idx_from_id_id,idx_to_id_id | 122,122 | NULL | 162106 |   100.00 | Using union(idx_from_id_id,idx_to_id_id); Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

发现优化器选择了新建的两个覆盖索引。 

创建覆盖索引之后,利用索引的有序性, select max(id)可以快速的取到最大id。

Image placeholder
guoqing
未设置
  27人点赞

没有讨论,发表一下自己的看法吧

推荐文章
效率提高N倍的19条MySQL优化秘籍

一、EXPLAIN做MySQL优化,我们要善用 EXPLAIN 查看SQL执行计划。下面来个简单的示例,标注(1,2,3,4,5)我们要重点关注的数据type列,连接类型。一个好的sql语句至少要达到

TPC-C解析系列03_TPC-C基准测试之SQL优化

TPC-C是一个非常严苛的基准测试模型,考验的是一个完备的关系数据库系统全链路的能力。这也是为什么在TPC-C的榜单前列,出现的永远只是大家熟知的那几家在业界有着几十年积累、从关系数据库理论开始发展就

SQL优化案例-定位系统中大量的rollback(十八)

系统中logfilesync比较严重,查看存储都没有问题,logfileparallelwrite很低,时间分布直方图也没问题数据库中提交和回滚操作比较频繁,每秒1000多次,rollback占比1/

【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

一场HBase2.x的写入性能优化之旅

本文通过实战跑分来展示HBase2.x的写入性能首先,简单介绍一下我们的测试环境:集群由5个节点组成,每个节点有12块800GB的SSD盘、24核CPU、128GB内存;集群采用HBase和HDFS混

为什么mysql索引要使用B+树,而不是B树,红黑树

我们在MySQL中的数据一般是放在磁盘中的,读取数据的时候肯定会有访问磁盘的操作,磁盘中有两个机械运动的部分,分别是盘片旋转和磁臂移动。盘片旋转就是我们市面上所提到的多少转每分钟,而磁盘移动则是在盘片

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

【MySQL实战45讲】索引部分整理

本文摘抄自极客时间【MySQL实战45讲】为什么要有索引?索引的作用是什么?索引的出现其实就是为了提高数据查询的效率,就像书的目录一样。一本书我们可以通过目录中快速的定位其中的某一个知识点;对于数据库

曾经,我真的以为我很懂MySQL索引...

课程推荐:Java开发工程师--学习猿地精品课程 一、什么是索引?  在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适

MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询

作者:逸宸a链接:https://www.jianshu.com/p/cbdef47fb837对MySQL的性能和亿级数据的处理方法思考,以及分库分表到底该如何做,在什么场景比较合适?比如银行交易流水

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

Mysql中,21个写SQL的好习惯,你值得拥有呀

课程推荐:PHP开发工程师--学习猿地精品课程 前言每一个好习惯都是一笔财富,本文分SQL后悔药,SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~github地址

低代码开发平台生存在标准化之外

这一次连续创业者任向晖找到他的PMF了。4月28日,明道更名为明道云,任向晖带着他的团队从SaaS协同向低代码开发平台(LowCodePlatform)转舵,而这并非孤例,更早一些还有从九章全协同转型

中国联通容器化之路及选型标准

“容器”的概念灵感来源于集装箱,有了集装箱,货物不会杂乱无章地堆放在一起,易于管理,也方便运输。容器技术也被称为轻量化虚拟化技术,与集装箱的作用相似。相比前网红虚拟机,容器技术凭借其轻量化、快速部署以

为什么不建议在 MySQL 中使用 UTF-8?

最近我遇到了一个bug,我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串,然后出现了一个离奇的错误:Incorrect string value: ‘😃 

MySQL Batched Key Access (BKA)原理和设置使用方法举例

MySQL5.6版本开始增加了提高表join性能的算法:BatchedKeyAccess(BKA)的新特性。BKA算法原理:将外层循环的行/结果集存入joinbuffer,内存循环的每一行数据与整个b

mysql5.7 General tablespace使用说明

GeneraltablespaceGeneraltablespace 是一种共享的 innodb 表空间,有点类似 ibdata1 。可以在一个表空间数据文件下存储多张表,即使这些表来自不同的 sch

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My