8、性能分析
8.1、EXPLAIN简介
EXPLAIN：SQL的执行计划，使EXPLAIN关键字模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句的
#语法
mysql> explain select * from books \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: books
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1425
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
8.2、EXPLAIN信息
- id: 表的读取顺序
- select_type: 数据读取操作的操作类型
- possible_keys：哪些索引可以使用
- key：哪些索引被实际使用
- ref：表之间的引用。
- rows：每张表有多少行被优化器查询
8.3、EXPLAIN字段
id id：表的读取和加载顺序。 值有以下三种情况：
- id相同，执行顺序由上至下。
- id不同，如果是子查询，id的需要会递增，id值越大优先级越高，优先被执行
- id相同不同，同时存在。永远是id大的优先级最高，id相等的时候顺序执行。
select_type select_type：数据查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询
- simple：简单的select查询，查询中不包含子查询或者union
- primary：查询中如果包含任何复杂的子部分，最外层查询被标记为primary
- subquery：在select 或者 where 子句中包含了子查询
- derived：在from子句中包含的子查询被标记为derived（衍生），MySQL会递归执行这个子查询，把结果放在临时表中
- union：如果第二个select出现在union之后，则被标记为union；若union包含在from语句的子查询中，外层select将被标记为derived
- union result：从union表中获得的结果就是select
type type：访问类型排序
最好到最差以此排序为：system > const > eq_ref > ref > range > index > all 。除了ALL没有用到索引，其他级别都用到索引了。
一般来说，保证查询至少达到range级别，最好到达ref。
- system：标准是只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计。
- const：表示通过索引一次就找到了。const用于比较primary key或者unique索引。因为只匹配一行数据，索引很快。如果将主键置于where列表中，MySQL就能将该查询转化为常量。
- eq_ref：唯一性索引扫描，读取本表中和关联表表中的每行组成的一行，查询出来只有一条记录。除了system和const类型之外，这是最好的链接类型。
- ref：非唯一性索引扫描，返回本表和关联表某个值匹配的所有行，查出来有多条记录。
- range：只检索给定范围的行，一般就是在where语句中出现了between、<>、in等的查询。这种范围扫描索引比全表扫描要好，因为它只需要开始于索引树的某一点，而结束于另一点，不需要全盘扫描。
- index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说ALL和Index都是都全表，但是index是从索引中读的，ALL是从磁盘中读取的
- ALL：Full Table Scan，没有索引，全表扫描
possible_keys 和 key
- possible_keys：显示可能应用在这张表中的索引，一个或者多个。查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用。
- key：实际使用的索引。如果为NULL，则没有使用索引。查询中如果使用了覆盖索引，则该索引仅仅出现在key列表中
key_len
key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引长的。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得的，不是通过表内检索出的。在不巡视精度的情况下，越短越好。
mysql> explain select cat_id from pms_category where cat_id between 10 and 20 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: pms_category partitions: NULL type: range possible_keys: PRIMARY key: PRIMARY # 用到了主键索引，通过查看表结构知道，cat_id是bigint类型，占用8个字节 key_len: 8 # 这里只用到了cat_id主键索引，所以长度就是8！ ref: NULL rows: 11 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0.00 sec)
ref ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录需要读书的行数
Extra extra：包含不适合在其他列中显示但十分重要的额外信息。
- Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为位“文件内排序”
- Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用了临时表。常见于排序Order by 和分组查询group by。临时表对系统性能损耗较大。
- Using Index：表示相应的select操作中使用了覆盖索引，避免访问表的数据行，效率高，如果同时出现了using where，表示索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
9、索引分析
9.1、单表索引分析
#数据准备
DROP TABLE IF EXISTS `article`;
CREATE TABLE IF NOT EXISTS `article`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`author_id` INT(10) UNSIGNED NOT NULL COMMENT '作者id',
`category_id` INT(10) UNSIGNED NOT NULL COMMENT '分类id',
`views` INT(10) UNSIGNED NOT NULL COMMENT '被查看的次数',
`comments` INT(10) UNSIGNED NOT NULL COMMENT '回帖的备注',
`title` VARCHAR(255) NOT NULL COMMENT '标题',
`content` VARCHAR(255) NOT NULL COMMENT '正文内容'
) COMMENT '文章';
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,1,1,'1','1');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(2,2,2,2,'2','2');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(3,3,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,3,3,'3','3');
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES(1,1,4,4,'4','4');
查询category_id=1 && comments > 1 views最多的article_id
- 1、编写SQL语句并查看SQL执行 select id,author_id from article where category_id = 1 and comments > 1 order by views DESC limit 1
#查询结果
mysql> EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: article
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
filtered: 20.00
Extra: Using where; Using filesort # 产生了文件内排序，需要优化SQL
1 row in set, 1 warning (0.00 sec)
-
2、创建索引 idx_article_ccv create index idex_article_ccv ON article(category_id,comments,views)
-
3、查看当前索引 show index from article
- 4、再次查询
我们发现，创建符合索引idx_article_ccv之后，虽然解决了全表扫描的问题，但是在order by排序的时候没有用到索引，MySQL居然还是用的Using filesort，为什么？
- 5、我们尝试把SQL修改为select id,author_id where category_id = 1 AND comments = 1 order by views DESC limit 1
推论：当comments > 1 的时候order by排序views字段的索引就用不上，但是comments = 1 的时候，order by排序views字段索引就可医用上，所以，范围之后的索引会失效
- 6、我们现在知道范围之后的索引会失效，原来的索引idx_article_ccv最后一个字段views会失效，那么我们如果删除掉这个索引，创建cv索引。
CREATE INDEX idx_article_cv ON article(category_id,views);
查看索引
- 7、当前索引是idx_article_cv,来看一下SQL执行计划。
、
9.2、两表索引分析
#数据准备
DROP TABLE IF EXISTS `class`;
DROP TABLE IF EXISTS `book`;
CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '商品类别';
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '书籍';
两表链接查询的SQL
- 1、不创建索引的情况下，SQL的执行计划
- 2、左表创建索引
CREATE INDEX idx_book_card ON book(card);
- 3、删除book的索引，class表创建索引
CREATE INDEX idx_class_card ON class(card);
- 4、再次执行sql
由上可知，左连接将索引建在右表更合适，反之
9.3、三张表分析
#数据准备
DROP TABLE IF EXISTS `phone`;
CREATE TABLE IF NOT EXISTS `phone`(
`phone_id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
`card` INT(10) UNSIGNED NOT NULL COMMENT '分类'
) COMMENT '手机';
- 1、没有索引的情况下
- 2、根据两表查询优化的经验，左连接加游标，所以在book和phone表上添加索引
CREATE INDEX idx_book_card ON book(card);
CREATE INDEX idx_phone_card ON phone(card);
- 3、再次执行SQL
9.4、结论
Join语句的优化：
- 尽可能减少JOIN语句中的NestedLoop（嵌套循环）的总次数：永远都是小的结果集驱动大的结果集
- 优先优化NestedLoop的内层循环。
- 保证JOIN语句中被驱动表上JOIN条件字段已经被索引
- 当无法保证被驱动表的JOIN条件字段被索引且内存资源充足的前提下，不要太吝惜Join Buffer的设置。
10、索引失效
#数据准备
CREATE TABLE `staffs`(
`id` INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` INT(10) NOT NULL DEFAULT 0 COMMENT '年龄',
`pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
)COMMENT '员工记录表';
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('Ringo', 18, 'manager');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('张三', 20, 'dev');
INSERT INTO `staffs`(`name`,`age`,`pos`) VALUES('李四', 21, 'dev');
/* 创建索引 */
CREATE INDEX idx_staffs_name_age_pos ON `staffs`(`name`,`age`,`pos`);
10.1、索引失效的情况
- 全只匹配我最爱
- 最佳左前缀法则
- 不在索引列上做任何操作（计算、函数、自动or手动类型转换），会导致索引失效转向ALL
- 索引中范围条件右边的字段会全部失效
- 尽量使用覆盖索引，减少Select *
- MySQL在使用！=或者<> 的时候会导致全表扫描
- is null 、 is not null也无法使用索引
- like以通配符开头%abc索引失效会变成全表扫描
- 字符串不加单引号索引失效
- 少用or，用它来连接时会索引失效
10.2、最佳左前缀法则
/* 用到了idx_staffs_name_age_pos索引中的name字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo';
/* 用到了idx_staffs_name_age_pos索引中的name, age字段 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18;
/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `age` = 18 AND `pos` = 'manager';
/* 索引没用上，ALL全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `pos` = 'manager';
概念
最佳左前缀法则：如果索引时多字段的复合索引，要遵守最佳做前缀法则，指的是查询从索引的最左列开始并且不跳过索引中的字段
口诀：带头大哥不能死，中间兄弟不能断。
10.3、索引列上不计算
案例
# 现在要查询`name` = 'Ringo'的记录下面有两种方式来查询！
# 1、直接使用 字段 = 值的方式来计算
mysql> SELECT * FROM `staffs` WHERE `name` = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)
# 2、使用MySQL内置的函数
mysql> SELECT * FROM `staffs` WHERE LEFT(`name`, 5) = 'Ringo';
+----+-------+-----+---------+---------------------+
| id | name | age | pos | add_time |
+----+-------+-----+---------+---------------------+
| 1 | Ringo | 18 | manager | 2020-08-03 08:30:39 |
+----+-------+-----+---------+---------------------+
1 row in set (0.00 sec)
两条SQL似乎并没有不同，那么通过EXPALIN分析后
由此可见，在索引列上进行计算，会使索引失效。 口诀：索引列上不计算。
10.4、范围之后全失效
/* 用到了idx_staffs_name_age_pos索引中的name，age，pos字段 这是属于全值匹配的情况！！！*/
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 用到了idx_staffs_name_age_pos索引中的name，age字段，pos字段索引失效 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = '张三' AND `age` > 18 AND `pos` = 'dev';
由此可知，查询范围的字段使用到了索引，但是范围之后的索引字段会消失。 口诀：范围之后全失效
10.5、覆盖索引尽量用
写SQL的时候不要使用Select * ， 用什么字段就查询什么字段
/* 没有用到覆盖索引 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
/* 用到了覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` = 'Ringo' AND `age` = 18 AND `pos` = 'manager';
口诀：查询一定不用*
10.6、不等有时会失效
/* 会使用到覆盖索引 */
EXPLAIN SELECT `name`, `age`, `pos` FROM `staffs` WHERE `name` != 'Ringo';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` != 'Ringo';
10.7、like百分加右边
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing%';
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE '%ing';
/* 使用索引范围查询 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` LIKE 'Rin%';
口诀：like百分加右边。
如果一定要使用%like，而且还要保证索引不失效，那么使用覆盖索引来编写SQL
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `pos` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `age` FROM `staffs` WHERE `name` LIKE '%in%';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`,`name`, `age`, `pos` FROM `staffs` WHERE `name` LIKE '%in';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `pos` LIKE '%na';
/* 索引失效 全表扫描 */
EXPLAIN SELECT `name`, `age`, `pos`, `add_time` FROM `staffs` WHERE `name` LIKE '%in';
口诀：覆盖索引保两边。
10.8、字符要加单引号
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 'Ringo';
/* 使用到了覆盖索引 */
EXPLAIN SELECT `id`, `name` FROM `staffs` WHERE `name` = 2000;
/* 索引失效 全表扫描 */
EXPLAIN SELECT * FROM `staffs` WHERE `name` = 2000;
这里name = 2000在MySQL中会发生强制类型转换，将数字转成字符串。
口诀：字符要加单引号。
10.9、索引相关题目
假设index(a,b,c)
10.10、面试题分析
/* 创建表 */
CREATE TABLE `test03`(
`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
`c1` CHAR(10),
`c2` CHAR(10),
`c3` CHAR(10),
`c4` CHAR(10),
`c5` CHAR(10)
);
/* 插入数据 */
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('a1','a2','a3','a4','a5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('b1','b22','b3','b4','b5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('c1','c2','c3','c4','c5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('d1','d2','d3','d4','d5');
INSERT INTO `test03`(`c1`,`c2`,`c3`,`c4`,`c5`) VALUES('e1','e2','e3','e4','e5');
/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);
题目
/* 最好索引怎么创建的，就怎么用，按照顺序使用，避免让MySQL再自己去翻译一次 */
/* 1.全值匹配 用到索引c1 c2 c3 c4全字段 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` = 'a3' AND `c4` = 'a4';
/* 2.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' AND `c3` = 'a3';
/* 3.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c4` = 'a4' AND `c3` = 'a3' AND `c2` = 'a2' AND `c1` = 'a1';
/* 4.用到索引c1 c2 c3字段，c4字段失效，范围之后全失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c3` > 'a3' AND `c4` = 'a4';
/* 5.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';
/*
6.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;
/* 7.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;
/*
8.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;
/* 9.用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;
/* 10.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
/* 11.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY c2, c3;
/* 12.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c2, c3;
/*
13.用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort
因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
所以没有产生Using filesort 和(10)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;
/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */
/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;
/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;
GROUP BY基本上都需要进行排序，索引优化几乎和ORDER BY一致，但是GROUP BY会有临时表的产生。
10.11、总结
索引优化的一般性建议：
- 对于单值索引，尽量选择针对当前query过滤性更好的索引。
- 在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
- 在选择复合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引。
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。
口诀：
- 带头大哥不能死。
- 中间兄弟不能断。
- 索引列上不计算。
- 范围之后全失效。
- 覆盖索引尽量用。
- 不等有时会失效。
- like百分加右边。
- 字符要加单引号。
- 一般SQL少用or。
11、分析慢SQL的步骤
分析：
-
1、观察，至少跑1天，看看生产的慢SQL情况。
-
2、开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
-
3、explain + 慢SQL分析。
-
4、show Profile。
-
5、运维经理 OR DBA，进行MySQL数据库服务器的参数调优。
总结（大纲）：
-
1、慢查询的开启并捕获。
-
2、explain + 慢SQL分析。
-
3、show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。
-
4、MySQL数据库服务器的参数调优。
12、查询优化
12.1、小表驱动大表
优化原则：对于MySQL数据库而言，永远都是小表驱动大表。
/**
* 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
* 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
* 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
* 第一种可以理解为，和MySQL建立1000次连接每次查询5次。
*/
for(int i = 1; i <= 5; i ++){
for(int j = 1; j <= 1000; j++){
}
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
for(int j = 1; j <= 5; j++){
}
}
In和EXISTS
/* 优化原则：小表驱动大表，即小的数据集驱动大的数据集 */
/* IN适合B表比A表数据小的情况*/
SELECT * FROM `A` WHERE `id` IN (SELECT `id` FROM `B`)
/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM `A` WHERE EXISTS (SELECT 1 FROM `B` WHERE `B`.id = `A`.id);
Exists:
- 语法：select ... from tab where exists(subquery);
- 理解：将著查询的数据，放在子查询中做条件验证，根据验证结果来决定查询的数据结果是否得以保留。
提示：
- EXISTS（subquery）子查询只返回true或者false，因此子查询中select * 可以是select 1 or select X ， 他们并没有区别
- EXISTS（subquery）子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可以进行实际检验以确定是否有效率问题。
- EXISTS（subquery）子查询往往也可以用条件表达式，其他子查询或者join代替，何种最优需要具体问题具体分析。
12.2、ORDER BY优化
数据准备
CREATE TABLE `talA`(
`age` INT,
`birth` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO `talA`(`age`) VALUES(18);
INSERT INTO `talA`(`age`) VALUES(19);
INSERT INTO `talA`(`age`) VALUES(20);
INSERT INTO `talA`(`age`) VALUES(21);
INSERT INTO `talA`(`age`) VALUES(22);
INSERT INTO `talA`(`age`) VALUES(23);
INSERT INTO `talA`(`age`) VALUES(24);
INSERT INTO `talA`(`age`) VALUES(25);
/* 创建索引 */
CREATE INDEX idx_talA_age_birth ON `talA`(`age`, `birth`);
案例
/* 1.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`;
/* 2.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `age`,`birth`;
/* 3.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`;
/* 4.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `age` > 20 ORDER BY `birth`,`age`;
/* 5.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `birth`;
/* 6.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `birth`;
/* 7.使用索引进行排序了 不会产生Using filesort */
EXPLAIN SELECT * FROM `talA` WHERE `birth` > '2020-08-04 07:42:21' ORDER BY `age`;
/* 8.没有使用索引进行排序 产生了Using filesort */
EXPLAIN SELECT * FROM `talA` ORDER BY `age` ASC, `birth` DESC;
ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。
MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序：
- ORDER BY语句使用索引最左前列。
- 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列。
结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。
如果不在所以列上，File sort有两种算法：MySQL就要启动双路排序算法和单路排序算法
1、双路排序算法：MySQL4.1之前使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，対他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。一句话，从磁盘取排序字段，在buffer中进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。
2、单路排序算法：从磁盘读取查询需要的所有列，按照ORDER BY列在buffer対它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
由于单路排序算法是后出的，总体而言效率好过双路排序算法。
但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。
单路复用算法的优化策略：
- 增大sort_buffer_size参数的设置。
- 增大max_length_for_sort_data参数的设置。
提高ORDER BY排序的速度：
-
ORDER BY时使用SELECT * 是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
- 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
- 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
-
尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
-
尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。
12.3、group by优化
-
GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
-
当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
-
WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。
12.4、总结
为排序使用索引
- MySQL两种排序方式：Using filesort和Index扫描有序索引排序。
- MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。
/* 创建a b c三个字段的索引 */
idx_table_a_b_c(a, b, c)
/* 1.ORDER BY 能使用索引最左前缀 */
ORDER BY a;
ORDER BY a, b;
ORDER BY a, b, c;
ORDER BY a DESC, b DESC, c DESC;
/* 2.如果WHERE子句中使用索引的最左前缀定义为常量，则ORDER BY能使用索引 */
WHERE a = 'Ringo' ORDER BY b, c;
WHERE a = 'Ringo' AND b = 'Tangs' ORDER BY c;
WHERE a = 'Ringo' AND b > 2000 ORDER BY b, c;
/* 3.不能使用索引进行排序 */
ORDER BY a ASC, b DESC, c DESC; /* 排序不一致 */
WHERE g = const ORDER BY b, c; /* 丢失a字段索引 */
WHERE a = const ORDER BY c; /* 丢失b字段索引 */
WHERE a = const ORDER BY a, d; /* d字段不是索引的一部分 */
WHERE a IN (...) ORDER BY b, c; /* 对于排序来说，多个相等条件(a=1 or a=2)也是范围查询 */
13、慢查询日志
慢查询日志是什么？
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值为10，意思是运行10秒以上的语句。
- 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析。
特别说明
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动开启
如果不是调优需要，一般不建议启动该参数，因为开启后会对性能带来影响。
查看慢查询日志是否开启
- 查看慢查询日志是否开启：show variable like '%slow_query_log%';
- 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启，只对当前数据库生效，重启MySQL后会失效
# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/1dcb5644392c-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
# 2、开启慢查询日志
mysql> SET GLOBAL slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)
如果要使慢查询日志永久开启，需要修改my.cnf文件，在[mysqld]下增加修改参数。
# my.cnf
[mysqld]
# 1.这个是开启慢查询。注意ON需要大写
slow_query_log=ON
# 2.这个是存储慢查询的日志文件。这个文件不存在的话，需要自己创建
slow_query_log_file=/var/lib/mysql/slow.log
开启了慢查询日志后，什么样的SQL才会被记录到慢查询日志里面呢？
这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒。
MySQL中查看long_query_time的时间：SHOW VARIABLES LIKE 'long_query_time%';。
# 查看long_query_time 默认是10秒
# 只有SQL的执行时间>10才会被记录
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
修改long_query_time的时间，需要在my.cnf修改配置文件
[mysqld]
# 这个是设置慢查询的时间，我设置的为1秒
long_query_time=1
查新慢查询日志的总记录条数：SHOW GLOBAL STATUS LIKE '%Slow_queries%';。
mysql> SHOW GLOBAL STATUS LIKE '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries | 3 |
+---------------+-------+
1 row in set (0.00 sec)
13.2、日志分析工具
日志分析工具mysqldumpslow：在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
# 1、mysqldumpslow --help 来查看mysqldumpslow的帮助信息
root@1dcb5644392c:/usr/bin# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default # 按照何种方式排序
al: average lock time # 平均锁定时间
ar: average rows sent # 平均返回记录数
at: average query time # 平均查询时间
c: count # 访问次数
l: lock time # 锁定时间
r: rows sent # 返回记录
t: query time # 查询时间
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries # 返回前面多少条记录
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
# 2、 案例
# 2.1、得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
# 2.2、得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log
# 2.3、得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow.log
# 2.4、另外建议使用这些命令时结合|和more使用，否则出现爆屏的情况
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log | more
14、批量插入数据脚本
14.1、环境准备
- 1、建表SQL
/* 1.dept表 */
CREATE TABLE `dept` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
`dname` varchar(20) NOT NULL DEFAULT '' COMMENT '部门名字',
`loc` varchar(13) NOT NULL DEFAULT '' COMMENT '部门地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='部门表'
/* 2.emp表 */
CREATE TABLE `emp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`empno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '员工编号',
`ename` varchar(20) NOT NULL DEFAULT '' COMMENT '员工名字',
`job` varchar(9) NOT NULL DEFAULT '' COMMENT '职位',
`mgr` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
`hiredata` date NOT NULL COMMENT '入职时间',
`sal` decimal(7,2) NOT NULL COMMENT '薪水',
`comm` decimal(7,2) NOT NULL COMMENT '分红',
`deptno` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '部门id',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工表'
- 2、由于开启过慢查询日志，开启了bin-log，我们就必须为function指定一个参数，否则使用函数会报错。
# 在mysql中设置
# log_bin_trust_function_creators 默认是关闭的 需要手动开启
mysql> SHOW VARIABLES LIKE 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
上述修改方式MySQL重启后会失败，在my.cnf配置文件下修改永久有效。
[mysqld]
log_bin_trust_function_creators=ON
14.2、创建函数
# 1、函数：随机产生字符串
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwsyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
# 2、函数：随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100 + RAND() * 10);
RETURN i;
END $$
14.3、创建存储过程
# 1、函数：向dept表批量插入
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept(deptno,dname,loc) VALUES((START + i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
# 2、函数：向emp表批量插入
DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,ename,job,mgr,hiredata,sal,comm,deptno) VALUES((START + i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
14.4.调用存储过程
# 1、调用存储过程向dept表插入10个部门。
DELIMITER ;
CALL insert_dept(100,10);
# 2、调用存储过程向emp表插入50万条数据。
DELIMITER ;
CALL insert_emp(100001,500000);
15、Show Profile
Show Profile是什么？ Show Profile：MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
- 1、是否支持，看看当前的Mysql版本是否支持
# 查看Show Profile功能是否开启
mysql> SHOW VARIABLES LIKE 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.00 sec)
- 2、开启Show Profile功能，默认是关闭的，使用前需要开启。
# 开启Show Profile功能
mysql> SET profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- 3、运行SQL
SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000;
SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5;
- 4、查看结果，执行SHOW PROFILES; Duration：持续时间。
mysql> SHOW PROFILES;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------+
| 1 | 0.00156100 | SHOW VARIABLES LIKE 'profiling' |
| 2 | 0.56296725 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
| 3 | 0.52105825 | SELECT * FROM `emp` GROUP BY `id`%10 LIMIT 150000 |
| 4 | 0.51279775 | SELECT * FROM `emp` GROUP BY `id`%20 ORDER BY 5 |
+----------+------------+---------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
- 5、诊断SQL，SHOW PROFILE cpu,block io FOR QUERY Query_ID;
# 这里的3是第四步中的Query_ID。
# 可以在SHOW PROFILE中看到一条SQL中完整的生命周期。
mysql> SHOW PROFILE cpu,block io FOR QUERY 3;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000097 | 0.000090 | 0.000002 | 0 | 0 |
| checking permissions | 0.000010 | 0.000009 | 0.000000 | 0 | 0 |
| Opening tables | 0.000039 | 0.000058 | 0.000000 | 0 | 0 |
| init | 0.000046 | 0.000046 | 0.000000 | 0 | 0 |
| System lock | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000023 | 0.000037 | 0.000000 | 0 | 0 |
| preparing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000041 | 0.000053 | 0.000000 | 0 | 0 |
| Sorting result | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.520620 | 0.516267 | 0.000000 | 0 | 0 |
| Creating sort index | 0.000060 | 0.000051 | 0.000000 | 0 | 0 |
| end | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000032 | 0.000064 | 0.000000 | 0 | 0 |
| cleaning up | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
20 rows in set, 1 warning (0.00 sec)
-
Show Profile查询参数备注：
- ALL：显示所有的开销信息。
- BLOCK IO：显示块IO相关开销（通用）。
- CONTEXT SWITCHES：上下文切换相关开销。
- CPU：显示CPU相关开销信息（通用）。
- IPC：显示发送和接收相关开销信息。
- MEMORY：显示内存相关开销信息。
- PAGE FAULTS：显示页面错误相关开销信息。
- SOURCE：显示和Source_function。
- SWAPS：显示交换次数相关开销的信息。
-
6、Show Profile查询列表，日常开发需要注意的结论：
-
converting HEAP to MyISAM：查询结果太大，内存都不够用了，往磁盘上搬了。
-
Creating tmp table：创建临时表（拷贝数据到临时表，用完再删除），非常耗费数据库性能。
-
Copying to tmp table on disk：把内存中的临时表复制到磁盘，危险！！！
-
locked：死锁。
16.表锁（偏读）
- 特点：表锁偏向于MyISAM存储引擎，开销小，加锁快，无死锁，锁定颗粒度大，发生锁冲突的概率最高，并发度最低。
16.1、环境准备
# 1、创建表
CREATE TABLE `mylock`(
`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20)
)ENGINE=MYISAM DEFAULT CHARSET=utf8 COMMENT='测试表锁';
# 2、插入数据
INSERT INTO `mylock`(`name`) VALUES('ZhangSan');
INSERT INTO `mylock`(`name`) VALUES('LiSi');
INSERT INTO `mylock`(`name`) VALUES('WangWu');
INSERT INTO `mylock`(`name`) VALUES('ZhaoLiu');
16.2、表锁的命令
-
1、查看数据库表锁的命令
- SHOW OPEN TABLES;
-
2、给mylock表上读锁，给book表上写锁
- LOCK TABLE
mylockREAD,bookWRITE;
- LOCK TABLE
#查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis | book | 1 | 0 |
| sql_analysis | mylock | 1 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
- 3、释放表锁。
# 释放给表添加的锁
UNLOCK TABLES;
# 查看当前表的状态
mysql> SHOW OPEN TABLES;
+--------------------+------------------------------------------------------+--------+-------------+
| Database | Table | In_use | Name_locked |
+--------------------+------------------------------------------------------+--------+-------------+
| sql_analysis | book | 0 | 0 |
| sql_analysis | mylock | 0 | 0 |
+--------------------+------------------------------------------------------+--------+-------------+
16.3、读锁案例
- 1、打开两个会话，Session1为mylock加读锁。
- lock table
mylockread;
- lock table
- 2、打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？
# SESSION1
# 问题1：SESSION1为mylock表加了读锁，可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name |
+----+----------+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+----+----------+
4 rows in set (0.00 sec)
# 问题2：SESSION1为mylock表加了读锁，不可以修改mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1099 (HY000): Table 'mylock' was locked with a READ lock and can't be updated
# 问题3：SESSION1为mylock表加了读锁，不可以读其他的表！
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# SESSION2
# 问题1：SESSION1为mylock表加了读锁，SESSION2可以读mylock表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name |
+----+----------+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+----+----------+
4 rows in set (0.00 sec)
# 问题2：SESSION1为mylock表加了读锁，SESSION2修改mylock表会被阻塞，需要等待SESSION1释放mylock表！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1317 (70100): Query execution was interrupted
# 问题3：SESSION1为mylock表加了读锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 1 |
| 7 | 4 |
| 8 | 4 |
| 9 | 5 |
| 5 | 6 |
| 17 | 6 |
| 15 | 8 |
+--------+------+
24 rows in set (0.00 sec)
16.4、写锁案例
- 1、打开两个会话，SESSION1为mylock表添加写锁。
- lock table
mylockwrite;
- lock table
- 2、打开两个会话，SESSION1是否可以读自己锁的表？是否可以修改自己锁的表？是否可以读其他的表？那么SESSION2呢？
# SESSION1
# 问题1：SESSION1为mylock表加了写锁，可以读mylock的表！
mysql> SELECT * FROM `mylock`;
+----+----------+
| id | name |
+----+----------+
| 1 | ZhangSan |
| 2 | LiSi |
| 3 | WangWu |
| 4 | ZhaoLiu |
+----+----------+
4 rows in set (0.00 sec)
# 问题2：SESSION1为mylock表加了写锁，可以修改mylock表!
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 问题3：SESSION1为mylock表加了写锁，不能读其他表!
mysql> SELECT * FROM `book`;
ERROR 1100 (HY000): Table 'book' was not locked with LOCK TABLES
# SESSION2
# 问题1：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> SELECT * FROM `mylock`;
ERROR 1317 (70100): Query execution was interrupted
# 问题2：SESSION1为mylock表加了写锁，SESSION2读mylock表会阻塞，等待SESSION1释放！
mysql> UPDATE `mylock` SET `name` = 'abc' WHERE `id` = 1;
ERROR 1317 (70100): Query execution was interrupted
# 问题3：SESSION1为mylock表加了写锁，SESSION2可以读其他表！
mysql> SELECT * FROM `book`;
+--------+------+
| bookid | card |
+--------+------+
| 1 | 1 |
| 7 | 4 |
| 8 | 4 |
| 9 | 5 |
| 5 | 6 |
| 17 | 6 |
| 15 | 8 |
+--------+------+
24 rows in set (0.00 sec)
16.5、案例结论
MyISAM引擎在执行查询语句select之前，会自动给涉及到所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁
MySQL的表级锁有两种模式：
- 表共享读锁（Table Read Lock）。
- 表独占写锁（Table Write Lock）。 对MyISANM表进行操作，会有以下情况：
- 对MyISAM表的读操作（加读锁），不会阻塞其他线程对同一表的读操作，但是会阻塞其他线程对同一表的写操作。只有当读锁释放之后，才能执行其他线程的写操作
- 対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。
16.6、表锁分析
mysql> SHOW STATUS LIKE 'table%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Table_locks_immediate | 173 |
| Table_locks_waited | 0 |
| Table_open_cache_hits | 5 |
| Table_open_cache_misses | 8 |
| Table_open_cache_overflows | 0 |
+----------------------------+-------+
5 rows in set (0.00 sec)
可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：
-
Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
-
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况
此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞。
17、行锁（偏写）
行锁特点：
- 偏向于InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。InnocentDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁 事务的ACID：
- Atomicity 原子性
- Consistency 一致性
- Isolation 隔离性
- Durability 持久性
17.1、环境准备
# 建表语句
CREATE TABLE `test_innodb_lock`(
`a` INT,
`b` VARCHAR(16)
)ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='测试行锁';
# 插入数据
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(1, 'b2');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(2, '3');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(3, '4000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(4, '5000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(5, '6000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(6, '7000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(7, '8000');
INSERT INTO `test_innodb_lock`(`a`, `b`) VALUES(8, '9000');
# 创建索引
CREATE INDEX idx_test_a ON `test_innodb_lock`(a);
CREATE INDEX idx_test_b ON `test_innodb_lock`(b);
17.2、行锁案例
-
1、手动开启提交 Set autocommit = 0;
-
2、不提交
# SESSION1
# SESSION1対test_innodb_lock表做写操作，但是没有commit。
# 执行修改SQL之后，查询一下test_innodb_lock表，发现数据被修改了。
mysql> UPDATE `test_innodb_lock` SET `b` = '88' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a | b |
+------+------+
| 1 | 88 |
| 2 | 3 |
| 3 | 4000 |
| 4 | 5000 |
| 5 | 6000 |
| 6 | 7000 |
| 7 | 8000 |
| 8 | 9000 |
+------+------+
8 rows in set (0.00 sec)
# SESSION2
# SESSION2这时候来查询test_innodb_lock表。
# 发现SESSION2是读不到SESSION1未提交的数据的。
mysql> SELECT * FROM `test_innodb_lock`;
+------+------+
| a | b |
+------+------+
| 1 | b2 |
| 2 | 3 |
| 3 | 4000 |
| 4 | 5000 |
| 5 | 6000 |
| 6 | 7000 |
| 7 | 8000 |
| 8 | 9000 |
+------+------+
8 rows in set (0.00 se
- 3、行锁两个Session同时对一条记录进行写操作
# SESSION1 対test_innodb_lock表的`a`=1这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '99' WHERE `a` = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# SESSION2 也对test_innodb_lock表的`a`=1这一行进行写操作，但是发现阻塞了！！！
# 等SESSION1执行commit语句之后，SESSION2的SQL就会执行了
mysql> UPDATE `test_innodb_lock` SET `b` = 'asdasd' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
- 4、行锁两个Session同时对不同记录进行写操作
# SESSION1 対test_innodb_lock表的`a`=6这一行进行写操作，但是没有commit
mysql> UPDATE `test_innodb_lock` SET `b` = '8976' WHERE `a` = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# SESSION2 対test_innodb_lock表的`a`=4这一行进行写操作，没有阻塞！！！
# SESSION1和SESSION2同时对不同的行进行写操作互不影响
mysql> UPDATE `test_innodb_lock` SET `b` = 'Ringo' WHERE `a` = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
17.3、索引失效行锁变表锁
# SESSION1 执行SQL语句，没有执行commit。
# 由于`b`字段是字符串，但是没有加单引号导致索引失效
mysql> UPDATE `test_innodb_lock` SET `a` = 888 WHERE `b` = 8000;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
# SESSION2 和SESSION1操作的并不是同一行，但是也被阻塞了？？？
# 由于SESSION1执行的SQL索引失效，导致行锁升级为表锁。
mysql> UPDATE `test_innodb_lock` SET `b` = '1314' WHERE `a` = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
17.4、间隙锁的危害
什么是间隙锁？
当我们用范围条件而不是相等条件检索数据，并请求共享或者排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范文内但并不存在的记录，叫做"间隙(GAP)"。
间隙锁的危害
因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
间隙锁有一个比较致命的缺点，
就是当锁定一个范围的键值后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会対性能造成很大的危害。
17.5、如何锁定一行
select.. for update 在锁定某一行后，其他写操作会被阻塞，直到锁定的行被COMMIT。
mysql InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。
17.6、案例结论
InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。
17.7、行锁分析
mysql> SHOW STATUS LIKE 'innodb_row_lock%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 124150 |
| Innodb_row_lock_time_avg | 31037 |
| Innodb_row_lock_time_max | 51004 |
| Innodb_row_lock_waits | 4 |
+-------------------------------+--------+
5 rows in set (0.00 sec)
対各个状态量的说明如下：
- Innodb_row_lock_current_waits：当前正在等待锁定的数量。
- Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
- Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
- Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
- Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。
18.主从复制
18.1.复制基本原理
MySQL复制过程分为三步：
- Master将改变记录到二进制日志Binary Log。这些记录过程叫做二进制日志事件，Binary Log Events；
- Slave将Master的Binary Log Events拷贝到它的中继日志
- Slave重做中继日志的时间，将改变应用到自己的数据库中。MySQL复制是异步且串行化的
18.2、复制基本原则
- 每个Slave只有一个Master
- 每个Slave只能由唯一一个服务器ID
- 每个Master可以用多个Salve
18.3、一主一从配置
- 1、基本要求：Master和Slave的MySQL服务器版本一致且后台以服务运行。
# 创建mysql-slave1实例
docker run -p 3307:3306 --name mysql-slave1 \
-v /root/mysql-slave1/log:/var/log/mysql \
-v /root/mysql-slave1/data:/var/lib/mysql \
-v /root/mysql-slave1/conf:/etc/mysql \
-e MYSQL_ROOT_PASSWORD=333 \
-d mysql:5.7
- 2、主从配置都是配在[mysqld]节点下，都是小写
# Master配置
[mysqld]
server-id=1 # 必须
log-bin=/var/lib/mysql/mysql-bin # 必须
read-only=0
binlog-ignore-db=mysql
# Slave配置
[mysqld]
server-id=2 # 必须
log-bin=/var/lib/mysql/mysql-bin
3、Master配置
# 1、GRANT REPLICATION SLAVE ON *.* TO 'username'@'从机IP地址' IDENTIFIED BY 'password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'172.18.0.3' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)
# 2、刷新命令
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
# 3、记录下File和Position
# 每次配从机的时候都要SHOW MASTER STATUS;查看最新的File和Position
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 602 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4、Slave从机配置
CHANGE MASTER TO MASTER_HOST='172.18.0.4',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.File的编号',
MASTER_LOG_POS=Position的最新值;
# 1、使用用户名密码登录进Master
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
-> MASTER_USER='zhangsan',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=602;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
# 2、开启Slave从机的复制
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 3、查看Slave状态
# Slave_IO_Running 和 Slave_SQL_Running 必须同时为Yes 说明主从复制配置成功！
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event # Slave待命状态
Master_Host: 172.18.0.4
Master_User: zhangsan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 602
Relay_Log_File: b030ad25d5fe-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 602
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
- 5、测试
# Master创建数据库
mysql> create database test_replication;
Query OK, 1 row affected (0.01 sec)
# Slave查询数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_replication |
+--------------------+
5 rows in set (0.00 sec)
- 6、停止服务
# 1、停止Slave
mysql> STOP SLAVE;
Query OK, 0 rows affected (0.00 sec)
# 2、重新配置主从
# MASTER_LOG_FILE 和 MASTER_LOG_POS一定要根据最新的数据来配
mysql> CHANGE MASTER TO MASTER_HOST='172.18.0.4',
-> MASTER_USER='zhangsan',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='mysql-bin.000001',
-> MASTER_LOG_POS=797;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.18.0.4
Master_User: zhangsan
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 797
Relay_Log_File: b030ad25d5fe-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 797
Relay_Log_Space: 534
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bd047557-b20c-11ea-9961-0242ac120002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
