高性能的MySQL(5)索引策略一压缩,冗余,重复,索引和锁

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

一、压缩索引

MyISAM使用前缀压缩来减少索引的大小,默认只压缩字符串,但是通过设置也可以对整数做压缩。

压缩可以使用更少的空间,代价是某些操作可能更慢。特别是倒序的查询,测试表明,对于CPU密集型的应用,查询会很慢,特别是倒序。对于I/O密集型应用,查询可能会不错。

可以在CREATE TABLE的语句中指定PACK_KEYS参数来指定索引压缩方式。


二、冗余和重复索引

重复索引:指在相同的列上按照相同的顺序创建的相同类型的索引,要尽量避免重复索引,除非在同一列上创建不同类型的索引来满足不同的查询需求。比方说 key(col) 和 fulltext key(col)。

冗余索引:如果创建了索引(A,B),再创建索引(A)就是冗余索引。因为索引(A,B)也可以当作(A)来使用(只针对B-Tree)。但是如果再创建索引(B,A)或者(B),则不是冗余索引。或者类型不同,比方说哈希,全文索引等。

冗余索引通常发生在添加索引的时候,大多数情况下都不需要冗余索引,而是尽量扩展已有的索引,除非扩展已有的索引会导致索引很大,从而影响其他使用索引的性能。

例如:在一个整数列的索引上扩展一个很长的varchar列的索引,性能可能就会急剧下降。但是增加一个新的索引,就会对增删改操作影响很大,所以要平衡使用。


如何找到这写索引,以便删除,可以访问INFORMATION_SCHEMA,或者一些现有的工具来定位。

有一个值得注意的地方:

对于InnoDB因为二级索引包换了主键,所以列(A)上的索引就相当与(A,ID),所以类似

where A=5 order by id 这样的查询,这个索引会很有用。但是扩展为索引(A,B)

之后,则实际变成了(A,B,ID),则order by就无法用到索引排序了。


三、索引和锁

索引可以让查询锁定更少的行,因为索引可以让查询不访问那些不需要的行,那么就会锁定更少的行。这有2点好处:

1、减少锁定行带来的额外的开销。

2、锁定超过需要的行会增加锁争用和减少并发性。


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

举例说明:表结构

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  `job_num` (`job_num`)
) ENGINE=InnoDB  DEFAULT  CHARSET=utf8 ;

数据如下:

151658496.png

在一个终端执行如下的操作:

151920121.png

虽然只返回了10002-10004之间的行,但是实际上10001-10004都被锁定了

152151828.png

也就是说,底层存储引擎的操作是从索引开头开始获取满足id<10005的记录,服务器并没有告诉InnoDB可以过滤第一行的where条件。注意到EXPLAIN中的Extra出现了“Using where”,这表示MySQL服务器将存储引擎返回行以后再应用where过滤条件。

我们来证明第一行确实是被锁定了,保持这个终端链接不关闭,然后我们打开另一个终端。

171221780.png

这个查询会挂起,知道第一个事务释放了第一行的锁。

按照这个例子,即使使用了索引,InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话,结果会更糟。MySQL会全表扫描并锁住所有的行,而不管是不是需要。





















本文转自shayang8851CTO博客,原文链接:http://blog.51cto.com/janephp/1312782,如需转载请自行联系原作者
相关实践学习
如何快速连接云数据库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数据库中慢查询分析的关键概念和技术手段。
310 80
MySQL底层概述—10.InnoDB锁机制
本文介绍了:锁概述、锁分类、全局锁实战、表级锁(偏读)实战、行级锁升级表级锁实战、间隙锁实战、临键锁实战、幻读演示和解决、行级锁(偏写)优化建议、乐观锁实战、行锁原理分析、死锁与解决方案
103 24
MySQL底层概述—10.InnoDB锁机制
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底层概述—6.索引原理
本文详细回顾了:索引原理、二叉查找树、平衡二叉树(AVL树)、红黑树、B-Tree、B+Tree、Hash索引、聚簇索引与非聚簇索引。
MySQL底层概述—6.索引原理
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
134 25
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
63 22
MySQL 中如何实现分库分表?常见的分库分表策略有哪些?
在MySQL中,分库分表(Sharding)通过将数据分散到多个数据库或表中,以应对大量数据带来的性能和扩展性问题。常见策略包括:哈希分片(分布均匀,查询效率高)、范围分片(适合范围查询)、列表分片(适用于特定值查询)、复合分片(灵活性高)和动态分片(灵活应对负载变化)。每种策略各有优劣,需根据业务需求选择。常用工具如MyCAT、ShardingSphere和TDDL可简化实现过程。
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
176 42