SQL高级

SQL高级
目录

介绍以及命名规则

在linux在安装mysql5.7

数据存放的位置

字符集问题

远程连接

如何给用户授权

分组的问题

查询每个机构年龄最大的人

缓存和缓冲的区别：

底层逻辑架构

打开缓存，利用show profile查看sql执行周期（是否因为缓存命中而减少时间）

存储引擎

MyISAM与InnoDB的区别：

然后讲到两种存储引擎的区别(InnoDB和MyISAM)：

什么情况用MyISAM？

Archive引擎

csv引擎

其他引擎

sql预热

练习

索引简介，结构

正式讲，mysql索引结构

关于数据结构的时间复杂度：

聚簇索引

非聚簇索引

索引的使用

1.创建索引

2.哪些需要创建索引，哪些不需要

介绍以及命名规则

分库分表，分到两台不同的机器，表最多500w数据，库最多5000w数据

表的命名规则

在linux在安装mysql5.7

先卸载mysql

1查看数据库的版本信息
```
rpm -qa | grep mariadb
```
2.卸载数据库（强删除）
```
rpm -e --nodeps mariadb-libs
```
3.卸载后，查看mysql类库有没有（libaio）
```
rpm -qa | grep libaio
```
4.查看网络工具有没有
```
rpm -qa | grep net-tools
```
5.查看tmp文件权限是不是777

5.7版本不会自动初始化，得自己根据命令去编写

6.查看数据库版本
```
mysqladmin --version
```
7.初始化mysql
```
mysqld --initialize --user=mysql
```
8. 查看密码（用日志查看）
```
cat /var/log/mysqld.log
```
```
 oJdkEa:3HWEj
```
9.进行启动
```
systemctl start mysqld.service 
```
10.查看状态（看日志即可）
```
systemctl status mysqld
```
```
cat /var/log/mysqld.log
```
11.登录
```
mysql -uroot -poJdkEa:3HWEj
```
12.修改密码，得到权限
```
mysql -uroot -p2002514wyh11
```
quit退出mysql

具体数据库操作照常实现即可

13.查看mysql是否是自启动
```
systemctl list-unit-files | grep mysqld 
```
数据存放的位置

字符集问题

配置文件在/etc/my.cnf里面

加配置信息然后重启即可
```
character_set_server=utf8
```
然后重启
```
systemctl restart mysqld
```
再次登录
```
mysql -uroot -pnew_password
```
然后我们看看数据库和表的字符集

发现我们需要手动修改才行（只修改配置文件是不行的）

修改数据库和表中的字符集
```
alter table mytbl character set 'utf8';
```
```
alter table mytbl convert to character set 'utf8';
```
然后再次插入发现中文可以了

远程连接

首先我们看下系统库中的user表

切换到mysql库查看user表
```
select * from user\G;
```
查看权限

查看host，user以及权限
```
select host,user,authentication_string from user;
```
创建自定义用户
```
create user Fairy identified by '2002514wyh11';
```
发现默认是所有用户都能访问

然后我们在客户端进行测试

如何给用户授权

1.新建一个root用户进行授权，所有ip都支持，所有权限对于所有库表
```
grant all privileges on *.* to root @'%' identified by '123456';
```
2.然后进行远程登录，可以访问所有的库

远程修改密码（mysql本地库中）

不管是改密码还是改权限，改的是硬盘中的数据，需要同步，所以要flush一下

分组的问题
```
CREATE TABLE mytbl2(
  id INT,
  NAME VARCHAR(200),
  age INT,
  dept INT
);
 
INSERT INTO mytbl2 VALUES(1,'zhangsan1',33,101);
INSERT INTO mytbl2 VALUES(2,'lisi2',31,101);
INSERT INTO mytbl2 VALUES(3,'san1',35,102);
INSERT INTO mytbl2 VALUES(1,'ffu',37,102);
```
查询每个机构年龄最大的人

会报错

原因：

5.5的mysql中的sql_mode为空，所以说直接select对应字段能够出来，但是会出现杂项；

5.7有sql_mode配置，目的是严格配置
```
show variables like 'sql_mode'
```
解决：

我们sq按照deptl执行分组的select字段，是不能有dept的字段的
```
SELECT * FROM mytbl2 m INNER JOIN(SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.`dept` AND m.`age`=ab.maxage;
```
缓存和缓冲的区别：

一个是对数据的读，一个是对数据的写；

底层逻辑架构

Sql interface：sql的执行接口

