【金三银四】Mysgl优化了解？什么情况下会导致SQL索引失效？如何写出高效SQL与优化慢SQL

2024-03-03 06:04
文章标签 sql 高效 优化 索引 了解 导致 情况 失效 database 金三银 写出 下会 mysgl

本文主要是介绍【金三银四】Mysgl优化了解？什么情况下会导致SQL索引失效？如何写出高效SQL与优化慢SQL，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Mysgl优化

MySQL 优化是指对 MySQL 数据库的配置、表设计、查询语句等进行针对性的优化，以提高数据库的性能和效率。这包括但不限于合理设计数据库表结构、编写高效的 SQL 查询语句、创建合适的索引以及调整数据库服务器的参数等。

当MySQL单表记录数过大时，性能下降是一个常见问题。这是因为随着数据量的增加，数据库在执行增删改查操作时需要处理更多的数据。

当涉及到 MySQL 数据库优化时，可以从以下几个方面进行详细讨论：

一、单表优化：

  1. 字段优化：

    • 选择合适的数据类型以减少存储空间和提高查询效率。
    • 使用 TINYINT、SMALLINT、MEDIUM_INT 代替 INT，非负数加 UNSIGNED。
    • VARCHAR 长度根据实际需要分配。
    • 首选 TIMESTAMP 而非 DATETIME。
    • 单表字段不超过 20 个。
    • 尽量使用NOT NULL约束，避免NULL带来的额外开销。
    • 枚举（ENUM）或整数（INT）比字符串（VARCHAR）更高效，特别是有大量重复值时。
    • 使用整数存储IP地址以提高查询效率。

  2. 索引优化：

    • 根据查询模式创建索引，重点关注WHERE和ORDER BY中的列，可根据EXPLAIN命令来查看是否用了索引还是全表扫描。
    • 避免在 WHERE 子句中对字段进行 NULL 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
    • 稀少值的字段不适合建索引，如性别。
    • 避免使用外键和 UNIQUE 约束。
    • 对字符字段可以考虑创建前缀索引以减少索引大小。
    • 避免对索引列进行运算，以免引起索引失效。

  3. 查询SQL优化：

    • 开启慢查询日志以定位性能瓶颈。
    • 避免列运算，尽量简化 SQL。
    • 不使用 SELECT *。
    • 将 OR 改写成 IN。
    • 避免函数和触发器，推迟至应用程序层实现。
    • 少用 JOIN，使用同类型比较。
    • 使用索引避免全表扫描，提高查询效率。
    • 使用LIMIT进行分页查询，避免一次性获取大量数据。
    • 尽量避免在WHERE子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

  4. 存储引擎选择：

    • MyISAM适用于读多写少的场景，提供全文索引和压缩表功能。
    • InnoDB适用于写多读少的场景，支持事务、行锁和外键，提供更高的并发处理能力和数据保护。
      当然可以，以下是一个简单的表格，列出了MyISAM和InnoDB存储引擎的特点：
特点MyISAMInnoDB
读写特性读多写少写多读少
事务支持不支持支持
锁定方式表级锁定行级锁定
外键支持不支持支持
全文索引支持不支持
压缩表支持不支持
并发处理较差较好
数据保护不提供数据保护机制提供数据保护机制

总体来讲，MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表。
希望这个表格能够清晰地展示出MyISAM和InnoDB存储引擎的特点。

  1. 系统调优参数：

sysbench：一个模块化，跨平台以及多线程的性能测试工具。

https://github.com/akopytov/sysbench

iibench-mysql：基于Java的MySQL / Percona / MariaDB 索引进行插入性能测试工具。

https://github.com/tmcallaghan/iibench-mysql

tpcc-mysql：Percona开发的TPC-C测试工具。

https://github.com/Percona-Lab/tpcc-mysql
在优化数据库时，建议定期监控性能并评估优化效果。在进行大规模优化前，应在测试环境中验证，确保安全有效。

二、读写分离：

通过将读操作和写操作分开到不同的数据库服务器上，提高系统的并发处理能力和读取性能。写操作集中在主库上，读操作则可以分摊到多个从库上，有效减轻主库的压力。

