MySQL高级—尚硅谷—周阳—2018-03-28

张老师的分享

已于 2023-02-28 08:24:19 修改

阅读量1k

点赞数

分类专栏： # - 4.5：数据库文章标签： java

于 2022-04-09 17:20:02 首次发布

本文链接：https://blog.csdn.net/qq_43056248/article/details/124064266

版权

- 4.5：数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

视频链接

sql 优化 5 招：  （前 4 步，可拿下 99%）
    分析：观察，至少跑一天，看生产环境的 慢sql 情况。
    开启慢查询日志，设置阙值。比如超过 2 秒，就是慢sql，并将它抓取出来。
    explain + 慢 SQL 分析（出来八成了）
    进一步分析：show profile，查询 SQL 在 MYSQL 服务器里面的执行细节 和 生命周期情况。
    运维 or dba，进行 SQL 数据库服务器 参数调优。

面试：https://www.cnblogs.com/setalone/p/14851000.html
95. 你是如何监控你们的数据库的？你们的慢日志都是怎么查询的？
监控的工具有很多，例如zabbix，lepus，我这里用的是lepus

96. 你是否做过主从一致性校验，如果有，怎么做的，如果没有，你打算怎么做？
主从一致性校验有多种工具 例如checksum、mysqldiff、pt-table-checksum等

。不要说我觉得。

一.：Mysql 的架构介绍（1～9）

1：Mysql 简介：

1）概述：
在这里插入图片描述
2）高级 MySQL ：（完整的 mysql 优化，需要很深的功底，大公司甚至有专门的 DBA 维护。）
-1：Mysql 内核、Sql 优化工程师、Mysql 服务器优化、
-2：各种参数常量设定、查询语句优化、
-3：主从复制、软硬件升级、容灾备份、sql 编程等。

2：Mysql 的 Linux 安装：（安装、修改配置文件）

5.7 及以上安装：

。。。老韩 5.5 安装：起。。。。。。。。。。。。。。。。。。。。
1）下载：https://downloads.mysql.com/archives/community/
在这里插入图片描述
2）检查当前系统是否安装过 mysql：

3）安装 mysql 服务端：
-1：安装包放在：/opt 目录下：（出现问题：yum remove mysql-libs）
-2：安装：

rpm -ivh MySQL-server-5.5.48-1.linux2.6.x86_64.rpm

-- 提示
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h bogon password 'new-password'

在这里插入图片描述

4）安装 Mysql 客户端：
-1：安装包放在：/opt 目录下：
-2：安装：

rpm -ivh MySQL-client-5.5.48-1.linux2.6.x86_64.rpm

在这里插入图片描述

5）查看 Mysql 安装时，创建的 Mysql 用户和 Mysql 组：（top 命令）
在这里插入图片描述

6）Mysql 服务的（启动｜停止）：（start｜stop）
在这里插入图片描述

7）mysql 服务启动后，开始连接：
-1：首次连接成功：（没有输入密码）
在这里插入图片描述
-2：按照安装 Server 中的提示，修改登录密码：

/usr/bin/mysqladmin -u root password 123456

在这里插入图片描述

8）开机自启动 Mysql 服务：
在这里插入图片描述

9）修改配置文件位置：
-1：（Mysql 配置文件位置：/usr/share/mysql ）（此文件与调优有关）
-2：（拷贝进 /etc 所有配置的集散地）
在这里插入图片描述

10）设置外网可访问：
在这里插入图片描述
。。。。老韩 5.5 安装：结束。。。。。。。。。。。。。。。。。。。

11）修改字符集 & 数据存储路径：
-1：插入中文显示乱码：（建库、建表）
在这里插入图片描述
-2：查看字符编码：（默认客户端和服务端都使用了 latin1 ，所以会中文乱码）

	-- 字符串的编码与解码
    public static void main(String[] args) throws UnsupportedEncodingException {
        String str = URLEncoder.encode("😂", StandardCharsets.UTF_8.toString());
        System.out.println(str);

        String dStr = URLDecoder.decode("%F0%9F%98%82", StandardCharsets.UTF_8.toString());
        System.out.println(dStr);	// 😂
    }

show variables like 'character%';

show variables like '%char%';

在这里插入图片描述
-3：修改编码：（在 mysql 配置文件中，修改）

 17 [client]
 18 password        = 123456
 19 port            = 3306	# mysql 端口
 20 socket          = /var/lib/mysql/mysql.sock
 -- mysql 客户端连接mysql服务器时，指定客户端的编码规则
 -- 客户端 会忽略操作系统当前使用的字符集，
 	直接将 default-character-set 启动选项中指定的值，作为客户端默认的字符集。
 21 default-character-set=utf8	
