史上最全MySQL性能优化总结

最新推荐文章于 2024-09-21 15:28:01 发布
最新推荐文章于 2024-09-21 15:28:01 发布
阅读量827 收藏 7
点赞数 1
分类专栏： 数据库知识 文章标签： 数据库 mysql优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nonage_bread/article/details/113180454
版权

1. 优化思路

作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？
如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库，你会怎么回答?

说到性能调优，大部分时候想要实现的目标是让我们的査询更快。一个査询的流程又是由很多个环节组成的，每个环节都会消耗时间。

mysql架构图：
在这里插入图片描述
我们要减少查询所消耗的时间，就要从每一个环节入手。
先确认一下环境：

select version();
show variables like '%engine%';

2. 连接一一配置优化

第一个环节是客户端连接到服务端，连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。
比如我们遇到过的

Mysql: error 1040: Too many connections的错误。

这个是超过了服务端设置的最大并发连接数。

可以从两个方面来解决连接数不够的问题:

  1. 从服务端来说，我们可以增加服务端的可用连接数。如果有多个应用或者很多请求同时访问数据库，连接数不够的时候，我们可以:

    • 增加可用连接数，修改max_connections的大小:
      show variables like 'max_connections';-- 修改最大连接数，当有多个应用连接的时候
    • 及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是28800秒，8小时，我们可以把这个值调小。
      show global variables like 'wait_timeout; -- 及时释放不活动的连接，注意不要释放连接池还在使用的连接

  2. 从客户端来说，可以减少从服务端获取的连接数。如果我们想要不是每一次执行 SQL都创建一个新的连接，应该怎么做？
    这个时候我们可以引入连接池，实现连接的重用。
    常见的数据库连接池有老牌的DBCP和C3P0、阿里的Druid、Hikari (Spring Boot 2.x版本默认的连接池)。
    连接池并不是越大越好，只要维护一定数量大小的连接池，其他的客户端排队等待获取连接就可以了。有的时候连接池越大，效率反而越低。

Druid的默认最大连接池大小是8。Hikari的默认最大连接池大小是10。
为什么默认值都是这么小呢？
在Hikari的github文档中，给出了一个PostgreSQL数据库建议的设置连接池大小的公式。
https://github.com/brettwooldridge/HikariCP/wiki/About-Pool-Sizing

它的建议是机器核数乘以2加1。也就是说，4核的机器，连接池维护9个连接就够了。这个公式从一定程度上来说对其他数据库也是适用的。这里面还有一个减少连接池大小实现提升并发度和吞吐量的案例。

为什么有的情况下，减少连接数反而会提升吞吐量呢？为什么建议设置的连接池大小要跟CPU的核数相关呢？
答：每一个连接，服务端都需要创建一个线程去处理它。连接数越多，服务端创建的线程数就会越多。

问题：CPU是怎么同时执行远远超过它的核数大小的任务的？分配时间片。上下文切换。
而CPU的核数是有限的，频繁的上下文切换会造成比较大的性能开销。

我们这里说到了从数据库配置的层面去优化数据库。不管是数据库本身的配置，还是安装这个数据库服务的操作系统的配置，对于配置进行优化，最终的目标都是为了更好地发挥硬件本身的性能，包括CPU、内存、磁盘、网络。
在不同的硬件环境下，操作系统和MySQL的参数的配置是不同的，没有标准的配置。

很多的MySQL和InnoDB的配置参数，包括各种开关和数值的配置，大多数参数都提供了一个默认值，比如默认的页大小，连接数，日志文件大小等等。这些默认配置可以满足大部分情况的需求，除非有特殊的需求，在清楚参数的含义的情况下再去修改它。修改配置的工作一般由专业的DBA完成。也有一些工具可以给出推荐值。

至于硬件本身的选择，比如使用固态硬盘，搭建磁盘阵列，选择特定的CPU型号这些，更不是我们开发人员关注的重点，这个我们就不做过多的介绍了。回到连接的这个话题。

3. 架构优化

3.1 缓存

除了合理设置服务端的连接数和客户端的连接池大小之外，我们还有哪些减少客户端使用的连接数的方案呢？
我们可以引入缓存。

在系统里面有一些很慢的查询，要么是数据量大，要么是关联的表多，要么是计算逻辑非常复杂，这样的查询每次会占用连接很长的时间。

所以为了减轻数据库的压力，和提升查询效率，我们可以把数据放到内存缓存起来, 比如使用Redis