读写分离是一种数据库优化策略，通过将数据库的读操作和写操作分别分配到不同的数据库服务器上，以提高系统的并发处理能力和读取性能。在这种策略下，写操作集中在主数据库服务器上，而读操作则可以分摊到多个从数据库服务器上。这样做的好处是可以有效减轻主库的压力，提高系统的吞吐量。

读写分离的关键在于使用数据库代理程序，如MySQL Proxy、MyCAT，这些代理程序充当了数据库服务器和应用程序之间的中介。读操作可以通过代理程序进行缓存，而写操作则被转发到主数据库服务器进行实际的数据存储。这种分离使得读操作可以轻松地从缓存中获得高性能，而写操作则可以确保数据的一致性和完整性。

除了提高性能外，读写分离还可以帮助组织实现高可用性。通过将读和写操作分离到不同的服务器上，组织可以在不影响读操作的情况下进行数据库的维护和升级。这使得组织能够更轻松地保持系统的正常运行，并减少停机时间。

需要注意的是，读写分离并不总是适用于所有情况。在某些情况下，这种分离可能会导致数据不一致。例如，如果两个用户同时进行读操作，可能会读到旧的数据。因此，在实现读写分离时，需要采取一定的措施来保证数据的一致性。

三、缓存：

利用缓存技术（如 Memcached、Redis 等）缓存热点数据，减少对数据库的频繁访问，提高访问速度和并发能力。
缓存技术是提高系统性能的关键手段之一，它通过存储频繁访问的数据或结果，从而减少对数据库或其他数据源的直接访问，加快数据检索速度，并提高系统的并发处理能力。

常用的缓存技术包括Memcached和Redis。Memcached是一个高性能、分布式内存对象缓存系统，主要用于缓存数据库中的对象，以减少数据库的访问次数。而Redis是一个开源的、支持网络、可基于内存也可以持久化的日志型、Key-Value数据库，并提供多种类型的数据结构来适应不同场景下的缓存需求。

在实现缓存时，需要关注以下几个关键点：

  1. 缓存命中率：缓存命中率是衡量缓存效果的重要指标，高命中率意味着更多的请求可以直接从缓存中获取，减少了对后端数据库的访问。
  2. 缓存更新策略：包括过期时间、懒加载和主动更新等，选择合适的更新策略可以确保缓存数据的及时性和有效性。
  3. 缓存穿透：为了避免缓存未命中时对数据库的巨大压力，需要采取措施如缓存空值或布隆过滤器等来防止缓存穿透。
  4. 缓存雪崩：在缓存失效时，大量请求直接打在数据库上，可能导致系统瘫痪。可以通过设置不同的过期时间、限流、增加缓存实例等方式来避免缓存雪崩。
  5. 缓存预热：在系统启动或缓存数据初始化时，提前将热点数据加载到缓存中，以避免系统上线初期大量请求直接访问数据库。
  6. 缓存粒度：根据业务需求，选择合适的缓存粒度，如数据行、数据页或数据块，以平衡缓存效率和内存利用率。

通过合理使用缓存技术，可以有效降低系统响应时间，提高系统的并发处理能力，从而改善用户体验。

四、表分区：

将大表按照一定的规则分割成小的分区，可以加速查询和提高性能。常见的分区方式有范围分区、哈希分区、列表分区等。

五、垂直拆分：

将原本一个大表按列拆分成多个表，每个表包含部分列，可以提高查询效率，降低数据冗余。

六、水平拆分：

将原本一个大表按行拆分成多个表，每个表包含部分行数据，可以分散数据存储，提高并发能力和负载均衡。

七、水平拆分跟垂直拆分的区别