--影响参数：character_set_client，character_set_connection 和character_set_results。
--注意：修改后无需重启数据库。
 
 	-- 可以控制：（以下四个变量，都是 SESSION 级别的）
 		-- 描述：服务器认为，请求是按照，该系统变量指定的字符集，进行编码的。
 		-- show variables like 'character_set_client'; == utf8; 

		-- 描述：服务器再处理请求时，会把请求字节序列从 character_set_client 
				转换为 character_set_connection。
 		-- show variables like 'character_set_connection'; == utf8
 		-- show variables like 'collation_connection'; == gbk_chinese_ci

		-- 描述：服务器采用，该系统变量，指定的字符集，对返回给客户端的字符串，进行编码。
 		-- show variables like 'character_set_results'; == utf8

 26 [mysqld]
 27 port            = 3306	# mysql 端口
    skip-grant-tables	-- 跳过密码检查（忘记密码时使用）
 28 character_set_server=utf8	# 设置编码
 29 character-set-client=utf8	# 设置编码 --（貌似可以不用）
 30 collation-server=utf8_general_ci	# 设置编码
-- character_set_server=utf8
-- 影响参数：character_set_server 和 character_set_database
-- 注意：修改后要重启数据库才能生效。

 # （注意，Linux 下，mysql 安装完默认：表名区分大小写，列名不区分大小写；0:区分；1:不区分）
 # lower_case_table_names=1
 
 # 设置最大连接数，默认为：151，mysql 服务器允许最大连接数：16348.
 #  max_connections=1000


139 [mysql]
140 no-auto-rehash
141 default-character-set=utf8

-4：重启 Mysql，还是乱码：需要重新创建数据库，重新创建表，插入中文呢数据，就不显示乱码了。

12）Mysql 的安装配置：
-1：windows：D:\devSoft\MySQLServer5.5\data
-2：Limux：默认路径：/var/lib/mysql。（查看：ps -ef | grep mysql）
在这里插入图片描述

3：Mysql 配置文件：（主要配置文件）

1）二进制日志（log-bin）（主要用于主从复制）
（配置文件中：配置文件存放位置）
在这里插入图片描述
2）错误日志（log-error）
-1：位置：（配置文件：my.cnf 中配置路径：mysql/data/mysqlerr.err ）
-2：默认关闭：
-3：记录严重的（警告 & 错误）信息，每次（启动 & 关闭）的详细信息等。

3）查询日志（log）（可用于慢查询分析）
-1：默认关闭：
-2：记录查询的 sql 语句，如果开启，会降低 mysql 整体性能。
-3：因为，记录日志也是需要消耗系统资源的。

4）数据文件：
-1：两系统：
a：Windows：D:\devSort\MysqlServer5.5\data 目录下，可以挑选很多库。
b：Linux：默认路径：（ /var/lib/mysql ）

-2：xx.frm 文件：（存放：表结构）（format）
-3：xx.myd 文件：（存放：表数据）（my data）
-4：xx.myi 文件：（存放：索引）（my index）

5）如何配置：（配置文件位置）
-1：windows：（ my.ini 文件）
-2：Linux：（ /etc/my.cnf 文件）

4：Mysql 逻辑架构介绍：

1）总体概览：
在这里插入图片描述

5： Mysql 存储引擎：（详见数据库基础）

1）查看命令：

-- 查看本 mysql ，现在已提供什么引擎：
show engines;

-- 查看当前默认存储引擎：
show variables like '%storage_engine%';

2）（InnoDB｜MyISAM｜Memory）：
在这里插入图片描述
-1：InnoDB：支持事务，支持主外键，支持行级锁（适合高并发）。（关注点为：事务）
-2：MyISAM：不支持事务，不支持主外键，支持表级锁（不适合高并发）。（添加速度快）
-3：Memory：数据存储在内存中【重启mysql服务，数据丢失，但是表结构还在】。执行速度快（没有 IO 读写），默认支持索引（hash 表）

3）阿里、淘宝用哪个：（存储引擎给换了）
在这里插入图片描述

二.（1）：索引优化分析：（10～19）

1：性能下降 SQL 慢：（执行时间长、等待时间长）

1）查询语句写得烂：各种连接、各种子查询，导致用不上索引。或者没建索引。

2）索引失效：（建了索引没用上）

mysql> select * from user where name = '张三';
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
+------+--------+

-- 创建单值索引
mysql> create index idx_user_name on user (name);

-- 创建复合索引
mysql> create index idx_user_idName on user (id, name);

3）关联查询太多 join：（设计缺陷 / 不得已的需求）

4）服务器调优及各个参数设置不合理：（缓冲、线程数等）

2：常见通用的 Join 查询：

1）SQL 执行顺序：
-1：手写：

SELECT DISTINCT *
FROM table01
         LEFT JOIN table2 ON
    ta = tb
WHERE a = 1
GROUP BY age
HAVING age = 1
ORDER BY age ASC
LIMIT 2,2;

-2：机读：（通过 mysql 优化器认为最优的方式，去运行）
在这里插入图片描述

2）Join 图：
在这里插入图片描述

3）建表 SQL：

-- 部门名称、楼层
create table tb1_dept(
	id int(11) primary key auto_increment,
	dept_name varchar(40) default null,
	ioc_add varchar(40) default null
)character set utf8 collate utf8_general_ci engine innodb;

-- 员工姓名、部门id
create table tb1_emp(
	id int(11) primary key auto_increment,
	name varchar(20) default null,
	dept_id int(11) default null
	-- foreign key (dept_id) references tb1_dept(id)
)character set utf8 collate utf8_general_ci engine innodb;

