mysql 高级第一天：
重点：
1. mysql 介绍
开源的关系型数据库，有一个分支MariaDB！支持的单表数据存储 500万左右！采用GPL协议，开源自己进行二次开发！
mysql 存储数据更加灵活方便！
了解：关系型数据库：内部数据结构都是有数据库自定义的！关系表！记录这表中能有哪些数据类型，是否可以有主外键关联！mysql ，oracle非关系数据库：key -value 键值对的形式存储数据！ redis MongoDBmysql 5.7 InnoDBMariaDB -- XtraDB工作中：有一个岗位 数据库开发工程师！ 编写数据库中的常用系统函数，存储过程！PL/SQL 编程！目前高手：1. 编写好sql语句！ 基本原则2. 索引优化！3. 主从复制！安全方面4. 使用mysql 中间件 mycat 分库分表！-------------------------------------函数，存储过程讲解！了解2. mysql 安装5.7.28 版本！linux 服务器上！*** 安装：先写在mysql 的分支！rpm -e --nodeps mariadb-libsrpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm安装过程中如果出现了错误：卸掉安装好的rpm 文件重新开始！rpm -e --nodeps -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm...后续：检查版本：mysqladmin 在bin 目录！mysqladmin -- version查看mysql 进程：[root@localhost opt]# ps -ef | grep mysql# 它不是mysql 的进程！表示有一个终端在干什么事，在执行什么root 4927 3810 0 09:52 pts/1 00:00:00 grep --color=auto mysql*** mysql 初始化：mysqld --initialize --user=mysql 给mysql 用户root 生成一个密码！登录进去之后，修改密码！查看密码：cat /var/log/mysqld.loghdHM:t=R3fMN*** 启动mysql 的服务！[root@localhost opt]# systemctl start mysqld[root@localhost opt]# ps -ef | grep mysqlmysql 5280 1 7 10:19 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pidroot 5309 3810 0 10:19 pts/1 00:00:00 grep --color=auto mysql*** 登录mysqlmysql -u root -p*** 修改密码：ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)*** 重要目录：/usr/bin/var/log/mysqld.log/etc/my.cnf 核心配置文件！mysql 字符集：有乱码的情况下：肯定是字符集出现了问题！通常字符集都会设置成utf-8 国际编码字符集！错误案例：首次查看：mysql> show variables like '%char%';+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | latin1 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | latin1 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.00 sec)create database mydb;use mydb;create table mytbl (id int ,name varchar(20));以上两条sql 语句： 我们称之为 DDL 语句 {数据定义语言}执行插入语句：name ： admin 张三mysql> insert into mytbl values (1,'admin');Query OK, 1 row affected (0.00 sec)mysql> select * from mytbl;+------+-------+| id | name |+------+-------+| 1 | admin |+------+-------+1 row in set (0.00 sec)mysql> insert into mytbl values (2,'张三');ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1插入中文失败，是由字符集导致！修改设置字符集编码！vim /etc/my.cnf[client]default-character-set=utf8[mysqld]character-set-server=utf8collation-server=utf8_general_ci修改完成之后：mysql> show variables like '%char%';+--------------------------+----------------------------+| Variable_name | Value |+--------------------------+----------------------------+| character_set_client | utf8 || character_set_connection | utf8 || character_set_database | utf8 || character_set_filesystem | binary || character_set_results | utf8 || character_set_server | utf8 || character_set_system | utf8 || character_sets_dir | /usr/share/mysql/charsets/ |+--------------------------+----------------------------+8 rows in set (0.01 sec)再次执行依然失败！原因：我们创建库的时候，还有创建表的时候，已经是拉丁字符集了！mysql> insert into mytbl values (2,'张三');ERROR 1366 (HY000): Incorrect string value: '\xE5\xBC\xA0\xE4\xB8\x89' for column 'name' at row 1alter database mydb character set 'utf8';alter table mytbl convert to character set 'utf8';注意：修改完成之后，对新创建的数据库，新创建的表才能生效！原来创建的不生效！正常：mysql 安装完成之后，第一件事就是要修改字符集！vim /etc/my.cnf[client]default-character-set=utf8[mysqld]character-set-server=utf8collation-server=utf8_general_ci3. mysql 基本使用mysql 用户权限：{了解范围}创建用户：create user [用户名] identified by [密码];新创建的用户只能登录，并且只能看到一个系统库！其他不允许用户表信息：mysql> select host, user,Select_priv,Insert_priv,Update_priv,Create_priv ,authentication_string from user;+-----------+---------------+-------------+-------------+-------------+-------------+-------------------------------------------+| host | user | Select_priv | Insert_priv | Update_priv | Create_priv | authentication_string |+-----------+---------------+-------------+-------------+-------------+-------------+-------------------------------------------+| localhost | root | Y | Y | Y | Y | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B || localhost | mysql.session | N | N | N | N | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || localhost | mysql.sys | N | N | N | N | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE || % | zhang3 | N | N | N | N | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |修改用户密码：用zhang3账号登录进来 再修改！ 了解set password = password('123456')使用root 超级管理员修改！ [推荐]update mysql.user set authentication_string = password('111111') where user='zhang3';flush privileges; 刷新修改的内容！删除用户：drop user [用户名];扩展：删除表，数据库drop table [表名];drop database [数据库名];删除表中的数据delete from [表名];truncate table [表名];delete 与 truncate 区别?delete 可以回滚数据！delete 删除数据的时候，会记录日志信息，通过回滚rollback 可以读取日志将数据获取回来！truncate 不可以回滚数据！truncate 删除数据的时候，不会记录日志信息，直接删除！mysql 数据库 默认事务是开启，还是关闭？默认事务开启： commit rollback;delete from wucap_nc_voucher;rollback;回滚失败！为什么?因为mysql 事务默认提交！show VARIABLES LIKE 'autocommit';autocommit on！set autocommit = 0; 关闭默认提交功能！set autocommit = 1; 开启默认提交功能！从这一点上来讲：mysql 删除数据的时候就不是很安全！oracle 数据库删除相对安全！授予权限：grant all privileges on *.* to joe@'%' identified by '123123';all：表示所有权限！ insert upadte delete create 等...*: 第一个* 表示任意库:*: 第二个* 表示人意表；joe: 表示用户作用：授予权限并创建一个新的用户！回收权限：REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';查看权限：select host, user,Select_priv,Insert_priv,Update_priv,Create_priv ,authentication_string from user;给root 账号 设置远程访问权限：grant all privileges on *.* to root@'%' identified by 'root';如果远程链接失败了，如何处理?1. ping通 虚拟机ip2. 关闭防火墙，放行端口linux window 都关闭了！杂项配置： sql_mode案例：select id, name, deptId from t_emp group by deptId;"windows" 中的mysql 执行上述的sql 语句！现在执行通过！windows 下：select @@SESSION.sql_mode;STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONlinux 下:mysql> select @@SESSION.sql_mode;+-------------------------------------------------------------------------------------------------------------------------------------------+| @@SESSION.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 |+-------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)linux 下的mysql 相对交全！linux 下 查询每个部门年龄最大的人！mysql> select * from mytbl2;+------+--------+------+------+| id | NAME | age | dept |+------+--------+------+------+| 1 | zhang3 | 33 | 101 || 2 | li4 | 34 | 101 | max| 3 | wang5 | 34 | 102 || 4 | zhao6 | 34 | 102 || 5 | tian7 | 36 | 102 | max+------+--------+------+------+执行通过 ：select max(age) maxAge,dept from mytbl2 group by dept;执行未通过：select max(age) maxAge,name,dept from mytbl2 group by dept;mysql> select max(age) maxAge,name,dept from mytbl2 group by dept;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregatedcolumn 'mydb.mytbl2.NAME' which is not functionally dependent on columns inGROUP BY clause; this is incompatible with sql_mode=only_full_group_by正确的sql语句：1. 先找到每个部门的年龄最大！mysql> select max(age) maxAge,dept from mytbl2 group by dept;+--------+------+| maxAge | dept |+--------+------+| 34 | 101 || 36 | 102 |+--------+------+2. 可以根据上述的结果：maxAge dept 与 原有表进行关联查询！select m.* from mytbl2 m inner join (select max(age) maxAge,dept from mytbl2 group by dept) mid on m.age = mid.maxAge and m.dept = mid.dept;+------+-------+------+------+| id | NAME | age | dept |+------+-------+------+------+| 2 | li4 | 34 | 101 || 5 | tian7 | 36 | 102 |+------+-------+------+------+windows:执行通过 ：select max(age) maxAge,dept from mytbl2 group by dept;执行通过：select max(age) maxAge,name,dept from mytbl2 group by dept;但是，执行之后获取的结果是错误的！*** 结论：windows 与 linux 的sql_mode 值不一样！w: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONL: 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如果sql_mode 里面有 ONLY_FULL_GROUP_BY 时，则要求使用group by 时必须按照严格模式使用！要遵循使用group by 的规则：查询项， 必须是组函数[聚合函数] 或者 是分组字段！要想修改sql_mode 的值！ 最好在核心配置文件中编写！vim /etc/my.cnfsql-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 5.7 安装在linxu 中的sql_mode 是相对交全的配置！而window 5.7 sql_mode 相对交弱！4. mysql 逻辑架构：1. 连接层：建立本地通信，链接mysql 的服务层： JDBC ,C#....2. 服务层: *****连接池：管理工具：SQL interface:接收用户传入的SQL！解析器：校验用户编写的sql 语句的语法是否正确！优化器：选择一种最优的查询方案！缓存：存储用户执行过后的数据！编写一条sql 语句：第一次执行：select max(age) maxAge,dept from mytbl2 group by dept;第二次执行：select max(age) maxAge,dept from mytbl2 group by dept;3. 引擎层：MYSQL 5.7 默认使用的InnoDB;4. 存储层：获取数据！使用show profiles 主要的目的是查看sql 语句的执行过程！vim /etc/my.cnf ;query_cache_type=1 # 开启缓存！开启profilingset profiling = 1;执行sql ：连着执行两次！select * from mydb.mytbl where id=1;不一样的SQL：SELECT * from mydb.mytbl where id=1;查看执行的SQL：mysql> show profiles;+----------+------------+-------------------------------------+| Query_ID | Duration | Query |+----------+------------+-------------------------------------+| 1 | 0.00270375 | show variables like '%profiling' || 2 | 0.01592750 | select * from mydb.mytbl where id=1 || 3 | 0.00010575 | select * from mydb.mytbl where id=1 |+----------+------------+-------------------------------------+3 rows in set, 1 warning (0.00 sec)查看执行计划：show profile cpu ,block io for query Query_ID;缓存生效的前提是：1. 两条语句要一模一样！执行 ： select * from mydb.mytbl where id=1; 两次！ 第二次查询使用了缓存！执行 ： SELECT * from mydb.mytbl where id=1; 一次！ 没有使用缓存！再执行：select * from mydb.mytbl where id=1; 一次！ 使用了缓存！select * from mydb.mytbl where id=2;select * from mydb.mytbl where id>1 and id<3;2. 两条语句中间不能有INSERT ,UPDATE ，DELETE 操作！select * from mydb.mytbl where id=1; 有缓存的！insert into mydb.mytbl values (3,'li4'); 对原有数据集进行更改，那么缓存中的数据就失效！select * from mydb.mytbl where id=1;mysql 查询：要想查找数据快!需要使用索引, 新华字典的目录！ a -- z ;底层索引结构 B+TREE , 无论sql 怎么写，正常都会使用到mysql 的索引！5. 数据结构以及时间复杂度：线性结构：顺序存储：存储数据的方式连续的链式存储：存储数据的方式不连续的非线性结构：图，树时间复杂度：Ο(1)＜Ο(log2N)＜Ο(n)＜Ο(nlog2N)＜Ο(n^2)＜Ο(n^3)＜ Ο(n^k) ＜Ο(2^n)结论：时间复杂度最好的是：哈希，平衡树次之！
mysql 第二天：
回顾：
1. 安装
推荐使用rpm方式：
细节：卸载Mariadb, 检查依赖
common
libs
client
server
初始化登录mysql 然后修改密码！
mysqld --initialize --user=mysql
看log日志中会有初始化密码，使用初始化密码登录，然后修改密码！
cat /var/log/mysqld.log
mysql -u root -p
2. 设置mysql 的相关配置
字符集：
核心配置文件： /etc/my.cnf
vim /etc/my.cnf
[client]
default-character-set=utf8
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
创建数据库，创建表 都是中文！
sql_mode:杂项配置：
如果安装的linux 版本就不用配置了。
如果安装的windows 配置，与linux 相同，特别是 ONLY_FULL_GROUP_BY!
使用Group by 的规则：
查询项 是分组字段 或 组合函数。
linux : select name ,deptId from emp group by deptId;
windows : select name ,deptId from emp group by deptId;
请问上述sql 是否执行通过? 为什么?
是否能执行通过主要依据sql_mode 配置，如果配置中有 ONLY_FULL_GROUP_BY 则不能执行通过！反之！
3. mysql 的用户与权限：
了解：create user [用户名] identified by [密码];
权限：select,update,insert ...
// 授权+创建
grant all privileges on *.* to atguigu@'%' identified by '123123';
// 回收权限：
revoke all privileges *.* from atguigu@'%';
// 删除用户：
drop user [用户名];
truncate 与 delete 区别！
truncate 全部删除数据，不会写日志，delete 会有日志记录，可以回滚！
mysql 默认是开启事务的，但是这个事务是默认提交的！
oracle 默认是开启事务的，但是这个事务是默认提交是关闭的！
查看用户权限;
select host ,user from mysql.user;
4. mysql 逻辑架构：
连接层：
服务层：
记住解析树:
引擎层：
存储层：
记住执行过程：
select name ,empno from emp;
select name ,empno from emp;
看sql 执行计划：show profiles;
mysql 底层使用的索引结构是B+TREE !
5. 数据结构：
哈希最好，平衡树次之！
重点：
1. 了解树：
2. 索引：
算法：
时间复杂度： 运算次数！
空间复杂度： 内存空间！
应用程序好或者坏 ，或者说运行的速度快，慢！ 通常都与时间复杂度有关系！
mysql 底层索引结构为什么使用B+TREE ,而不使用Hash?
1. 因为 哈希出现范围查看时间复杂度会退化 O(n);
2. mysql 5.7 默认使用的InnoDB ，但是InnoDB 不支持哈希！
选择 平衡树！
树：
1. 普通二叉树：
在特殊情况下，会导致时间复杂度上升！
2. 平衡树：
在插入数据的时候，子节点会自动旋转，在旋转的过程中肯定会消耗一定的性能！平衡树它的左子树，右子树高度差不能超过1！
优点：查询的时候，会比普通的二叉树效率要高！
缺点：
1. 在旋转的过程中会有性能损耗！（io操作）
2. 当存储数据量很大的时候，这棵树就会变的瘦高！
解决方案：
将瘦高--->矮胖！
在节点中存储多个元素！
在平衡树的基础上，引出一个红黑树！
介绍：
红黑树：要求左子树与右子树的高度差为2！减少旋转次数来提高效率！
3. BTree
将瘦高--->矮胖！
id name age;
1 zs 18
2 li4 19
...
5 admin 20
...
select * from stu where id = 5; 检索的过程中加载3个磁盘块！ 叶子节点
select * from stu where id = 8; 检索的过程中加载2个磁盘块！ 非叶子节点
select * from stu where id = 17;检索的过程中加载1个磁盘块！ 非叶子节点
BTree 中磁盘块中：存储的内容有： 指针，[键值，数据]
***** BTree 的叶子节点与非叶子节点中存储这 这个表中的每一行数据！
叶子节点： 这棵树的最后的节点！
非叶子节点：根节点+根节点下的子节点！
磁盘块中 ：存储指针，键值，数据！
可以对BTree 优化:如果在磁盘块大小固定的情况下， 不存储数据[name,age这两列]，只存储 指针，键值！这样在磁盘块中就能够存储更多的指针与键值！
4. B+Tree：
与BTree 最大的区别在于数据的存储！
BTree 数据存在 【叶子节点与非叶子节点】
B+Tree 数据只存在 【叶子节点】 ，叶子节点都是相连的！
在相同的数据量情况下，B+TREE 更加容易矮胖！
例如：存储100万条数据，哪个树的结构更加合适！
id name age;
1 zs 18
2 li4 19
...
5 admin 20
...
100w;
磁盘大小固定的 16 KB;
B+Tree 更适合作为mysql 的索引的数据结构！
结论：
mysql 底层索引结构使用B+TREE！
存储引擎： 了解：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 |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql 5.7 默认 存储引擎：InnoDB;
面试题：*****
InnoDB 与 MYISAM 区别?
5.5以后默认InnoDB;
5.5以前默认MYISAM;
InnoDB： 支持事务，支持行锁，支持外键，保存点，缓存索引+真实数据，处理复杂业务{并发，事务}
MYISAM: 支持表锁！缓存的索引，处理简单业务
保存点了解：
mysql ：数据库开发工程师 【pl/sql 系统的存储过程，函数等。savepoint a ; working 100行sql, 修改了昨天的业务逻辑 中午 ，
下午 组长来了，不符合业务逻辑，go to a; 类似于git 版本控制】
七种Join：
t_emp a; t_dept b;
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全有（查询所有员工和所有部门）
组合两个结果集用的！
union: 去掉重复部分
union all: 不会去掉重复部分！
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;
发现共有的部分：只显示一次！
扩展内容：
oracle: 这个叫全链接 full outer join
select * from emp a full join dept b on a.deptno = b.deptno;
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;
8. 扩展掌门人：
a. 求各个门派对应的掌门人名称 {left inner}
CEO: 就是掌门人的编号
mysql> select * from t_dept;
+----+----------+-----------+------+
| id | deptName | address | CEO |
+----+----------+-----------+------+
| 1 | 华山 | 华山 | 2 |
| 2 | 丐帮 | 洛阳 | 4 |
| 3 | 峨眉 | 峨眉山 | 6 |
| 4 | 武当 | 武当山 | 8 |
| 5 | 明教 | 光明顶 | 9 |
| 6 | 少林 | 少林寺 | NULL |
+----+----------+-----------+------+
mysql> select * from t_emp;
+----+--------------+------+--------+--------+
| id | name | age | deptId | empno |
+----+--------------+------+--------+--------+
| 1 | 风清扬 | 90 | 1 | 100001 |
| 2 | 岳不群 | 50 | 1 | 100002 |
| 3 | 令狐冲 | 24 | 1 | 100003 |
| 4 | 洪七公 | 70 | 2 | 100004 |
| 5 | 乔峰 | 35 | 2 | 100005 |
| 6 | 灭绝师太 | 70 | 3 | 100006 |
| 7 | 周芷若 | 20 | 3 | 100007 |
| 8 | 张三丰 | 100 | 4 | 100008 |
| 9 | 张无忌 | 25 | 5 | 100009 |
| 10 | 韦小宝 | 18 | NULL | 100010 |
+----+--------------+------+--------+--------+
select * from t_dept b left join t_emp a on b.ceo = a.id;
select * from t_dept b inner join t_emp a on b.ceo = a.id;
b. 求所有当上掌门人的平均年龄:
组函数/聚合函数 如果这个列为NULL ,它会算在内么？ " 不会！"
select avg(a.age) from t_dept b inner join t_emp a on b.ceo = a.id;
c. 求所有人，对应的掌门是谁(课堂练习，4种写法分析)
预想结果：
------------------------------
name ceoname
风清扬 岳不群
岳不群 岳不群
令狐冲 岳不群
洪七公 洪七公
乔峰 洪七公
灭绝师太 灭绝师太
周芷若 灭绝师太
张三丰 张三丰
张无忌 张无忌
------------------------------
韦小宝
思路：写多表关联查询的思路与方法！
1. 先找到相关的表！
t_emp; 员工表
t_dept; 部门表
2. 确定表与表之间的关联方式,并且确定好关联条件！
left join on
right join on
inner join on
union
3. 看是否需要有过滤条件！ 综合调试写出适合业务的sql 语句！
a.deptId = b.id a.id = b.ceo;
第一种方式：
SELECT a.name,c.name 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 c.name,(SELECT a.name ceoname FROM t_emp a WHERE a.id = b.`CEO`) ceoname FROM t_emp c
LEFT JOIN t_dept b ON b.`id` = c.`deptId`;
第三种方式：临时表 a.deptId = b.id
SELECT a.name ceoname, m.name FROM t_emp a RIGHT JOIN (SELECT c.name,b.ceo FROM t_emp c LEFT JOIN t_dept b ON b.`id` = c.`deptId`) m
ON m.ceo = a.`id`;
相等
SELECT m.name,a.name ceoname FROM (SELECT c.name,b.ceo FROM t_emp c LEFT JOIN t_dept b ON b.`id` = c.`deptId`) m LEFT JOIN t_emp a
ON m.ceo = a.`id`;
第四种方式：临时表 a.id = b.ceo;
SELECT c.name, m.ceoname ceoname FROM t_emp c LEFT JOIN (SELECT a.name ceoname , b.id deptid FROM t_emp a INNER JOIN t_dept b ON a.id = b.`CEO`) m
ON c.`deptId` = m.deptid;
mysql 高级第三天：
回顾：
1. 时间复杂度，还有算法从这两方面来讲：
哈希最好，平衡树次之！
哈希在范围查找的时候，它的时间复杂度会变成O(N),不如平衡树！
2. 树：
二叉树：
平衡二叉树：高度差1
延伸红黑树：高度差2
BTree：
节点下可以有多个子节点
数据分布在叶子节点+非叶子节点上！
每个节点都有数据！
磁盘块：指针，键值，数据！ 默认16KB!
固定磁盘块大小 16KB! 相对磁盘块中存储的指针就会少！
数据存储量相对少！
B+Tree：
BTree 升级版！
数据都存储在叶子节点上！并且叶子节点都是连续的！
非叶子节点磁盘块：指针，键值！
存储叶子节点磁盘块中：指针，键值，数据！
存储100万条数据，哪个树的结构更加合适！
B+Tree 更适合作为mysql索引数据存储！
mysql 的索引底层使用的数据结构？为什么不使用BTree 而使用B+TREE！
1. B+Tree 能够存储更多的数据 【数据都存储在叶子节点上！】
2. mysql 默认的存储引擎InnoDB，InnoDB 不支持哈希索引！
注意：mysql 数据库不支持哈希索引，这句话对么？
InnoDB 不支持哈希索引！
InnoDB 在内部利用哈希索引来实现其自适应哈希索引功能 不用！范围的查找的时候就死掉了。通常都将其关闭！
支持：MVCC 是一种控制并发的方法，作用提高数据的并发性能！
MEMORY/HEAP 支持哈希索引，BTree 索引！
哈希索引o(1) select * from stu where id = 2; 但是，有范围查找的时候hash 索引就不好了！BTree好、1
InnoDB 与 MYISAM 区别?
5.5以后默认InnoDB;
5.5以前默认MYISAM;
InnoDB： 支持事务，支持行锁，支持外键，保存点，缓存索引+真实数据，处理复杂业务{并发，事务}, MVCC
MYISAM: 支持表锁！缓存的索引，处理简单业务
3. MySQL 7 中join！
left join
right join
inner join
union / union all
总结过写多表关联查询的步骤：
1. 先找到相关的表！
2. 确定表与表之间的关联方式,并且确定好关联条件！
3. 看是否需要有过滤条件！ 综合调试写出适合业务的sql 语句！
重点：
Index 索引：
索引：就是一个数据结构[B+Tree]，目的：提高检索数据！索引是以文件的形式存储在磁盘上的！
索引它的磁盘页：默认大小16KB, 加载的时候是根据页加载，并非全部加载所有的索引文件！
优势：
1. 提高检索效率
2. 降低排序成本
劣势：
1. 所以是以文件的形式存储在磁盘上的，本质也是一张表，这个表保存的主键与索引字段。 需要占用空间的！
2. 索引能够提高检索效率，但是，对于表的INSERT ,UPDATE ,DELETE 操作来讲，会降低表的响应速度！
聚簇索引：
数据与索引放在一起的！
select * from stu where id = 9; 根据聚簇索引查询！只查询一次，就可以了！
好处：提高检索效率，节省Io成本！
限制：
只有InnoDB 支持聚簇索引，通常都是表的主键{id}！
如果表中没有主键id, 则InnoDB 会使用唯一键{card,iphoneNo}当做聚簇索引，那么唯一键也没有呢? 表会有一个默认的rowId，当做聚簇索引！
建议使用有序增长的Id，不建议使用无序的UUID！
非聚簇索引{辅助索引}：
反之....
在查找的时候，可能会查收回表！
stu 表
key index
id ,name, age , sex;
id 主键：聚簇索引： 索引与数据是在一起的！
select * from stu where id = 9; 只需要查询一次就可以获取到数据了！
select * from stu where name = 'c'; 这个查询需要两次，会产生回表！
1. 先在name 这棵索引树中找到c对应的键值 9;
2. 根据键值9在聚簇索引中找到其他数据！
非聚簇索引下：存储的数据”键值“
只要是通过非聚簇索引去查找就会产生回表？
不一定！ 跟覆盖索引有关系！
select id,name from stu where name ='c'; 会产生回表么? 不会！
因为：使用了覆盖索引！
覆盖索引：select 到 from 之间 查询的项 是索引列
索引的分类：
单值索引：索引中值包含一个列！
唯一索引：索引列的值必须唯一，但允许有空值
主键索引: 表中的主键就是索引，InnoDB 默认主键是聚簇索引！
复合索引：索引中值包含多个列！
创建索引语法：
create [unique] index idx_name on mytbl(name...);
查看索引：
show index from mytbl;
删除索引：
drop index idx_name on mytbl;
**** 哪些情况需要创建索引！
1)主键自动建立唯一索引
三大范式规定：表中必须有主键!
2)频繁作为查询条件的字段应该创建索引
select * from stu where stuNo = 789;
iphoneNo;
3)查询中与其它表关联的字段，外键关系建立索引
班级表： Id
学生表： classId
4)单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
create index idx_name_no on stu (name,iphoneNo);
5)查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
排序：通常都是按照Id 进行排序！
6)查询中统计或者分组字段
select avg(age), deptId from t_emp group by deptId;
create index idx_deptId on t_emp(deptId);
思考：排序和分组哪个更伤性能？
数据库三大范式：了解
1. 保证字段拆分到不可拆分为止！
2. 保证每列数据要与主键有关系！ 创建表的时候，规定必须要有主键！
3. 保证每列数据要与主键有直接或者间接关系！
**** 哪些情况不需要创建索引！
1)表记录太少
创建索引跟表的记录总条数有关系！比如说字段多少，字段的数据类型！
500万数据时MySQL性能就开始下降了，这时就可以开始开始优化了
2)经常增删改的表
提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
3)where条件里用不到的字段不创建索引
id,
name,
age,
sex,
address,
isdelete,
status,
createTime,
updateTime
where id = '' ;创建了索引 createTime ,updateTime 不需要的！
explain 关键字：
explain：查看sql 的执行计划 {包含索引的使用状况！}
explain sql 语句！
id：
相同：
从上到下依次执行！
不同：
id 值越大，越先执行！
相同，不同：
相同是从上倒下执行，不同的时候大的先执行！
type：
从最好到最差排序！
system>const>eq_ref>ref>range>index>ALL
system：系统表，只有一条记录
const：通过主键索引或唯一索引，一次命中！
eq_ref: 通过主键，唯一索引 扫描只有一条记录与之匹配！
ref: 通过单值数据，可能找到多条数据记录！
range：范围匹配！
index：遍历整棵索引树！ 【覆盖索引！explain select id,content from t1; useing Index ;】
ALL：表示全磁盘扫描！
key_len: 代表key 的实际长度：
通常来讲key 的值越大越好！
需要记住算法！
rows：
表示执行查询时必须检查的行数，这个值越小越好！
Extra：额外的配置！
1. Using filesort：表示排序字段没有使用上索引！
使用的是小表 t_emp;
id:主键索引
name:普通索引
age : 非索引也就一个普通的字段！
demo1:
explain select id,name,age from t_emp order by name limit 10;
create index idx_name on t_emp(name);
explain select id,name,age from t_emp order by name limit 10;
Using filesort; 有！
demo2:
explain select id,name from t_emp order by name limit 10;
create index idx_name on t_emp(name);
Using filesort; 没有！
Using index; 覆盖索引!
a. 给排序后面的字段创建索引，同时看是否有过滤条件等，如果有过滤条件的话，则需要创建”复合索引“
b. 或者使用覆盖索引都能去掉Using filesort;
如果没有去掉useing filesort,则mysql 会启动默认的排序规则： 称之为：文件排序----> “单路排序，双路排序”！
2. Using temporary：表示分组没有使用上索引！
分组的时候，需要先排序的，所以会出现Using filesort!
3. Using index :
表示使用了覆盖索引！
4. using join buffer：
表示关联条件没有使用上索引！
以后看sql语句执行的快慢，或者是在索引的选择上，以上多个条件要综合判断！
直接执行sql！
案例实战：
1. 创建两个数据量比较大的表！ emp dept;
案例：
1. 全值匹配我最爱
在where 后面 将过滤条件加上索引！
2. 最佳左前缀法则
不能跳过索引字段！
3. 计算、函数导致索引失效！
4. 范围条件右边的列索引失效！
解决方案：
可以将范围条件放在where 的最后面，再根据实际情况创建索引！
mysql 高级第四天：
回顾：
索引：底层的数据结构，InnoDB B+Tree
什么是聚簇索引：
数据和索引在一起！
InnoDB 默认表中的主键Id！ 就是聚簇索引 ，聚簇索引叶子节点存储的是一整行记录！
表中如果没有主键，则会使用唯一键作为聚簇索引，如果没有唯一键，则会生产一个rowId 的作为聚簇索引！
非聚簇索引【辅助索引】：
叶子节点存储的数据，键值！
什么是回表?
create table stu(
id int primary key ,
name varchar(20),
address varchar(200),
age int
);
create index idx_name on stu(name);
id 主键 ；聚簇索引,主键索引!
name 单值索引，非聚簇索引！
select * from stu where id = 10; 一次查询！
叶子节点存储的是一整行记录！
select * from stu where name = 'zhang3'; 两次查询！
1. 需要在name 这棵索引树找到 zhang3 对应的键值 。
2. 根据这个键值去id 聚簇索引找到其他数据。
这条sql 就会产生回表！
非聚簇索引一定会产生回表么？
不一定！
select * from stu where name = 'zhang3'; 会产生回表！
select id ,name from stu where name = 'zhang3'; 不会产生！ 覆盖索引！
select 项 是索引字段，这个时候，可能用上覆盖索引，如果出现覆盖索引，则在Extra 区域；会产生using index！
索引下推：
create table stu(
id int primary key ,
name varchar(20),
address varchar(200),
age int
);
sql: select * from stu where name like '陈%' and age = 20;
create index idx_name_age on stu(name,age);
mysql 5.6 以前版本：
idx_name_age索引树： 聚簇索引
name age id id name address age
陈道明 1 1 陈道明 bjcp 60
陈乔恩 2 2 陈乔恩 bjdx 20
张无忌 3 3 张无忌 bjcy 80
1. 根据name 陈% 找到 id 为 1,2的键值 [陈道明,陈乔恩]
2. 根据age 20 的数据！ 陈乔恩！
产生回表次数较多！两次回表！
mysql 5.6 以后版本：
idx_name_age索引树： 聚簇索引
name age id id name address age
陈道明 60 1 1 陈道明 bjcp 60
陈乔恩 20 2 2 陈乔恩 bjdx 20
张无忌 80 3 3 张无忌 bjcy 80
1. 根据name and age 去索引树查找数据！ 找到 id = 2 ，然后根据2 去聚簇索引找！
相当于产生了一次回表！
mysql 5.7 会将age 这字段的值直接添加到这个索引树上！
上述的存储方式，就是索引下推！
索引下推：目的减少回表次数，从而提高查询效率！
这个就是索引下推标识： Using index condition
索引的分类：
单值，
复合，
唯一，
主键,
........
大表数据创建：函数，存储过程！
explain 关键字的使用：
explain sql语句：
工作中你的sql 能够达到 eq_ref ，range 就可以了
哪些情况创建索引：
哪些情况不适合创建：
案例测试：
重点：
索引案例测试：
主从复制：
mycat 技术：
索引案例测试：单表注意事项！
1. 全值匹配我最爱
在where 后面 将过滤条件加上索引！
2. 最佳左前缀法则
不能跳过索引字段！
3. 计算、函数导致索引失效！
4. 范围条件右边的列索引失效！
解决方案：
可以将范围条件放在where 的最后面，再根据实际情况创建索引！
5. 不等于(!= 或者<>)索引失效
不建议使用！
6. is not null无法使用索引，is null可使用索引
七种JOIN 的时候，获取到某个表的独有 判断 id is null!
7. like以通配符%开头索引失效
不建议使用开头为%!
select * from sku where name like '%?%';
这样会导致索引失效！查询速度就慢！
因此：电商中的全文检索内容 不是从数据库查询出来！ 而是使用企业级搜索引擎 es 存储的数据！
8. 类型转换导致索引失效
建议：
对于单键索引，尽量选择过滤性更好的索引（例如：手机号，邮件，身份证）
在选择组合索引的时候，过滤性最好的字段在索引字段顺序中，位置越靠前越好。
选择组合索引时，尽量包含where中更多字段的索引
组合索引出现范围查询时，尽量把这个字段放在索引次序的最后面
尽量避免造成索引失效的情况
关联查询优化：
1. 左关联：
创建索引的时候，尽量在右表{被驱动表} 创建索引，避免全表扫描！
2. 内连接：
EXPLAIN SELECT SQL_NO_CACHE * FROM class INNER JOIN book ON class.card=book.card;
EXPLAIN SELECT SQL_NO_CACHE * FROM class ,book where class.card = book.card;
mysql 会自动识别哪张表作为驱动表！
inner join 和 where 区别?
inner join : 显性链接
where : 隐性链接，可能会产生笛卡尔积！
mysql 优化器： 会自动将where 转换成功 inner join！ 则不会产生笛卡尔积！
子查询：
SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL; 使用 IS NOT NULL 导致索引失效！
SELECT SQL_NO_CACHE * FROM t_emp WHERE age IS NOT NULL;
SELECT SQL_NO_CACHE b.ceo FROM t_dept b WHERE b.ceo IS NOT NULL;用了！
原因是啥? 查询项 就是索引！
结论：
NOT IN --> LEFT JOIN xxx ON xx WHERE xx IS NULL
尽量不要使用not in 或者 not exists
left join --- ref
子查询 --- range
排序、分组优化：
1. 无过滤 不索引
Using filesort 排序字段没有使用上索引！ 建立索引，索引要想被使用，必须得有个过滤条件！
t_emp; emp; 两张表，结构一样，索引一样！
执行了同一条语句结果确不一样！ EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10;
# 结果：大表去掉了Using filesort,小表没有去掉！
emp : 50万
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM emp ORDER BY age,deptid LIMIT 10;
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | index | NULL | idx_age_deptId | 10 | NULL | 10 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+----------------+---------+------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
t_emp 10条！
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM t_emp ORDER BY age,deptid LIMIT 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | t_emp | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 2 warnings (0.00 sec)
原因：
唯一不一样的点在于数据量！ 当数据量小的时候，mysql的排序，不需要使用索引排序速度可能要比使用索引排序速度快！底层的优化器
自动选择是否需要使用索引！
2. 顺序错，比排序：
排序字段顺序，要与创建索引顺序一致！
3. 方向反 必排序:
索引选择：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age = 30 AND empno <101000 ORDER BY NAME ;
第一种： 选择age name 排序
CREATE INDEX idx_age_name ON emp(age,NAME);
查询数据时间：
49 rows in set, 1 warning (0.09 sec)
第二种： 选择age empno 过滤
CREATE INDEX idx_age_empno ON emp(age,empno);
查询数据时间：
49 rows in set, 1 warning (0.00 sec)
第二种速度杠杆滴！
原因：
在排序或过滤中选择索引！
排序：过滤的时候可能需要很多时间 50万条！ 过滤 49！ 这个时间消耗在过滤上了！
50万条！ 49万条！
由于过滤出来的数据量非常大，采用排序使用索引，效率才会高！
50万条！ 剩下49！
如果 过滤的数量少，再使用排序，效率就不会特别明显！ mysql 本身的排序方式也很快！
应该将索引都用在过滤的条件上！
检索速度提高了，剩下的数据交给mysql 本身排序方式！
排序方式：
双路排序： 慢！
两次扫描！
第一次扫描：读取指针，数据列！
第二次扫描：扫描其他字段！
单路排序： 快！
一次扫描！
需要将所有的字段，全部读取出来，然后在排序！ 但是需要足够的空间！
对排序的优化：
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
减少select 后面的查询的字段。 禁止使用select *
设置配置文件！
vim /etc/my.cnf;
GROUP BY 优化 跟 ORDER BY !
1. 默认先排序再分组！
2. 能写在where 中，就不要写在having！
3. group by 不需要过滤也可以使用索引！
思考：排序和分组哪个更伤性能？
分组！
覆盖索引：Using Index 如果出现了这个表示，则表示使用了覆盖索引！
再次重申 禁止使用 select * ;
慢查询日志：了解！
是什么？
将运行时间比较长的sql 语句，记录到日志中！
这个时间是由你自己决定的； time > 0.1s
long_query_time
怎么用： 通常在项目开发阶段，为了写出完美高效的SQL。
默认关闭：
手动开启：
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
SHOW VARIABLES LIKE '%long_query_time%';
查看默认时间：
SET SESSION long_query_time=0.1;
以配置 都可以放入/etc/my.cnf
测试两个sql ：
cat /var/lib/mysql/localhost-slow.log
但是，项目中，运行的sql 有很多。 如果有大量的sql > 0.1s
查看格式：
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
视图：
是什么：由sql 语句组成的一个虚拟机的表，类似于快照！
作用： 记录当前的数据，提高数据结果集的复用性！ 【能够随着数据的变化而变化】。
场景： 报表【公司有自己的开发规约，有的公司，限制使用视图！】
语法：
创建 or 修改
create or replace view v_name as
sql 语句;
删除：
删除整个view
drop view v_name;
向原表中插入数据！
INSERT INTO t_emp (NAME,age,deptId,empno) VALUES ('岳灵珊',18,1,100011);
主从复制：
基本原理：
两日志{二进制日志文件，中继日志文件}，两线程{sql 线程，io 线程}！
1. 主机将所有的操作写入二进制文件中！
2. io 线程将二进制文件拷贝到从机，写入中继日志！
3. sql 线程读取中继日志数据，完成数据同步！
可能出现的问题：延迟 {重新开启主从复制}
基本原则：
每个slave只有一个master
每个master可以有多个salve
每个slave只能有一个唯一的服务器ID
实战：
有两台虚拟机：
主：168 一个机器
从：166 从主克隆过来， 或者创建一个！
细节：建议大家的主从两台机器的版本要一致！
5.7.28;
1. 配置主机：168
vim /etc/my.cnf;
server-id=1
log-bin=mysql-bin
binlog-ignore-db=mysql
binlog-ignore-db=infomation_schema
binlog-do-db=mytestdb
binlog_format=STATEMENT
先不要创建mytestdb数据库。主从搭建好了再创建。
2. 配置从机：166
server-id=2
relay-log=mysql-relay
3. 关闭范获取重启mysql 服务！
4. 在主机建立账号并授权！
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY '123456';
flush privileges;
查看master 状态！
mysql> show master status;
+------------------+----------+--------------+-------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+-------------------------+-------------------+
| mysql-bin.000001 | 154 | mytestdb | mysql,infomation_schema | |
+------------------+----------+--------------+-------------------------+-------------------+
1 row in set (0.00 sec)
5. 在从机配置读取哪台服务器的数据！
CHANGE MASTER TO MASTER_HOST='192.168.200.168',
MASTER_USER='slave',MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=154;
启动主从复制功能! 并查看状态！
start slave;
show slave status \G;
重点：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
必须都是yes！
如果出现了NO, 课件有答案！
在克隆的时候，两台机器的uuid 可能一致！
vim /var/lib/mysql/auto.cnf
6. 测试主从复制！
7. 主从复制相关命令：
a. 停止主从复制：
在从机上执行！ stop slave;
b. 重新生成中继日志文件！
在从机！reset slave;
c. 重新生成二进制文件！
在主机执行 reset master;
总结SQL语句优化规则：
1. 看是否有过滤条件 limit 优化 where 后面的字段！
2. 关联条件！
3. 优化的时候尽量尝试各种优化方式！
若有收获，就点个赞吧
0 人点赞