水平拆分和垂直拆分是在数据库设计和优化中常用的两种数据分割策略。它们的区别在于数据如何被拆分和存储。

  1. 水平拆分（Horizontal Sharding）：

    • 水平拆分是指将数据按照某种规则（比如按照用户ID、时间范围等）分割成多个部分，然后分别存储到不同的数据库节点或表中。
    • 这种方式适合于需要横向扩展的场景，可以更好地应对数据量大、并发读写压力大的情况。
    • 例子：将全国用户的数据按照地理位置分割存储到不同的数据库节点上。

  2. 垂直拆分（Vertical Partitioning）：

    • 垂直拆分是指将数据表按照字段的关系和访问模式进行拆分，将不同的字段分别存储到不同的表或数据库中。
    • 这种方式适合于减少单个表的宽度，提高数据读取效率，同时可以根据不同的业务需求将数据存储到不同的物理存储介质上。
    • 例子：将包含大量稀疏字段的表进行拆分，将常用的字段存储到主表，将稀疏字段存储到关联表中。

总的来说，水平拆分注重的是数据的分布和扩展能力，而垂直拆分注重的是数据的结构和访问模式。在实际应用中，通常会综合考虑两种拆分方式，根据具体的业务需求和系统特点来选择最合适的拆分策略。

综合来看，MySQL 数据库优化是一个综合性的工作，需要结合具体业务场景和需求，从表结构设计、索引优化、查询语句编写、缓存应用、分区与拆分等多个方面综合考虑，以提高数据库性能和系统稳定性。

什么情况下会导致SQL索引失效?

SQL索引失效的情况包括：

  1. 使用OR条件：当查询条件中包含OR时，即使部分条件涉及索引列，也可能导致索引失效。
SELECT * FROM users WHERE age = 30 OR name = 'Alice';
  1. 类型转换：对索引列进行类型转换，如使用函数，可能导致索引失效。
SELECT * FROM users WHERE UPPER(name) = 'ALICE';
  1. 范围条件右侧列：在复合索引中，范围条件（如>、<、BETWEEN等）右侧的列无法使用索引。
SELECT * FROM users WHERE age > 25 AND name = 'Alice';
  1. 不等于条件：使用!=或<>会导致索引失效。
SELECT * FROM users WHERE age != 25;
  1. IS NULL/IS NOT NULL：这些条件会导致索引失效。
SELECT * FROM users WHERE name IS NULL;
  1. LIKE通配符开头：如果LIKE查询以通配符%开头，会导致索引失效。
SELECT * FROM users WHERE name LIKE '%Alice';
  1. 字符串未加引号：字符串类型字段未加单引号可能导致索引失效。
SELECT * FROM users WHERE name = Alice;
  1. 索引列上计算：在索引列上进行计算或其他操作，会导致索引失效。
SELECT * FROM users WHERE age + 10 = 30;
  1. 最佳左前缀法则：在复合索引中，必须从最左边的列开始查询，并且不能跳过中间列。
SELECT * FROM users WHERE name = 'Alice' AND age = 25;
  1. 存储引擎限制：某些存储引擎对索引的使用有限制。
  2. 优化器选择：优化器可能根据成本考虑不使用索引。
  3. 数据分布不均：索引列数据分布不均，可能导致索引失效。
  4. 表数据量小：对于小表，全表扫描可能比索引更快。
  5. 查询条件覆盖索引：尽量使用覆盖索引，避免回表查询。

如何使用EXPLAIN查看SQL有没有走索引

要使用EXPLAIN来查看SQL语句是否使用了索引，可以按照以下步骤进行：

  1. 在待查询的SQL语句前加上EXPLAIN关键字，例如：

    EXPLAIN SELECT * FROM table_name WHERE column_name = 'value';

  2. 执行该带有EXPLAIN关键字的SQL语句，数据库系统将返回一个结果集，其中包含了查询的执行计划。

  3. 查看执行计划中的type列，该列表示查询使用了哪种类型的访问方法：

    • 如果type列显示为index，表示查询使用了索引。
    • 如果type列显示为range，表示查询使用了范围索引。
    • 如果type列显示为ALL，表示查询进行了全表扫描，没有使用索引。

  4. 可以查看key列，该列表示查询实际使用的索引名称。

  5. 如果查询涉及多个表，还可以查看possible_keys列，表示查询可能使用的索引列表。

  6. 最后，查看rows列，表示查询预计需要检查的行数，行数越少表示查询效率可能越高。