insert into tb1_dept (dept_name,ioc_add) values ('dept_name_a','11');
insert into tb1_dept (dept_name,ioc_add) values ('dept_name_b','12');
insert into tb1_dept (dept_name,ioc_add) values ('dept_name_c','13');
insert into tb1_dept (dept_name,ioc_add) values ('dept_name_d','14');
insert into tb1_dept (dept_name,ioc_add) values ('dept_name_e','15');

INSERT INTO tb1_emp (name, dept_id) VALUES('z1', 1);
INSERT INTO tb1_emp (name, dept_id) VALUES('z2', 1);
INSERT INTO tb1_emp (name, dept_id) VALUES('z3', 1);
INSERT INTO tb1_emp (name, dept_id) VALUES('a4', 2);
INSERT INTO tb1_emp (name, dept_id) VALUES('x5', 2);
INSERT INTO tb1_emp (name, dept_id) VALUES('a6', 3);
INSERT INTO tb1_emp (name, dept_id) VALUES('a7', 51);

在这里插入图片描述

4）7 种 join 测试：（查询以上两张表）
在这里插入图片描述
（mysql 不支持 full outer join，可以使用【 union｜union all 】替换）

3：索引简介：

1）是什么：
-1：MySQL 官方，对索引的定义为：索引（Index），是帮助 MySQL，高效获取数据的数据结构。
-2：索引的本质：索引是一种数据结构。
-3：索引的目的在于提高查询效率：可以类比字典。
-4：可以简单理解为：排好序的快速查找数据结构。（影响：排序，查找）
a：讲解：（二叉树）（BTREE 索引）（平衡二叉树）
在这里插入图片描述

b：结论：（数据一般不删除，只是修改状态）

-5：索引存储位置：
a：一般来说：索引本身也很大，不可能全部存储在内存中。
b：因此，索引往往以索引文件的形式，存储在磁盘上。
c：存放位置：（ /var/lib/mysql ）

-6：索引结构：（B+Tree ：多路平衡查找树）
在这里插入图片描述
1）Mysql 索引结构：
-1：BTree：（不用）

-2：为什么是 B+ 树：（在 B-树的基础上，把所有节点，放在叶子结点上，形成链表）

2）优势：
-1：通过索引的数据结构：提高数据检索的效率，降低数据库的 IO 成本。
-2：通过索引列：对数据进行排序，降低数据排序成本，降低了 CPU 的消耗。

3）劣势：
-1：实际上，索引也是一张表：
a：该表保存了主键与索引字段，并通过指针方式，指向实体表的记录。
b：所以，索引也是要占用空间的。

-2：虽然：索引大大提高了查询速度，同时也会降低表的更新速度：
a：如：对表进行 insert、update、delete。
b：因为：更新表时，Mysql 不仅要保存数据，还要维护索引文件；

-3：索引，只是提高查询效率的一个因素：
a：如果 Mysql 中有大数据量的表，就需要花时间研究，建立最优秀的索引，或优化查询。

4）mysql 索引分类：
（一张表的索引，最好不要超过 5 个）
（一个查询，同时只能加载一个索引去使用）
-1：单值索引：即：一个索引只包含单个列，一个表可以有多个单列索引。（复合索引优于单值索引）

-2：唯一索引：即：索引列的值必须唯一，但允许有空值；（例如：主键约束｜唯一约束）

-3：复合索引：即：一个索引包含多个列；

-4：基本语法：（加上 Unique 就是唯一索引）
在这里插入图片描述

5）mysql 索引结构：
-1：BTree 索引：（BTree 的 检索原理：下面）
在这里插入图片描述

-2：Hash 索引：
-3：full-text 全文索引：
-4：R-Tree 索引：

6）哪些情况，需要创建索引：
-1：主键自动建立唯一索引；
-2：频繁作为（where）查询条件的字段，应该创建索引；；where 条件里用不到的字段，不要创建索引。

-3：查询中与其他表关联的字段，外键关系建立索引；（外键字段创建索引）
-4：频繁更新的字段不适合创建索引；（因为每次更新，要修改数据的同时，还要维护索引）

-5：（单键｜组合）索引的选择问题？（在高并发下，倾向于组合索引）
-6：查询中，排序字段若通过索引去访问，将大大提高排序速度；
-7：查询中，统计或者分组的字段；（分组前，必排序）

7）哪些情况，不要创建索引：
-1：表记录太少：
-2：经常增删改的表：
在这里插入图片描述
-3：数据重复且分布平均的表字段：（索引的选择性越接近 1 ，效率越高）

二.（2）：索引优化分析 — 性能分析：（20～30）

1：Mysql Query Optimiter：

（mysql 自带的：查询优化器模块）
在这里插入图片描述

2：Mysql 常见瓶颈：

在这里插入图片描述

3：Explain 介绍：

4：Explain 各字段解释：

5：Explain 热身 case：

1）判断 SQL 执行循序：
在这里插入图片描述

二.（3）：索引优化分析 — 索引优化（31～43）

1：索引分析：

1）单表：
-1：建表 SQL：

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'),
       (4, 4, 4, 4, '4', '4');

