[mysq一l]:sql如何执行\如何建表\如何查询更快

最新推荐文章于 2024-09-19 21:27:19 发布

qwrasdzcc

最新推荐文章于 2024-09-19 21:27:19 发布

阅读量152

点赞数

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

本文链接：https://blog.csdn.net/qwrasdzcc/article/details/131196367

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章详细探讨了MySQL的SQL执行机制，包括连接方式、TCP通讯过程及SQL执行步骤。讲解了如何构建更符合业务需求的表，重点介绍了索引组织表、B+树索引以及InnoDB存储引擎的特性和数据存储方式。此外，还分析了查询优化策略，如覆盖索引、排序优化和分页查询优化，旨在提升查询速度。

摘要由CSDN通过智能技术生成

一、三高和sql如何执行

1、三高三大手段

复制
扩展
切换

如何提升单店性能?
1.建表时:表结构合理,索引搞笑
使用B+树数据结构与InnoDB的存储结构;使用InnoDB行记录格式;使用索引、数据约束、视图
2.查询时:优化sql语句,选择正确索引
使用覆盖索引、索引下推、松散索引；使用排序、随机选取、COUNT优化；排查索引失效
3.更新时:正确使用锁,合理优化事务
Mysql和InnoDB日志体系；全局锁、表锁、元数据锁、行锁、间隙锁；死锁优化；事务、MVCC和性能优化

2、一个sql如何执行

1. sql连接4种方式？

包括tcp/ip连接、命名管道、共享内存、UNIX域套接字四种方式

2.mysql tcp通讯过程？

mysql底层是通过Mysql TCP报文进行交互，报文包括四个字节指令(其中3个字节表示长度，1个字节表示序列号)，N个字节数据。如（0x02）指令表示切换数据库、（0x03）指令表示查询命令。
1.tcp/ip连接过程
-》首先客户端先发起握手请求，进行三次握手建立TCP连接
-》然后进行认证连接：握手成功后，mysql服务端先发起版本号，然后客户端发送认证信息，服务端返回认证结果
-》认证通过后，客户端与服务端交互，如Show Database命令和Query请求
-》断开mysql连接
-》4次握手断开tcp连接

认证连接：
在这里插入图片描述
客户端与服务端交互，即命令执行：

断开连接：
客户端向服务器发送退出命令包

其他三种连接方式？（了解）
其他三种仅限于本机
命名管道：sql-server，同一台windows服务器通讯
共享内存：通过共享同一块内存访问。
UNIX域套接字：一个从socket存，一个取

3.一个sql是如何执行的？

一个sql按照分析-优化-执行-落盘执行的。
1.连接器：监听客户端请求，然后将指令发送给缓存
2.缓存查找指令：以KV形式存放sql和sql结果,mysql缓存很容易失效,因为是表级别的,数据表修改,会删除表相关所有缓存。（注缓存在mysq8.0停用）
3.分析器分析你想“干什么”，会进行词法分析，分析sql中关键字；会进行语法分析，判断sql语句是否符合语法，然后将sql丢给优化器进行优化。
4.优化器是指定执行计划，主要工作是如何使用索引，指定完毕将计划推给执行器。
5.执行器首先校验用户对目标数据有无权限，如果有权限，则执行器会以行为粒度调用存储引擎执行sql（在没有索引情况下执行器会循环查询所有行，如select * from t where age > 10，遍历每一行看是否age > 10）。
6.存储引擎任务是讲执行器的指令落实在数据文件上，以InnoDB为例，执行器想取id为1的数据，执行器不管数据是如何存在文件上的，怎么存在文件上由InnoDB存储引擎负责，InnoDB负责将id=1的数据取出来给执行器。

4.常见的mysql存储引擎

在这里插入图片描述

二、如何建表更符合业务？

1.什么叫索引组织表？

索引组织表是由索引“组织起来的”表并根据“主键”顺序组织存放，在InnoDB中的数据表均为索引组织表。

什么事索引？
索引是数据库中对某列或多列的值进行“预排序(预排序就是数据在存入的时候就根据索引进行排序)”的数据结构，索引可以理解为数据的“目录”。