缓存适用于实时性不是特别高的业务，例如报表数据，一次查询要2分钟，但是一天只需要更新一次。

运行独立的缓存服务，属于架构层面的优化。

3.2 Mysql集群，主从复制

有一种提升可用性的手段，叫做冗余，也就是创建集群。
集群的话必然会面临一个问题，就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点，怎么让所有的节点数据保持一致？
这个时候我们需要用到复制技术(replication),被复制的节点称为master,复制的节点称为slave。slave本身也可以作为其他节点的数据来源，这个叫做级联复制。

在这里插入图片描述
MySQL的主从复制是怎么实现的呢？
MySQL所有更新语句都会记录到Server层的binlog。
有了这个binlog,从服务器会不断获取主服务器的binlog文件，然后解析里面的SQL语句，在从服务器上面执行一遍，保持主从的数据一致。

在这里插入图片描述
这里面涉及到三个线程：

  • I/O线程： 连接到master获取bin log,并且解析bin log写入中继日志。
  • log dump线程： 在Master节点上，是用来发送bin log给slave的。
  • 从库的SQL线程： 是用来读取relay log,把数据写入到数据库的。

注：（主从同步配置和原理，详细内容见Mycat专栏）
做了主从复制配置案之后，我们只把数据写入master节点，而读的请求可以分担到slave节点。我们把这种方案叫做读写分离。

对于读多写少的项目来说，读写分离对于减轻主服务器的访问压力很有用。在集群的架构中，所有的节点存储的都是相同的数据。如果单张表存储的数据过大的时候，比如一张表有上亿的数据，每天以百万的量级增加，单表的查询性能还是会大幅下降。这个时候我们应该怎么办呢？
这个时候就要用到分布式架构中的第二个重要的手段，叫做分片。把单个节点的数据分散到多个节点存储，减少存储和访问压力，这个就是分库分表。

3.3 分库分表

分库分表总体上可以分为两类。

  • 垂直分库，减少并发压力。
  • 水平分表，解决存储瓶颈。

垂直分库的做法，把一个数据库按照业务拆分成不同的数据库:
在这里插入图片描述
水平分库分表的做法，把单张表的数据按照一定的规则分布到多个数据库。

在这里插入图片描述

以上是架构层面的优化，可以用缓存，读写分离，分库分表。
这些措施都可以减轻服务端的访问压力，提升客户端的响应效率。

第三个环节:
解析器，词法和语法分析，主要保证语句的正确性，语句不出错就没问题。跳过。
第四步：优化器

4 SQL语句优化

4.1 SQL语句优化的配置

优化器的作用就是对我们的SQL语句进行优化分析，生成执行计划。
问题： 在我们做项目的时候，有时会收到DBA的邮件，里面列出了我们项目上几个耗时比较长的查询语句，让我们去优化，这些语句是从哪里来的呢？

我们的服务层每天执行了这么多SQL语句,它怎么知道哪些SQL语句比较慢呢？

第一步，我们要把SQL执行情况记录下来，用到服务端的慢查询日志。

4.1.1 慢查询日志slow query log

打开慢日志开关
因为开启慢查询日志是有代价的（跟binlog、optimizer-trace —样），所以它默认是关闭的：

show variables like 'slow_query%';

在这里插入图片描述
除了这个开关，还有一个参数，控制执行超过多长时间的SQL才记录到慢日志，默认是10秒。如果改成0秒的话就是记录所有的SQL。

show variables like '%long_query%';

参数的两种修改方式：

  1. set动态修改参数（重启后失效）。

    set @@global.slow_query_log=1;--1开启，0关闭，重启后失效
set @@global.long_query_time=3;--默认10秒，另开一个窗口后才会查到最新值 
show variables like '%long_query%'; 
show variables like '%slow_query%';

  2. 修改配置文件my.cnf
    以下配置定义了慢査询日志的开关、慢査询的时间、日志文件的存放路径。

    slowquerylog = ON 
long_query_time=2 
slow_query_log_file =/var/lib/mysql/localhost-slow.log

模拟慢査询：

select sleep(10);

査询user_innodb表的500万数据，不使用索引。

SELECT * FROM user_innodb where phone = '136';

慢日志分析

  1. 日志内容
less /var/lib/mysql/localhost-slow.log

#Time^ 2020-05-12T05:59:59.373938Z
#User@Host: root [root] @	[192.168.44.1] Id:	30
# Query_time: 229.418907 Lock_time: 0.000275 Rows_sent: 0
SET timestamp=1589263199;
DELETE from test_data;