在这里插入图片描述

-2：案例
a：查询：（结论：必须进行优化）

-- 查询 category_id = 1 且 comments > 1 的情况下的全部数据。
-- 并用 views 倒序排序。
explain
select a.id,
       a.author_id
from article a
where a.category_id = 1
  AND a.comments > 1
order by a.views DESC
limit 1;

+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | a     | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------+

-- 结论：很显然，type 为 ALL，即最坏的情况。
-- Extra 里 还出现了 Using filesort ，也是最坏的情况
-- 所以，必须进行优化

b：开始优化：（创建复合索引）

-- 创建 索引：
create index idx_article_ccv on article (category_id, comments, views);

-- 再次查询，显示结果如下（用到了索引）（但范围以后的索引会失效）
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys   | key             | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | a     | range | idx_article_ccv | idx_article_ccv | 8       | NULL |    1 | Using where; Using filesort |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

c：范围以后的索引，会失效：（条件尽量写等于）
在这里插入图片描述

-3：上面的索引：不太合适范围查询，因为出现了文件排序；故删除索引。

drop index idx_article_ccv on article;

-- 重新创建索引（跳过排序字段），再次查询
create index idx_article_cv on article (category_id, views);

在这里插入图片描述

2）两表：
-1：建表 SQL

create table if not exists class (
    id   int(10) 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)));

create table if not exists book (
    bookid int(10) primary key auto_increment,
    card   int(10) unsigned not null
);
-- 随机插入数据
insert into book(card) values (floor(1 + (rand() * 20)));
insert into book(card) values (floor(1 + (rand() * 20)));

在这里插入图片描述

-2：案例：（索引应该加在左表还是右表）
a：查询：（结论：type 为 ALL，必须进行优化）
在这里插入图片描述

b：为 book 标，添加索引优化：（book 为右表）（左连接，加右表）

create index idx_book_card on book (card);

在这里插入图片描述

c：为 class 表，添加索引优化：（book 为右表）
（观察到：左连接，索引加在左表上的话，type=index，rows=40，效果没有之前好）

drop index idx_book_card on book;
create index idx_class_card on class (card);

在这里插入图片描述
d：结论：左连接索引加在右表，右连接索引加在左表。（表的位置交换一下也可以）

3）三表：
-1：建表 SQL：

create table if not exists phone (
    phoneid int(10) primary key auto_increment,
    card    int(10) unsigned not null
);

INSERT INTO phone (card) VALUES (floor(1 + (rand() * 20)));
INSERT INTO phone (card) VALUES (floor(1 + (rand() * 20)));

在这里插入图片描述

-2：案例：
a：查询：（结论：type 为 ALL，必须进行优化）
在这里插入图片描述

b：为 book、phone 添加索引优化，再次查询。

create index idx_book_card on book (card);
create index idx_phone_card on phone (card);

在这里插入图片描述

c：结论：
（左连接建右表，右连接建左表，依然适用）
（永远是：小表驱动大表）（小表的 type 可以为 all，但是大表，必须用索引）
在这里插入图片描述

2：索引失效（建表 SQL）：

create table if not exists 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 '入职时间'
) character set utf8 comment '员工记录表';

INSERT INTO staffs (name, age, pos, add_time) VALUES ('z3', 22, 'manager', CURRENT_TIMESTAMP);
INSERT INTO staffs (name, age, pos, add_time) VALUES ('Jujy', 23, 'dev', CURRENT_TIMESTAMP);
INSERT INTO staffs (name, age, pos, add_time) VALUES ('2000', 23, 'dev', CURRENT_TIMESTAMP);

select * from staffs;

create index idx_staffs_nameAgePos on staffs (name, age, pos);

在这里插入图片描述

3：索引失效（案例）：

在这里插入图片描述
1）全值匹配我最爱：（全值匹配我最爱）
（索引：name、age、pos）（下面的搜索，精度越来高）

2）最佳左前缀法则：（带头大哥不能死，中间兄弟不能断）
在这里插入图片描述

3）不在索引列上做任何操作：（索引列上少计算）
（计算、函数、（自动 / 手动）类型转换），都会导致索引失效而转向全表扫描。
在这里插入图片描述

4）存储引擎，不能使用索引中，范围条件右边的列：（范围之后全失效）
（范围列用到，后面的失效）
（in、like、between and）（like 使用 % 开头，也是范围）
在这里插入图片描述

5）尽量使用覆盖索引：
-1：（只访问索引的查询（索引排序与查询列一致），减少 select * ）
-2：select * ：（不仅不能使用覆盖索引，还可能占用内存空间大了之后，把 sort_buffer 占满，由单路排序转变为双路排序）（总结详解）

-3：如何使用：
a：使用覆盖索引和不使用覆盖索引
在这里插入图片描述
b：只用一部分覆盖索引：（这个不区分先后顺序）

6）Mysql 在使用不等于（ != 或 <> ）的时候：（无法使用索引会导致全表扫描）
在这里插入图片描述

7）（ is null、is not null ）也无法使用索引：
（SQL 中尽量避免空值，写 default 值也可以）
在这里插入图片描述

