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
网友评论