MySQL从基础到高级六

MySQL从基础到高级六

面试题讲解：

一、建表sql、初始化数据、创建索引

# 建表 sql DDL
CREATE TABLE `customer` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `username` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gander` int DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_usr_pas_pho` (`username`,`password`,`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

# 初始化数据
insert  into `customer`(`id`,`username`,`password`,`phone`,`gander`) values 
(1,'李白','libai','123456',1),
(2,'杜甫','dufu','123456',1),
(3,'李清照','liqingzhao','123456',0),
(4,'苏东波','sudongbo','123456',1),
(5,'武则天','wuzetian','123456',0),
(6,'方冰冰','fanye','123456',0),
(7,'刘德华','liudehua','123456',1);

# 给该表的 username, password, phone 字段创建一个联合索引
CREATE INDEX idx_usr_pas_pho ON customer(username, PASSWORD, phone)

# 查看索引情况
SHOW INDEX FROM customer

# 使用全值索引查询
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD = 'libai' AND phone = '123456'

# 使用乱序全值索引查询(在索引乱序的情况下，MySQL自带的优化器会按照联合索引的顺序自动排序索引列——自我优化机制，最好是按照建索引的顺序，自我优化也是消耗资源的)
EXPLAIN SELECT * FROM customer WHERE phone = '123456' AND PASSWORD = 'libai' AND username = '李白'

# 中间条件使用范围查询(范围之后全失效)
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD > 'libai' AND phone = '123456'

# 中间条件使用范围查询(自我优化机制会把索引重新排序)
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND phone > '123456' AND PASSWORD = 'libai'

# 索引的功能：排序和查找
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND phone = '123456' ORDER BY PASSWORD

# 索引的功能：排序和查找(查找和排序均用到索引)
EXPLAIN SELECT * FROM customer WHERE username = '李白' ORDER BY PASSWORD

# 索引的功能：排序和查找(中间兄弟断了，排序用不了索引)
EXPLAIN SELECT * FROM customer WHERE username = '李白' ORDER BY phone

# 索引的功能：排序和查找(查找和排序均用到索引)
EXPLAIN SELECT * FROM customer WHERE username = '李白' ORDER BY PASSWORD, phone

# 索引的功能：排序和查找(排序没有用到索引, order by 无法自我优化——排序索引列)
EXPLAIN SELECT * FROM customer WHERE username = '李白' ORDER BY phone, PASSWORD

# 索引的功能：排序和查找(排序没有用到索引, order by 自我优化——字段列为常量的时候会被隐藏)
EXPLAIN SELECT * FROM customer WHERE username = '李白' AND PASSWORD = 'libai' ORDER BY phone, PASSWORD

# 查找和分组
EXPLAIN SELECT PASSWORD, phone FROM customer WHERE username = '李白' AND phone = '123456' GROUP BY PASSWORD, phone

# 查找和分组(分组之前必排序)
EXPLAIN SELECT PASSWORD, phone FROM customer WHERE username = '李白' GROUP BY phone, PASSWORD

结论：
① 定值、范围还是排序，一般 order by 是给个范围
② group by 基本上都需要进行排序，会产生临时表

建议：
① 对于单键索引，尽量选择针对当前 query 过滤性更好的索引
② 在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
③ 在选择组合索引的时候，尽量选择可以能够包含当前 query 中的 where 子句中更多的索引字段
④ 尽可能通过分析统计信息和调整 query 的写法来达到选择合适索引的目的

查询截取分析

sql 故障排查或优化流程

① 把 SQL 运行 1~3 天，期间观察那些 SQL 执行比较慢
② 开启慢查询日志，设置阈值，比如执行时间超过 10 秒的就是慢 SQL，并将它们抽取出来
③ explain + 慢 SQL 分析
④ show profile
⑤ 运维经理 or DBA，进行 SQL 数据库服务器的参数调优

总结：

a：慢查询的开启并捕获
b：explain + 慢 SQL 分析
c：show profile 查询 SQL 在 MySQL 服务器里面的执行细节和生命周期情况
d：SQL 数据库服务器的参数调优

1、查询优化

① in 和 exists
	优化原则：永远是小数据集驱动大数据集(注意：A、B 表的 id 均为索引字段)
		select * from A where id in(select id from B)
		等价于：
		for select id from B
		for select * from A where A.id = B.id
		当 B 表的数据集小于 A 表时，则 in 优化 exists
		================================================
		select * from A where exists(select 1 from B where B.id = A.id)
		等价于：
		for select * from A
		for select * from B where A.id = B.id
		当 A 表的数据集小于 B 表时，则 exists 优化 in
		================================================
		exists：
			select .... from table where exists(subquery)
			该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE/FALSE)来决定主查询的数据结果是否可以保留
		提示：
			1、exists(subquery)只返回 TRUE 或 FALSE，因此子查询中的 select * 也是可以是 select 1 或 select 'X'，官方说法是执行时会忽视 select 清单，因此没有区别
			2、exists 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条比较，如果担心效率问题，可进行实际的验证以确定是否有效率问题
			3、exists 子查询往往也可以用条件表达式，其他子查询或者 json 来替代，何种最优需要具体问题具体分析
		
② order by 优化
	a：order by 子句，尽量使用 index 方式排序，避免使用 filesort 方式排序
		一、建表SQL
			CREATE TABLE `customer` (
			  `id` bigint NOT NULL AUTO_INCREMENT,
			  `username` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
			  `password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
			  `phone` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
			  `gander` int DEFAULT '0',
			  PRIMARY KEY (`id`),
			  KEY `idx_usr_pas_pho` (`username`,`password`,`phone`)
			) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
		二、case

