MySQL高级

mysql安装配置

mysql配置，修改字符集编码是用于当我们创建数据库时，没有指定数据字符集编码类型时，就会用配置默认的字符集编码，因此我们最好修改了默认字符集编码为utf8,当然更重要的就是在创建数据库和表时一定要指定好utf8字符集编码，utf8mb4这种。

image.png image.png

#vim 编辑文件时
#在当前光标的下一行输入用：按o,就会跳到下一行，插入状态
#log-bin 二进制日志，用于主从复制
#log-error 错误日志，默认是关闭的。记录严重的警告和错误信息，每次启动和关闭的详细信息等。
#查看mysql支持的存储引擎
show engines;
#查看mysql当前默认的存储引擎
show VARIABLES like '%storage_engine%';

mysql逻辑架构

架构

image.png image.png image.png

分层的好处

分层的好处就是分层隔离后，哪一层出了问题就找谁，方便定位查找。

Optimizer（优化器）是mysql的优化器。阿里取消了Optimizer优化器来提高执行性能，因为其sql已经表示写得最优了，不需要再经过一层优化器来耗时。

mysql架构：分层可拔插。分为4层：

• 连接层
• 服务层
• 引擎层
• 存储层

存储引擎

image.png
image.png

MyISAM与InnoDB的区别

image.png

MyISAM适合读多的场景，InnoDB适合写多特别是需要事务支持的场景。

索引

创建索引

创建索引.png

SQL执行时间慢的原因

SQL执行时间慢的原因（慢查询）:

• sql语句写得烂
• 索引失效（没有索引）
• 太多关联查询join
• mysql服务器调优及各个参数设置（缓冲、线程数等）

sql执行加载顺序

sql语句格式

image.png

机器读取顺序

image.png image.png

7种JOIN理论

image.png
image.png

• 注意B.key is null的场景。
• mysql用union实现并集。
• mysql用union加where a.key is null 实现获取各自独立的部分。

image.png
image.png

索引原理

• 索引是Mysql高效获取数据的数据结构，也就是说索引本质是数据结构，是排好序的快速查找数据结构，底层是B+树（多路搜索树）。

• 索引会影响作用于where条件的查找和order by 的排序。

• update更新慢的原因是除了数据在改变，索引也在改变重新建立索引。

• 索引本身也很大，不可能全部在内存中，一般会以索引文件的形式存储在磁盘中，在用到的时候再加载到内存。

Mysql索引分类

• B+树索引
• Hash索引
• full-text全文索引
• R-tree索引

索引的优势和劣势

优势：

• 提高数据检索效率，降低磁盘IO成本
• 降低数据排序成本，降低CPU消耗

劣势：

• 创建索引要占用空间
• 虽然索引会提高查询速度，但是在增删改时因为要维护索引所以会降低更新速度
• 查询语句和索引都需要通过优化才能实现高效率的查找

索引的分类

• 单值索引——一个索引只包含单个列
• 复合索引——一个索引包含多个列
• 唯一索引——索引列的值必须唯一，但允许有空值

B+树索引查找过程

b+树.png b+树2.png

哪些情况下适合建立索引

索引.png

哪些情况下不适合建立索引

索引2.png

Explain——执行计划

-- 语句
EXPLAIN SELECT * FROM city c where c.CountryCode='NLD' ;

explain.png

id——执行顺序

explain-id.png

select_type——查询的操作类型

select_type.png
select_type2.png

table——查询的表或者临时表

表示当前查询的是哪张表或者临时表。

type——执行效率类型

type.png
type2.png
type3.png

-- index 全索引扫描，虽然是读全表，但是因为字段id是从索引中读取的，所以比读全表要好。
select id from user;

possible_keys——可以用到的索引

possible_keys.png

key——实际用到的索引

key.png

key_len——索引中使用的字节数

key_len.png

ref——显示索引的哪一列被使用或者索引值是否是常量

ref.png ref2.png

rows——找到记录所要读取的行数

rows.png

读取的行数越少越好。

Extra——额外信息

extra.png

using filesort表示没有使用到索引进行排序，通常出现在排序的字段不是索引字段，需要优化。

using filesort.png

using temporary表示使用了临时表保存中间结果，mysql在排序时使用了临时表，特别需要优化。

using temporary.png

using index 使用了索引覆盖，是好的。

索引覆盖.png

索引优化

索引单表优化

针对下面的查询sql在没有建立索引时的执行计划。

单表索引.png

加索引优化，但是查询执行效率只有range级别，这是因为索引在遇到范围查找如>大于、小于等时会导致索引实现，只用到了复合索引的第一个字段category_id，且排序也用不到索引，是using filesort表示没有使用到索引进行排序，需要继续优化。

单表索引2.png
单表索引3.png

继续优化，正确的索引建立

因为范围查找导致索引失效的问题，我们应该只建立两个用到索引的字段，对于范围查找的字段就不建立索引了，否则索引会失效。

单表索引4.png
单表索引5.png

索引双表优化

