【尚硅谷MySQL高级课程 个人笔记(含SQL)】

MySQL高级

索引优化分析

单值索引

create index 索引名 on 表名(字段);

复合索引

create index 索引名 on 表名(字段1,字段2,...);

P13

建表语句

CREATE TABLE `tbl_emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`deptId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`) ,
KEY `fk_dept_id`(`deptId`)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

CREATE TABLE `tbl_dept` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`deptName` varchar(30) DEFAULT NULL,
`locAdd` varchar(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8;

插入语句

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);

INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);

查询语句

SELECT * FROM tbl_emp;

SELECT * FROM tbl_dept;

SELECT * 
FROM tbl_emp a 
INNER JOIN tbl_dept b
ON a.deptId = b.id;

SELECT * 
FROM tbl_emp a 
LEFT JOIN tbl_dept b
ON a.deptId = b.id;

SELECT * 
FROM tbl_emp a 
RIGHT JOIN tbl_dept b
ON a.deptId = b.id;

SELECT * 
FROM tbl_emp a 
LEFT JOIN tbl_dept b
ON a.deptId = b.id
WHERE b.id IS NULL;

SELECT * 
FROM tbl_emp a 
RIGHT JOIN tbl_dept b
ON a.deptId = b.id
WHERE a.deptId IS NULL;

SELECT * 
FROM tbl_emp a 
FULL OUTER JOIN tbl_dept b
ON a.deptId = b.id;

SELECT * 
FROM tbl_emp a 
LEFT JOIN tbl_dept b
ON a.deptId = b.id
UNION
SELECT * 
FROM tbl_emp a 
RIGHT JOIN tbl_dept b
ON a.deptId = b.id;

SELECT * 
FROM tbl_emp a 
LEFT JOIN tbl_dept b
ON a.deptId = b.id
WHERE b.id IS NULL
UNION
SELECT * 
FROM tbl_emp a 
RIGHT JOIN tbl_dept b
ON a.deptId = b.id
WHERE a.deptId IS NULL;

P16

索引基本语法

创建

CREATE [UNIQUE] INDEX indexName ON tableName(columnName(length));

ALTER tableName ADD [UNIQUE] INDEX [indexName] ON (columnName(length));

删除

DROP INDEX [indexName] ON tableName;

查看

SHOW INDEX FROM tableName;

EXPLAIN

字段：id,select_type,table,type,possible_keys,key,key_len,ref,rows,extra

type好到差排列:system>const>eq_ref>ref>range>index>ALL

P31

建表、插入、查询init语句

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;

索引案例

SELECT id,author_id
FROM article
WHERE category_id = 1
AND comments > 1
ORDER BY views
DESC LIMIT 1;

CREATE INDEX idx_article_cv on article(category_id,views);

小结：复合索引中间有范围查询，范围查询会导致后面索引无法使用，建议建立索引时跳过范围字段。

P32

建表、插入init语句

CREATE TABLE IF NOT EXISTS `class`(
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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)));

案例

SELECT * FROM book INNER JOIN class on book.card = class.card;

SELECT * FROM book left JOIN class on book.card = class.card;

SELECT * FROM book right JOIN class on book.card = class.card;

CREATE INDEX Y ON class(card);

DROP INDEX Y ON class;

SHOW INDEX FROM class;

小结：左连接建右表索引，右连接建左表索引。

P33

建表、插入语句

CREATE TABLE IF NOT EXISTS `phone`(
`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)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)));
INSERT INTO phone(card)VALUES(FLOOR(1+(RAND()*20)));

案例

SELECT * FROM class 
LEFT JOIN book on class.card = book.card
LEFT JOIN phone on book.card = phone.card;

SELECT * FROM class 
INNER JOIN book on class.card = book.card
INNER JOIN phone on book.card = phone.card;

ALTER TABLE `book` ADD INDEX Y (`card`);

DROP INDEX Y ON book;

ALTER TABLE `phone` ADD INDEX Y (`card`);

DROP INDEX Y ON phone;

小结：索引最好设置在需要经常查询的字段中。尽可能减少Jion语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集“。优化NestedLoop的内层循环，保证Jion语句中的被驱动表上Jion条件字段已经被索引；当无法保证被驱动表的Jion字段被索引且内存资源充足的前提下，不要太吝啬JionBuffer的设置。

P34

建表、插入语句

CREATE TABLE staffs(
id INT 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());

案例

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);

SELECT * FROM staffs;

SHOW INDEX FROM staffs;

select * from staffs where name = 'July';

select * from staffs where name = 'July' and age = 23;

select * from staffs where name = 'July' and age = 23 and pos = 'dev';

select * from staffs where age = 23 and pos = 'dev';

select * from staffs where pos = 'dev';

select * from staffs where name = 'July' and pos = 'dev';

索引失效

1、全值匹配我最爱；

2、最佳左前缀法则；

3、不在索引列上做任何操作（计算、函数、类型转换（手动or自动）），会导致索引失效，转向全表扫描；

4、存储引擎不能使用索引中范围条件右边的列；

5、尽量使用覆盖索引（只访问索引列（查询列和索引列一致）），减少select *；

6、MySQL在使用不等于运算符（!=或<>）时，无法使用索引，会导致全表扫描；

