MySQL高级
mysql安装配置
mysql配置，修改字符集编码是用于当我们创建数据库时，没有指定数据字符集编码类型时，就会用配置默认的字符集编码，因此我们最好修改了默认字符集编码为utf8,当然更重要的就是在创建数据库和表时一定要指定好utf8字符集编码，utf8mb4这种。
#vim 编辑文件时
#在当前光标的下一行输入用：按o,就会跳到下一行，插入状态
#log-bin 二进制日志，用于主从复制
#log-error 错误日志，默认是关闭的。记录严重的警告和错误信息，每次启动和关闭的详细信息等。
#查看mysql支持的存储引擎
show engines;
#查看mysql当前默认的存储引擎
show VARIABLES like '%storage_engine%';
mysql逻辑架构
架构
分层的好处
分层的好处就是分层隔离后，哪一层出了问题就找谁，方便定位查找。
Optimizer（优化器）是mysql的优化器。阿里取消了Optimizer优化器来提高执行性能，因为其sql已经表示写得最优了，不需要再经过一层优化器来耗时。
mysql架构：分层可拔插。分为4层：
- 连接层
- 服务层
- 引擎层
- 存储层
存储引擎
MyISAM与InnoDB的区别
MyISAM适合读多的场景，InnoDB适合写多特别是需要事务支持的场景。
索引
创建索引
SQL执行时间慢的原因
SQL执行时间慢的原因（慢查询）:
- sql语句写得烂
- 索引失效（没有索引）
- 太多关联查询join
- mysql服务器调优及各个参数设置（缓冲、线程数等）
sql执行加载顺序
sql语句格式
机器读取顺序
7种JOIN理论
- 注意B.key is null的场景。
- mysql用union实现并集。
- mysql用union加where a.key is null 实现获取各自独立的部分。
索引原理
-
索引是Mysql高效获取数据的数据结构，也就是说索引本质是数据结构，是排好序的快速查找数据结构，底层是B+树（多路搜索树）。
-
索引会影响作用于where条件的查找和order by 的排序。
-
update更新慢的原因是除了数据在改变，索引也在改变重新建立索引。
-
索引本身也很大，不可能全部在内存中，一般会以索引文件的形式存储在磁盘中，在用到的时候再加载到内存。
Mysql索引分类
- B+树索引
- Hash索引
- full-text全文索引
- R-tree索引
索引的优势和劣势
优势：
- 提高数据检索效率，降低磁盘IO成本
- 降低数据排序成本，降低CPU消耗
劣势：
- 创建索引要占用空间
- 虽然索引会提高查询速度，但是在增删改时因为要维护索引所以会降低更新速度
- 查询语句和索引都需要通过优化才能实现高效率的查找
索引的分类
- 单值索引——一个索引只包含单个列
- 复合索引——一个索引包含多个列
- 唯一索引——索引列的值必须唯一，但允许有空值
B+树索引查找过程
哪些情况下适合建立索引
哪些情况下不适合建立索引
Explain——执行计划
-- 语句
EXPLAIN SELECT * FROM city c where c.CountryCode='NLD' ;
id——执行顺序
select_type——查询的操作类型
table——查询的表或者临时表
表示当前查询的是哪张表或者临时表。
type——执行效率类型
-- index 全索引扫描，虽然是读全表，但是因为字段id是从索引中读取的，所以比读全表要好。
select id from user;
possible_keys——可以用到的索引
key——实际用到的索引
key_len——索引中使用的字节数
ref——显示索引的哪一列被使用或者索引值是否是常量
rows——找到记录所要读取的行数
读取的行数越少越好。
Extra——额外信息
using filesort表示没有使用到索引进行排序，通常出现在排序的字段不是索引字段，需要优化。
using temporary表示使用了临时表保存中间结果，mysql在排序时使用了临时表，特别需要优化。
using index 使用了索引覆盖，是好的。
索引优化
索引单表优化
针对下面的查询sql在没有建立索引时的执行计划。
加索引优化，但是查询执行效率只有range级别，这是因为索引在遇到范围查找如>大于、小于等时会导致索引实现，只用到了复合索引的第一个字段category_id，且排序也用不到索引，是using filesort表示没有使用到索引进行排序，需要继续优化。
继续优化，正确的索引建立
因为范围查找导致索引失效的问题，我们应该只建立两个用到索引的字段，对于范围查找的字段就不建立索引了，否则索引会失效。
索引双表优化
针对下面的左联查询sql在没有建立索引时的执行计划。
对左联查询的右表加索引优化
可以看到右表查询优化到了ref，查询rows也优化了。
对左联查询的左表加索引优化
可以看到左表查询只优化到index，查询rows数没有减少。
结论：左联查询要对右表加索引，右联则要对左表加索引。
索引三表优化
针对下面的三表左联查询sql在没有建立索引时的执行计划
优化：参考双表左联查询的结论，要在右表上加索引。
结论：与双表左联查询类似，要在右表上加索引，且查询的时候左表必须是小表，用小表驱动大表。
索引优化原则及索引失效案例
- MySQL查询语句中where条件里面的顺序并不会因为与组合索引中的顺序不同而失效，MySQL优化器会作优化。
- group by 基本上都需要排序，会有临时表产生。
索引判断题
索引口诀
排序——Order By 优化
案例分析
排序的两种方式
filesort的两种排序方式与优化
双路排序和单路排序
单路排序的问题
单路排序优化
Order by排序优化总结
Group By优化
慢查询调优
步骤
小表驱动大表
什么是慢查询
默认不开启，只有在有问题的时候才使用命令开启慢查询日志。
开启慢查询
查询脚本
-- 查看是否开启慢查询
SHOW VARIABLES LIKE '%slow_query_log%';
-- 开启和关闭慢查询 注意在mysql重启后会失效，如果要永久生效则要配置在my.cnf上
SET GLOBAL slow_query_log =1;
SET GLOBAL slow_query_log =0;
-- 查询，默认10s
SHOW VARIABLES LIKE '%long_query_time%';
-- 设置慢查询阈值
SET GLOBAL long_query_time =3;
-- 通过加global查询是否设置生效
SHOW GLOBAL VARIABLES LIKE '%long_query_time%';
-- 查询当前系统中有几条慢sql记录
SHOW GLOBAL STATUS LIKE '%Slow_queries%';
本次开启生效
永久开启生效
配置如下：
慢查询日志linux文件路径
慢查询阈值
注意需要新开会话或者加global才能看到生效值
mysql日志分析工具——mysqldumpslow
#查看命令帮助
mysqldumpslow --help
show profiles——慢查询资源消耗明细
show profile查询使用参数
show profile要注意的资源消耗点
使用步骤
sql脚本
-- 查看show profile 是否打开 ,有的版本是默认开启，有的是默认关闭，开启和关闭不生效
SHOW VARIABLES LIKE '%profiling%';
-- 通过加global查询show profile是否设置生效
SHOW GLOBAL VARIABLES LIKE '%profiling%';
-- 开启和关闭profiling
SET profiling ='on';
SET profiling ='off';
-- 查询最近15条sql的明细id
show PROFILES;
-- 根据查询id查看查询的资源消耗明细
show PROFILE cpu,block io for QUERY 247;
-- 查看所有的开销信息
show PROFILE all for QUERY 247;
全局查询日志——测试环境用
永远不要在生产上开启该功能。
配置启动
sql启动——本次有效
sql脚本
-- 开启全局日志
set GLOBAL general_log=1;
SET GLOBAL log_output='TABLE';
-- 查询全局日志表
SELECT * FROM mysql.general_log;
MySQL锁机制
锁按照粒度和操作类型区分方式
表锁的加锁、解锁和查看锁情况——表锁偏读
表加读锁后的情况
session1加了读锁时：session1可以查自己锁的表，不能新增或更新自己锁的表，也不能查询或更新别的表；
session2可以读被锁的表，但不能更新被锁的表（会被阻塞）；可以读和更新其他表。
表加写锁后的情况
session1加了写锁时：session1可以查自己锁的表，可以新增或更新自己锁的表；不能查询或更新别的表；
session2可以读被锁的表会被阻塞，也不能更新被锁的表；可以读和更新其他表。
结论
表锁分析
行锁——行锁偏写（写多的场景）
MyIsam与InnoDB最大的不同
并发事务带来的问题
行锁变表锁的情况
查询条件类型使用不当导致的索引失效变表锁的情况，所以在使用更新/查询时一定要注意查询条件与字段类型相匹配。
间隙锁
mysql会通过间隙锁来锁定一个范围，保证不出现幻读的情况。但是会阻塞住该范围内的插入操作。
给某行要修改的数据加行锁
被for unpdate锁定的行在其他操作过来是会被阻塞，属于悲观锁。
行锁分析
-- 行锁情况分析
SHOW STATUS LIKE '%innodb_row_lock%';
行锁优化建议
MySQL主从复制
主从复制的过程（原理）
一主一从配置（window主linux从）
- 如果都是linux上的mysql主从复制则直接参考linux上的命令位置，修改的配置是一样的。
- 主从复制的两个服务器要能互相ping通。
其他
创建函数
注意：函数有返回值，存储过程没有返回值，只有输出值。
创建存储过程