什么是主键？
没有显式指定索引的情况下，表中非空唯一的索引就是主键，如果有多个非空索引，在第一个声明的非空索引就是主键。
如果没显式指定且没有非空唯一的索引则InnoDB自动创建一个6字节的row id指针，作为隐式主键。

主键和主索引关系？
主键是一个特殊的索引(或目录)字段，而主索引是主键排序后组成的索引

2.Mysql使用B+树索引算法的演变

1)二叉树

在这里插入图片描述
如果每次都往右下角加入节点，结果会退化到链表查找

2)平衡二叉树

平衡二叉树会通过“左旋”或“右旋”平衡这颗树，不会退化成链表。注：当高度差超过1时就会进行旋转操作
AVL树缺点是：增删需要增删维护树的平衡，且一个节点包含的数据太低了

3)B和B+树

B树特点？

节点内线性查找，节点外树形查找
单节点存放多数据
不需要旋转就可以保证树的平衡

B树缺点？
对范围查找效率很低

B+树

B+树将索引和数据分开，所有数据都在叶子节点
B+树的叶子结点用指针连接成线性表

3.为什么说InnoDB索引即数据？

因为在InnoDB中直接把数据记录在主索引里

聚簇索引和辅助索引？

InnoDB索引分为聚簇索引（或主索引）和辅助索引。
聚簇索引 clustered index也叫主键索引，把数据和索引放一起，根据表的主键构造一个B+树，叶子结点直接存放数据。
在这里插入图片描述
辅助索引：叶子结点并不包含数据，而是记录了行的主键，指示数据位置。
聚簇索引和辅助索引区别和联系？区别：聚簇索引叶子结点包含数据，辅助索引不包含数据，而是记录行的主键。联系：拿着主键通过查聚簇索引表获取数据，即“回表操作”

InnoDB是如何实现B+树的？

同层B+树节点间也做了双向链表，便于查找。
B+树节点内，数据条目之间为单向链表。

4.InnoDB数据如何存储？

在这里插入图片描述

5.InnoDB行记录细节？

1)变长列导致的行溢出问题？以及使用行溢出机制解决

在这里插入图片描述
InnoDB中CHAR和VARCHAR都是边长列

在这里插入图片描述

2)InnoDB行记录格式的演变

行记录格进化的核心是：节约行记录空间，让一页存放更多行记录
在这里插入图片描述

在这里插入图片描述

到了Compact阶段，就只记录变长字段，并且用NULL位图记录每个字段是否为空
在这里插入图片描述

用的最多的是Dynamic。到了Dynamic阶段，在变长字段溢出时，将所有数据都放到BLOB中，列字段只记录一个指向BLOB的指针。
在这里插入图片描述

到了Compressed阶段，Compressed就比Dynamic多了对数据行的压缩，每次需要压缩和解压缩，用时间换空间，用的不如Dynamic多。

6(*).索引注意事项？

mysql索引“最左原则”

索引的第一个元素必须在where条件中存在，与顺序无关。

联合索引

联合索引是一种辅助索引
在这里插入图片描述

字符串前缀索引

字符串前缀索引就是：取字符串的前几位作为索引
如

alter table user add index indexname(email(6))

如果字符串过长（如：身份证），可以用前缀索引
如果前缀区分度小，可以用“倒序存储(如用身份证后几位区分度大的做索引)”或“新建Hash字端(整体hash一遍再作为索引)”解决

索引左侧用法

联合索引可以代替最左侧字段的单独索引
字符串前缀索引可以节约磁盘空间

字符串“左模糊”即“内容%”才可以使用索引，否则会导致索引失效，其他模糊情况可以使用ES或存储引擎

7.视图

视图的原理是预设一个SELECT语句，SELECT语句的查询结果作为虚拟表的数据

视图算法？

merge，将视图SQL和主查询SQL合并成一条SQL执行
template，先执行视图SQL，然后再从中间结果执行主查询SQL

如：查询大于2块钱的数据视图
create algorithm=merge(或template) view bigpay
as
select * from payment where amount > 2
merge模式执行select * from bigpay where staff_id = 1;
实际执行select * from bigpay where staff_id = 1 and amount > 2;

template模式执行select * form bigpay where staff_id = 1；
实际先执行视图sql“where amount > 2”,再执行自己sql“where staff_id = 1 ”