EXPLAIN SELECT * FROM customer WHERE username LIKE '李白%' ORDER BY username

EXPLAIN SELECT * FROM customer WHERE username LIKE '李白%' ORDER BY PASSWORD

EXPLAIN SELECT * FROM customer WHERE username LIKE '李白%' ORDER BY phone

EXPLAIN SELECT * FROM customer WHERE username LIKE '李白%' ORDER BY username, PASSWORD

EXPLAIN SELECT * FROM customer WHERE username LIKE '李白%' ORDER BY PASSWORD, username

		三、MySQL 支持两种方式排序，filesort 和 index，index 效率高，它指 MySQL 扫描索引本身完成排序，filesort 方式效率较低。
		四、order by 满足两种情况，会使用 index 方式排序：order by 语句使用索引最左前列、使用 where 子句和 order by 子句条件列组合满足索引最左前列
	b：尽可能在索引列上完成排序操作，遵照索引键的最佳最前缀
	c：如果不在索引列上，filesort 有两种算法
		一、双路排序：
			1、MySQL 4.1 之前时使用山路排序，字面意思就是要经过两次扫描磁盘，才能得到结果数据。读取行指针和 order by 列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取数据
			2、从磁盘读取排序字段，在 buffer 进行排序，再从取其他字段。
		二、单一批数据，要对磁盘进行两次扫描，总所周知，I/O 是很耗时间的，所以在 MySQL4.1 之后，出现了第二种算法改进，就是单路排序
		三、单路排序：从磁盘中读取查询需要的所有列，按照 order by 列在 buffer 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 I/O 变成了顺序 I/O，但它会消耗更多的空间，因为它把每一行都保存在内存中了。7
		四、结论及引申出的问题：
			1、由于单路排序是双路排序的改进，总体而言优于双路排序
			2、但是单路也有缺陷：在 sort_buffer 中，方法 B 要比方法 A 多占用很多空间，因为方法 B 把所有字段都取出了，所以有可能取出的数据总大小超出了 sort_buffer 的容量，导致每次只能取出 sort_buffer 容量大小的数据，进行排序(创建 temp 文件，多路合并)，排完序再取 sort_buffer 容量大小，再排.....从而多次 I/O。		
	d：MySQL 就要启动双路排序和单路排序
	e：优化策略
		一、增大 sort_buffer_size 参数设置
		二、增大 max_length_for_sort_data 参数的设置
		三、why(提高 order by 的速度)
			1、order by 时 select * 是一个大忌，只需 query 需要的字段列，这个非常重要，这里的影响是：
				① 当 query 的字段的大小总和小于 max_length_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序
				② 两种算法的数据都有可能超过 sort_buffer 容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是单路排序算法的风险更大一些，所以要提高 sort_buffer_size。
			2、尝试提高 sort_buffer_size，不管使用哪种算法，提高这个参数都会提高效率，当然这个需要根据系统能力去提高，因为这个参数是针对每一个进程的。
			3、尝试提高 max_length_for_sort_data，提高这个参数会增加使用改进算法的概率，但是如果设置过高，数据总量超出 sort_buffer_size 的概率就增大，明显征兆就是高的磁盘 I/O 活动和低的处理器使用率。
	f：小总结：
		一、MySQL 两种排序方式：文件排序或扫描有序索引排序
		二、MySQL 能为排序和查询使用相同的索引字段
		三、索引排序和非索引排序(KEY a_b_c(a, b, c))
			① order by 使用索引最左前缀
			——order by a
			——order by a, b
			——order by a, b, c
			——order by a desc, b desc, c desc
			② 如果 where 是索引最左前缀定义为常量，则 order by 能使用索引
			——where a = const order by b, c
			——where a = const and b = const order by c
			——where a = const and b > const order by b, c
			③ 不能使用索引进行排序
			——order by a asc, b desc, c desc	# 排序不一致
			——where g = const order by b, c # 带头大哥没了
			——where a = const order by c 	# 中间兄弟断了
			——where a = const order by b, d		# d 不是索引字段
			——where a in(.....) order by b, c		# 对于排序来说，多个相等条件也是范围查询