8）like 以通配符开头（‘%abc…’）Mysql 索引失效，会变成全表扫描的操作：（百分 like 加右边）
（like 使用 % 开头，也是范围）（但是 like 以常量值开头，就不是范围，后面的字段依然可以使用索引）
-1：测试：使用 like ‘%abc’ ，索引会失效，转为全表扫描；
在这里插入图片描述

-2：问题：解决 Like ‘%abc%’ 时，索引不被使用的方法？
（使用覆盖索引查询字段：要不全部吻合，要不沾边就行，但不能超过 ）

-- 建表 sql
create table tbl_user(
    id    int(11) primary key auto_increment,
    name  varchar(20) default null,
    age   int(11)     default null,
    email varchar(20) default null
) character set utf8  engine innodb;

INSERT INTO tbl_user (name, age, email) VALUES ('1aa1', 21, 'a@163.com');
INSERT INTO tbl_user (name, age, email) VALUES ('2aa2', 222, 'b@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');

-- 创建索引
create index idx_user_nameAge on tbl_user (name, age);

在这里插入图片描述

-3：覆盖索引，索引失效情况：

9）字符串类型，不加单引号索引失效：（varchar 不加单引号，索引失效）
更会：（导致行锁升级为表锁）
（数字不加引号，mysql也能查出来）
（但是：不加单引号，会自动进行类型转换，索引失效）
在这里插入图片描述
（如果是数字，加不加单引号都行。）

10）少用 or，用它连接时，会导致索引失效：
在这里插入图片描述

3.x：索引使用：小总结：（口诀）

全值匹配我最爱，最佳左前缀法则；
带头大哥不能死，中间兄弟不能断；
索引列上无计算，范围之后全失效；
like百分加右边，覆盖索引不写*。
字符串里加引号，不等空值和or。

12）习题案例：
在这里插入图片描述

4：面试题讲解：

先说结论：
1、索引分析：定值为常量、范围之后失效，最终看排序，一般 order by 是给个范围：
2、group by 基本上都需要进行排序（分组之前必排序），会有临时表产生：

1）建表、建索引语句：

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

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

在这里插入图片描述

2）简单测试索引：
在这里插入图片描述

3）复杂分析：
-1：复杂分析（1）：
a：（1234 或 4321 或 1324，都能使用索引，全出现了就行，查询优化器会开启自我优化分析）
b：（在常量的范围，会优化分析）（前提是，建立的索引不需全部都在，少一个就不会自动优化）
c：（但最好，怎么建立索引，怎么使用索引）
在这里插入图片描述

-2：复杂分析（2）（范围之后全失效，但范围会用得到；用到了 3 个）
在这里插入图片描述

-3：复杂分析（3）（优化器会自动优化顺序，用到了 4 个）
在这里插入图片描述

-4：复杂分析（4）（c1,c2 用到了查找，c3 用到了排序。但是：c4 没有用到索引）
在这里插入图片描述

-5：复杂分析（5）排序
在这里插入图片描述

-6：复杂分析（6）
在这里插入图片描述

-7：复杂分析（7）
在这里插入图片描述

5：一般性建议：：

（只要是 range ，索引一定是走散了）
在这里插入图片描述

三.：查询截取分析（44～50）

1：查询优化：

1）永远小表驱动大表：
（类似嵌套循环 Nested Loop ）（减少数据库连接与释放）：
-1：原理：（选择左面的）
在这里插入图片描述
-2：case：（小表驱动大表与 in 和 exists 的使用）
（怎么换：id in 抹掉，换掉 exists ，后面变为子查询）

-3：exists 原理：（in 写法的变种）（怎么换、原理：true保留，false算了）
a：在子查询中，需要引用到主查询的字段数据，使用 exists 关键字。
b：exists 后面的字查询，最少返回一条数据，则整个条件返回 true。
c：用在 where 之后，子查询之前。
d：关注是否可以查询到数据，而不在乎查询到的值。
e：exists 关键字后面跟着一个子查询，当该子查询可以查出至少一条记录时，则返回true，exists 条件成立。
在这里插入图片描述

-4：in 变 exists 案例：（结果一样）
在这里插入图片描述

2）order by 关键字优化：
-1：order by 子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序。
a：建表、创建索引：

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_tblA_ageBirth on tblA (age, birth);

select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2022-04-11 21:10:16 |
|   23 | 2022-04-11 21:10:16 |
|   24 | 2022-04-11 21:10:16 |
+------+---------------------+

b：case：（只要从 age 开始，就可以使用到索引）
在这里插入图片描述

c：Mysql 支持两种方式的排序：（FileSort & Index）
1、（index 效率高，它指 MySQL 扫描索引本身完成排序）
2、（FileSort 效率低）

d：Order by 满足两种情况，会使用 Index 方式排序：
1、（Order by 语句使用索引最左前列）
2、（使用 Where 子句与 Order by 子句条件列，组合满足索引最左前列）

-2：尽可能在索引列上，完成排序操作；遵照索引建的最佳做前缀：

