MYSQL优化简单记录

最新推荐文章于 2020-07-27 21:31:06 发布

sunny-life

最新推荐文章于 2020-07-27 21:31:06 发布

阅读量122

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/weixin_42683522/article/details/103903496

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

MYSQL优化简单记录 :

1. Mysql的体系结构

MYSQL体系结构图

整个MYSQL Server由以下组成

Connection pool : 连接池组件
Management services & Utilities: 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buffers :缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

MYSQL与其他数据库不同的是 : 插拔式存储引擎,可以根据业务自行选择.

存储引擎是作用在表上的.

InnoDB : 存储限制64TB, 支持事务, 支持行级锁(适合高并发), 支持外键, 支持B树索引, 支持全文索引(5.6之后) 支持集群索引, 支持数据索引, 支持索引缓存, 空间使用高, 内存使用高, 批量插入低.

MyISAM : 存储有限制, 支持表锁, 支持B树索引, 支持全文索引, 支持索引缓存, 支持数据可压缩, 空间使用低, 内存使用低, 批量插入高

2. 查询数据库全局执行统计:

-- 查看数据库全局统计
show global status like 'Com_______';

-- 针对InnoDB引擎查询统计
show global status like 'Innodb_rows_%';

3. 定位低效SQL:

可以通过两种方式定位执行效率较低的SQL语句.

慢查询日志: 通过慢查询日志定位那些执行效率较低的SQL语句, 用–log-slow-queries[=file_name]选项启动时, mysqId写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件.
show processlist : 慢查询日志在查询结束以后才记录, 所以在应用反映执行效率出现问题的时候查询慢查询日志 并不能定位问题.可以使用show processlist命令查看当前MySQL在进行的线程,包括线程状态,是否锁表等.可以实时查看SQL执行的情况,同时对一些锁表操作进行优化.

-- 查看当前执行效率低的SQL连接
show processlist

-- 查看SQL执行计划
explain + sql 语句

explain 执行计划字段含义:

字段	含义
id	select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序( 按照值大小进行依次执行 :　由大到小)
select_type	表示select的类型,常见的取值有SIMPLE(简单表,即不使用表连接或者子查询),PRIMARY(主查询,即外层的查询),UNION(UNION中的第二个或者后面的查询语句),SUBQUERY(子查询中的第一个SELECT)等.
table	输出结果集的表
type	表示表的连接类型,性能由好到差的连接类型为(system —> const —> eq_ref —> ref —> ref_or_null —> index_merge —> index_subquery —> range —> index —> all)
possible_keys	表示查询时,可能使用索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

explain 之 select＿type:

select_type	含义(从上到下,性能越低)
SIMPLE	简单得SELECT查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询,最外层查询标记为该标识
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询,被标记为DERIVED(衍生) MYSQL会递归执行这些子查询,把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后,则标记为UNION; 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为: DERIVED
UNION RESULT	从UNION表获取结果的SELECT

explain 之 type :

type	含义
NULL	MySQL不访问任何表,索引,直接返回结果
system	表只有一行记录(等于系统表),这时const类型的特例,一般不会出现
const	标识通过索引一次就找到了,const用于比较primary key 或者 unique索引.因为只匹配一行数据,所以很快.如果将主键置于where列表中,MySQL就能将该查询转换为一个常量.const于将"主键"或"唯一"索引的所有部分于常量值进行比较
eq_ref	类似ref,区别在于使用的时唯一索引,使用主键的关联查询,关联查询出的记录只有一条,常见于主键或者唯一索引扫描
ref	非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,返回所有匹配某个单独值的所有行(多个)
range	只检索给定返回的行,使用一个索引来选择行. where之后出现between , < , > , in 等操作
index	index与ALL的区别为index类型只遍历索引树,通常比ALL快
ALL	将遍历全表找到匹配行

explain 之 key … rows … extra :

possible_keys : 显示可能应用在这张表的索引,一个或多个.

key : 实际使用的索引,如果为NULL,则没有使用索引.

key_len : 表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失经度的前提下,长度越短越好.

rows : 扫描的行数.

extra : 其他的额外的执行计划信息,在该列展示.

extra	含义
using filesort	说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的所有顺序进行读取,称为"文件排序",效率低
using temporary	使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表.常见于 order by 和 group by, 效率低
using index	表示相应的select操作使用了覆盖索引,避免访问表的数据行. 有使用索引,可忽略…

4. MySQL 之 profiling :

-- 查询是否开启profiling
select @@have_profiling;

-- 若值 = 0则为关闭, 值 = 1则为开启.
set @@profiling = 1;

-- 查询SQL执行耗时
show profiles;

-- 查询指定profile_id的执行耗时详情
show profile for query profile_id;

-- 查询指定profile_id整个系统的消耗详情(包括cpu,磁盘...)
show profile all for query profile_id;

5. MySQL 之 trace :

MYSQL 5.6提供了对SQL的跟踪trace,通过trace文件能够进一步了解为什么优化器选择A计划,而不选择B计划.

打开trace:

-- 设置格式为JSON,并设置trace最大能够使用的内存大小,避免解析过程中因为默认内存过小而不能完整展示:
set optimizer_trace = "enabled=on" , end_markers_in_json=on;
set optimizer_trace_max_mem_size = 1000000;

