MYSQL优化
2023-01-06 15:44:55 0 举报AI智能生成
mysql优化
作者其他创作
大纲/内容
索引
帮助MySQL高效获取数据的排好序的数据结构
索引数据结构
二叉树
会退化成一个链表
全左链表
全右链表
红黑树
能解决二叉树的问题---自平衡
不能解决时间复杂度的问题---》B树一般只有3层
Hash表
对索引的key进行一次hash计算就可以定位出数据存储的位置
很多时候Hash索引要比B+ 树索引更高效
仅能满足 “=”，“IN”，不支持范围查询
hash冲突问题
例如hash算法：md5/crc16
B树
叶节点具有相同的深度，叶节点的指针为空
所有索引元素不重复
节点中的数据索引从左到右递增排列
B+ tree
非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
叶子节点包含所有索引字段
叶子节点用指针连接，提高区间访问的性能
存储引擎分类
mylsum
MyISAM索引文件和数据文件是分离的(非聚集)
innodb
InnoDB索引实现(聚集)
表数据文件本身就是按B+Tree组织的一个索引结构文件
聚集索引-叶节点包含了完整的数据记录
为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
自增长的整形ID比UUID好，因为B+树查找时候，字符串的uuid还要通过ak2码去
一一比较，耗费比较多的时间和磁盘空间
一一比较，耗费比较多的时间和磁盘空间
为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)
InnoDB支持事务（TRANSACTION）
InnoDB支持行级锁
索引分类
主键索引
联合索引（非主键索引）
索引最左前缀原理
非主键索引叶子节点是主键
这样是保持一致性
节省存储空间
如果还要获取其他数据，则最后还要通过主键反查住索引
非主键索引，如果是联合索引abc，则叶子上面有abc+主键四个字段的数据
mysql问题分析方案
Explain工具
id列
id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
select_type列
select_type 表示对应行是简单还是复杂的查询。
simple：简单查询。查询不包含子查询和union
primary：复杂查询中最外层的 select
subquery：包含在 select 中的子查询（不在 from 子句中）
derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含 义）
union：在 union 中的第二个和随后的 select
table列
这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是 <derivenN> 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查 询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
type列
表示关联类型或访问类型
最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
NULL
mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可 以单独查找索引来完成，不需要在执行时访问表
explain select min(id) from film;
const
mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。
explain select * from film where id = 1
system
是 const的特例，表里只有一条元组匹配时为system
eq_ref
primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。
这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。
explain select * from film_actor left join film on film_actor.film_id = film.id;
ref
相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行。
简单 select 查询，name是普通索引（非唯一索引）
关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。
range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。
使用一个索引来检索给定范围的行。
mysql> explain select * from actor where id > 1;
index
扫描全索引就能拿到结果，一般是扫描某个二级索引
二级索引一般比较小，所以这 种通常比ALL快一些
ALL
即全表扫描，扫描你的聚簇索引的所有叶子节点
一般来说，得保证查询达到range级别，最好达到ref
possible_keys列
这一列显示查询可能使用哪些索引来查找。
key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
key_len列
n均代表字符数
char(n)：如果存汉字长度就是 3n 字节
varchar(n)：如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为 varchar是变长字符串
数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型
date：3字节
timestamp：4字节
datetime：8字节
date：3字节
timestamp：4字节
datetime：8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL
ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
Extra列
这一列展示的是额外信息。常见的重要值如下
Using index：使用覆盖索引
覆盖索引一般针对的是辅助索引，整个 查询结果只通过辅助索引就能拿到结果，
不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值
简单来说，就是不用回表查询，索引已经有需要查询的字段
Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖
Using index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
ysql> explain select * from film_actor where film_id > 1;
Using temporary
mysql需要创建一张临时表来处理查询。
出现这种情况一般是要进行优化的，首先是想到用索 引来优化。
actor.name没有索引，此时创建了张临时表来distinct
mysql> explain select distinct name from actor;
导致Using temporary
Using filesort
将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。
actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录
mysql> explain select * from actor order by name;
导致Using filesort
Select tables optimized away
使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是
效率其实是挺好的
索引最佳实践
全词匹配
这样理解，查询条件用=‘字符串’比like好，前者mysql看成一个常量
最左前缀法则
如果索引了多列，要遵守最左前缀法则
指的是查询从索引的最左前列开始并且不跳过索引中的列。
不在索引列上做任何操作
（计算、函数、（自动or手动）类型转换）
会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
尽量使用覆盖索引
（只访问索引的查询（索引列包含查询列））
减少 select * 语句
代码先行，索引后上
一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立 索引。
联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原 则。
不要在小基数字段上建立索引
长字符串我们可以采用前缀索引
对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
where与order by冲突时优先where
基于慢sql查询做优化
https://blog.csdn.net/qq_40884473/article/details/89455740
不容易理解的综合例子
联合索引第一个字段用范围不会走索引
mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不 如就全表扫描
强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 A ND position ='manager';
虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表 扫描高，因为回表效率不高
in和or
在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
like KK% 一般情况都会走索引
[索引下推]
索引下推:mysql5.6之前 like‘k%’在子索引找到主键之后，会反查主索引
5.6开始，则直接在子索引找其他对应的字段
5.6开始，则直接在子索引找其他对应的字段
排序
单路排序
单路排序会把所有需要查询的字段都放到 sort buffer 中
双路排序
双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段
max_length_for_sort_data
可以适当把 max_length_for_sort_data 配置【小】点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更 多的行，只是需要再根据主键回到原表取数据
如果 MySQL 排序内存有条件可以配置比较【大】，可以适当增大 max_length_for_sort_data 的值，让优化器 优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查
询结果了。
询结果了。
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增 大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。
Join关联查询优化
前提
T2表有100条数据
T1表有1W跳数据
a是T1 T2表的非主键索引字段
b是非索引字段
嵌套循环连接 Nested-Loop Join(NLJ) 算法
1 mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a; 【a是T1 T2表的非主键索引字段】
驱动表是 t2，被驱动表是 t1。
优化器一般会优先选择【小表】做驱动表。所以使用 【inner join 】时，排在前面的表并不一定就是驱动表
当使用【left join】时，左表是驱动表，右表是被驱动表
当使用【right join】时，右表时驱动表，左表是被驱动表
过程
整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值
根据 t2 表中 a 的值索引扫描 t1 表 中的对应行
(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行)。
(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行)。
因此整个过程扫描了 200 行。
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b; 【b是非索引字段】
1.把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据
磁盘扫描次数=1W+100=10100次
内存判断次数1W*100=100W次
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单， 就是分段放
非索引字段不使用NLJ
磁盘扫描会100W次
内存运算100W次比磁盘扫描100W次效率高很多
in和exsits优化
小表驱动大表，即小的数据集驱动大的数据集
IN
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B)
#等价于：
for(select id from B){
select * from A where A.id = B.id
}
select * from A where A.id = B.id
}
EXSITS
当A表的数据集小于B表的数据集时，exists优于in
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
select * from B where B.id = A.id
}
EXISTS (subquery)只返回TRUE或FALSE,
因此子查询中的SELECT * 也可以用SELECT 1替换,
官方说法是实际执行时会忽略SELECT清单,因此没有区别
因此子查询中的SELECT * 也可以用SELECT 1替换,
官方说法是实际执行时会忽略SELECT清单,因此没有区别
count(*)查询优化
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)
为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索 性能应该更高，mysql内部做了点优化
查询mysql自己维护的总行数
myisam
对于myisam存储引擎的表做不带where条件的count查询性能是很高的
因为myisam存储引擎的表的总行数会被 mysql存储在磁盘上，查询不需要计算
innodb
不会存储表的总记录行数(因为有MVCC机制)
查询count需要实时计算
事务
ACID
【面向过程】原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
【面向数据】一致性(Consistent) ：
在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规 则都必须应用于事务的修改,以保持数据的完整性。
比如A向B转账，不可能A扣了钱，B却没收到。
隔离性(Isolation) ：
数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。
这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：
事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
redo日志实现
并发带来的问题
脏写
【最后的更新覆盖了由其他事务所做的更新。】
【当多个事务并发写同一数据时，先执行的事务所写的数据会被后写的覆盖，这也就是更新丢失。】
当两个事务同时尝试去更新某一条数据记录时，就肯定会存在一个先一个后。
而当事务A更新时，事务A还没提交，事务B就也过来进行更新，覆盖了事务A提交的更新数据，这就是脏写。
脏读
事务A读取到了事务B已经修改但尚未提交的数据
不可重读
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读
事务A读取到了事务B提交的新增数据，不符合隔离性
锁详解
从性能上分为乐观锁(用版本对比来实现)和悲观锁
从对数据库操作的类型分，分为读锁和写锁(都属于悲观锁)
读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
当前session和其他session都可以读该表
当前session中插入或者更新锁定的表都会报错，其他session插入或更新则会等待
写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
当前session对该表的增删改查都没有问题
其他session对该表的所有操作被阻塞
从对数据操作的粒度分，分为表锁和行锁
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；
锁定粒度大，发生锁冲突的概率最高，并发度最低；
一般用在整表数据迁移的场景。
手动增加表锁：lock table 表名称 read(write),表名称2 read(write);
删除表锁 ：unlock tables;
行锁
每次操作锁住一行数据。
开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最 高
一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞
总结
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,
在执行update、insert、delete操作会自 动给涉及的表加写锁
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁
但是update、insert、delete操作会加行 锁。
读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
间隙锁(Gap Lock)
间隙锁，锁的就是两个值之间的空隙。
间隙锁 在某些情况下可以解决幻读问题。
锁优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
合理设计索引，尽量缩小锁的范围
尽可能减少检索条件范围，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
尽可能低级别事务隔离
MVCC多版本并发控制机制
前提：可重复读隔离级别
对一行数据的【读】和【写】两个操作默认 是不会通过加锁互斥来保证隔离性
Mysql在【读已提交】和【可重复读隔离级别】下都实现了MVCC机制。
0 条评论
下一页