parser：sql的解析器，解析复杂sql

optimizer：优化器，类似于JVM的优化的一个作用，对于sql起到一个优化

它会产生一个存储引擎，出现一个类似军师的一个作用，能够给sql执行对应的情况，得到结果后返回给你的客户端，当然，会先放一份给到我们的缓存，下次就不用对数据库操作了；

打开缓存，利用show profile查看sql执行周期（是否因为缓存命中而减少时间）

1.先在配置文件中加一条缓存信息
```
vi /etc/my.cnf
```
```
query_cache-type=1 #开启缓存
```
2.然后restart一下，重启mysql
```
systemctl restart mysqld
```
3.查看状态发现成功

4.开启缓存的开关（可以发现profiling是关的），后续要设置
```
show variables like '%profiling%';
```
5.然后设置profiling，开启执行计划（我们的存储引擎会按照执行计划执行对应的sql）
```
set profiling=1;
```
6.查询表中数据，然后看它的执行计划，看看缓存

7.查看它的一个执行计划
```
show profiles
```
可以看到执行的sql和耗时

8. 详细计划：
```
show profile cpu,block io for query 2;
```
有头有尾，从缓存开始，然后结束

1.首先等待缓存锁然后开始执行，在缓存中查询

2.checking permission查看权限

3.打开表，然后初始化

4.然后又等待缓存锁，然后optimizing进行优化，生成执行假话然后转变execute执行sql

5.然后发送数据sending data

6.查询结束后，关闭表，等待缓存锁

7.把数据写入缓存，然后clear up全部清除

再次查询发现命中缓存

注意：只有sql一样才能命中缓存，就跟map的kv键值对类似；

原理：

disnect：去重

having：再次筛选

group by：分组，执行函数

具体情况具体分析，因为会根据优化器optimizer进行sql优化

存储引擎

查看所有的存储引擎
```
show engines;
```
MyISAM与InnoDB的区别：

注：其实现在我们项目一般都不用外键，因为你用了外键就会约束表之间的关系

1.导致耦合较高，并且性能很低，比如班级学生表（学生表有班级外键），当往学生表中插入数据的时候，会扫描以便班级表，看是不是有符合的班级，这样会导致效率较低；

2.并且插入数据的时候，速度慢，一个地方错了，就全部得改（在表多的时候就是噩梦，环环串联）；

3.并且在执行删除操作的时候，也会因为外键的原因不能对单个表中的数据进行删除，比如班级学生，班级在学生中是一个外键，想删除班级而不删除学生，这就是不行的

所以我们一般都是自己逻辑判断；

然后讲到两种存储引擎的区别(InnoDB和MyISAM)：

MylSAM用的表锁，一锁就是整个表，不适合高并发

InnoDB用的是行锁，对单条数据上锁，适合高并发操作，会出现死锁情况；

缓存方面：MyISAM的话只缓存索引，没有缓存真实数据；而InnoDB，都缓存了，不过对性能方面会有影响；

事务：MyISAM不支持事务，而InnoDB支持事务

还一个很重要的区别：

1.而且MyISAM索引文件和数据文件它是一个分离的，索引文件仅仅保存数据记录的地址；

2.而InnoDB中表数据文件本身就是按照B+树组织的一个索引结构