有了慢查询日志,怎么去分析统计呢？比如哪条SQL语句的出现的慢查询次数最多,平均每次执行了多久？人工肉眼分析显然不可能。

  1. mysqldumpslow
    MySQL提供了 mysqldumpslow的工具，在MySQL的bin目录下。
    	mysqldumpslow —-help
	```

例如：查询用时最多的10条慢SQL:

mysqldumpslow -s t -t 10 -g 'select' /var/lib/mysql/localhost-slow.log
  • Count代表这个SQL执行了多少次；
  • Time代表执行的时间，括号里面是累计时间；
  • Lock表示锁定的时间，括号是累计；
  • Rows表示返回的记录数，括号是累计。

当然，有的时候查询慢，不一定是SQL语句的问题，也有可能是服务器状态的问题。 所以我们也要掌握一些查看服务器和存储引擎状态的命令。

其他系统命令

  1. show processlist 运行线程
show full processlist;

这是很重要的一个命令，用于显示用户运行线程。可以根据id号kill线程。也可以查表，效果一样：

select * from information_schema.processlist;

在这里插入图片描述

列含义
Id线程的唯一标志，可以根据它kill线程
User启动这个线程的用户，普通用户只能看到自己的线程
Host哪个IP端口发起的连接
db操作的数据库
Command线程的命令
https://dev.mysql.com/doc/refman/5.7/en/thread-commands.html
Time操作持续时间，单位秒
State线程状态，比如查询可能有 copying to tmp table, Sorting result, Sending data
https://dev.mysql.com/doc/refman/5.7/en/general-thread-states.html
InfoSQL语句的前100个字符，如果要查看完整的SQL语句，用SHOW FULL PROCESSLIST

show status 服务器运行状态
SHOW STATUS 用于查看MySQL服务器运行状态（重启后会清空）。

SHOW GLOBAL STATUS;

有session和global两种作用域，格式：参数-值。
可以用like带通配符过滤。

SHOW GLOBAL STATUS LIKE 'com_select';--查看 select 次数

show engine 存储引擎运行信息

show engine用来显示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息;事务的锁等待情况；线程信号量等待；文件IO请求；buffer pool统计信息。

例如

show engine innodb status;

开启InnoDB监控:
-开启标准监控和锁监控

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

很多开源的MySQL监控工具，其实他们的原理也都是读取的服务器、操作系统、MySQL服务的状态和变量。
现在我们已经知道哪些SQL慢了，为什么慢呢？慢在哪里？
MySQL提供了一个执行计划的工具。通过EXPLAIN我们可以模拟服务端执行SQL 查询语句的过程。通过这种方式我们可以分析语句或者表的性能瓶颈。

explain 可以分析 update、delete、insert 么？
MySQL 5.6.3 以前只能分析 SELECT; MySQL5.6.3 以后就可以分析 update、delete, insert 了。

4.2 EXPLAIN执行计划

我们先创建三张表。一张课程表，一张老师表，一张老师联系方式表（没有任何索引）。

DROP TABLE IF EXISTS course;
CREATE TABLE `course` ( `cid` int(3) DEFAULT NULL, `cname` varchar(20) DEFAULT NULL, `tid` int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher;

CREATE TABLE `teacher` ( `tid` int(3) DEFAULT NULL, `tname` varchar(20) DEFAULT NULL, `tcid` int(3) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TABLE IF EXISTS teacher contact;

CREATE TABLE `teacher_contact` (
`tcid` int(3) DEFAULT NULL, `phone` varchar(200) DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO 'course' VALUES (1, ‘mysql’，'1');
INSERT INTO 'course VALUES (2, jvm', '1');
INSERT INTO 'course VALUES (3, juc', '2');
INSERT INTO 'course VALUES (4, 'spring', '3');

INSERT INTO 'teacher' VALUES (1, 'xiaoming', '1');
INSERT INTO 'teacher VALUES (2, 'laowang' '2');
INSERT INTO 'teacher VALUES (3, 'tom' '3');

INSERT INTO 'teacher contact' VALUES ('1', '13688888888');
INSERT INTO 'teacher_contact' VALUES ('2', '18166669999');
INSERT INTO 'teacher contact' VALUES ('3','17722225555');

explain的结果有很多的字段，我们详细地分析一下。

4.2.1 id

id是查询序列编号，每张表都是单独访问的，一个SELECT就会有一个序号。
1. id值不同的时候，先査询id值大的 （先大后小） 。

-- 查询 mysql课程的老师手机号 
EXPLAIN
SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (SELECT tcid
              FROM teacher t
              WHERE t.tid = (SELECT c.tid
                             FROM course c
                             WHERE c.cname = 'mysql'));

查询顺序：course c—> teacher t -----> teacher_contact tc
在这里插入图片描述
先查课程表，再査老师表，最后查老师联系方式表。子査询只能以这种方式进行, 只有拿到内层的结果之后才能进行外层的查询。

2. id值相同时

-- 查询课程ID为2，或者联系表ID为3的老师 
EXPLAIN
SELECT t.tname, c.cname, tc.phone
FROM teacher t,
     course c,
     teacher_contact tc
WHERE tid = c.tid
  AND t.tcid = tc.tcid
  AND (c.cid = 2 OR tc.tcid = 3);

在这里插入图片描述
id值相同时：表的查询顺序是从上往下顺序执行。例如这次查询的id都是1 （说明子査询被优化器转换成了连接査询）
査询的顺序是teacher —（3条）— course c （4条）— teacher contact tc （3 条）。

在连接查询中，先查询的叫做驱动表，后查询的叫做被驱动表。
应该先查小表（得到结果少的表）还是大表（得到结果多的表）？ 我们肯定要把小表放在前面查询，因为它的中间结果最少。（小标驱动大表的思想）

3. 既有相同也有不同
如果ID有相同也有不同，就是ID不同的先大后小，ID相同的从上往下。

4.2.2 select type （查询类型）

这里并没有列举全部（其它：DEPENDENT UNION. DEPENDENT SUBQUERY.MATERIALIZED. UNCACHEABLE SUBQUERY. UNCACHEABLE UNION）。

下面列举了一些常见的查询类型：

  • SIMPLE：简单査询，不包含子查询，不包含关联査询union。
EXPLAIN SELECT * FROM teacher;

再看一个包含子査询的案例:

-- 查询mysql课程的老师手机号
EXPLAIN
SELECT tc.phone
FROM teacher_contact tc
WHERE tcid = (
    SELECT tcid
    FROM teacher t
    WHERE t.tid = (
        SELECT c.tid
        FROM course c
        WHERE c.cname = 'mysql')
);
  • PRIMARY： 子查询SQL语句中的主查询，也就是最外面的那层查询。
  • SUBQUERY： 子査询中所有的内层查询都是SUBQUERY类型的。
  • DERIVED： 衍生査询，表示在得到最终查询结果之前会用到临时表。例如：
-- 查询ID为1或2的老师教授的课程
EXPLAIN SELECT cr.cname
FROM(
SELECT * FROM course WHERE tid = 1
UNION
SELECT * FROM course WHERE tid = 2
)cr;

在这里插入图片描述
对于关联査询，先执行右边的table (UNION),再执行左边的table,类型是DERIVED

  • UNION： 用到了 UNION查询。同上例。
  • UNION RESULT： 主要是显示哪些表之间存在UNION査询。＜union2,3＞代表id=2和id=3的査询存在UNION。同上例。

4.2.3 type（连接类型）

所有的连接类型中，上面的最好，越往下越差。
在常用的链接类型中：system > const > eq_ref > ref > range > index > all
这里并没有列举全部(其他：fulltext 、ref_or_null 、 index_merger、unique_subquery、 index_subquery) 。

以上访问类型除了 all,都能用到索引。

  • const： 主键索引或者唯一索引，只能査到一条数据的SQL。
  • system： system是const的一种特例，只有一行满足条件，对于MylSAM、Memory的表, 只查询到一条记录，也是system。比如系统库的这张表(8.0的版本中系统表全部变成 InnoDB存储引擎了):
  • eq_ref： 通常出现在多表的join査询，被驱动表通过唯一性索引（UNIQUE或PRIMARY
    KEY）进行访问，此时被驱动表的访问方式就是eq_ref。eqref是除const之外最好的访问类型。

小结: 以上三种system, const, eq_ref,都是可遇而不可求的,基本上很难优化到这个状态。

  • ref: 查询用到了非唯一性索引，或者关联操作只使用了索引的最左前缀。
  • range: 索引范围扫描。如果where后面是between and 或 < 或 > 或 >= 或 <= 或 in 这些，type类型就为range。不走索引一定是全表扫描（ALL）,所以先加上普通索引。
  • index: Full Index Scan,查询全部索引中的数据（比不走索引要快）。
  • all： Full Table Scan,如果没有索引或者没有用到索引，type就是ALL。代表全表扫描。
  • NULL： 不用访问表或者索引就能得到结果，例如：
EXPLAIN select 1 from dual where 1=1;

小结：
一般来说，需要保证查询的type至少达到range级别，最好能达到ref。
ALL （全表扫描）和index （查询全部索引）都是需要优化的。

4.2.4 possible_key 、 key

可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。 possible_key可以有一个或者多个，可能用到索引不代表一定用到索引。 反过来，possible_key为空，key可能有值吗？

表上创建联合索引：

ALTER TABLE user innodb DROP INDEX comidx_name_phone;
ALTER TABLE user innodb add INDEX comidx_name_phone (name,phone);

执行计划（改成select name也能用到索引）:

explain select phone from user innodb where phone='126';

在这里插入图片描述
结论：是有可能的（这里是覆盖索引的情况）。
如果通过分析发现没有用到索引，就要检査SQL或者创建索引。

4. 2. 5 key_len

索引的长度（使用的字节数）。跟索引字段的类型、长度有关。
表上有联合索引 ： KEY ‘comidx_name_phone’ (‘name’,‘phone’)

explain select * from user innodb where name ='赵四';

key_len =1023,为什么不是 255 + 11=266 呢?
这里的索弓I只用到了 name字段，定义长度255。
utf8mb4编码1个字符4个字节。所以是255*4=1020。
使用变长字段varchar需要额外增加2个字节，允许NULL需要额外增加1个字节。所以一共是1023。

4.2.6 rows

MySQL认为扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。

4.2.7 filtered

这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例，它是一个百分比 。
在这里插入图片描述
如果比例很低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。

4.2.8 ref

使用哪个列或者常数和索引一起从表中筛选数据。

4.2.9 Extra

执行计划给出的额外的信息说明。

  • using index: 用到了覆盖索引，不需要回表。

  • using where： 使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要 在server层进行过滤（跟是否使用索引没有关系）。

  • Using index condition （索引条件下推）

  • using filesort： 不能使用索引来排序，用到了额外的排序（跟磁盘或文件没有关系）。需要优化。

  • using temporary： 用到了临时表。例如（以下不是全部的情况）：

    1、distinct非索引列

    EXPLAIN select DISTINCT(tid) from teacher t;

    2、group by非索引列

    EXPLAIN select tname from teacher group by tname;

    3、使用join的时候，group任意列（t表的结果）

    EXPLAIN select t.tid 
from teacher t 
join course c on t.tid = c.tid 
group by t.tid;

    需要优化，例如创建复合索引。

如果需要具体的cost信息，可以用:

EXPLAIN FORMAT=JSON。

如果觉得EXPLAIN还不够详细/可以用开启optimizer trace。

SHOW VARIABLES LIKE 'optimizer_trace'; 
set optimizer_trace='enabled=on';
select * from information_schema.optimizer_trace\G

总结: explain模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。

通过这种方式我们可以分析语句或者表的性能瓶颈。
分析出问题之后，就是对SQL语句的具体优化。
比如怎么用到索引，怎么减少锁的阻塞等待。

4.3 SQL与索引优化

当我们的SQL语句比较复杂，有多个关联和子査询的时候，就要分析SQL语句有没有改写的方法。

举个简单的例子，一模一样的数据:

-- 大偏移量的limit
select * from user_innodb limit 900000,10;
-- 改成先过滤ID,再limit
SELECT * FROM user_innodb WHERE id > 900000 LIMIT 10;

对于具体的SQL语句的优化，MySQL官网也提供了很多建议，这个是我们在分析具体的SQL语句的时候需要注意的，在以后的工作里面要去慢慢地积累的。
这一步是SQL语句的优化，目的是让SQL语句的的cost更小。

5 存储引擎

5.1 存储引擎的选择

为不同的业务表选择不同的存储引擎，例如:查询插入操作多的业务表，用MylSAMo。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。

5.2 分表或者分区

交易历史表：在年底为下一年度建立12个分区，每个月一个分区。
渠道交易表：分成：当日表、当月表、历史表，历史表再做分区。

5.3字段定义

原则：使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型：

5.3.1 整数类型

INT有6种类型，(tinyint,smallint,mediumint,int,integer,bigint,bit)不同的类型的最大存储范围是不一样的，占用的存储空间也是不
一样的。
举例：存储性别字段？用TINYINT,因为ENUM也是整数存储。

5.3.2 字符类型

变长情况下，varchar更节省空间，但是对于varchar字段，需要一个字节来记录长度。比如：联系地址。

固定长度的用char,不要用varchar。比如：行政区划编码。

5.3.3 非空

非空字段尽量定义成NOT NULL,提供默认值，或者使用特殊值、空串代替null。 NULL类型的存储、优化、使用都会存在问题。

5.3.4 不要用外键、触发器、视图

降低了可读性；影响数据库性能，应该把把计算的事情交给程序，数据库专心做存储；数据的完整性应该在程序中检查。

5.3.5 大文件存储

图片和音频、视频怎么存储？
不要用数据库存储图片（比如base64编码）或者用二进制编码，或者大文件。
把文件放在NAS上，数据库只需要存储URI （相对路径），在应用中配置NAS服务器地址。

5.3.6 表拆分或者字段冗余

表拆分：
将不常用的字段拆分出去，避免列数过多和数据量过大。
淘宝的商品表。商户信息表。
比如在业务系统中，要记录所有接收和发送的消息，这个消息是XML格式的，用 blob或者text存储，用来追踪和判断重复，可以建立一张表专门用来存储报文。

字段冗余：
合同表的客户姓名。

6.总结：优化体系

所以，如果在面试的时候再问到这个问题"你会从哪些维度来优化数据库"，你会怎么回答？

优化数据库的方面
SQL与索引
存储引擎与表结构
数据库架构
Mysql配置
硬件与操作系统

除了对于代码、SQL语句、表定义、架构、配置优化之外，业务层面的优化也不能忽视。举两个例子:

  1. 在某一年的双十一，为什么会做一个充值到余额宝和余额有奖金的活动？现在会推荐大家用花呗支付，而不是银行卡支付？
    因为使用余额或者余额宝付款是记录本地或者内部数据库，而使用银行卡付款，需要调用接口，操作内部数据库肯定更快。
  2. 在某一年的双十一，为什么在凌晨禁止查询今天之外的账单？为什么小鸡的饲料发放延迟了？
    这是一种降级措施，用来保证当前最核心的业务。
  3. 某银行的交易记录，只能按月份查询。
  4. 最近几年的双十一，为什么11月1日就开始了？变成了各种定金红包模式？
    预售分流。

在应用层面同样有很多其他的方案来优化，达到尽量减轻数据库的压力的目的，比如限流，或者引入MQ削峰，等等。

为什么同样用MySQL,有的公司可以抗住百万千万级别的并发，而有的公司几百个并发都扛不住，关键在于怎么用。所以，用数据库慢，不代表数据库本身慢，有的时候还要往上层去优化。

当然，如果关系型数据库解决不了的问题，我们可能需要用到搜索引擎或者大数据的方案了，并不是所有的数据都要放到关系型数据库存储。

7. 优化案例

服务端状态分析:
如果出现连接变慢，查询被阻塞，无法获取连接的情况。

  1. 重启！
  2. show processlist查看线程状态，连接数数量、连接时间、状态
  3. 查看锁的状态
  4. kill有问题的线程

对于具体的慢SQL:

一、分析查询基本情况
涉及到的表的表结构，字段的索引情况、每张表的数据量、查询的业务含义。
这个非常重要，因为有的时候你会发现SQL根本没必要这么写，或者表设计是有问题的。

二、 找出慢的原因

  1. 查看执行计划，分析SQL的执行情况，了解表访问顺序、访问类型、索引、扫描行数等信息。
  2. 如果总体的时间很长，不确定哪一个因素影响最大，通过条件的增减，顺序的调整，找出引起查询慢的主要原因，不断地尝试验证。

找到原因：比如是没有走索引引起的，还是关联查询引起的，还是。order by引起的。
找到原因之后：

三、 对症下药

  1. 创建索引或者联合索引
  2. 改写SQL,这里需要平时积累经验，例如：
    1） 使用小表驱动大表
    2） 用join来代替子查询
    3） not exist 转换为 left join IS NULL
    4） or 改成 union
    5） 如果结果集允许重复的话，使用UNION ALL代替UNION
    6） 大偏移的limit,先过滤再排序。
    如果SQL本身解决不了了，就要上升到表结构和架构了。
  3. 表结构（冗余、拆分、not null等）、架构优化（缓存、读写分离、分库分表）。
  4. 业务层的优化，必须条件是否必要。

掌握正确的调优思路，才是解决数据库性能问题的根本。

依剑行走天下
关注
专栏目录
【MySQL】史上最全的MySQL高性能优化总结
金鳞踏雨
12-19 1363
本人之前写过许多关于MySQL性能优化的文章，在这里会将它们做一个大串讲~ 这里主要是讲解MySQL底层数据结构，索引优化，事务及其相关特性，MVCC机制，BufferPool缓存机制等内容。
MySQL 性能优化总结
qq_38362274的博客
01-27 4648
1 优化思路 作为架构师或者开发人员，说到数据库性能优化，你的思路是什么样的？ 或者具体一点，如果在面试的时候遇到这个问题：你会从哪些维度来优化数据库， 你会怎么回答？ 我们在第一节课开始的时候讲了，这四节课的目标是为了让大家建立数据库的知识 体系，和正确的调优的思路。 我们说到性能调优，大部分时候想要实现的目标是让我们的查询更快。一个查询的 动...
MySQL 数据库性能优化之表结构优化（这是 MySQL数据库性能优化专题 系列的第二篇文章）
nbrremix的专栏
03-09 1133
很多人都将 数据库设计范式 作为数据库表结构设计“圣经”，认为只要按照这个范式需求设计，就能让设计出来的表结构足够优化，既能保证性能优异同时还能满足扩展性要求。殊不知，在N年前被奉为“圣经”的数据库设计3范式早就已经不完全适用了。这里我整理了一些比较常见的数据库表结构设计方面的优化技巧，希望对大家有用。 由于MySQL数据库是基于行（Row）存储的数据库，而数据库操作 IO 的时候是以 page
mysql性能优化- 数据库配置优化
最新发布
Flying_Fish_roe的博客
09-21 1666
在优化的过程中，监控数据库性能指标，确保配置的调整能够带来实际的性能提升。当有新连接请求时，MySQL 会尝试从缓存中获取现有的线程，而不是每次创建一个新的线程，这可以减少线程创建的开销。当查询需要访问表时，如果表不在缓存中，MySQL 会从磁盘中打开表，这会影响性能。连接管理也是 MySQL 性能优化的重要方面，特别是在高并发场景下，合理配置连接参数可以避免不必要的连接开销和资源浪费。日志记录对 MySQL 的性能有一定影响，特别是在事务繁重的环境中，日志配置对性能优化至关重要。用于关联查询的缓冲区。
Mysql性能优化总结
tc979907461的博客
03-24 178
1、查找出慢查询sql，在一下配置文件中 C:\ProgramData\MySQL\MySQL Server 8.0\my.ini 慢查询的日志： 慢查询的常用分析工具：Mysqldumpslow 需要拥有的功能： 排序，先优化时间消耗多的，再优化消耗少的 常用的优化方式： 服务器硬件优化 mysql服务器优化 sql本身优化 数据查询数量大时尽量使用连接查询代替子查询。 反范式设计优化：...
干货：MySql性能优化总结详解
人生不怕起点低，就怕没追求
10-20 1893
MySQL数据库作为目前流行的数据库大量应用于PHP、JAVA、Python等Web语言开发项目中，大多数情况下，数据库的操作性能成为整个应用的性能瓶颈。 数据库的性能是程序员需要去关注的事情，当设计数据库表结构以及操作数据库（尤其是查询数据时），都需要注意数据操作的性能。 优化目标 01 减少IO次数 IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是IO操作所占用的，减少IO次数是SQL优化中需要第一优先考虑，当然，也是收效最明显的优化手段。 02 降.
mysql 结构优化建议_MySQL性能优化-表结构优化
weixin_39968410的博客
02-18 154
表结构优化是MySQL性能优化中的重要一环，性能优化从设计阶段就应该被考虑，良好的表结构设计从一开始就为系统的高性能打下了基础。本文整理了MySQL表结构优化的一些原则、经验和技巧。一、选择合适的存储引擎MySQL存储引擎建议使用InnoDB，支持事务，支持行级锁，数据更安全。MySQL的其他引擎在实际应用中并不多，比如曾经风光无限的MyISAM引擎随着时代的发展逐渐没落，MySQL组复制Grou...
史上最牛逼的mysql性能优化方案
02-28
MySQL性能优化是一个复杂而关键的任务，它涉及到...综上所述，MySQL性能优化是一个全方位的过程，涉及数据库设计、索引策略、SQL编写、系统配置等多个环节。每个环节的优化都可以显著提升数据库的处理能力和响应速度。
史上最全MySQL⾯试题及答案
03-14
本文总结了MySQL面试题及答案，涵盖了MySQL的基本概念、事务、索引、锁、优化等方面的知识点。 1. 事务的基本特征 事务是指一个数据库操作序列，具有原子性、一致性、隔离性和持久性四大特征。原子性是指一个事务...
重磅-史上最全的Java面试文档总结(jvm,mybatis,mysql优化算法)等总结文档大合集（300份）.zip
03-20
重磅推荐，史上最全的Java面试文档总结(jvm,mybatis,mysql优化算法)等总结文档大合集，共300份。 ActiveMQ消息中间件面试专题 Dubbo面试及答案（上） Dubbo面试专题及答案（下） Java并发体系知识导图笔记.xmind ...
史上最全面-最实用的MySQL优化方法培训材料
02-28
MySQL优化是提升数据库性能的关键步骤，它涉及到多个层面，包括系统层、数据库层以及SQL语句的优化。本文将深入探讨这些方面的优化方法，帮助你掌握全面而实用的MySQL优化技巧。 首先，定位问题是优化的第一步。当...
MySQL性能管理及架构设计（三）：SQL查询优化、分库分表 - 完结篇
HiterCoder
03-30 495
上一篇：MySQL性能管理及架构设计（二）：数据库结构优化、高可用架构设计、数据库索引优化一、SQL查询优化（重要）1.1 获取有性能问题SQL的三种方式通过用户反馈获取存在性能问题的SQL；通过慢查日志获取存在性能问题的SQL；实时获取存在性能问题的SQL；1.1.2 慢查日志分析工具相关配置参数：slow_query_log # 启动停止记录慢查日志，慢查询日志默认是没有开启的可以在配置文件中...
数据库索引创建与优化
weixin_34038652的博客
01-29 89
2019独角兽企业重金招聘Python工程师标准>>> ...
数据库索引的创建以及索引的优化
qq_43750656的博客
06-16 451
什么是索引？ 在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中信息。当表中有大量记录时，若要对表进行查询，第一种
MySQL连接过慢优化
偷懒的加菲
02-24 301
MySQL连接过慢优化
数据库索引的设计与优化（一）
weixin_47103265的博客
05-12 629
1、索引的原理 首先要先了解索引是什么，例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。 2、索引的分类 索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的
（二）数据库索引优化
roothahh的博客
04-17 1229
有关树的的基本知识可参考：二叉树抽象数据类型及遍历实现 （一）索引的数据结构 使用索引的原因：避免全表扫描，提高检索效率 可以作为索引：主键，唯一键 1.普通查找： 二叉搜索树：有左子树和右子树 优点：利用二叉查找；时间复杂度为O（log n） 缺点：数据库要进行插入和删除，当对其操作后其高度可能会发生变化，时间复杂度增加，I/O访问时间增加，性能下降； 具体代码实现可参考：二叉搜索树，平衡二叉树 二叉树主要用于组织内存中规模较小的目录，在检索过程中平均要对外存使用log2 n次访问 对于较大的
20+条MySQL性能优化的最佳经验
林的小站
03-18 574
今天，数据库的操作越来越成为整个应用的性能瓶颈了，这点对于Web应用尤其明显。关于数据库的性能，这并不只是DBA才需要担心的事，而这更是我们程序员需要去关注的事情。 当我们去设计数据库表结构，对操作数据库时（尤其是查表时的SQL语句），我们都需要注意数据操作的性能。这里，我们不会讲过多的SQL语句的优化，而只是针对MySQL这一Web应用最多的数据库。希望下面的这些优化技巧对你有用。
解决MySQL打开或创建连接慢的问题
dreamofheart1的博客
06-27 1778
需要注意的是，关闭 DNS 解析功能可能会影响 MySQL 的某些功能，例如基于主机名的授权、基于主机名的日志记录等。因此，在关闭 `skip-name-resolve` 参数之前，需要仔细评估其对系统的影响，并采取相应的措施来保证系统的稳定性和安全性。当 `skip-name-resolve` 参数被设置为 `ON` 时，MySQL 将不再对客户端连接的主机名进行 DNS 解析，而直接使用客户端连接中的 IP 地址进行连接。如果需要关闭客户端的 DNS 解析功能，需要在客户端的配置文件中进行相应设置。
"史上最牛逼的MySQL性能优化方案：表结构、索引、SQL语句优化与配置监控
“史上最牛逼的mysql性能优化方案”是一个包括多个方面的综合性优化方案，涉及数据表结构的设计与性能优化、索引优化、多表联查优化、并发和阻塞问题、日志库优化、MySQL配置优化、CentOS参数优化、MySQL服务器监控...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值