MySQL的查询优化思路

目录

前言

解决方案

减少查询

SQL优化

索引优化

减少锁

避免大事务

扩容

硬件升级


前言

一般的系统中,数据库往往都是性能瓶颈。在一个系统中,数据库被使用的频率很高,因为几乎所有的应用程序都需要与数据库交互来读取或写入数据。所以一旦数据库的响应慢,负载突增,则会大大影响系统的运行效率,严重点甚至可能直接崩溃。

面对数据库响应变慢,负载突增问题,应该及时处理,以下是处理的思路,依次考虑

  1. 减少查询
  2. SQL优化
  3. 索引优化
  4. 减少锁
  5. 避免大事务
  6. 扩容
  7. 硬件升级

解决方案

减少查询

减少查询时最优先考虑的优化步骤,具体的做法就是做一层缓存,每次请求优先从缓冲中读取,缓存中不存在数据再来查询数据库,这样能够有效地减少数据库查询的次数。

SQL优化

查询路径优化

使用limit提前终止查询

联表查询中使用小表驱动大表(实际上MySQL的查询优化器会自动优化)

查询语句优化

explain 检查查询语句是否走了索引

查询语句尽量做到索引覆盖,避免回表查询

索引下推:MySQL 5.7 的版本能在二级索引查询后先过滤再回表查询,减少了回表查询的次数 

索引优化

如果索引列数据量过大,只要索引列值足够散列,则可以只截取前面部分做索引

创建联合索引,应当根据索引列值的散列程度由高到低从左到右排列创建,创建联合索引后,叶子节点数据按索引列排序。在查询的时候,根据最左匹配原则,优先匹配最左边散列度最高的列,由于散列度足够高,匹配到的数据最为精确,范围最小,所以遍历时用的时间也最少。

减少锁

MySQL事务的默认隔离级别为 RR(REPEATABLE READ 可重复读)

为了不可重复度问题和部分幻读问题,在 RR 隔离级别下,除了会加 Record Lock(记录锁)外,还会加 Gap Lock(间隙锁)Next-key Lock(临键锁),使用了间隙锁和临键锁,就会使得所得粒度变大,发生死锁的概率也变大,支撑不了高并发业务场景。

为了提高并发度,可以将MySQL的事务隔离级别降为 RC(READ COMMITTED 读已提交)

在 RC 隔离级别下,只会添加 Record Lock(记录锁),只会对要修改的当前行加锁,粒度相比间隙锁和临键锁小了很多,因此能够提升并发度和降低死锁概率。

如果将事务隔离级别修改成 RC,则需要自己去处理事务中的不可重复读问题。在 RC 级别中,每次读取都会得到最新的数据,所以很有可能读取到其他事务提交的修改。

避免大事务

并不是事务中执行的语句很多才叫大事务,而是指运行时间很长的事务。即使事务中只有一条执行语句,但是这条执行语句执行时间很长,它就是一个大事务

不用非索引列来更新数据,会锁全表

尽可能用主键索引列来更新数据。

由于使用非主键索引,锁住非主键索引后还需要回表去锁住主键索引叶子节点中的数据。

在某些情况下,会发生死锁。

事务1 事务2 执行结构
begin
锁住id=1的主键索引 success
begin
锁住id=2的主键索引 success

锁住name=b的非主键索引

回表加锁id=2的主键索引

block
锁住name=b的非主键索引 dead lock

删除数据时需先检查数据是否存在,因为删除一个不存在的数据,扫描索引后发现不存在,会加上间隙锁。

扩容

在以上措施都做了之后还是没法将很好降低数据库负载,那么还可以采用读写分离的技术方案,为MySQL做一个主从复制,主库处理写请求,从库处理读请求,根据读请求的并发量进行水平扩容,增加从库的数量。

MySQL的主从复制,可以实现读写分离,以及读库的水平扩容,将读请求分散到多个数据库中,从而降低单个数据库的负载,非常适用于读多写少的业务场景

硬件升级

最后,就是升级数据库服务器了,没有什么是升级硬件解决不了的。早点升级服务器,少整一些有的没的优化,早点下班,反正是老板出钱

相关推荐

  1. MySQL查询优化思路

    2024-07-23 09:56:03       27 阅读
  2. MySQL 查询优化思路

    2024-07-23 09:56:03       119 阅读
  3. mysql怎么优化查询

    2024-07-23 09:56:03       46 阅读
  4. Mysql in查询优化

    2024-07-23 09:56:03       59 阅读
  5. mysql 索引优化查询

    2024-07-23 09:56:03       53 阅读
  6. MySQL查询优化(学习)

    2024-07-23 09:56:03       41 阅读
  7. MySql 查询优化

    2024-07-23 09:56:03       36 阅读

最近更新

  1. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-07-23 09:56:03       106 阅读
  2. Could not load dynamic library ‘cudart64_100.dll‘

    2024-07-23 09:56:03       116 阅读
  3. 在Django里面运行非项目文件

    2024-07-23 09:56:03       95 阅读
  4. Python语言-面向对象

    2024-07-23 09:56:03       103 阅读

热门阅读

  1. 数据库分表实践

    2024-07-23 09:56:03       30 阅读
  2. 陌陌聊天数据案例分析

    2024-07-23 09:56:03       25 阅读
  3. [算法题]删除相邻数字的最大分数

    2024-07-23 09:56:03       24 阅读
  4. 后端开发面试题6(附答案)

    2024-07-23 09:56:03       24 阅读
  5. 紫龙游戏服务器面试

    2024-07-23 09:56:03       24 阅读
  6. C#类型基础Part2-对象判等

    2024-07-23 09:56:03       23 阅读
  7. 量化机器人能否提高市场预测精度?

    2024-07-23 09:56:03       29 阅读