【Mysql优化01】优化分析-7种join和索引命令

最新推荐文章于 2023-05-29 10:22:03 发布

fixAllenSun

最新推荐文章于 2023-05-29 10:22:03 发布

阅读量236

点赞数

分类专栏： Java基础与算法文章标签：算法数据结构数据库索引 sql

本文链接：https://blog.csdn.net/weixin_44823875/article/details/106190644

版权

Java基础与算法专栏收录该内容

80 篇文章 3 订阅

订阅专栏

【Mysql优化01】优化分析-7种join和索引命令

（1）认识7种join，学习索引命令

（1）认识7种join，学习索引命令

（1.1）sql性能下降的原因（执行时间长、等待时间长）

（1.1.1）排查性能下降的原因

（1）先观察，至少跑一天，看看生产的慢SQL情况
（2）开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并把它抓取出来
（3）explain+慢sql分析
（4）show profile（99%拿下）
（5）运维经理或者DBA，进行SQL数据库服务器的参数调优

（1.1.2）分析性能下降的原因

（1）原因一：查询数据过多：看看能不能拆开，条件过滤尽量少
（2）原因二：索引失效
（3）原因三：关联了太多的表，用了太多的join
join原理，用a表的每一条数据扫描b表的所有数据，所以尽量先过滤
（4）原因四：没有利用索引
什么时候创建索引：索引针对列建素银，但并不可能每一列都建索引，索引并非越多越好，当数据更新了，索引会进行调整，也会很消耗性能，且mysql并不会把所有索引都用上，只会根据其算法挑一个索引用，所以建的准很重要
（5）
什么是单值索引：即一个索引只包含单个列，一个表可以有多个单列索引（例如用户频繁使用的是name这一列进行检索）
什么是唯一索引：索引列的值必须唯一，但允许有空值
什么是复合索引：即一个索引包含多个列（例如用户频繁使用的是name和sex和number多个列进行检索）
（6）原因五：服务器调优和各个参数设置（缓冲。线程数等等）

（1.1.3）Mysql常见瓶颈

（1）CPU：sql中对大量数据进行比较、关联、排序、分组。最大的压力在于比较
（2）IO：实例内存满足不了缓存数据或者排序等需要，导致产生大量物理IO。或者是查询执行效率低，扫面过多数据行
（3）锁：不适宜的锁的设置，导致线程阻塞，性能下降。或者出现死锁，线程之间交叉调用资源，导致死锁，程序卡住。
（4）服务器硬件的性能瓶颈：top、free、iostat和vmstat来查看系统的性能状态

（1.1.4）Mysql逻辑架构

（1）连接层
最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
（2）服务层
Management Serveices & Utilities：系统管理和控制工具
SQL Interface: SQL接口，接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
Parser: 解析器SQL命令传递到解析器的时候会被解析器验证和解析。
Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。用一个例子就可以理解： select uid,name from user where gender= 1;优化器来决定先投影还是先过滤。
2.5 Cache和Buffer：查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。缓存是负责读，缓冲负责写。
（3）引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
（4）存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

在这里插入图片描述

（1.2）常见通用的join查询

（1.2.1）sql执行顺序

（1）手写
在这里插入图片描述
（2）机读：优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序，下面是经常出现的查询顺序

（3）总结

（1.2.2）常见的join查询图（七种）

共有：满足a.user_id=b.id的叫共有
A独有：A表中所有不满足a.user_id=b.id的叫独有
在这里插入图片描述

（1.2.3）建表sql：为后面的案例做环境准备

创建两张表，t_dept是部门表，t_emp是员工表，然后分别为两张表插入数据。
（1）创建新的数据库dbImprove：create database dbImprove;
（2）打开新建的数据库：use dbImprove;
（3）复制下面的sql语句直接执行
（4）查看一下：show tables;
在这里插入图片描述
（5）看一下单表查询的效果
部门表如下：

员工表如下：

