【Mysql】 Mysql 进阶

最新推荐文章于 2023-04-03 16:39:08 发布

AldarLin

最新推荐文章于 2023-04-03 16:39:08 发布

阅读量192

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_34416331/article/details/107622942

版权

数据库专栏收录该内容

13 篇文章 0 订阅

订阅专栏

一、配置文件

二进制日志 log-bin 用于主从复制
错误日志 log-error 默认是关闭的，记录验证的警告和错误信息，每次启动和关闭的详细信息
慢查询日志 log 默认关闭，记录查询的sql语句，如果开启会降低mysql的效率

数据文件：
frm => 存放表结构
myd => 存放表数据
myi => 存放表索引

二、MyISAM 与 InnoDB 的对比

	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁住某一行，不对其他行有影响，适合高并发操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

三、SQL 性能下降的原因

SQL慢、执行时间长、等待时间长 => {

查询语句写的烂

索引失效

关联查询太多join（设计缺陷或不得已的需求）

服务器调优及各个参数设置(缓冲、线程数等)

}

3.2 SQL 执行加载顺序

手写顺序

机读顺序

四、Join关系

4.1 内连接 Inner Join

只获取两者的共有部分

select <select_list> from tableA a inner join tableB b on a.key=b.key

4.2 左连接 Left Join

得到左表的全部，右表不足补 null

select <select_list> from tableA a left join tableB b on a.key=b.key

4.3 右连接 Right Join

右表的全部，A表不足补null

select <select_list> from tableA a right join tableB b on a.key=b.key

4.4 获取一个表的独有

select <select_list> from tableA a left join tableB b on a.key=b.key where b.key is null;

4.5 获取一个表的独有2

select <select_list> from tableA a right join tableB b on a.key=b.key where a.key is null;

4.6 全连接

select <select_list> from tableA a full outer join tableB b on a.key=b.key;

4.7 去除重合

select <select_list> from tableA a full outer join tableB b on a.key=b.key where a.key is null or b.key is null;

五、索引

5.1 索引的概念

索引是已经排好序以便快速查找的数据结构

在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式指向数据。
这种数据结构，就是索引。

5.2 索引的优劣势

5.2.1 索引的优势

实现目录的功能，提高检索效率，降低数据库的IO成本
通过索引对数据进行排序，降低数据排序成本，降低 CPU 消耗

5.2.2 索引的劣势

索引文件需要消耗磁盘空间。索引起始就是一张表，以文件形式存储在磁盘上，表内保存了主键和索引字段，指向实体表的记录，
额外的更新和保存开销。索引增加了查询速度，但降低了更新速度，由于更新表时不仅要保存数据，还要更新和保存索引文件，修改索引列的字段以及指向位置
需要更新和优化索引

5.3 索引操作

5.4 索引实现方式

5.4.1 概述

常见的索引类型有：

BTree 索引
hash索引
full-text 全文索引
RTree索引

一般来说索引也很大，不可能全部存储在内存中，因为索引往往以索引文件的形式存储在磁盘上
我们平常所说的索引，如果没有特别指明，都是指B+树(多路搜索树，不一定的二叉树)结构阻止的索引。其中聚集索引、次要索引、符合索引、前缀索引、唯一索引默认都是用B+树索引。
除了B+树外，还有哈希索引等

5.4.2 BTree 索引

5.5 索引的适用

5.5.1 适合建立索引

5.5.2 不适合建立索引

六、性能优化

6.1 MySql Query Optimizer

6.2 其他

七、Explain 使用

7.1 什么是 Explain

7.2 Explain 能做什么

7.3 对应表头

id 决定了表的读取和加载顺序

select_type 主要是查询的类型

table 表示这一行数据是哪张表的

type 表示查询的类型，能体现出查询效率

possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引则会被列出，但不一定被查询实际使用

key 实际使用的索引，如果为 NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在 key 列表中

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

其显示的值为索引字段的最大可能长度，并非实际使用长度，是根据表定义计算得到的，而不是通过表内检索得到

ref 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

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

Extra 包含不在列中显示，但十分重要的信息

7.3.1 id

id 决定了表的读取和加载顺序

id相同：执行顺序从上到下

id不同：

id相同和不同同时存在

7.3.2 select_type