-3：如果不在索引列上排序，FileSort 有两种算法：（双路排序 & 单路排序）
a：mysql 双路排序：（两次IO，IO很耗时，Mysql 4.1 之前使用）
b：mysql 单路排序：
在这里插入图片描述
c：结论及引申出的问题：
（由于单路算法是后出的，总体好过双路排序）
（但是单路有问题，可能比双路排序更耗时）

-4：优化策略：
在这里插入图片描述
-5：小总结：

3）group by 关键字优化：（和 order by 类似）
在这里插入图片描述

2：慢查询日志：

1）是什么：
在这里插入图片描述
2）怎么玩：
-1：说明：（MySQL 中，大部分牛逼的功能，都是默认关闭）

-2：（查看是否开启 & 如何开启）

-- 查看
mysql> show variables like '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/bogon-slow.log |
+---------------------+-------------------------------+

-- 开启（只对当前数据库生效，重启 mysql server后，会失效）
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述

-3：开启了慢查询日志后，什么样的 SQL，才会被记录到：慢查询日志里面呢？

-4：Case：（设置阈值时间、并查看日志）
a：设置阙值时间：
在这里插入图片描述

-- 查看默认时间
mysql> show variables like 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

-- 修改默认时间
mysql> set global long_query_time = 3;
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述
b：记录慢 SQL，并后续分析：

c：查询当前系统中，有多少条慢查询记录：（超过阈值的 sql）

mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 2     |
+---------------+-------+

-5：配置版：（永久生效：不常用）
在这里插入图片描述

3）日志分析工具：（mysqldumpslow）
-1：是什么：生产环境中，MySQL 提供的日志分析工具。
-2：怎么用：（ mysqldumpslow --help ）
在这里插入图片描述
-3：工作常用参考：（灵活组合得到信息）

3：批量数据脚本：（插入 1000w 数据）

1）建表：

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

create table emp (
    id       int unsigned primary key auto_increment,
    empno    mediumint unsigned not null default 0 comment '编号',
    ename    varchar(20)        not null default '' comment '名字',
    job      varchar(9)         not null default '' comment '工作',
    mgr      mediumint unsigned not null default 0 comment '上级编号',
    hiredate date               not null comment '入职时间',
    sal      decimal(7, 2)      not null comment '薪水',
    comm     decimal(7, 2)      not null comment '红利',
    deptno   mediumint unsigned not null default 0 comment '部门编号'
);

2）设置参数，防止大数据插入报错（ log_bin_trust_function_creators ）：

-- 查看
mysql> show global variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

-- 开启
mysql> set global log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

在这里插入图片描述
3）创建函数，保证每条数据都不同：
-1：随机产生字符串：

delimiter $$ -- sql 结束符号变为 $$;
create function rand_string(n int) returns varchar(255)
begin
	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
	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 $$

-3：假如要删除：（drop function rand_num）

4）创建存储过程：
-1：创建往 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_string(6),'salesman',0001,curdate(),2000,400,rand_num());
	until i = max_num
	end repeat;
	commit;
end $$

-2：创建往 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 $$

-3：假如要删除：（delimiter）（drop procedure insert_emp）
-4：SQL 结束标记改回来：（delimiter ;）

5）调用存储过程：
-1：插入 dept 表：

-- 插入 10 条：
call insert_dept(100,10);

-- 插入 50 万条：
call insert_dept(100001,500000);

-2：插入 emp 表：

-- 插入 50 万条：
call insert_emp(100001,500000);

4：Show Profile：

1）是什么：
-1：是 mysql 提供的，可以用来分析：当前会话中语句执行的资源消耗情况。
-2：可以用于 SQL 的调优的测量。

2）官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html

3）默认参数下，参数处于关闭状态，并保存最近 15 次的运行结果：

4）分析步骤：
-1：查看当前 mysql 版本是否支持：

mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | ON    |
| profiling_history_size | 15    |
+------------------------+-------+

-2：开启功能：

set profiling = on;

-3：运行 SQL：

mysql> select * from dept limit 0,5

mysql> select * from emp group by id%10 limit 150000;
10 rows in set (1.96 sec)

mysql> select * from emp group by id%10 order by deptno;
10 rows in set (1.07 sec)

-4：查看结果：（ show profiles ）

mysql> show profiles;
+----------+------------+--------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                          |
+----------+------------+--------------------------------------------------------------------------------+
|        7 | 0.00140675 | select * from dept limit 0,5                                                   |
|        8 | 0.00120050 | explain select * from dept left join emp on dept.deptno = emp.deptno limit 0,5 |
|        9 | 0.96275650 | select * from emp group by id%10 limit 150000                                  |
|       10 | 1.08553950 | select * from emp group by id%100 limit 150000                                 |
|       11 | 1.06771725 | select * from emp group by id%10 order by deptno                               |
+----------+------------+--------------------------------------------------------------------------------+
11 rows in set (0.00 sec)

-5：诊断 SQL：（查看 SQL 细节）（分解步骤）