尽量使用merge算法，效率高，并避免无法使用merge的sql（一些动态的sql不能合并，如聚集函数、GROUP BY、HAVING、子查询、UNION、DISTINCT）

8.现在回答如何建表更符合业务？更高效？

在这里插入图片描述

三、怎么查询更快

1、Sakila-db示例数据库

在这里插入图片描述
MySQL Workbench用来可视化查看 mwb文件（mwb文件是表的设计文件）

2、覆盖索引或叫联合索引

在这里插入图片描述
inventory_1表将联合索引删除其他不变

在这里插入图片描述
1）不使用索引，使用扫描

2）使用联合索引，若待查询为索引字段或者加一个主键字段，不需要回表

Using index方式从查询到结果使用同一条索引。
联合索引是按照索引字段排序的，若联合索引刚好包括待查询字段，则走这条联合索引就可直接查询结果。
同理：加一个主键也不需要回表，因为联合索引属于辅助索引，辅助索引需要依靠主键索引回表，辅助索引当然包括主键。
在这里插入图片描述

3)使用联合索引，但包括非索引字段，则需要回表来查询非索引字段值

3、有更合适索引不走，怎么办？强制使用某个索引

索引基数或者叫区分度越大，索引性能越好。可以使用命令show index from table查看索引区分度;
mysql使用抽样方式生成索引基数(随机选取几页，根据这几页有多个非重复值*页数得到索引基数)。
如果人为查看索引基数不合适，可以使用force index强制使用索引或者 “analyze table”重新生成索引基数

4、count（）函数

首先存储引擎查询出结果集，然后server层逐个判断值是否为null，不为null则加1。

count（非索引字段），使用全表扫描，查询出结果还需要解析出字段，并判断是否为null，效率低。
count（索引字段），不需要解析表获得字段，因为索引本身就是字段，但还需要判断是否为null。count（主键）也是一样的。
count（1）和 count（索引字段）一样，只不过送到server层检查的是1.
count（）一般用来返回表行数。MyISAM有字段记录行值。InnoDB支持事务，导致数据库中不记录行数。MySQL优化count（）直接返回Mysql认为最有的B+树，直接返回B+树种数据个数，跳过判断为null阶段。

5、ORDER BY排序优化

ORDER BY原理：
在这里插入图片描述
MySQL排序一般需要生成中间结果集、排序、回表过程，可以考虑优化这三个过程。
索引覆盖跳过生成中间结果集，是最高效的，但需要sql的筛选、排序、输出都走的同一条索引。
如果不能走索引覆盖，则考虑增大sort缓冲区大小sort_buff_size，尽量让数据在内存排序。
或者增大容纳字段max_length_for_sort_data，尽量生成全字段中间表，不要回表查询。
在这里插入图片描述

6、ORDER BY RAND（）原理

RAND（）随机生成0-1之间数据。
需求：随机生成一条数据
执行过程：创建一个临时表，遍历每一行调用rand（）函数，然后再抽取rand字段和行位置(或主键)再创建一个临时表sort_buff并排序，最后取出第一个行位置，查询第一个临时表返回结果。
在这里插入图片描述

注：临时表放在内存，则对应抽取行位置；放在硬盘，则对应抽取主键

在这里插入图片描述
解决方案一：通过sql提前确定随机值。

解决方案二：通过业务逻辑解决
先查出数据表总数，然后随机选择一个数字r，最后执行分页sql，从r开始选1行。

select title,description from film limit r,1

7、联合索引带头大哥丢了怎么办？

1.索引下推特性

mysql5.6以后出现新特性，索引下推：当where条件中出现in等范围条件，联合索引最左字段匹配多行结果，索引下推到辅助索引查找结果。（例子中先锁定sotre_id为1,2的行，然后查找film_id，找到行后再回表，只用回一次表）
5.6以前，根据最左字段查找有多行的话，会逐个根据主键回表查询辅助索引字段（例子中先锁定store_id为1，2的行，然后逐个根据inventory_id主键回表查找所有字段，找到film_id为3的行，执行多次回表效率低）

在这里插入图片描述
注：知道explain时，Extra字段出现Using index condition时就是索引下推