你可能不知道的Mysql（JOIN连接查询、优化分析、索引、性能分析、Explain使用方法）？

最新推荐文章于 2024-04-29 11:45:00 发布

进击的小绵羊

最新推荐文章于 2024-04-29 11:45:00 发布

阅读量1.1k

点赞数 2

分类专栏： Mysql 文章标签： mysql 索引数据库数据结构

本文链接：https://blog.csdn.net/qq_43060870/article/details/106032736

版权

Mysql 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

1、JOIN连接查询

首先创建两张表（员工表和部门表）

DDL语句：

create table employees
(
    id     int auto_increment
        primary key,
    name   varchar(20)    null,
    dep_id int            null,
    age    int            null,
    salary decimal(10, 2) null,
    cus_id int            null
);
create table department
(
    id       int auto_increment
        primary key,
    deptName varchar(30) null,
    address  varchar(40) null
);

1、1内连接

	图示	作用	语句	示例
1.内连接		查询两张表的共有部分	`Select <select_list> from tableA A Inner join tableB B on A.Key = B.Key`	`select * from employees e inner join department on e.dep_id = department.id;`
2.左连接		把左边表的内容全部查出,右边表只查出满足条件的记录	`Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key`	`select * from employees e left join department on e.dep_id = department.id;`
3.右连接		把右边表的内容全部查出,左边表只查出满足条件的记录	`Select <select_list> from tableA A right Join tableB B on A.Key = B.Key`	`select * from employees e right join department on department.id = e.dep_id;`
4.查询左表独有数据		查询A的独有数据	`Select <select_list> from tableA A Left Join tableB B on A.Key = B.Key where B.key IS NULL`	`select * from employees e left join department on e.dep_id = department.id where department.id is null ;`
5.查询右表独有数据		查询B的独有数据	`Select <select_list> from tableA A Right Join tableB B on A.Key = B.Key where A.key IS NULL`	`select * from employees e right join department on e.dep_id = department.id where e.dep_id is null ;`
6.全连接		查询两个表的全部信息	`Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key` 注:Mysql 默认不支持此种写法 Oracle支持	`select * from employees e left join department on e.dep_id = department.id union select * from employees e right join department on department.id = e.dep_id;`
7.查询左右表各自的独有的数据		查询A和B各自的独有的数据	`Select <select_list> from tableA A Full Outter Join tableB B on A.Key = B.Key where A.key = null or B.key=null` 注:Mysql 默认不支持此种写法 Oracle支持	`select * from employees e left join department on e.dep_id = department.id where department.id is null union select * from employees e right join department on department.id = e.dep_id where e.dep_id is null ;`

2、优化分析

sql性能下降的表现：

执行时间长
等待时间长

性能下降的原因：

查询语句写的不好：各种连接,各种子查询导致用不上索引或者没有建立索引
建立的索引失效：建立了索引,在真正执行时,没有用上建立的索引
关联查询太多join
服务器调优及和个配置参数导致：如果设置的不合理,比例不恰当,也会导致性能下降,sql变慢

3、索引

什么是索引？

帮助Mysql高效获取数据的数据结构
索引就是数据结构
类似新华字典的索引目录,可以通过索引目录快速查到你想要的字
排好序的快速查找数据

为什么要建立索引

提高查询效率：没有排序之前一个一个往后找，通过索引进行排序之后,可以直接定义到想要的位置
排好序的快速查找数据结构-->就是索引

优势

索引类似大学图书馆建立的书目索引,提高数据检索的效率,降低数据库的IO成本
通过索引对数据项进行排序,降低数据排序成本,降低了CPU的消耗

劣势

一般来说, 索引本身也很大, 索引往往以文件的形式存储到磁盘上
索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录.所以索引也是要占磁盘空间的
虽然索引提高了查询速度,但是会降低更新表的速度.
因为更新表时, MYSQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,
会调整因为更新所带来的键值变化后索引的信息

索引分类

单值索引：一个索引只包含间个列,一个表可以有多个单值索引，一般来说, 一个表建立索引不要超过5个
唯一索引：索引列的值必须唯一,但允许有空值
复合索引：一个索引包含多个列
全文索引：MySQL全文检索是利用查询关键字和查询列内容之间的相关度进行检索，可以利用全文索引来提高匹配的速度。
聚集索引:也称为聚簇索引（Clustered Index），聚类索引，簇集索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引对于那些经常要搜索范围值的列特别有效。

索引为什么能快速查找数据

            在我们存数据时, 如果建立索引
            数据库系统会维护一个满足特定查找算法的数据结构,这些数据结构以某种方式引用数据
            可以在这些数据结构之上,实现高级查找算法,这种结构就是索引
            一般来说, 索引本身也很大, 不可能全部存储在内存中, 因此索引往往以索引文件的形式存储在磁盘上
            为了加快数据的查找,可以维护二叉查找树, 每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针,
            这样就可以运用二叉查找在一定的复杂度内获取相应的数据,从而快速的检索出符合条件的记录
            除了二叉树还有BTtree索引
            我平时所说的索引,如果没有特别指定, 都是指B树结构组织的索引
            其中聚焦索引,次要索引,复合索引,前缀索引,唯一默认都是B+树索引
            除B+树索引之外, 还有哈希索引(Hash index)等