③ group by
	a：group by 实质是先排序再进行分组，遵照最佳左前缀法则
	b：当无法使用索引列，增大 sort_buffer_size 和 max_length_for_sort_data 的参数设置
	c：where 高于 having，能写在 where 限定的条件就不要去 having 限定了。

2、慢查询日志

① 是什么？
	a：MySQL 的慢查询日志是 MySQL 提供的一种日志记录，它是用来记录在 MySQL 中响应时间超过阈值的 sql 语句，具体指的是运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中
	b：具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中，long_query_time 的默认值为10，意思是执行时间超过10秒的 SQL。
	c：可以通过查看慢查询日志得知那些 sql 语句执行时间超过了最大忍耐时间值，比如一条 SQL 超过了5秒，我们就算它为慢 sql，希望收集超过5秒的 SQL，结合之前 explain 进行全面分析。
② 怎么玩
	a：说明
		一、默认情况下，MySQL 数据库关闭慢查询日志记录功能，需要我们手动去设置这个参数
		二、当前，如果不是调优需要，一般不建议启动该参数，因为开启慢查询日志记录功能或多或少会带来一定性能消耗，慢查询日志支持将日志记录写入文件
	b：查看是否开启及如何开启
		一、查看慢查询开关状态(默认关闭)：
		SHOW VARIABLES LIKE '%slow_query_log%'
		二、开启/关闭(只对当前会话的数据库有效，数据库重启参数无效)：SET GLOBAL slow_query_log=1/0
		三、永久开启：
			① 找到 MySQL 配置文件 my.cnf
			② 在配置文件中加入
				——SET GLOBAL slow_query_log=1
				——slow_query_log_file=/var/lib/mysql/XXX-slow.log
	c：开启慢查询之后，什么样的 sql 会被记录到慢查询日志文件中
	d：case
		# 查看慢查询日志的阈值：
		SHOW VARIABLES LIKE '%long_query_time%'
		# 设置慢查询日志的阈值：
		SET GLOBAL long_query_time=20
		# 为什么设置之后看不出变化？
			① 需要打开一个新窗口才能开到变化
			② SHOW GLOBAL VARIABLES LIKE 'long_query_time%'
		# 记录慢查询后并分析：
		select sleep(20)
		# 查询当前系统中有多少条慢查询记录：
		show global status like '%slow_queries%'			
	e：配置版
		一、配置四个参数
			slow_query_log=1
			slow_query_log_file=路径.log
			long_query_time=5
			log_output=FILE
③ 日志分析工具
	a：mysqldumpslow
	b：在生产环境中，如果要手工分析日志，查询、分析 sql，显然这是个体力活，MySQL 提供了日志分析工具 mysqldumpslow
	c：查看 mysqldumpslow 的帮助信息
		s：是表示按照何种方式排序
		c：访问次数
		i：锁定时间
		r：返回记录
		t：查询时间
		al：平均锁定时间
		ar：平均返回记录数
		at：平均查询时间
		t：即返回前面多少条的数据
		g：后边搭配一个正则匹配模式，大小写不敏感的
	d：case
		# 得到返回记录的最多的10个sql
		mysqldumpslow -s r -t 10 /慢查询日志存储路径.log
		# 得到访问次数最多的10个sql
		mysqldumpslow -s c -t 10 /慢查询日志存储路径.log
		# 得到按照时间排序的前10条里面含有左连接的查询语句
		mysqldumpslow -s t -t 10 -g "left join" /慢查询日志存储路径.log
		# 另外建议在使用这些命令时结合 | 和 more 使用，否则有可能出现爆屏情况
		mysqldumpslow -s r -t 10 /慢查询日志存储路径.log | more

3、批量数据脚本(往数据表插入1000w数据)

① 创建数据表
	CREATE TABLE `customer` (
	  `id` bigint NOT NULL AUTO_INCREMENT,
	  `username` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	  `password` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	  `phone` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	  `gander` int DEFAULT '0',
	  PRIMARY KEY (`id`),
	  KEY `idx_usr_pas_pho` (`username`,`password`,`phone`)
	) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
