MySQL高级笔记

一.mysql基础加深

1.sql语句的执行过程

0.程序访问MySQL
1.和连接池进行沟通，获得连接
2.在缓存中查询结果(只有sql语句一摸一样的会在缓存中命中，结果相同的不会命中)
3.如果缓存中有则直接返回结果，如果缓存没有命中则利用SQL接口分析sql语句
4.解析器对sql语句进行分析
5.分析结束后利用优化器优化对sql的执行顺序进行改进(在不改变结果的前提下)
6.执行引擎执行sql语句
7.将结果返回，同时将结果写入缓存

★2.mysql的存储引擎（面试）

两个常用的存储引擎MyISAM和InnoDB
区别：
		MyISAM					InnoDB(默认)
外键		不支持					支持
事务		不支持					支持
行表锁	表锁，不适合高并发			行锁，适合高并发
		但不会出现死锁			但可能会出现死锁
缓存		只缓存索引				缓存索引和真实结果
关注点	节约资源					高并发

★3.多表查询

二.索引

1.说明：
	索引是帮助MySQL高效获取数据的数据结构
	
2.好处与坏处
	好处：索引能提高数据检索的效率，也可以较低数据排序的成本，例如平衡二叉树
	坏处：写操作变慢

1.数据结构

(1)BTree索引(太费内存)
	Ⅰ.结构
		每个节点都包含
		两个数据,
		指向数据的指针,
		和指向下方的指针，
		这个比较类似三叉树，可以通过三分法查找数据
(2)B+Tree索引(MySQL使用的是B+Tree)
	Ⅰ.结构
		每个节点只包含
		三个数据,
		指向下方的指针
		只有叶子节点中才包含指向数据的指针，这样无论怎么查都需要查到叶子节点，会比BTree慢
(3)对比：
	因为B+Tree的非叶子节点中不含有指向数据的指针，所以每一个节点占的空间都比较小
	这样在内存有限的情况下可以有更多的节点进入内存，这样其实查找会更快，所以MySQL使用的是B+Tree

2.索引分类

索引的数据结构：B+Tree
mysql在数据插入时会根据主键索引进行排序，来加快查找效率，但是因为每次都通过链表进行查找效率还是太低，因此mysql想了个办法：将数据进行分页(innodb默认一页16KB)，然后再创建一个页目录，通过查找页目录来加快定位数据的所在位置，这就是我们的索引。
注：innodb顶层页目录常驻内存不需要磁盘IO

(1)按照存储分
	Ⅰ.聚簇索引
	    将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据。
		如果数据存储和索引放到一起，叶子节点存储数据则为聚簇索引。
		一般来说主键索引是聚簇索引，一个表中只有一个聚簇索引
	Ⅱ.非聚簇索引
		将数据与索引分开存储，索引结构的叶子节点存储对应数据的主键值。
		在聚簇索引的基础上建立的索引称为非聚簇索引/辅助索引
		非聚簇索引的叶子节点存储的不是数据而是数据对应的主键值，拿到主键值后再通过聚簇索引查找。
		因此非聚簇索引查找的IO次数总是比聚簇索引多
		注：如果创建的时候没有指定主键，mysql会自动生成rowId然后生成聚簇索引
Ⅲ.非聚簇索引不存地址存主键的原因
		虽然直接存储地址值查找会更快
		但是一旦插入了新的数据而导致排序，数据的地址发生的更改
		存储主键能让我们只更改聚簇索引的叶子节点而不用更改非聚簇索引。
	
(2)按特性分
	Ⅰ.单列索引
		一个索引只包含单个列，一个表可以有多个单列索引
	Ⅱ.唯一索引
		索引列的值必须唯一，允许有空值
	Ⅲ.主键索引
		设定主键后，数据库会自动建立主键索引,一个表中只有一个主键索引,主键索引为聚簇索引
	Ⅳ.复合索引
		一个索引包含多个列
			