select_type 主要记录了查询的类型

7.3.3 type

一般来说，需要保证查询至少达到 range 级别，最好能达到 ref

	描述
system	单表，且表只有一行记录，是 const 类型的特例，等于系统表
const	通过索引一次就找到了，ocnst 用于比较 primary key 或者 unique 索引，由于只匹配一行数据，速度很快。
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描，与const不同的是可能用在多表操作中
ref	非唯一性索引扫描，即通过索引扫描，可能找出多个符合条件的行
range	只检索给定范围的行，使用一个索引来所选择，key 列显示使用了哪个索引一般是在 where 语句中使用了 between、<、>、in 等的查询这种范围索引扫描优于全表扫描，它只需要开始于索引的某一点，结束与另一个点，不需要扫描全部索引
index	Full Index Scan，索引表的全表扫描，由于 index 只遍历索引树，且索引文件一般比数据文件小，通常比全表扫描快。且 index 是从索引中读取的，all 从硬盘中读取的，index 有更少的 io
all	Full Table Scan，全表扫描

7.3.4 Extra

	描述	严重程度
using filesort	说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 Mysql 中将无法利用索引完成的排序操作称为 “文件排序”	严重，必须优化
using temporary	使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by 等需要统计的模块	非常严重，必须优化由于临时表的创建，数据搬运以及删除临时表，都十分消耗数据库性能
using index	表示对应的 select 操作中使用了覆盖索引(Covering Index)，直接从索引中找出数据，而没有查询数据库表，效率较高如果同时出现 using where，表明索引被用来执行索引键值的查找若没有出现 using where，表明索引用来读取数据，而非执行查找动作即建的索引为 q1,q2，查询的内容小于等于q1,q2，不能超出范围	效率较高
using where	表示使用了 where 查询，无需回表查询数据
using index condition	表示查找中使用了索引，但需要回表查询数据。即索引下推技术
Backward index scan	方向扫描，MySQL 8.0 存在，避免 filesort
using join buffer	使用了连接缓存
impossible where	where 自居的值总是false，不能用来获取任何元组。如查性别又是男性又是女性的 select * from test where sex=1 and sex=2	等于sql语句错误，需要优化。

7.3.5 覆盖索引

7.3.6 索引下推技术(Using index condition)

https://www.jianshu.com/p/bdc9e57ccf8b

八、索引优化demo

8.1 单表优化demo

# 准备动作

创建表：

create table if not exists article(
	id int(10) unsigned not null primary key auto_increment,
	author_id int(10) unsigned not null,
	category_id int(10) unsigned not null,
	views int(10) unsigned not null,
	comments int(10) unsigned not null,
	content text not null
);

insert into article(author_id, category_id, views, comments, title, content) 
values 
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(3,3,3,3,'3','3')

select * from article;

最开始的查询语句

EXPLAIN SELECT id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

# 添加索引优化

添加索引

create index idx_article_ccv on article(category_id,comments,views);

查询

EXPLAIN SELECT id,author_id from article where category_id=1 and comments>1 order by views desc limit 1;

问题

# 索引再次优化

删除索引

alter table article drop index idx_article_ccv;

重建索引

create index idx_cv on article(category_id,views);

结论

可以看到 type 变为了 ref，Extra 中的 using filesort 也消失了，达到了预期目标

8.2 两表优化demo

# 准备工作

create table if not exists class(
	id int(10) UNSIGNED not null auto_increment primary key,
	card int(10) UNSIGNED not null
);
create table if not exists book(
	bookid int(10) UNSIGNED not null auto_increment primary key,
	card int(10) UNSIGNED not null
);

insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
insert into class(card) values(floor(1 + (RAND() * 20)));
 
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));
insert into book(card) values(floor(1 + (RAND() * 20)));

# 加索引

记住：左连接，右表加索引。右连接，左表加索引。

这是由左连接的特性决定的，left join 条件用于确定如何从右表开始搜素行，左表数据一定都有

alter table book add index idx_card(card);

查询

explain select * from class left join book on class.card=book.card

结果：

8.3 三表优化demo

# 准备工作

create table if not exists phone(
	phoneid int(10) unsigned not null auto_increment primary key,
	card int(10) unsigned not null
)engine = innodb;

insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));
insert into phone(card) values(floor(1 + (RAND() * 20)));

select * from phone;

# 增加索引

alter table book add index idx_card(card);
alter table phone add index idx_card(card);
alter table class add index idx_card(card);

# 查询

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

# 结果

8.4 总结

小表驱动大表，尽可能减少 join 语句中的 NeedLoop(嵌套) 的循环总次数。即以小表作为最左边的表
优先优化 NeedLoop(嵌套) 的内层循环
保证 join 语句中被驱动表上 join 的条件字段已经被索引
当无法保证被驱动表上的 join 条件字段被索引且内存资源充足的前提下，不要太吝啬 JoinBuffer 的设置

九、索引失效场景

9.1 准备工作

建表，插入数据，建立索引

create table staffs(
	id int primary key auto_increment,
	name varchar(24) not null default '' comment '姓名',
	age int not null default 0 comment '年龄',
	pos varchar(20) not null default '' comment '职位',
	add_time timestamp not null default current_timestamp comment '入职时间'
)charset utf8 comment '员工记录表';

insert into staffs(name, age, pos, add_time) values('z3',22,'manager',NOW());
insert into staffs(name, age, pos, add_time) values('July',22,'dev',NOW());
insert into staffs(name, age, pos, add_time) values('tom',22,'dev',NOW());

select * from staffs;


alter table staffs add index idx_staffs_name_age_pos(name,age,pos);
show index from staffs;

9.2 索引失效场景总结

9.2.1 违背最左前缀

最左前缀即在复合索引中，如(a,b,c)，搜索条件必须匹配为：(a)、(ab)、(abc)，否则会部分失效。

例子：

有复合索引(name,age,pos)，此时查询条件为：(name, age, pos)

explain select * from staffs where name='tom' and age=22 and pos='dev';

若查询条件改成（age, pos）

explain select * from staffs where age=22 and pos='dev';

9.2.2 在索引列上使用了mysql内置函数，导致索引失效

explain select * from staffs where left(name, 4) = 'July';

9.2.3 使用 sql 语句中范围右边的列，索引失效

explain select * from staffs where name='tom' and age>21 and pos='manager';

9.2.4 like 后面以通配符 % 开头，索引失效

explain select * from staffs where name like '%tom%';

如果左边必须有 % 如何处理：

使用覆盖索引进行全表扫描。即查询出的字段必须在建立的索引范围内

explain select name from staffs where name like '%tom%';

十、常见面试题

10.1 优化sql

最左原则

若有范围，由于>号后索引失效，需要调整范围顺序，使范围顺序顺从索引顺序，能够增加使用的索引字段

Order By 注意事项

使用 order by 时，尽量按最左原则排序，若无法按照最左原则，可以尝试将某个内容变为变量如：

Group By 注意事项

分组之前必排序，若顺序错乱后，就会出现临时表

使用 group by 时，尽量按最左原则排序，若无法按照最左原则，可以尝试将某个内容变为变量如：

10.2 索引建议

10.3 优化

10.3.1 in 和 exist

https://www.jianshu.com/p/f212527d76ff

10.3.2 order by

## 双路排序

mysql 4.1 之前是使用双路排序，即两次扫描磁盘，才最终得到数据。

读取行指针和 orderby 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

从磁盘取排序字段，在 buffer 上进行排序，再从磁盘上读取其他字段

缺点：

取一批数据需要对磁盘进行两次扫描，由于 I/O 是很耗时的，故在 mysql 4.1 之后，出现了第二种改进的算法

## 单路排序

## 对比

若单路取出的数据总大小超出了 sort_buffer 的容量，会导致多次 I/O 并多次排序

10.3.3 group by

分组先查询，故大部分与 order by 一致

AldarLin

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
【Mysql】 Mysql 进阶

一、配置文件二进制日志 log-bin 用于主从复制错误日志 log-error 默认是关闭的，记录验证的警告和错误信息，每次启动和关闭的详细信息慢查询日志 log 默认关闭，记录查询的sql语句，如果开启会降低mysql的效率数据文件：frm => 存放表结构myd => 存放表数据myi => 存放表索引二、MyISAM 与 InnoDB 的对比 MyISAM InnoDB 外键不支持支持事务不支持 ...
复制链接

扫一扫

专栏目录