Mysql优化笔记

最新推荐文章于 2024-08-15 09:21:27 发布

阿宅Java

最新推荐文章于 2024-08-15 09:21:27 发布

阅读量158

点赞数

分类专栏：数据库学习笔记文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/qq_33586611/article/details/127923596

版权

数据库学习笔记专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一.Mysql逻辑分层

1.链接层

提供与客户端链接的服务

2.服务层

提供各种用户使用的接口(select)
提供sql优化器（对效率低sql进行自动优化）

3.引擎层

提供各种存储数据的方式-引擎类型

4.存储层

存储数据

二.Mysql数据库引擎

1.Mysql引擎分类

InnoDb：事务优先，适合高并发操作，使用行锁
Mysiam：性能优先，使用表锁

2.Mysql引擎命令

#获取当前数据库支持引擎：
show engines ; 
show variables like '%storage_engine%' 
#创建表指定引擎： 
CREATE TABLE `NewTable` (...) ENGINE=InnoDB ;

三.索引Index

1.索引原理BTree+

每个节点都可以有n个数据及n+1条指向下层节点的指针，n个数据作用为用来切分n+1条指针
BTree+ 中查询任意数据次数为BTree树的高度，BTree+ 中所有数据存贮在叶节点中
BTree查询过程：根据顶层n条数据确定区间即定位索引，再根据索引检索到下层节点重复根据数据定位索引操作，直至叶节点查询到数据

2.索引分类：

单值索引 index：普通索引，索引某列，表中可存在多个单值索引
唯一索引 unique：同普通索引，但值不可重复
复合索引 index：多列构成的索引，命中索引头即可命中索引
主键索引 primary Key : 同唯一索引，但主键索引值不可为null

3.索引优缺

3.1.索引优点

根据BTree+查询策略更快的命中数据，提供查询效率
降低io使用率，降低cpu使用率（排序等-索引本身已排序）

3.2.索引缺点

索引本身较大,过多索引会大量增加数据存储内存
索引会降低增删改效率（需修改数据后修改索引）

3.3.不适用场景
少量数据，
频繁更新数据
较少使用查询字段

4.索引命令：
创建索引

creat 索引类型索引名 on 表名（字段…）
修改索引

alter table 表名 add 索引类型索引名（字段…）
查询索引

show index from 表名

四.Explain结果解析

模拟sql优化器执行sql语句，从而了解sql执行情况

1.id-执行编号

id相同时，以自上而下执行，同优先级的执行顺序,因数据量改变,较小先执行：笛卡儿积策略;
id不同时，以自大向小的优先级执行；
id相同与不同同时存在，则先按照自大向小优先级查询，再按照自上而下执行

2.select_type-查询类型

PRIMARY：包含子查询sql中的主查询,一般为最外层
SUBQUERY：包含子查询sql中的子查询，一般为非最外层
simple：简单查询（不包含子查询，union）

derived：衍生查询（使用了临时表）

例:
	a. form子查询中只有一张表
	explain select  cr.cname 	from ( select * from course where tid in (1,2) ) cr ;
	b. form子查询中,如存在table1 union table2 则 table1为derived，table2为union
	explain select  cr.cname 	from ( select * from course where tid = 1  union select * from course where tid = 2 ) cr ;

union: 子查询存在

例:	table1 union table2此时table2为union

union result :表之间存在union查询

3.table-表名

4.type-查询类型

system : 只有一条数据的系统表；或衍生表只有一条数据的主查询

const : 仅能查到一条数的sql：用于primary key 或者unique索引
eq_ref ：唯一性索引：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多、不能0

ref ：非唯一性索引：对于每个索引建的查询，返回匹配的所有行
range ：检索指定范围的行：where后范围查询(between,> < >=, 特殊:in有时候会失效，从而转为无索引all)

index: 查询全部索引中数据

all：查询全部表中的数据

注:

效率对比 : system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

5.possible_keys-可能命中索引

6.key-实际使用的索引

7.key_len-实际使用索引的长度,

用于判断复合索引是否被完全使用（a,b,c）。

8.ref-表之间的引用

指明当前表所参照的字段

9.rows-通过索引查询到的数据量

10.Extra-额外的信息

using filesort ：性能消耗大；需要“额外”的一次排序（查询）,常见于 order by 语句中。

对于单索引，如果排序和查找为同字段，则不会出现using filesort；否则，则会出现using filesort

对于复合索引：不能跨列（最佳左前缀），where和order by 按照复合索引的顺序使用，不要跨列或无序使
using temporary: 性能损耗大，用到了临时表。一般出现在group by 语句中。

应查询那些列，就根据那些列 group by
using index :性能提升; 索引覆盖（覆盖索引）,不读取原文件，只从索引文件中获取数据,不需要回表查询

如果用到了索引覆盖(using index时)，会对 possible_keys和key造成影响：

a.如果没有where，则索引只出现在key中；

b.如果有where，则索引出现在key和possible_keys中。
using where （需要回表查询）
impossible where ： where子句永远为false
Using join buffer: Mysql引擎使用了连接缓存。

五.慢sql排查

