本文详述了在Linux环境下安装MySQL 5.7的步骤，包括卸载旧版本、初始化数据库、设置远程连接和字符集。接着讨论了MySQL的存储引擎，特别是InnoDB与MyISAM的区别，以及何时选择MyISAM。此外，讲解了索引的原理、类型，如聚簇和非聚簇索引，并给出了创建和使用索引的建议。最后，涉及了数据库分组查询、数据缓存与缓冲区的区别，以及SQL优化实践。
目录
打开缓存，利用show profile查看sql执行周期（是否因为缓存命中而减少时间）
介绍以及命名规则
分库分表，分到两台不同的机器，表最多500w数据，库最多5000w数据
表的命名规则
在linux在安装mysql5.7
先卸载mysql
1查看数据库的版本信息
rpm -qa | grep mariadb
2.卸载数据库（强删除）
rpm -e --nodeps mariadb-libs3.卸载后，查看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:3HWEj9.进行启动
systemctl start mysqld.service 10.查看状态（看日志即可）
systemctl status mysqldcat /var/log/mysqld.log
11.登录
mysql -uroot -poJdkEa:3HWEj
12.修改密码，得到权限
mysql -uroot -p2002514wyh11quit退出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.keyselect 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
扫一扫
203
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
