数据库调优笔记

慢查询

处理步骤

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

性能调优

应用程序优化

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

SQL语句优化

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

表结构优化

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

硬件和数据库配置优化

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

全文索引

MySQL

版本支持

1. MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
2. MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
3. 只有字段的数据类型为 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从大到小的执行

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

select\_type

查询中每个select子句的类型

1. SIMPLE(简单SELECT,不使用UNION或子查询等)
2. PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
3. UNION(UNION中的第二个或后面的SELECT语句)
4. DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
5. UNION RESULT(UNION的结果)
6. SUBQUERY(子查询中的第一个SELECT)
7. DEPENDENT SUBQUERY(子查询中的第一个SELECT，取决于外面的查询)
8. DERIVED(派生表的SELECT, FROM子句的子查询)
9. 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后查看执行计划

版权属于：青阳のBlog

本文链接：https://www.hipyt.cn/256.html

作品采用：《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权
转载时须注明出处及本声明