慢sql: 性能低，执行时间较长，等待时间过长，sql语句欠佳（链接查询），索引失效，服务器参数设置不合理（缓冲、线程数）

1.开启慢查询日志:

慢查询日志:记录MySQL种响应时间超过阀值的SQL语句（long_query_time，默认10秒）,默认关闭
查询慢查询阈值

show variables like ‘%long_query_time%’ ;
临时设置慢查询阈值（需重新登录）

set global long_query_time = 5 ;
查询是否开启慢查询日志

show variables like ‘%slow_query_log%’ ;
临时开启慢查询日志，需重启mysql

set global slow_query_log = 1 ;
永久开启慢查询日志，需重启mysql

修改vi /etc/my.cnf，加入：
#–永久开启慢查询日志
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log
#–永久设置慢查询阈值（不生效则重启）
long_query_time=3

2.查看慢查询sql

查看慢查询sql数目

show global status like ‘%slow_queries%’
查看慢查询日志记录的sql

cat /var/lib/mysql/localhost-slow.log
mysqldumpslow工具查看慢SQL

mysqldumpslow 【参数】慢查询日志的文件
实例：
#获取返回记录最多的3个SQL
mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
#获取访问次数最多的3个SQL
mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
#按照时间排序，前10条包含left join查询语句的SQL
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost-slow.log
注：
s：排序方式
r:逆序
l:锁定时间
g:正则匹配模式

六.sql解析过程

编写过程：

select dinstinct …from …join …on …where …group by …having …order by …limit …
解析过程：

from … on… join …where …group by …having …select dinstinct …order by limit …
order by 查询原理

1.using filesort 据IO的次数区分为两种算法：
双路排序：MySQL4.1之前默认，扫描两次磁盘：1.从磁盘中扫描排序字段，在buffer中排序，2.扫描其他排序字段
单路排序：MySQL4.1之后默认，仅读取一次字段，在buffer中排序（并非绝对，如数据量过大，则会进行分片读取，多次读取）
```
  注： 
  	单路占用buffer>双路，如设置buffer过小（需要排序的列的总大小超过了max_length_for_sort_data定义的字节数），将自动单路转双路，设置buffer大小为 set max_length_for_sort_data = 1024 （byte）	
```

七.sql优化策略

1.避免索引使用乱序

alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a3=3 and a4 =4 ; 
	--最佳写法,索引的使用顺序和复合索引一致
explain select a1,a2,a3,a4 from test03 where a4=1 and a3=2 and a2=3 and a1 =4 ; 
	--概率最佳写法，索引顺序使用虽不一致，但sql在真正执行前经过了SQL优化器的调整下与索引保持了一致。

2.避免索引使用跨列

alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; 
	--以上SQL用到了a1 a2两个索引，而a4跨列使用,索引失效,a3与a1,12连续,索引有效;

3.避免索引使用时进行排操作

alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
explain select * from test03 where a1= 1 and a2 = 2 ;
	--用到了a1,a2个索引
explain select * from test03 where a1 = 1 and a2*2 = 2 ;
	--用到了a1索引
explain select * from test03 where a1*2 = 1 and a2*2 = 2 ;
	--用到了0个索引
explain select * from test03 where a1*2 = 1 and a2 = 2 ;
	--用到了0个索引,对于复合索引，左边失效，右侧全部失效。
explain select * from test03 where a1 = '123' ; 
	--程序底层将 '123' -> 123，即进行了类型转换，因此索引失效

3.避免回表查询

alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
explain select a1,a2,a3,a4 from test03 where a1=1 and a2=2 and a4=4 order by a3; 
	--以上SQL用到了a1 a2两个索引，该两个字段using index索引覆盖 ;而a4跨列使用，该索引失效，回表查询，出现using where;
explain select a1,a2,a5 from test03 where a1=1 and a2=2; 
	--以上SQL用到了a1 a2两个索引，该两个字段using index索引覆盖 ;而a5需回表查询，出现using where;

4.避免额外排序

alter table test03 add index idx_a1_a2_a3_4(a1,a2,a3,a4) ;
explain select a1,a2,a3,a4 from test03 where a1=1  order by a3; 
	--以上SQL用到了a1索引，orderby条件中a3与a1跨列，故出现using filesort(“多了一次额外的查找/排序”) -	--不要跨列使用( where和order by 拼起来，不要跨列使用)
explain select a1,a2,a3,a4 from test03 where a1=1  order by a2 , a3; 
	--以上SQL用到了a1索引，oderby中a2，a3与a1连续，故不会出现出现using filesort ：

5.避免其他问题索引失效

复合索引不能使用不等于 in-概率失效,如必须使用in查询放入最后，避免in范围查询造成索引失效
符合索引不能使用 != < > 或is null (is not null) ，否则自身以及右侧索引全部失效
like尽量以“常量”开头，不要以’%'开头，否则索引失效
不要使用or，否则可能所有索引失效(包含左侧索引)

6.其他优化

多表关联小表驱动大表原则,小表在左
主查询的数据集大，使用in，子查询数据集较大，则使用exist
order by: 选择使用单路、双路；调整buffer的容量大小；
保证全部的排序字段排序的一致性（都是升序或降序）