mysql> show profile cpu, block io for query 9;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000034 | 0.000025 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000007 | 0.000006 |   0.000000 |            0 |             0 |
| checking query cache for query | 0.000093 | 0.000094 |   0.000000 |            0 |             0 |
| checking permissions           | 0.000013 | 0.000012 |   0.000000 |            0 |             0 |
| Opening tables                 | 0.000025 | 0.000025 |   0.000000 |            0 |             0 |
| System lock                    | 0.000018 | 0.000018 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000033 | 0.000033 |   0.000000 |            0 |             0 |
| init                           | 0.000192 | 0.000198 |   0.000000 |            0 |             0 |
| optimizing                     | 0.000019 | 0.000012 |   0.000000 |            0 |             0 |
| statistics                     | 0.000098 | 0.000101 |   0.000000 |            0 |             0 |
| preparing                      | 0.000034 | 0.000031 |   0.000000 |            0 |             0 |
| Creating tmp table             | 0.000234 | 0.000239 |   0.000000 |            0 |             0 |
| executing                      | 0.000016 | 0.000010 |   0.000000 |            0 |             0 |
| Copying to tmp table           | 0.961687 | 0.819602 |   0.000000 |            0 |             0 |
| Sorting result                 | 0.000087 | 0.000083 |   0.000000 |            0 |             0 |
| Sending data                   | 0.000057 | 0.000054 |   0.000000 |            0 |             0 |
| end                            | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
| removing tmp table             | 0.000011 | 0.000011 |   0.000000 |            0 |             0 |
| end                            | 0.000006 | 0.000006 |   0.000000 |            0 |             0 |
| query end                      | 0.000017 | 0.000017 |   0.000000 |            0 |             0 |
| closing tables                 | 0.000010 | 0.000009 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000015 | 0.000015 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000004 | 0.000003 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000025 | 0.000025 |   0.000000 |            0 |             0 |
| Waiting for query cache lock   | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| freeing items                  | 0.000003 | 0.000003 |   0.000000 |            0 |             0 |
| storing result in query cache  | 0.000006 | 0.000005 |   0.000000 |            0 |             0 |
| logging slow query             | 0.000004 | 0.000004 |   0.000000 |            0 |             0 |
| cleaning up                    | 0.000005 | 0.000004 |   0.000000 |            0 |             0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
29 rows in set (0.00 sec)

在这里插入图片描述
-5：日常开发需要注意的结论：（以下四个，都是惹事的；有任何一个，必须优化）

5：全局查询日志：

（一张表，收集所有的 SQL）
（永远不要在，生产环境开启这个配置，只能在测试环境使用）
（这个不常用，建议使用 show profile）
1）配置启用：
在这里插入图片描述
2）编码启用：

四.：Mysql 锁机制（51～60）

1：概述：

1）定义：
在这里插入图片描述
2）生活购物：

3）锁的分类：
-1：从对数据操作的类型分类：
a：读锁（共享锁）：针对同一份数据，多个读操作可以同时进行，而不会互相影响。
b：写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

-2：从对数据操作的粒度分类：
a：表锁：
b：行锁：

-3：根据：（开销、加锁速度、死锁、粒度、并发性能），具体应用来选择不同的锁。

2：三锁 — 表锁：（偏读）（重要）

1）特点：
-1：偏向 MyISAM 存储引擎。
-2：开销小、加锁块；无死锁。
-3：锁定粒度大，发生锁冲突的概率最高，并发度最低。

2）案例分析：
-1：建表 SQL：（engine myisam）

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');
INSERT INTO mylock (name) VALUES ('f');

-- 锁表
mysql> lock table mylock read, emp write;

-- 查看
mysql> show open tables;
+-----------+---------+--------+-------------+
| Database  | Table   | In_use | Name_locked |
+-----------+---------+--------+-------------+
| db02      | mylock  |      1 |           0 |
| db02      | emp     |      1 |           0 |
+-----------+---------+--------+-------------+

-- 解锁表（解开全部表的锁）
mysql> unlock tables;

-2：加读锁：（共享锁）（手动增加表的读锁）（看执行顺序读图）
a：自己会话：（可以读，不能改）
b：其他会话：（可以读、不能改）（改的时候进入阻塞状态）（表锁结束后，自动继续执行）
在这里插入图片描述

-3：加写锁：（独占锁）（排它锁）：（手动增加表写锁）
a：（自己能读能写，不能读写其他）
b：（别的会话无论读写，都进入阻塞状态）
在这里插入图片描述

3）案例结论：
在这里插入图片描述
4）表锁分析：

mysql> show status like 'table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1000221 |
| Table_locks_waited    | 0       |
+-----------------------+---------+
2 rows in set (0.00 sec)

在这里插入图片描述

3：三锁 — 行锁：（偏写）（重要）：

1）特点：
-1：行锁：偏向 InnoDB 存储引擎；
-2：开销大，加锁慢；会出现死锁；
-3：锁定粒度最小，发生锁冲突的概率最低，并发度也最好；
-4：InnoDB 与 MyISAM 最大不同：（2）
a：InnoDB：支持事务：（Transaction）
b：InnoDB：采用了行级锁：

2）由于行锁支持事物：（复习老知识）
-1：事务（Transaction）及 ACID 属性：（原子性、一致性、隔离性、持久性）
-2：并发事务处理，带来的问题：
a：更新丢失：
b：脏读：
c：不可重复读：
d：幻读：