(3)语法
	创建：create [unique] index 索引名 on 表名(字段列表);
	删除：drop index 索引名 on 表名;
	删除表上所有索引:call proc_drop_index(表名列表)
	查看：show index from 表名_索引名\G;
	我们的索引名的命名规则:idx_字段1_字段2_...;
	注意：字段列表的顺序无所谓
 		  索引名在一张表内是唯一的，不能取一样的索引名

(4)索引适用和不适用的情况
	Ⅰ.适用情况
		主键自动建立唯一索引
		频繁作为查询条件的字段应该创建索引
		查询中与其它表关联的字段，外键关系建立索引
		比起单键索引，组合索引性价比更高
		查询中排序，统计或分组的字段可以建立索引
	Ⅱ.不适用的情况
		表的记录太少
		经常增删改的字段不创建索引
		where条件中用不到的字段
		过滤性不好的不适合建立索引

3.explain性能分析

(1)作用
	使用explain关键字可以模拟优化器执行SQL查询语句，从而了解MySQL是如何处理SQL语句的
	可以用于分析查询语句或者是表结构的性能瓶颈
(2)信息
	表的读取顺序
	哪些索引可以使用
	数据读取操作的操作类型
	哪些索引被实际使用
	表之间的引用
	每张表有多少行被物理查询
(3)语法
	explain SQL语句;
(4)重要的字段含义
	Ⅰ.id(☆)
		①含义：表示查询中执行select子句或操作表的顺序
		②三种情况
			id相同，执行顺序由上至下
			id不同，如果是子查询，id的序号会递增，执行顺序由大到小
			id相同不同同时存在，先从大到小，id相同的再从上到下
		③关注：每个id都代表一趟独立的查询，一个sql语句查询的趟数越少越好
	
	Ⅱ.select_type
		①含义：表示查询的类型
		②情况
			simple:简单的select查询，查询中不包含子查询或者unique
			primary:主查询，如果查询内部包含子查询，则其被标记为主查询
			derived:衍生查询，位于from子句内的查询被标记为衍生查询，即它的查询结果作为数据源
			subquery:子查询，位于select或where子句中的查询被标记为子查询
			dependent subquery:依赖子查询，位于select或where子句中的查询且它的结果基于外层
			uncacheable subquery:不可用缓存子查询，即查询中用到变量
			union:连接查询
			
	Ⅲ.type(★)
		①含义：表示查询用了哪种类型
		②情况
			all(★):需要遍历全表才能找到匹配的行
			index(☆):where后选择时没有用到索引(导致优化效果低)
			range(☆):查询只在范围内用到索引
			system:表中只有一行记录
			const:通过索引只查询一次就找到了
			eq_ref:使用了唯一索引进行查询
			ref:使用了索引进行查询，但使用的索引不是唯一索引
			index_merge:查询中索引组合使用，例如OR条件连接的两个字段都有索引，MySQL查询时会自动组合
			ref_or_null:表示某个字段要么查到值，要么为NULL
			index_subquery:子查询使用了索引进行查询
			unique_subquery:子查询使用了唯一索引进行查询
		进行优化最少优化到range级别

	Ⅳ.key_len(☆)
		①含义
			where后面的筛选条件命中索引的长度
		②key_len越小越好
	
	Ⅴ.rows
		①含义：表示MySQL真正物理扫描的行数
		②越小越好

	Ⅵ.extra(★)
		①含义：额外字段，包含了order by和group by使用索引的情况
		②情况
			using filesort(★):使用文件排序，表示order by没用到索引
			using temporary(★):表示group by没用到索引
								因为group by包含order by所以这个情况会伴随着using filesort一起出现，所以这个很严重
			using join buffer(☆):表示两个表关联字段没用上索引
			impossible where:表示sql语句写错了

4.索引的建立

如果有多个可选索引MySQL会帮我们选择效果较好的那个
MySQL中优化器能在不改变查询结果的条件下调整查询语句中字段的顺序以匹配上索引

