Mysql学习笔记（全），2024年最新分享PDF高清版

最新推荐文章于 2024-05-07 09:37:25 发布

2401_84170414

最新推荐文章于 2024-05-07 09:37:25 发布

阅读量1k

点赞数 14

分类专栏：程序员文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/2401_84170414/article/details/137629459

版权

程序员专栏收录该内容

172 篇文章 0 订阅

订阅专栏

id 相同，执行顺序由上至下
id不同，如果是子查询，id序号递增，id越大优先级越高
id相同不同，同时存在

select_type

SIMPLE 简单查询
PRIMARY 主查询（最外层的查询）
SUBQUERY 子查询
DERIUED 某个查询的子查询的临时表
UNION 联合查询
UNION RESULT 联合查询结果

type：：

type显示的是访问类型排列，是较为重要的一个指标

从最好到最差依次是：

system > const > eq_ref> ref > range > index > ALL;

一般来说，得保证查询至少达到range级别，最好ref

----------------------------------------------type类型-------------------------------------------------------

system:表只有一行记录（等于系统表）这是const类型的特列一般不会出现，可忽略不计
const：表示通过索引一次就查询到了，const用来比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，Mysql就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，表中只有一条记录与之匹配，常用于主键或唯一索引扫描（两个表是多对一或者一对一的关系，被连接的表是一的情况下，他的查询是eq_ref）
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回匹配某个单独值的所有行，然而他可能会找到多个复合条件的行，属于查找和扫描的结合体
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般where语句里出现了betweent，<,>,in等的查询，这种范围扫描索引比全表扫描好
index：index与ALL的区别，index只遍历索引树，索引文件通常比数据文件小
ALL：全表扫描

----------------------------------------------type类型-------------------------------------------------------

possible_keys：显示可能应用的的索引（理论上）

key：实际使用的索引，查询中若使用了覆盖索引，则该索引仅仅出现在key中

**key_len：**表示索引中使用的字节数，在不损失精度的情况下越短越好，kenlen显示的值为索引字段的最大可能长度，并非实际使用长度，kenlen是根据表定义计算而得，而不是通过表内检索出的

key_len长度：13是因为char(4)*utf8(3)+允许为null(1)=13

**ref：**显示索引的哪一列被使用了，如果可能的话是一个常数，哪些列或常量被用于查找索引列上的值

rows：根据表统计信息及索引选用情况，大致计算出找到所需的记录所需要读取的行数

没建立索引时查询t1 t2表 t1表对应t2表的id t2表 col1的值要为’ac’

对于Id这个字段t1表对t2表相当于 一对多

t1表的type为 eq_ref代表唯一性索引扫描，表中只有一条记录与之匹配，t2表对应t1的这个id对应的col值只有一个，根据t2表的主键id索引查询，t1表读取了一行，t2表读取了640行

建立索引后

t1读取一行，t2读取142行，ref非唯一性索引扫描，返回匹配某个单独值的所有行，返回t2对应id的col所有行，而t1对应id的col只有一行，所以type为eq_ref

Extra

包含不适合在其他列展现但十分重要的信息

\G ：竖直显示排序

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成排序的操作称为文件排序未被方框框住的图建立了复合索引，但是直接使用col3进行排序导致空中楼阁，mysql不得已只能进行filesoft

Using temporary：使用了临时表保存中间中间结果，MySQL在对查询结果排序时使用临时表。常见于order by排序和group by分组上表中建立了复合索引 col1_col2 但是直接通过col2进行分组导致了mysql不得已只能进行filesoft和建立临时表
using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，如果同时出现using where 表示索引被用来执行索引键值的查找，没有usingwhere表示索引用来读取数据而非执行查找动作
using where 表示使用了 where过滤
using join buffer 私用了链接缓存
impossible buffer where子句的值总是false 不能用来获取任何元组
select tables optimized away 在没有group by子句的情况下，基于索引优化min/max操作，或者对myisam存储引擎执行count(*)操作，不必等到执行操作进行，查询执行计划生成的阶段即完成优化
distinct 优化distinct操作，在找到第一匹配的元组后立即停止查找同样值的操作

案例

索引优化

单表优化

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;

mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

±—±----------+

| id | author_id |

±—±----------+

| 3 | 1 |

±—±----------+

1 row in set (0.00 sec)

mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc li

imit 1;

±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------+

| 1 | SIMPLE | article | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using filesort |

±—±------------±--------±-----------±-----±--------------±-----±--------±-----±-----±---------±----------------------------+

1 row in set, 1 warning (0.00 sec)

可以看出虽然查询出来了但是 type是all，Extra里面出现了using filesort证明查询效率很低

需要优化

建立索引

create index idx_article_ccv on article(category_id,comments,views);

查询

mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

±—±------------±--------±-----------±------±----------------±----------------±--------±-----±-----±---------±--------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±--------±-----------±------±----------------±----------------±--------±-----±-----±---------±--------------------------------------+

| 1 | SIMPLE | article | NULL | range | inx_article_ccv | inx_article_ccv | 8 | NULL | 1 | 100.00 | Using index condition; Using filesort |

±—±------------±--------±-----------±------±----------------±----------------±--------±-----±-----±---------±--------------------------------------+

1 row in set, 1 warning (0.00 sec)

这里发现type 变为了 range 查询全表变为了范围查询优化了一点

但是 extra 仍然有 using filesort 证明 索引优化并不成功

所以我们删除索引

drop index idx_article_ccv on article;

建立新的索引，排除掉range

create index idx_article_cv on article(category_id,views);

mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

±—±------------±--------±-----------±-----±---------------±---------------±--------±------±-----±---------±------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±--------±-----------±-----±---------------±---------------±--------±------±-----±---------±------------+

| 1 | SIMPLE | article | NULL | ref | idx_article_cv | idx_article_cv | 4 | const | 2 | 33.33 | Using where |

±—±------------±--------±-----------±-----±---------------±---------------±--------±------±-----±---------±------------+

1 row in set, 1 warning (0.00 sec)

这时候会发现优化成功 type 变为了ref extra变为了 using where

在这次实验中我又加入了一次试验发现当建立索引时comments放在最后也是可行的

mysql> create index idx_article_cvc on article(category_id,views,comments);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

±—±------------±--------±-----------±-----±----------------±----------------±--------±------±-----±---------±------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±--------±-----------±-----±----------------±----------------±--------±------±-----±---------±------------+

| 1 | SIMPLE | article | NULL | ref | idx_article_cvc | idx_article_cvc | 4 | const | 2 | 33.33 | Using where |

±—±------------±--------±-----------±-----±----------------±----------------±--------±------±-----±---------±------------+

1 row in set, 1 warning (0.00 sec)

这里发现了 type仍然是ref，extra也是usingwhere，而只是把索引建立的位置换了一换，把范围查询的字段挪到了最后!!!

双表优化

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 book(card)VALUES(FLOOR(1+(RAND()*20)));

mysql> create index Y on book(card);

explain select * from book left join class on book.card=class.card;

±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

| 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index |

| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

±—±------------±------±-----------±------±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

2 rows in set, 1 warning (0.00 sec)

会发现并无多大区别还是全表查询这是因为俩表查询左连接把左表必须全查询 这时候只有对右表建立索引才有用

相反的右链接必须对左表建立索引才有用

对右表建立索引

create index Y on class;

explain select * from book left join class on book.card=class.card;

±—±------------±------±-----------±------±--------------±-----±--------±---------------±-----±---------±------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±------±--------------±-----±--------±---------------±-----±---------±------------+

| 1 | SIMPLE | book | NULL | index | NULL | Y | 4 | NULL | 20 | 100.00 | Using index |

| 1 | SIMPLE | class | NULL | ref | Y | Y | 4 | db01.book.card | 1 | 100.00 | Using index |

±—±------------±------±-----------±------±--------------±-----±--------±---------------±-----±---------±------------+

2 rows in set, 1 warning (0.00 sec)

会发现右表只查询了一次。。type为ref

三表优化

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)));

先删除所有索引

drop index Y on book;

drop index Y on class;

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |

| 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

| 1 | SIMPLE | phone | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (Block Nested Loop) |

±—±------------±------±-----------±-----±--------------±-----±--------±-----±-----±---------±---------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

建立索引

create index y on book(card);

create index z on phone(card);

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

±—±------------±------±-----------±-----±--------------±-----±--------±----------------±-----±---------±------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±-----±--------------±-----±--------±----------------±-----±---------±------------+

| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL |

| 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index |