-3：事务隔离级别：(默认级别：可重复度)（有幻读，但是可以优化）
在这里插入图片描述

3）案例分析：
-1：建表 SQL：

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, '400');
insert into test_innodb_lock values (5, '500');
insert into test_innodb_lock values (6, '600');
insert into test_innodb_lock values (7, '700');
insert into test_innodb_lock values (8, '800');
insert into test_innodb_lock values (9, '900');
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);

在这里插入图片描述

-2：行锁定，基本演示：
a：关闭自动提交：（ set autocommit = 0; ）
1、（只影响修改后的行，只影响锁定的行，其他行查询修改不受影响）
2、（A更新不提交，B读之前的数据不会变化）
3、（A更新不提交，B修改阻塞，A commit之后才可以修改）
4、（会话B如果自动 commit，B 就不用再次 commit ，就可以读到 A 行锁提交后的数据）
在这里插入图片描述

-3：无索引行锁，升级为表锁：（varchar 字段不使用 ’ '，行锁变为表锁）
在这里插入图片描述

-4：间隙锁危害：（宁可错杀，不可放过）
修改范围内数据：（会锁住范围内的数据 --> 然后插入范围内数据时，会阻塞）
在这里插入图片描述

-5：面试题：（如何锁定一行数据）（前面是锁一张表，现在是锁定一行数据）
（修复数据时使用）

select * from test_innodb_lock where a = 8 for update;

在这里插入图片描述

4）案例结论：
在这里插入图片描述

5）行锁分析：
在这里插入图片描述

6）优化建议：
在这里插入图片描述

4：三锁 — 页锁：（略）

在这里插入图片描述

五.：主从复制（61～61）

1：复制的基本原理：

（主从复制延迟解决：https://blog.csdn.net/soar_away/article/details/72615012）
1）MySQL 主从复制原理：slave 会从 master 读取 binlog（二进制数据文件）来进行数据同步。

2）特点：（从接入点开始复制，和 redis 不同）（有延时性）

3）三步骤 + 原理图：
在这里插入图片描述

2：复制的基本原则：

1）每个 slave 只有一个 master；
2）每个 master 可以有多个 salve；（一主多从）
3）每个 slave 只能有一个唯一的服务器 ID；（为了区分）

3：复制的最大问题：（网络会有延迟）

4：一主一从常见配置：

1）主机从机，保证 mysql 版本一致，且后台以服务运行：（主机从机同一网段，互相可以ping通）

2）主从都配置在：（ my.cnf ）配置文件中（ mysqld ）节点下：（都是小写）

3）主机修改 my.cnf 配置文件：（ vim /etc/my.cnf ）
-1：配置选项
（1） 28 server-id=1
（2） 57 log-bin=mysql-bin
（3） 58 log-err=mysql-err
在这里插入图片描述
-2：配置案例：

-3：binlog 日志三种格式：

4）从机修改 my.cnf 配置文件：

-- 从服务器唯一 ID
  63  # server-id     = 1
 100  server-id       = 2
 
-- 启用 二进制日志
 117  log-bin=mysql-bin

-- 启用中继日志（不修改也可以）
      relay-log=mysql-relay

5）主从重启 Mysql 服务器，并关闭防火墙：

6）在主机上建立账户，并授权给 slave：
（创建主从复制权限，所有表 slave 用户，指定密码 123123）
-1：主机执行：

-- 主机 mysql 里执行，授权命令。
mysql> GRANT REPLICATION SLAVE ON *.* TO 'zhangxudong'@'从机数据库ip' IDENTIFIED BY '123456';

-- 刷新
mysql> flush privileges;

-2：查询 master 状态：（show master status;）
file：binlog日志的名字。
position：切入点：（这上面文件的 341 位置，开始进行复制）。
Binlog_Do_DB：需要复制的数据库。
Binlog_Ignore_DB：忽略的数据库（不复制）。
（记录下：File & Position 的值）
在这里插入图片描述

7）从机配置：（show slave status;）
-1：连接主机：

change master to
    master_host = '192.168.43.69',	-- 主机 ip 地址
    master_user ='zhangsan',
    master_password ='123456',
    master_log_file ='mysql-bin.000004',	-- 日志文件（主机查看）
    master_log_pos =595413537;	-- 开始位置（主机查看）

（如果之前连过主机，重新配置主重：先stop slave，~~后 reset master~~ ；然后在运行连接）
在这里插入图片描述
-2：启动从服务器复制功能：（ start slave ）

-3：检查是否成功：
在这里插入图片描述

8）测试：主机（新建数据库｜新建表｜insert 记录），看到从机复制成功。

9）如何停止从服务器复制功能：（从机执行：stop slave；）（注意：重启复制，要从新查看值）

张老师的分享

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级—尚硅谷—周阳—2018-03-28

视频链接一.：（1～） 1：： 1）： -1： -2： -3： -4： -5： 2）： -1： -2： -3： -4： -5： 3）： -1： -2： -3： -4： -5： 4）： -1： -2： -3： -4： -5： 5）： -1： -2： -3： -4： -
复制链接

扫一扫