通过分析EXPLAIN的结果，可以判断SQL语句是否使用了索引，以及索引的使用是否合理。如果查询未使用索引或使用了不合适的索引，可以考虑修改查询语句或创建更合适的索引以提高查询性能。

下面是一个表格格式的展示，包含了在使用EXPLAIN命令时通常可以查看的参数：

参数说明
id每个查询的序号，如果有子查询则会有多行记录，父查询的id值会与子查询相关联
select_type查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等
table正在访问的表
partitions匹配的分区信息
type连接类型，包括system、const、eq_ref、ref、range、index等
possible_keys可能应用在表中的索引，但不一定被查询使用
key实际使用的索引
key_len索引字段的长度
ref使用的索引的哪一列，通常是常数或字段
rows数据库系统认为必须检查的行数
filtered表的过滤行百分比
Extra提供关于执行查询的额外信息，如Using index、Using temporary、Using filesort等

如何写出高效SQL与优化慢SQL

要编写高效的 SQL 并优化慢 SQL，可以遵循以下一些建议：

编写高效 SQL：

  1. 选择合适的数据类型：使用最适合数据存储需求的数据类型，避免过度使用大型数据类型。

  2. 编写简洁的查询：只检索需要的列，避免不必要的数据传输和处理。

  3. 避免使用“SELECT *”：明确指定需要的列，减少不必要的数据载入。

  4. 使用索引：确保表上的经常查询的列有索引，以加快检索速度。

  5. 避免在 WHERE 子句中对列进行函数操作：这会导致无法利用索引，影响查询性能。

  6. 合理使用 JOIN：选择合适的 JOIN 类型，避免笛卡尔积，确保连接条件正确。

  7. 避免使用子查询：尽量优化为 JOIN 操作，避免多次执行子查询。

  8. 避免在查询中使用通配符：如 %，这会导致全表扫描。

优化慢 SQL：

  1. 使用 EXPLAIN 分析查询计划：了解查询是如何执行的，找出潜在的性能瓶颈。

  2. 优化查询语句：根据 EXPLAIN 结果，考虑是否可以调整查询、添加索引或重写查询以提高性能。

  3. 创建索引：分析查询中涉及的列，为经常用于搜索和连接的列创建索引。

  4. 避免在 WHERE 子句中进行不必要的计算：将计算移到应用程序层面，减轻数据库负担。

  5. 定期分析慢查询日志：识别哪些查询较慢，并针对性地进行优化工作。

  6. 使用缓存：利用缓存技术如 Redis 缓存热点数据，减少数据库压力。

  7. 定期维护数据库：包括优化表结构、清理无用数据、重建索引等操作，保持数据库的健康状态。

通过以上方法，可以提高 SQL 查询的效率并优化慢 SQL，从而提升数据库性能和用户体验。

超级全面的MySQL优化指南1

  1. 本文参考上述文章进行整理。 ↩︎

这篇关于【金三银四】Mysgl优化了解？什么情况下会导致SQL索引失效？如何写出高效SQL与优化慢SQL的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/768680。

相关文章

Vue3 的 shallowRef 和 shallowReactive：优化性能

Vue3 的 shallowRef 和 shallowReactive：优化性能

大家对 Vue3 的 ref 和 reactive 都很熟悉，那么对 shallowRef 和 shallowReactive 是否了解呢？ 在编程和数据结构中，“shallow”（浅层）通常指对数据结构的最外层进行操作，而不递归地处理其内部或嵌套的数据。这种处理方式关注的是数据结构的第一层属性或元素，而忽略更深层次的嵌套内容。 1. 浅层与深层的对比 1.1 浅层（Shallow） 定义
阅读更多...
SQL中的外键约束

SQL中的外键约束

外键约束用于表示两张表中的指标连接关系。外键约束的作用主要有以下三点： 1.确保子表中的某个字段（外键）只能引用父表中的有效记录2.主表中的列被删除时，子表中的关联列也会被删除3.主表中的列更新时，子表中的关联元素也会被更新 子表中的元素指向主表 以下是一个外键约束的实例展示
阅读更多...
基于MySQL Binlog的Elasticsearch数据同步实践