-- 查看优化器内SQL执行详情
select * from information_schema.optimizer_trace;

6.索引的使用

索引时数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的MySQL的新跟那个优化问题.

-- 创建普通索引,复合索引
-- 复合索引需遵守最左前缀法则(查询条件包含最左的列,不跳过其他列)
-- 范围查询会导致索引失效
-- 运算操作会导致索引失效
-- 字符串类型必须加'',否则MYSQL底层会进行隐式转换会导致索引失效
-- or之后的字段若无索引则将导致索引失效
-- 模糊匹配 '%XXX%' 导致索引失效,覆盖索引可以解决此问题
-- MYSQL若判断全表扫描比走索引更快,则会放弃索引 - is null 与 is not null 也会依照此原则
-- in会使用到索引,not in 会导致索引失效
-- 创建索引时,尽量使用复合索引.而少使用单列索引
create index index_name on table_name(field ... );

-- 删除索引
drop index index_name on table_name;

-- 查看全局索引使用情况
show [ global ] status like 'Handler_read%';

7. 大批量插入数据时优化方向:

-- 使用load将文件数据导入到表中
load data local infile 'file_path' into table 'table_name' fields terminated by ',' lines terminated by '\n';

-- 关闭唯一性校验,可以提高写入效率
-- 导入数据前
SET UNIQUE_CHECKS=0;
-- 导入数据后
SET UNIQUE_CHECKS=1;

-- 关闭自动提交事务
-- 导入数据前
SET AUTOCOMMIT=0;
-- 导入数据后
SET AUTOCOMMIT=1;

8. insert 语句优化:

-- 例子:
	-- 原始方式:
	insert into t_XX values (1, 'XXX1');
	insert into t_XX values (2, 'XXX2');
	insert into t_XX values (3, 'XXX3');
	
	-- 优化后方案:
	-- 减少IO次数,降低链接次数.
	insert into t_XX values(1, 'XXX1'),(2, 'XXX2'),(3, 'XXX3');
	
-- 大量插入时,建议事务提交改为手动提交
	start transaction;
	insert into t_XX values (1, 'XXX1');
	insert into t_XX values (2, 'XXX2');
	insert into t_XX values (3, 'XXX3');
	commit;
	
-- 按照主键顺序插入
	-- 原始方式:
	insert into t_XX values (1, 'XXX1');
	insert into t_XX values (3, 'XXX2');
	insert into t_XX values (2, 'XXX3');
	
	-- 优化后方案:
	insert into t_XX values (1, 'XXX1');
	insert into t_XX values (2, 'XXX2');
	insert into t_XX values (3, 'XXX3');

9. order by 优化:

FileSort : 效率低,未使用索引
use index : 效率高,有使用到索引
多字段排序应尽量全部升序,要么全部降序
适当修改sort_buffer_size 和 max_length_for_sort_data系统变量来提升效率

10. group by 优化:

group by 默认会使用 order by 排序,若不需要排序则使用:
```
group by field order by null
```

11. 子查询优化:

-- 嵌套查询例 :
select u.id,u.name from user u where u.id in (select user_id from user_role);
-- 通过 explain 查看执行计划得出: 图(11.1)

-- 优化子查询
select u.id,u.name from user u, user_role ur where u.id = ur.id;
-- 通过 explain 查看执行计划得出: 图(11.2)

图:(11.1)

子查询执行结果

图:(11.2)

在这里插入图片描述

通过对比可以得出,使用连接查询在执行计划中减少了执行次数.

12. 优化 OR 条件:

OR 条件在使用中建议添加索引.
OR 条件不会走复合索引,只有对单列索引有效.
使用UNION 替换 OR.

13. 分页查询LIMIT优化 :

LIMIT 在查询时,越往后所需时间越大.
根据主键筛选出所需要的主键ID集合,通过连接查询获取分页结果集,这样可以使用到索引.

(适用于主键自增的优化) 例如:查询200万的分页结果集时

-- 此时查询使用不到索引
select * from user limit 2000000,10;

-- 若表结构主键为自增主键,且没有断层,则可使用此方法
select * from user where id > 2000000 limit 10;

14. SQL提示:

use index : 指定索引 (对于mysql只是参考)
ignore index : 忽略索引 (对于mysql只是参考)

force index : 强制使用索引 (强制使用)

-- 指定索引
select * from tb_XX use index(index_name) where name = 'XXXX';

-- 忽略索引
select * from tb_XX ignore index(index_name) where name = 'XXXX';

-- 忽略索引
select * from tb_XX force index(index_name) where name = 'XXXX';

ex : 强制使用索引 (强制使用)

-- 指定索引
select * from tb_XX use index(index_name) where name = 'XXXX';

-- 忽略索引
select * from tb_XX ignore index(index_name) where name = 'XXXX';

-- 忽略索引
select * from tb_XX force index(index_name) where name = 'XXXX';

sunny-life

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL优化简单记录

MYSQL优化简单记录 :1. Mysql的体系结构整个MYSQL Server由以下组成Connection pool : 连接池组件Management services & Utilities: 管理服务和工具组件SQL Interface : SQL接口组件Parser : 查询分析器组件Optimizer : 优化器组件Caches & Buffers...
复制链接

扫一扫

专栏目录