八.mysql锁机制分析

锁机制：解决因资源共享而造成的并发问题。

示例：
买最后一件衣服X
A: X 买：X加锁 ->试衣服…下单…付款…打包 ->X解锁
B: X 买:发现X已被加锁，等待X解锁,X已售空

1. 锁分类：

操作类型分类:
- 读锁（共享锁）：对同一个数据（衣服），多个读操作可以同时进行，互不干扰
- 写锁（互斥锁）：如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作
操作范围分类
- 表锁:一次性对一张表整体加锁,如MyISAM引擎
  - 特点:开销小、加锁快；无死锁
  - 缺点:锁的范围大，容易发生锁冲突、并发度低
- 行锁:一次性对一条数据加锁,如InnoDB引擎
  - 特点:；锁的范围较小,不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）
  - 缺点:。开销大，加锁慢；容易出现死锁
- 页锁

2. 锁互斥：

2.1 表锁

是通过unlock tables，也可以通过事务解锁

读锁:

	当前表	其他表
当前会话	可读不可写	不可读不可写
其他会话	可读不可写	可读可写

写锁

	当前表	其他表
当前会话	可读可写	不可读不可写
其他会话	不可读不可写	不可读不可写

2.2.行锁

是通过unlock tables，也可以通过事务解锁

注: 如获取锁语句有where，则实际加索的范围就是where后面的范围（不是实际的值,如间隙锁）

不同会话操作(DML)同条数据,其他会话必须等当前commit/rollback后才能操作;

会话1:
insert into linelock values(6,'a6') ;
	---插入数据id=6,未commit
会话2:
update linelock set name='ax' where id = 6;
	---修改数据id=6,阻塞

不同会话操作(DML)不同数据,互不干涉,各自commit后生效;

会话1:
insert into linelock values(8,'a8') ;
	---插入数据id=8,未commit
会话2:
update linelock set name='ax' where id = 5;
	---修改数据id=5,未阻塞

如表无索引,则行锁升级为表锁;

---------------无索引--------------------
会话1:
update linelock set name = 'ai' where name = '3' ;
	---修改数据name=3,name字段无索引,未commit
会话2:
update linelock set name = 'aiX' where name = '4' ;
	---修改数据name=4,name字段无索引,阻塞,待会话1commit后,会话2才能执行完毕,且需要会话2commit后生效

---------------有索引--------------------
alter table linelock add index idx_linelock_name(name);
会话1:
update linelock set name = 'aj' where name = '1' ;
	---修改数据name=1,name字段有索引,未commit
会话2:
update linelock set name = 'ajX' where name = '2' ;
	---修改数据name=2,name字段有索引,未阻塞,commit后生效

如索引发生类型转化,则行锁升级为表锁;

---------------无索引--------------------
会话1:
update linelock set name = 'ak' where name = 9 ;
	---修改数据name=9,类型发生转化,未commit
会话2:
update linelock set name = 'akX' where name = 10 ;
	---修改数据name=10,类型发生转化,阻塞,待会话1commit后,会话2才能执行完毕,且需要会话2commit后生效

---------------有索引--------------------
alter table linelock add index idx_linelock_name(name);
会话1:
update linelock set name = 'aj' where name = '1' ;
	---修改数据name=1,name字段有索引,未commit
会话2:
update linelock set name = 'ajX' where name = '2' ;
	---修改数据name=2,name字段有索引,未阻塞,commit后生效

注:测试表如下

create table linelock(
id int(5) primary key auto_increment,
name varchar(20)
)engine=innodb ;
insert into linelock(name) values('1')  ;
insert into linelock(name) values('2')  ;
insert into linelock(name) values('3')  ;
insert into linelock(name) values('4')  ;
insert into linelock(name) values('5')  ;


insert into linelock(name) values('9')  ;
insert into linelock(name) values('10')  ;

2.2.1.间隙锁

行锁的一种特殊情况,值在范围内,但却不存在,Mysql会自动给间隙加索

update linelock set name ='x' where id >1 and id<9 ;   
	---即在此where范围中，没有id=7的数据，则id=7的数据成为间隙,mysql会对其加间隙锁

3.锁操作

3.1表锁

增加/释放/查看表锁

locak table 表1  read/write  ,表2  read/write   ,...;
unlock tables;
show open tables ;

分析表锁定的严重程度

show status like 'table%' ;
	---Table_locks_immediate :即可能获取到的锁数
	---Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
	---Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎

3.2.行锁

分析表锁定的严重程度

show status like '%innodb_row_lock%' ;
    ---Innodb_row_lock_current_waits :当前正在等待锁的数量  
    ---Innodb_row_lock_time：等待总时长。从系统启到现在 一共等待的时间
    ---Innodb_row_lock_time_avg  ：平均等待时长。从系统启到现在平均等待的时间
    ---Innodb_row_lock_time_max  ：最大等待时长。从系统启到现在最大一次等待的时间
    ---Innodb_row_lock_waits ：	等待次数。从系统启到现在一共等待的次数