索引失效的情况	
	(1)最佳左前缀法则：
	   联合索引B+Tree节点中的数据是组合字段
	   即优先按照左边的字段进行排序，左边的字段有序了再按照右边的字段排序
	   所以只有满足最佳左前缀法则才能保证查找的数据在B+Tree上是有序的
		   
	(2)函数会导致内部字段的索引失效，所以能不用函数就别用
		
	(3)范围查询字段右边的字段索引失效,所以在建立索引时把可能会涉及范围查询的字段放在右边
		范围查询：<,>,<>等，但是当表中的数据量很大时，即使有范围查询也会用上索引
		范围查找因为确定的是左边字段的范围而右边字段没办法保证有序所以不能用上索引
		
	(4)IS NOT关键字会导致索引失效，所以一般尽量避免
		
	(5)用like关键字进行模糊匹配时，如果字符串的首字母不确定则索引失效,如：'%a%'
		
	(6)字段进行类型转换会导致索引失效，所以in对象中的字段和数据库中字段的类型必须匹配

(1)单表优化

Ⅰ.索引的创建
	type显示all
		表示我们根本没建索引，只要把选择语句后的字段合起来建一个组合索引即可
		create index [索引名] on 表名(字段列表);

Ⅱ.建议
	①在建单列索引时，尽量选择过滤性更好的字段建索引，如身份证，id等
	②在建立组合索引时，尽量将过滤性更好的字段放在左边
	③在建立组合索引时，尽量选择能包含where子句中最多字段的索引
	④如果出现范围查询，则出现范围查询的字段放在最后面

(2)多表优化

Ⅰ.索引的建立
	建立的语法与之前相同
	
Ⅱ.注意
	①连接查询需要给从表的相关列创建索引，给主表建立索引没有意义，还是会全盘扫描
	 因为连接查询是将主表的每一条都跟从表进行匹配，即使建了索引也一样
	 内连接的主表和从表MySQL会自己选择合适的，如果觉得MySQL选择错了可以使用
	 	straight_join
	 这个效果和inner join一样只不过可以自己指定主表和从表
	②连接的关联字段都需要创建索引
	③虚拟表不能建索引，所以虚拟表不要作为从表位置

Ⅲ.建议
	①保证从表已经建立索引
	②外连接时，将小表作为主表，大表作为从表
	③内连接，MySQL会自动帮你选择主表和从表
	④子查询尽量不要放在从表，因为虚拟表不能建立索引
	⑤能直接用多表连接查询的就用连接查询，尽量不要使用子查询，会增加查询的趟数

Ⅳ.子查询优化
	尽量不要使用not in 或者not exists，改用外连接选择关联不上的来替代

(3)排序，分组优化

Ⅰ.order by特点
	①我们想要让order by排序时用上索引，我们order dy后面的字段应该建一个组合索引
	②无过滤，不索引
	 order by想要使用索引，查询语句中必须有筛选条件，实在没有就用limit分页也可以
	③顺序错，必排序
	 MySQL中优化器是在不改变查询结果的条件下调整查询语句中字段的顺序，但是在order by中的字段如果顺序变了结果也会变
	 所以order by后面字段的顺序优化器不会帮我们调整
	 因此我们的组合索引的字段顺序应该和他们在order by后面的顺序一致
	③方向反，必排序
	 排序字段如果都是升序或者都是降序那么不会出现using filesort
	 但是如果有升序也有降序，那即使你建了索引也会失效

Ⅱ.注意
	如果where中有范围查询，那么这时排序，分组中的字段索引就会失效，那就不可避免的using filesort
	但是filesort也有双路排序和单路排序两种算法
	单路排序：比较像归并排序，会让内存帮忙查找，并且一边查找一边排序
	双路排序：就普通的查找完之后在排序
	单路排序比双路排序要快的多，但单路排序更耗内存
	所以在不能避using filesort时尝试用
		sort_buffer_size
	来提高sql语句占的内存使用单路排序

Ⅲ.group by特点
	group by的特点与order by几乎一致，唯一的区别是group by即使没有筛选条件，也可以使用索引

