数据库内部结构和原理
数据库建模优化
数据库索引建立
SQL语句优化
SQL编程(自定义函数、存储过程、触发器、定时任务)
mysql服务器的安装配置
数据库的性能监控分析与系统优化
各种参数常量设定
主从复制
分布式架构搭建、垂直切割和水平切割
数据迁移
容灾备份和恢复
shell或python等脚本语言开发
对开源数据库进行二次开发
执行 mysqladmin --version命令，类似java -version如果打出消息
为了保证数据库目录为与文件的所有者为 mysql 登录用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化
mysqld --initialize --user=mysql
另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登录后你需要设置一个新的密码
查看密码：cat /var/log/mysqld.log
root@localhost: 后面就是初始化的密码
启动：systemctl start mysqld.service
关闭：systemctl stop mysqld.service
重启：systemctl restart mysqld.service
查看状态：systemctl status mysqld.service
查看进程：ps -ef | grep -i mysql
查看mysql服务是否自启动
systemctl list-unit-files|grep mysqld.service
如不是enabled可以运行如下命令设置自启动
systemctl enable mysqld.service
如果希望不进行自启动，运行如下命令设置
systemctl disable mysqld.service
## 修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
使用quit退出，重新登录
quit
mysql -u root -p
查找mysql安装位置
which mysql
whereis mysql
安装后的目录结构
字符集
字符集默认latin1字符编码，不支持中文
创建数据库
create database mydb;
使用数据库
use mydb;
建表
create table mytbl(id int,name varchar(20));
插入数据
insert into mytbl(id,name) value(1,'张三');
所以修改字符集
vim /etc/my.cnf
改完重启
systemctl restart mysqld.service
之前存在的字符集不会变
修改以前数据库的字符集
alter database mydb character set 'utf8';
修改以前数据表的字符集
alter table mytbl convert to character set 'utf8';
但是原有的数据如果是用非'utf8'编码的话，数据本身编码不会发生改变。
已有数据需要导出或删除，然后重新插入。
用户与权限管理
创建用户
create user zhang3 identified by '123123';
查看表结构
desc user;
查询密码、查询权限、插入权限、删除权限
select host,user,authentication_string,select_priv,insert_priv,drop_priv from mysql.user;
- 修改密码:
修改当前用户的密码：
set password =password('123456')
修改某个用户的密码:
必须使用root 用户
update mysql.user set authentication_string=password('111111') where user='zhang3';
**flush privileges;** #所有通过user表的修改，必须用该命令才能生效。
- 删除用户
drop user zhang3 ; 推荐使用！
不要通过delete from user where user='li4' 进行删除，系统会有残留信息保留。
- 授权命令
grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
该权限如果发现没有该用户，则会直接新建一个用户。
给li4用户用本地命令行方式下，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to li4@localhost identified by '123123';
授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123123.
grant all privileges on *.* to joe@'%' identified by '123123';
收回权限命令
revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%'; #收回全库全表的所有权限
REVOKE select,insert,update,delete ON *.* FROM joe@'%';
#收回mysql库下的所有表的插删改查权限
须用户重新登录后才能生效
查看当前用户权限：show grants;
查看某用户的全局权限：select * from mysql.user ;
## 通过工具远程访问
grant all privileges on *.* to root@'%' identified by 'root';
MySQL的sql_mode
sql_mode设置是否允许一些非法操作，比如允许一些非法数据的插入,数据查询等。
在生产环境必须将这个值设置为严格模式，所以开发、测试、生产环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。
select @@GLOBAL.sql_mode;全局的sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
select @@SESSION.sql_mode;会话级别sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，而该列又是自增长的，那么去掉该选项。
STRICT_ALL_TABLES,STRICT_TRANS_TABLES
对于支持事务的表，这两种模式是一样的：如果发现某个值缺失或非法，MySQL将抛出错误，语句会停止运行并回滚。
对于不支持事务的表，这两种模式的效果：
1、如果在插入或修第一个数据行时就发现某个值非法或缺失，那该语句直接抛错，语句停止执行。这个和支持事务的数据表行为时一样的。
2、如果在插入或修改第n个（n>1)数据行时才发现错误，那就会出现下面的情况：
2.1 在STRICT_ALL_TABLES模式下，停止语句执行，存在部分更新的问题
2.2 在STRICT_TRANS_TABLES模式下，MySQL将继续执行该语句避免“部分更新问题”，对每个非法值将其转换为最接近的合法值。
NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零
NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或不存在，那么抛出错误。不设置此值时，用默认的存储引擎替代
设置当前窗口中设置sql_mode
SET GLOBAL sql_mode = 'modes...';
SET SESSION sql_mode = 'modes...';
在/etc/my.cnf中配置sql_mode,永久生效
sql-mode = "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
MySQL架构_逻辑架构
1、连接层
2、服务处
3、引擎层
4、存储层
和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。
这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.连接层
最上层是一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。
主要完成一些类似于连接处理、授权认证、及相关的安全方案。
在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。
同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
2.服务层
第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。
所有跨存储引擎的功能也在这一层实现，如过程、函数等。
在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。
如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
2.1 Management Serveices & Utilities： 系统管理和控制工具
2.2 SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
2.3 Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析。
2.4 Optimizer: 查询优化器。
SQL语句在查询之前会使用查询优化器对查询进行优化。
2.5 Cache和Buffer： 查询缓存。
如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
3.引擎层
存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
4.存储层
数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
利用show profiles 查看sql的执行周期
了解查询语句底层执行的过程：select @@profiling;查看是否开启计划。
1)修改配置文件/etc/my.cnf，先开启查询缓存
新增一行：query_cache_type=1
重启mysql：systemctl restart mysqld
2)再开启查询执行计划
show variables like '%profiling%';
set profiling=1;
3)执行语句两次：select * from mydb.mytbl where id=1 ;
4)显示最近执行的语句
show profiles;
5)显示执行计划
这是查询第二条sql的执行计划
show profile cpu,block io for query 2；
这是查询第三条sql的执行计划
show profile cpu,block io for query 3；
执行编号3时，比执行编号2时少了很多信息，从上面截图中可以看出查询语句 直接从缓存中获取数据；
注意：SQL必须是一致的，否则，不能命中缓存。
注意：如果对数据库表进行 insert ,update ,delete 这个时候，缓存会失效！
就是说如果在两个相同的sql中间执行了insert ,update ,delete sql后，缓存会失效
查询说明
mysql的查询流程大致是：
首先，mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析，也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。
如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。
mysql解析器将使用mysql语法规则验证和解析查询；
预处理器则根据一些mysql规则进一步检查解析树是否合法。
查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。
一条查询可以有很多种执行方式，最后都返回相同的结果。 优化器的作用就是找到这其中最好的执行计划。
然后，mysql默认使用的 BTREE索引
，并且一个大致方向是：无论怎么折腾sql，至少在目前来说，mysql至少用到表中的一个索引。
https://www.cs.usfca.edu/~galles/visualization/AVLtree.html
二叉树
面试题
为什么用B+不用B
首先，B+树的查找和Ｂ树一样，起始于根节点，自顶向下遍历树。
不同的是，B+树中间节点不存储数据，只有键值和指针，而Ｂ树每个结点要存储键值和实际数据，这就意味着同样的大小的磁盘块B+树可以容纳更多节点元素，在相同的数据量下，B+树更加“矮胖”，IO操作更少 。
现代操作系统中，磁盘的存储结构使用的是B+树机制，MySQL的InnoDB引擎的存储方式也是B+树机制。
MySQL架构_存储引擎简介
查看mysql提供什么存储引擎：show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
7.2.1.InnoDB存储引擎
o大于等于5.5之后，默认采用InnoDB引擎。
oInnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
o除了增加和查询外，还需要更新，删除操作，那么，应优选选择InnoDB存储引擎。
o除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
InnoDB有聚簇索引，MyISAM没有
7.2.2.MyISAM存储引擎
oMyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
o5.5之前默认的存储引擎
索引优化分析
性能下降SQL慢 执行时间长 等待时间长
数据过多——分库分表 mycat
索引失效，没有充分利用到索引——索引建立
关联查询太多join（设计缺陷或不得已的需求）——SQL优化 添加冗余字段减少关联
服务器调优及各个参数设置（缓冲、线程数等）——调整my.cnf
SQL执行顺序
1 A、B两表共有(查询所有有部门的员工->员工和部门之间必须存在关联的数据)
SELECT * FROM t_emp a INNER JOIN t_dept b on a.deptid=b.id
2 A、B两表共有+A的独有（列出所有用户，并显示其机构信息）A的全集
SELECT * FROM t_emp a LEFT JOIN t_dept b on a.deptid=b.id
3 A、B两表共有+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.id 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 ;
------
SELECT * FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id
UNION ALL
SELECT * FROM t_emp a RIGHT JOIN t_dept b ON a.deptid = b.id ;
#1.UNION ALL 不会自动去重。
#2.UNION在使用时，两张表的字段保证一致，如果不一致，请在select后面列选字段，不要使用*
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.id IS NULL ;
求各个门派对应的掌门人名称
SELECT a.deptname,b.name FROM t_dept a LEFT JOIN t_emp b on a.ceo=b.id
求所有当上掌门人的平均年龄:
SELECT avg(a.age) FROM t_emp a
INNER JOIN t_dept b on a.id=b.ceo
求所有人，对应的掌门是谁(课堂练习，4种写法分析)
select b.name,a.name,b.deptname from t_emp a right join (
SELECT a.name,b.ceo,b.deptname FROM t_emp a LEFT JOIN t_dept b on a.deptid=b.id ) b
on a.id=b.ceo
#临时表连接方式
#step1根据ceo 去查找每个部门的掌门是谁
{SELECT b.id AS deptId,a.name AS ceoname FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo }
#step2 与员工表整合
SELECT c.name,ab.ceoname FROM t_emp c LEFT JOIN
( SELECT b.id AS deptId,a.name AS ceoname FROM t_emp a INNER JOIN t_dept b ON a.id = b.ceo ) ab
ON c.deptId = ab.deptId ;
#临时表连接方式 根据员工id查找
#step1 根据部门Id 查询员工的信息
SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id
# step2 与员工表整合
SELECT ab.name,c.name AS ceoname FROM
( SELECT a.name,b.ceo FROM t_emp a LEFT JOIN t_dept b ON a.deptid = b.id ) ab
LEFT JOIN t_emp c ON ab.ceo = c.id ;
#三表左连接方式（最好）
(t_emp 已经有人名了，差的是掌门名，所以让c去拿掌门名)
SELECT a.name,c.name AS ceoname FROM t_emp a
LEFT JOIN t_dept b ON a.deptid = b.id
LEFT JOIN t_emp c ON b.ceo = c.id ;
#子查询方式
SELECT a.name ,(SELECT c.name FROM t_emp c WHERE c.id=b.ceo) AS ceoname
FROM t_emp a LEFT JOIN t_dept b
ON a.deptid = b.id ;
三表左连接方式（最好）
评论区