② 设置参数 
	# 由于开启了慢查询日志，创建函数可能会报错(This function has none of DETERMINISTIC.....)，因为我们开启了log-bin，所以必须要为function指定一个参数
	SHOW VARIABLES LIKE 'log_bin_trust_function_creators%'
	# 开启参数(当前会话有效，永久需要在my.cnf配置文件中设置该参数)
	SET GLOBAL log_bin_trust_function_creators=1
③ 创建一些随机生产姓名、电话和字符串的函数，保证插入数据表的1000w数据每条都不一样。
	# 根据指定长度随机生产人的姓名函数
	DELIMITER $$
	USE `learn`$$
	DROP FUNCTION IF EXISTS `rand_username`$$
	CREATE DEFINER=`root`@`%` FUNCTION `rand_username`(len INT) RETURNS VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
	BEGIN
		DECLARE base_firstname VARCHAR(400) DEFAULT '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜戚谢
			邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍史唐费廉岑薛雷贺倪汤滕殷罗毕郝邬安常乐于时
			傅皮卞齐康伍余元卜顾孟平黄和穆萧尹姚邵湛汪祁毛禹狄米贝明臧计伏成戴谈宋茅庞熊纪舒屈项祝董粱杜阮蓝闵
			席季麻强贾路娄危江童颜郭梅盛林刁钟徐邱骆高夏蔡田樊胡凌霍虞万支柯昝管卢莫经房裘缪干解应宗丁宣贲邓';
		DECLARE base_lastname VARCHAR(800) DEFAULT 
			'郁单杭洪包诸左石崔吉钮龚程嵇邢滑裴陆荣翁荀羊於惠甄麴家封芮羿储靳汲邴糜松井段富巫
					乌焦巴弓牧隗山谷车侯宓蓬全郗班仰秋仲伊宫宁仇栾暴甘钭厉戎祖武符刘景詹束龙叶幸司韶
					郜黎蓟薄印宿白怀蒲邰从鄂索咸籍赖卓蔺屠蒙池乔阴欎胥能苍双闻莘党翟谭贡劳逄姬申扶堵
					冉宰郦雍舄璩桑桂濮牛寿通边扈燕冀郏浦尚农温别庄晏柴瞿阎充慕连茹习宦艾鱼容向古易慎
					戈廖庾终暨居衡步都耿满弘匡国文寇广禄阙东殴殳沃利蔚越夔隆师巩厍聂晁勾敖融冷訾辛阚
					那简饶空曾毋沙乜养鞠须丰巢关蒯相查後荆红游竺权逯盖益桓公万俟司马上官欧阳夏侯诸葛
					闻人东方赫连皇甫尉迟公羊澹台公冶宗政濮阳淳于单于太叔申屠公孙仲孙轩辕令狐钟离宇文
					长孙慕容鲜于闾丘司徒司空亓官司寇仉督子车颛孙端木巫马公西漆雕乐正壤驷公良拓跋夹谷
					宰父谷梁晋楚闫法汝鄢涂钦段干百里东郭南门呼延归海羊舌微生岳帅缑亢况后有琴梁丘左丘
					东门西门商牟佘佴伯赏南宫墨哈谯笪年爱阳佟第五言福百家姓终';
		DECLARE result_str VARCHAR(255) DEFAULT '';
		# 设置姓氏(长度为4有两位字符，长度为3有一个字符组成)
		CASE 
		WHEN len = 3 THEN 
			SET result_str = CONCAT(result_str, SUBSTR(base_firstname, FLOOR(1 + RAND() * 180), 1));
		ELSE 
			SET result_str = CONCAT(result_str, SUBSTR(base_firstname, FLOOR(1 + RAND() * 170), 1));
			SET result_str = CONCAT(result_str, SUBSTR(base_firstname, FLOOR(1 + RAND() * 170), 1));
		END CASE;
		# 设置名字两位
		SET result_str = CONCAT(result_str, SUBSTRING(base_lastname, FLOOR(1 + RAND() * 388), 1));
		SET result_str = CONCAT(result_str, SUBSTRING(base_lastname, FLOOR(1 + RAND() * 380), 1));
		RETURN result_str;
		END$$
	DELIMITER ;
	
	# 根据指定长度生产随机字符串的函数
	DELIMITER $$
	USE `learn`$$
	DROP FUNCTION IF EXISTS `rand_string`$$
	CREATE DEFINER=`root`@`%` FUNCTION `rand_string`(len INT) RETURNS VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
	BEGIN
		DECLARE base_str VARCHAR(100) DEFAULT 'abcdefghjklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
		DECLARE result_str VARCHAR(255) DEFAULT '';
		DECLARE i INT DEFAULT 0;
		WHILE i < len DO
			SET result_str = CONCAT(result_str, SUBSTR(base_str, FLOOR(1 + RAND() * 62), 1));
			SET i = i + 1;
		END WHILE;
		RETURN result_str;
	END$$
	DELIMITER ;
	
	# 创建一个能随机生产中国大陆手机号码的函数
	DELIMITER $$
	USE `learn`$$
	DROP FUNCTION IF EXISTS `rand_phone`$$
	CREATE DEFINER=`root`@`%` FUNCTION `rand_phone`() RETURNS VARCHAR(255) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
	BEGIN
		DECLARE base_phone VARCHAR(100) DEFAULT '0123456789';
		DECLARE last_phone VARCHAR(9) DEFAULT '';
		DECLARE i INT DEFAULT 0;
		DECLARE first_phone VARCHAR(2) DEFAULT '';
		DECLARE rand_num INT DEFAULT FLOOR(1 + RAND() * 5);
		# 大陆电话号码前缀：13/15/17/18/19
		CASE 
		WHEN rand_num = 1 THEN SET first_phone = '13';
		WHEN rand_num = 2 THEN SET first_phone = '15';
		WHEN rand_num = 3 THEN SET first_phone = '17';
		WHEN rand_num = 4 THEN SET first_phone = '18';
		ELSE SET first_phone = '19';
		END CASE;
		# 大陆电话号码后9位
		WHILE i < 9 DO
			SET last_phone = CONCAT(last_phone, SUBSTR(base_phone, FLOOR(1 + RAND() * 10), 1));
			SET i = i + 1;
		END WHILE;
		RETURN CONCAT(first_phone, last_phone);
		END$$
	DELIMITER ;