CREATE TABLE `t_dept` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
CREATE TABLE `t_emp` (
 `id` INT(11) NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
  `age` INT(3) DEFAULT NULL,
 `deptId` INT(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `fk_dept_id` (`deptId`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
INSERT INTO t_dept(deptName,address) VALUES('华山','华山');
INSERT INTO t_dept(deptName,address) VALUES('丐帮','洛阳');
INSERT INTO t_dept(deptName,address) VALUES('峨眉','峨眉山');
INSERT INTO t_dept(deptName,address) VALUES('武当','武当山');
INSERT INTO t_dept(deptName,address) VALUES('明教','光明顶');
INSERT INTO t_dept(deptName,address) VALUES('少林','少林寺');
 
INSERT INTO t_emp(NAME,age,deptId) VALUES('风清扬',90,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('岳不群',50,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('令狐冲',24,1);
INSERT INTO t_emp(NAME,age,deptId) VALUES('洪七公',70,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('乔峰',35,2);
INSERT INTO t_emp(NAME,age,deptId) VALUES('灭绝师太',70,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('周芷若',20,3);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张三丰',100,4);
INSERT INTO t_emp(NAME,age,deptId) VALUES('张无忌',25,5);
INSERT INTO t_emp(NAME,age,deptId) VALUES('韦小宝',18,null);

（1.2.4）测试7种join（使用上面新建的表进行sql编写）

（1）A、B两表共有：inner join
select * from t_emp a inner join t_dept b on a.deptId = b.id;
在这里插入图片描述

（2）A、B两表共有+A的独有：left join
select * from t_emp a left join t_dept b on a.deptId = b.id;
左连接的t_emp表全查询，t_dept 没有值就显示为null

在这里插入图片描述

（3）A、B两表共有+B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id;
在这里插入图片描述

（4）A的独有
select * from t_emp a left join t_dept b on a.deptId = b.id where b.id is null;
在这里插入图片描述

（5）B的独有
select * from t_emp a right join t_dept b on a.deptId = b.id where a.deptId is null;
在这里插入图片描述

（6）AB全有

#MySQL Full Join的实现 因为MySQL不支持FULL JOIN,下面是替代方法
#left join + union(可去除重复数据)+ right join
SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id

这里因为要联合的缘故，不能考虑到小表驱动大表的情况。只能用right join。要保证查询出来的数字要一致。

（7）A的独有+B的独有

SELECT * FROM t_emp A LEFT JOIN t_dept B ON A.deptId = B.id WHERE B.`id` IS NULL
UNION
SELECT * FROM t_emp A RIGHT JOIN t_dept B ON A.deptId = B.id WHERE A.`deptId` IS NULL;

（1.3）索引简介

（1.3.1）索引是什么

索引index是帮助mysql高效获取数据的数据结构，之所以使用索引查询更快是因为索引把数据都排好了序。

在维护数据之外，数据库系统还维护这满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据。这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引。
在这里插入图片描述左边是数据表，一共有两列七条数据，最左边的是数据记录的物理地址，为了加快col2的查询，可以维护一个右边的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据，从而快速的检索出符合条件的记录

二叉树的弊端：如果有序的插入数据，可能会退化成链表
B树的优点和弊端：b树会自动根据两边的情况自动调节，使两端无线接近于平衡状态，可以使性能最稳定，是MyIsam引擎的数据结构，弊端就是插入和修改操作多时，b树会不断调整消耗性能，另外查询的时候性能不稳定，最后不适用于范围查询
B+树：InnoDB用的索引，查询效率稳定，适用于范围查询

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上

我们平常所说的索引，如果没有特别执行，都是指B+树。聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树，统称索引。此外，还有哈希索引。

（1.3.2）索引的优势

类似于大学图书馆的书目索引，提高数据检索的效率，降低数据库的IO成本

通过索引列队数据进行排序，降低数据排序的成本，降低了CPU的消耗

（1.3.3）索引的劣势

实际上索引也是一张表，这个表保存了主键和索引字段，并且指向实体表的记录，所以索引列也是要占用空间的

虽然索引大大提高了查询速度，同时也会降低更新表的速度，比如对表进行insert、update、delete。因为更新表的时候，mysql不仅仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息

索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或者优化查询语句

（1.3.4）mysql索引结构（结构另外一篇博客进行总结）

（1）b树索引
（2）b+树索引
（3）聚簇索引和非聚簇索引
（4）full-text全文索引
全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。

CREATE TABLE `article` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `title` (`title`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

不同于like方式的的查询：

SELECT * FROM article WHERE content LIKE ‘%查询字符串%’;

全文索引用match+against方式查询：

SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

明显的提高查询效率。

（5）hash哈希索引
Hash索引只有Memory, NDB两种引擎支持，Memory引擎默认支持Hash索引，如果多个hash值相同，出现哈希碰撞，那么索引以链表方式存储。
NoSql采用此中索引结构。

（6）R树索引
R-Tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。
相对于b-tree，r-tree的优势在于范围查找。

（1.3.5）mysql索引分类和建索引命令

（1）主键索引
设定为主键后数据库会自动建立索引，InnoDB为聚簇索引

随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

unsigned (无符号的)，使用 AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。

CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);

单独建主键索引：

ALTER TABLE customer 
 add PRIMARY KEY customer(customer_no);

删除建主键索引：

ALTER TABLE customer 
 drop PRIMARY KEY ;

修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

（2）单值索引
就是一个索引只包含单个列，一个表可以有多个单列索引
1）随表一起建索引：
随表一起建立的索引索引名同列名(customer_name)

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);

2）单独建单值索引：

CREATE  INDEX idx_customer_name ON customer(customer_name);

3）删除索引：

DROP INDEX idx_customer_name ;

（3）唯一索引
索引列的值必须唯一，但允许为空
1）随表一起建索引：
建立唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);

2）单独建唯一索引：

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

3）删除索引：

DROP INDEX idx_customer_no on customer ;

（4）复合索引
一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)
当表的行数远大于索引列的数目时可以使用复合索引

1）随表一起建索引：

CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);

2）单独建索引：

CREATE  INDEX idx_no_name ON customer(customer_no,customer_name);

3）删除索引：

DROP INDEX idx_no_name  on customer ;

（1.3.5）基本语法

（1）创建索引

ALTER mytable add [unique] index [indexName] on (columnname(length))

（2）删除索引

drop index [indexName] on mytable;

（3）查看索引

show index from table_name\G

non_unique：是否是唯一索引 1：是 0：不是。
seq_in_index：列在索引中的序列。针对符合索引(一个索引对应多个列)。针对同一个复合索引按照创建复合索引时的顺序进行排序
Null：是否允许 null 值

（4）使用alter命令
有四种方式来添加数据表的索引：

该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

该语句指定了索引为 FULLTEXT ，用于全文索引。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

（1.3.6）哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引（where后面的语句）
查询中与其它表关联的字段，外键关系建立索引：A 表关联 B 表：A join B。on 后面的连接条件既 A 表查询 B 表的条件。所以 B 表被关联的字段建立索引能大大提高查询效率，因为在 join 中，join 左边的表会用每一个字段去遍历 B 表的所有的关联数据，相当于一个查询操作
单键、组合索引的选择问题，who？（在高并发下倾向创建组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度：group by 和 order by 后面的字段有索引大大提高效率
查询中统计或者分组字段

（1.3.7）哪些情况不要创建索引

表记录太少
经常增删改的表
因为索引提高了查询速度，但是也会降低更新表的速度，比如insert、update、delete。因为更新表的时候，mysql不仅要保存数据，还要保存一下索引文件
where条件里用不到的字段不创建索引
数据重复且分布平均的表字段，因此应该只为最经常查询的最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

fixAllenSun

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
【Mysql优化01】优化分析-7种join和索引命令

【Mysql优化】优化分析-7种join和索引简介（1）索引优化分析（1）sql性能下降的原因（执行时间长、等待时间长）（2）常见通用的join查询（1）sql执行顺序（2）join图（七种）（3）建表sql（4）7种join（使用上面新建的表进行sql编写）（3）索引简介（1）索引是什么（2）索引的优势（3）索引的劣势（4）mysql索引结构（结构另外一篇博客进行总结）（5）基本语法（6）哪些情况需要创建索引（7）哪些情况不要创建索引（1）索引优化分析（1）sql性能下降的原因（执行时间长、等待时间长
复制链接

扫一扫