(4)覆盖索引

在筛选条件没办法用到索引的情况下的最后一招

就是在where后面的索引都失效了，我们可以给select后面的字段写出来，创建索引，这就是覆盖索引
这样多少快一点，所以之后select后面别写*，尽量写字段名，这样有合适的索引还能快一点

（5）索引失效

Ⅰ.查询中使用like关键字
	如果匹配字符串的第一个字符为“%”，索引不会被使用
	如果“%”不是在第一个位置，则使用索引
Ⅱ.查询语句中使用多列索引
	只有查询条件中使用了创建索引字段的第一个字段，索引才会被使用(最左匹配)
Ⅲ.查询语句中使用or关键字
	如果or前后两个条件的列都是索引，那么查询中将使用索引
	如果or前后有一个条件的列没有索引，则不使用索引

三.查询截取分析

1.慢查询日志

(1)说明
	慢查询日志是MySQL提供的一种日志记录，它能记录MySQL中响应时间超过设定值的语句
	一般只有运维人员有权限开，一般是关闭状态想要优化时可以让运维人员开启
(2)语法
	Ⅰ.查看
		show variables like '%slow query log%';
	Ⅱ.开启
		set global slow_query_log=1;
	Ⅲ.查看当前阈值
		show varialbes like 'long_query_time%';
	Ⅳ.设置阈值
		set long_query_time=值;#单位是秒
(3)日志分析工具mysqldumpslow，用的时候查一下就行
	在终端中使用mysqldumpslow即可
	一般是：
		mysqldumpslow -s c -t n -a 日志文件;#n表示最慢的前n条

2.show processlist

该命令能展示进程信息
使用情况：数据库出现死锁，用这个命令找出来杀掉
	kill 进程id;

四.视图

MySQL从5.1开始提供视图功能,但在5.7以前视图中from后面不可以跟子查询，但是5.7这个限制没了
(1)视图：
	视图是一种虚拟存在的表，数据来自于原始表，视图中的数据是在使用视图时自动生成的
	视图中只保存sql逻辑，不保存结果。
(2)例子
	#查询姓张的学生的姓名和专业名
	#一般做法
	SELECT stuname,majorname
	FROM stinfo s
	INNER JOIN major m ON s.`majorid`=m.`id`
	WHERE s.`stuname` LIKE '张%';
	
	#加上视图的做法
	CREATE VIEW v1
	AS
	SELECT stuname,majorname
	FROM stinfo s
	INNER JOIN major m ON s.`majorid`=m.`id`;
	
	SELECT *
	FROM v1
	WHERE stuname LIKE '张%';
(3)视图的好处
	Ⅰ.重用sql语句
	Ⅱ.简化复杂的sql操作，不必知道它的实现细节
	Ⅲ.保护数据，提高安全性
(4)视图和表
			创建语法的关键字	实际占用的物理空间	使用
	视图	create view		  只保存了sql逻辑	增删改查,一般只能查
	表		create table	   保存了数据		增删改查

1.视图的创建

(1)语法
	create view 视图名
	as
	查询语句;
创建完之后就可以当作一个表了，只不过是一个虚拟的表，每次调用都相当于重新执行一遍sql语句

2.视图的重建

(1)语法
	Ⅰ.方式一(如果存在就修改，如果不存在就创建)
		create or replace view 视图名
		as
		查询语句;
	Ⅱ.方式二(跟表的修改类似)
		alter view 视图名
		as
		查询语句;

3.视图的删除与查看

(1)删除的语法
	drop view 视图名1,视图名2...;
(2)查看的语法
	desc 视图名;

4.视图的更新(基本不用)

(1)插入
(2)修改
(3)删除
语法与表的更新操作相同
但大部分时候视图都不支持更新，而且这样操作也不安全，基本不用

五.存储过程和函数

1.函数

(1)函数和存储过程的区别
	存储过程：可以有0个或多个返回，适合做批量插入，更新
	函数：有且只有一个返回，适合处理数据后返回一个结果