④ 创建存储过程
	# 往数据表中插入数据的存储过程
	DELIMITER $$
	CREATE
		/*[DEFINER = { user | CURRENT_USER }]*/
		PROCEDURE `learn`.`batch_insert_customer`(IN START INT(10), IN max_num INT(20))
		/*LANGUAGE SQL
		| [NOT] DETERMINISTIC
		| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
		| SQL SECURITY { DEFINER | INVOKER }
		| COMMENT 'string'*/
		BEGIN
		DECLARE i INT DEFAULT 0;
		# 先把 MySQL 自动提交持久化功能关闭，把 autocommit = 0;
		SET autocommit = 0;
		# 重复执行 insert 操作，注意这里只是执行了SQL语句，还没有把逻辑持久化到数据库
		REPEAT
			SET i = i + 1;
			INSERT INTO customer(username, PASSWORD, phone, gander) VALUES(rand_username(3 + FLOOR(RAND() * 2)), rand_string(8), rand_phone(), FLOOR(RAND() * 2));
			UNTIL i = max_num
		END REPEAT;
		# 重复执行结束，一起提交持久化
		COMMIT;
		END$$
	DELIMITER ;

⑤ 调用存储过程
	# 使用存储过程对数据表进行批量插入1000w数据
	call batch_insert_customer(10000000);

4、show profile

① 是什么：是 MySQL 提供可以用来分析当前会话中语句执行的资源消耗情况下，可以用于 SQL 的调优测试
② 官网：http://dev.mysql.com/doc/refman/8.0/en/show-profile.html
③ 默认情况下,参数属于关闭状态，并保存最近15次的运行结果
④ 分析步骤
	# 查看 profile 的开关状态，当前 MySQL 版本是否支持
	show variables like 'profiling%';
	# 设置 profile 开关(on/off)，默认关闭(off)
	set profiling=on
	# 运行 SQL
	select * from customer group by id % 10 limit 100000
	select * from customer group by id % 20 order by 5
	# 查看结果
	show profile;
	# 诊断 sql, SHOW PROFILE TYPE FOR QUERY sql_ID
	SHOW PROFILE CPU, BLOCK IO FOR QUERY sql_ID
	# TYPE 参数：
	ALL —— 显示所有信息
	BLOCK IO —— 显示块I/O操作的计数
	CONTEXT SWITCHES —— 显示上下文切换相关的开销
	CPU —— 显示 CUP 相关开销信息
	IPC —— 显示发送和接收相关的开销信息
	MEMORY —— 显示内存相关的开销信息
	PAGE FAULTS —— 显示页面错误相关的开销信息
	SOURCE —— 显示和Source_function、Source_file、Source_line 相关的开销信息
	SWAPS —— 显示交换次数相关开销的信息
⑤ 日常开发需要注意的结论(坏结果)
	a：converting HEAP to MYISAM 查询结果太大，内存不够用了往磁盘上搬了
	b：create tmp table 创建临时表：1、拷贝数据到临时表，2、用完再删除
	c：copying to tmp table on disk 把内存中的临时表复制到磁盘，危险！！！
	d：locked

5、全局查询日志

