目录
数据库调优的目的
响应更快,吞吐量更大。
性能问题的反馈方式
用户的反馈
日志分析
服务器资源使用监控
数据库内部状况监控(活动会话监控,事务、锁等待监控)
数据库调优的思路
1,选择适合的DBMS
DBMS的选择关系到了后面的整个设计过程,所以第一步就是要选择适合的DBMS。
对事务性处理以及安全性要求高,可以选择商业的数据库产品。
SQL Server:单表可存储上亿条数据。如果数据表设计得好,即使不采用分库分表的方式,
查询效率也不差。
MySQL:进行事务处理的话可以选择 InnoDB,非事务处理可以选择 MyISAM。MySQL可以
根据不同表的使用需求,选择不同的存储引擎。
NoSQL:列式存储数据库可以大幅度降低系统的I/O,适合于分布式文件系统和OLAP,但如
果数据需要频繁地增删改,那么列式存储就不太适用了。
2,优化表设计
表结构要尽量遵循第三范式的原则,这样可以让数据结构更加清晰规范,减少冗余字段,同
时也减少了在更新,插入和删除数据时等异常情况的发生。
如果分析查询应用比较多,尤其是需要进行多表联查的时候,可以采用反范式进行优化。反
范式采用空间换时间的方式,通过增加冗余字段提高查询的效率。
表字段的数据类型选择,关系到了查询效率的高低以及存储空间的大小。一般来说,如果字
段可以采用数值类型就不要采用字符类型;字符长度要尽可能设计得短一些。针对字符类型来说,
当确定字符长度固定时,就可以采用 CHAR 类型;当长度不固定时,通常采用 VARCHAR 类型。
3,SQL查询优化
SQL查询优化,可以分为逻辑查询优化和物理查询优化。
逻辑查询优化
对SQL语句进行等价变换,对查询进行重写,通过改变SQL语句的内容让SQL执行效率更高效。
SQL的查询重写包括了子查询优化、等价谓词重写、视图重写、条件简化、连接消除和嵌套
连接消除等。
重写查询的数学基础就是关系代数。
例子:EXISTS、IN子查询的时候,会根据小表驱动大表的原则选择适合的子查询。
物理查询优化
核心是高效地建立索引,并通过这些索引来做各种优化。
详见索引篇章;
4,使用Redis、Memcached缓存
对于查询响应要求高的场景(响应时间短,吞吐量大),可以考虑内存数据库。
传统的 RDBMS 都是将数据存储在硬盘上,而内存数据库则存放在内存中,查询起来要快得多。
5,库级优化
库级优化是站在数据库的维度上进行的优化策略,比如控制一个库中的数据表数量。
常用方法:
主从数据库;
读写分离:采用主从架构优化读写策略。
分表分库:采用垂直分表的形式,就是将一张数据表分拆成多张表,采用水平拆分的方
式,就是将单张数据量大的表按照某个属性维度分成不同的小表。
定位SQL执行慢的原因
1,观察服务器的状态是否存在周期性的波动
如果存在周期性波动,有可能是周期性节点的原因,比如双十一、促销活动等。这种情况加
缓存,或者更改缓存失效策略。
2,开启慢查询定位执行慢的SQL语句
如果缓存策略没有解决,或者不是周期性波动的原因,我们就需要进一步分析查询延迟和卡
顿的原因。
通过设置long_query_time参数定义“慢”的阈值,如果 SQL 执行时间超过long_query_time,
则会认为是慢查询。
查看和设置慢查询开关:
mysql > show variables like '%slow_query_log';
mysql > set global slow_query_log='ON';
查看和设置慢查询时间阈值:
mysql > show variables like '%long_query_time%';
mysql > set global long_query_time = 5;
使用mysqldumpslow工具统计慢查询日志(需要先安装好 Perl)
perl mysqldumpslow.pl -s t -t 2 "D:\Data\sql-slow.log"
-s:采用 order 排序的方式,排序方式:
c(访问次数)、t(查询时间)、l(锁定时间)、r(返回记录)、ac(平均查询次数)、al(平均锁定时间)、ar(平均返回记录数)和 at(平均查询时间)。其中 at 为默认排序方式。
-t:返回前 N 条数据 。
-g:后面可以是正则表达式,对大小写不敏感。
3,分析执行慢的SQL语句
找到执行慢的SQL语句,针对性地用EXPLAIN查看对应SQL语句的执行计划,使用
SHOW PROFILE查看SQL中每一个步骤的时间成本。
分析SQL查询慢是因为执行时间长,还是等待时间长。
EXPLAIN分析慢查询
例子:EXPLAIN SELECT * FROM table
EXPLAIN显示数据表的读取顺序、SELECT子句的类型、数据表的访问类型、可使用的索
引、实际使用的索引、使用的索引长度、上一个表的连接匹配条件、被优化器查询的行的数量以及
额外的信息(比如是否使用了外部排序,是否使用了临时表等)等。
SQL 执行的顺序是根据 id 从大到小执行的,也就是 id 越大越先执行,当 id 相同时,从上到
下执行。
Type类型:
数据表的访问类型:
all
没有索引;
EXPLAIN SELECT id, name, age FROM table1 JOIN table2 on table1.id = table2.f_id;
index
id,name,age创建组合索引;
EXPLAIN SELECT id, name, age FROM table1;
Extral列显示Using index,说明采用了索引覆盖,也就是索引可以覆盖所需的SELECT 字
段,就不需要进行回表,这样就减少了数据查找的开销。
range
id创建索引;
EXPLAIN SELECT * from table1 where id > 300 and id < 350;
range表示采用了索引范围扫描,从这一级别开始,索引的作用会越来越明显,因此我们需要
尽量让SQL查询可以使用到range这一级别及以上的type访问方式。
index_merge
id创建索引;
num创建索引;
EXPLAIN SELECT id, num, name, age FROM table1 WHERE id = 123 OR num = 456;
index_merge 说明查询同时使用了两个或以上的索引,最后取了交集或者并集。
ref
EXPLAIN SELECT id, num, name, age FROM table1 WHERE id = 123;
ref类型表示采用了非唯一索引,或者是唯一索引的非唯一性前缀。ref列中显示const,表示连
接匹配条件是常量,用于索引列的查找。
eq_ref
EXPLAIN SELECT id, num, name, age FROM table1 JOIN table2 WHERE table1.id = table2.f_id;
eq_ref 类型是使用主键或唯一索引时产生的访问方式,通常使用在多表联查中。
const
EXPLAIN SELECT id, num, name, age FROM table1 WHERE table1.id = 123;
const类型和eq_ref都使用了主键或唯一索引,不过这两个类型有所区别,const 是与常量进行
比较,查询效率会更快,而 eq_ref 通常用于多表联查中。
通常希望执行计划至少可以使用到 range 级别以上的连接方式,如果只使用到了all或者index
连接方式,可以从 SQL 语句和索引设计的角度上进行改进。
Show Profile分析慢查询
查询和设置profiling
mysql> select @@profiling;
mysql> set profiling=1;
0代表关闭,1代表打开:
查看当前会话所产生的所有 profiles:
mysql>show profiles
获取上一次查询的执行时间:
mysql> show profile;