我们说到性能调优,大部分时候想要实现的目标是让我们的査询更快。一个査询的流程又是由很多个环节组成的,每个环节都会消耗时间。
我们要减少查询所消耗的时间,就要从每一个环节入手。
1. 连接——配置优化
第一个环节是客户端连接到服务端,连接这一块有可能会出现什么样的性能问题?有可能是服务端连接数不够导致应用程序获取不到连接。
比如我们遇到过的 Mysql:error 1040: Too many connections的错误。这个是超过了服务端设置的最大并发连接数。
我们可以从两个方面来解决连接数不够的问题:
1、从服务端来说,我们可以增加服务端的可用连接数。
如果有多个应用或者很多请求同时访问数据库,连接数不够的时候,我们可以:
(1) 增加可用连接数,修改max_connections的大小:
show variables like 'max_connections';//修改最大连接数,当有多个应用连接的时候
(2) 或者及时释放不活动的连接。交互式和非交互式的客户端的默认超时时间都是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型号这些,更不是我们开发人员关注的重点,这个我们就不做过多的介绍了。
那么,除了合理设置服务端的连接数和客户端的连接池大小之外,我们还有哪些减少客户端使用的连接数的方案呢?相信大家都能脱口而出,引入缓存提升查询速度。
2. 缓存——架构优化
2.1 缓存
在系统里面有一些很慢的查询,要么是数据量大,要么是关联的表多,要么是计算逻辑非常复杂,这样的查询每次会占用连接很长的时间。
所以为了减轻数据库的压力,和提升查询效率,我们可以把数据放到内存缓存起来, 比如使用Redis。
缓存适用于实时性不是特别高的业务,例如报表数据,一次查询要2分钟,但是一 天只需要更新一次。
这种运行独立的缓存服务,属于架构层面的优化。
我们还可以从减少单台数据库服务器的读写压力方面下手。
2.2 数据库集群,主从复制
集群的话必然会面临一个问题,就是不同的节点之间数据一致性的问题。如果同时读写多台数据库节点,怎么让所有的节点数据保持一致?
这个时候我们需要用到复制技术(replication),被复制的节点称为master,复制的节点称为slave。slave本身也可以作为其他节点的数据来源,这个叫做级联复制。
MySQL的主从复制是怎么实现的呢?
我们知道,MySQL所有更新语句都会记录到Server层的bin log 日志中。所以有了这个bin log,从服务器会不断获取主服务器的bin log文件,然后解析里面的SQL语句,在从服务器上面执行一遍,保持主从的数据一致。
这里面涉及到三个线程,连接到master获取binlog,并且解析binlog写入中继日志,这个线程叫做I/O线程。
Master节点上有一个log dump线程,是用来发送binlog给slave的。 从库的SQL线程,是用来读取relay log,把数据写入到数据库的。
做了主从复制配置之后,我们只把数据写入master节点,而读的请求可以分担到slave节点。
这种方案就叫做读写分离。
对于读多写少的项目来说,读写分离对于减轻主服务器的访问压力很有用。
在集群的架构中,所有的节点存储的都是相同的数据。如果单张表存储的数据过大的时候,比如一张表有上亿的数据,每天以百万的量级增加,单表的查询性能还是会大幅下降。这个时候我们应该怎么办呢?
这个时候就要用到分布式架构中的第二个重要的手段,叫做分片。把单个节点的数据分散到多个节点存储,减少存储和访问压力,这个就是分库分表。
2.3 分库分表
分库分表总体上可以分为两类。具体内容可以通过MyCat和Sharding JDBC实现。
垂直分库,减少并发压力。水平分表,解决存储瓶颈。
垂直分库的做法,把一个数据库按照业务拆分成不同的数据库:
水平分库分表的做法,把单张表的数据按照一定的规则分布到多个数据库。
以上是架构层面的优化,可以用缓存,读写分离,分库分表。
这些措施都可以减轻服务端的访问压力,提升客户端的响应效率。
第三个环节:
解析器,词法和语法分析,主要保证语句的正确性,语句不出错就没问题,所以没啥优化的地方。
第四步:优化器,这是我们重点需要关注的地方。
3. 优化器——SQL语句分析与优化
优化器的作用就是对我们的SQL语句进行优化分析,生成执行计划。
在我们做项目的时候,有时会收到DBA的邮件,里面列出了我们项目上几个耗时比较长的查询语句,让我们去优化,这些语句是从哪里来的呢?
我们的服务层每天执行了这么多SQL语句,它怎么知道哪些SQL语句比较慢呢?
第一步,我们要把SQL执行情况记录下来,用到服务端的慢查询日志。
3.1 慢查询日志 slow query log
3.1.1 打开慢日志开关
因为开启慢查询日志是有代价的(跟binlog、optimizer-trace —样),所以它默认是关闭的:
show variables like 'slow_query%';
除了这个开关,还有一个参数,控制执行超过多长时间的SQL才记录到慢日志,默认是10秒。如果改成0秒的话就是记录所有的SQL。
show variables like '%long_query%';
参数的两种修改方式:
- 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%';
- 修改配置文件 my.cnf
以下配置定义了慢査询日志的开关、慢査询的时间、日志文件的存放路径。
slow_query_log = ON
long_query_time=2
slow_query_log_file =/var/lib/mysql/localhost-slow.log
3.1.2 慢日志分析
- 日志内容
less /var/lib/mysql/localhost-slow.log
有了慢查询日志,怎么去分析统计呢?比如那条SQL语句的出现的慢查询次数最多,平均每次执行了多久?人工肉眼分析显然不可能。
- 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语句的问题,也有可能是服务器状态的问题。 所以我们也要掌握一些查看服务器和存储引擎状态的命令。
3.1.3 其他系统命令
show processlist 运行线程
show full processlist;
这是很重要的一个命令,用于显示用户运行线程。可以根据id号kill线程。
也可以查表,效果一样:
select * from information_schema.processlist;
列 | 含义 |
Id | 线程的唯一标志,可以根据它kill线程 |
User | 启动这个线程的用户,普通用户只能看到自己的线程 |
Host | 哪个IP端口发起的连接 |
db | 操作的数据库 |
Command | 线程的命令 |
Time | 操作持续时间,单位秒 |
State | 线程状态,比如查询可能有 copying to tmp table, Sorting result, Sending data |
Info | SQL 语句的前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用来显示存储引擎的当前运行信息,包括事务持有的表锁、行锁信息;事务的锁等待情况;线程信号量等待;文件I/O请求;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 查询语句的过程。通过这种方式我们可以分析语句或者表的性能瓶颈。
3.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;
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;
DROP TABLE
IF
EXISTS teacher_contact;
CREATE TABLE `teacher_contact` ( `tcid` INT ( 3 ) DEFAULT NULL, `phone` VARCHAR ( 200 ) DEFAULT NULL ) ENGINE = INNODB;
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, 'jack', 1 );
INSERT INTO teacher
VALUES
( 2, 'tom', 2 );
INSERT INTO teacher
VALUES
( 3, 'mic', 3 );
INSERT INTO teacher_contact
VALUES
( 1, '13688888888' );
INSERT INTO teacher_contact
VALUES
( 2, '18166669999' );
INSERT INTO teacher_contact
VALUES
( 3, '17722225555' );
explain的结果有很多的字段,我们详细地分析一下。
3.2.1 id
id是查询序列编号,每张表都是单独访问的,一个SELECT就会有一个序号。
id值不同
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.
先查课程表,再査老师表,最后查老师联系方式表。子査询只能以这种方式进行, 只有拿到内层的结果之后才能进行外层的查询。
id值相同
-- 查询课程ID为2,或者联系表id为3的老师
EXPLAIN SELECT
t.tname,
c.cname,
tc.phone
FROM
teacher t,
course c,
teacher_contact tc
WHERE
t.tid = c.cid
AND t.tcid = tc.tcid
AND (
c.cid = 2
OR tc.tcid = 3
)
id值相同时, 表的查询顺序是从上往下顺序执行。例如这次查询的id都是1(说明子查询被优化器转换成了连接查询),查询的顺序是 teacher t (3条) —— course c(4条) ——teacher_contect tc(3 条)。
在连接查询中,先查询的叫做驱动表,后查询的叫做被驱动表。
应该先查小表(得到结果少的表)还是大表(得到结果多的表)? 我们肯定要把小表放在前面查询,因为它的中间结果最少。
既有相同又有不同
如果ID有相同也有不同,就是ID不同的先大后小,ID相同的从上往下。
3.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
衍生査询,表示在得到最终查询结果之前会用到临时表。例如:
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。同上例。
3.2.3 type 连接类型
有的连接类型中,上面的最好,越往下越差。
在常用的链接类型中:system > const > eq ref > ref > range > index > all
这里并没有列举全部(其他:fulltext ,ref_or_null,index_merger,unique_subquery,index_subquery)。
以上访问类型除了 all,都能用到索引。
const
主键索引或者唯一索引,只能査到一条数据的SQL。
DROP TABLE IF EXISTS single_data;
CREATE TABLE single_data(
id int(3) PRIMARY KEY,
content varchar(20)
);
insert into single_data values(1,'a');
EXPLAIN SELECT * FROM single_data a where id = 1;
system
system是const的一种特例,只有一行满足条件,对于MylSAM、Memory的表,只查询到一条记录,也是system。比如系统库的这张表(8.0的版本中系统表全部变成 InnoDB存储引擎了):
EXPLAIN SELECT * FROM mysql.proxies_priv;
eq_ref
通常出现在多表的join査询,被驱动表通过唯一性索引(UNIQUE或PRIMARY KEY)进行访问,此时被驱动表的访问方式就是eq_ref。
eq_ref是除const之外最好的访问类型。
先删除teacher表中多余的数据,teacher_contact有3条数据,teacher表有3条数据。
为teacher_contact表的tcid (第一个字段)创建主键索引。
ALTER TABLE teacher_contact ADD PRIMARY KEY(tcid);
执行以下SQL语句:
explain select t.tcid from teacher t,teacher_contact tc where t.tcid = tc.tcid;
此时的执行计划(先大后小,从上往下,tc是被驱动表。tc表是eq_ref):被驱动表用主键索引进行访问。
以上三种system,const, eq_ref,都是可遇而不可求的’基本上很难优化到这个状态。
ref
查询用到了非唯一性索引,或者关联操作只使用了索引的最左前缀。
为teacher表的tcid (第三个字段)创建普通索引。
ALTER TABLE teacher ADD INDEX idx_tcid (tcid);
例如:使用tcid上的普通索引査询:
explain SELECT * FROM teacher where tcid = 3;
range
索引范围扫描。
如果where后面是between and或<或 > 或 > = 或 <= 或in这些,type类型就为range。
不走索引一定是全表扫描(ALL),所以先加上普通索引。
ALTER TABLE teacher ADD INDEX idx_tid (tid);
执行范围查询(字段上有普通索引):
EXPLAIN SELECT * FROM teacher t WHERE t.tid <3;
-- 或
EXPLAIN SELECT * FROM teacher t WHERE tid BETWEEN 1 AND 2;
IN查询也是range (字段有主键索引)
EXPLAIN SELECT * FROM teacher_contact t WHERE tcid in (1,2,3);
index
Full Index Scan,查询全部索引中的数据(比不走索引要快)。
EXPLAIN SELECT tid FROM teacher;
all
Full Table Scan,如果没有索引或者没有用到索引,type就是ALL。代表全表扫描。
NULL
不用访问表或者索引就能得到结果,例如:
EXPLAIN select 1 from dual where 1=1;
一般来说,需要保证查询的type至少达到range级别,最好能达到ref。
ALL (全表扫描)和 index (查询全部索引)都是需要优化的。
3.2.4 possible_key、 key
可能用到的索引和实际用到的索引。如果是NULL就代表没有用到索引。 possible_key可以有一个或者多个,可能用到索引不代表一定用到索引。 反过来,possible_key为空,key可能有值吗?
表上创建联合索引:
ALTER TABLE user_innodb DROP INDEX comidx_namejDhone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
执行计划(改成select name也能用到索引):
explain select phone from user_innodb where phone='126';
结论:是有可能的(这里是覆盖索引的情况)。
如果通过分析发现没有用到索引,就要检査SQL或者创建索引。
3.2.5 key_len
索引的长度(使用的字节数)。跟索引字段的类型、长度有关。
表上有联合索弓I : KEY ‘comidx_name_phone’ (‘name’,‘phone’)
explain select * from user_innodb where name ='能轭'
这里的key_len =1023,为什么不是 255 + 11=266 呢?
这里的索引只用到了 name字段,他的定义长度是255。
utf8mb4编码1个字符4个字节。所以是255*4=1020。
使用变长字段varchar需要额外增加2个字节,允许NULL需要额外增加1个字节。
所以一共是1023。
3.2.6 rows
MySQL认为扫描多少行才能返回请求的数据,是一个预估值。一般来说行数越少越好。
3.2.7 filtered
这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,它是一个百分比。
如果比例很低,说明存储引擎层返回的数据需要经过大量过滤,这个是会消耗性能的,需要关注。
3.2.8 ref
使用哪个列或者常数和索引一起从表中筛选数据。
3.2.9 Extra
执行计划给出的额外的信息说明。
using index
用到了覆盖索引,不需要回表。
EXPLAIN SELECT tid FROM teacher ;
using where
使用了 where过滤,表示存储引擎返回的记录并不是所有的都满足查询条件,需要 在server层进行过滤(跟是否使用索引没有关系)。
EXPLAIN select * from user_innodb where phone = '15506587094';
Using index condition (索引条件下推)
属于自动优化,默认开启。
using filesort
不能使用索引来排序,用到了额外的排序(跟磁盘或文件没有关系),需要优化。
将 order by id去掉后就没有了。
EXPLAIN select * from user_innodb where name ='能轭'order by id;
using temporary
用到了临时表。例如(以下不是全部的情况):
1、distinct非索引列(确定tid字段是否有索引)
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;
这种都是需要优化的,例如创建复合索引。
总结一下:模拟优化器执行SQL查询语句的过程,来知道MySQL是怎么处理一条SQL语句的。
通过这种方式我们可以分析语句或者表的性能瓶颈。分析出问题之后,就是对SQL语句的具体优化。比如怎么用到索引,怎么减少锁的阻塞等待。
3.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更小。
4. 存储引擎
4.1 存储引擎的选择
为不同的业务表选择不同的存储引擎,例如:查询插入操作多的业务表,用MylSAM。 临时数据用Memeroy。常规的并发大更新多的表用InnoDB。
4.2 分表或者分区
交易历史表:在年底为下一年度建立12个分区,每个月一个分区。
渠道交易表:分成:当日表、当月表、历史表,历史表再做分区。
4.3 字段定义
原则:使用可以正确存储数据的最小数据类型。
为每一列选择合适的字段类型:
4.3.1 整数类型
INT有6种类型,不同的类型的最大存储范围是不一样的,占用的存储空间也是不一样的。
举例:如何存储性别字段?我们可以直接使用TINYINT类型。
4.3.2 字符类型
变长情况下,varchar更节省空间,但是对于varchar字段,需要一个字节来记录长度。比如:家庭地址。
固定长度的用char,不要用varchar。比如:行政区划编码。
4.3.3 非空
非空字段尽量定义成NOT NULL,提供默认值,或者使用特殊值、空串代替null。
NULL类型的存储、优化、使用都会存在一些问题。
4.3.4 不要用外键、触发器、视图
降低了可读性;影响数据库性能,应该把把计算的事情交给程序,数据库专心做存储;数据的完整性应该在程序中检查。
4.3.5 避免大文件存储
不要用数据库存储图片(比如base64编码)或者大文件。
我们应该把文件放在云上,数据库只需要存储URI (相对路径),在应用中配置云服务器地址即可。
4.3.6 表拆分或者字段冗余
表拆分:
将不常用的字段拆分出去,避免列数过多和数据量过大。
比如在业务系统中,要记录所有接收和发送的消息,这个消息是XML格式的,用 blob或者text存储,用来追踪和判断重复,可以建立一张表专门用来存储报文。
字段冗余:
合同表中正常存储一个客户ID,但是往往需要显示客户姓名,这个时候我们可以在表中冗余一个客户姓名字段,避免联合查询。
5. 总结
最后,如果在面试的时候问到这个问题“你会从哪些维度来优化数据库”,你会怎么回答?
我们应该按照如下层次结构回答:
- SQL与索引
- 存储引擎与表结构
- 系统架构
- MySQ参数配置
- 硬件与操作系统
越往下成本越高,所以我们基本都在第一层进行优化。
除了对于代码、SQL语句、表定义、架构、配置优化之外,业务层面的优化也不能忽视。
在应用层面同样有很多其他的方案来优化,达到尽量减轻数据库的压力的目的,比如限流,或者引入MQ削峰,等等等等。
为什么同样用MySQL,有的公司可以抗住百万千万级别的并发,而有的公司几百个 并发都扛不住,关键在于怎么用。所以,用数据库慢,不代表数据库本身慢,有的时候还要往上层去优化。
当然,如果关系型数据库解决不了的问题,我们可能需要用到搜索引擎或者大数据的方案了,并不是所有的数据都要放到关系型数据库存储。
6. 优化案例
服务端状态分析:
如果出现连接变慢,查询被阻塞,无法获取连接的情况。
- 重启!
- show processlist查看线程状态,连接数数量、连接时间、状态
- 查看锁的状态
- kill有问题的线程
对于具体的慢SQL:
一、分析查询基本情况
涉及到的表的表结构,字段的索引情况、每张表的数据量、查询的业务含义。
这个非常重要,因为有的时候你会发现SQL根本没必要这么写,或者表设计是有问题的。
二、找出慢的原因
- 查看执行计划,分析SQL的执行情况,了解表访问顺序、访问类型、索引、扫描行数等信息。
- 如果总体的时间很长,不确定哪一个因素影响最大,通过条件的增减,顺序的调整,找出引起查询慢的主要原因,不断地尝试验证。
找到原因:比如是没有走索引引起的,还是关联查询引起的,还是order by引起的。
找到原因之后:
三、对症下药
- 创建索引或者联合索引
- 改写SQL,这里就需要平时积累经验了,例如:
- 使用小表驱动大表
- 用join来代替子查询
- not exist 转换为 left join IS NULL
- or 改成 union
- 如果结果集允许重复的话,使用UNION ALL代替UNION
- 大偏移的limit,先过滤再排序。
如果SQL本身解决不了了,就要上升到表结构和架构了。
- 表结构(冗余、拆分、not null等)、架构优化(缓存读写分离分库分表)。
- 业务层的优化。
掌握正确的调优思路,才是解决数据库性能问题的根本。