基于MySQL Binlog的Elasticsearch数据同步实践

一、为什么要做 随着马蜂窝的逐渐发展，我们的业务数据越来越多，单纯使用 MySQL 已经不能满足我们的数据查询需求，例如对于商品、订单等数据的多维度检索。 使用 Elasticsearch 存储业务数据可以很好的解决我们业务中的搜索需求。而数据进行异构存储后，随之而来的就是数据同步的问题。 二、现有方法及问题 对于数据同步，我们目前的解决方案是建立数据中间表。把需要检索的业务数据，统一放到一张M
阅读更多...
关于数据埋点，你需要了解这些基本知识

关于数据埋点，你需要了解这些基本知识

产品汪每天都在和数据打交道，你知道数据来自哪里吗？ 移动app端内的用户行为数据大多来自埋点，了解一些埋点知识，能和数据分析师、技术侃大山，参与到前期的数据采集，更重要是让最终的埋点数据能为我所用，否则可怜巴巴等上几个月是常有的事。   埋点类型 根据埋点方式，可以区分为： 手动埋点半自动埋点全自动埋点 秉承“任何事物都有两面性”的道理：自动程度高的，能解决通用统计，便于统一化管理，但个性化定
阅读更多...
如何去写一手好SQL

如何去写一手好SQL

MySQL性能 最大数据量 抛开数据量和并发数，谈性能都是耍流氓。MySQL没有限制单表最大记录数，它取决于操作系统对文件大小的限制。 《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。 博主曾经操作过超过4亿行数据
阅读更多...
HDFS—存储优化（纠删码）

HDFS—存储优化（纠删码）

纠删码原理 HDFS 默认情况下，一个文件有3个副本，这样提高了数据的可靠性，但也带来了2倍的冗余开销。 Hadoop3.x 引入了纠删码，采用计算的方式，可以节省约50％左右的存储空间。 此种方式节约了空间，但是会增加 cpu 的计算。 纠删码策略是给具体一个路径设置。所有往此路径下存储的文件，都会执行此策略。 默认只开启对 RS-6-3-1024k
阅读更多...
性能分析之MySQL索引实战案例

性能分析之MySQL索引实战案例

文章目录 一、前言二、准备三、MySQL索引优化四、MySQL 索引知识回顾五、总结 一、前言 在上一讲性能工具之 JProfiler 简单登录案例分析实战中已经发现SQL没有建立索引问题，本文将一起从代码层去分析为什么没有建立索引？ 开源ERP项目地址：https://gitee.com/jishenghua/JSH_ERP 二、准备 打开IDEA找到登录请求资源路径位置
阅读更多...
使用opencv优化图片（画面变清晰）

使用opencv优化图片（画面变清晰）

文章目录 需求影响照片清晰度的因素 实现降噪测试代码 锐化空间锐化Unsharp Masking频率域锐化对比测试 对比度增强常用算法对比测试 需求 对图像进行优化，使其看起来更清晰，同时保持尺寸不变，通常涉及到图像处理技术如锐化、降噪、对比度增强等 影响照片清晰度的因素 影响照片清晰度的因素有很多，主要可以从以下几个方面来分析 1. 拍摄设备 相机传感器：相机传
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
高效+灵活，万博智云全球发布AWS无代理跨云容灾方案！

高效+灵活，万博智云全球发布AWS无代理跨云容灾方案！

摘要 近日，万博智云推出了基于AWS的无代理跨云容灾解决方案，并与拉丁美洲，中东，亚洲的合作伙伴面向全球开展了联合发布。这一方案以AWS应用环境为基础，将HyperBDR平台的高效、灵活和成本效益优势与无代理功能相结合，为全球企业带来实现了更便捷、经济的数据保护。 一、全球联合发布 9月2日，万博智云CEO Michael Wong在线上平台发布AWS无代理跨云容灾解决方案的阐述视频，介绍了
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2