① 配置启用(在 mysql 的 my.cnf 配置文件中，设置如下)
	# 开启
	general_log=1
	# 记录日志文件的路径
	general_log_file=/path/logfile
	# 日志输出格式
	log_output=FILE
② 编码启用(客户端使用命令行的方式开启)
	# 开启
	set global general_log=1;
	# 日志输出格式
	set global log_output='TABLE'
③ 永远不要在生产环境开启这个功能

MySQL 锁机制

1、锁是计算机协调多个进程或线程并发访问某一资源的机制
2、在数据库中，除了传统的计算资源(CPU、RAM、I/O等)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、
有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言尤为重要，也更加复杂。
3、概述
	① 定义
	② 生活购物
	③ 锁的分类
		A：从数据的操作类型来分(读锁/写锁)
			a：读锁(共享锁) —— 针对同一份数据，多个操作可以同时进行而不会互相影响
			b：写锁(排它锁) —— 当前写操作未完成之前，他会阻断其他写锁和读锁
		B：从对数据操作颗粒度来分(行锁/表锁)
4、三锁
	① 表锁
		A：特点
		B：案例分析
			a：建表 SQL
				# 建表语句
				CREATE TABLE mylock(
					id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
					`name` VARCHAR(255)
				) ENGINE MYISAM;
				
				# 向 mylock 表中插入数据
				INSERT INTO mylock(NAME) VALUE('a'), ('b'), ('c'), ('d'), ('e');
			b：查看锁状态
				# 查看数据库中数据表的锁状态
				SHOW OPEN TABLES;
			c：加读锁
				# 手动给 mylock 数据表添加锁
				LOCK TABLE mylock READ

	session1	session2
查询 mylock 表数据	SELECT * FROM mylock(成功)	SELECT * FROM mylock(成功)
查询 customer 表数据	SELECT * FROM customer(失败)	SELECT * FROM customer(成功)
修改 mylock 表数据	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(失败)	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(堵塞，直到锁被释放)
删除 mylock 表数据	DELETE FROM mylcok WHERE NAME = ‘a’(失败)	DELETE FROM mylcok WHERE NAME = ‘a’(失败)
插入 mylock 表数据	INSERT INTO mylock(NAME) VALUES(‘e’)(失败)	INSERT INTO mylock(NAME) VALUES(‘e’)(堵塞，直到锁被释放)

			d：加写锁
				# 手动给 mylock 数据表添加锁
				LOCK TABLE mylock write

	session1	session2
查询 mylock 表数据	SELECT * FROM mylock(成功)	SELECT * FROM mylock(失败)
查询 customer 表数据	SELECT * FROM customer(失败)	SELECT * FROM customer(成功)
修改 mylock 表数据	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(成功)	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(堵塞，直到锁被释放)
删除 mylock 表数据	DELETE FROM mylcok WHERE NAME = ‘a’(失败)	DELETE FROM mylcok WHERE NAME = ‘a’(失败)
插入 mylock 表数据	INSERT INTO mylock(NAME) VALUES(‘e’)(成功)	INSERT INTO mylock(NAME) VALUES(‘e’)(堵塞，直到锁被释放)

			e：解除表锁
				# 释放表锁
				UNLOCK TABLES
		C：案例结论
			a：MyISAM 在执行 select 语句前会自动给此次查询涉及到的表加上读锁
			b：在执行 insert、update、delete 语句前会自动给涉及到此次操作的表加上写锁
			c：MySQL 的表锁有两种模式
				① 表共享读锁 —— table read lock
				② 表独占写锁 —— table write lock
			d：结论
				① 对 MyISAM 表的读操作(加读锁)，不会阻塞其他进程对同一表的读请求，但是会阻塞对同一表的写请求，只有当读锁释放后写操作才能继续执行
				② 对 MyISAM 表的写操作(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，其他进程的读和写操作才能继续进行。
			e：重点：简而言之，就是读锁只会阻塞写，但不会阻塞读，而写锁会把读和写都阻塞
		D：表锁分析
			a：可以通过检查 table_lock_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定
			b：SQL：show status like 'table%'
			c：这里有两个状态变量记录 MySQL 内部表级锁定的情况，两个变量说明如下：
				① table_locks_immediate：产生表级锁定的次数，表示可以立即获得锁的查询次数，每次立即获取锁值加1
				② table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，没等待一次锁值加1)，此值高则说明存在着较严重的表锁争用情况
				③ 此外，MyISAM 的读写锁调度是写优先，这也是 MyISAM 不合适做写为主的表搜索引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
	② 行锁
		A：特点
			a：偏向 innoDB 存储引擎，开销大加锁慢，会出现死锁，锁粒度最小发生锁冲突的概率最低，并发度也最高
			b：InnoDB 与 MyISAM 的最大不同有两点：一是支持事务(transaction)；二是采用了行级锁
		B：由于行锁支持事务，复习老知识
			a：事务的ACID属性
				① 原子性(Atomicity)：事务是一个原子操作单位，其对数据的修改，要么全部执行，要么全部不执行
				② 一致性(Consistent)：在事务开始和完成时，数据都必须保持一致状态，这意味着所有数据规则都必须应用于事务的修改，
									以保持数据的完整性，事务结束时，所有内部数据结构(如B树索引和双向链表)也都必须是正确的
				③ 隔离性(Isolation)：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
				④ 持久性(Durable)：事务完成之后，它对于数据的修改是永久的，即使出现系统故障也能够保持。
			b：并发事务处理带来的问题
				① 更新丢失(lost update)：多个事务同时更新同一行数据，最后事务的更新会覆盖前面事务的更新
				② 脏读(dirty reads)：事务A读取到事务B已经修改但未提交的数据
				③ 不可重复读(non-repeatable reads)：事务A两次读取同一条记录的数据，但两次数据不一样
				④ 幻读(phantom reads)：事务A读取到事务B新增的数据，破坏了事务的隔离性
			c：事务隔离级别
				① 读未提交
				② 读已提交
				③ 可重复读
				④ 串行化

		C：案例结论
			a：建表 SQL
				# 建表
				CREATE TABLE innodb_lock(
					id INT(11),
					b VARCHAR(16)
				)ENGINE=INNODB

				# 初始化数据
				INSERT INTO innodb_lock(id, b) VALUES(1, 'a'),(2, 'b'),(3, 'c'),(4, 'd'),(5, 'e'),(6, 'f');

				# 创建索引
				CREATE INDEX idx_lock_id ON innodb_lock(id)
				CREATE INDEX idx_lock_b ON innodb_lock(b)
				
				# 关闭自动提交功能
				SET autocommit=0
			b：行锁定级别演示

	session1	session2
