- 生产过程中优化的过程
- 观察，至少跑一天，看看生产的慢SQL情况
- 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
- Explain+慢SQL分析
- show profile
- 运维经理 or DBA，进行SQL数据库服务器的参数调优
- 目标
- 慢查询的开启并捕获
- explain+慢SQL分析
- show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优### 索引优化 （*）
索引分析
单表
- 建表SQL
+ 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql复制代码//建表
CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL,
`category_id` INT(10) UNSIGNED NOT NULL,
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL
);
//插入数据
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
//查看表数据
SELECT * FROM article;
- 案例分析
+ 查询 category\_id 为1 且 comments 大于 1 的情况下,views 最多的 article\_id
- 1
mysql复制代码SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
- Explain分析
结论：很显然，type是ALL，即最坏的情况，Rxtra里还出现了Using filesort，也是最坏的情况，优化是必须的
- 查看文章表已有的索引
1
mysql复制代码show index from article;
- 开始优化
1. 第一次优化：创建复合索引
1
mysql复制代码create index idx_article_ccv on article(category_id,comments,views);
第二次执行Explain
结论：
type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。
但是我们已经建立了索引,为啥没用呢?
这是因为按照 BTree 索引的工作原理,先排序 category\_id,如果遇到相同的 category\_id 则再排序 comments,如果遇到相同的 comments 则再排序 views，当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range)，MySQL 无法利用索引再对后面的 views 部分进行检索,**即 range 类型查询字段后面的索引无效**
2. 第二次优化：删除第一次优化建立的索引，重建索引
1
2
3
4
5
mysql复制代码//删除索引
DROP INDEX idx_article_ccv ON article;
//重新创建索引
#ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);
第三次执行Explain
- 结论：可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想两表(关联查询)
- 建表SQL
+ 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
mysql复制代码
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`)
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
- 案例分析
+ 1
mysql复制代码Explain select * from class c left join book b on c.card=b.card;
第一次执行Explain
+ 第一次优化 ，book表（右表）card字段 添加索引
1
mysql复制代码ALTER TABLE `book` ADD INDEX Y (`card`);
加索引后，第二次执行Explain
结论：可以看到第二行的 type 变为了 ref,rows 也变成了**优化比较明显**
+ 第二次优化，删除book表的索引，在class表（左表）的card字段，创建索引
1
2
3
mysql复制代码drop index Y on book;
ALTER TABLE class ADD INDEX X (`card`);
show index from class;
第三次执行Explain
优化效果不明显
+ **结论**：由左连接特性决定的，LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以**右边是我们的关键点,一定需要建立索引**；同理右连接，左边表一定要建立索引三表
- 建表SQL
+ 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
mysql复制代码CREATE TABLE IF NOT EXISTS `phone` (
`phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`phoneid`)
)ENGINE=INNODB;
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO phone(card) VALUES(FLOOR(1 + (RAND() * 20)));
- 案例分析
+ 1
mysql复制代码Explain select * from class inner join book on class.card=book.card inner join phone on book.card=phone.card;
三表关联，第一次Explain
+ 跟phone表和book表的 card字段 创建索引
1
2
mysql复制代码ALTER TABLE phone ADD INDEX Z (`card`);
ALTER TABLE book ADD INDEX Y (`card`);
创建完索引后，第二次Explain
优化明显，后两行的type都是ref且总的必须检查的记录数rows优化很好，效果不错。因此**索引最好设置在需要经常查询的字段中**
+ 结论：join语句的优化
- 尽可能减少Join语句中的NestedLoop的循环总次数：**永远用小的结果集驱动大的结果集**
- **优先**优化NestedLoop的**内层循环**
- **保证Join语句中被驱动表上Join条件字段已经被索引**
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，不要太吝惜**JoinBuffe**r的设置索引失效（应该避免）
- 建表SQL
+ 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql复制代码CREATE TABLE IF NOT EXISTS `staffs` (
`id` INT(10) Primary key AUTO_INCREMENT,
name varchar(24) not null default ' ' comment '姓名',
age INT not NULL default 0 comment '年龄',
pos VARCHAR(20) not null default ' ' comment '职位',
add_time TimeStamp not null default current_timestamp comment '入职时间'
)charset utf8 comment '员工记录表';
INSERT INTO staffs(NAME,age,pos,add_time)values('z3',22,'manager',now());
INSERT INTO staffs(NAME,age,pos,add_time)values('july',23,'dev',now());
INSERT INTO staffs(NAME,age,pos,add_time)values('2000',23,'dev',now());
select * from staffs;
//创建复合索引
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);
案例（索引失效）
- 全值匹配最喜欢看到
* 1
mysql复制代码 Explain Select * from staffs where name='july' and age=25 and pos='manager';
- 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
* 当使用**覆盖索引**的方式时，(select name,age,pos from staffs where age=10 (后面没有其他没有索引的字段条件))，即使不是以 name 开头，也会使用 idx\_nameAgePos 索引
* 如果中间有跳过的列 name、pos,则只会**部分使用**索引
* 索引 idx\_staffs\_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。全值匹配表示 按顺序匹配的
1
2
3
4
5
6
7
8
mysql复制代码Explain select * from staffs where name='july';
Explain select * from staffs where name='july' AND age=23;
Explain select * from staffs where name='july' AND age=23 AND pos='dev';
```
* 改变查询语句
mysql复制代码Explain select * from staffs where age=23 AND pos='dev';
Explain select * from staffs where pos='dev';
1
2
3
4
5
6
7
8
9
10
11
未遵循最佳左前缀法则，导致了索引失效
3. 不要在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
* 索引列 name 上做了函数操作
4. 存储引擎不能使用索引中范围条件右边的列，**范围之后全失效**
*
mysql复制代码Explain Select * from staffs where name='july' and age=25 and pos='manager';
Explain Select * from staffs where name='july' and age>25 and pos='manager';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
* 范围 若有索引则能使用到索引，**范围条件右边的索引会失效**(范围条件右边与范围条件使用的同一个组合索引，右边的才会失效。若是不同索引则不会失效)
5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select \*
6. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
7. is not null 也无法使用索引,但是is null是可以使用索引的
8. like以通配符**开头**（’%abc‘）mysql索引失效会变成全表扫描的操作
* % 写在右边可以避免索引失效
* 问题：解决like ‘%字符串%’时索引不被使用的方法？？------**覆盖索引**（完全重合或包含，但不能超过）
+ **覆盖索引**：建的索引和查询的字段，最好完全一致或者包含，但查询字段不能超出索引列
+ 建表SQL
mysql复制代码CREATE TABLE `tbl_user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(20) DEFAULT NULL,
`age` INT(11) DEFAULT NULL,
email VARCHAR(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_user(NAME,age,email) VALUES('1aa1',21,'b@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('2aa2',222,'a@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('3aa3',265,'c@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('4aa4',21,'d@163.com');
INSERT INTO tbl_user(NAME,age,email) VALUES('aa',121,'e@163.com');
1
+ before index 未创建索引
mysql复制代码EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id,NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT NAME,age FROM tbl_user WHERE NAME LIKE '%aa%';
1
2
3
+ 创建索引后，再观察变化（使用**覆盖索引**来解决like 导致索引失效的问题）
mysql复制代码CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);
#DROP INDEX idx_user_nameAge ON tbl_user
EXPLAIN SELECT name,age FROM tbl_user WHERE NAME like '%aa%';
EXPLAIN SELECT name FROM tbl_user WHERE NAME like '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME like '%aa%';
EXPLAIN SELECT id,name FROM tbl_user WHERE NAME like '%aa%';
EXPLAIN SELECT id,name,age FROM tbl_user WHERE NAME like '%aa%';
EXPLAIN SELECT name,age FROM tbl_user WHERE NAME like '%aa%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
完全一致或者包含的情况，成功使用覆盖索引（match匹配）
查询字段，不一致或者超出建立的索引列
9. 字符串不加单引号索引失效
* mysql 优化分析，会将int类型的777 **自动转化**为String类型，但违背了不能再索引列上进行手动或自动的转换（索引失效--案例3），导致索引失效
* 
10. 少用or，用它来连接时会索引失效
* 
11. 小总结
* 
##### 面试题讲解（\*）
* 题目SQL
+
mysql复制代码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','b2','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');
select * from test03;
1
2
3
+
mysql复制代码//创建复合索引
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
show index from test03;
1
2
3
4
5
6
+ 问题：创建了复合索引`idx_test03_c1234` ,根据以下SQL分析索引使用情况？
-
mysql复制代码#基础
explain select * from test03 where c1='a1';
explain select * from test03 where c1='a1' and c2='a2';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
1
2
3
1.基础使用
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
1
2
3
2.效果与 c1、c2、c3、c4按顺序使用一样，mysql底层优化器会自动优化语句，尽量保持顺序一致，可避免底层做一次翻译
-
mysql复制代码 explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
1
2
3
3.索引只用到部分c1、c2、c3（只用来排序，无法查找）。范围之后全失效，c4完全没有用到
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
1
2
3
4.同理2，mysql底层优化器会自动调整语句顺序，因此索引c1、2、3、4全起效
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
1
2
3
5.c3作用在排序而不是查找（因此没有统计到ref） c1、c2、c3
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' order by c3;
1
2
3
6.同理5 c1、c2、c3
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' order by c4;
1
2
3
7.出现filesort 文件排序 c1、c2
-
mysql复制代码explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;
1
2
8.1只用c1一个字段索引查找，但是c2、c3用于排序,无filesort
mysql复制代码explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
1
2
3
8.2出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2 颠倒了
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
1
2
3
9.用c1、c2两个字段索引，但是c2、c3用于排序,无filesort
-
mysql复制代码explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
1
2
3
4
5
6
10.本例对比8.2 多了c2常量（无需排序），不会导致filesort
-
mysql复制代码explain select c2,c3 from test03 where c1='a1' and c4='a4' group by c2,c3;
explain select c2,c3 from test03 where c1='a1' and c4='a4' group by c3,c2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
11.**group by 分组之前必排序**
* > 定值（常量）、范围（范围之后皆失效）还是排序（**索引包含查找排序两部分**），一般order by是给个范围
* group by 基本上是需要进行排序，会有临时表产生
#### 一般性建议
* 对于单键索引，尽量选择针对当前query过滤性更好的索引
* 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前（左）越好
* 在选择组合索引的时候，尽量选择能够包含当前query中的where字句更多字段的索引
* 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查看截取分析
------
* 生产过程中优化的过程
+ 观察，至少跑一天，看看生产的慢SQL情况
+ 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
+ Explain+慢SQL分析
+ show profile
+ 运维经理 or DBA，进行SQL数据库服务器的参数调优
* 总结
+ 慢查询的开启并捕获
+ explain+慢SQL分析
+ show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
+ SQL数据库服务器的参数调优
### 查询优化
#### 永远小表驱动大表（子查询）
* 案例
+ 优化原则：小表驱动大表，即小的数据集驱动大的数据集
- 原理 `in`
mysql复制代码select * from A where id in(select id From B);
##等价于(嵌套循环)
for select id from B
for select * from A where A.id=B.id
1
2
- 当B表的数据集必须小于A表的数据集时，用`in`优于`exists`
- 原理 `exists`
mysql复制代码select ...from table where exists(subQuery)
1
2
3
4
5
6
7
8
9
该语法可以理解为：**将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果是否得以保留**
- 提示：
1. Exists(subquery)只返回TRUE或FALSE，因此子查询中的SELECT \* 也可以是SELECT 1 或其他，官方说法是实际执行时会忽略掉SELECT清单，因此没有区别
2. Exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际校验以确定是否有效率问题
3. Exists子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要计提问题具体分析
- 当A表的数据集小于B表的数据集时，用`exists`优于`in`
mysql复制代码select * from A where exists (select 1 from B where B.id=A.id)
##等价于
for select * from A
for select * from B where B.id=A.id
1
2
3
4
5
6
7
8
9
10
- 注意：A表与B表的ID字段应建立索引
#### ORDER BY 关键字优化
* ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
+ 建表SQL
mysql复制代码
CREATE TABLE tblA(
id int primary key not null auto_increment,
age INT,
birth TIMESTAMP NOT NULL,
name varchar(200)
);
INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');
CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);
SELECT * FROM tblA;
1
2
3
+ Case
mysql复制代码Explain Select * From tblA where age>20 order by age;
Explain Select * From tblA where age>20 order by age,birth;
#是否产生filesort
Explain Select * From tblA where age>20 order by birth;
Explain Select * From tblA where age>20 order by birth,age;
1
2
mysql复制代码explain select * From tblA order by birth;
explain select * From tblA Where birth >'2020-9-09 00:00:00' order by birth;
explain select * From tblA Where birth >'2020-9-09 00:00:00' order by age;
explain select * From tblA order by age ASC,birth DESC; #mysql默认升序
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
+ MySQL支持两种方式排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序，FileSort方式效率较低
+ ORDER BY满足两种情况，会使用Index方式排序：
- ORDER BY语句使用索引最左前列
- 使用WHERE子句与ORDER BY子句条件列组合满足索引最左前列
* 尽可能在索引列上完成排序操作，遵循索引建的**最佳左前缀**
* 如果不在索引列上，filesort有两种算法：
+ 双路排序
- MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
- 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
- 取一批数据，要对磁盘进行了**两次扫描**，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
+ 单路排序
- 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了
+ 结论及引申出的问题
- 由于单路是后出的，总体而言好过双路
- 但是用单路有问题： 在sort\_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort\_buffer的容量，导致每次只能取sort\_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort\_buffer容量大小，再排……从而多次I/O，本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失
* 优化策略
+ 增大sort\_buffer\_size参数的设置
- 用于单路排序的内存大小
+ 增大max\_length\_for\_sort\_data参数的设置
- 单次排序字段大小(单次排序请求)
+ 提高ORDER BY的速度
- Order by时select \* 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：
* 当Query的字段大小总和小于max\_length\_for\_sort\_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序
* 两种算法的数据都有可能超出sort\_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort\_buffer\_size
- 尝试提高 sort\_buffer\_size。不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
- 尝试提高 max\_length\_for\_sort\_data。提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort\_buffer\_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率
* 小总结（\*）
+ 为排序使用索引
- 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 // 丢失a索引
+ WHERE a = const ORDER BY c //丢失b索引
+ WHERE a = const ORDER BY a,d //d不是索引的一部分
#### GROUP BY 关键字优化
* GROUP BY 实质是先排序后进行分组，遵照索引建的最佳左前缀（其他大致同 ORDER BY）
* 当无法使用索引列，增大max\_length\_for\_sort\_data 参数的设置 ＋ 增大sort\_buffer\_size参数的设置
* WHERE高于HAVING，能写在WHERE限定的条件就不要去HAVING限定了
### 慢查询日志
#### 是什么
* MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中**响应时间超过阀值**的语句，具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中
* 具体指运行时间超过long\_query\_time值的SQL，则会被记录到慢查询日志中。long\_query\_time的**默认值为10**，意思是运行10秒以上的语句
* 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析
#### 怎么玩
##### 说明
* 默认情况下，MySQL数据库**没有开启慢查询日志**，需要我们手动来设置这个参数。当然，**如果不是调优需要的话，一般不建议启动该参数**，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
##### 查看是否开启及如何开启
* 默认
+
mysql复制代码SHOW VARIABLES LIKE '%slow_query_log%';
1
2
3
4
5
6
7
8
+ 默认情况下slow\_query\_log的值为OFF,表示慢查询日志是禁用的，可以通过设置slow\_query\_log的值来开启
* 开启
+
mysql复制代码set global slow_query_log=1;
1
2
3
4
5
6
7
8
+ 使用该命令开启了慢查询日志只对当前数据库生效，如果MySQL**重启后则会失效**
+ 如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
修改my.cnf文件，[mysqld]下增加或修改参数`slow_query_log`和`slow_query_log_file`后，重启mysql服务器
c复制代码slow_query_log=1
slow_query_log_file=/var/lib/mysql/touchair-slow.log
1
2
3
4
5
6
7
8
9
10
+ 关于慢查询的参数 slow\_query\_log\_file, 它指定慢查询日志文件的存放路径，**系统默认会给一个缺省的文件 host\_name-slow,log** （如果没有指定参数 slow\_query\_log\_file 的话）
##### 查看慢查询内容
* 这个是**由参数long\_query\_time控制**，默认情况下long\_query\_time的值为**10秒**；
+ 命令：
mysql复制代码SHOW VARIABLES LIKE 'long_query_time%';
1
2
3
4
5
6
7
8
9
假如运行时间正好等于long\_query\_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断**大于long\_query\_time，而非大于等于**
* 使用命令设置阙值超过3秒钟就是慢SQL
+
mysql复制代码set global long_query_time=3;
//再次查看
SHOW VARIABLES LIKE 'long_query_time%';
1
2
3
4
5
* 会发现查看 long\_query\_time 的值并没有改变？原因：
+ 需要重新连接或者新开一个会话才能看到修改值
+ 或修改查看命令
mysql复制代码SHOW global VARIABLES LIKE 'long_query_time%';
1
2
3
4
5
6
7
8
9
10
11
##### Case
* 记录慢SQL，并后续分析
+ 执行一条休眠4秒的SQL
mysql复制代码SELECT SLEEP(4);
1
2
3
4
+ 查看日志文件
前面配置的日志文件路径或者默认路径
shell复制代码cat /var/lib/mysql/localhost-slow.log
1
2
3
4
5
6
* 查询当前系统中有多少条慢查询记录
+
mysql复制代码show global status like '%Slow_queries%';
1
2
3
4
5
6
7
8
##### 配置版
* 【mysqld】下配置：
c复制代码slow_query_log=1;
slow_query_log_file=/var/lib/mysql/touchair-slow.log
long_query_time=3;
log_output=FILE
1 |
|
shell复制代码mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
1
+ 得到访问次数最多的10个SQL
shell复制代码mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
1
+ 得到安装时间排序的前10条里面含有左连接的查询语句
mysql复制代码mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log
1
+ 另外建议在使用这些命令时，结合 | 和 more使用，否则有可能出现爆屏情况
shell复制代码mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log | more
1
2
3
4
5
6
7
8
9
### 批量数据库脚本（模拟大批量数据）
* 往数据库表里插1000w条数据
1. 建表SQL
mysql复制代码# 新建库
create database bigData;
use bigData;
#1 建表dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=INNODB DEFAULT CHARSET=UTF8 ;
#2 建表emp
CREATE TABLE emp
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ;
1
2
3
4
5
2. 设置参数log\_bin\_trust\_function\_creators
+ 创建函数，假如报错：This function has none of DETERMINISTIC......，由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数
+
mysql复制代码show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;
1
2
3
4
5
6
7
8
9
10
11
+ 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
- windows下my.ini[mysqld]加上log\_bin\_trust\_function\_creators=1
- linux下 /etc/my.cnf下my.cnf[mysqld]加上log\_bin\_trust\_function\_creators=1
3. 创建函数，保证每条数据都不同
+ 随机产生字符串
mysql复制代码DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN ##方法开始
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
##声明一个 字符窜长度为 100 的变量 chars_str ,默认值
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));
##concat 连接函数 ，substring(a,index,length) 从index处开始截取
SET i = i + 1;
END WHILE;
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;
1
+ 随机产生部门编号
mysql复制代码#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
#假如要删除
#drop function rand_num;
1
2
3
4
4. 创建存储过程
+ 创建往emp表中插入数据的存储过程
mysql复制代码DELIMITER $$
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0 ；提高执行效率
SET autocommit = 0;
REPEAT ##重复
SET i = i + 1;
INSERT INTO emp(empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),FLOOR(1+RAND()*20000),FLOOR(1+RAND()*1000),rand_num());
UNTIL i = max_num ##直到 上面也是一个循环
END REPEAT; ##满足条件后结束循环
COMMIT; ##执行完成后一起提交
END $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
1
+ 创建往dept表中插入数据的存储过程
mysql复制代码#执行存储过程，往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 $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
1
2
3
4
5. 调用存储过程
+ dept
mysql复制代码DELIMITER ;
CALL insert_dept(100,10);
1
+ emp
mysql复制代码#执行存储过程，往emp表添加50万条数据
DELIMITER ; #将 结束标志换回 ;
CALL insert_emp(100001,500000);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
插入50w条数据，耗时约24s
查询50w条数据，耗时约0.67s
### Show Profile（生命周期）
#### 是什么
* 是mysql提供可以用来分析当前会话中语句执行的**资源消耗情况**。可以用于SQL的调优的测量
* [官网](https://dev.mysql.com/doc/refman/5.7/en/preface.html)
#### 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
#### 分析步骤
##### 1.是否支持，看看当前的mysql版本是否支持
* 默认关闭，使用前需要开启
* 查看状态
mysql复制代码SHOW VARIABLES LIKE ‘profiling’;
1 |
|
mysql复制代码set profiling=1;
1 |
|
mysql复制代码SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY,',''));
1
2
3
4
5
6
7
8
9
10
11
+ 
* select \* from emp group by id%20 order by 5;
+ 
##### 4.查看结果，show profiles
* 命令
mysql复制代码show profiles;
1 |
|
mysql复制代码show profile cpu,block io for query 17;
1 |
|
c复制代码#开启
general_log=1
记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
1 |
|
mysql复制代码set global general_log=1;
1 | * 全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中 |
mysql复制代码set global log_output=’TABLE’;
1 | * 此后 ，你所编写的sql语句，将会记录到mysql库里的general\_log表，可以用下面的命令查看 |
mysql复制代码select * from mysql.general_log;
1 |
|
mysql复制代码create table mylock(
id int not null primary key auto_increment,
name varchar(20)
)engine myisam;
insert into mylock(name) values(‘a’);
insert into mylock(name) values(‘b’);
insert into mylock(name) values(‘c’);
insert into mylock(name) values(‘d’);
insert into mylock(name) values(‘e’);
select * from mylock;
1 |
|
mysql复制代码lock table 表名字1 read(write),表名字2 read(write),其它;
1 | * 查看表上加过的锁 |
mysql复制代码show open tables;
1 |
|
mysql复制代码unlock tables;
1 | * 给`mylock`、`dept`表分别添加读锁和写锁 |
mysql复制代码lock table mylock read,dept write;
1 |
|
mysql复制代码lock table mylock write;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
| session1 | session2 |
| --- | --- |
| 当前session1对锁定的表的查询+更新+插入操作都可以执行image-20200910153707795 | 其他session对锁定的表的查询被阻塞，需等待锁被释放image-20200910153758366 |
| | 在锁表前，如果session2有数据缓存，锁表以后，在锁住的表不发生改变的情况下session2可以读出缓存数据，一旦数据发生改变，缓存将失效，操作将被阻塞住，最好使用不同的id进行测试 |
| session1释放锁image-20200910154022340 | session2获得锁，返回查询结果image-20200910154105524 |
##### 案例结论
* MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁
* MySQL的表级锁有两种模式：
+ 表共享读锁（Table Read Lock）
+ 表独占写锁（Table Write Lock）
| 锁类型 | 可否兼容 | 读操作 | 写操作 |
| --- | --- | --- | --- |
| 读锁 | 是 | 是 | 否 |
| 写锁 | 是 | 否 | 否 |
* 结论：
结合上表，所有对MyISAM表进行操作，会有以下情况：
+ 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作
+ 对MyISAM表的写操作（加写锁），会阻塞其他进程读同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作
+ 简而言之，就是读锁会阻塞写，但不阻塞读操作。而写操作则会把读和写都阻塞
##### 表锁分析
* 看看哪些表被加锁了
mysql复制代码show open tables;
1 | * 如何分析表锁定 |
mysql复制代码show status like ‘table%’;
1 |
|
mysql复制代码
create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;
insert into test_innodb_lock values(1,’b2’);
insert into test_innodb_lock values(3,’3’);
insert into test_innodb_lock values(4,’4000’);
insert into test_innodb_lock values(5,’5000’);
insert into test_innodb_lock values(6,’6000’);
insert into test_innodb_lock values(7,’7000’);
insert into test_innodb_lock values(8,’8000’);
insert into test_innodb_lock values(9,’9000’);
insert into test_innodb_lock values(1,’b1’);
create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);
select * from test_innodb_lock;
1 |
|
mysql复制代码set autocommit=0;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
+ 正常情况，各自**锁定各自的行**，互相不影响，一个2000另一个3000
* **无索引行锁升级为表锁**
+ 正常情况，各自锁定**各自的行**，**互相不影响**
+ 由于在column字段b上面建了索引，如果没有正常使用，会导致行锁变表锁
- 比如没加单引号导致索引失效，行锁变表锁
- 被阻塞，等待。只到Session\_1提交后才阻塞解除，完成更新
| session1 | session2 |
| --- | --- |
| 更新session1中的一条记录，未手动commit，故意写错b的类型image-20200911092824894 | |
| session1,commit提交image-20200911093141141 | 更新session2，阻塞等待锁释放image-20200911093043823 |
| | session2完成update |
* 间隙锁危害
+ 什么是间隙锁
- 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（GAP Lock）
+ 危害
- 因为Query执行过程中通过过范围查找的话，他会**锁定整个范围内所有的索引键值，即使这个键值并不存在**
- 间隙锁有一个比较**致命的弱点**，就是当锁定一个范围键值之后，即使某些**不存在**的键值**也会被无辜的锁定**，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
##### 面试题
* 常考如何锁定某一行
##### 案例结论
* Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了
* 但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差
##### 行锁分析
* 如何分析行锁定
+ 通过检查InnoDB\_row\_lock状态变量来分析系统上的行锁的争夺情况
mysql复制代码show status like 'innodb_row_lock%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
* 各个状态量说明
+ Innodb\_row\_lock\_current\_waits：当前正在等待锁定的数量
+ Innodb\_row\_lock\_time：从系统启动到现在锁定总时间长度
+ Innodb\_row\_lock\_time\_avg：每次等待所花平均时间
+ Innodb\_row\_lock\_time\_max：从系统启动到现在等待最常的一次所花的时间
+ Innodb\_row\_lock\_waits：系统启动后到现在总共等待的次数
* 对于这5个状态变量，**比较重要的主要是**：
+ **Innodb\_row\_lock\_time\_avg（等待平均时长）**
+ **Innodb\_row\_lock\_waits（等待总次数）**
+ **Innodb\_row\_lock\_time（等待总时长）**
+ **尤其是当等待次数很高**，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划
* **查询正在被锁阻塞的sql语句**
mysql复制代码SELECT * FROM information_schema.INNODB_TRX\G;
1 |
|
shell复制代码service mysqld restart
1
2
3
4
5
6
7
8
9
* 5.主机从机都关闭防火墙
+ windows手动关闭
+ 关闭虚拟机linux防火墙 service iptables stop
* 6.在Windows主机上建立帐户并授权slave
+ 授权命令
mysql复制代码GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';
1
mysql复制代码GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.83.133' IDENTIFIED BY '123456';
1
2
3
4
5
6
7
8
9
10
+ flush privileges; （刷新）
+ 查询master的状态
-
mysql复制代码show master status;
1
2
3
4
5
6
7
8
9
10
记下File 和 Position 的值
+ 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
* 7.在Linux从机上配置需要复制的主机
+ 从机命令（如果之前同步过，先停止（stop slave;）再次授权）
mysql复制代码CHANGE MASTER TO MASTER_HOST='192.168.1.8',
MASTER_USER='root',
MASTER_PASSWORD='123456',
#MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体值;
MASTER_LOG_FILE='mysqlbin.000002',MASTER_LOG_POS=154;
1
2
3
+ 启动从服务器复制功能
mysql复制代码start slave;
1
+ 查看slave状态
mysql复制代码show slave status\G;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
`Slave_IO_Running: Yes`
`Slave_SQL_Running:Yes`
两个参数都是Yes,说明主从配置成功！
#### 测试主从效果
* 主机新建库、新建表、insert记录，从机复制
mysql复制代码#建库
create database mydb77;
#建表
create table touchair(id int not null,name varchar(20));
#插入数据
insert into touchair valies(1,’a’);
insert into touchair values(2,’b’);
1 |
|
mysql复制代码use mydb77;
select * from touchair;
1
2
3
4
* 主从复制成功！
* 如何停止从服务复制功能
mysql复制代码stop slave;
**本文转载自:** [掘金](https://juejin.cn/post/6921495541897510919)
*[开发者博客 – 和开发相关的 这里全都有](https://dev.newban.cn/)*