| 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index |

±—±------------±------±-----------±-----±--------------±-----±--------±----------------±-----±---------±------------+

3 rows in set, 1 warning (0.00 sec)

会发现索引建立的非常成功。。 但是left join 最左表必须全部查询 建立索引

create index x on class(card);

explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

±—±------------±------±-----------±------±--------------±-----±--------±----------------±-----±---------±------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±------±-----------±------±--------------±-----±--------±----------------±-----±---------±------------+

| 1 | SIMPLE | class | NULL | index | NULL | x | 4 | NULL | 20 | 100.00 | Using index |

| 1 | SIMPLE | book | NULL | ref | y | y | 4 | db01.class.card | 1 | 100.00 | Using index |

| 1 | SIMPLE | phone | NULL | ref | z | z | 4 | db01.book.card | 1 | 100.00 | Using index |

±—±------------±------±-----------±------±--------------±-----±--------±----------------±-----±---------±------------+

3 rows in set, 1 warning (0.00 sec)

结果仍然一样

建立表

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`);

索引口诀

1.带头大哥不能死，中间兄弟不能断:当建立复合索引时，必须带上头索引，不能跳过中间索引直接使用后面的索引，使用后面的索引必须加上中间的索引（可以先使用后面的索引再使用中间的索引，但是不能直接使用后面的索引而跳过中间索引）（针对where）

可以从上图看出跳过name的都用不了索引

mysql> explain select * from staffs where name=‘july’;

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±------+

| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 100.00 | NULL |

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±------+

1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where name=‘july’ and pos=‘dev’;

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±----------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±----------------------+

| 1 | SIMPLE | staffs | NULL | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | 33.33 | Using index condition |

±—±------------±-------±-----------±-----±------------------------±------------------------±--------±------±-----±---------±----------------------+

1 row in set, 1 warning (0.00 sec)

可以从语句中看出跳过中间的索引后 key_len 不变证明第索引pos没有被用到

2.不能对索引列进行任何操作（计算，类型转换等等）

3.存储引擎不能使用索引中范围条件右边的列（索引列上少计算）

4.尽量使用覆盖索引，即是只访问索引的查询减少select *的用法

5.少使用（！=，<>,<,>） is not null ,is null;

6.like以 '%'开头会导致索引失效（使用覆盖索引课避免索引失效）覆盖索引：（建立的索引与查询的字段顺序数量尽量一致）

7.字符串不加单引号会导致索引失效（mysql会将字符串类型强制转换导致索引失效）

8.少用or，用它连接会失效

索引案例

假设index（a，b，c）

Y代表索引全部使用了 N全没使用

| where语句 | 索引是否被使用 |

| — | — |

| where a=3 and c=5 | (中间b断掉了)使用了a 没使用c |

| where a=3 and b=4 andc=5 | Y |

| where a=3 and c=5 and b=4 | Y这里mysql自动做了优化对语句排序 |

| where a=3 and b>4 and c=5 | a,b被使用 |

| where a=3 and b like ‘k%’ and c=5 | Y like后面常量开头索引全用 |

| where b=3 and c=4 | N |

| where a=3 and c>5 and b=4 | Y:mysql自动做了优化对语句排序范围c之后索引才会失效 |

| where b=3 and c=4 and a=2 | Y :mysql自动做了优化对语句排序 |

| where c=5 and b=4 and a=3 | Y :mysql自动做了优化对语句排序 |

假设index（a，b，c, d）

create table test03(

id int primary key not null auto_increment,

a int(10),

b int(10),

c int(10),

d int(10),

insert into test03(a,b,c,d) values (3,4,5,6);

create index idx_test03_abcd on test03(a,b,c,d);

| where a=3 and b>4 and c=5 | 使用了a和b ，b后面的索引全失效 |

| — | — |

| where a=3 and b=4 and d=6 order by c | 使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中 |

| where a=3 and b=4 order by c | 使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中 |

| where a=3 and b=4 order by d | 使用了a和b, 这里跳过c 会导致using filesort |

| where a=3 and d=6 order by b ,c | 使用了a, 排序用到了b,c索引 |

| where a=3 and d=6 order by c ,b | 使用了 a，会产生using filesort，因为跳过了b对c进行排序 |

| where a=3 and b=4 order by b ,c | Y 全使用 |

| where a=3 and b=4 and d=6 order by c , b | 使用了a，b，不会产生using filesort 因为在对c，b排序前对b进行了查询，查询时b已经确定了（常量），这样就没有跳过b对c进行排序了，而是相当于直接对c排序相当于第三格的查询语句 |

group by 更严重group by先分组再排序把order by换为 group by 甚至会产生using temporary，与order by差不多，但是更严重而且与group by产生的效果差不多就不做演示了

Order By 索引优化

| orderBy 条件 | Extra |

| — | — |

| where a>4 order by a | using where using index |

| where a>4 order by a,b | using where using index |

| where a>4 order by b | using where, using index ,using filesort（order by 后面带头大哥不在） |

| where a>4 order by b,a | using where, using index ,using filesort（order by 后面顺序） |

| where a=const order by b,c | 如果where使用索引的最左前缀定义为常量，则order by能使用索引 |

| where a=const and b=const order by c | where使用索引的最左前缀定义为常量，则order by能使用索引 |

| where a=const and b>3 order by b c | using where using index |

| order by a asc, b desc ,c desc | 排序不一致升降机 |

| | |

exsites

select a.* from A a where exists(select 1 from B b where a.id=b.id)

以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false. 它的查询过程类似于以下过程

List resultSet=[]; Array A=(select * from A)

for(int i=0;i<A.length;i++) { if(exists(A[i].id) { //执行select 1 from B b where b.id=a.id是否有记录返回 resultSet.add(A[i]); } } return resultSet;

当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行. 如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等. 如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果. 再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

Mysql慢查询日志命令

show VARIABLES like ‘%slow_query_log%’;

显示是否开启mysql慢查询日志

set global slow_query_log=0;

关闭mysql慢查询日志

set global slow_query_log=1;

开启mysql慢查询日志

show VARIABLES like ‘%long_query_time%’;

显示超过多长时间即为慢查询

set global long_quert_time=10;

修改慢查询时间为10秒，当查询语句时间超过10秒即为慢查询

show global status like ‘%Slow_queries%’;

显示一共有几条慢查询语句

[root@iZ0jlh1zn42cgftmrf6p6sZ data]# cat mysql-slow.log

linux查询慢sql

函数操作批量插入数据

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=GBK;

CREATE TABLE emp(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, #编号

enname 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=GBK;

show variables like ‘log_bin_trust_function_creators’;

set global log_bin_trust_function_creators=1;

创建函数：随机产生部门编号随机产生字符串

DELIMITER ** $* * 是因为 s ql 都是；进行结尾但是创建函数过程要多次使用；所以改变 s ql 执行结束的条件为输入$ ，相当于代替了分号’ ；’

//定义函数1

DELIMITER $$

CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)

BEGIN

DECLARE chars_set VARCHAR(100) DEFAULT ‘abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ’;

DECLARE return_str VARCHAR(255) DEFAULT ‘’;

DECLARE i INT DEFAULT 0;

WHILE i < n DO

SET return_str = CONCAT(return_str,SUBSTRING(chars_set,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 $$

//定义存储过程1

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, enname, job, mgr, hiredate, sal, comm, deptno) VALUES((start + i),rand_string(6),‘SALESMAN’,0001,CURDATE(),2000,400,rand_num());

UNTIL i = max_num

END REPEAT;

COMMIT;

END $$

//定义存储过程2

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 ;

call insert_dept(100,10);

call insert_emp(100001,500000);

show Profile分析sql

mysql> show variables like ‘profiling’;

±--------------±------+

| Variable_name | Value |

±--------------±------+

| profiling | OFF |

±--------------±------+

1 row in set (0.00 sec)

mysql> set profiling=on;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like ‘profiling’;

±--------------±------+

| Variable_name | Value |

±--------------±------+

| profiling | ON |

±--------------±------+

1 row in set (0.01 sec)

随便写几条插入语句‘

显示查询操作语句的速度

mysql> show profiles;

±---------±-----------±---------------------------------------------------------------+

| Query_ID | Duration | Query |

±---------±-----------±---------------------------------------------------------------+

| 1 | 0.00125325 | show variables like ‘profiling’ |

| 2 | 0.00018850 | select * from dept |

| 3 | 0.00016825 | select * from tb1_emp e inner join tbl_dept d on e.deptId=d.id |

| 4 | 0.00023900 | show tables |

| 5 | 0.00031125 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |

| 6 | 0.00024775 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |

| 7 | 0.00023725 | select * from tbl_emp e inner join tbl_dept d on e.deptId=d.id |

| 8 | 0.00023825 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id |

| 9 | 0.35058075 | select * from emp group by id%10 limit 15000 |

| 10 | 0.35542250 | select * from emp group by id%10 limit 15000 |

| 11 | 0.00024550 | select * from tbl_emp e left join tbl_dept d on e.deptId=d.id |

| 12 | 0.36441850 | select * from emp group by id%20 order by 5 |

±---------±-----------±---------------------------------------------------------------+

12 rows in set, 1 warning (0.00 sec)

显示查询过程 sql生命周期

mysql> show profile cpu,block io for query 3;

±---------------------±---------±---------±-----------±-------------±--------------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

±---------------------±---------±---------±-----------±-------------±--------------+

| starting | 0.000062 | 0.000040 | 0.000021 | 0 | 0 |

| checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 |

| checking permissions | 0.000015 | 0.000006 | 0.000003 | 0 | 0 |

| Opening tables | 0.000059 | 0.000039 | 0.000020 | 0 | 0 |

| query end | 0.000004 | 0.000002 | 0.000001 | 0 | 0 |

| closing tables | 0.000002 | 0.000001 | 0.000000 | 0 | 0 |

| freeing items | 0.000014 | 0.000010 | 0.000005 | 0 | 0 |

| cleaning up | 0.000009 | 0.000006 | 0.000003 | 0 | 0 |

±---------------------±---------±---------±-----------±-------------±--------------+

8 rows in set, 1 warning (0.00 sec)

mysql> show profile cpu,block io for query 12;

±---------------------±---------±---------±-----------±-------------±--------------+

| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |

±---------------------±---------±---------±-----------±-------------±--------------+

| starting | 0.000063 | 0.000042 | 0.000021 | 0 | 0 |

| checking permissions | 0.000006 | 0.000003 | 0.000002 | 0 | 0 |

| Opening tables | 0.000013 | 0.000009 | 0.000004 | 0 | 0 |

| init | 0.000028 | 0.000017 | 0.000008 | 0 | 0 |

| System lock | 0.000007 | 0.000004 | 0.000002 | 0 | 0 |

| optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |

| statistics | 0.000014 | 0.000010 | 0.000004 | 0 | 0 |

| preparing | 0.000008 | 0.000005 | 0.000003 | 0 | 0 |

| Creating tmp table | 0.000028 | 0.000018 | 0.000009 | 0 | 0 |

| Sorting result | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |

| executing | 0.000002 | 0.000002 | 0.000001 | 0 | 0 |

| Sending data | 0.364132 | 0.360529 | 0.002426 | 0 | 0 |

| Creating sort index | 0.000053 | 0.000034 | 0.000017 | 0 | 0 |

| end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 |

| query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 |

| removing tmp table | 0.000005 | 0.000003 | 0.000002 | 0 | 0 |

| query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 |

| closing tables | 0.000006 | 0.000004 | 0.000002 | 0 | 0 |

| freeing items | 0.000023 | 0.000016 | 0.000007 | 0 | 0 |

| cleaning up | 0.000012 | 0.000007 | 0.000004 | 0 | 0 |

±---------------------±---------±---------±-----------±-------------±--------------+

20 rows in set, 1 warning (0.00 sec)

如果出现以上这四个中的任何一个就需要优化查询语句

全局查询日志

set global general_log=1;

set global log_output=‘TABLE’;

此后你编写的sql语句将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;

mysql> select * from mysql.general_log;

±---------------------------±--------------------------±----------±----------±-------------±--------------------------------+

| event_time | user_host | thread_id | server_id | command_type | argument |

±---------------------------±--------------------------±----------±----------±-------------±--------------------------------+

| 2021-12-06 11:53:53.457242 | root[root] @ localhost [] | 68 | 1 | Query | select * from mysql.general_log |

±---------------------------±--------------------------±----------±----------±-------------±--------------------------------+

1 row in set (0.00 sec)

Mysql锁

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁）：当前写操作没有完成时，它会阻断其他写锁和读锁

行锁：偏向InnoDB引擎，开销大，加锁慢，会出现死锁：锁定粒度最小，发生锁冲突的概率最低，并发量高

表锁：偏向myisam引擎，开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低

在下面进行表锁的测试

use big_data;

create table mylock (

id int not null primary key auto_increment,

name varchar(20) default ‘’

) 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 mylock read,book write;## 读锁锁mylock 写锁锁book

show open tables; ##显示哪些表被加锁了

unlock tables;##取消锁

表锁：读锁

##添加读锁后不可修改

mysql> lock table mylock read;##1

Query OK, 0 rows affected (0.00 sec)

mysql> select * from mylock;##1

±—±-----+

| id | name |

±—±-----+

| 1 | a |

| 2 | b |

| 3 | c |

| 4 | d |

| 5 | e |

±—±-----+

5 rows in set (0.00 sec)

mysql> update mylock set name=‘a2’ where id=1; ##1

ERROR 1099 (HY000): Table ‘mylock’ was locked with a READ lock and can’t be updated

##改不了当前读锁锁住的表

##读不了其他表

mysql> select * from book;##1

ERROR 1100 (HY000): Table ‘book’ was not locked with LOCK TABLES

为了区分两个命令 把1当作原有的mysql命令终端上的操作，2当作新建的mysql终端

新建一个mysql终端命令操作

##新建一个mysql终端命令操作

mysql> update mylock set name=‘a3’ where id=1; ##2

发现会出现阻塞操作

在原有的mysql命令终端上取消锁

unlock tables;##1

Query OK, 1 row affected (2 min 1.46 sec) ##2

Rows matched: 1 Changed: 1 Warnings: 0 ##2

会发现阻塞了两分钟多

总结：当读锁锁表mylock之后：**1.查询操作：**当前client（终端命令操作1）可以进行查询表mylock

其他client（终端命令操作2）也可以查询表mylock 2.DML操作（增删改）当前client会失效报错 ERROR 1099 (HY000): Table ‘mylock’ was locked with a READ lock and can’t be updated 其他client进行DML操作会让mysql陷入阻塞状态直到当前session释放锁

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录大纲截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且后续会持续更新

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）

笔者福利

以下是小编自己针对马上即将到来的金九银十准备的一套“面试宝典”，不管是技术还是HR的问题都有针对性的回答。

有了这个，面试踩雷？不存在的！

回馈粉丝，诚意满满！！！

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！

atermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA6L-b5Ye75Zyw5bCP55m9,size_9,color_FFFFFF,t_70,g_se,x_16)

新建一个mysql终端命令操作

##新建一个mysql终端命令操作

mysql> update mylock set name=‘a3’ where id=1; ##2

发现会出现阻塞操作

在原有的mysql命令终端上取消锁

unlock tables;##1

Query OK, 1 row affected (2 min 1.46 sec) ##2

Rows matched: 1 Changed: 1 Warnings: 0 ##2

会发现阻塞了两分钟多

总结：当读锁锁表mylock之后：**1.查询操作：**当前client（终端命令操作1）可以进行查询表mylock

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
[外链图片转存中…(img-KwII1pyX-1712802197459)]
[外链图片转存中…(img-pUGjqqiI-1712802197460)]
[外链图片转存中…(img-aYCQrAyk-1712802197460)]
[外链图片转存中…(img-iICoZpBP-1712802197460)]
[外链图片转存中…(img-IploaKN0-1712802197461)]
[外链图片转存中…(img-dFwHA2O3-1712802197461)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

如果你觉得这些内容对你有帮助，可以添加V获取：vip1024b （备注Java）
[外链图片转存中…(img-bZPmHacl-1712802197461)]

笔者福利

以下是小编自己针对马上即将到来的金九银十准备的一套“面试宝典”，不管是技术还是HR的问题都有针对性的回答。

有了这个，面试踩雷？不存在的！

回馈粉丝，诚意满满！！！

[外链图片转存中…(img-1eh3UZfY-1712802197462)]
[外链图片转存中…(img-vcJdVuIh-1712802197462)]
[外链图片转存中…(img-ZsaUklZh-1712802197462)]
[外链图片转存中…(img-mQ65tGvs-1712802197463)]

一个人可以走的很快，但一群人才能走的更远。不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎扫码加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！
[外链图片转存中…(img-1PDhYa1I-1712802197463)]