7、is null和is not null，无法使用索引；

8、like若以通配符开头（’%aa’），则索引失效，全表扫描；***–解决方法：1、通配符放左边 2、使用覆盖索引(见P40)***

9、字符串不加引号导致索引失效（相当于使用了隐式的类型转换–见第3条）；

10、少用or，用它来连接条件时，索引失效。

小结：【优化总结口诀】
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写*；
不等空值还有or，索引失效要少用。

P40

建表、插入语句

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,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

案例

create index idx_user_nameAge on tbl_user(name,age);

SELECT name,age from tbl_user where name like '%aa%';
SELECT name from tbl_user where name like '%aa%';
SELECT id from tbl_user where name like '%aa%';
SELECT age from tbl_user where name like '%aa%';
SELECT id,name from tbl_user where name like '%aa%';
SELECT * from tbl_user where name like '%aa%';
SELECT id,name,age,email from tbl_user where name like '%aa%';

P42

建表、插入语句

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;

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

show index from test03;

面试题分析

-- 问题：我们创建了复合索引idx_test03_c1234，根据以下SQL分析索引的使用情况？
 
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)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
-- 2)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' and c3 = 'a3';
 
 
-- 3)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 > 'a3' and c4 = 'a4';
-- 4)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 > 'a4' and c3 = 'a3';
 
 
-- 5)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c4 = 'a4' order by c3;
-- c3作用在排序而不是查找
-- 6)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c3;
-- 7)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c4;
-- 出现了filesort
 
 
-- 8)
-- 8.1
explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c2,c3;
-- 只用c1一个字段索引，但c2、c3用于排序，无filesort
-- 8.2
explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c3,c2;
-- 出现了filesort，因为之前建的索引是1234，它没有按照顺序来，3 2 颠倒了
-- 9)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' order by c2,c3;
-- 10)
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c2,c3;
-- 用了c1、c2两个字段索引，但是c2、c3用于排序，无filesort
 
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c5 = 'a5' order by c3,c2;
-- 本例有常量c2的情况，和8.2对比
explain select * from test03 where c1 = 'a1' and c5 = 'a5' order by c3,c2;
-- filesort
 
 
-- 11)
explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c2,c3;
-- 12)
explain select * from test03 where c1 = 'a1' and c4 = 'a4' group by c3,c2;
-- temporary;filesort

查询截取分析

p46

建表、插入语句

create table tblA(
age int,
birth timestamp not null
);
 
insert into tblA(age,birth) values (22,now());
insert into tblA(age,birth) values (23,now());
insert into tblA(age,birth) values (24,now());
 
 
create index idx_A_ageBirth on tblA(age,birth);

案例

select * from tblA;

select * from tbla where age > 20 order by age;
select * from tbla where age > 20 order by age,birth;
select * from tbla where age > 20 order by birth;
select * from tbla where age > 20 order by birth,age;

select * from tbla order by birth;
select * from tbla where birth > '2016-01-28 00:00:00' order by birth;
select * from tbla where birth > '2016-01-28 00:00:00' order by age;
select * from tbla where birth > '2016-01-28 00:00:00' order by age asc,birth desc;

慢查询日志

-- 查询慢sql监控是否开启
show variables like '%slow_query_log%';
-- 查询慢sql定义时间
show VARIABLES like 'long_query_time%';
-- 模拟4秒钟的慢sql
select sleep(4);
-- 查看当前慢sql条数
show global status like 'Slow_queries%';

Linux端查看慢sql

得到返回记录最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/slow.log
注意：后面的路径是慢sql存放日志的路径

批量插入数据脚本

delimiter $$
create function rand_str(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str,substr(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;

# 用于随机产生部门编号
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i = floor(100 + rand()*10);
return i;
end $$
 
delimiter ;
 
 
# 删除语法
# drop funciton rand_num;

# 执行存储过程，往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_str(10),rand_str(8));
until i = max_num
end repeat;
commit;
end $$
 
delimiter ;


-- 执行存储过程，往emp表添加随机数据
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_str(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
 
delimiter ;

call insert_dept(100,10);

select * from dept;

call insert_emp(100001,500000);

select * from emp;

Show Profile

select * from emp;

show profiles;

show profile cpu,block io for query 3;

MySQL锁机制

P52\53

create table mylock(
id int not null auto_increment,
name varchar(20),
primary key(id)
)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;

-- 手动增加表锁
lock table tableName1 read(write),tableName2 read(write),others;

-- 查看表上加过的锁
show open tables;

-- 释放表锁
unlock tables;

-- 分析表锁定
show status like 'table%';
-- Table_locks_immediate: 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
-- Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），
-- 此值高则说明存在较严重的表级锁争用情况。

P54

-- 查看当前数据库的隔离级别
show variables like 'tx_isolation';

P54

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;

P59

-- 锁定某一行
begin;
-- 表明将RowName = value这行锁定
select * from tableName where RowName = value for update;
-- commit提交后锁定释放
commit;

P60

-- 分析行锁定
show status like 'innodb_row_lock%';

脑图感谢：https://github.com/a29hbGE/mysql.git
个人笔记md、阳哥PDF脑图、mmap脑图下载：https://download.csdn.net/download/ema7526/74000079