　(图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

什么情况用MyISAM？

我们的系统表用的就是MyISAM，减少资源，比较简单业务，但是系统崩溃后数据是无法恢复的；

Archive引擎

一般用于日志和数据采集

csv引擎

是一种存储格式，每一列用逗号分割，一行用换行格式

场景：一般用于存放数据，作为Mysql的表去处理——>可以作为数据交换的一种方式

比如电商平台，你去支付下单，但钱不一定真正就扣了，我们消息队列中的消息被消息者处理是有延迟的，那么我们可以将数据存放到csv文件中，然后每天进行对账；

(26条消息) 初识MQ-01_Fairy要carry的博客-CSDN博客_mq全称

(26条消息) RabbitMQ_Fairy要carry的博客-CSDN博客

每次更新就更新csv文件

其他引擎

Memory引擎

就是缓存，重启也不会丢失，速度较快

Federated引擎

Federated引擎是访问其他Mysql服务器的一个代理

sql预热

真实开发中一般都用左外连接left join得到数据

mysql是没有全连接的，但是我们可以实现它，左外连接+右外非空
```
select xx from T A left join T B on A.key=B.key
```
```
select xx from T A right join T B on A.key=B.key where A.key is null;
```
练习

union对比与union all：拼接sql时前者会去重，后者不会去重->叠加数据

场景：有重复数据就用union进行过滤，没有重复数据就union all
```
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,
deptld INT(11) DEFAULT NULL,
empno INT NOT NULL,
PRIMARY KEY(id),
KEY idx_dept_id(deptld)
#constraint fk_dept_id foreign key(deptld) references t_dept(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
SELECT * FROM t_dept;
SELECT * FROM t_emp;
 
#内连接串联数据(ab都要求有)
SELECT * FROM t_emp a INNER JOIN t_dept b ON a.`deptld`=b.`id`;
 
 
#列出所有用户，并且显示dept信息（左外)
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptld`=b.`id`;
 
 
#所有不入门派的人(a表独有)
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.`deptld`=b.`id` WHERE b.`id` IS NULL;
 
#所有没人去的门派(b表独有)
SELECT * FROM t_dept b LEFT JOIN t_emp a ON a.`deptld`=b.`id` WHERE a.`id` IS NULL;
 
#全集
SELECT a.*,b.* FROM t_emp a LEFT JOIN t_dept b ON a.`deptld`=b.`id`
UNION 
SELECT a.*,b.* FROM t_dept b LEFT JOIN t_emp a ON a.`deptld`=b.`id` WHERE a.`id` IS NULL;
 
 
 
```
然后我们给门派表加了一个CEO字段
```
 
ALTER TABLE `t_dept` ADD CEO INT(11);
 
UPDATE t_dept SET CEO=2 WHERE id=1;
UPDATE t_dept SET CEO=4 WHERE id=2;
UPDATE t_dept SET CEO=6 WHERE id=3;
UPDATE t_dept SET CEO=8 WHERE id=4;
UPDATE t_dept SET CEO=9 WHERE id=5;
 
 
#利用b表的ceo的id与a表的人物id相关联(人物与ceo)
SELECT * FROM t_emp a INNER JOIN t_dept b ON b.`CEO`=a.`id`;
 
#基于以上求掌门CEO平均年龄
SELECT AVG(a.`age`) FROM t_emp a INNER JOIN t_dept b ON b.`CEO`=a.`id`;
 
 
#求所有dept对应的CEO名称
 
#1.
SELECT c.`name`,ab.name ceoname FROM t_emp c LEFT JOIN
(SELECT b.`id`,a.`name` FROM t_emp a INNER JOIN t_dept b ON a.`id`=b.`CEO`)ab
ON c.`deptld`=ab.id;
 
#得到dept的掌门
SELECT b.`id`,a.`name` FROM t_emp a INNER JOIN t_dept b ON a.`id`=b.`CEO`
 
#2.先查询名字和CEO
SELECT ab.name,c.`name` ceoname FROM
(SELECT a.`name`,b.`CEO` FROM t_emp a LEFT JOIN t_dept b ON a.`deptld`=b.`id`) ab 
LEFT JOIN t_emp c ON ab.ceo=c.`id`;
 
 
#3.一次性两次外连接得到帮派派主（第一次得到用户那边信息，第二次根据CEO进行筛选）
SELECT a.`name`,c.`name` ceoname FROM t_emp a 
LEFT JOIN t_dept b ON a.`deptld`=b.`id`
LEFT JOIN t_emp c ON b.`CEO`=c.`id`;
```
索引简介，结构

问：100w条数据，如果你要插入，怎么保证执行效率？

作用：

简而言之，索引就是能够帮助排好序且快速查找的数据结构

查找算法的数据结构：

以某种方式引用数据——>用的二叉树，查找速度上升，以空间换时间（当然不是二叉树，只是相关）

mysql数据和索引在不同文件中保存，索引保存在硬盘上

优势：

1.查询速度快，2.排序速度也快

缺点：

1.耗空间较大，2.增删改速度较慢，因为二叉树，n个节点，时间复杂度可能为log2n

(24条消息) 数据结构 : 数组 / 链表 / 二叉排序树增删改查的时间复杂度解析_demon_倔强青铜的博客-CSDN博客_二叉排序树删除的时间复杂度

缺点场景：

有时候会产生联表（情况就很垃圾了），查询速度变成链式，很慢

我们的mysql索引实际上就是平衡树（当不平衡时，它会帮你变成平衡）

正式讲，mysql索引结构

B树包含：数据、向下的指针、指向当前数据的指针

B+树：数据、向下的指针

mysql选的是B+树，因为我们的内存是有限的，我们B+树是没有向下的指针和指向数据的指针的；而同等内存的B+树能加载的数据比B数要多1/3（也就是说B+一次能加载的数据比B多1/3），如果没有读到数据，（发送缺页现象）就会再次读，再次发送一次IO，这样就会浪费很多内存，性能消耗过大；

关于数据结构的时间复杂度：

数组插入直接a[n]=即可

发现所有的算法都是以时间为重的，空间换时间

比如：跳表，我们每一个数据都有对应的指针，那么我们要找这个数据通过指针即可，但是缺点很明显内存up

(24条消息) 十分钟弄懂什么是跳表，不懂可以来打我_愤怒的可乐的博客-CSDN博客_跳表

哈希表的话有唯一键对应值，所以查找效率非常快，底层又是基于数组，所以插入删除都很快

至于树的话——>较稳妥我们可以使用平衡树（平衡需要时间），它不会像二叉树那样出现联表的极端情况

特点：

（1）非叶子节点最多拥有两个子节点；

（2）非叶子节值大于左边子节点、小于右边子节点；

（3）树的左右两边的层级数相差不会大于1;（重点）

（4）没有值相等重复的节点;

聚簇索引

它不是一种单独的索引类型，而是一种数据存储方式

具体是由B+树实现的，每张表的主键构造一颗B+树，同时叶子节点存放的是整张标的行记录数据

，可以将叶子节点称为数据页。每张表只能有一个聚簇索引（因为物理存储只能有一个顺序）；

而我们的InnoDB就会通过主键聚集数据，如果没有定义主键，InnoDB就会选择非空的唯一索引代替，如果还是没有的话，就会定义一个隐式的主键作为聚簇索引；

优点：

1.数据访问很快，比较聚簇索引这种存储方式->将索引和数据都保存在同一个B+树中了

2.而且对于主键的排查查找也比较快；

非聚簇索引

这里我感觉还不是很理解，个人感觉就是数据和索引是分开的，叶子节点指向是数据的地址，也就是说，我们是按照地址找到数据的（就跟你在字典找字，查的是笔画，而聚簇索引是强调的顺序，类似拼音查找）

我们的InnoDB使用的索引结构就是聚簇索引，MyISAM用的索引结构是非聚簇索引

聚簇索引和非聚簇索引的关系 - 小学生很小 - 博客园 (cnblogs.com)

索引的使用

1.创建索引
```
#单值索引，一个索引只包含单个列（指定字段名）
CREATE INDEX idx_name ON t_emp(NAME);
 
 
#唯一索引
SELECT * FROM t_emp;
CREATE UNIQUE INDEX idx_empno ON t_emp(empno);
 
 
#一下创建多条索引（复合索引）
CREATE INDEX idx_no_name ON t_emp(id,NAME,deptld); 
```
2.哪些需要创建索引，哪些不需要

那些频繁使用的字段，创建索引会导致索引保存up，内存不够用，性能down

explain
相关阅读:
TRex学习之旅七
 【web-攻击web服务器】(13.2)易受攻击的服务器软件、Web 应用程序防火墙
 iOS卡顿原因与优化
 ES6解构赋值与扩展运算符
 cc2530用中断程序控制led灯亮灭
 Opus Security利用其AI驱动的多层次优先级引擎提升漏洞管理能力
 如何基于OpenCV和Sklearn算法库开展机器学习算法研究
 【Vue3】第十四部分父子组件传参
 总抱怨Mac运行速度又卡又慢？这些方法你用得上
 音视频开发：直播推流技术指南
原文地址：https://blog.csdn.net/weixin_57128596/article/details/125431021

介绍以及命名规则

在linux在安装mysql5.7

数据存放的位置

字符集问题

远程连接

如何给用户授权

分组的问题

查询每个机构年龄最大的人

缓存和缓冲的区别：

底层逻辑架构

打开缓存，利用show profile查看sql执行周期（是否因为缓存命中而减少时间）

存储引擎

MyISAM与InnoDB的区别：

然后讲到两种存储引擎的区别(InnoDB和MyISAM)：

什么情况用MyISAM？

Archive引擎

csv引擎

其他引擎

sql预热

练习

索引简介，结构

正式讲，mysql索引结构

关于数据结构的时间复杂度：

聚簇索引

非聚簇索引

索引的使用

1.创建索引

2.哪些需要创建索引，哪些不需要