数据库调优笔记

原创

青阳

修改于 2021-09-26 09:14:37

7890

修改于 2021-09-26 09:14:37

文章被收录于专栏：玩转云服务玩转云服务

慢查询

处理步骤

判断慢查询产生（CPU负载、IO读写、执行时间）
打开慢查询日志或使用分析工具（mysqldumpslow等）
选择调优方式

性能调优

应用程序优化

减少数据库连接次数，空间换时间
拆分复杂语句，多表分别查询

SQL语句优化

避免使用 SELECT *
避免负向查询（NOT != <> !< !> MOT IN NOT LIKE）和%开头的like（前导模糊查询）–会导致全表扫描
避免大表使用JOIN查询和子查询–会产生临时表，消耗较多CPU和内存，影响数据库性能
确定只有一条记录返回，可以加上limit 1
可以使用 exist 和 not exist 代替 in 和 not in
WHERE 语句中对字段做计算操作、使用函数、类型转换等会导致无法命中索引

表结构优化

字段类型优化，使用合适的类型（字段长度，避免 text，使用 not null）
合理使用索引，去除无用索引
读写分离和分库分表
避免使用触发器，存储过程、外键等

硬件和数据库配置优化

集群和分布式部署，减少单台机器压力
升级机器配置
使用合适的储存引擎，表锁、行锁的选择
增加缓存系统

全文索引

MySQL

版本支持

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

创建

建表时创建：

create table TABLE_NAME(
    id int NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    name varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_name_fulltext(content,name)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

已存在的表上创建

create fulltext index content_name_fulltext on fulltext_test(content,name);

或

alter table fulltext_test add fulltext index content_name_fulltext(content,name);

删除

drop index content_name_fulltext on fulltext_test;

或

alter table fulltext_test drop index content_name_fulltext;

explain 语句的应用

使用 explain 可以得到以下信息

表的读取顺序
数据读取操作的类型
哪些索引可以使用
哪些索引实际被使用
表之间的引用
每张表有多少行被优化器扫描

id

SQL执行的顺序的标识,SQL从大到小的执行

id相同时，执行顺序由上至下
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

select_type

查询中每个select子句的类型

SIMPLE(简单SELECT,不使用UNION或子查询等)
PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
UNION(UNION中的第二个或后面的SELECT语句)
DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
UNION RESULT(UNION的结果)
SUBQUERY(子查询中的第一个SELECT)
DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
DERIVED(派生表的SELECT, FROM子句的子查询)
UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

table

显示这一行的数据是关于哪张表的，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

type

表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL, index, range, ref, eq_ref, const, system, NULL（从左到右，性能从差到好）

possible_keys

指出MySQL能使用哪个索引在表中找到记录

Key

显示MySQL实际决定使用的键（索引）如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。不损失精确性的情况下，长度越短越好

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra

该列包含MySQL解决查询的详细信息

总结：

EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的，并非精确值
EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

自动驾驶

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

登录后参与评论

0 条评论

热度