二叉查找树

特性：

左子树的键值小于根的键值
右子树的键值大于根的键值

B-Tree（平衡多路查找树）

特性：

根节点至少包括两个孩子
树中每个节点最多有m个孩子(m>=2)
除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
所有叶子节点都在同一层
ki(1=1…n)为关键字,且关键字按顺序升序排列k(i-1) < k               8 < 9
关键字的个数n满足：ceil(m/2)-1 <= n <= m-1    (非叶子节点关键字个数比指向孩子的指针少1个)
非叶子结点的指针p[1],p[2],…p[m] 其中p1指向关键字小于k[1]的子树    3 < 8
p[m]指针关键字大于k[m-1]的子树   15 > 12
p[i]指向关键字属于(k[i-1],k[i])的子树      9,10 是位于8 和 12之间

B+Tree

B+树是B树的变体,基本与B-Tree相同
不同点

非叶子节点的子树指针与关键字个数相同
非叶子节点的子树指针,指向关键字值[k[i],k[i+1]]的子树
非叶子节点仅用来做索引,数据都保存在叶子节点中
所有叶子节点均有一个链指针指向下一个叶子节点

采用B+Tree做为主流索引数据结构的原因

更适合用来做存储索引
B+树的磁盘读写代价更低：内部的结构并没有指向关键字的具体指针，不存放数据,只存放索引信息，内部节点相对B树更小
B+树的查询效率更加稳定：内部节点并不是最终指向文件内容的节点,只是叶子节点中关键字的索引,所以它任何关键字的查找,必须走一条从根节点到叶子节点的路，所有关键字查询的长度相同,导致每一个数据查询的效率也几乎是相同
B+树更有利于对数据库的扫描：B树在提高IO性能同时,并没有解决，B+树只需要遍历叶子节点,就可以解决对全部关键字信息的扫描元素遍历效率底下问题，对数据库中, 频繁使用的范围查询,性能更高

基本语法

创建索引： create 【unique|fulltext|spatial】index 索引名 on 表名（长度【ASC|DESC】）；
```
create unique index ss on department(deptName);
```
查看索引：show index from 表名
```
show index from department;
```
删除索引：drop index 索引名 on 表名；
```
drop index  ss on department;
```

更改索引：alter 表名 add [unque] index [索引名称](字段(长度))

alter table department add unique index uk_test(deptName);
alter table department add unique index (deptName(10));

索引建立选择

适合建立索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引:比如银行系统银行帐号,电信系统的手机号
查询中与其它表关联的字段,外键关系建立索引:比如员工,部门外键
频繁更新的字段不适合建立索引:每次更新不单单更新数据,还要更新索引
where条件里用不到的字段不建立索引
查询中排序的字段,排序的字段若通过索引去访问将大提升排序速度:索引能够提高检索的速度和排序的速度
查询中统计或分组的字段:分组的前提是必排序

不适合建立索引

记录比较少
经常增删改的表:索引提高了查询的速度,同时却会降低更新表的速度,如果对表的INSERT,UPDATE和DELETE,因为建立索引后, 更新表时, MYSQL不仅要保存数据,还要保存一下索引文件
数据重复的表字段:如果某个数据列包含了许多重复的内容,为它建立索引就没有太大在的实际效果,比如表中的某一个字段为国籍,性别,数据的差异率和重复率不高,这种建立索引就没有太多意义

4、性能分析

表设计准则

满足关系数据库的三范式:

1NF:是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值（无重复的列）
2NF:每一行被码唯一标识(消除部分函数依赖)
3NF:要求一个数据库表中不包含已在其它表中已包含的非码信息（消除函数传递依赖）
大表拆小表，有大数据的列单独拆成小表：在一个数据库中,一般不会设计属性过多的表;在一个数据库中,一般不会有超过500/1000万数据的表拆表，有大数据的列单独拆成小表(富文本编辑器,CKeditor);

DQL的执行过程

SQL的执行过程：

客户端发送一条查询给服务器；
服务器通过权限检查之后,先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果。否则进入下一阶段；
服务器端进行SQL解析、预处理，再由优化器根据该SQL所涉及到的数据表的统计信息进行计算，生成对应的执行计划；
MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询；
将结果返回给客户端。

查询优化器：

写的任何sql,到底是怎么样真正执行的,按照什么条件查询,最后执行的顺序,可能都会有多个执行方案
查询优化器根基对数据表的统计信息(比如索引,有多少条数据),在真正执行一条sql之前,会根据自己内部的数据,进行综合的查询,
根据mysql自身的统计信息, 从多种执行方案当中, 选择一个它认为是最优的执行方案,来去执行