查询 mylock 表数据	SELECT * FROM innodb_lock WHERE id = 1(成功)	SELECT * FROM innodb_lock WHERE id = 1(失败)
修改 mylock 表数据	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(成功)	UPDATE mylock SET NAME = ‘0’ WHERE NAME = ‘a’(堵塞，直到锁被释放)
删除 mylock 表数据	DELETE FROM innodb_lock WHERE id = 2(成功)	DELETE FROM innodb_lock WHERE id = 2(堵塞，直到锁被释放)

			c：无索引行锁升级为表锁
				# session1 修改 innodb_lock 表中 b = 300 的数据，由于 b 字段是字符类型，使用数值查会造成索引失效
				UPDATE `innodb_lock` SET b = '500' WHERE b = 300
				# session2 修改 innodb_lock 表中 b != '300' 的数据
				UPDATE `innodb_lock` SET b = '100' WHERE b != '300'		## 产生阻塞，直到 session1 锁释放
			d：间隙锁危害
				① 当我们使用范围条件检索数据，并请求共享或排它锁时，innoDB 会给符合条件的已有数据记录索引项加锁；对于条件范围内，但又不存在的记录叫做"间隙GAP"
				② 因为间隙锁会锁住条件范围内的所有索引，即使这个索引记录并不存在，会造成在锁住时间内无法对这个条件范围的索引插入数据。
			e：面试题：常考如何锁定一行
				# 开启行锁
				BEGIN
				# 执行语句操作
				SELECT * FROM innodb_lock WHERE id = 3
				# 释放锁
				COMMIT
			f：结论分析
				① innoDB 存储引擎由于实现了行锁，虽然在锁机制实现方面所带来的性能损耗可能比表锁更高一些，但是在整体并发处理能力上要远远高于 
				  MyISAM 的表锁机制，当系统的并发量越高的时候，innoDB 的整体性能和 MyISAM 相比就会有比较明细的优势。
				② innoDB 存储引擎同样也有其脆弱的一面，当我们使用不当的时候，可能会让 innoDB 的整体性能表现不仅不能比 MyISAM 高，反而会更差。
		D：行锁分析
			a：通过查询 innodb_row_lock 状态变量来分析系统上的行锁争夺情况
			b：SQL：show status like 'innodb_row_lock%';
			c：查询结果变量说明
				① innodb_row_lock_current_waits：当前产生锁阻塞的 SQL 数量		# 重点
				② innodb_row_lock_time：从系统启动到现在锁定总时间长度		# 重点
				③ innodb_row_lock_time_avg：每次等待所花平均时间		# 重点
				④ innodb_row_lock_time_max：从系统启动到现在等待最长一次所花时间
				⑤ innodb_row_lock_waits：从系统启动到现在总共等待的次数
		E：优化建议
			a：尽可能让所有数据检索由索引来完成，避免无索引造成行锁升级为表锁
			b：合理设计索引，尽量缩小锁的范围
			c：尽可能缩小检索条件，产生间隙锁
			d：尽量控制事务大小，减少锁定资源量和时间长度
			e：尽可能降低事务隔离级别
	③ 页锁(了解一下即可)
		a：开销和加锁时间介于行锁和表锁之间，会出现死锁，锁定颗粒度介于表锁和行锁之间，并发度一般