针对下面的左联查询sql在没有建立索引时的执行计划。

双表索引.png

对左联查询的右表加索引优化

可以看到右表查询优化到了ref，查询rows也优化了。

双表索引2.png

对左联查询的左表加索引优化

可以看到左表查询只优化到index，查询rows数没有减少。

双表索引3.png

结论：左联查询要对右表加索引，右联则要对左表加索引。

双表索引4.png

索引三表优化

针对下面的三表左联查询sql在没有建立索引时的执行计划

三表索引.png

优化：参考双表左联查询的结论，要在右表上加索引。

三表索引2.png
三表索引3.png

结论：与双表左联查询类似，要在右表上加索引，且查询的时候左表必须是小表，用小表驱动大表。

三表索引4.png

索引优化原则及索引失效案例

索引失效.png
索引失效2.png
索引失效3.png
索引失效4.png
索引失效5.png
索引失效6.png
索引失效7.png
索引失效8.png
索引失效9.png
索引失效10.png
索引失效11.png
索引失效12.png

• MySQL查询语句中where条件里面的顺序并不会因为与组合索引中的顺序不同而失效，MySQL优化器会作优化。

索引失效13.png

• group by 基本上都需要排序，会有临时表产生。

索引失效18.png

索引判断题

索引失效14.png
索引失效15.png
索引失效16.png
索引失效17.png
索引失效19.png

索引口诀

口诀.png
口诀2.png

排序——Order By 优化

案例分析

排序.png

排序的两种方式

排序1.png

filesort的两种排序方式与优化

双路排序和单路排序

排序3.png

单路排序的问题

排序4.png

单路排序优化

排序5.png

Order by排序优化总结

排序2.png

Group By优化

排序6.png

慢查询调优

步骤

慢查询.png

小表驱动大表

小表驱动大表.png
exist.png

什么是慢查询

慢查询.png

默认不开启，只有在有问题的时候才使用命令开启慢查询日志。

慢查询2.png

开启慢查询

查询脚本

-- 查看是否开启慢查询
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%';

本次开启生效

慢查询3.png 慢查询4.png

永久开启生效

慢查询5.png

配置如下：

慢查询10.png

慢查询日志linux文件路径

慢查询8.png 慢查询9.png

慢查询阈值

慢查询6.png

注意需要新开会话或者加global才能看到生效值

慢查询7.png

mysql日志分析工具——mysqldumpslow

#查看命令帮助
mysqldumpslow --help

慢查询11.png
慢查询12.png

show profiles——慢查询资源消耗明细

image.png

show profile查询使用参数

profile2.png

show profile要注意的资源消耗点

image.png

使用步骤

profile.png image.png

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;

全局查询日志——测试环境用

永远不要在生产上开启该功能。

配置启动

image.png

sql启动——本次有效

image.png

sql脚本

-- 开启全局日志
set GLOBAL general_log=1;
SET GLOBAL log_output='TABLE';

-- 查询全局日志表
SELECT * FROM mysql.general_log;

MySQL锁机制

锁按照粒度和操作类型区分方式

image.png

表锁的加锁、解锁和查看锁情况——表锁偏读

image.png
image.png image.png

表加读锁后的情况

session1加了读锁时：session1可以查自己锁的表，不能新增或更新自己锁的表，也不能查询或更新别的表；

session2可以读被锁的表，但不能更新被锁的表（会被阻塞）；可以读和更新其他表。

image.png

表加写锁后的情况

session1加了写锁时：session1可以查自己锁的表，可以新增或更新自己锁的表；不能查询或更新别的表；

session2可以读被锁的表会被阻塞，也不能更新被锁的表；可以读和更新其他表。

image.png

结论

image.png

表锁分析

image.png
image.png

行锁——行锁偏写（写多的场景）

MyIsam与InnoDB最大的不同

image.png

并发事务带来的问题

image.png

行锁变表锁的情况

查询条件类型使用不当导致的索引失效变表锁的情况，所以在使用更新/查询时一定要注意查询条件与字段类型相匹配。

image.png

间隙锁

mysql会通过间隙锁来锁定一个范围，保证不出现幻读的情况。但是会阻塞住该范围内的插入操作。

image.png

给某行要修改的数据加行锁

被for unpdate锁定的行在其他操作过来是会被阻塞，属于悲观锁。

image.png

行锁分析

image.png
image.png

-- 行锁情况分析
SHOW STATUS LIKE '%innodb_row_lock%';

行锁优化建议

image.png

MySQL主从复制

image.png

主从复制的过程（原理）

image.png

一主一从配置（window主linux从）

• 如果都是linux上的mysql主从复制则直接参考linux上的命令位置，修改的配置是一样的。
• 主从复制的两个服务器要能互相ping通。

image.png
image.png
image.png image.png
image.png
image.png
image.png

其他

创建函数

注意：函数有返回值，存储过程没有返回值，只有输出值。

函数.png

创建存储过程

存储过程.png

参考

MySQL数据库高级