做优化,做什么：

做优化, 就是想让查询优化器按照我们的想法,帮我们选择最优的执行方案,
让优化器选择符合程序员计划的执行语句,来减少查询过程中产生的IO

MYSQL常见瓶颈

CPU饱和
磁盘I/0读取数据大小
服务器硬件比较底

5、Explain使用方法

查询执行计划

使用explain关键字,可以模拟优化器执行的SQL语句，从而知道MYSQL是如何处理sql语句的，通过Explain可以分析查询语句或表结构的性能瓶颈。

作用：

查看表的读取顺序
数据读取操作的操作类型
查看哪些索引可以使用
查看哪些索引被实际使用
查看表之间的引用
查看每张表有多少行被优化器执行

使用方法：

explain  sql语句
explain
select a.*
from employees a,
     (select id from employees where name like '李%' limit 1,3) b
where a.id = b.id
  and name like '李%';

运行结果：

分析包含信息	id	select查询的序列号，包含一组数字,表示查询中执行select子句或操作表的顺序 id相同：执行顺序由上到下 id不同：如果是子查询,id的序号会递增,id值越大优先级越高,优先被执行 id相同不同,同时存在：可以认为是一组,从上往下顺序执行，在所有组中,id值越大,优先级越高,越先执行，deriverd 衍生出来的虚表总结相同,顺序走不同,看谁大大的先执行
	select_type	查询类型,主要用于区别普通查询,联合查询,子查询等复杂查询结果值 SIMPLE 简单select查询,查询中不包含子查询或者UNION PRIMARY 查询中若包含任何复杂的子查询,最外层查询则被标记为primary SUBQUERY 在select或where中包含了子查询 DERIVED 在from列表中包含的子查询被标记为derived(衍生) 把结果放在临时表当中 UNION 若第二个select出现的union之后,则被标记为union 若union包含在from子句的子查询中,外层select将被标记为deriver UNION RESULT 从union表获取结果select 两个UNION合并的结果集在最后
	table	显示这一行的数据是关于哪张表的
	partitions	如果查询是基于分区表的话, 会显示查询访问的分区
	type	访问类型排列，结果值最好到最差 system 表中有一行记录(系统表) 这是const类型的特例,平时不会出现 const 表示通过索引一次就找到了 const用于比较primary 或者 unique索引. 直接查询主键或者唯一索引因为只匹配一行数据,所以很快示例 eq_ref 唯一性索引扫描对于每个索引键,表中只有一条记录与之匹配常见于主键或唯一索引扫描 ref 非唯一性索引扫描,返回匹配某个单独值的所有行本质上也是一种索引访问它返回所有匹配某个单独值的行可能会找到多个符合条件的行, 所以它应该属于查找和扫描的混合体 range 只检索给定范围的行,使用一个索引来选择行 key列显示使用了哪个索引一般就是在你的where语句中出现between\<\>\ in等查询这种范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点.而结束语另一点不用扫描全部索引 index Full Index Scan index与All区别为index类型只遍历索引树,通常比All要快,因为索引文件通常比数据文件要小 all和index都是读全表,但index是从索引中读取,all是从硬盘当中读取 ALL 将全表进行扫描,从硬盘当中读取数据如果出现了All 切数据量非常大, 一定要去做优化
	possible_keys	key与keys主要作用,是查看是否使用了建立的索引, 也即判断索引失效，在建立多个索引的情况下, mysql最终用到了哪一个索引 possible_keys 显示可能应用在这张表中的索引,一个或者多个查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用可能自己创建了4个索引,在执行的时候,可能根据内部的自动判断,只使用了3个
	key	实际使用的索引,如果为NULL,则没有使用索引查询中若使用了覆盖索引 ,则该索引仅出现在key列表中 possible_keys与key关系理论应该用到哪些索引实际用到了哪些索引覆盖索引查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引
	key_len	表示索引中使用的字节数,可通过该列计算查询中使用的索引长度 .
	ref	索引是否被引入到, 到底引用到了哪几个索引
	rows	根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数，每长表有多少行被优化器查询过
	filtered	满足查询的记录数量的比例，注意是百分比，不是具体记录数，值越大越好，filtered列的值依赖统计信息，并不十分准确
	Extra	产生的值

进击的小绵羊

关注

2
点赞
踩
7

收藏

觉得还不错? 一键收藏
打赏
1
评论
你可能不知道的Mysql（JOIN连接查询、优化分析、索引、性能分析、Explain使用方法）？

1、JOIN连接查询首先创建两张表（员工表和部门表）DDL语句：create table employees( id int auto_increment primary key, name varchar(20) null, dep_id int null, age int null, salary decimal(10, 2) null, cus_id
复制链接

扫一扫