慢查询
处理步骤
- 判断慢查询产生（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后查看执行计划
版权属于：青阳のBlog
本文链接：https://hipyt.cn/256.html
作品采用：《署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)》许可协议授权
转载时须注明出处及本声明