主从复制

1、复制的基本原理

	A：slave 会 从 master 读取 binarylog 二进制日志文件来进行数据同步
	B：三个步骤 + 原理图
		a：master 将非查询 SQL 记录在二进制文件(binary log),这些记录过程叫做二进制日志事件，binary log events;
		b：slave 将 master 的 binary log events 拷贝到它的中继日志文件(relay log)
		c：slave 重做中继日志中的事件，将改变应用到自己的数据库中，MySQL 复制是异步的且串行化的

2、复制的基本原则

	A：每个 slave 只有一个master
	B：每个 slave 都只能有一个唯一的服务器ID
	C：每个 master 可以有多个 slave

3、复制的最大问题：网络延时

4、一主一从常见配置

	A：MySQL 版本要一致且以后台服务运行
	B：主从配置在[mysqld]节点下，都是小写
	C：主机修改 my.cnf 配置文件
		a：主服务器唯一ID：
			① server-id=1
		b：启用二进制日志文件
			① log-bin=mysqlbin
		c：启用错误日志
			① log-err=mysqlerr
		d：根目录(可选)
			① basedir="mysql数据存储路径"
		e：临时目录(可选)
			① basedir="mysql数据存储路径"
		e：数据目录(可选)
			① basedir="mysql数据存储路径"
		f：主机拥有读写权限
			① read-only=0
		g：设置不要复制的数据库(可选)
			① binlog-ignore-db=数据库名称
		h：设置需要复制的数据库
			① binlog-do-db=数据库名称

# ------------------------------ 主从复制配置参数列表 ----------------------------
# 主服务器唯一ID
server-id=1
# 启用二进制日志文件
log-bin=mysql-bin
# 启用错误日志
log-err=mysql-err
# 主机拥有读写权限
read-only=0
# 设置不要复制的数据库(可选)
binlog-ignore-db=mysql
# 设置需要复制的数据库
binlog-do-db=learn

	D：从机修改 my.cnf 配置文件
		a：主服务器唯一ID：
			① server-id=2
		b：启用二进制日志文件
			① bin-log=mysqlbin

# ------------------------- 主从复制的 slave 配置参数列表 --------------------------
# # 主服务器唯一ID
server-id=2
# # 启用二进制日志文件
log-bin=mysql-bin

	E：因修改过配置文件，请主机 + 从机都重启后台 MySQL 服务
	F：主机、从机都要关闭防火墙
	G：在 master 上建立账号并授权给 slave
		a：授权账号
			GRANT REPLICATION SLAVE ON *.* TO '用户名'@'slave IP' IDENTIFIED BY '密码';
		b：刷新信息
			FLUSH PRIVILEGES;
		c：查询 master 状态(注意记录：file、position两个变量的值)
			SHOW MASTER STATUS;
		d：执行完此步骤后不要再动 master，防止主服务器状态值变化
	H：在 slave 上配置需要复制的 master
		a：账号授权
			CHANGE MASTER TO MASTER_HOST='master IP', 
			MASTER_USER='用户名', 
			MASTER_PASSWORD='密码', 
			MASTER_PORT=端口号, 
			MASTER_LOG_FILE='file变量值', 
			MASTER_LOG_POS= position变量值, 
			MASTER_CONNECT_RETRY=30;
		b：启动从机服务器复制功能
			START SLAVE;
		c：查看 slave 状态：SHOW SLAVE STATUS\G;
			① 下面两个变量都为yes，则说明主从配置成功
			② Slave_IO_Running:yes
			③ Slave_SQL_Running:yes
		d：停止从机服务器复制功能
			STOP SLAVE;
	I：主从复制排错
		a：使用start slave开启主从复制过程后，如果SlaveIORunning一直是Connecting，则说明主从复制一直处于连接状态，
		这种情况一般是下面几种原因造成的，我们可以根据 Last_IO_Error提示予以排除。
			① 网络不通
			② 检查ip,端口
			③ 密码不对
			④ 检查是否创建用于同步的用户和用户密码是否正确
			⑤ pos不对
			⑥ 检查Master的 Position
	I：master 
		a：新建数据库
		b：数据表
		c：insert 数据
		d：slave 复制