(2)语法
	Ⅰ.创建
		create function 函数名(参数列表) returns 返回变量类型
		begin
			函数体
		end 结束标记
	Ⅱ.调用
		select 函数名(实参列表)结束标记
	Ⅲ.查看
		show create function 函数名;
	Ⅳ.删除
		drop function 函数名;
(3)注意
	首先允许定义函数SET GLOBAL log_bin_trust_function_creators=TRUE;
	Ⅰ.函数参数列表中的参数有两部分构成
		参数名 参数类型
		
	Ⅱ.函数体中一定要包含return语句
		return 值;
		
	Ⅲ.如果函数体中只有一句话，则可以省略begin end
	
	Ⅳ.也是使用delimiter语句设置结束标记

案例

(1)无参函数
	#返回公司的员工个数
	DELIMITER $
	CREATE FUNCTION myf1() RETURNS INT
	BEGIN
		DECLARE c INT DEFAULT 0;#定义返回变量
		SELECT COUNT(*) INTO c
		FROM employees;
		RETURN c;
	END $
	
	SELECT myf1()$

(2)有参函数
	#根据部门名，返回部门平均工资
	DELIMITER $
	CREATE FUNCTION myf2(deptName VARCHAR(20)) RETURNS DOUBLE
	BEGIN
		DECLARE sal DOUBLE;
		SELECT AVG(salary) INTO sal
		FROM employees AS e
		INNER JOIN departments AS d ON e.department_id=d.department_id
		WHERE d.department_name=deptName;
		RETURN sal;
	END $
	
	SELECT myf2('IT')$

六.流程控制结构

分类
	顺序结构：程序从上往下依次执行
	分支结构：程序从两条或多条路径中选择一条去执行
	循环结构：程序在满足一定条件的基础上，重复执行一段代码

1.分支结构

(1)if函数
	Ⅰ.功能：
		能实现简单的双分支
	Ⅱ.语法：
		if(expr1,expr2,expr3)
		相当于三元运算符,如果表达式1成立，则返回表达式2的值，否则返回表达式3的值
		例子：
		SELECT
			IF(commission_pct IS NULL,'没奖金','有奖金')
		FROM
			employees;
	Ⅲ.应用：可以应用在任何地方

(2)if结构
	Ⅰ.功能：实现多重分支
	Ⅱ.语法：
		if 条件1 then 语句1;
		elseif 条件2 then 语句2;
		...
		[else 语句n;]
		end if;
	Ⅲ.应用：只能在begin end中

(3)case结构，这个之前在DQL的流程控制中都讲过了，我就直接拿过来了
	Ⅰ.情况1：类似于java中switch case的效果
		case 要判断的变量|字段|表达式
		when 常量1 then 要显示的值1或语句1;
		when 常量2 then 要显示的值2或语句2;
		...
		[else 要显示的值n或语句n;]
		end
		如果then后面是值则不需要加';'，如果是语句就需要加';'
		例子：
			SELECT salary AS 原始工资,department_id,
			CASE department_id
			WHEN 30 THEN salary*1.1
			WHEN 40 THEN salary*1.2
			WHEN 50 THEN salary*1.3
			ELSE salary
			END AS 	新工资
			FROM employees;
	Ⅱ.情况2：类似于java中的多重if
		case
		when 条件1 then 要显示的值1或语句1;
		when 条件2 then 要显示的值2或语句2;
		...
		[else 要显示的值n或语句n;]
		end
		同样，如果then后面是值则不需要加';'，如果是语句就需要加';'
		例子：
			SELECT salary,
			CASE
			WHEN salary>20000 THEN 'A'
			WHEN salary>15000 THEN 'B'
			WHEN salary>10000 THEN 'C'
			ELSE 'D'
			END AS 工资级别
			FROM employees;
	这两种方法语法上的区别就是case后面有没有表达式
	Ⅲ.应用：
	   可以作为表达式，嵌套在其他语句中使用，可以放在任何地方，begin end内外都可以
	   也可以作为独立的语句使用，这样就只能放在begin end中
	   如果then后面跟的是值，那就是作为表达式，如果跟的是语句那就是作为独立的语句使用
