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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:

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

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

需要考虑是需要索引来排序还是先检索数据再排序,因为使用索引排序会严格限制索引和查询的设计。如果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的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
MySQL底层概述—8.JOIN排序索引优化
本文主要介绍了MySQL中几种关键的优化技术和概念,包括Join算法原理、IN和EXISTS函数的使用场景、索引排序与额外排序(Using filesort)的区别及优化方法、以及单表和多表查询的索引优化策略。
104 22
MySQL底层概述—8.JOIN排序索引优化
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。
无缝集成 MySQL,解锁秒级 OLAP 分析性能极限,完成任务可领取三合一数据线!
通过 AnalyticDB MySQL 版、DMS、DTS 和 RDS MySQL 版协同工作,解决大规模业务数据统计难题,参与活动完成任务即可领取三合一数据线(限量200个),还有机会抽取蓝牙音箱大奖!
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
docker拉取MySQL后数据库连接失败解决方案
通过以上方法,可以解决Docker中拉取MySQL镜像后数据库连接失败的常见问题。关键步骤包括确保容器正确启动、配置正确的环境变量、合理设置网络和权限,以及检查主机防火墙设置等。通过逐步排查,可以快速定位并解决连接问题,确保MySQL服务的正常使用。
101 82
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
193 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。