MySQLSQL调优
2022-04-01 16:50:32 1 举报AI智能生成
MySQL面试八股文
作者其他创作
大纲/内容
SQL调优
数据库事务
数据库事务代表一次完整的操作
ACID
A 原子性
全部成功、全部失败
C 一致性
开始和结束必须保持一致
I 隔离性
事务相互隔离，中间状态不可见
D 持久性
修改是永久的
隔离级别
原因：多个单元操作，并发执行会出现，脏读，不可重复读 幻读
解决方案
进行并发处理时候隔离
进行并发处理时候隔离
未提交读（R-U）
读取的时候不加锁
已提交读 (R-C)
读取时候加行级共享锁，一旦读完立刻释放，读取成功后释放
可重复读 (R - R)
事务默认级别， 读取时候加行锁，事务结束释放
串行化 (SE)
无并发可言 ，读取时候加表锁
实现（InnoDB）
锁
mvcc
生成redoLog，进行数据回滚
CAS 版本控制 + 读写分离
schema与数据类型优化
范式和反范式
范式
反范式
其他注意点
多加一些冗余，减少JOIN选择。以空间换时间的思想进行设计
主键选择
代理主键（推荐）
与业务无关、无意义的数字序列
自然主键
事务属性中的自然唯一标识
数据库字符集
存储引擎
InnoDB
聚簇索引、支持事务、表锁、行锁、全文索引
MyISAM
非聚簇索引、不支持事务、不支持表锁、不支持行锁、使用大量select
MEMORY
数据存储在内存中
InnoDB 与 MyISAM 对比
适当拆分
执行计划
explain select * from users
ID 越大 越先执行
id号分为三种情况：
1、如果id相同，那么执行顺序从上到下
2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
1、如果id相同，那么执行顺序从上到下
2、如果id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3、id相同和不同的，同时存在：相同的可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行
select_type 查询类型
主要用来分辨查询的类型，是普通查询还是联合查询还是子查询
sample:简单的查询，不包含子查询和union
primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
union:若第二个select出现在union之后，则被标记为union
dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
union result:从union表获取结果的select
subquery:在select或者where列表中包含子查询
dependent subquery:subquery的子查询要受到外部表查询的影响
DERIVED: from子句中出现的子查询，也叫做派生类，
UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
sample:简单的查询，不包含子查询和union
primary:查询中若包含任何复杂的子查询，最外层查询则被标记为Primary
union:若第二个select出现在union之后，则被标记为union
dependent union:跟union类似，此处的depentent表示union或union all联合而成的结果会受外部表影响
union result:从union表获取结果的select
subquery:在select或者where列表中包含子查询
dependent subquery:subquery的子查询要受到外部表查询的影响
DERIVED: from子句中出现的子查询，也叫做派生类，
UNCACHEABLE SUBQUERY：表示使用子查询的结果不能被缓存
uncacheable union:表示union的查询结果不能被缓存：sql语句未验证
type 访问方式
type显示的是访问类型，访问类型表示我是以何种方式去访问我们的数据，最容易想的是全表扫描，直接暴力的遍历一张表去寻找需要的数据，效率非常低下，访问的类型有很多，效率从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_key 显示可能用到那些索引
key 实际用到索引
rows 预估行数
extra 额外信息 例如排序类型 、覆盖索引
索引优化
索引基础知识
索引结构
哈希表
B+树
了解常用数据结构 哈希表、二叉树、红黑树、B树、B+树
优点
减少服务器数据检索时间 , 索引列已经排好顺序
随机IO变为顺序IO
用途
快速匹配where子句
MySQL会排除多余数据扫描，总会命中行数最少的索引
分类
主键
唯一
普通
全文
组合
技术名词
回表
覆盖索引
最左匹配
索引下推
索引匹配方式
全值匹配
匹配最左前缀
匹配列前缀
匹配范围
精确匹配某一列并范围匹配另一列
只访问索引查询
hash索引
memory 存储引擎
基于哈希表实现，只有精确匹配才能被查找
只存储hash值，数据紧凑，索引非常快
限制
只包含hash值，不进行字段存储，不是顺序存储，无法进行排序
不支持部分列存储
考虑哈希冲突情况，冲突比较多会造成数据列维护不方便，代价也很高
使用案例
当需要存储大量的URL，并且根据URL进行搜索查找，如果使用B+树，存储的内容就会很大
select id from url where url=""
也可以利用将url使用CRC32做哈希，可以使用以下查询方式：
select id fom url where url="" and url_crc=CRC32("")
此查询性能较高原因是使用体积很小的索引来完成查找
select id from url where url=""
也可以利用将url使用CRC32做哈希，可以使用以下查询方式：
select id fom url where url="" and url_crc=CRC32("")
此查询性能较高原因是使用体积很小的索引来完成查找
组合索引
包含多个列作为索引，需要注意的是正确的顺序依赖改索引的查询，同事满足最左匹配原则
案例，建立索引 a,b,c
a = 3
a
a=3 and b=5
a 、 b
a=3 and b=4 and c=5
a,b,c
b=3 or c=4
没使用索引
a = 3 and c=4
a
a = 3 and b>10 and c = 7
a,b
a = 3 and b like '%1%' and c = 7
a
聚簇索引与非聚簇索引
聚簇索引
优点
可以把相关数据保存在一起
数据访问更快，不需要另外查找
覆盖索引扫描可以直接到叶节点
缺点
聚簇数据极大的现在了数据IO的扩展性
插入速度验证依赖插入书序，按照主键插入顺序最快
更新聚簇索引列代价很高，会强制将每个更新的行移动到新的位置
新插入行如果超过索引页大小，会导致页分裂的问题
全表扫描变慢
非聚簇索引
覆盖索引
介绍
如果一个索引包含所有需要查询的字段的值，称之为覆盖索引
不是所有类型的索引都成为覆盖索引，覆盖索引必须要存储索引列的值
不同存储引擎实现索引的方式不同，不是所有引擎都支持覆盖索引，memory不支持
优势
索引条目通常小于数据行大小，如果只需要读取索引，会极大的减少数据访问
索引按照顺序存储的，对IO密集型范围查找会比随机查找快很多
IO密集型
InnoDB是聚簇索引，覆盖索引效率特别高
案例
优化细节
索引列进行扫描时，尽量不要用表达式，将计算放到业务层而不是数据层
尽量使用主键索引，避免发生回表
使用前缀索引
使用索引扫描进行排序
union all , in , or 都能够使用索引，推荐使用in
范围列可以用到索引 但是只能用一列索引
强制转换可能会导致全表扫描
更新十分频繁的操作不宜建立索引
索引列不能为空
表进行连接时候，最好不要超过三张表，因为join需要的字段数据类型必须一致
能使用limit时候尽量使用limit limit 限制输出
单表索引建议控制在5个以内
组合索引字段不应该超过5个
索引监控
索引优化简单案例
查询优化
查询慢的原因
网络
CPU
IO
上下文切换
系统调用
生成统计信息
锁等待时间
1：分析存储引擎
2：读锁、写锁
优化数据访问
大数据量
加载多余数据
执行过程的优化
查询缓存
MySQL 优化机制
重新定义关联查询顺序
外连接转内连接
等价变换规则、简化SQL
优化 count min max
带条件 ， 索引分组
索引覆盖扫描
子查询进行优化、进行缓存
等值传播
两个列的值关联，MySQL可以进行参数传播
排序优化
算法优化
两次传输排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序，第二次是将排好序的结果按照需要去读取数据行。
这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
这种方式效率比较低，原因是第二次读取数据的时候因为已经排好序，需要去读取所有记录而此时更多的是随机IO，读取数据成本会比较高
两次传输的优势，在排序的时候存储尽可能少的数据，让排序缓冲区可以尽可能多的容纳行数来进行排序操作
一次传输排序
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果，此方式只需要一次顺序IO读取所有的数据，而无须任何的随机IO，问题在于查询的列特别多的时候，会占用大量的存储空间，无法存储大量的数据
当需要排序的列的总大小超过max_length_for_sort_data定义的字节，mysql会选择双次排序，反之使用单次排序，当然，用户可以设置此参数的值来选择排序的方式
分区表
服务器参数设置
MySQL集群
面试
锁
单一版本控制
事务具有隔离性
隔离级别
读未提交
读已提交
可重复读
默认
串行化
高度计算
InnoDB存储引擎默认一个数据页大小为16kb，非叶子节点存放（key，pointer），pointer为6个字节，key为4个字节，即非叶子节点能存放16kb/14左右的key，pointer，而叶子节点如果一条数据大小为100字节，那一个叶子节点大约可存放160条数据。
如果高度为3，则可存放数据为：16kb/14 * 16kb/14 * 160大约1亿多数据。
因此InnoDB存储引擎b+树的高度基本为2-3.
如果高度为3，则可存放数据为：16kb/14 * 16kb/14 * 160大约1亿多数据。
因此InnoDB存储引擎b+树的高度基本为2-3.
log
CRUD 操作 -- 写入LOG-Buffer
redo-log
更新操作
undo-log
原子性、执行前的状态 ， 用于记录回滚
bin-log
服务端日志文件
主从复制
主 -- 从 进行 log复制
1：主库 binlog 日志生成后进行网络传输
2：从库将binlog 日志落盘 SQL 解析
3：5.7 以后 IO线程是多线程，从库SQL解析是串行化解析的，延迟非常高 relaylog
2：从库将binlog 日志落盘 SQL 解析
3：5.7 以后 IO线程是多线程，从库SQL解析是串行化解析的，延迟非常高 relaylog
主库 1000/s 操作以后会造成延迟1 、 2 ns 的延迟
问题解决
semi sync
半同步复制
半同步复制
必须同步到从库，才操作成功
SQL 线程开启并行复制
show status ；
seconds_behind_master 查看当前落后从库的状态 ms
seconds_behind_master 查看当前落后从库的状态 ms
分库 、 削峰
分多个库，开启并行复制
代码、重新设计
分多个库，开启并行复制
代码、重新设计
慢查询查看
开启慢查询日志
slow_query_log -- on 打开慢查询
show_query_log_file -- 默认记录文件
log_query_time -- 10S 超过执行时间会被记录
explain 分析sql
type 索引类型
联合索引最左匹配原则
0 条评论
下一页