(3)特点
	Ⅰ.如果when中的值满足或条件成立，则执行相应的语句，并结束case
	   如果都不满足，则执行else中的语句或值
	   
	Ⅱ.else可以省略，如果else省略了，并且所有when条件都不满足，则返回NULL；

2.循环结构

应用：只能在begin end中

(1)分类
	loop：一般用于实现简单的死循环
	while：先判断后执行
	repeat：先执行后判断，至少执行一次
(2)循环控制
	iterate:类似于continue，结束本次循环，继续下一次循环
	leave:类似于break，结束当前所在循环
(3)语法
	Ⅰ.while
		[标签:] while 循环条件 do
			循环体;
		end while [标签];
	
	Ⅱ.loop
		[标签:] loop do
			循环体;
		end loop [标签];
		因为loop没有循环条件，所以如果不用循环控制语句就会变成死循环
	Ⅲ.repeat
		[标签:] repeat
			循环体;
		until 结束循环的条件
		end repeat [标签];
(4)例子
	#批量插入，根据次数插入到admin表中，如果次数>20则停止
	DELIMITER $;
	CREATE PROCEDURE test_while(IN insertCount INT)
	BEGIN
		DECLARE i INT DEFAULT 1;
		a:WHILE i<insertCount DO
			INSERT INTO admin(username,`password`)
			VALUES(CONCAT('xiaohua',i),'1234');
			IF i>=20 THEN LEAVE a;
			END IF;
			SET i=i+1;
		END WHILE a;
	END $
(5)特点
		Ⅰ.标签可以不写，但是加上标签才能使用循环控制

七.主从复制

MySQL的主从复制分为三个步骤
	（1）master主机将改变记录到二进制日志(binary log)。
	（2）slave从机将主机的二进制日志文件拷贝到它自己的中继日志
	（3）从机重做中继日志中的事件，将改变应用到自己的数据库。MySQL复制是异步且串行化的。
	因此我们MySQL进行主从复制之后，主表和从表是会有延迟的，不要一插完数据就立刻查询
binary log日志的格式有几种？他们的特点是什么？
	（1）STATEMENT(默认)：记录主机的所有写操作，但如果sql语句中有函数(如查询当前系统事件)会导致主表从表不一致的情况
	（2）ROW：记录的是主表每一行数据的改变，但如果出现整表的改变，效率会很低
	（3）MIXED：判断写操作有没有函数，如果没有切换成STATEMENT，如果有则切换成ROW，但如果sql语句中出现系统变量还是会导致主从复制不一致

八.Mycat

1.说明
	Mycat是数据库中间件
2.作用
	(1)读写分离:因为读的请求次数比写的请求次数要多的多
	(2)数据分片
		垂直拆分
		水平拆分
		垂直+水平拆分
	(3)多数据源整合
3.原理
	拦截：把客户端发给服务器的请求进行拦截，根据条件把请求发送到分表中，分表再将结果返回给Mycat，Mycat再发送给客户端，就实现了数据的拆分
	相当于一个中间人的作用，因为MySQL搭建了主从复制之后，主机和从机都能进行读和写的操作，那么可能会导致java程序进行数据库访问的时候需要配置多个数据库信息，为了解耦我们可以让java只配置mycat，然后让mycat去连接数据库，进行分表，类似于Nginx的作用
4.schema.xml中的重要配置项balance
	（1）balance="0"，不开启读写分离机制，所有读操作和写操作都发送到当前可用的writeHost上
	（2）balance="1"，双主双从模式，两组主从复制
	（3）balance="2"，所有读操作都随机分配到writeHost和readHost上
	（4）balance="3"，所有读请求随机分配到readHost执行，writeHost上不负担读压力
	一般配成1或3