尚硅谷—高级—MySQL数据库高级

（视频：尚硅谷MySQL数据库高级，mysql优化，数据库优化_哔哩哔哩_bilibili）

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

目录

一.mysql 的架构介绍：

二.索引优化分析：

      二.一.性能分析：

      二.二.索引优化：：

三.查询截取分析：

四.Mysql 锁机制：

五.主从复制：

---

---

一.mysql 的架构介绍：

             1）Mysql 简介：
                      a：概述：
                      b：Mysql 高级干什么：（功底很深，DBA）
                               Mysql 内核、Sql 优化工程师、Mysql 服务器优化、
                               各种参数常量设定、查询语句优化、
                               主从复制、软硬件升级、容灾备份、sql 编程。


             2）Mysql Linux 版本的安装：（安装，修改配置文件）

                      a：下载：（网址：MySQL :: Download MySQL Community Server (Archived Versions)）


                      b：检查 当前系统 是否安装过 mysql：



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



                      d：安装 Mysql 客户端：
                            - 安装包 放在 /opt/ 目录下：
                            - 



                      e：查看 Mysql 安装时，创建的 Mysql 用户和 Mysql 组：（top 命令）



                      f：Mysql 服务的 启动、停止：（start、stop）



                      g：mysql 服务启动后，开始连接：
                                - 首次连接成功（没有输入密码）：

                                - 按照 安装 Server 中的提示，修改登录密码：




                      h：自启动 Mysql 服务：




                      i：修改 配置文件位置：
                    （Mysql 配置文件位置：/usr/share/mysql ）（此文件与 调优有关）（拷贝进 /etc 所有配置的集散地）


                      g：修改 字符集 和 数据存储路径：
                              - 插入中文乱码：


                              - 查看字符编码：（默认 客户端 和 服务端 都使用了 latin1 ，所以会中文乱码）

show variables like 'character%';

show variables like '%char%';

                              - 修改编码：（在 mysql 配置文件中，修改）

 17 [client]
 18 #password       = your_password
 19 port            = 3306
 20 socket          = /var/lib/mysql/mysql.sock
 21 default-character-set=utf8

 26 [mysqld]
 27 port            = 3306
 28 character_set_server=utf8
 29 character-set-client=utf8
 30 collation-server=utf8_general_ci
 31 lower_case_table_names=1


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

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

                      k：设置外网可访问：




                      k：Mysql 的 安装配置：
                              - windows：D:\devSoft\MySQLServer5.5\data
                              - Limux：默认路径：/var/lib/mysql





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

                      a：二进制日志 log-bin：主要用于主从复制：
（配置文件中）


                      b：错误日志 error-log：（位置：my.cnf 中配置路径：mysql/data/mysqlerr.err ）
                              - 默认关闭：
                              - 记录严重的 警告 和 错误 信息，每次启动 和 关闭 的详细信息等；

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

                      d：数据文件：
                              - 两系统：
                                       Windows：D:\devSort\MysqlServer5.5\data 目录下，可以挑选很多库。
                                       Linux：默认路径：/var/lib/mysql。
                              - frm 文件：存放表结构（format）
                              - myd 文件：存数据（data）
                              - myi 文件：存索引（index）

                      e：如何配置（配置文件位置）：
                              - Windows：my.ini 文件；
                              - Linux：/etc/my.cnf；

             4）Mysql 逻辑架构介绍：
                      a：总体概览：




             5）Mysql 存储引擎：（详见 数据库 基础）
                      a：查看命令：show engines;
                      a：查看 默认、当前 存储引擎：show variables like '%storage_engine%';

                      b：InnoDB 和 MyISAM：

                            - innoDB：支持事务 ，支持主外键，支持行级锁（适合高并发）。
                            - myisam：不支持事务，不支持主外键，支持表级锁（不适合高并发）。添加速度快；
                            - memory：数据存储在内存中【重启mysql服务，数据丢失，但是表结构还在】，执行速度快（没有 IO 读写），默认支持索引（hash 表）

                      c：阿里、淘宝用哪个：（存储引擎给换了）

 


 

二.索引优化分析：

             1）性能下降SQL慢，（执行时间长，等待时间长）；
                      a：查询语句写的烂：
                      b：索引失效：（建了没用上）
                              - 单值索引：   - 复合索引：

                      c：关联查询 太多 join（设计缺陷 或 不得已的需求）
                      d：服务器调优 及 各个参数设置（缓冲、线程数等）

             2）常见通用的 Join 查询；
                      a：SQL 执行顺序：
                              - 手写顺序：

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;

                              - 机读顺序：（通过 mysql 优化器认为最优的方式，去运行）



                      b：Join 图：




                      c：建表 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;

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 ('a','11');
insert into tb1_dept (dept_name,ioc_add) values ('b','12');
insert into tb1_dept (dept_name,ioc_add) values ('c','13');
insert into tb1_dept (dept_name,ioc_add) values ('d','14');
insert into tb1_dept (dept_name,ioc_add) values ('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', 3);
INSERT INTO tb1_emp (name, dept_id) VALUES('a6', 2);

                      d：7 种 JOIN：（查询以上 两张表）

                               - 公共的数据，被剔除掉：

                               - mysql 不支持 全外连接：

                               - 不支持就换一种方法：
                                    （使用上面几种类型，使用 union / union all 拼接出来）（两种组合）



             3）索引简介：
                      a：是什么：
                               - 官方对索引的定义为：索引（index）是帮助 Mysql 高效获取数据的数据结构。
                                 索引的本质：索引是一种 数据结构。
                               - 可以简单理解为：排好序的快速查找数据结构。（影响：排序，查找）
                                       1.讲解：（二叉树）（BTREE 索引）

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


                               - 索引存储位置：一般来说：索引本身也很大，不可能全部存储在内存中，因此，索引往往以 索引文件 的形式，存储在磁盘上。
                               - 索引结构：（BTree ：多路搜索树）



                      b：优势：