高性能的MySQL(5)索引策略-索引案例分析

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

理解索引最好的办法是结合实例,接下来分析一个例子。

假设要设计一个在线约会网站,用户信息表有很多列,包括国家,地区,城市,性别,眼睛颜色等等。网站必须支持上面的各种组合来搜索用户,包括根据用户的最后在线时间,评分等进行排序的限制。

需要考虑是需要索引来排序还是先检索数据再排序,因为使用索引排序会严格限制索引和查询的设计。如果MySQL使用了某个索引的范围查询,也就无法再使用另一个索引或者是该索引的后续字段进行排序了。接下来一步步讨论:

1、支持多种过滤条件

country列的选择性通常不高,但是可能很多查询会用到。sex列的选择性很低,但是也会有很多查询用到。所以考虑到使用频率,建议创建不同组合的时候将(country,sex)作为前缀。

这和以前说的不应该在选择性低的列上创建索引是矛盾的,这么做有2个理由:

a、因为sex使用的太频繁。

b、更重要的一点是索引加了这一列没有什么坏处,即使查询没有使用sex,我们也可以通过技巧绕过,那就是在查询条件中增加and sex in ('m','f'),这样写不会过滤任何行,而且能够匹配索引最左前缀,这个非常有效,但是不要让in()列表太长

因为查询条件的复杂,可能会有很多需要常见的符合索引比如,(sex,country,age),(sex,country,region,age),(sex,country,region,city,age),如果想尽可能重用索引,那么in()技巧是很重要的,但是也不可滥用,如果列表太长的话,组合到一起会很影响性能。

我们会注意到一点,我们一直将age列放到索引的最后面。这是因为age特点,age列会有很多范围查询,而最左前缀中,遇到第一个范围查询,则后面的列索引就不能使用了。

所以一个重要的原则是将范围查询较多的列放在后面。

2、避免多个范围查询

什么是范围查询?从explain的输出很难区分范围查询(<,>,between)和列表值查询in(),因为explain中的type都是range,但是两种访问效率不同,对于后面字段的索引使用也是完全不一样的。前者后面的索引不能用了,后者却可以。

3、优化排序

使用文件排序对小数据是很快的,但是如果上百万数据,如何排序?

比如创建(sex,rating)索引用于下面的排序

1
select  <clos>  from  profiles  where  sex= 'M'  order  by  rating limit 10;

如过数据需要翻页,那么比较靠后的查询可能会像这样

1
select  <cols>  from  profiles  where  sex= 'M'  order  by  rating limit 100000,10;

这样即使有合适的索引,依旧是个严重的问题,除了预先计算,缓存可以优化这列问题,令一个比较好的策略是使用延迟关联

通过使用覆盖索引查询返回需要的主键,再根据这写主键关联原表获得需要的行,这可以减少mysql扫描那些需要丢弃的行

1
2
3
4
select  <cols>  from  profiles  inner  join (
select  < primary  key  cols>  from  profiles
where  sex= 'M'  order  by  rating limit 100000,10)
as  x using(< primary  key  cols>);

接下来我们看这个例子:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  TABLE  `emp3` (
   `id`  int (11)  NOT  NULL  DEFAULT  '0' ,
   ` name varchar (100)  NOT  NULL ,
   `job`  varchar (100)  NOT  NULL ,
   `num1`  int (10)  DEFAULT  NULL ,
   `num2`  int (10)  DEFAULT  NULL ,
   `num3`  int (10)  DEFAULT  NULL ,
   `job_num`  int (10)  DEFAULT  NULL ,
   `d`  date  DEFAULT  NULL ,
   PRIMARY  KEY  (`id`),
   KEY  `num1` (`num1`,`job_num`)
) ENGINE=InnoDB  DEFAULT  CHARSET=utf8;

表里有100万记录,如图:

135142942.png


看如下2个语句的差别:

1
select  from  emp3  where  num1=1  order  by  job_num limit 100000,10;

135346285.png


1
select  from  emp3  inner  join  select  id  from  emp3  where  num1=1  order  by  job_num limit 100000,10)  as  x using(id);

135454250.png

可以明显看到时间上的差异,不是在一个量级的。

注意:这个差距在InnoDB里是很明显的,因为只有在InnoDB中,这样子查询的索引才能用到覆盖索引,如果是在MyISAM就不会有这么明显的效果了,如下图:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE  TABLE  `emp4` (
   `id`  int (11)  NOT  NULL  DEFAULT  '0' ,
   ` name varchar (100)  NOT  NULL ,
   `job`  varchar (100)  NOT  NULL ,
   `num1`  int (10)  DEFAULT  NULL ,
   `num2`  int (10)  DEFAULT  NULL ,
   `num3`  int (10)  DEFAULT  NULL ,
   `job_num`  int (10)  DEFAULT  NULL ,
   `d`  date  DEFAULT  NULL ,
   PRIMARY  KEY  (`id`),
   KEY  `num1` (`num1`,`job_num`)
) ENGINE=MyISAM  DEFAULT  CHARSET=utf8 ;

20W的数据测试结果如下:

140231632.png






















本文转自shayang8851CTO博客,原文链接:http://blog.51cto.com/janephp/1313556,如需转载请自行联系原作者

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
100
分享
相关文章
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
MySQL底层概述—8.JOIN排序索引优化
MySQL原理简介—9.MySQL索引原理
本文详细介绍了MySQL索引的设计与使用原则,涵盖磁盘数据页的存储结构、页分裂机制、主键索引设计及查询过程、聚簇索引和二级索引的原理、B+树索引的维护、联合索引的使用规则、SQL排序和分组时如何利用索引、回表查询对性能的影响以及索引覆盖的概念。此外还讨论了索引设计的案例,包括如何处理where筛选和order by排序之间的冲突、低基数字段的处理方式、范围查询字段的位置安排,以及通过辅助索引来优化特定查询场景。总结了设计索引的原则,如尽量包含where、order by、group by中的字段,选择离散度高的字段作为索引,限制索引数量,并针对频繁查询的低基数字段进行特殊处理等。
MySQL原理简介—9.MySQL索引原理
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
MySQL底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
服务器数据恢复—云服务器上mysql数据库数据恢复案例
某ECS网站服务器,linux操作系统+mysql数据库。mysql数据库采用innodb作为默认存储引擎。 在执行数据库版本更新测试时,操作人员误误将在本来应该在测试库执行的sql脚本在生产库上执行,导致生产库上部分表被truncate,还有部分表中少量数据被delete。
70 25
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
MySQL原理简介—11.优化案例介绍
本文介绍了四个SQL性能优化案例,涵盖不同场景下的问题分析与解决方案: 1. 禁止或改写SQL避免自动半连接优化。 2. 指定索引避免按聚簇索引全表扫描大表。 3. 按聚簇索引扫描小表减少回表次数。 4. 避免产生长事务长时间执行。
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
176 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。